Когда формула строится для одной ячейки то не имеет значения какого типа ссылки на ячейки в ней испотльзуют

Работа добавлена на сайт samzan.net:

Статистические методы_КОНСПЕКТ

Адрессация в Eхlее

Для того что бы указать аргументы в функции (мастер функции) или в формуле (формула начинается с =, +) можно использовать ссылки на ячейки, которые представляют собой адреса ячеек. Когда формула строится для одной ячейки, то не имеет значения, какого типа ссылки на ячейки в ней испотльзуются.

Существующая адрессация (ссылки на ячейки):

Относительные
Абсолютне
Смежные

При копировании формулы имеет значения вид ссылки. Формулу можно копировать.

Относительные ссылки – имеют вид (В7, Н18) т.е это № ряда и № строки

Начинатьс левого верхнего угла.

Если формула содержит относительные ссылки на ячейку, то при копированиии формулы, относительные ссылки на ячейки меняются на то же кол-со строк и столбцов и в том же направлении, котором меняется сама формула.

Абсолютные ссылки – имеют форму такую же как относительные, только перед именем столбца и строки ставится $.

Абсолютные ссылки при копировании формулы не меняются.

Смешанные ссылки имеют значения только если формула копируется по диагонали. Они закрепляют в ссылке на ячейки либо имя столбца либо № строки. Если данные в строке – строку, если в столбце – столбец.

Использование вложенных функций

Часто при построении формул используются вложенные функции – функции, которые являются аргументами других функций.

Для того чтобы использовать в качестве аргумента функции другую вложенную ф-цию надо поставить курсор в поле ввода аргумента мастера функции, а затем еще раз нажать на кнопку вызова мастера функции.

РАБОТА С ТАБЛИЧНЫМИ БАЗАМИ ДАННЫХ В MS EXSEL

Список – набор строк таблицы, содержащий связанные данные. (список жильцов дома)

Список может использоваться как база данных, в которой строки – записи, столбцы – поля, при этом 1-я строка – заголовки столбцов (имена полей) (название столбцов.

Правила оформления списка

Можно использовать только 1 список на 1 листе
Подобные объекты надо помещать в один столбец (фамилии под фамилией)
Между списком и другими данными на листе надо оставлять 1 пустую строку и 1 пустой столбец
Важные данные располагать сверху и снизу от списка
В списке не должно быть полностью пустых строчек и полностью пустых столбцов
Для подписей столбцов надо использовать особой форматирование ( курсив, подчеркнуть)

Возможности анализа данных

Новую информацию в список удобно вносить с помощью формы

Форму можно использовать и для добавления, изменения и удаления строк в списке

Форма – диалоговое окно, в котором заголовки столбцов отображаются в виде подписей.

Для того чтобы вызвать форму для работы со списком нужно:

Добавить кнопку «ФОРМА» на панель быстрого доступа (доля этого нужно в вернем левом углу экрана нажать кнопку со стрелочкой в открывшемся диалогом окне выбрать команды_ все команды_форма_добавить _ОК)
Поставить указатель ячейки на любое место списка, нажать кноупку форма и быстро вызвать

Или

Иногда при работе с большими списками удобно закрепить постоянно на экране несколько верхних строк и несколько левых столбцов

Например: список

ФИО	1-й забег	2-й забег	3-й забег	4-й забег	5-й забег

Для этого нужно поставить указатель ячейки справа и ниже закрепляемых строк и столбцов, на вкладке ВИД в группе ОКНО нажать кнопку или свернутый список ЗАКРЕПИТЬ ОБЛАСТИ и выбрать 1 из 3 вариантов:

Если нужно закрепить только 1 верхнюю сточку – выбираем пункт ЗАКРЕПИТЬ СТРОКУ
Если нужно закрепить только 1 левый столбец– выбираем пункт ЗАКРЕПИТЬ СТОЛБЕЦ
Если несколько строк и столбцов – ЗАКРЕПИТ ОБЛАСТИ

Чтобы снять – надо поставить указатель на любое место списка и в том же меню ВИД_ОКНО,ЗАКРЕПИТЬ ОБЛАСТИ выбрать СНЯТЬ ЗАКРЕПЛЕНИЕ ОБЛАСТЕЙ.

СОРТИРОВКА ДАННЫХ СПИСКА – это порядок расположения данных

В списках используется сортировка строк, столбцы остаются на месте.

По умолчанию под сортировкой подразумевается расположение строк в порядку возрастания, если текст – по алфавиту, если числа и текст – числа потом текст

Для того чтобы отсортировать текст нужно:

Поставить указатель ячейки на список
В меню ДАННЫЕ выбрать группу СОРТИРОВКА и нажать кнопку СОРТИРОВКА, появиться диалоговое окно СОРТИРОВКА, в котором нужно указать:
- Столбец в котором осуществляется сортировка, одновременно можно осуществить сортировку до 7 столбцов, для этого надо нажать кнопку ДОБАВИТЬ УРОВЕНЬ

Для того чтобы при сортировке списка заголовки столбцов оставались на месте надо поставить галочку в опции МОИ ДАННЫЕ СОДЕРЖАТ ЗАГОЛОВКИ.

Для того чтобы отсортировать список в обратном порядке (от А до Я, от большего к меньшему) – нужно выбрать в списке порядок строку от А до Я.

Если надо создать нестандартный порядок сортировки то он называется ПОЛЬЗОВАТЛЬСКИМ. Их Есть несколько – по дням недели и по месяцам

Для создания своего ( мастер спорта, канн. в мастера спорта, 1,2,3 р.) нужно…

Использование фильтров для анализа данных

Фильтрация данных = это способ выделения подмножества данных по заданному критерию.

ЕХ есть 2 вида фильтров:

Автофильстры для простых случаев( для работы с ним нужно :
- Поставить 3казатель ячейки на любую ячейку списка
  - Меню данные группа СОРТИРОВКА И ФИЛЬТР кнопка ФИЛЬТР после чего загаоловкас столбцов списка будут добавлены кнопки свернутого списка (треуголдьники). Для сортировки по определенному условию нужно нажать эту свернутую кнопку автофильтра возле того поля по которому осуществляется фильтрация данных (по разряду).
  - В открывшемся списке нужно указать критерий или условия фильтрации. Фильтр возможен по :
    - По значению, которое встречается в столбуце (только КМС)
    - Можно выбирать число или % наименьших и наибольших элементов (5 лучших в забеге или 10%)
    - Каждый тип данных поля имеет свои условия фильтрации :
      - Для текстовых нужно выбрать все записи или строки , которые начинаются с определенных символов, кончаются или содержат
      - Для числовых полей надо показать все записи которые больше или меньше определенного значения, больше или меньше среднего, выше или ниже среднего
      - Для столбцов с датой. Даты можно фильтровать по значению, до и после опред. Даты, на прошлой неделе, в прошлом месяце.

Для того чтобы снять фильтр нужно в поле на которое накладываются условия в кнопке свернутого списка, где прорисован фильтр и выбрать ОТОБРАЗИТЬ ВСЕ, после чего в меню ДАНННЫЕ группа СОРТИРОВКА_ФИЛЬТР нажать ФИЛЬТР и полностью снять автофильтр.

Если необходимо отфильтровать список по 2 условиям для одного и того же столбца необходимо после нажатия кнопки автофильтр возле нужного поля выбрать пункт НАСТРАИВАЕМЫЙ ФИЛЬТР и в открывшемся окне указать каким условиям связаны 2 или 3 критерия (И, ИЛИ) (МС и КМС – «И»)

Расширенные (усиленные) фильтры – применяется при более сложных условиях фильтрации по более сложным критериям

Для этого нужно: предварительно создать диапазон условий . Вид его зависит от вида условия. Имеется 2 вида :

Не вычисляемые условия (все старше 25, все КМС)
Вычисляемые – (выше среднего, а средний надо вычислить)

Для этого надо в меню ДАННЫЕ в группе СОРТИРОВКА И ФИЛЬТР, кнопка ДОПОЛНИТЕЛЬНО, пункт РАСШИРЕННЫЙ ФИЛЬТР, в открывшемся диалоговом окне надо указать диапазон условий фильтрации и место расположения отфильтрованных значений.пункт ПОМЕСТИТЬ РЕЗУЛЬТАТ В ДИАПАЗОН.

8.11.13

ФУНКЦИИ РАБОТЫ С БАЗАМИ ДАННЫХ

БД-функции, они Имеют 3 параметра: список, столбец, критерий.

Для того чтобы использовать любую функцию БД (БД сумм, Б, б счета, Дмин, Дмакс) необходимо:

Выше или ниже списка (или на другом листе) создать диапазон условий. Способ создания диапазона условий зависит от того, вычисляемое или не вычисляемое условие будет использовано:
- При создании не вычисляемого условия диапазон условий создается:
  - Из списка копируются заголовки столбцов на которые накладываются условия
  - Под скопированными заголовками пишутся сами условия
  - Если используются несколько условий (посчитать средний оклад менеджера и начальника или суммарный оклад, или кол-во сотрудников старше 35 в отделе снабжения) по подпунктам
  - Если условия связаны логическим «и», их пишут в одну строку (РИСУНОК)
  - Если условия связаны логическим «или» - их пишут на разных строчках (минимальный возраст работников отдела снабжения и реализации); условие – отдел снабжения ИЛИ реализации, Определить мин. возраст женщин в организации и (ИЛИ) работников АХЧ (пишем на разных строчках)
- При использовании вычисляемых условий – диапазон условий создается (посчитать кол-во работников организации выше среднего возраста) – условие ВЫШЕ СРЕДНЕГО; посчитать кол-во сотрудников с минимальной зарплатой – условие МИН ЗАРПЛАТА: Для создания диапазона выч. условий нужно:
  - Создать заголовки этого вычисляемого списка, которые должны отличаться от заголовков списка – для этого в заголовок добавляем 1….или 2…
  - Ссылки на ячейки находящиеся вне списка должны быть абсолютными ($)
  - Ссылки на ячейки внутри списка должны быть относительными кроме диапазона, диапазоны списка должны обозначаться абсолютными ссылками
  - Вычисляемое условие должно начинаться с = затем идет относительная ссылка на 1-ю ячейку в списка данного столбца и условие
Поставить указатель ячейки на список и вызвать необходимую функцию работы с БД посчитать кол-во работников старше 35 лет – БД сред знач, если кол-во работников с мин. окладом – Б счета, условие мин оклад.)
Функции БД имеют 3 параметра (диапазон всего списка – протягиванием мыши), столбец на котором производятся вычисления (кол-во работников с мин. окладом – условие мин оклад, столбец – фамилия), созданный нами диапазон условий

ФУНКЦИИ ДАТЫ ВРЕМЕНИ

В экселе под датой, независимо от его формата, хранится число дней прошедших с 1-го января 1900 года

дата	число
1.01.1900	1
1.02.1900	32
8.11.2013	365х112+10х30,5+8=

Для того, чтобы проверить используем функцию СЕГОДНЯ, потом поменять фомат на цифровой

Время соответствует десятичной дроби (т.е. дробной части …0,5=12часов; …0, 25=6часов…

Работа с датами подразумевает работу с числами – если из одной даты вычесть другую, то получим разницу в днях.

Название дня недели – дата и формат

Формат бывает:

ДДД – буквенно
ДДДД – полное название
МММ – сокращенное название месяца
ММММ - 4-хбуквенное
ЕЕЕ – 2-буквенный год
ЕЕЕЕ- 4-х буквенный год
Д- день месяца в виде от 1 до 31

15.11.13

Использование функции категория ссылки и массива (ссылки и подстановки)

Они предназначены для поиска:

Поиск позиции ПОИСКПОЗ , имеет 3 параметра:
- Искомое значение – то что мы ищем
- Просматриваемый диапазон – где ищем (абсолютные ссылки)
- Тип сопоставления (-1,0,1) и он указывает принцип поиска:
  - =1 или не указан, то в просматриваемом диапазоне ищется ближайшее значение которое меньше или равно искомому и определяется его позицию в этом диапазоне, при этом диапазон должен быть отсортирован по возрастанию

Задача

7чел пробежали 100м…..найти кто пробежал после 11

Записать в порядке возрастания по времени (значению)
Искомое значение11
Просматриваемый диапазон $В1? D4
- =0 ищет точное совпадение, просматриваемый диапазон не нужно сортировать, если результат не найден, нет ответа
  - = -1 то ф-я ПОИСК ПОЗИЦИИ ищет ближайшее значение больше или равно искомому, диапазон надо отсортировать по убыванию

Функция ВП (ветрикальный просмотр) – ищет значение у крайнелевом столбце и возвращает соответствующее ему значение из указанного столбца, имеет 4 параметра:
- Искомое значение – значение, которое должно быть найдено в первом столбце таблицы(текст, число, ссылка на ячейку)
- Диапазон списка (список где осуществляется выбор данных, 1-й столбец тот в котором вы ищете)
- Номер столбца (№, значение из которого должно быть возвращено)
Интервальный просмотр (логическое значение, которое предъявляет тип соответствия – точное или приблизительное),
- если ложь – ВП ищет точное совпадение,
- если «истина» или пропущен, то в случае отсутствия точного совпадения, возвращается в ближайшее значение меньше искомого , в этом случае значения в первом столбце должны быть отсортированы по возрастанию
ГП – аналогично АП, но рассматривает массив горизонтальным столбцом, т.е значения ищутся в первой строке и соответствующее значение находится из заданного номера строки

6/12/2013

Структуризация таблиц

- позволяет придать списку определенную структуру и иерархию, имеющую до 8 уровней вложения.

Дл добавления в список промежуточных итогов т.е.для стуктуризации надо:

Определить по какому столбцу будут добавляться промежуточные итоги

1Например добавить промежуточное значение, определив кол-во сотрудников в каждом отделе – подведение итогов осуществляется по столбцу – ОТДЕЛ

2Посчитать суммарный оклад работников по должности - ДОЛЖНОСТЬ

Осуществить сортировку списка по тому столбцу по которому осуществляется подведение итогов

по ОТДЕЛ
по ДОЛЖНОСТЬ

после того как список отсортирован поставить указатель ячейки на список , затем на вкладке ДАННЫЕ, группа СТРУКТУРА нажать кнопку ПРОМЕЖУТОЧНЫК ИТОГИ, после чего откроется диалоговое окно ПРОМЕЖУТОЧНЫК ИТОГИ
заполнение диалогового окна ПРОМЕЖУТОЧНЫК ИТОГИ
- в поле ПРИ КАЖДОМ ИЗМЕНЕНИИИ В указать наименование столбца при изменении значения в котором необходимо подводить итоги т.е столбец по которому осуществлялась сортировка
- в свернутом списке ОПЕРАЦИЯ выбрать функцию и операцию, с помощью которой будут подводиться тоги (СУММА, КО_ВО, СРЕДНЕЕ, МИН, МАКС, ОТКЛОНЕНИЯ, ДИСПЕРСИЯ
В поле ДОБАВИТЬ ИТОГ ПО указать столбец по которому осуществляется операция (суммирование, кол-во и т.д). все функции применяются только к числовым полям кроме функции КОЛ-ВО, которая применяется к любому полю т.к. подсчитывает количество строчек.

1 – операция КОЛ-ВО и применить к полю ФАМИЛИЯ или любому заполненному

2 – операция СУММИРОВАНИЕ и применить к полю ОКЛАД

Установить опции ИТОГИ ПОД ДАННЫМИ , в том случае если итоговую операцию надо разместить под списком, ниже каждой группы, по умолчанию – итоговые функции размещаются над каждой группой.
Установить опцию КОНЕЦ СТРАНИЦЫ МЕЖДУ ГРУППАМИ если необходима печать даного списка и каждую группу нужно начинать с нового листа
Опцию ЗАМЕНИТЬ ТЕКУЩИЕ ИТОГИ включить если создаются новые вычисления в списке вместо уже имеющихся. Если НОВЫЕ ИТОГИ добавляются к уже имеющимся, то эту опцию надо выключить

РАБОТА С СГРУПИРРОВАННЫМИ ДАННЫМИ

Можно формировать различные структуры документа с различными вложенностями . Для этого используются знаки + и – напротив указания уровней иерархии, которые добавляются в список.

РЕДАКТИРОВАНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ

Очень часто возникает необходимость применения нескольких итоговых формул или нескольких группировок, к одному столбцу или к разным столбцам.

Например. Необходимо посчитать средний оклад по каждому отделу и одновременно ср. оклад по каждой должности в каждом отделе. Для этого надо:

Поставить указатель на любое место списка
Если необходимо применить несколько итоговых формул одновременно к одному столбцу т.е. посчитать ср. оклад по отделу, суммарный и кол-во сотрудников в отделе. В это случае надо вызвать окно ПРОМЕЖУТОЧНЫЕ ИТОГИ столько раз сколько операций, убирая флажок ЗАМЕНИТЬ ТЕКУЩИЕ ИТОГИ И ЗАМЕНИТь….
Если необходимо применить несколько итоговых формул одновременно к разным столбцам :
- Последовательно осуществить сортировку по всем полям по которым надо подводить итоги ( по отделу и по должности)
- Затем вызвать ПРОМЕЖУТОЧНЫЕ ИТОГИ, первый раз для того столбца, по которому будет первый уровень группировки ( 1- отдел), приэтом первые итоги – ВНЕШНИЕ, остальные - ВЛОЖЕННЫЕ
- Для того чтобы удалить промежуточные итоги из списка надо вызвать ПРОМЕЖУТОЧНЫЕ ИТОГИ (данные группа, структура, промежуточные итоги) и нажать кнопку УБРАТЬ ВСЕ.

СОЗДАНИЕ ДИАГРАММ ПО ПРОМЕЖУТОЧНЫМ ИТОГАМ.

Для этого нужно

добавить в список ПРОМЕЖУТОЧНЫЕ ИТОГИ
затем используя левые кнопки структуры ПРОМЕЖУТОЧНЫХ ИТОГОВ отобразить тот список по которому будет создаваться диаграмма, т.е. свернуть
вставка, диаграмма, вызвать ДИАГРАММА и построить промежуточную диаграмму.

АНАЛИТИЧЕСКАЯ ОБРАБОТКА ДАННЫХ С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ

Сводные таблицы – вспомогательные таблицы, которые содержат часть анализируемых данных с наиболее удобным образом отображенными зависимостями между этими данными.

Их можно создавать на основе 1 или нескольких массивов и на основании внешних данных.

На основе списка:

указать ячейки на любом месте списка
ВСТАВИТЬ – гр. таблицы- сводная таблица( раскрыть свернутый список созданный сводной таблицей:
- Нужно убедиться, что правильно обозначен диапазон списка
- Указать место размещения сводной таблицы
- Сост. Пакета СТ: слева макет, справа окно списка пакета СТ

Необходимо добавить столбцы списка в созданный макет для этого :

В списке полей СТ поставить галочки

По умолчанию для нечисловых полей добавить в область названий строк, числовые в область значений дата/время – название столбцов.

Щелкнуть правой клавишей по этом у полю СТ , добавить фильтр, значение, название
Перетащить вручную

В каждую область несколько полей.

Работа со сводной таблицей

После создания СТ надо ее реорганизовать в соответствии с имеющимися потребностями*

Поля помещенные в область строк и столбцов отображать как все так и выборочно. Чтобы выбрать элементы надо открыть свернутый список рядом с выбранным макетом и те поля, которые помещены в область фильтра они отображают толь 1 элемент своего поля или все элементы.
Можно реорганизовать сводную таблицу (ее ориентацию).

Для реорганизации необходимо изменить область расположения полей в макете сводной таблицы:

Переставить поля за заголовки
Использовать область списка полей СТ

Если нет списка область полей СТ –левая кнопка- показать список полей. По умолчанию, когда создается СТ все элементы полей в которой отображаются, но их можно сортировать и фильтровать.

Группировка значений: элементы полей, помещенных в область строк или столбцов можно группировать (особенно для дата/время).

Правая клавиша мыши щелкнуть по названию поля и выбрать ГРУППИРОВКА.

Вычисления в сводных таблицах (СТ) – В числовых полях, которые помещаются в область значений Exel по умолчанию применить Сумма. Для текстовых полей в области значений автомат – КОЛЛИЧЕСТВО. Чтобы поменять функцію : среднее, сумма, минимум, максимум нужно правой клавишей щелкнуть – ПАРАМЕТРЫ ПОЛЯ, можно одновременно поместить в область значений несколько копий или одно поле к котрому приманять разные операции. Для этого нужно поместить в область сталько, сколько нужно и поменять операции.

1. Машинний переклад
2. Управленческие решения в коммуникационном процессе
3. Контрольная работа- ldquo;Геологическое строение Сибирской платформы и ее западного складчатого обрамленияrdqu
4. Происхождение и развитие солнечной системы
5. Проблемы пространственных представлений у детей дошкольного возраста с общим недоразвитием речи в психолого-педагогических исследованиях
6. Лабораторная работа 6
7. Разработка модуля для контура управления tic-104
8. Тема- Розробка вебсайту засобами мови HTML та текстового редактора Блокнот
9. 1 Понятие и сущность инновационной деятельности 1
10. Управление ресурсами предприяти
11. В тот день на берегу моря Зыбин всётаки достал краба
12. Правовое регулирование гражданства Республики Беларусь
13. Тема- Генезис политических идей в истории цивилизации
14. Догосударственный период в истории Рус
15. 20 2025 2530 Кількість використаних методик ~ 2
16. Тема 1 Вступ Загальні питання ОП
17. В чём был прав и в чём заблуждался Раскольников
18. Тема нашего времени
19. Как избавиться от речей Марк Твен Как избавиться от речей OCR Александр Шереметьев http---www
20. Закон РФ о государственной тайне

Материалы собраны группой SamZan и находятся в свободном доступе