Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel Starter 2010 Еще…Меньше
Функция СУММЕСЛИМН — одна из математических и тригонометрических функций, которая суммирует все аргументы, удовлетворяющие нескольким условиям. Например, с помощью функции СУММЕСЛИМН можно найти число всех розничных продавцов, (1) проживающих в одном регионе, (2) чей доход превышает установленный уровень.
Синтаксис
СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
-
=СУММЕСЛИМН(A2:A9; B2:B9; “=Я*”; C2:C9; “Артем”)
-
=СУММЕСЛИМН(A2:A9; B2:B9; “<>Бананы”; C2:C9; “Артем”)
|
Имя аргумента |
Описание |
|---|---|
|
Диапазон_суммирования (обязательный аргумент) |
Диапазон ячеек для суммирования. |
|
Диапазон_условия1 (обязательный аргумент) |
Диапазон, в котором проверяется Условие1.
|
|
Условие1 (обязательный аргумент) |
Условие, определяющее, какие ячейки суммируются в аргументе Диапазон_условия1. Например, условия могут вводится в следующем виде: 32, “>32”, B4, “яблоки” или “32”. |
|
Диапазон_условия2, Условие2, … (необязательный аргумент) |
Дополнительные диапазоны и условия для них. Можно ввести до 127 пар диапазонов и условий. |
Примеры
Чтобы использовать эти примеры в Excel, выделите нужные данные в таблице, щелкните их правой кнопкой мыши и выберите команду Копировать. На новом листе щелкните правой кнопкой мыши ячейку A1 и в разделе Параметры вставки выберите команду Использовать форматы конечных ячеек.
|
Проданное количество |
Продукт |
Продавец |
|---|---|---|
|
5 |
Яблоки |
Артем |
|
4 |
Яблоки |
Ольга |
|
15 |
Артишоки |
Артем |
|
3 |
Артишоки |
Ольга |
|
22 |
Бананы |
Артем |
|
12 |
Бананы |
Ольга |
|
10 |
Морковь |
Артем |
|
33 |
Морковь |
Ольга |
|
Формула |
Описание |
|
|
=СУММЕСЛИМН(A2:A9; B2:B9; “=Я*”; C2:C9; “Артем”) |
Суммирует количество продуктов, названия которых начинаются с Я и которые были проданы продавцом Артем. Подстановочный знак (*) в аргументе Условие1 (“=Я*”) используется для поиска соответствующих названий продуктов в диапазоне ячеек, заданных аргументом Диапазон_условия1 (B2:B9). Кроме того, функция выполняет поиск имени “Артем” в диапазоне ячеек, заданных аргументом Диапазон_условия2 (C2:C9). Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 20. |
|
|
=СУММЕСЛИМН(A2:A9; B2:B9; “<>Бананы”; C2:C9; “Артем”) |
Суммирует количество продуктов, которые не являются бананами и которые были проданы продавцом по имени Артем. С помощью оператора <> в аргументе Условие1 из поиска исключаются бананы (“<>Бананы”). Кроме того, функция выполняет поиск имени “Артем” в диапазоне ячеек, заданных аргументом Диапазон_условия2 (C2:C9). Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 30. |
Распространенные неполадки
|
Проблема |
Описание |
|---|---|
|
Вместо ожидаемого результата отображается 0 (нуль). |
Если выполняется поиск текстовых значений, например имени человека, убедитесь в том, что значения аргументов Условие1, 2 заключены в кавычки. |
|
Неверный результат возвращается в том случае, если диапазон ячеек, заданный аргументом Диапазон_суммирования, содержит значение ИСТИНА или ЛОЖЬ. |
Значения ИСТИНА и ЛОЖЬ в диапазоне ячеек, заданных аргументом Диапазон_суммирования, оцениваются по-разному, что может приводить к непредвиденным результатам при их суммировании. Ячейки в аргументе Диапазон_суммирования, которым присвоено значение ИСТИНА, оцениваются как 1. Ячейки, которым присвоено значение ЛОЖЬ, оцениваются как 0 (ноль). |
Рекомендации
|
Необходимые действия |
Описание |
|---|---|
|
Использование подстановочных знаков |
Подстановочные знаки, такие как вопросительный знак (?) или звездочка (*), в аргументах Условие1, 2 можно использовать для поиска сходных, но не совпадающих значений. Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед вопросительным знаком. Например, формула =СУММЕСЛИМН(A2:A9; B2:B9; “=Я*”; C2:C9; “Арте?”) будет суммировать все значения с именем, начинающимся на “Арте” и оканчивающимся любой буквой. |
|
Различия между функциями СУММЕСЛИ и СУММЕСЛИМН |
Порядок аргументов в функциях СУММЕСЛИ и СУММЕСЛИМН различается. Например, в функции СУММЕСЛИМН аргумент Диапазон_суммирования является первым, а в функции СУММЕСЛИ — третьим. Этот момент часто является источником проблем при использовании данных функций. При копировании и изменении этих похожих формул нужно следить за правильным порядком аргументов. |
|
Одинаковое количество строк и столбцов для аргументов, задающих диапазоны ячеек |
Аргумент Диапазон_условия должен иметь то же количество строк и столбцов, что и аргумент Диапазон_суммирования. |
К началу страницы
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Просмотрите видео об использовании усложненных функций ЕСЛИ, таких как функция СУММЕСЛИМН.
Функция СУММЕСЛИ суммирует только те значения, которые соответствуют одному условию
Функция СУММКВ суммирует несколько значений, предварительно возведя каждое из них в квадрат
Функция СЧЁТЕСЛИ подсчитывает только те значения, которые соответствуют одному условию
Функция СЧЁТЕСЛИМН подсчитывает только те значения, которые соответствуют нескольким условиям
Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздние версии)
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Математические и тригонометрические функции
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Функция СУММЕСЛИМН используется для суммирования переданных значений с учетом нескольких критериев отбора данных, которые указываются в качестве аргументов функции, и возвращает полученное суммарное значение.
Примеры использования функции СУММЕСЛИМН в Excel
В отличие от СУММЕСЛИ, в рассматриваемой функции предусмотрена возможность внесения до 127 критериев отбора данных для суммирования. СУММЕСЛИМН удобна для работы с объемными таблицами, содержащими большое число полей данных. Например, если требуется рассчитать общую сумму средств, полученных в результате поставки определенного типа товара указанной компанией в определенную страну, удобно использовать функцию СУММЕСЛИМН.
Динамический диапазон суммирования по условию в Excel
Пример 1. В таблице содержатся данные о успеваемости студентов по некоторому предмету в университете. Определить итоговую оценку для студентов с фамилией, начинающейся на букву «А» при условии, что минимальный балл должен быть не менее 5 (успеваемость студентов оценивается по 10-бальной шкале).
Вид таблицы данных:
Для расчета суммарного балла согласно установленным критериям используем следующую формулу:
Описание аргументов:
- C3:C14 – диапазон ячеек с баллами, из которых будут автоматически выбраны значения для расчета суммы, которые соответствуют установленным критериям;
- C3:C14 – первый диапазон ячеек, к которому будет применен первый критерий;
- “>5” – первое условие отбора значений из указанного выше диапазона;
- B3:B14 – второй диапазон ячеек (с фамилиями студентов), к которому будет применен второй критерий;
- “А*” – второе условие отбора значений (все фамилии, которые начинаются с буквы «А»).
Результат расчетов:
В результате формула автоматически определила динамически изменяемый диапазон в соответствии с условиями отбора значений и просуммировала его.
Выборочное суммирование по условию в Excel
Пример 2. В таблице указаны данные об отгрузках трех товаров в различные страны на протяжении трех месяцев. Определить суммарный доход от отгрузок товара №2 в Казахстан за июнь и август.
Вид таблицы данных:
Для расчета итоговой суммы используем формулу:
Результат расчетов:
Примечание: формула выглядит громоздкой несмотря на то, что для столбца «Месяц» было использовано всего 2 критерия отбора. Формулы с несколькими критериями для одного диапазона данных можно существенно сократить способом, который будет показан в следующем примере.
СУММЕСЛИМН для суммирования значений по нескольким условиям в Excel
Пример 3. Используя таблицу из второго примера определить суммарной прибыли от поставок товара №1 в Китай и Грузию на протяжении всего периода (трех месяцев).
Для нахождения искомого значения используем формулу массива (для ввода жмем CTRL+SHIFT+Enter):
Функция СУММЕСЛИМН возвращает массив значений для критериев «Китай» и «Грузия» соответственно, которые суммируются функцией СУММ.
Примечание: если критерии переданы в качестве константы массива для двух и более пар диапазон_условияN; условиеN, результат выполнения формулы будет некорректным.
Полученное значение:
Особенности использования функции СУММЕСЛИМН в Excel
Функция имеет следующую синтаксическую запись:
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
Описание аргументов:
- диапазон_суммирования – обязательный аргумент, принимающий ссылку на ячейки с числами, для которых будет вычислено суммарное значение с учетом установленных критериев;
- диапазон_условия1 – обязательный аргумент, принимающий ссылку на ячейки, в которых содержатся данные для проверки на соответствие заданному критерию;
- условие1 – обязательный аргумент, принимающий ссылку на ячейку с условием поиска, текстовую запись логического выражения (например, «>=100» или «<>0»), проверяемый текст (например, «Продавец_1», «Товар_1») или числовое значение (Например, 20), определяющие ячейки в диапазоне условия1, для которых будут отобраны и просуммированы данные из диапазона суммирования.
Последующие аргументы являются необязательными для заполнения, а их смысл соответствует аргументам диапазон_условия1; условие1. Всего может быть задано до 127 условий отбора данных для суммирования.
Примечания:
- Если в качестве аргумента диапазон_суммирования была передана ссылка на диапазон ячеек, содержащие текстовые строки или пустые значения, функция СУММЕСЛИМН будет их игнорировать.
- В качестве условий суммирования данных могут выступать числовые значения, логические выражения, текстовые строки, формулы, ссылки на ячейки, содержащие условия.
- Условия проверки в виде текста или выражений с логическими и математическими символами должны быть указаны в кавычках.
- Максимальная длина условия, переданного в качестве аргумента условие1 (условие2 и т. д.) не должна превышать 255 символов.
- Для создания неточных текстовых критериев поиска требуемых значений можно использовать подстановочные знаки «?» и «*», замещающие один или несколько символов соответственно.
- Если в диапазоне ячеек, переданных в качестве аргумента диапазон_суммирования, есть ячейки, содержащие логические ИСТИНА или ЛОЖЬ, они будут автоматически преобразованы к числовым значениям 1 и 0 соответственно и будут учтены в итоговой сумме.
- Число ячеек в диапазонах суммирования и условиях должно быть одинаковым, иначе функция СУММЕСЛИМН вернет код ошибки #ЗНАЧ!.
В предыдущей статье мы рассмотрели функцию =СУММЕСЛИ(), которая справляется с задачей, если вам необходимо произвести сложение значений соответствующих определенному одному условию. Что делать, если у вас несколько условий? Как получить общий объем продаж какого-то продукта, реализованного продавцом в определенный месяц? И вот здесь на помощь приходит функция =СУММЕСЛИМН()
В конце статьи рассмотрим один очень полезный прием использования функции =СУММЕСЛИМН(), который я уверен вам очень пригодится
Функция =СУММЕСЛИМН() расширяет возможности функции =СУММЕСЛИ() , позволяя указать от 2 до 127 критериев, а не только один.
И как обычно перед использованием функции давайте разберем ее состав и принцип работы.
=СУММЕСЛИМН() ищет определенные критерии в двух или более полях записи, и только если он находит соответствие для каждого указанного поля, данные для этой записи суммируются.
= СУММЕСЛИМН (диапазон суммирования; диапазон 1-го условия; критерий 1-го условия; диапазон 2-го условия; критерий 2-го условия; и т.д.)
Диапазон суммирования – непосредственно сам диапазон, в котором нам необходимо произвести суммирование данных на основании заданных условий;
Диапазон 1-го условия – диапазон ячеек, содержащий критерии, на основании которых нам необходимо произвести отбор ячеек для суммирования, отвечающие первому условию;
Критерий 1-го условия – непосредственно сам критерий 1-го условия. Он может быть в форме числа, выражения, ссылки на ячейку, текста или функции.
Диапазон 2-го условия – диапазон ячеек, содержащий критерии, на основании которых нам необходимо произвести отбор ячеек для суммирования, отвечающие второму условию;
Критерий 2-го условия – непосредственно сам критерий 2-го условия. Он может быть в форме числа, выражения, ссылки на ячейку, текста или функции.
Важно. Если критерии текстовые, логические или дата вам необходимо заключить их в двойные кавычки. Например – “Картофель” или “01.01.2021” или “>01.01.2021”
В качестве примера возьмем приведенные в таблице данные
Из нашего списка давайте попробуем узнать какой составил объем продаж Лука в Сентябре, для удобства восприятия информации давайте внизу таблицы визуализируем наши условия в виде небольшой таблички
Затем введем формулу, которая будет выглядеть следующим образом
=СУММЕСЛИМН(E2:E21;A2:A21;B24;D2:D21;B25)
В результате мы получили сумму тех чисел, которые соответствуют заданному условию.
Полезный прием =СУММЕСЛИМН() с и/или логикой
Допустим на примере нашей таблицы перед Вами встала задача просуммировать все значения на основании нескольких условий, находящихся в одном столбце.
Допустим нам необходимо узнать общий объем продаж по Картофелю и Моркови.
Для этого мы можем найти сумму объема продаж отдельно Моркови и отдельно Картофеля, а потом их сложить.
Отлично!!!
Но я вам предложу более простой и быстрый способ это сделать, потому-что в вашем случае это может быть куда больше критериев в одном столбце.
И в данном случае мы будем использовать две функции, которые производят суммирование =СУММ() и =СУММЕСЛИМН()
И формула наша будет выглядеть следующим образом:
=СУММ(СУММЕСЛИМН(E2:E21;D2:D21;{“Картофель”;”Морковь”}))
Чтобы понять эту формулу, вам нужно разделить ее на три разные части.
- Во- первых , необходимо понять, что в этой формуле мы используем два разных критерия, используя концепцию массива
- Во- вторых , когда мы указали два разных значения с помощью массива, функция =СУММЕСЛИМН() должна искать оба значения по отдельности.
- В- третьих , даже после использования формулы массива функция =СУММЕСЛИМН() не может вернуть сумму обоих значений в одной ячейке. Вот почему ее необходимо использовать внутри функции =СУММ()
С помощью описанной выше концепции вы можете получить общую сумму для обоих типов продуктов в одной ячейке. Такое сочетание функций будет одинаково работать и с =СУММЕСЛИ()
Пробуйте использовать эту функцию в сочетании с другими, не бойтесь экспериментировать и у Вас все получится!
https://t.me/Excel_how_it_works
Skip to content
В этом руководстве объясняется различие между функциями СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) с точки зрения их синтаксиса и использования, а также приводятся примеры формул для суммирования значений с несколькими критериями в Excel 2016, 2013, 2010, 2007, 2003 и ниже.
Как известно, Microsoft Excel предоставляет множество функций для выполнения различных расчетов с данными. Мы уже рассмотрели СУММЕСЛИ, которая суммирует числа, соответствующие указанным критериям. Теперь пришло время перейти к расширенной версии этой функции СУММЕСЛИМН, которая позволяет найти сумму по нескольким условиям.
Те, кто знаком с функцией СУММЕСЛИ, могут подумать, что преобразование ее в СУММЕСЛИМН потребует лишь букв “МН” и некоторого количества дополнительных критериев. Это может показаться вполне логичным… но «логично» – это не всегда имеет место при работе с Microsoft:)
Как работает СУММЕСЛИМН?
При помощи СУММЕСЛИМН можно найти сумму величин, для которых есть много условий. Она появилась впервые в MS Excel 2007, поэтому вы можете использовать ее во всех современных версиях программы.
По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:
СУММЕСЛИМН(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2],…)
Первые 3 аргумента являются обязательными, а вот дополнительные диапазоны и связанные с ними условия являются необязательными.
диапазон_суммирования – требуется одна или более ячеек для суммирования. Это может быть отдельная клеточка, область или именованный диапазон. Суммируются только ячейки с чисами; пустые и текстовые значения игнорируются.
диапазон_условия1 – обязательный первый диапазон, который должен быть оценен по соответствующим критериям.
условие1– обязательное первое условие, которое должно быть выполнено. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, “> = 10”, A1, “яблоко” или СЕГОДНЯ().
Все, что следует далее – это дополнительные диапазоны и связанные с ними критерии. Они не являются обязательными, но если у вас только одно ограничение, то зачем вам эта функция? Просто используйте СУММЕСЛИ. Тем не менее, вы можете использовать до 127 пар диапазон/условие.
Важно! Функция СУММЕСЛИМН работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным критериям (все требования соблюдаются для этой ячейки).
Использование СУММЕСЛИМН и СУММЕСЛИ в Excel – что нужно запомнить?
Поскольку целью этого руководства является охват всех возможных способов суммирования значений по большому количеству ограничений, мы обсудим примеры выражений с обеими функциями – СУММЕСЛИМН и СУММЕСЛИ с несколькими критериями. Чтобы использовать их правильно, вам необходимо четко понимать, что общего между этими двумя функциями и чем они отличаются.
Хотя общая часть ясна – схожий пункт назначения и параметры – но различия не так очевидны, хотя и достаточно существенны.
1. Порядок аргументов
Аргументы применяются по-разному. В частности, диапазон_сумирования является 1-м параметром в СУММЕСЛИ, но является третьим в СУММЕСЛИМН.
На первый взгляд может показаться, что Microsoft намеренно усложняет процесс обучения для своих пользователей. Однако при ближайшем рассмотрении вы увидите причины этого. Дело в том, что этот диапазон является необязательным в СУММЕСЛИ. Если вы его опустите, то нет никаких проблем, ваша формула будет складывать в диапазоне поиска (первый параметр).
В СУММЕСЛИМН он, напротив, очень важен и обязателен, и поэтому и стоит первым. Вероятно, ребята из Microsoft подумали, что после добавления 10- й или 100- й пары диапазон/критерий кто-то может забыть указать диапазон для суммирования:)
2. Диапазон суммирования и область критериев должны быть одинакового размера
В функции СУММЕСЛИ эти аргументы не обязательно должны иметь одинаковую размерность. Достаточно указать начальную точку. В СУММЕСЛИМН они должны содержать одинаковое количество строк и столбцов.
Выражение =СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F22;I3) вернет сообщение об ошибке #ЗНАЧ!, так как второй параметр поиска (F2:F22) не совпадает по размеру с остальными (E2:E21) и (C2:C21).
Хорошо, хватит стратегии (т.е. теории), давайте перейдем к тактике (к примерам).
Суммирование с множеством условий.
Имеются данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».
Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.
=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I3)
В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.
В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.
Если ОБА эти требования выполняются, то такой заказ нам подходит, и его стоимость мы учтём.
Как видите, у нас найдено 2 совпадения, в которых был продан молочный шоколад.
Использование операторов сравнения.
Рассчитаем по покупателю «Красный» стоимость заказов, в которых было более 100 единиц товара. Как видим, здесь нужно использовать и текстовый, и числовой критерий.
Критерии можно записать в саму формулу, и выглядеть это будет так:
=СУММЕСЛИМН(E2:E21;B2:B21;”Красный”;D2:D21;”>100”)
Но более рационально использовать ссылки, как это и сделано на рисунке:
=СУММЕСЛИМН(E2:E21;B2:B21;I2;D2:D21;I4)
Примечание. Обратите внимание, что в формулах логические выражения с операторами сравнения всегда должны быть заключены в двойные кавычки (“”).
Синтаксис, а также работа с числами, текстом и датами у этой функции точно такие же, как и СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу о условном суммировании.
А как еще можно решить нашу задачу?
Способ 2. Используем функцию СУММПРОИЗВ.
Разберем подробнее, как работает СУММПРОИЗВ():
=СУММПРОИЗВ(–(B2:B21=$I$12);–(D2:D21>I13);E2:E21)
Результатом вычисления B2:B21=$I$12 является массив
{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}.
ИСТИНА означает соответствие кода покупателя условию, т.е. слову Красный. Массив этот можно увидеть, выделив в строке формул B2:B21=$I$12, а затем нажав F9.
А что за странные знаки «минус» перед этими выражениями? Дело в том, что нам необходимы не эти логические выражения, а числа, чтобы их затем можно было перемножать и складывать. Если Эксель производит математическую операцию с логическим выражением, то он автоматически преобразует его в число. А знак минус означает умножение на -1. А если дважды умножить на -1, то число в результате не изменится. Это мы помним еще из школьной математики 😊
И в результате логический массив превратится в массив чисел {0:1:0:0:0:0:0:0:0:0:0:0:1:0:0:0:1:0:0:0}.
Результатом вычисления D2:D21>I13 является массив
{ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}.
ИСТИНА соответствует ограничению «количество больше 100». Здесь мы также применяем двойное отрицание, чтобы преобразовать логические переменные в числа.
И, наконец, результатом вычисления В2:В13 является массив {11250:23210:12960:3150:5280:9750:3690:18300:5720:6150: 8400:2160:7200:1890:17050:3450:15840:2250:7200:8250}, т.е. просто числа из столбца E.
Результатом поэлементного умножения этих трех массивов является {0:23210:0:0:0:0:0:0:0:0:0:0:0:0:0:0:15840:0:0:0}. Суммируем эти произведения и получаем 39050.
Способ 3. Формула массива.
И еще один вариант расчета – применим формулу массива. В I14 запишем:
=СУММ((B2:B21=I12)*(D2:D21>I13)*(E2:E21))
Не забудьте в конце нажать комбинацию клавиш CTRL+SHIFT+ENTER, чтобы обозначить это выражение как формулу массива. Фигурные скобки в начале и в конце программа добавит автоматически. Вновь получим результат 39050.
Способ 4. Автофильтр.
Еще один альтернативный вариант – применение автофильтра. Для этого преобразуйте диапазон данных A1:F21 в «умную» таблицу. Напомню, что для этого в меню «Главная» выберите «Форматировать как таблицу». После этого добавьте в нее строку итогов (вкладка «Конструктор») и установите необходимые фильтры.
Без всяких формул итог по отфильтрованным строкам будет определён.
Как СУММЕСЛИМН работает с датами?
Если вы хотите отобрать и сложить какие-то показатели в определенном временном интервале на основе текущей даты, используйте функцию СЕГОДНЯ() в ваших ограничениях, как это показано ниже.
Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце А попадает в последние 7 дней, включая сегодняшний день (предполагается, что сегодня 7 февраля):
=СУММЕСЛИМН(D2:D21;A2:A21;”<=”&СЕГОДНЯ();A2:A21;”>=”&СЕГОДНЯ()-6)
Замечание. Когда вы при составлении ограничения используете другую функцию Excel вместе с логическим оператором, нужно использовать амперсанд (&) для объединения всего выражения в виде текста, например “<=”&СЕГОДНЯ().
Аналогичным образом вы можете использовать функцию Excel СУММЕСЛИ для суммирования каких-то показателей в заданном диапазоне дат. Например, следующая формула также решит нашу задачу:
=СУММЕСЛИ(A2:A21;”>=”&СЕГОДНЯ()-6;D2:D21) – СУММЕСЛИ(A2:A21;”<=”&СЕГОДНЯ();D2:D21)
Однако СУММЕСЛИМН сложение делает гораздо проще и понятнее, не так ли?
Суммирование по пустым и непустым ячейкам.
При анализе отчетов и других данных вам часто может потребоваться суммировать данные, соответствующие пустым или непустым клеткам таблицы.
| Критерии | Описание | Содержание | |
| Пустые ячейки | “=” | Суммируйте числа, соответствующие пустым, которые не содержат абсолютно ничего – ни формулы, ни строки нулевой длины. | =СУММЕСЛИМН(C2:C10;A2:A10;”=”;B2:B10,”=”) Суммируйте в C2:C10, если соответствующие ячейки в столбцах A и B абсолютно пусты. |
| «» | Суммируйте числа, соответствующие «визуально» пустым, включая те, которые содержат пустые строки, возвращаемые какой-либо другой функцией Excel (например, ячейки с формулой вроде = “”). | =СУММЕСЛИМН(C2:C10;A2:A10;””;B2:B10,””) Суммируйте в C2:C10 с теми же параметрами, что и в приведенной выше формуле, но с пустыми строками. |
|
| Непустые ячейки | “<>” | Суммируйте числа, соответствующие непустым, включая строки нулевой длины. | =СУММЕСЛИМН(C2:C10;A2:A10;”<>”;B2:B10,”<>”) Суммируйте в C2: C10, если соответствующие ячейки в столбцах A и B не пусты, включая ячейки с пустыми строками. |
| Суммируйте числа, соответствующие непустым, не включая строки нулевой длины. | =СУММ(C2:C10) – СУММЕСЛИМН(C2:C10;A2:A10;””;B2:B10,””) или {=СУММ((C2:C10)*(ДЛСТР(A2:A10)>0)*(ДЛСТР(B2: B10)>0))} Если в столбцах A и B содержится текст ненулевой длины, тогда соответствующее число из C складывается. Внимание! Это формула массива! Фигурные скобки вводить не нужно! |
А теперь давайте посмотрим, как вы можете использовать формулу СУММЕСЛИМН с «пустыми» и «непустыми» ячейками для реальных данных.
По покупателю «Красный» рассчитаем количество товара в невыполненных заказах. Для этого в столбце B ищем соответствующее название клиента, а в F – пустую ячейку. Если оба требования совпадают, складываем количество товара из столбца D.
=СУММЕСЛИМН(D2:D21;F2:F21;””;B2:B21;”Красный”)
или
=СУММЕСЛИМН(D2:D21;F2:F21;”=”;B2:B21;”Красный”)
Каждое из этих выражений дает верный результат – 144 единицы в заказе от 4 февраля.
Сумма нескольких условий.
А теперь давайте рассчитаем общую стоимость выполненных заказов по двум товарам.
Если мы просто добавим второй критерий в I3, и вместо I2 используем область I2:I3, то расчет будет неверным, поскольку в C2:C21 будем искать товар, в названии которого есть И «черный», И «молочный» одновременно. Ведь таких просто нет.
Поэтому первый вариант расчета таков:
=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I4)+СУММЕСЛИМН(E2:E21;C2:C21;I3;F2:F21;I4)
Просто складываем выполненные заказы сначала по первому, а затем по второму наименованию.
Второй вариант: используем элемент массива критериев и функцию СУММ.
=СУММ(СУММЕСЛИМН(E2:E21;C2:C21;{“*молочный*”;”*черный*”};F2:F21;I4))
Как видите, результаты получены одинаковые. Выбирайте способ, который будет для вас проще и понятнее.
Ещё примеры расчета суммы:
По названиям функций из заголовка статьи уже можно понять, для чего они предназначены. Первая суммирует числа указанного диапазона только в том случае, если они попадают под критерий. СУММЕСЛИМН работает точно так же, но с возможностью указать несколько разных аргументов.
Я покажу, как устроен синтаксис этих функций в Microsoft Excel и для каких задач их можно применить.
Разбор синтаксиса СУММЕСЛИ
Начать стоит с правил ввода составляющих функции СУММЕСЛИ. Из ее частей формируется СУММЕСЛИМН, поэтому детальное описание второй опустим, а вернемся к ней только в завершающем разделе статьи. Стандартная запись функции выглядит как =СУММЕСЛИ(A1:A10;”>1″).
Значит, что суммироваться будут только те ячейки, которые подпадают под указанный критерий, то есть больше 1. Это удобно, например, когда вам нужно посчитать, сколько у вас всего значений, меньше, больше или равняются конкретному. Если вызвать окно «Аргументы функции», вы увидите более понятный вариант записи и можете использовать его, указав диапазон и критерий.
Задать значение для «Диапазон_суммирования» нужно только в том случае, если вы ищете значения больше или меньше конкретного, но при этом добавлять в сумму необходимо ячейки из другого столбца (пока это звучит непонятно, но далее вы увидите такое представление функции на наглядном примере).
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Запись СУММЕСЛИ при неравенстве
Еще раз пройдемся по базовому типу использования СУММЕСЛИ. После объявления функции укажите диапазон для проверки в виде A1:A20;. Обязательно поставьте точку с запятой, ведь это нужно для корректной работы функции.
После этого введите вручную сам критерий (нужно именно указывать число, а не брать его из конкретной ячейки с записью A1). Выберите знак больше, меньше, равно или <>, чтобы указать, что число не равняется конкретному.
Функция берет указанный вами диапазон и ищет там значения по критерию. Если значения попадают, они включаются в сумму. Затем происходит обращение к следующим ячейкам и так до конца диапазона. В итоге вы видите сумму только из тех чисел, которые соответствуют введенному вами неравенству. 
Использование СУММЕСЛИ с текстом
В качестве критерия можно использовать текст, вводя слово в точности так же, как оно указано и в ячейках. Для реализации мы будем использовать диапазон суммирования, который опустили при разборе предыдущего примера.
Допустим, у вас есть столбец, в котором несколько строк с названием «Морковь». Нужно сделать так, чтобы функция посчитала сумму всего количества морковок, но не брала во внимание другие овощи и фрукты, записанные в том же столбце. Для этого отлично подойдет рассматриваемая функция, а ее запись в таком случае будет выглядеть так:
-
Объявите саму функцию, после чего в качестве диапазона выберите столбец с наименованиями продуктов.
-
Затем вместо неравенства введите текст для поиска.
-
Поставьте после кавычки точку с запятой и укажите диапазон суммирования – тот столбец, где расположено количество морковок.
-
Это то самое значение «Диапазон_суммирования», о котором я говорил при просмотре окна «Аргументы функции».
-
На следующем скриншоте вы видите, что морковок в списке всего две, каждая запись имеет значение 12, а это значит, что в сумму попадают только эти два числа, в результате получается 24.
Вы можете использовать такой подход, например, чтобы посчитать количество рабочих дней конкретного человека, получить результаты по месяцам или пройтись по количеству одинаковой продукции, записанной в столбце. Ничего сложного в объединении СУММЕСЛИ с текстом нет, в чем вы и убедились выше.
Использование функции СУММЕСЛИМН
Последний пример – функция СУММЕСЛИМН, которая похожа на предыдущую, но позволяет работать со множеством аргументов. Возьмем таблицу, где указано наименование продукции с некоторыми одинаковыми названиями, есть разная цена и количество.
Посчитаем количество груш, но только тех, чья цена будет выше 10 за единицу. Соответственно, это только пример, а вы можете использовать функцию для совершенно разных задач как при работе с текстовыми значениями, так и неравенствами.
-
Объявите функцию СУММЕСЛИМН и сначала запишите тот диапазон, который будете считать. В моем случае это количество груш.
-
Далее нужно вычленить из списка продукции только груши, для чего используйте критерий текста точно так же, как это уже было показано в предыдущем примере.
-
Второй критерий – цена, которая должна превышать 10 за единицу. Соответственно, впишите блок с неравенством A1:A10;”>10″, где A1:A10 – диапазон ячеек, а >10 – критерий.
-
Нажмите клавишу Enter и ознакомьтесь с результатом. На следующем скриншоте вы видите, что груш с ценой больше 10 довольно много, все их количество суммировалось и отображается в отдельной ячейке.
При первой записи у вас могут возникнуть трудности с правильным написанием функции, ведь она содержит много условий. Я оставлю вам ее отдельно, чтобы вы могли скопировать ее и вставить, подставив вместо текущих диапазонов ячеек свои: =СУММЕСЛИМН(B2:B25;A2:A25;”Груши”;C2:C25;”>10″). Не забывайте о том, что первый диапазон – то, что вы считаете, далее идет первый критерий – диапазон с названием столбца, потом второй – диапазон с неравенством.
Это лишь несколько примеров использования функций СУММЕСЛИ и СУММЕСЛИМН в Excel. Полученные знания вы можете использовать в своих целях, выполняя необходимые расчеты и упрощая процесс взаимодействия с электронной таблицей.
