Предположим что у Вас есть вот такой отчёт по продажам торговых представителей:
Из
него Вам необходимо узнать сколько карандашей продал торговый представитель Иванов в январе.
ПРОБЛЕМА: Как суммировать данные по нескольким критериям??
РЕШЕНИЕ: Способ 1:
Code
=БДСУММ(A1:G16;F1;I1:K2)
В английской версии:
Code
=DSUM(A1:G16,F1,I1:K2)
КАК ЭТО РАБОТАЕТ:
Из указанной нами базы данных A1:G16 функция БДСУММ извлекает и суммирует данные столбца Количество (аргумент "Поле" = F1) по заданным в ячейках I1:K2 (Продавец = Иванов; Продукция = Карандаши; Месяц = Январь) критериям.
МИНУСЫ:
Список критериев должен быть на листе.
ПРИМЕЧАНИЯ: Количество критериев суммирования ограничено оперативной памятью. ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая версия Excel
Функция СУММПРОИЗВ формирует массивы из значений ИСТИНА и ЛОЖЬ, согласно выбранным критериям, в памяти 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.
КАК ЭТО РАБОТАЕТ: Точно так же как и Способ №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
А в данном конкретном примере для функции СУММПРОИЗВ Примечание не относиться ("Количество обрабатываемых массивов ограничено 255.") ? Массив ведь всего один.
А если один из критериев пустой, ексель ищет нулевые значения, допустим необходимо по одному из критериев найти любое значение, не меняя формулы, есть ли какой-то вариант задать критерий так чтобы есель воспринимал его как любое значение, или нужен макрос, и поможет ли он?
Натолкнулся на одну неприятную особенность при использовании данной формулы - если база данных находится в другом файле, то она обязательно должна быть открыта, иначе формула не будет считать. У меня Офис 2007. Никто не в курсе, в 2010 или 2013 офисе данная оплошность исправлена?
Упс, простите, писал, о чем думал - а думал я о "суммеслимн". =) "суммпроизв" слишком тяжелая. Стоит целерон 2,1гГц и 512мб оперативки - страшно повесить систему.
Разница в быстродействии между СУММЕСЛИМН() и СУММПРОИЗВ() небольшая, обе формулы довольно "тяжелые", хотя СУММЕСЛИМН() немного быстрее. В любом случае, если исходных данных много - лучше пользоваться сводной таблицей. Это в разы быстрее и удобнее
Добрый день, подскажите, пожалуйста, если в одном из диапазонов условий (новый столбец E) будут значения дат в таком формате 04.05.2014, 03.01.2013 и т.п. Одним из условий будет меньше или равно, допустим 03.01.2014, которое будет в ячейке N2. Как будет выглядеть формула? В моем случае это не получается сделать: выдает ошибку. Формула =СУММЕСЛИМН(G2:G16;B2:B16;J2;D2:D16;K2;A2:A16;L2;E2:E16;<=N2).
Спасибо за статью, давно хотел разобраться с подобным инструментарием, не всегда получается создать дополнительные ячейки для промежуточных расчетов. Особенное спасибо за масштаб статьи - рассмотрено сразу же несколько способов суммирования по нескольким критериям. А чем большим инструментарием мы владеем, тем эффективнее решаем такие задачи.
Хорошо всё расписано, но есть один вопрос. Если в столбце "А" указан не месяц, а конкретная дата, при этом суммировать надо диапазон, к примеру от "10 января до 27 января"
Добрый день, подскажите пож. как быть если в одном диапазоне несколько критериев, вариант сумеслимн(F:F;D:D;"карандаши")+сумеслимн(F:F;D:D;"папки") не подходит.
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
