Excel vba суммирование по условию

Обновлено: 03.07.2024

Суммируем ячейки по критерию
Необходимо вычислить общую сумму по каждому отделу. Многие делают это при помощи фильтра и записи ручками в ячейки.
Хотя сделать это можно легко и просто при помощи всего одной функции - СУММЕСЛИ.
СУММЕСЛИ (SUMIF) – Суммирует ячейки, удовлетворяющие заданному условию (условие можно задать только одно). Эту функцию так же можно применить, если таблица разбита в столбцах на периоды(помесячно, в каждом месяце по три столбца - Доход|Расход|Разница) и необходимо подсчитать общую сумму за все периоды только по Доходу, Расходу и Разнице.

Всего для СУММЕСЛИ предусмотрено три аргумента: Диапазон, Критерий, Диапазон_Суммирования.
=СУММЕСЛИ( A1:A20000 ; A1 ; B1:B20000 )
=SUMIF(A1:A20000,A1,B1:B20000)

Диапазон ( A1:A20000 ) - указывается диапазон с критериями. Т.е. столбец, в котором искать значение, указанное аргументом Критерий.
Критерий ( A1 )- значение(текстовое или числовое, а так же дата), которое необходимо найти в Диапазоне. Может содержать символы подстановки "*" и "?". Т.е. указав в качестве Критерия "*масса*" будут просуммированы значения, в которых встречается слово "масса". При этом слово " масса " может либо встречаться в любом месте текста, либо в ячейке может быть только одно это слово. А указав " масса* ", будут просуммированы все значения, начинающиеся на "масса". "?" - заменяет лишь один символ, т.е. указав " мас?а " вы сможете просуммировать строки и со значением "масса" и со значением "маска" и т.д.
Если критерий записан в ячейке и надо все же использовать подстановочные символы, то можно сделать ссылку на эту ячейку добавив нужное. Допустим, надо просуммировать значения, содержащие слово " итог ". Слово " итог " записано в ячейке A1 , в столбце A при этом могут встречаться различные по написанию значения, содержащие слово "итог" : "итоги за июнь" , "итоги за июль" , "итоги за март" . Формула тогда должна выглядеть так:
=СУММЕСЛИ( A1:A20000 ;"*"& A1 &"*"; B1:B20000 )
"*"& A1 &"*" - знак &(амперсанд) объединяет несколько значений в одно. Т.е. в результате получится "*итог*".
Чтобы лучше понять принцип работы формул лучше использовать инструмент Вычислить формулу: Как просмотреть этапы вычисления формул
Все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки ( =СУММЕСЛИ( A1:A20000 ;"итог"; B1:B20000 ) ). Если критерием является число, использовать кавычки не требуется. Если требуется найти непосредственно вопросительный знак или звездочку, необходимо поставить перед ним знак "тильды" (

Как это работает: функция ищет в Диапазоне значение, указанное аргументом Критерий, и при нахождении совпадения суммирует данные, указанные аргументом Диапазон_Суммирования. Т.е. если у нас в столбце А название отдела, а в столбце В суммы, то указав в качестве критерия "Отдел развития" результатом функции будет сумма всех значений столбца В, напротив которых в столбце А встречается "Отдел развития". Фактически Диапазон_Суммирования может не совпадать по размеру с аргументом Диапазон и ошибки самой функции это не вызовет. Однако при определении ячеек для суммирования, в качестве начальной ячейки для суммирования будет использована верхняя левая ячейка аргумента Диапазон_Суммирования , а затем суммируются ячейки, соответствующие по размеру и форме аргументу Диапазон .

Некоторые особенности
Последний аргумент функции(Диапазон_Суммирования - B1:B20000 ) является необязательным. А это значит, что его можно не указывать. Если его не указать, то функция просуммирует значения, указанные аргументом Диапазон. Для чего это нужно. Например, Вам необходимо получить сумму только тех чисел, которые больше нуля. В столбце А суммы. Тогда функция будет иметь такой вид:
=СУММЕСЛИ( A1:A20000 ;">0")

Что следует стоит учитывать: диапазон_суммирования и диапазон должны быть равны по количеству строк. Иначе можно получить неверный результат. Оптимально, если это будет выглядеть как в приведенных мной формулах: диапазон и диапазон_суммирования начинаются с одной строки и имеют одинаковое количество строк: A1:A20000 ; B1:B20000

Ложка дегтя: - СУММЕСЛИ(а так же СУММЕСЛИМН, СЧЁТЕСЛИ и иже с ними) всегда стремятся преобразовать все значения аргументов к типам. Это значит, что если у нас в ячейке записано число 23 - оно будет воспринято как число. Если тоже число будет записано как текст - "23" , то функция преобразует его сначала в число, а потом уже будет работать с ним. Т.е. и 23 и "23" у нас будут считаться одинаковым значением. С одной стороны это хорошо, но иногда такое поведение может сыграть злую шутку. Например, у Вас в ячейках столбца А расположены некие номер счетов, длина которых более 15-ти символов и могут иметь ведущие нули:
000 34889913131323455
00 34889913131323455
000 34889913131323477
как видно, первые два числа почти одинаковые, но у первого числа три ведущих нуля спереди, а второго - два. И это разные счета. А третий счет вообще отличается на последние цифры. Но СУММЕСЛИ после преобразования все три этих значения будет считать как число 348899131313234 00 и если записать функцию так: =СУММЕСЛИ( $A$1:$A$3 ; A1 ; $B$1:$B$3 ) , где в столбце В суммы для каждого счета, то она просуммирует значения для всех трех счетов, считая их одинаковыми. Особо обращаю внимание еще на тот факт, что все числа после 15-го знака будут преобразованы в нули. Эти особенности всегда необходимо учитывать при использовании данных функций, чтобы не попасть в неловкую ситуацию, когда результат будет некорректным.

Суммирование по двум и более критериям
Но что делать, когда критериев для суммирования 2 и больше? Допустим, Вам надо просуммировать только те суммы, которые относятся к одному отделу и только за определенную дату. Счастливые обладатели версий офиса 2007 и выше могут воспользоваться функцией СУММЕСЛИМН:
=СУММЕСЛИМН( $C$2:$C$50 ; $A$2:$A$50 ; $I$3 ; $B$2:$B$50 ; $H8 )
$C$2:$C$50 - диапазон_суммирования. Первым аргументов указывается диапазон ячеек, содержащих суммы, которые и будут собираться в одну.
$A$2:$A$50 , $B$2:$B$50 - Диапазон_критерия. Указывается диапазон ячеек, в которых необходимо искать совпадение по критерию.
$I$3 , $H8 - критерий. Здесь, как и в СУММЕСЛИ, допускается указание символов подстановки * и ? и работают они так же.

Особенность указания аргументов: сначала указывается диапазон критерия(они пронумерованы) затем через точку-с-запятой указывается непосредственно значение(критерий), которое в этом диапазоне необходимо найти - $A$2:$A$50 ; $I$3 . И никак иначе. Не стоит пытаться сначала указать все диапазоны, а потом критерии к ним - функция выдаст либо ошибку, либо просуммирует не то, что надо.

Все условия сравниваются по принципу И . Это значит, что если все перечисленные условия выполняются. Если хоть одно условие не выполняется - функция пропускает строку и ничего не суммирует.
Так же как и для СУММЕСЛИ диапазоны суммирования и критериев должны быть равны по количеству строк.

СУММЕСЛИМН так же как и СУММЕСЛИ приводит значения к типу, а это значит, что все числа с ведущими нулями и более 15 знаков могут быть вычислены некорректно.

Т.к. СУММЕСЛИМН появилась только в версиях 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 )
Итак, выражение ( $A$2:$A$5 = $I$3 ) и ( $B$2:$B$5 = H5 ) являются логическими и возвращают массивы логических ЛОЖЬ и ИСТИНА. ИСТИНА, если ячейка диапазона $A$2:$A$5 равна значению ячейки $I$3 и ячейка диапазона $B$2:$B$5 равна значению ячейки H5 . Т.е. получается у нас следующее:
=СУММПРОИЗВ(*; $C$2:$C$50 )
Как видно, в первом массиве два совпадения условию, а во втором одно. Далее эти два массива перемножаются(за это отвечает знак умножения(*)). При перемножения происходит неявное преобразование массивов ЛОЖЬ и ИСТИНА в числовые константы 0 и 1 соответственно(*). Как известно, при умножении на нуль получаем нуль. И в результате получается один массив:
=СУММПРОИЗВ(; $C$2:$C$50 )
Далее происходит уже перемножение массива на массив чисел в диапазоне $C$2:$C$50 :
=СУММПРОИЗВ(;)
И как результат получаем 30. Что нам и требовалось - мы получаем лишь ту сумму, которая соответствует критерию. Если сумм, удовлетворяющих критерию будет больше одной, то они будут просуммированы.

Недостатки
В СУММПРОИЗВ невозможно использовать символы подстановки * и ?. Точнее использовать можно, но они будут восприняты не как спец.символы, а как непосредственно звездочка и вопр.знак. Я считаю это существенным минусом. И хотя это можно обойти, использую внутри СУММПРОИЗВ иные функции - все же было бы замечательно, если бы функция каким-то образом могла использовать символы подстановки.

Сумма по нескольким критериям (41,5 KiB, 12 784 скачиваний)

Для выборочного подсчета по нескольким условиям в больших таблицах можно использовать несколько способов: фильтры, сводные таблицы, функции СУММЕСЛИ и СУММЕСЛИМН и т.д.

Еще одним, относительно экзотическим, но весма мощным инструментом является функция БДСУММ (DSUM) из категории Работа с базой данных (Database) . При внешней простоте, она позволяет гибко фильтровать списки по нескольким сложным и связанным между собой условиям и подсчитывает сумму найденных записей по заданному столбцу. Синтаксис функции таков:

=БДСУММ( Исходные_данные ; Столбец_результата ; Диапазон_условий )

Исходные_данные - диапазон, включающий в себя таблицу с данными, которые мы анализируем и строкой заголовка.
Столбец_результата - название (из шапки таблицы) или порядковый номер столбца, по которому нужно просуммировать данные.
Диапазон_условий - диапазон, содержащий названия столбцов и условия по ним.

Чтобы удобнее было ссылаться эту таблицу в будущем, конвертируем ее в "умную" командой Форматировать как таблицу на вкладке Главная (Home - Format as Table) или сочетанием клавиш Ctrl + T . На появившейся затем вкладке Конструктор (Design) зададим ей имя - например БазаДанных.

Простая сумма по одному условию

Начнем с простого случая. Допустим, нам нужно просуммировать стоимость по всем сделкам сока Добрый. Это будет выглядеть следующим образом:

Обратите внимание на следующие моменты:

Приблизительный и точный текстовый поиск

При необходимости, можно легко реализовать не только точный, но и приблизительный текстовый поиск. Посмотрите внимательно на следующие варианты суммирования выручки по городу Абакан и разницу между ними:

Если нужен поиск точного соответствия, то используем конструкцию '= (апостроф и знак равно).
Если нужен поиск подстроки, т.е. всех ячеек, которые содержат нужное значение, то его надо заключить в звездочки. В нашем случае будут просуммированы все варианты Абакана (с "г.", без "г.", с пробелами перед-после и т.п.)
Если просто ввести значение без равно и звездочек, то будут найдены и просуммированны все строки, где содержимое начинается с указанного значения, т.е. это равноценно звездочке в конце.

Несколько условий со связками "И" - "ИЛИ"

Если нужно просуммировать данные по нескольким условиям, связанным друг с другом логическим оператором И (AND), то ячейки с этими условиями должны быть в одной строке. Например, если нужно просуммировать все продажи Fanta по Абакану (в любом виде его написания), то это будет выглядеть так:

Если же нужно связать несколько условий логическим оператором ИЛИ (OR), то их нужно расположить в разных строчках. Например, если нужно просуммировать деньги по всем вариантам написания "города на Неве", коих великое множество:

И конечно же, можно комбинировать оба подхода, сочетания в одном запросе условия со связками И и ИЛИ одновременно:

В этом случае вычисляется сумма продаж Fanta в Абакане и Burn у Дубинина.

Суммирование по интервалу дат

В диапазоне условий можно легко задать интервал дат для подсчета. Если интервал ограничен с двух сторон, то нужно будет задать два условия, связанных между собой логическим "И" - поэтому придется писать условия в одной строке, т.е. добавить еще один столбец с тем же названием Дата сделки:

В данном случае вычисляется сумма продаж Fanta за 2016 год и Фруктайм до 2016 года.

Условия для чисел

Для отбора по числовым критериям можно смело использовать обычные знаки неравенств >, <, >=, <= как и в обычных формулах Excel. Например, если нам нужно просуммировать все продажи любых видов колы, где сумма сделки была в интервале 500-600:

Исключения "все кроме"

Если нужно при суммировании исключить записи по какому-либо параметру, то можно использовать символы "<>" обозначающие "не равно" в синтаксисе Excel. Допустим, нам нужно просуммировать все данные по Fanta кроме Самары и по Квасу кроме Пензы - это будет выглядеть так:

Заключение

Надеюсь, вы уже поняли, что функция БДСУММ является очень неплохим инструментом и, зачастую, более удобной альтернативой классическим функциям выборочного подсчета типа СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) . Кроме того, в той же категории Работа с базой данных (Database) можно найти ее "подруг", вычисляющих не только сумму:

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.

Способ 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) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:

Функция СУММЕСЛИМН — одна из математических и тригонометрических функций, которая суммирует все аргументы, удовлетворяющие нескольким условиям. Например, с помощью функции СУММЕСЛИМН можно найти число всех розничных продавцов, (1) проживающих в одном регионе, (2) чей доход превышает установленный уровень.

Это видео — часть учебного курса Усложненные функции ЕСЛИ.

Синтаксис

СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

=СУММЕСЛИМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Артем")

=СУММЕСЛИМН(A2:A9; B2:B9; "<>Бананы"; C2:C9; "Артем")

Имя аргумента

Диапазон_суммирования (обязательный аргумент)

Диапазон ячеек для суммирования.

Диапазон_условия1 (обязательный аргумент)

Диапазон, в котором проверяется Условие1.

Диапазон_условия1 и Условие1 составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента Диапазон_суммирования.

Условие1 (обязательный аргумент)

Условие, определяющее, какие ячейки суммируются в аргументе Диапазон_условия1. Например, условия могут вводится в следующем виде: 32, ">32", B4, "яблоки" или "32".

Диапазон_условия2, Условие2, … (необязательный аргумент)

Дополнительные диапазоны и условия для них. Можно ввести до 127 пар диапазонов и условий.

Примеры

Чтобы использовать эти примеры в Excel, выделите нужные данные в таблице, щелкните их правой кнопкой мыши и выберите команду Копировать. На новом листе щелкните правой кнопкой мыши ячейку A1 и в разделе Параметры вставки выберите команду Использовать форматы конечных ячеек.

Проданное количество

=СУММЕСЛИМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Артем")

Суммирует количество продуктов, названия которых начинаются с Я и которые были проданы продавцом Артем. Для поиска совпадающих названий продуктов в Criteria_range1 B2:B Criteria_range2 9 в группе Условия1(=A*) используется подп. Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 20.

=СУММЕСЛИМН(A2:A9; B2:B9; "<>Бананы"; C2:C9; "Артем")

Суммирует количество продуктов, которые не являются бананами и которые были проданы продавцом по имени Артем. Он исключает бананы, используя<>в критерии1, "<>Бананы"и ищет имя "Том" в Criteria_range2 C2:C9. Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 30.

Распространенные неполадки

Вместо ожидаемого результата отображается 0 (нуль).

Если выполняется поиск текстовых значений, например имени человека, убедитесь в том, что значения аргументов Условие1, 2 заключены в кавычки.

Неверный результат возвращается в том случае, если диапазон ячеек, заданный аргументом Диапазон_суммирования, содержит значение ИСТИНА или ЛОЖЬ.

Значения ИСТИНА и ЛОЖЬ в диапазоне ячеек, заданных аргументом Диапазон_суммирования, оцениваются по-разному, что может приводить к непредвиденным результатам при их суммировании.

Ячейки в аргументе Диапазон_суммирования, которым присвоено значение ИСТИНА, оцениваются как 1. Ячейки, которым присвоено значение ЛОЖЬ, оцениваются как 0 (ноль).

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Читайте также: