Суммирование в Excel по нескольким условиям на практике.
Источник проблемы и цель задачи.
При работе в Excel встречаются ситуации, когда требуется провести суммирование не по каждой позиции, а только по тем, которые соответствуют заданному признаку. Например, вычислить количество составов, отправленных в указанный город, найти наличие на складе только выбранного товара, рассчитать затраты по конкретной статье расходов и так далее. Позиции для такого суммирования выбираются либо по соответствия одному условию, либо по одновременному соответствую нескольким условиям. Сравните две задачи:
1. Найти количество вагонов, отправленных в город N.
2. Найти количество вагонов для сыпучих грузов, с автоматической разгрузкой, отправленных в город N во втором квартале текущего года.
В первом случае условие только одно. Такое суммирование по условию в Excel несложно, с расчетами справится рядовая формула массива или функция СУММЕСЛИ. Эти способы подробно рассмотрены в прошлых материалах.
Во втором случае ситуация сложнее, так как требуется одновременно отследить и выполнить суммирование по нескольким условиям.
В приведенном примере допустимо применение функции СУММЕСЛИМН, о которой тоже говорилось в наших материалах. Но ведь на практике встречаются неодинаковые ситуации. Невозможно соответствие значений из одних и тех же ячеек одновременно противоречивым условиям. Если стоит задача отследить отправку и вагонов для сыпучих грузов и цистерн, исключая открытые площадки, то СУММЕСЛИМН не справится. Дело в том, что и выгоны для сыпучих грузов, и цистерны – это тип подвижного грузового состава. Если в ячейке написано, что это сахар, то этот товар уже не будет хлебом.
Сегодня мы рассмотрим способы суммирования ячеек по нескольким условиям на практике. Сразу определимся, что способы с поверочными столбцами рассматриваться не будут, так как они больше относятся к теме применения логических выражений в Excel. Так же не рассматриваем формулы массивов, так как это тоже отдельная тема для разговора. Мы применим профессиональные приемы работы и будем использовать стандартные функции, специально предназначенные для такого выборочного суммирования. Кроме функций СУММЕСЛИ и СУММЕСЛИМН допускается использование таких функций как СУММПРОИЗВ и БДСУММ.
Работаем с СУММПРОИЗВ.
Вначале рассмотрим функцию СУММПРОИЗВ в различных ситуациях.
1. Ситуация, когда условия отслеживаются в разных колонках таблицы. В этом случае применяется функция СУММЕСЛИМН, о которой уже говорилось. Помимо этого, допускается применение такой функции, как СУММПРОИЗВ. Только ее надо немного изменить при вводе. Указываем не диапазоны, которые надо перемножить, а сравниваем ячейки этих диапазонов с условием проверки. Такое сравнение выполняем в скобках, а затем эти значения перемножаем между собой и адресом диапазона с числами. В общем виде синтаксис данной функции для решения указанной выше задачи выглядит следующим образом
=СУММПРОИЗВ((«Диапазон проверки 1 условия»=«1 условие»)*(«Диапазон проверки 2 условия»=«2 условие»)*…* «диапазон ячеек для суммирования»).
Естественно, в реальности кавычки не ставим. Исключение – ситуация, когда одно из условий содержит явно заданный текст.
Сразу приведем типичную задачу
Имеется отчет следующего вида:
Рисунок 1
Задача – определить сумму продаж для брюк-джоггеров. Всего в таблице185 позиций, не учитывая заголовок. В результате получаем запись формулы
=СУММПРОИЗВ((B2:B186=”брюки”)*(C2:C186=”джоггеры”)*(E2:E186
Рисунок 2
Заметьте, что результат расчета с помощью функции совпадает с вычислениями с помощью фильтра. Обратите внимание на такие моменты
- Диапазон для суммирования можно указать без скобок
- Условия, напротив, заключаем в круглые скобки.
- Явный текст условий пишем в кавычках
- Вместо явного текста условий можно указать ячейки, содержащие данный текст
Если поместить нужные критерии в ячейки J1:J3, то формулу для расчета, указанную выше, можно задать так:
=СУММПРОИЗВ((B2:B186=J1)*(C2:C186=J2)*(E2:E186
Рисунок 3
Обратите внимание, что использовать подстановочные знаки в этой формуле нельзя, выйдет ошибочное значение. Именно ошибочное, а не сообщение о ошибке.
Рисунок 4
Чтобы этого не случилось, то, если требуется проверить наличие теста в ячейках, применяйте комбинацию ЕЧИСЛО и ПОИСК. В предыдущем случае это выглядит так
=СУММПРОИЗВ((ЕЧИСЛО(ПОИСК(J1;B2:B186)))*(ЕЧИСЛО(ПОИСК(J2;C2:C186)))*(E2:E186
Вначале функция ПОИСК ищет вхождение нужного текста в содержимое ячейки. Если такое вхождение найдено, другими словами – если текст ячейки содержит нужное нам слово или строку, то функция ЕЧИСЛО выдаст результат ИСТИНА. В этом случае подстановочные знаки не требуются, н могут понадобится, если известны начало и конец строки в ячейке, но неизвестна середина этой строки.
Ситуация 2. Критерии расположены в одной колонке. Тут ввод функции гораздо проще. В качестве варианта применим комбинацию функций СУММПРОИЗВ и СУММЕСЛИ. допустимо указывать в качестве условия для функции СУММЕСЛИ содержимое не одной ячейки, а диапазона. Представим, что в предыдущем отчете надо найти итоговое количество брюк и рубашек. Получаем такую формулу
=СУММПРОИЗВ(СУММЕСЛИ(B2:B186;J2:J3;D2:D186))
Рисунок 5
Та же задача решается и по-другому. Дело в том, что знак умножения в функции СУММПРОИЗВ работает как логическое ИЛИ. А вот знак плюс срабатывает как логическое И.
=СУММПРОИЗВ(((B2:B186=J2)+(B2:B186=J3))*D2:D186)
Рисунок 6
Ситуация 3. Критерии для суммирования требуется отследить как в одной колонке, так и в нескольких. В качестве примера рассмотрим ситуацию, когда требуется найти итоговое количество для рубашек и брюк с ценой свыше 5000. Такая ситуация представляет собой общий случай первых двух. Для нахождения нужных критериев в одной колонке используем вторую формулу, описанную выше, дальше же поступаем так, как описано в ситуации 1.
=СУММПРОИЗВ(((B2:B186=J2)+(B2:B186=J3))*(E2:E186>=J4)*D2:D186)
Рисунок 7
Используем БДСУММ.
Теперь поговорим о функции БДСУММ.
Сразу стоит отметить, что эта функция применяется в указанных выше ситуациях без изменений в ее написании. Другими словами, при работе с функцией БДСУММ не учитывают ситуацию для применения. Проще происходит и обработка подстановочных знаков для обработки неточных текстовых и иных значений.
Синтаксис функции БДСУММ:
=БДСУММ(А;В;С)
А- таблица для обработки
В – колонка обрабатываемой таблицы, по которой проводится суммирование. Допускается как заголовок нужного столбца, так и порядковый номер в таблице.
С – проверочный диапазон. Содержит заголовки таблицы. Для каждого заголовка задается условие, которое проверяется при расчете в колонке с указанным заголовком.
Самое важное при работе с указанной функцией – правильно настроить диапазон проверки. Принцип указания условия для проверки в данном диапазоне идентичен указанию условий для расширенного фильтра. О настройке таких условий написано немало, в том числе и нами. Поэтому сейчас мы только напомним их вкратце.
- Условия задаются указанием критериев, с которыми должны совпадать данные в заданном столбце.
- Текстовые значения задаем в виде формулы явного сравнения. Если требуется найти все донные по товару с конкретным названием, указываем его формулой =«=товар». Слово товар в формуле меняем на нужное наименование конкретной позиции. Аналогично, если точное название неизвестно, то в этой формуле добавляем знак звездочки. В частности, если название товара начинается с слова «весы», то указываем это так:=«=весы*»
- Наименование столбца с условием должно совпадать с столбцами таблицы значений.
- При необходимости допускается указание несколько критериев, одновременно совпадающих по разным столбцам.
- Если необходимо в одной колонке указать несколько разных критериев, их располагают построчно (логическое ИЛИ).
- Если критерием служит некий диапазон значений, или ячейка из колонки должна соответствовать одновременно нескольким условиям, то данную колонку в диапазоне проверки дублируют, указывая в каждой копии в одной строке отдельный вариант критерия (логическое И).
- В каждой строке задается отдельное условие. При этом допускается построчное совпадение условий в одной колонку для разных условий в другой колонке диапазона проверки.
- Допускается применение в условиях подстановочных символов.
- Допускается в качестве условий проверки применять логические функции, функции поверки свойств и их комбинации. В качестве поверяемой ячейки указывается адрес проверяемой ячейки нужной колонки таблицы. В таком случае заголовок диапазона проверки не должен совпадать с заголовками полей исходной таблицы.
- Порядок колонок в диапазоне проверки не зависит от порядка колонок в таблице со значениями.
- Колонки исходной таблицы в формуле называются полями, а строки – записями Учитывайте это при изучении функции
В качестве иллюстрации приведем несколько примеров, используя таблицу «отчет».
1. необходимо найти общее количество брюк и рубашек. В этом случае в диапазоне проверки используем единственную графу «наименование номенклатуры», в которую внесем нужные наименования. Учитывая, что таблица с данными располагается в диапазоне A1:G186, диапазон проверки лежит в диапазоне J1:J3, а заголовок столбца «количество» находится в ячейке D1, получаем следующую формулу.
=БДСУММ(A1:G186;D1;J1:J3)
Тот же результат можно получить, если вместо D1 ввести цифру 4, так как «количество» – четвертый столбец в таблице. На рисунке ниже приведены обе формулы и результаты вычислений.
Рисунок 8
2. Предположим, что точное название товара неизвестно. Возможно, в колонке «наименование номенклатуры» дополнительно указан цвет товара, его код или что-то еще. Применим для условий подстановочные знаки в виде звездочек. Расположив диапазон проверки в ячейках J10:J12 , получаем формулу
=БДСУММ(A1:G186;4;J10:J12)
Обратите внимание, что при внесении изменений в таблицу путем добавления текста к наименованиям номенклатуры предыдущая формула уже часть позиций по рубашкам и брюкам не обнаружила.
Рисунок 9
3. Усложним задачу, записав предыдущее условие в виде формулы. Применим комбинацию функций ИЛИ, ЕЧИСЛО, ПОИСКПОЗ, как было указано выше .
=ИЛИ(ЕЧИСЛО(ПОИСК(“рубашка”;B2));ЕЧИСЛО(ПОИСК(“брюки”;B2)))
Рисунок 10
Еще раз обращаю внимание, что при использовании функций для задания условия указываем только первую ячейку нужного поля. Программа сама проверит все остальные значения в колонке с заданной ячейкой.
4. Применим формулы для условий. Найдем общую сумму продаж для всех рубашек за первое полугодие, и для всех брюк за второе полугодие. В функции СУММПРОИЗВ это сделать если и не невозможно, то затруднительно – предется делать расчет отдельно для каждого наименования, а затем суммировать. В функции БДСУММ это сделать проще.
В диапазоне условий пропишем в графе «проверка наименования» построчно две формулы
=ЕЧИСЛО(ПОИСК(“рубашка”;B2))
=ЕЧИСЛО(ПОИСК(“брюки”;B2))
В графе «тест по датам», учитывая, что номера месяцев первого полугодия меньше 7, а номера месяцев 2 полугодия больше 6, указываем напротив предыдущих условий соответствующие им формула проверки дат
=МЕСЯЦ(G2)
=МЕСЯЦ(G2)>6
Рисунок 11
5. Найдем количество по рубашкам с ценой в диапазоне от 3000 до 7000, с учетом того, что нас интересуют именно рубашки. В диапазон проверки вносим графу «наименование номенклатуры» и указываем в ней формулу =«=рубашка». С ценами сложнее. Для их проверки необходимо использовать логическое И, так как цена должна одновременно быть и больше 300и меньше 7000. Поэтому в диапазон проверки добавляем две колонки «Цена». В первой из них указываем:
>3000, а во второй
=БДСУММ(A1:G186;4;J1:L2)
Рисунок 12
Подведем итог.
Для нахождения общей суммы по одной или нескольким позициям не обязательно применять функции СУММЕСЛИ и СУММЕСЛИМН. С задачей неплохо справляются функции БДСУММ и СУММПРОИЗВ. Применение данных функций особенно оправдано при использовании проверочных критериев в одной колонке исходной таблицы, а также при сложных ситуациях, например, когда условия для выбранных позиций в одной колонке различаются в других колонках (посмотрите еще раз четвертый пример для БДСУММ). Кроме этого, СУММПРОИЗВ, в отличии от СУММЕСЛИМН, работает с данными из закрытых книг.
Однако эти функции имеют и недостатки. Функция БДСУММ обрабатывает любые условия для отбора позиций суммирования. Однако она требует диапазона проверки условий, что не всегда приемлемо. Кроме этого, указание условий жв данной функции может создать проблему для новичков. Зато сама функция проще по написанию. Функция СУММПРОИЗВ сложнее по написанию, но все условия задаются наглядно. С другой стороны, СУММПОИЗВ работает медленнее, и это тоже надо знать.
На этом наше занятие подошло к концу. Желаем всем хорошего дня и успешной работы. Файл исходной таблицей для самостоятельной тренировки по материалу урока доступен по ссылке ниже.
До встречи на наших семинарах от учебного центра «Зерде»!
