Microsoft Excel

Предположим что у Вас есть вот такой отчёт по продажам торговых представителей:



Из него Вам необходимо узнать сколько карандашей продал торговый представитель Иванов в январе.



ПРОБЛЕМА: Как суммировать данные по нескольким критериям??

РЕШЕНИЕ: Способ 1:

=БДСУММ(A1:G16;F1;I1:K2)

В английской версии:

Code

=DSUM(A1:G16,F1,I1:K2)

КАК ЭТО РАБОТАЕТ:
 


Из указанной нами базы данных A1:G16 функция БДСУММ извлекает и суммирует данные столбца Количество (аргумент "Поле" = F1) по заданным в ячейках I1:K2 (Продавец = Иванов; Продукция = Карандаши; Месяц = Январь) критериям.



МИНУСЫ: Список критериев должен быть на листе.

ПРИМЕЧАНИЯ: Количество критериев суммирования ограничено оперативной памятью.

ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая версия Excel

Способ 2:

Code

=СУММПРОИЗВ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

В английской версии:

Code

=SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

КАК ЭТО РАБОТАЕТ:

Функция СУММПРОИЗВ формирует массивы из значений ИСТИНА и ЛОЖЬ, согласно выбранным критериям, в памяти Excel.



Если-бы вычисления производились в ячейках листа (для наглядности я всю работу формулы продемонстрирую так, как-будто вычисления происходят на листе, а не в памяти), то массивы выглядели бы так:



Очевидно что если например, D2=Карандаши, то значение будет равно ИСТИНА, а если D3=Папки, то ЛОЖЬ (так как критерием отбора товара в нашем примере является значение Карандаши).



Зная о том что значение ИСТИНА всегда равно 1, а ЛОЖЬ всегда равно 0 мы продолжаем работать с массивами как с числами 0 и 1.
Перемножив полученные значения массивов между собой последовательно, мы получим ОДИН массив из нолей и единиц. Там где выполнялись все три критерия отбора, (ИВАНОВ, КАРАНДАШИ, ЯНВАРЬ) т.е. все условия принимали значения ИСТИНА получаем 1 (1*1*1 = 1), если же хотя-бы одно условие не выполнялось - получим 0 (1*1*0 = 0 ; 1*0*1 = 0 ; 0*1*1 = 0 ).

Теперь осталось только умножить полученный массив на массив содержащий данные, которые нам необходимо в итоге просуммировать ( диапазон F2:F16) и собственно, просуммировать то что на 0 не умножилось.




Теперь сравните полученные при помощи формулы и при пошаговом вычислении на листе массивы (выделены красным).


 
Думаю всё понятно :)

МИНУСЫ: СУММПРОИЗВ - "тяжёлая" формула массива. При вычислениях на больших диапазонах данных заметно увеличивается время пересчёта.

ПРИМЕЧАНИЯ: Количество обрабатываемых массивов ограничено 255.

ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая версия Excel

Способ 3: Формула массива

Code

=СУММ(ЕСЛИ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2);F2:F16))

В английской версии:

Code

=SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

КАК ЭТО РАБОТАЕТ:  Точно так же как и Способ №2. Есть только два отличия - данная формула вводится нажатием Ctrl+Shift+Enter, а не просто нажатием Enter и массив 0-й и 1-ц не умножается на диапазон суммирования, а отбирается с помощью функции ЕСЛИ.

МИНУСЫ: Формулы массива при вычислениях на больших диапазонах данных заметно увеличивают время пересчёта.

ПРИМЕЧАНИЯ: Количество обрабатываемых массивов ограничено 255.
 
ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая версия Excel

Способ 4:

Code

=СУММЕСЛИМН(F2:F16;B2:B16;I2;D2:D16;J2;A2:A16;K2)

В английской версии:

Code

=SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)

КАК ЭТО РАБОТАЕТ:
Функция СУММЕСЛИМН требует обязательно указать диапазон суммирования (в нашем случае F2:F16) и хотя бы одну пару Диапазон/Условие.
По сути эта функция "один в один" повторяет действие функции СУММПРОИЗВ в нашем примере, но не является формулой массива.



МИНУСЫ: нет.
 
ОБЛАСТЬ ПРИМЕНЕНИЯ: Начиная с версии Excel 2007.

ПРИМЕЧАНИЯ: Количество пар диапазон/критерий ограничено 127