Прокофьева О. Е. / Excel / СМ 12 / СМ 12 СуммаЕсли по одному или нескольким критериям
.docВыборочные вычисления по одному или нескольким критериям
Постановка задачи
Имеем таблицу по продажам, например, следующего вида:
Менеджер |
Регион |
Стоимость |
Дата |
Петров |
Восток |
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)