Формирование итогов в списках по заданным условиям
Команда Итоги производит автоматический расчет итогов по всем строкам списка для одинаковых значений поля группировки. Чтобы получигь итоги только для тех строк или ячеек таблицы, которые удовлетворяют определенным условиям, следует использовать специальные функции EXCEL, а также функции баз данных. Первую группу функций применяют при формировании итогов по одному критерию, заданному в виде константы, а вторую — при расчете итогов по комплексному критерию фильтрации строк списка.
Решение подобных задач осуществляется с помощью следующих функций EXCEL:
СУММЕСЛИ(диапазон;условие;диапазон_суммирования);
СЧЕТЕСЛИ(диапазон;условие);
где диапазон - фрагмент списка, который оценивается на критерий;
условие - задастся в виде числового выражения с использованием логических операторов =, >, >= и т.п. или в виде текстовой константы;
диапазон_суммирования — задает интервал ячеек списка для формирования итогов.
В качестве примера рассчитаем количество компьютеров в прайс-листе, цена которых не превышает $700 (таблица 3). Для этого в ячейку А1 (рисунок 6) введем следующую формулу: =СЧЕТЕСЛИ(G6:G15,"<700") или =СЧЕТЕСЛИ(Цена,"<700"), при выполнении которой получим число 6, что соответствует значениям прайс-листа (таблица 3).
При выполнении расчетов часто используют также группу функций баз данных, которые позволяют формировать итоги по комплексным критериям. К ним относятся следующие функции:
БДСУММ, БДДИСП(), БСЧЕТ( ), БСЧЕТА( ), БИЗВЛЕЧЬ( ), ДМАКС( ), ДСРЗНАЧ(), ДСТАНДОТКЛ( ) и другие.
Практически каждая из функций баз данных, является аналогом обычной статистической функции, например, функция БДСУММ( ) — это аналог функции СУММ( ), а функция ДСТАНДОТКЛ( ) — аналог СТАНДОТКЛОН( ). Различие между функциями баз данных и их аналогами состоит в том, что первые оперируют только теми данными списка, которые удовлетворяют заданным условиям. Формат функций баз данных следующий:
Функция БД(Фрагмент БД, Поле, Критерий),
где Фрагмент БД - это адрес области списка или весь список, для которого выполняется расчет;
Поле - имя столбца или адрес столбца, по которому ведется расчет;
Критерий — адрес области критериев, где хранятся условия фильтрации списка (часть списка, или предварительно построенная область подобно Диапазону условий в команде Расширенный фильтр).
Например, чтобы рассчитать среднюю цену компьютеров с размером Cache-памяти в 512 Kb по значениям прайс-листа, приведенного в таблице 3, можно воспользоваться функцией ДСРЗНАЧ( ), для чего предварительно построить область критериев с заголовком Cache и значением 512. Если область критериев размещается по адресу В1:В2 и ей присвоено имя блока Критерий, а фрагмент исходного списка занимает ячейки блока В5:G15 (таблица 3), которому присвоено имя Фрагмент, то аргументы функции ДСРЗНАЧ( ) должны быть заданы следующим образом: ДСРЗНАЧ(Фрагмент;G5;Критерий).[4] В результате расчета в месте расположения функции получим значение 872,8.
Для расчета максимальной (минимальной) цены компьютеров типа Pentium 200 ММХ по прайс-листу в таблице 3 можно использовать функцию ДМАКС( ), предварительно разместив в ячейке C1 заголовок области критериев Тип, а в ячейке С2 — значение Pentium 200 ММХ. Если блоку ячеек С1:С2 присвоить имя Критерий2, а блоку ячеек исходного списка А5:G15 - имя БД, то можно обратиться к функции: ДМАКС(БД;G5;Критерий2). При этом получится значение 730.
Отметим, что в области критериев функций баз данных можно использовать несколько условий, связанных операциями И либо ИЛИ. Например, необходимо определить цену компьютера (таблица 11) с характеристиками, заданными в ячейках блока C1:E2, которому присвоено имя Критерий З (таблица 17):
Таблица 17 – Критерий отбора
| Тип | HDD | Cache |
| Pentium 166 ММХ | >1000 | >256 |
Для решения этой задачи следует воспользоваться функцией БИЗВЛЕЧЬ(), которая возвращает значение ячейки в столбце, удовлетворяющее нескольким условиям, заданным в области критериев. Функция БИЗВЛЕЧЬ(БД;G5; Критерий3) для нашего примера дает следующий результат - 674.
Решение задач бизнес-анализа средствами аппарата сводных
таблиц MS Excel
Сводная таблица — удобный и мощный инструмент обработки больших списков (баз данных), позволяющий автоматически подводить итоги, а также выполнять сортировку и фильтрацию списков.
Рассмотрим построение сводной таблицы на примере формирования Ведомости зарплаты за полугодие на основе Ведомостей зарплаты сотрудников за 1-й и 2-й кварталы. Прежде всего необходимо разместить исходные ведомости на одном рабочем листе EXCEL, как этo представлено в таблице 18.[5]
Таблица 18 – Подготовка списка для создания сводной ведомости за полугодие
| Ф.И.О. | Всего начислено, руб. | Всего удержано, руб. | Сумма к выдач, руб.е | Отдел | Дата |
| Иванов И.А. | 4 500 | 487,80 | 4 012,20 | 1 кв 1998 | |
| Малаев В.П. | 3 900 | 441,12 | 3 458,88 | 1 кв 1998 | |
| Климов Ф.Ф. Ф. | 4 000 | 484,60 | 3 515,40 | 1 кв 1998 | |
| Иванов И.А. | 7 700 | 899,96 | 6 800,04 | 2 кв 1998 | |
| Малаев В.П. | 6 100 | 724,48 | 5 375,52 | 2 кв 1998 | |
| Климов Ф.Ф. | 4 800 | 587,64 | 4 212,36 | 2 кв 1998 |
В меню Данные следует выбрать команду Сводная таблица, по которой на экран выводится окно Мастер сводных таблиц для задания области обрабатываемых данных, в нашем примере - это весь список.
 |
На следующем шаге формируется макет итогового документа из полей исходного списка - макет сводной таблицы, диалоговое окно которого применительно к описанному выше примера приведено на рисунке 35.
Рисунок 35 – Образец подготовки списка для создания сводной ведомости
за полугодие в MS Excel
Макет сводной таблицы содержит 4 области (рисунок 7):
• Страница - обеспечивает фильтрацию данных сводной таблицы по значениям выбранных полей, может содержать О-N полей списка;
• Строка, Столбец - обеспечивают группирование строк и столбцов списка для вычисления итогов (О-N полей списка);
• Данные - обязательная область макета; содержит произвольное число полей, не включенных в другие области. Одно и то же поле списка может быть многократно размещено в области данных, если для него нужны разные виды итогов (сумма, среднее значение и т.д.).
Порядок следования полей в областях Страница, Строка, Столбец определяет иерархию группирования данных и формируемых итогов.
На последнем шаге определяется место размещения результатов построения сводной таблицы. Конечный результат формирования Ведомости зарплаты за полугодие приведен в таблице 19.
Таблица 19 – Сводная ведомость зарплаты за полугодие
| Ф.И.О. | Данные | Дата | ||
| 1 кв 1998 | 2 кв 1998 | Общий итог, руб. | ||
| Иванов И.А. | Сумма по полю Всего начислено | 4 500,00 | 7 700,00 | 12 200,00 |
| Сумма по полю Всего удержано | 487,80 | 899,96 | 1 387,76 | |
| Сумма по полю Сумма к выдаче | 4 012,20 | 6 800,04 | 10 812,24 | |
| Климов Ф.Ф. | Сумма по полю Всего начислено | 4 000,00 | 4 800,00 | 8 800,00 |
| Сумма по полю Всего удержано | 484,60 | 587,64 | 1 072,24 | |
| Сумма по полю Сумма к выдаче | 3 515,40 | 4 212,36 | 7 727,76 | |
| Малаев В.П. | Сумма по полю Всего начислено | 3 900,00 | 6 100,00 | 10 000,00 |
| Сумма по полю Всего удержано | 441,12 | 724,48 | 1 165,60 | |
| Сумма по полю Сумма к выдаче | 3 458,88 | 5 375,52 | 8 834,40 | |
| Итог Сумма по полю Всего начислено | 12 400,00 | 18 600,00 | ||
| Итог Сумма по полю Всего удержано | 1413,52 | 2212,08 | 3625,6 | |
| Итог Сумма по полю Сумма к выдаче | 10986,48 | 16387,92 | 27374,4 |
При подведении итогов можно объединять данные в группы в зависимости от типа данных разными способами:
• группировать выделенные элементы по выбранным категориям;
• автоматически группировать числовые элементы;
• автоматически группировать даты и время в более крупные единицы времени, например, дни, месяцы, кварталы, годы.
Можно создавать подгруппы в группах, например, сгруппировать даты по месяцам, а затем месяцы в кварталы.
Для группировки данных следует выделить элементы, которые группируются, и воспользоваться кнопкой Группировка на панели инструментов Сводные таблицы. Например, для объединения сотрудников по отделам и получения дополнительных итогов для предыдущего примера (таблица 18) следует в построенной сводной таблице выделить требуемые фамилии, а затем выполнить группировку описанным способом.
С помощью мастера сводных таблиц можно также консолидировать данные из таблиц, расположенных на разных рабочих листах EXCEL. Непременным условием такой консолидации является единая структура таблиц. При этом каждая таблица должна содержать данные одного временного (или другого типа) диапазона. Например, ведомости зарплаты за каждый квартал 1998 года сформированы на разных рабочих листах EXCEL с именами 1кв-98, 2 кв-98, 3 кв-98, 4 кв-98. Для построения сводной ведомости за весь 1998 год не обязательно переносить их на один рабочий лист EXCEL, а можно воспользоваться средством консолидации мастера сводных таблиц, для чего выполнить следующие действия:
- в команде Сводная таблица установить флажок в нескольких диапазонах консолидации;
- в режиме переключателя Создать одно поле страницы указать диапазон каждой консолидируемой таблицы, включая в него заголовки, кроме итоговых строк и столбцов;
- сформировать макет итогового документа,[6] выбирая для расчетов требуемые функции.
Таблица 20 - Образец сводной ведомости зарплаты за 1998 год в MS Excel
| (Все) | |||
| Сумма по полю Значение | Столбец | ||
| Ф.И.0. | Начислено | Удержано | К выдаче |
| Иванов И.А | 2763,93 | 21546,07 | |
| КлимовФ.Ф. | 2505,43 | 18134,57 | |
| Малаев В.П. | 2587,51 | 19402,49 | |
| Общий итог | 3625,6 | 27374,4 |
Результат формирования Ведомости зарплаты за 1998 год методом консолидации диапазонов, расположенных на листах 1кв-98, 2 кв-98, 3 кв-98, 4 кв-98, приведен на рисунке 38.
По списку (Все) можно получить сведения по каждому кварталу.
Варианты заданий для самостоятельной работы