Как сделать формулу суммеслимн
Добавил пользователь Евгений Кузнецов Обновлено: 04.10.2024
Рассмотрим пример использования функции СУММЕСЛИМН в Excel, которая позволяет просуммировать ячейки по нескольким критериям.
Аналогом данной функции для одного критерия является функция СУММЕСЛИ.
Описание функции СУММЕСЛИМН
СУММЕСЛИМН(Диапазон_суммирования; Диапазон_условия1; Условие1; [Диапазон_условия2;Условие2]; …)
Суммирует ячейки, удовлетворяющие заданному набору условий.
- Диапазон суммирования(обязательный аргумент) — диапазон ячеек, который подлежит суммированию;
- Диапазон условия 1(обязательный аргумент) — диапазон проверяемых ячеек, оцениваемый условием 1;
- Условие 1(обязательный аргумент) — условие, определяющее какие ячейки нужно суммировать;
- Диапазон условия 2, Условие 2(необязательный аргумент) — дополнительные диапазоны и условия для них.
Пример использования функции СУММЕСЛИМН
Предположим у нас есть данные по продажам продуктов в магазинах в разных городах:
Одной из самых популярных задач в Excel является суммирование по условию, для этого используются функции СУММЕСЛИ (в случае если условие одно) и СУММЕСЛИМН (если условий несколько). В этом уроке рассмотрим использование этих функций более подробно.
СУММЕСЛИ в Эксель
Функция СУММЕСЛИ применяется в случае, если вам необходимо просуммировать числа по одному определенному условию. Синтаксис функции следующий.
Диапазон – область ячеек, в которых проверяется соответствие условию
Критерий – условие для проверки
Диапазон суммирования – область ячеек с числами, которые суммируются по соответствующему условию
Для примера разберем ситуацию по продажам. У нас есть два менеджера – Иванов и Петров, которые занимаются продажами в городах Москва и Казань. Наша задача: Узнать сколько выручки нам принес менеджер Петров.
Синтаксис функции для нашего примера будет:
Первым аргументом (A2:A13) мы задаем область, в которой будем проверять условие, в нашем случае это фамилии менеджеров, вторым аргументом (E2) мы задаем ячейку, в которой указано условие, третьи аргументом (C2:C13) мы задаем область с выручкой, которую должны просуммировать.
Что будет делать формула? Она сопоставит в каждой строке с фамилиями менеджеров фамилию из условия и фамилию в ячейке и если они совпали, то из соответствующей строки с выручкой возьмет значение и добавит к результату. Если вы все сделали правильно, то сумма выручки по менеджеру Петров должно составить 3 850 рублей.
СУММЕСЛИМН в Эксель
СУММЕСЛИМН является более универсальной формулой, она позволяет суммировать по нескольким условиям, в том числе по одному, т.е. заменяя функцию СУММЕСЛИ. Синтаксис у функции следующий:
Диапазон суммирования – это область ячеек с числами, которые будут суммироваться по заданным условиям
Диапазон условия 1 – это область ячеек с первым условием
Условие 1 – это критерий для проверки в диапазоне условия 1
Диапазонов условий и соответственно условий может быть несколько.
В нашем случае мы хотим узнать, какую выручку нам принес менеджер Петров, работая в городе Москва. Синтаксис функции:
C2:C13 – диапазон ячеек с выручкой, которую мы хотим просуммировать
A2:A13 – область ячеек с данными по фамилиям менеджеров, которые осуществляли продажи
E2 – ячейка с критерием по определенному менеджеру, эффективность работы которого мы хотим узнать
B2:B13 – диапазон ячеек с городами, где осуществлялись продажи
F2 – ячейка с условием, в каком именно городе нас интересуют продажи
По итогам работы этой функции, мы получим размер выручки, которую принес нам менеджер Петров, работая в городе Москва. Если вы все сделали правильно, то результат должен получиться 1 775 рублей.
Функция СУММЕСЛИМН в Excel может помочь суммировать значения ячеек на основе нескольких критериев, в этом руководстве будет объяснен синтаксис функции СУММЕСЛИМН и некоторые примеры использования этой функции.
Синтаксис:
Синтаксис функции СУММЕСЛИМН в Excel:
Аргументы:
- sum_range : Необходимые. Ячейки или диапазон ячеек, которые вы хотите суммировать. Суммируются только ячейки с числами, пустые ячейки и текстовые значения игнорируются.
- criteria_range1 : Необходимые. Первый диапазон, к которому вы хотите применить критерий1.
- criteria1 : Необходимые. Первое условие, которое будет использоваться в диапазоне1.
- criteria_range2, criteria2, … : Необязательный. Другие дополнительные диапазоны и связанные с ними критерии.
Вернуть:
Возврат суммирования для списка чисел, отвечающих всем определенным критериям.
Примеры:
Пример 1: базовое использование функции СУММЕСЛИМН
Вставьте следующую формулу в пустую ячейку:
А затем нажмите Enter ключ, чтобы получить нужный вам результат. Смотрите скриншот:
Внимание: Вы также можете использовать ссылку на ячейку, чтобы заменить условие в приведенной выше формуле следующим образом: =SUMIFS(D2:D12, A2:A12,F2, C2:C12, G2) .
Пример 2: функция СУММЕСЛИМН с датами в Excel
Если у вас есть список дат и вам нужно просуммировать значения ячеек между двумя заданными датами, функция СУММЕСЛИМН может решить эту задачу быстро и легко.
Используйте приведенную ниже формулу в пустую ячейку:
- Советы: В приведенной выше формуле:
- C2: C12: диапазон ячеек, которые вы хотите суммировать.
- A2: A12, "> = 4 января 1 г.": Первый диапазон_критерия и критерии, по которым вы хотите суммировать значения.
- A2: A12, « = СУММЕСЛИМН (C2: C12, A2: A12, "> =" & E2, A2: A12, "
Пример 3: функция СУММЕСЛИМН с пустыми и непустыми ячейками
Предположим, у вас есть диапазон данных, как показано на следующем снимке экрана. Теперь, если вы хотите суммировать общую цену, когда ячейки в столбце B не являются пустыми ячейками, а ячейки в столбце C являются пустыми ячейками, как вы могли бы это сделать?
Для решения этой задачи следует использовать следующую формулу:
=SUMIFS(D2:D12, B2:B12,"<>", C2:C12," margin-left: 13px;" >Затем нажмите Enter ключ, чтобы получить результат так, как вам нужно. Смотрите скриншот:
Пример 4: функция СУММЕСЛИМН с несколькими критериями и логикой ИЛИ
По умолчанию функция СУММЕСЛИМН допускает только логику И, но иногда может потребоваться суммировать значения ячеек на основе нескольких критериев с логикой ИЛИ в Excel.
Например, если у вас есть ряд данных, как показано на скриншоте ниже, чтобы суммировать общую цену от Дэвида, Джейн или Санни, продажи которых превышают 200.
Формула1: СУММЕСЛИМН () + СУММЕСЛИМН () + СУММЕСЛИМН () +…
Чтобы решить эту задачу, вы можете объединить функцию СУММЕСЛИМН вместе для суммирования ячеек с логикой ИЛИ. Пожалуйста, примените следующую формулу:
=SUMIFS(D2:D12, B2:B12, "Jane", C2:C12, ">200")+SUMIFS(D2:D12, B2:B12, "David", C2:C12, ">200")+SUMIFS(D2:D12, B2:B12, "Sunny", C2:C12, ">200")
А затем нажмите Enter ключ для получения правильного результата, как вам нужно:
Формула 2: СУММ (СУММЕСЛИМН (<>))
При наличии нескольких логических операций ИЛИ приведенная выше формула будет несколько трудной. Чтобы упростить приведенную выше формулу, вы можете создать формулу массива с функциями СУММ и СУММЕСЛИМН.
Введите или скопируйте следующую формулу в пустую ячейку:
Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить результат, как показано на скриншоте ниже:
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
Добрый день друзья!
=СУММЕСЛИМН( диапазон для суммирования; диапазон где условия; наше условие; [диапазон где условия; новое условие]; и т.д. до 127 раз), где,
диапазон для суммирования – это тот диапазон, где находятся данные, которые нужно суммировать, когда наши условия будут выполнены;
диапазон где условие – это диапазон откуда выбирается данные согласно нашему критерию для дальнейшего суммирования;
наше условие – это, собственно, есть наше условие, которое нужно выбрать в таблице и просуммировать его значения.
"Бедность и богатство – суть слова для обозначения нужды и изобилия. Следовательно, кто нуждается, тот не богат, а кто не нуждается, тот не беден.
"
Демокрит
Читайте также: