Формирование итогов в списках по заданным условиям
Команда Итоги производит автоматический расчет итогов по всем строкам списка для одинаковых значений поля группировки. Чтобы получигь итоги только для тех строк или ячеек таблицы, которые удовлетворяют определенным условиям, следует использовать специальные функции 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.