Как работает функция суммеслимн в excel примеры

Использование функции СУММЕСЛИМН в Excel ее особенности примеры

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

Синтаксис СУММЕСЛИМН и распространенные ошибки

Аргументы функции СУММЕСЛИМН:

1. Диапазон ячеек для нахождения суммы. Обязательный аргумент, где указаны данные для суммирования.
2. Диапазон ячеек для проверки условия 1. Обязательный аргумент, к которому применяется заданное условие поиска. Найденные в этом массиве данные суммируются в пределах диапазона для суммирования (первого аргумента).
3. Условие 1. Обязательный аргумент, составляющий пару предыдущему. Критерий, по которому определяются ячейки для суммирования в диапазоне условия 1. Условие может иметь числовой формат, текстовый; «воспринимает» математические операторы. Например, 45; « , = и др.).



Примеры функции СУММЕСЛИМН в Excel

У нас есть таблица с данными об оказанных услугах клиентам из разных городов с номерами договоров.

Предположим, нам необходимо подсчитать количество услуг в определенном городе с учетом вида услуги.

Как использовать функцию СУММЕСЛИМН в Excel:

1. Вызываем «Мастер функций». В категории «Математические» находим СУММЕСЛИМН. Можно поставить в ячейке знак «равно» и начать вводить название функции. Excel покажет список функций, которые имеют в названии такое начало. Выбираем необходимую двойным щелчком мыши или просто смещаем курсор стрелкой на клавиатуре вниз по списку и жмем клавишу TAB.
2. В нашем примере диапазон суммирования – это диапазон ячеек с количеством оказанных услуг. В качестве первого аргумента выбираем столбец «Количество» (Е2:Е11). Название столбца не нужно включать.
3. Первое условие, которое нужно соблюсти при нахождении суммы, – определенный город. Диапазон ячеек для проверки условия 1 – столбец с названиями городов (С2:С11). Условие 1 – это название города, для которого необходимо просуммировать услуги. Допустим, «Кемерово». Условие 1 – ссылка на ячейку с названием города (С3).
4. Для учета вида услуг задаем второй диапазон условий – столбец «Услуга» (D2:D11). Условие 2 – это ссылка на определенную услугу. В частности, услугу 2 (D5).
5. Вот так выглядит формула с двумя условиями для суммирования: =СУММЕСЛИМН(E2:E11;C2:C11;C3;D2:D11;D5).

Результат расчета – 68.

Гораздо удобнее для данного примера сделать выпадающий список для городов:

Теперь можно посмотреть, сколько услуг 2 оказано в том или ином городе (а не только в Кемерово). Формулу немного видоизменим: =СУММЕСЛИМН($E$2:$E$11;$C$2:$C$11;F$2;$D$2:$D$11;$D$5).

Все диапазоны для суммирования и проверки условий нужно закрепить (кнопка F4). Условие 1 – название города – ссылка на первую ячейку выпадающего списка. Ссылку на условие 2 тоже делаем постоянной. Для проверки из списка городов выберем «Кемерово»:

Результат тот же – 68.

По такому же принципу можно сделать выпадающий список для услуг.

Функция СУММЕСЛИМН и суммирование по нескольким условиям в 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. В качестве условий суммирования данных могут выступать числовые значения, логические выражения, текстовые строки, формулы, ссылки на ячейки, содержащие условия.
3. Условия проверки в виде текста или выражений с логическими и математическими символами должны быть указаны в кавычках.
4. Максимальная длина условия, переданного в качестве аргумента условие1 (условие2 и т. д.) не должна превышать 255 символов.
5. Для создания неточных текстовых критериев поиска требуемых значений можно использовать подстановочные знаки «?» и «*», замещающие один или несколько символов соответственно.
6. Если в диапазоне ячеек, переданных в качестве аргумента диапазон_суммирования, есть ячейки, содержащие логические ИСТИНА или ЛОЖЬ, они будут автоматически преобразованы к числовым значениям 1 и 0 соответственно и будут учтены в итоговой сумме.
7. Число ячеек в диапазонах суммирования и условиях должно быть одинаковым, иначе функция СУММЕСЛИМН вернет код ошибки #ЗНАЧ!.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Функция СУММЕСЛИМН в Excel с примером использования в формуле

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

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

У нас есть список сотрудников, выбирая город нам необходимо посчитать сумму подключенных услуг по сотрудникам и видам услуг. То есть нам необходимо заполнить вот такую таблицу (то что выделено желтым).

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

Для наглядности я перенес данную таблицу на один лист с исходными данными.

Синтаксис функции СУММЕСЛИМН:

СУММЕСЛИМН( диапазон_суммирования ; диапазон_условий1 ; условия1 ;[ диапазон_условий2 ; условия2 ];…)
диапазон_суммирования — В нашем случае нам необходимо просуммировать количество подключенных услуг, поэтому это столбец Количество и диапазон Е2:E646

Далее указываются условия по которым необходимо просуммировать услуги. У нас три условия:

1. должна совпадать фамилия сотрудника;
2. должна совпадать услуга;
3. должен совпадать город.

диапазон_условий1 — первое условие у нас сотрудники и диапазон условий это столбец с именами ФИО сотрудников A2:A646

условия1 — это сам сотрудник, так как мы начинаем прописывать формулу напротив сотрудника Апанасенко Е.П то и условия1 у нас будет ссылка на его ячейку G3

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

Продолжим, следующая условие это услуга

диапазон_условий2 — это столбец с услугами D2:D646

условия2 — это ссылка на услугу 1, то есть H2

Вот как должна выглядеть наша формула на текущий момент:

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2

Добавляем третье условие по городам

диапазон_условий3 — диапазон условий по городам это столбец «Город клиента» и диапазон B2:B646

условия3 — это ссылка на город в раскрывающемся списке G1

Все три условия учтены и формула наша выглядит следующим образом, скобку можно закрыть.

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2; B2:B646; G1 )

Для первой ячейке мы посчитали значения, но теперь нам необходимо протянуть формулу на остальные ячейки. Для этого нам необходимо закрепить некоторые диапазоны.

Во-первых все диапазоны условий у нас не двигаются и постоянны поэтому закрепим их с помощью знака доллара (выделить данный диапазон в формуле и нажать клавишу F4):

Диапазон суммирования у нас так же постоянный E2:E646 → $E$2:$E$646

Так же условия3 по городу G1 y нас всегда находится только в ячейке G1 и не должен смещаться при протягивании, поэтому так же закрепляем данную ячейку

G1 → $G$1

Услуги (условия2) при протягивании вправо должны меняться по столбцам, а вот строка при протягивании вниз не должна меняться, поэтому закрепляем только строку

H2 → H$2

Ссылка на фамилии наоборот должна меняться при протягивании вниз, но не должна меняться при протягивании формулы вправо, поэтому закрепляем только столбец

G3 → $G3

Итоговая формула будет выглядеть следующим образом

=СУММЕСЛИМН( $E$2:$E$646 ; $A$2:$A$646 ; $G3 ; $D$2:$D$646 ; H$2; $B$2:$B$646 ;$ G$1 )

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

Как использовать функции СУММЕСЛИ и СУММЕСЛИМН в Excel

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

Я покажу, как устроен синтаксис этих функций в Microsoft Excel и для каких задач их можно применить.

Разбор синтаксиса СУММЕСЛИ

Начать стоит с правил ввода составляющих функции СУММЕСЛИ. Из ее частей формируется СУММЕСЛИМН, поэтому детальное описание второй опустим, а вернемся к ней только в завершающем разделе статьи. Стандартная запись функции выглядит как =СУММЕСЛИ(A1:A10;»>1″).

Значит, что суммироваться будут только те ячейки, которые подпадают под указанный критерий, то есть больше 1. Это удобно, например, когда вам нужно посчитать, сколько у вас всего значений, меньше, больше или равняются конкретному. Если вызвать окно «Аргументы функции», вы увидите более понятный вариант записи и можете использовать его, указав диапазон и критерий.

Задать значение для «Диапазон_суммирования» нужно только в том случае, если вы ищете значения больше или меньше конкретного, но при этом добавлять в сумму необходимо ячейки из другого столбца (пока это звучит непонятно, но далее вы увидите такое представление функции на наглядном примере).

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей

Запись СУММЕСЛИ при неравенстве

Еще раз пройдемся по базовому типу использования СУММЕСЛИ. После объявления функции укажите диапазон для проверки в виде A1:A20;. Обязательно поставьте точку с запятой, ведь это нужно для корректной работы функции.

После этого введите вручную сам критерий (нужно именно указывать число, а не брать его из конкретной ячейки с записью A1). Выберите знак больше, меньше, равно или <>, чтобы указать, что число не равняется конкретному.

Функция берет указанный вами диапазон и ищет там значения по критерию. Если значения попадают, они включаются в сумму. Затем происходит обращение к следующим ячейкам и так до конца диапазона. В итоге вы видите сумму только из тех чисел, которые соответствуют введенному вами неравенству.

Использование СУММЕСЛИ с текстом

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

Допустим, у вас есть столбец, в котором несколько строк с названием «Морковь». Нужно сделать так, чтобы функция посчитала сумму всего количества морковок, но не брала во внимание другие овощи и фрукты, записанные в том же столбце. Для этого отлично подойдет рассматриваемая функция, а ее запись в таком случае будет выглядеть так:

1. Объявите саму функцию, после чего в качестве диапазона выберите столбец с наименованиями продуктов.
2. Затем вместо неравенства введите текст для поиска.
3. Поставьте после кавычки точку с запятой и укажите диапазон суммирования – тот столбец, где расположено количество морковок.
4. Это то самое значение «Диапазон_суммирования», о котором я говорил при просмотре окна «Аргументы функции».
5. На следующем скриншоте вы видите, что морковок в списке всего две, каждая запись имеет значение 12, а это значит, что в сумму попадают только эти два числа, в результате получается 24.

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

Использование функции СУММЕСЛИМН

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

Посчитаем количество груш, но только тех, чья цена будет выше 10 за единицу. Соответственно, это только пример, а вы можете использовать функцию для совершенно разных задач как при работе с текстовыми значениями, так и неравенствами.

1. Объявите функцию СУММЕСЛИМН и сначала запишите тот диапазон, который будете считать. В моем случае это количество груш.
2. Далее нужно вычленить из списка продукции только груши, для чего используйте критерий текста точно так же, как это уже было показано в предыдущем примере.
3. Второй критерий – цена, которая должна превышать 10 за единицу. Соответственно, впишите блок с неравенством A1:A10;»>10″, где A1:A10 – диапазон ячеек, а >10 – критерий.
4. Нажмите клавишу Enter и ознакомьтесь с результатом. На следующем скриншоте вы видите, что груш с ценой больше 10 довольно много, все их количество суммировалось и отображается в отдельной ячейке.

При первой записи у вас могут возникнуть трудности с правильным написанием функции, ведь она содержит много условий. Я оставлю вам ее отдельно, чтобы вы могли скопировать ее и вставить, подставив вместо текущих диапазонов ячеек свои: =СУММЕСЛИМН(B2:B25;A2:A25;»Груши»;C2:C25;»>10″). Не забывайте о том, что первый диапазон – то, что вы считаете, далее идет первый критерий – диапазон с названием столбца, потом второй – диапазон с неравенством.

Это лишь несколько примеров использования функций СУММЕСЛИ и СУММЕСЛИМН в Excel. Полученные знания вы можете использовать в своих целях, выполняя необходимые расчеты и упрощая процесс взаимодействия с электронной таблицей.