Подведение итогов в списках

Для проведения вычислений над данными списка, которые удовлетворяют заданным условиям, предназначены функции работы с базой данных. Функция БИЗВЛЕЧЬ извлекает отдельное значение из столбца списка, удовлетворяющее заданным условиям, остальные функции данной категории имеют аналоги в других категориях: БДСУММ à СУММ, ДСРЗНАЧ à СРЗНАЧ, ДМИН à МИН, ДМАКС à МАКС, БДСЧЁТ à СЧЁТ, БДСЧЁТА à СЧЁТЗ, БДПРОИЗВЕД à ПРОИЗВЕД и др. В отличие от аналогов эти функции имеют три аргумента:

1) База данных — весь диапазон списка, содержащий строку с названиями полей.

2) Поле — заголовок в кавычках, порядковый номер поля или адрес ячейки с именем поля, по которому следует вычислить итог.

3) Критерий — адрес диапазона, содержащего условия. Правила создания диапазона условий такие же, как для расширенного фильтра.

Подведение итогов по группам записей начинается с сортировки по тем полям, для которых требуется получить итоговые значения. Затем выполняется команда Данные—Итоги. В появившемся диалоге Промежуточные итоги (рис. 6.6-б) в списке При каждом изменении в выбирается поле, по которому проводилась сортировка. Указывается также функция, с помощью которой вычисляются итоги, и поле, к которому она применяется.

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

Перед созданием сводной таблицы из списка убирают все промежуточные итоги и фильтры. Затем выполняют команду Данные—Сводная таблица и заполняют диалоговые окна Мастера сводных таблиц. На третьем шаге мастера нужно нажать кнопку Макет… и в открывшемся окне перетащить кнопки соответствующих полей в нужные области сводной таблицы (рис. 6.8). После окончания работы мастера будет создана сводная таблица и на экране появится панель инструментов Сводные таблицы (рис. 6.1).

Рис. 6.1. Кнопки панели инструментов Сводные таблицы

1. Мастер сводных таблиц — открывает шаги мастера для изменения диапазона исходного списка и макета сводной таблицы.

2. Скрыть/Отобразить детали

3. Разгруппировать/Группировать — используется для группировки данных поля, например, поля, содержащего даты — по месяцам и кварталам.

4. Обновить данные — обновляет сводную таблицу, если изменились данные, по которым она была построена.

5. Отобразить страницы — позволяет создать сводные таблицы на отдельных листах для всех значений поля, размещенного в области Страница.

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

7. Список полей — отображает или скрывает окно списка полей, с помощью которого можно перестраивать сводную таблицу непосредственно на рабочем листе.

Рис. 6.2. Изменение параметров поля сводной таблицы

Примеры

Пример 6.1. Из списка (рис. 6.3) отобрать с помощью расширенного фильтра записи о работах подряда № 25020601, на которые затрачено от 10 до 16 часов.

Рис. 6.3. Список исполнителей и выполненных работ

Выполнение:

Создать диапазон условий — А16:С17 (рис. 6.5). Поле «Кол-во часов» добавлено дважды, чтобы задать интервал от 10 до 16, т.е. требуется одновременное выполнение двух условий >=10 и <=16.
Выполнить команду Данные—Фильтр—Расширенный фильтр и заполнить диалог (рис. 6.4):

Рис. 6.4. Заполнение диалога расширенного фильтра

После нажатия ОК в ячейках А19:F23 (рис. 6.5) будут выведены записи, удовлетворяющие заданным условиям.

Рис. 6.5. Результаты решения примеров 6.1 и 6.2

Пример 6.2. С помощью функции работы с базой данных вычислить среднее количество часов по работам, шифр которых начинается на «Ш».

Выполнение:

Создать диапазон условий — Е16:E17 (рис. 6.5).
В ячейку F17 выполнить вставку функции ДСРЗНАЧ.
Заполнить аргументы функции:

– База данных à A1:F14;

– Поле à C1;

– Критерий à E16:E17.

Результат и текст формулы в строке формул приведены на рис. 6.5.

Пример 6.3. Подвести итоги оплаченной суммы по каждому исполнителю.

Выполнение:

Выполнить сортировку по полю «Испол-нитель» (рис. 6.6-а). В результате список будет упорядочен по фамилиям исполнителей.
Выполнить команду Данные—Итоги и заполнить диалог (рис. 6.6-б).

А) б)

Рис. 6.6. Сортировка и подведение промежуточных итогов в списке

После нажатия ОК под группой записей для каждой фамилии появится строка с вычисленным итогом, а слева от заголовков строк — уровни структуры. Щелкая по этим кнопкам можно скрывать и раскрывать детальные данные (рис. 6.7).

Рис. 6.7. Результат выполнения примера 6.3

Пример 6.4. По данным списка (рис. 6.3) построить сводную таблицу, отражающую сумму к оплате по каждому подряду для каждой профессии.

Выполнение:

Выполнить команду Данные—Сводная таблица и на первом шаге выбрать опцию «в списке или базе данных MS Excel».
На втором шаге указать диапазон списка A1:F14.
На третьем шаге нажать кнопку Макет… и перетащить поля как показано на рис. 6.8.

Рис. 6.8. Построение макета сводной таблицы

После окончания работы мастера будет построена сводная таблица (рис. 6.9). В этой таблице данные получены путем суммирования соответствующих значений, например, 142000 это сумма оплаченных работ по подряду № 18030602, выполненных слесарями (42000 + 50000 + 30000 + 20000).