Функция СУММЕСЛИМН() Сложение с несколькими критериями в EXCEL (Часть 2.Условие И)

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


В качестве исходной таблицы возьмем таблицу с двумя столбцами (полями): текстовым « Фрукты » и числовым « Количество на складе » (См. файл примера ).

Задача1 (1 текстовый критерий и 1 числовой)

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

1. = СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;">="&E2)

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

  • B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
  • A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
  • D2 и ">="&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

Порядок аргументов различен в функциях СУММЕСЛИМН() и СУММЕСЛИ() . В СУММЕСЛИМН() аргумент интервал_суммирования является первым аргументом, а в СУММЕСЛИ() – третьим. При копировании и редактировании этих похожих функций необходимо следить за тем, чтобы аргументы были указаны в правильном порядке.

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);--(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :

  • Результатом вычисления A2:A13=D2 является массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2:A13=D2 , а затем нажав F9 ;
  • Результатом вычисления B2:B13 является массив {3:5:11:98:4:8:56:2:4:6:10:11}, т.е. просто значения из столбца B ;
  • Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является {0:0:0:0:4:8:56:0:0:0:0:0}. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
  • Разберем второе условие: Результатом вычисления --( B2:B13>=E2) является массив {0:0:1:1:0:1:1:0:0:1:1:1}. Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
  • Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15 ).

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Задача2 (2 числовых критерия)



Другой задачей может быть нахождение сумм ящиков только тех партий товаров, у которых количество ящиков попадает в определенный интервал, например от 5 до 20 (см. файл примера Лист 2Числа ).

Формулы строятся аналогично задаче 1: =СУММЕСЛИМН(B2:B13;B2:B13;">="&D2;B2:B13;"<="&E2)

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($B2>=$D$2;$B2<=$E$2)

Задача3 (2 критерия Дата)

Другой задачей может быть нахождение суммарных продаж за период (см. файл примера Лист "2 Даты" ). Используем другую исходную таблицу со столбцами Дата продажи и Объем продаж .

Формулы строятся аналогично задаче 2: = СУММЕСЛИМН(B6:B17;A6:A17;">="&D6;A6:A17;"<="&E6)

Примечание . Даты могут быть представлены в числовой форме см. статью Как Excel хранит дату и время

При необходимости даты могут быть введены непосредственно в формулу = СУММЕСЛИМН(B6:B17;A6:A17;">=15.01.2010";A6:A17;"<=06.03.2010")

Чтобы вывести условия отбора в текстовой строке используейте формулу ="Объем продаж за период с "&ТЕКСТ(D6;"дд.ММ.гг")&" по "&ТЕКСТ(E6;"дд.ММ.гг")

В последней формуле использован Пользовательский формат .

Задача4 (Месяц)

Немного модифицируем условие предыдущей задачи: найдем суммарные продаж за месяц(см. файл примера Лист Месяц ).

Формулы строятся аналогично задаче 3, но пользователь вводит не 2 даты, а название месяца (предполагается, что в таблице данные в рамках 1 года).

Месяц вводится с помощью Выпадающего списка , перечень месяцев формируется с использованием Динамического диапазона (для исключения лишних месяцев).

Альтернативный вариант

Альтернативным вариантом для всех 4-х задач является применение Автофильтра .

Для решения 3-й задачи таблица с настроенным автофильтром выглядит так (см. файл примера Лист 2 Даты ).

Предварительно таблицу нужно преобразовать в формат таблиц MS EXCEL 2007 и включить строку Итогов.


Комментарии
Только для авторизованных пользователей

Аноним, 4 августа 2016 г.
Чтобы не смущать людей можно в D19 и E19 (лист "2 даты") использовать формулу =">="&ТЕКСТ(D6;"ДД/ММ/ГГГГ") (просто >=15/01/2010 нагляднее, чем >=40193) 😊
Михаил, 12 августа 2016 г.
Согласен, переделаю
Михаил, 12 августа 2016 г.
Переделал )))
Аноним, 18 апреля 2017 г.
Здравствуйте, Михаил! Спасибо за хороший сайт! Подскажите, есть ли способ выбирать данные из "неправильной таблицы" (как у Вас в примере про сводные таблицы). То есть, есть таблица с индексами, сгруппированными по регионам (наименования строк) и по датам (наименования столбцов), нужно в другую таблицу сделать выборку по задаваемым датам и регионам. Представленные выше формулы работают на одном столбце (как я понял). При попытке растянуть Интервал_суммирования на несколько столбцов, формула дала ошибку. Заранее благодарен.
Михаил, 19 апреля 2017 г.
Добрый день, думаю вытащить данные можно из Вашей таблицы. Если актуально, присылайте таблицу на creator@excel2.ru с пояснением требуемого конечного результата.
Аноним, 29 ноября 2017 г.
Здравствуйте, Михаил! Подскажите, пожалуйста в каком направлении искать решение по следующему вопросу: - необходимо просуммировать значения с несколькими критериями в таблице, где есть пустые значения. Необходимо решить данную задачу при помощи формулы массива. Вся сложность заключается в том, что при умножении значения пустой ячейки ("") на какое-либо числовое значение получается не ноль, а #Знач. В результате при суммировании все выражение тоже будет #Знач
Михаил, 29 ноября 2017 г.
Можно обработать в формуле пустое значение ЕСЛИ(ЕПУСТО(А1);0;А1) Если не получается пришлите, пожалуйста, файл в группу https://vk.com/excel2ru
Аноним, 6 марта 2020 г.
[id295933596|Михаил], добрый день! Получилось найти решение? У меня такая же проблем(
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved