Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям
Думаю, для начала неплохо было бы пояснить, что такое СУММЕСЛИ(это для тех, кто не знает). =СУММЕСЛИ(A1:A20000;A1;B1:B20000) =СУММЕСЛИ(A1:A20000;">0")
СУММЕСЛИ – Суммирует ячейки, удовлетворяющие заданному условию. Всего для СУММЕСЛИ предусмотрено три аргумента: Диапазон, Критерий, Диапазон_Суммирования.
Диапазон(A1:A20000) — указывается диапазон с критериями. Т.е. столбец, в котором искать значение, указанное аргументом Критерий.
Критерий(A1)- значение(текстовое или числовое, а так же дата), которое необходимо найти в Диапазоне. Может содержать символы подстановки «*» и «?». Т.е. указав в качестве Критерия «*масса*» Вы сможете просуммировать по значениями, в которых встречается слово «масса». А указав «масса*» — значения, начинающиеся на «масса». «?» — заменяет лишь один символ, т.е. указав «мас?а» вы сможете просуммировать строки и со значением «масса» и со значением «маска» и т.д.
Все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки («). Если критерием является число, использовать кавычки не требуется. Если требуется найти непосредственно вопросительный знак или звездочку, необходимо поставить перед ним знак «тильды» (~).
Диапазон_Суммирования (B1:B20000)(необязательный аргумент) — указывается диапазон сумм или числовых значений, которые необходимо просуммировать.
Сначала поясню общий момент: функция ищет в Диапазоне значение, указанное аргументом Критерий, и при находждении совпадения суммирует данные, указанные аргументом Диапазон_Суммирования. Т.е. если у Вас в столбце А название отдела, а в столбце В суммы, то указав в качестве критерия «Отдел развития», то результатом функции будет сумма всех значений столбца В, напротив которых в столбце А встречается «Отдел развития». Диапазон_Суммирования может не совпадать по размеру с аргументом Диапазон. Однако при определении ячеек для суммирования, в качестве начальной ячейки для суммирования будет использована верхняя левая ячейка аргумента Диапазон_Суммирования, а затем суммируются ячейки, соответствующие по размеру и форме аргументу Диапазон.
Теперь некоторые особенности. Последний аргумент функции(Диапазон_Суммирования) является необязательным. А это значит, что его можно не указывать. Если его не указать, то функция просуммирует значения, указанные аргументом Диапазон. Для чего это нужно. Например, Вам необходимо получить сумму только тех чисел, которые больше нуля. В столбце А суммы. Тогда фунция будет иметь такой вид:
Но что делать, когда критериев для суммирования 2 и больше? Можно, конечно, воспользоваться функцией СУММЕСЛИМН, но…Она появилась только в версиях Excel, начиная с 2007. А как же быть несчастным пользователям более ранних версий? Очень просто: использовать другую функцию — СУММПРОИЗВ. Не буду расписывать аргументы, т.к. их много и все они являются массивами значений. Данная функция перемножает массивы, указанные аргументами. =СУММПРОИЗВ(($A$2:$A$50=$I$3)*($B$2:$B$50=H5);$C$2:$C$50)
Допустим, Вам надо просуммировать только те суммы, которые относятся к одному отделу и только за определенную дату. Тогда функция будет выглядеть так:
$A$2:$A$50 — диапазон дат. $I$3 — дата критерия, за которую необходимо просуммировать данные.
$B$2:$B$50 — наименования отделов. H5 — наименование отдела, данные по которому необходимо просуммировать.
$C$2:$C$50 — диапазон с суммами.
Разберем логику, т.к. многим она будет совершенно не ясна просто при взгляде на данную функцию. Хотя бы потому, что в справке подобное её применение не описывается. Для большей читабельности уменьшим размеры диапазонов: =СУММПРОИЗВ(($A$2:$A$5=$I$3)*($B$2:$B$5=H5);$C$2:$C$5) =СУММПРОИЗВ({ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}*{ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ};$C$2:$C$50) =СУММПРОИЗВ({0;0;1;0};$C$2:$C$50) =СУММПРОИЗВ({0;0;1;0};{10;20;30;40})
Итак, выражение ($A$2:$A$5=$I$3) и ($B$2:$B$5=H5) являются логическими и возвращают массивы логических ЛОЖЬ и ИСТИНА. ИСТИНА, если ячейка диапазона $A$2:$A$5 равна значению ячейки $I$3 и ячейка диапазона $B$2:$B$5 равна значению ячейки H5. Т.е. получается у нас следующее:
Как видно, в первом массиве два совпадения условию, а во втором одно. Далее эти два массива перемножаются(за это отвечает знак умножения(*)). При перемножения происходит неявное преобразование массивов ЛОЖЬ и ИСТИНА в числовые константы 0 и 1 соответственно({0;1;1;0}*{0;0;1;0}). Как известно, при умножении на нуль получаем нуль. И в результате получается один массив:
Далее происходит уже перемножение массива {0;0;1;0} на массив чисел в диапазоне $C$2:$C$50:
И как результат получаем 30. Что нам и требовалось — мы получаем лишь ту сумму, которая соответствует критерию. Если сумм, удовлетворяющих критерию будет больше одной, то они будут просуммированы.
В примере найдете пару примеров функций для более лучшего понимания написанного выше.
Tips_All_SUMPRODUCT.xls (29,5 KiB, 1 354 скачиваний)
Так же см.:
→Summ_CellColor - Суммирование ячеек по цвету заливки
→Summ_CellFont - Суммирование ячеек по цвету шрифта
→Summ_CellFormat - Суммирование ячеек по формату ячейки
рис.1
на панели.
