SUMIF несколько столбцов с одним или несколькими критериями
Этот учебник научит вас нескольким простым способам суммирования нескольких столбцов в Excel на основе одного или нескольких критериев.
Выполнение условной суммы в Excel не составляет труда, если все значения, которые нужно суммировать, находятся в одном столбце. Суммирование нескольких столбцов представляет собой проблему, поскольку обе функции СУММЕСЛИ и СУММЕСЛИМН требуют, чтобы диапазон суммы и диапазон критериев были одинакового размера. К счастью, когда нет прямого способа что-то сделать, всегда есть обходной путь 🙂
Сумма Excel Если: несколько столбцов, один критерий
Прежде всего, давайте узнаем, какую именно проблему мы пытаемся решить. Предположим, у вас есть таблица ежемесячных продаж, как показано ниже. Поскольку он был объединен из нескольких региональных отчетов, для одного и того же продукта есть несколько записей:
Вопрос в том, как получить общий объем продаж определенного товара?
Первая идея, которая приходит на ум, — использовать формулу СУММЕСЛИ в чистом виде:
=СУММЕСЛИ(A2:A10, “яблоки”, C2:E10)
К сожалению, это не сработает. Причина в том, что размеры сумма_диапазон определяются Excel автоматически на основе размеров диапазон аргумент. Поскольку диапазон наших критериев включает только один столбец (A2:A10), то же самое относится и к диапазону суммы (C2:C10). сумма_диапазон параметр, определенный в формуле (C2:E10), фактически определяет только верхнюю левую ячейку диапазона, который будет суммироваться. В результате приведенная выше формула суммирует продажи яблок только в столбце C. Не то, что мы ищем, а?
Самое простое рабочее решение, которое напрашивается само собой, — создать вспомогательный столбец, суммирующий числа для каждой отдельной строки, а затем использовать этот столбец для сумма_диапазон.
Итак, продолжайте и поместите формулу СУММ в F2, затем перетащите ее вниз на столько ячеек, сколько необходимо:
=СУММ(C2:E2)
После этого можно быстро выполнить работу:
=СУММЕСЛИ(A2:A10, I1, F2:F10)
Где I1 представляет интерес.
В приведенной выше формуле сумма_диапазон имеет такой же размер, как диапазон (1 столбец и 9 строк), поэтому работает без сучка и задоринки:
Если в макете вашего рабочего листа нет места для дополнительных столбцов, примените одно из следующих решений.
SUMIF несколько столбцов
Идея состоит в том, чтобы написать отдельную формулу СУММЕСЛИ для каждого из столбцов, которые вы хотите суммировать, а затем сложить результаты:
СУММ(СУММЕСЛИ(…), СУММЕСЛИ(…), СУММЕСЛИ(…))
Или же
СУММЕСЛИ(…) + СУММЕСЛИ(…) + СУММЕСЛИ(…)
Практическая реализация выглядит следующим образом:
=СУММ(СУММЕСЛИ(A2:A10,H1,C2:C10), СУММЕСЛИ(A2:A10,H1,D2:D10), СУММЕСЛИ(A2:A10,H1,E2:E10))
Или же
=СУММЕСЛИ(A2:A10, H1, C2:C10) + СУММЕСЛИ(A2:A10, H1, D2:D10) + СУММЕСЛИ(A2:A10, H1, E2:E10)
Вы также можете «жестко закодировать» условие в формуле, если это необходимо:
=СУММЕСЛИ(A2:A10, “Яблоки”, C2:C10) + СУММЕСЛИ(A2:A10, “Яблоки”, D2:D10) + СУММЕСЛИСЛИ(A2:A10, “Яблоки”, E2:E10)
Это прекрасно работает для разумного количества столбцов, но для большого набора данных формула становится слишком длинной и трудной для чтения. В этом случае нижеприведенные решения являются более подходящими.
СУММ как формула массива
Другой способ сделать сумму, если в нескольких столбцах на основе одного критерия, состоит в том, чтобы построить формулу массива:
СУММА((сумма_диапазон) * (–(критерии_диапазонзнак равнокритерии)))
Для нашего примера набора данных формула принимает следующий вид:
=СУММ((C2:E10)*(–(A2:A10=H1)))
Или же
=СУММ((C2:E10)*(–(A2:A10=”Яблоки”)))
В Excel 2019 и более ранних версиях вы должны нажать Ctrl + Shift + Enter, чтобы правильно завершить формулу. В Excel 365 и Excel 2021 это работает как обычная формула благодаря встроенной поддержке динамических массивов.
Как работает эта формула:
Основная концепция состоит в том, чтобы умножить элементы этих двух массивов:
- (C2:E10) – все значения в диапазоне суммы. В нашем случае массив содержит 27 элементов (3 столбца и 9 строк: {250,120,210;155,180,210;130,175,125; …}
- (–(A2:A10=H1)) — сравнивает каждое значение в A2:A10 с целевым элементом в H1. Результатом является массив значений TRUE (условие выполнено) и FALSE (условие не выполнено), который затем преобразуется в массив из 1 и 0 с помощью двойного унарного оператора: {0;1;0 ;0;1;0;0;1;1}
Обратите внимание, что первый массив двумерный (каждый столбец данных разделен запятой, а каждая строка — точкой с запятой), а второй — одномерный вертикальный массив (1 столбец данных, строки разделены точкой с запятой). ). Когда два массива умножаются, все элементы двумерного массива в данной строке умножаются на соответствующий элемент одномерного массива:
Поскольку умножение на ноль дает ноль, выживают только числа, для которых критерий ИСТИНА, и функция СУММ складывает их:
=СУММ({0,0,0;155,180,210;0,0,0;0,0,0;160,140,170;0,0,0;0,0,0;…})
Чтобы упростить понимание логики формулы, вы можете написать первый множитель следующим образом:
=СУММ((C2:C10 + D2:D10 + E2:E10) * (–(A2:A10=H1)))
Это создаст массив сумм по строкам (как вспомогательный столбец в самом первом примере), который затем умножается на массив из 1 и 0:
{580;545;430;615;470;750;550;620;570}*{0;1;0;0;1;0;0;1;1}
Результат умножения подается в SUM:
=СУММ({0;545;0;0;470;0;0;620;570})
Не нравится использовать формулы массивов на листе? Я тоже. Что ж, давайте проверим следующее решение 🙂
формула СУММПРОИЗВ
Стратегию, описанную в приведенном выше примере, также можно реализовать с помощью функции СУММПРОИЗВ.
СУММПРОИЗВ((сумма_диапазон) * (критерии_диапазонзнак равнокритерии))
Реальная формула выглядит следующим образом:
=СУММПРОИЗВ((C2:E10) * (A2:A10=H1))
Логика формулы такая же, как и в предыдущем примере. Прелесть функции СУММПРОИЗВ заключается в том, что она изначально поддерживает массивы, поэтому она прекрасно работает как обычная формула во всех версиях Excel.
Сумма Excel, если: несколько столбцов, несколько критериев
Три подхода, которые мы использовали для сложения нескольких столбцов с одним критерием, также будут работать для условной суммы с несколькими критериями. Формулы просто станут немного сложнее.
СУММЕСЛИМН + СУММЕСЛИМН для суммирования нескольких столбцов
Для суммирования ячеек, соответствующих нескольким критериям, обычно используется функция СУММЕСЛИМН. Проблема в том, что, как и его аналог с одним критерием, СУММЕСЛИМН не поддерживает диапазон суммы из нескольких столбцов. Чтобы преодолеть это, мы пишем несколько СУММЕСЛИМН, по одному на каждый столбец в диапазоне сумм:
СУММ(СУММЕСЛИМН(…), СУММИММН(…), СУММИММН(…))
Или же
СУММЕСЛИМН(…) + СУММЕСЛИМН(…) + СУММЕСЛИМН(…)
Например, для суммирования продаж винограда (H1) в Северном регионе (H2) используется следующая формула:
=СУММЕСЛИМН(C2:C10, A2:A10, H1, B2:B10, H2) + СУММЕСЛИМН(D2:D10, A2:A10, H1, B2:B10, H2) + СУММЕСЛИМН(E2:E10, A2:A10, H1) , В2:В10, Н2)
Формула массива для условного суммирования нескольких столбцов
Формула СУММ для нескольких критериев очень похожа на формулу для одного критерия — вы просто включаете дополнительные пары критерии_диапазон=критерий:
СУММА((сумма_диапазон) * (–(критерии_диапазон1знак равнокритерии1)) * (–(критерии_диапазон2знак равнокритерии2)))
Например, чтобы суммировать продажи товара в H1 и региона в H2, формула выглядит следующим образом:
=СУММ((C2:E10) * (–(A2:A10=H1)) * (–(B2:B10=H2)))
В Excel 2019 и более ранних версиях не забудьте нажать Ctrl + Shift + Enter, чтобы сделать формулу массива CSE. В динамических массивах Excel 365 и 2021 обычная формула будет работать нормально, как показано на снимке экрана:
Формула СУММПРОИЗВ с несколькими критериями
Самый простой способ суммировать несколько столбцов на основе нескольких критериев — это формула СУММПРОИЗВ:
СУММПРОИЗВ((сумма_диапазон) * (критерии_диапазон1знак равнокритерии1) * (критерии_диапазон2знак равнокритерии2))
Как видите, она очень похожа на формулу СУММ, но не требует дополнительных манипуляций с массивами.
Для суммирования нескольких столбцов с двумя критериями используется следующая формула:
=СУММПРОИЗВ((C2:E10) * (A2:A10=H1) * (B2:B10=H2))
Это 3 способа суммирования нескольких столбцов на основе одного или нескольких условий в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Сумма, если несколько столбцов – примеры (файл .xlsx)
