Использование имен ячеек и диапазонов в формулах
В ячейке F3 Найдем количество пятерок для первого ученика. Вызовем Мастер функций, в списке Категория выберем Статистические. В списке функций выберем функцию СЧЕТЕСЛИ.
В поле Диапазон введем В3:Е3, в поле Критерий "5".
После нажатия на кнопку OK, в строке формул отобразится формула =СЧЁТЕСЛИ(В3:Е3;5), а в ячейке F3 будет находиться количество пятерок.
В ячейке F4 найдем количество пятерок для второго ученика по формуле =СЧЁТЕСЛИ(В4:Е4;5)
Аналогично найдем количество пятерок для третьего и четвертого учеников.
В ячейке G3 найдем количество четверок для первого ученика по формуле: =СЧЁТЕСЛИ(В3:Е3;4). Аналогично найдем количество четверок для второго , третьего и четвертого учеников.
В ячейке H3 найдем количество троек для первого ученика по формуле: =СЧЁТЕСЛИ(В3:Е3;3). Аналогично найдем количество троек для второго , третьего и четвертого учеников.
В ячейке I3 найдем количество двоек для первого ученика по формуле: =СЧЁТЕСЛИ(В3:Е3;2). Аналогично найдем количество двоек для второго , третьего и четвертого учеников.
В ячейку J3, вставим функцию ЕСЛИ.
Введите:
- в окошке Лог_выражение: I30;
- в окошке Значение_если_истина: неуспевающий;
Щелкните в окошке Значение_если_ложь и вызовите вложенную функцию ЕСЛИ.
Появится чистое диалоговое окно вложенной функции ЕСЛИ, в котором нужно ввести:
- в окошке Лог_выражение: H30;
- в окошке Значение_если_истина: с 3;
- в окошке Значение_если_ложь: вызовите третью функцию ЕСЛИ;
- в окошке Лог_выражение: G30;
- в окошке Значение_если_истина: на 4 и 5;
- в окошке Значение_если_ложь: отличник;
Скопируйте функцию из ячейки Л3в ячейки J4, J5, J6.
В ячейке J3 будет находится формула:
=ЕСЛИ(І30;неуспевающий;ЕСЛИ(Н30;с 3;ЕСЛИ^30;на 4
и 5;отличник)))
В ячейке J7 подсчитаем количество отличников по формуле:
=СЧЁТЕСЛИд3^6;отличник).
В ячейке J8 подсчитаем количество хорошистов по формуле:
=СЧЁТЕСЛИд3^6;на 4 и 5).
В ячейке J9 подсчитаем количество троечников по формуле:
=СЧЁТЕСЛИд3^6;с 3).
В ячейке J10 подсчитаем количество неуспевающих по формуле:
=СЧЁТЕСЛИ^3^6;неуспевающий).
Использование имен ячеек и диапазонов в формулах.
При организации расчетов очень удобно использовать заранее определенные имена ячеек и диапазонов ячеек.
Для того чтобы присвоить имя ячейке или диапазону ячеек следует:
1. Выделить ячейку или диапазон ячеек.
2. Выполнить команду меню Вставка/Имя/Присвоить.
3. Ввести имя создаваемого блока ячеек в строке Имя (автоматически
вводится имя, соответствующее содержимому ячейки,
расположенной над выделенным блоком), в строке Формула выводится ссылка на диапазон выделенных ячеек. Нажать кнопку
ОК.
Имя ячейки или диапазона не может содержать Важно! пробелы. Вместо пробела можно использовать символ
нижнего подчеркивания.
В дальнейшем в формулах можно использовать имена Выиматие' вместо абсолютных адресов ячеек и диапазонов. Для
вызова списка именованных диапазонов следует нажать клавишу F3.
Примеры использования имен в формулах.
ПРИМЕР 10. Расчет премии сотрудникам отдела с использованием имени ячейки
Рассмотрим расчет премии из примера 1.
1. Присвоим ячейке Е1 со значением премии имя Премия. Для этого:
- щелкните на ячейке Е1;
- выполните команду Вставка\Имя\Присвоить;
- в появившемся диалоговом окне (См.рис.12) в окошке Имя введите Премия;
- нажмите кнопку ОК.
2. При расчете величины премии для Сидорова В.И. щелкните в соответствующей ячейке, введите =D4*, затем, вместо ввода значения процента премии, нажмите на клавиатуре клавишу F3.
В появившемся диалоговом окне (См. рис. 13) щелкните на имени Премия.
Нажмите кнопку ОК.
Рис. 12. Диалоговое окно Присвоение имени
Формула примет вид: Б4*Премия. Скопируйте формулу для расчета премии остальным сотрудникам.
Именованная ячейка играет в формулах роль
Важно!
абсолютной ссылки.
Рис. 13. Диалоговое окно Вставка имени
ПРИМЕР 11. Пример расчета среднего значения премии сотрудников отдела с использованием именованного диапазона.
Рассмотрим пример расчета среднего значения премии сотрудников отдела.
1. Выделите диапазон с премиями сотрудников отдела.
2. Присвойте этому диапазону имя Премии_отдела. (Для этого выполните команду Вставка\Имя\ Присвоить.)
3. Щелкните в любой ячейке вне таблицы.
4.
5.
6.
7.
8.
Вызовите Мастер функций, нажав кнопку L_
В категории Статистические щелкните на функции СРЗНАЧ, нажмите кнопку ОК.
Проследите, чтобы в появившемся диалоговом окне (См. рис. 14) курсор находился в окне Число 1.
Нажмите на клавиатуре клавишу F3 и в появившемся списке именованных диапазонов дважды щелкните на имени Премии_отдела.
Нажмите кнопку ОК.
В результате этих действий будет рассчитано среднее значение премий сотрудников отдела. Формула расчета будет выглядеть следующим образом: СРЗНАЧ(Премии_отдела).
Рис. 14. Диалоговое окно функции СРЗНАЧ
Присвоение имени листу.
Для того чтобы присвоить имя листу установите курсор на имени листа (Листі) и нажмите правую кнопку мыши. В появившемся контекстном меню выберите команду Переименовать. Поверх старого имени листа введите новое имя.
Нажмите клавишу Enter.
Создание диаграмм в Excel.
Для создания диаграммы на основе данных таблицы необходимо:
1.
2. 3.
на панели
Вызвать Мастер диаграмм, нажатием кнопки инструментов Стандартная или командой Вставка\Диаграмма.
В появившемся диалоговом окне выбрать тип и вид диаграммы. Нажать кнопку Далее.
Во втором диалоговом окне:
- На вкладке Диапазон данных задать диапазон данных таблицы, подлежащий отображению на диаграмме, и указать вариант представления данных: по строкам или столбцам. (Для облегчения дальнейших настроек мы рекомендуем выделять данные, включая подписи строк и заголовки столбцов).
Несмежные диапазоны данных выделяются при нажатой клавише Ctrl.
Важно!
- На вкладке Ряд в окне Имя следует задать адрес ячейки, из которой формируется легенда для выделенного в окошке Ряд ряда данных. На этой же вкладке в окошке Подписи по оси Х можно задать адреса ячеек, из которых формируются подписи по оси Х.
Нажать кнопку Далее.
Ряд данных - это блок ячеек, содержащий данные, отражающиеся на диаграмме в виде отдельного Важно! графика.
Легенда - это подпись, определяющая соответствие цвета и ряда данных.
4. В третьем диалоговом окне следует задать параметры для оформления диаграммы. Нажать кнопку Далее.
5. В четвертом диалоговом окне следует указать, где будет размещаться диаграмма - на отдельном листе или на том же, что и таблица. Нажать кнопку Готово.
Диаграмма готова. Далее можно отредактировать диаграмму.
К редактированию диаграмм относится: изменение цвета ряда данных, формирование подписей данных рядов данных или их отдельных точек, изменение цвета области данных и т.д.
При редактировании диаграмм рекомендуется соблюдать общую технологию:
1. Щелчком левой кнопки мыши выделить подлежащий редактированию объект диаграммы (область диаграммы, область построения, ряд данных, точку данных, легенду, ось Х или У и т.д.)
2. Щелчком правой кнопки мыши на выделенном объекте вызвать контекстное меню и выбрать из него необходимую команду.
Например:
- для изменения типа предварительно выделенного ряда данных в соответствующем контекстном меню следует выбрать команду Тип диаграммы;
- Для изменения цвета предварительно выделенного ряда данных в соответствующем контекстном меню следует выбрать команду Формат рядов данных. На закладке Вид указать цвет ряда данных.
- Для настройки ряда на вспомогательную ось в соответствующем контекстном меню следует выбрать команду Формат рядов данных. На закладке Ось выбрать опцию по вспомогательной оси.
- Для организации подписи отдельной точки на диаграмме (выделить точку можно в два этапа: первым щелчком мыши выделяется ряд данных, а вторым щелчком - нужная точка) в соответствующем контекстном меню следует выбрать команду Формат точки данных. На закладке Подписи данных выбрать опцию значение.
Часто возникает необходимость добавить данные на уже построенную диаграмму.
Для добавления новых данных_на диаграмму необходимо:
1. выделить всю диаграмму (для этого достаточно щелкнуть один раз на
диаграмме);
2. выполнить команду ДиаграммаЩобавить данные;
3. в появившемся окне задать диапазон ячеек с добавляемыми данными.
Иногда Excel просит уточнить, являются ли новые данные значениями для нового ряда или они являются новыми элементами существующих рядов данных.
ПРИМЕР 12. Пример построения диаграммы.
| Построим диаграмму по следующей таблице: | |||||||||||||||||||||
|
|||||||||||||||||||||
| Табл. 13 Таблица начислений заработной платы сотрудникам отдела |
1. Для построения диаграммы запустите Мастер диаграмм.
2. В первом диалоговом окне укажите тип диаграммы - График. Нажмите кнопку Далее.
3. В следующем диалоговом окне щелкните в окошке Диапазон и выделите всю таблицу, включая заголовки столбцов и фамилии сотрудников.
4. Щелкните на опции Ряды в строках. В этом случае рядом данных будет строка таблицы.
5. Щелкните на закладке Ряд.
6. Проконтролируйте, чтобы в окне Подписи по оси Х был указан диапазон с названиями месяцев.
7. Обратите внимание, что в окошке Имя указана ячейка с фамилией сотрудника, соответствующая выделенному ряду. Нажмите кнопку Далее.
8. В следующем диалоговом окне укажите заголовок диаграммы Заработная плата за 1 квартал, и название оси У: Начислено в руб.. Нажмите кнопку ОК.
9. В следующем окне укажите месторасположение диаграммы. Нажмите кнопку ОК.
Типы диаграмм в Excel.
Обычно тип диаграммы выбирается в зависимости от того, что
именно надо продемонстрировать на диаграмме.
Гистограмма показывает изменение данных за определенный период времени и иллюстрирует соотношение отдельных значений данных.
Диагр. 2. Гистограмма
Линейчатая диаграмма отражает взаимное соотношение отдельных компонентов таблицы. Линейчатая диаграмма представлена на Диагр. 3. Круговая диаграмма характеризует как абсолютную величину каждого элемента ряда данных, так и его вклад в общую сумму.
Диагр. 5. Диаграмма типа График Успеваемость
Диагр. 6. Гистограмма
Отличники
Диагр. 7. Круговая диаграмма
Анализ данных в Excel
Excel имеет ряд инструментов для удобного анализа данных таблиц. К ним относятся сортировка данных, фильтрация данных, сводные таблицы и т. д.
Сортировка данных.
1. Для успешной сортировки данных в Excel рекомендуется проверить, как организованы данные в сортируемой таблице:
- если заголовок каждого из столбцов размещен строго в одной ячейке, расположенной непосредственно над столбцом, то такая таблица может быть отсортирована без предварительной подготовки;
- если заголовок каждого из столбцов размещен в нескольких ячейках, то следует перед первой строкой данных в таблице вставить строку с условными (вспомогательными) заголовками.
2. Для сортировки данных необходимо выделить весь сортируемый диапазон, включая заголовки столбцов (если была вставлена строка с условными заголовками, то выделяется именно она), и выполнить команду Данные\Сортировка. В появившемся диалоговом окне следует указать, по каким полям осуществляется сортировка таблицы.
ПРИМЕР 14. Пример сортировки таблицы.
Приведенная таблица (См. табл.14) может быть отсортирована сначала по виду Продукции, а затем по фамилии Продавца.
Такая сортировка означает, что каждая группа записей с одинаковым видом Продукции будет отсортирована, в свою очередь, по фамилии Продавца.
Для сортировки таблицы нужно
1. выделить таблицу, включая заголовки ее столбцов, и выполнить команду Данные\ Сортировка;
2. в появившемся диалоговом окне (См.рис. 15) в поле Сортировать по следует указать первое поле сортировки - Продукция;
3. в поле Затем по нужно указать Продавец. Нажмите кнопку ОК.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл.14. Фрагмент таблицы, подлежащей сортировке |
| ^^__^^едомостъ^спеваемости10_а_класса_заЛполугодие.і | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Рис. 15. Диалоговое окно Сортировка диапазона ПРИМЕР 15. Пример сортировки таблицы.
Список учеников
Приведенная таблица (См. табл. 15) может быть отсортирована сначала по Результату, а затем по фамилии ученика.
| После сортировки таблица примет вид. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл. 16. Отсортированный список учеников |
Одним из простых инструментов анализа данных является инструмент Промежуточные итоги.
Если требуется определить итоговую выручку, полученную каждым продавцом, то следует:
1. Отсортировать таблицу по полю Продавец.
2. Выполнить команду Данные\Промежуточные итоги.
3. В появившемся диалоговом окне (см. рис. 16) нужно указать:
- При каждом изменении в - Продавец;
- Операция - Сумма;
- Добавить итоги по Выручка в руб..
Поскольку после предварительной сортировки таблицы, записи с одинаковыми фамилиями продавцов стоят рядом, результат суммирования выручки для каждого продавца появится перед группой записей с фамилией другого продавца. (См. рис. 17).
В качестве функций анализа в Промежуточных итогах могут быть использованы функции Среднее, Максимум, Минимум, Количество и др.
Если для дальнейшего анализа требуется подвести ВажНО! промежуточные итоги по каждому из указанных
районов, то следует предварительно отсортировать таблицу по соответствующему полю.
Рис. 16. Диалоговое окно Промежуточные итоги
Рис. 17. Результат расчета Промежуточных итогов
Фильтрация данных.
Очень часто при анализе данных в таблице возникает необходимость анализировать только ту ее часть, данные которой отвечают определенным условиям.
Фильтр - инструмент Excel, позволяющий отобразить на экране только записи, удовлетворяющие определенному условию.
Для успешной фильтрации данных в Excel, так же как и для сортировки, рекомендуется проверить, как организованы заголовки в таблице, и при необходимости вставить строку с условными заголовками столбцов.
В Excel существуют два вида фильтров: автофильтр и расширенный фильтр.
Фильтрация по простым критериям реализуется с помощью автофильтра.
После выделения всех данных таблицы, включая заголовки столбцов, следует выполнить команду Данные\Фильтр\Автофильтр. Около заголовка каждого из столбцов появится стрелка.
Если щелкнуть на стрелке, например, в столбце Продавец (См. рис. 18), то в появившемся окне можно выбрать любую из фамилий, например, фамилию Ивин.
Рис. 18. Таблица с Автофильтром
В результате этих действий в таблице останутся только строки, содержащие фамилию продавца Ивин.
Если затем щелкнуть на стрелке Район и выбрать район Южный, то количество записей уменьшится, т.к. теперь останутся только записи, удовлетворяющие обоим условиям: Продавец = Ивин Район = Южный
Рис. 19. Диалоговое окно Пользовательский автофильтр
В результате этих действий останутся только строки с данными, удовлетворяющими всем указанным условиям.
Условия, задаваемые на значения разных столбцов, Важно1 рассматриваются при фильтрации, как условия,
выполняемые одновременно (их связывают
отношения типа логического И).
Номера отфильтрованных строк приобретают голубой цвет. Строки, не удовлетворяющие критериям фильтрации, не удаляются, а временно скрываются.
Чтобы вернуть на экран все строки таблицы, нужно выполнить команду Данные\Фильтр\Отобразить все в списках условий около каждого заголовка.
Некоторые критерии фильтрации не могут быть описаны средствами автофильтров. В этом случае используют инструмент Расширенный фильтр.
Расширенный фильтр.
Для применения расширенного фильтра необходимо предварительно подготовить т.н. Диапазон условий и Диапазон, в который будут помещены результаты.
Для организации Диапазона условий следует:
¦ в свободную строку вне таблицы скопировать заголовки тех столбцов, на данные которых будут наложены ограничения (заголовки несмежных столбцов могут оказаться рядом);
¦ под каждым из заголовков задать условие отбора данных.
Условия, находящиеся в одной строке Excel Важно! рассматривает, как условия И, условия, находящиеся
в разных строках как условия типа ИЛИ.
Строка копий заголовков вместе с условиями отбора и образуют Диапазон условий.
ПРИМЕР 16. Пример отбора записей с помощью расширенного фильтра
Для табл. 14 приме
ром диапазона условий может служить диапазон:
|
||||||
| Табл. 17. Пример Диапазона условий |
Если же в результатах фильтрации будут фигурировать только данные из определенных столбцов, то заголовки этих столбцов предварительно (до фильтрации) следует скопировать в строку в свободном месте экрана и указать их в окошке Поместить результат в диапазон.
Примером такого диапазона может служить диапазон:
Объем (шт.)
Район
Продукция
Табл.18. Пример диапазона, в который помещается результат
После подготовки указанных диапазонов можно приступать к фильтрации данных. Для этого следует выполнить команду Данные\Фильтр\Расширенный фильтр. В появившемся диалоговом окне необходимо указать:
1. где следует размещать отфильтрованные данные (мы рекомендуем выбрать опцию скопировать результат в другое место);
2. Исходный диапазон - все данные вместе с заголовками столбцов;
3. подготовленные Диапазон условий (табл. 17) и Поместить
результат в диапазон (табл. 18).
При организации расширенного фильтра удобно использовать имена диапазонов и ячеек.
ПРИМЕР 17. Пример формирования условий для применения расширенного фильтра.
Рассмотрим следующую задачу:
В таблице 14 найти записи, соответствующие январю месяцу, имеющие данные по Выручке, большие 1000000 и записи, соответствующие февралю месяцу, имеющие данные по Выручке, большие 150000.
Создадим Диапазон условий. Он будет иметь следующий вид:
|
||||||
| Табл. 19. Пример диапазона условий для примера 17 |
Создание и редактирование сводных таблиц.
Сводная таблица - это таблица, которая используется для анализа данных в таблицах. В сводной таблице автоматически создаются общие и промежуточные итоги. Для таблицы, приведенной в табл. 14, одним из вариантов сводной таблицы является таблица, приведенная на рис. 20.
Рис. 20 Пример сводной таблицы
|
||||||||||||||
Подведение итогов в сводной таблице производится с помощью итоговых функций СУММА, СРЕДНЕЕ ЗНАЧЕНИЕ и др.
Создание сводной таблицы.
Перед созданием сводной таблицы рекомендуется проверить, как организованы заголовки столбцов с данными.
- если заголовок каждого столбца размещен строго в одной ячейке, расположенной непосредственно над столбцом, то такая таблица не требует предварительной подготовки;
- в противном случае, перед первой строкой данных в таблицу следует вставить строку с условными (вспомогательными) заголовками.
Чтобы создать сводную таблицу, нужно выполнить команду Данные\Сводная таблица. Этой командой запускается Мастер сводных таблиц, позволяющий ввести информацию для создания сводной таблицы.
В первом диалоговом окне следует указать, где находятся обрабатываемые данные. В нашем примере данные находятся на одном листе и поэтому надо выбрать опцию Данные находятся в списке или базе данных Microsoft Excel, а затем нажать кнопку Далее.
В следующем диалоговом окне следует указать диапазон, содержащий исходные данные. Для этого необходимо мышью выделить исходную таблицу, включая заголовки столбцов. Если организация таблицы потребовала вставки строки с условными заголовками столбцов, то в качестве исходного диапазона указываются исходные данные вместе с условными заголовками.
В следующем диалоговом окне необходимо указать, где будет размещена сводная таблица - на имеющемся листе или на новом. Мы рекомендуем располагать сводные таблицы на отдельных листах.
После этого нажмите кнопку Макет.
Следующее диалоговое окно позволяет сконструировать макет создаваемой сводной таблицы (см. рис. 21).
Рис. 21 Макет сводной таблицы
На рис. 21 справа расположены т.н. кнопки полей, соответствующие названиям столбцов исходной таблицы.
Для организации сводной таблицы необходимо:
- перетащить с помощью мыши кнопки полей, соответствующие описательным данным таблицы в т.н. область сведения, которая представлена двумя прямоугольниками белого цвета Столбец и Строка. Выбор перетаскиваемых кнопок и их расположение определяются требованиями решаемой задачи.