Представим себе таблицу, в которой в строках вперемешку указаны названия отделов(или счетов, или еще чего-то).
Необходимо вычислить общую сумму по каждому отделу. Многие делают это при помощи фильтра и записи ручками в ячейки.
Хотя сделать это можно легко и просто при помощи всего одной функции - СУММЕСЛИ.
Всего для СУММЕСЛИ предусмотрено три аргумента: Диапазон, Критерий, Диапазон_Суммирования.
Диапазон ( ) - указывается диапазон с критериями. Т.е. столбец, в котором искать значение, указанное аргументом Критерий.A1:A20000 Критерий ( )- значение(текстовое или числовое, а так же дата), которое необходимо найти в Диапазоне. Может содержать символы подстановки "*" и "?". Т.е. указав в качестве Критерия "*масса*" будут просуммированы значения, в которых встречается слово "масса". При этом слово "A1 масса " может либо встречаться в любом месте текста, либо в ячейке может быть только одно это слово. А указав "масса* ", будут просуммированы все значения, начинающиеся на "масса". "?" - заменяет лишь один символ, т.е. указав "мас?а " вы сможете просуммировать строки и со значением "масса" и со значением "маска" и т.д.
Если критерий записан в ячейке и надо все же использовать подстановочные символы, то можно сделать ссылку на эту ячейку добавив нужное. Допустим, надо просуммировать значения, содержащие слово "итог ". Слово " " записано в ячейкеитог , в столбце A при этом могут встречаться различные по написанию значения, содержащие словоA1 "итог" :"итоги за июнь" ,"итоги за июль" ,"итоги за март" . Формула тогда должна выглядеть так:
=СУММЕСЛИ( A1:A20000 ;"*"&A1 &"*";B1:B20000 )
"*"& - знак &(амперсанд) объединяет несколько значений в одно. Т.е. в результате получится "*итог*".A1 &"*"
Чтобы лучше понять принцип работы формул лучше использовать инструмент Вычислить формулу: Как просмотреть этапы вычисления формул
Все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки (=СУММЕСЛИ( ). Если критерием является число, использовать кавычки не требуется. Если требуется найти непосредственно вопросительный знак или звездочку, необходимо поставить перед ним знак "тильды" (~).A1:A20000 ;"итог";B1:B20000 )
Про тильду и её особенности можно узнать в этой статье: Как заменить/удалить/найти звездочку? Диапазон_Суммирования ( )(необязательный аргумент) - указывается диапазон сумм или числовых значений, которые необходимо просуммировать.B1:B20000
Последний аргумент функции(Диапазон_Суммирования -
как видно, первые два числа почти одинаковые, но у первого числа три ведущих нуля спереди, а второго - два. И это разные счета. А третий счет вообще отличается на последние цифры. Но СУММЕСЛИ после преобразования все три этих значения будет считать как число
Но что делать, когда критериев для суммирования 2 и больше? Допустим, Вам надо просуммировать только те суммы, которые относятся к одному отделу и только за определенную дату. Счастливые обладатели версий офиса 2007 и выше могут воспользоваться функцией СУММЕСЛИМН:
Так же как и для СУММЕСЛИ диапазоны суммирования и критериев должны быть равны по количеству строк.
СУММЕСЛИМН так же как и СУММЕСЛИ приводит значения к типу, а это значит, что все числа с ведущими нулями и более 15 знаков могут быть вычислены некорректно.
Т.к. СУММЕСЛИМН появилась только в версиях Excel, начиная с 2007, то как же быть в таких случаях несчастным пользователям более ранних версий? Очень просто: использовать другую функцию - СУММПРОИЗВ. Не буду расписывать аргументы, т.к. их много и все они являются массивами значений. Данная функция перемножает массивы, указанные аргументами. Я постараюсь описать общий принцип использования этой функции для суммирования данных по нескольким условиям.
Для решения задачи суммирования по нескольким критериям функция будет выглядеть так:
Разберем логику, т.к. многим она будет совершенно не ясна просто при взгляде на данную функцию. Хотя бы потому, что в справке подобное её применение не описывается. Для большей читабельности уменьшим размеры диапазонов:
Итак, выражение
Как видно, в первом массиве два совпадения условию, а во втором одно. Далее эти два массива перемножаются(за это отвечает знак умножения(*)). При перемножения происходит неявное преобразование массивов ЛОЖЬ и ИСТИНА в числовые константы 0 и 1 соответственно({0;1;1;0}*{0;0;1;0}). Как известно, при умножении на нуль получаем нуль. И в результате получается один массив:
Далее происходит уже перемножение массива {0;0;1;0} на массив чисел в диапазоне
И как результат получаем 30. Что нам и требовалось - мы получаем лишь ту сумму, которая соответствует критерию. Если сумм, удовлетворяющих критерию будет больше одной, то они будут просуммированы.
- Если у аргументов вместо знака умножения указать знак плюс:
( $A$2:$A$5 =$I$3 )+($B$2:$B$5 =H5 )
то условия будут сравниваться по принципу ИЛИ: т.е. суммироваться итоговые суммы будут в случае, если хотя бы одно условие выполняется: или равна значению ячейки$A$2:$A$5 или ячейка диапазона$I$3 равна значению ячейки$B$2:$B$5 .H5
В этом преимущество СУММПРОИЗВ перед СУММЕСЛИМН. СУММЕСЛИМН не может суммировать значения по принципу ИЛИ, только по принципу И(все условия должны выполняться). - СУММПРОИЗВ не приводит значения к типам, а это значит, что значения вроде
будут восприняты как есть, без всякого отсечения ведущих нулей и знаков после 15-го.000 34889913131323455,00 34889913131323455,000 34889913131323477 - СУММПРОИЗВ может работать с закрытыми книгами. Например, из одной книги суммируется данные по таблице другой книги. После того как закроете книгу-источник, СУММЕСЛИ и СУММЕСЛИМН выдадут ошибку
#ЗНАЧ! . А СУММПРОИЗВ продолжит работать как ни в чем не бывало.(#VALUE) - если вдруг потребовалось получить не сумму, а количество ячеек, удовлетворяющих критериям - из СУММПРОИЗВ достаточно удалить диапазон ячеек для суммирования:
=СУММПРОИЗВ(( $A$2:$A$50 =$I$3 )*($B$2:$B$50 =H5 ))
В СУММПРОИЗВ невозможно использовать символы подстановки * и ?. Точнее использовать можно, но они будут восприняты не как спец.символы, а как непосредственно звездочка и вопр.знак. Я считаю это существенным минусом. И хотя это можно обойти, использую внутри СУММПРОИЗВ иные функции - все же было бы замечательно, если бы функция каким-то образом могла использовать символы подстановки.
В приложенном файле-примере найдете пару примеров функций для более лучшего понимания написанного выше.
Сумма по нескольким критериям (41,5 КиБ, 14 470 скачиваний)
Так же см.:
Суммирование ячеек по цвету заливки
Суммирование ячеек по цвету шрифта
Суммирование ячеек по формату ячейки
Подсчитать сумму ячеек по цвету заливки
Подсчитать сумму ячеек по цвету шрифта
Как просуммировать данные с нескольких листов, в том числе по условию
почему в некоторых строках при копировании или протягивании формул СУУММЕСЛИ не находит соответствие значению ячейки в критерии при использовании синтаксиса типа H96&"*" а при отсутствии &"*" формула находит соответствие? При этом у остальных значений работают оба варианта, есть странная зависимость, исключаются все значения порядка 8хххх при использовании в сочетании с *. Что посоветуете с чего начать поиск?
Сложно сказать. Возможно, там числа, а звездочка работает в паре с текстом. Если речь именно про цифры, то лучше использовать конструкции типа">"&H96&"0" .
Огромное спасибо!!!! Профессионально и по делу!!!!