Выборочные вычисления по одному или нескольким критериям

Постановка задачи

Имеем таблицу по продажам, например, следующего вида:

Менеджер	Регион	Стоимость	Дата
Петров	Восток	7957	01.02.2014
Петров	Центр	5914	24.01.2014
Григорьев	Восток	4813	04.02.2014
Григорьев	Центр	4867	25.03.2014
Григорьев	Восток	5263	24.01.2014
Петров	Запад	8959	30.01.2014
Михайлов	Центр	5539	07.01.2014
Петров	Центр	9863	03.02.2014
Михайлов	Центр	5510	22.02.2014
Лапин	Восток	6272	20.02.2014
Лапин	Центр	7189	29.03.2014
Лапин	Центр	4895	17.01.2014
Григорьев	Центр	8221	23.03.2014
Петров	Восток	8471	23.02.2014
Петров	Запад	6350	19.01.2014
Чадов	Центр	5888	11.03.2014
Григорьев	Центр	4558	20.02.2014
Петров	Центр	9253	28.02.2014
Иванов	Восток	8367	15.01.2014
Григорьев	Центр	9498	10.03.2014
Григорьев	Восток	8499	18.01.2014

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig). Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

Заказчик	Менеджер продаж	Регион	Стоимость продажи
Рамстор	Петров	Восток	7957
Рамстор	Петров	Восток	5914
Копейка	Григорьев	Центр	4813
Копейка	Григорьев	Центр	4867
Метро	Григорьев	Центр	5263
Рамстор	Петров	Восток	8959
Ашан	Михайлов	Запад	5539
Рамстор	Петров	Восток	9863
Ашан	Михайлов	Запад	5510
Метро	Лапин	Центр	6272
Копейка	Лапин	Центр	7189
Метро	Лапин	Центр	4895
Копейка	Григорьев	Центр	8221
Рамстор	Петров	Восток	8471
Рамстор	Петров	Восток	6350
Ашан	Чадов	Запад	5888
Копейка	Григорьев	Центр	4558
Рамстор	Петров	Восток	9253
Рамстор	Иванов	Восток	8367
Метро	Григорьев	Центр	9498
Метро	Григорьев	Центр	8499
Метро	Григорьев	Центр	3880
Рамстор	Петров	Восток	6888
Ашан	Чадов	Запад	5465
Копейка	Григорьев	Центр	5749

• Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.

• Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и  ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий ?????. А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.

• Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2="Копейка")*(B2="Григорьев")

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:

=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM), которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:

=БДСУММ(A1:D26;D1;F1:G2)