Суммесли с несколькими условиями

Суммесли с несколькими условиями

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

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

СУММЕСЛИ и ее синтаксис

Функция СУММЕСЛИ позволяет суммировать ячейки, которые удовлетворяют определенному критерию (заданному условию). Аргументы команды следующие:

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

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

Как работает функция СУММЕСЛИ в Excel?

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

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

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

  1. Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Поэтому проставляем E2:E14.
  2. Критерий выбора в нашем случае – продавец. Заключаем слово в кавычки и ставим вторым аргументом.
  3. Диапазон суммирования – это заработные платы, потому что нам нужно узнать сумму зарплат всех продавцов. Поэтому F2:F14.

Получилось 92900. Т.е. функция автоматически проработала список должностей, выбрала из них только продавцов и просуммировала их зарплаты.

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

Функция СУММЕСЛИ в Excel с несколькими условиями

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

Синтаксис с использованием функции по нескольким критериям

Аргументов у СУММЕСЛИМН может быть сколько угодно, но минимум – это 5.

  1. Диапазон суммирования. Если в СУММЕСЛИ он был в конце, то здесь он стоит на первом месте. Он также означает ячейки, которые необходимо просуммировать.
  2. Диапазон условия 1 – ячейки, которые нужно оценить на основании первого критерия.
  3. Условие 1 – определяет ячейки, которые функция выделит из первого диапазона условия.
  4. Диапазон условия 2 – ячейки, которые следует оценить на основании второго критерия.
  5. Условие 2 – определяет ячейки, которые функция выделит из второго диапазона условия.
Читайте также:  Выделение главной части бесконечно малой функции

И так далее. В зависимости от количества критериев, число аргументов может увеличиваться в арифметической прогрессии с шагом 2. Т.е. 5, 7, 9.

Пример использования

Предположим, нам нужно подсчитать сумму заработных плат за январь всех продавцов-женщин. У нас есть два условия. Сотрудник должен быть:

Значит, будем применять команду СУММЕСЛИМН.

  • диапазон суммирования – ячейки с зарплатой;
  • диапазон условия 1 – ячейки с указанием должности сотрудника;
  • условия 1 – продавец;
  • диапазон условия 2 – ячейки с указанием пола сотрудника;
  • условие 2 – женский (ж).

Итог: все продавцы-женщины в январе получили в сумме 51100 рублей.

СУММЕСЛИ в Excel с динамическим условием

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

У нас появилась дополнительная строчка. Сразу обращаем внимание, что диапазон условий и суммирования автоматически расширился до 15 строки.

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

Аналогично можно не только добавлять, но и удалять какие-либо строки (например, при увольнении сотрудника), изменять значения (заменить «январь» на «февраль» и подставить новые заработные платы) и т.п.

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

Сложение в Excel

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

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

Читайте также:  Как перевести фото в формат ворд

Нахождение суммы

Следующий этап – нахождение суммы по столбцам. В уже приведенном примере поставим задачу подсчитать общий доход с продажи всех заявок, включающий оплату отелей, транспорта и экскурсионных программ. Для этого нужно просуммировать все поля колонки «Общая стоимость». Применение арифметической суммы здесь неудобно, так как количество записей в таблице может быть очень большим.

Для таких ситуаций подходит функция Excel СУММ(). Она складывает числа в указанном диапазоне ячеек без необходимости прописывания расположения каждого поля: СУММ(число1;[число2];…), где в качестве аргумента «число» может быть указан диапазон чисел в ячейках. Функция принимает не более 255 аргументов. В примере заявок туристического агентства она будет применена к столбцу I:

Условное суммирование

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

СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]).

В интервале полей, указанном аргументом «диапазон» проверяется заданное требование для добавления числа к сумме. Этот атрибут и следующий за ним – «условие» — являются обязательными для использования в формуле. Поэтому в поле «Критерий» мы указываем свое требование.

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

Примеры использования

Подсчитаем доходы только от тех туристов, которые заказывали у нас индивидуальные трансферы. В качестве полей для проверки требования указываем столбец «Трансферы», сам критерий – равенство поля значению «Индивидуальные».

И в этом случае мы должны указать колонку слагаемых в поле «Диапазон_суммирования» окна параметров функции СУММЕСЛИ:

В результате получаем сумму данных только по заявкам с индивидуальными трансферами.

Еще один пример – использование команды в совокупности с формулой ВПР: ВПР(искомый показатель; интервал поиска; номер колонки в интервале; точное или примерное равенство — 0 (точное) или 1(примерное)).

На практике для использования ВПР в Excel выделяется подтаблица (параметр 2), первый столбец которой содержит искомое значение (параметр 1). Команда определяет строку, в которой оно содержится, и показывает в результате содержимое поля в найденной строке и указанном столбце (параметр 3).

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

Читайте также:  Стрелка не реагирует на мышь

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

Она вернет число 2 – код индивидуальных трансферов. Теперь подставляем эту функцию в окно параметров СУММЕСЛИ в Excel и получаем то же значение, что и в первом случае.

Сумма с несколькими условиями

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

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

СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …).

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

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

Этим критериям соответствует всего одна заявка, поэтому итог будет равен доходу по ней.

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

СУММЕСЛИМН с несколькими критериями и или логики DEVSAP

=Сумм(СУММЕСЛИМН(диапазон суммирования,диапазон критерия ,критерий)) #критерий=

СУММЕСЛИМН с несколькими критериями и / или логики
Если сумма равна либо x или Y

Для суммирования по нескольким критериям можно использовать функцию СУММЕСЛИМН с массивом констант.

В примере формулу в I9 является:

Как эта формула работает

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

Один из вариантов, несколько критериев в массиве :

Это вызовет СУММЕСЛИМН вернуть два результата: число «Выполнено» и число «Доставка», в результирующий массив такой:

Чтобы получить окончательный итог, мы используем СУММЕСЛИМН внутри суммы. Функция СУММ суммирует все элементы массива и возвращает результат.

Вы можете использовать не полное название а частичное совпадение с помощью «*» символов в критерии, если это необходимо. Например, для суммирования элементов, содержащих «Выполнено» или «Доставка» в любую точку в диапазон критерия, вы можете использовать:

=Сумм(СУММЕСЛИМН(диапазон суммирования ,диапазон критерия,<«*Выполнено*»,»*Доставка*»>))

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

Ссылка на основную публикацию
Статическая и динамическая озу
Оперативная память (Random Access Memory – RAM), т.е. память с произвольным доступом, используется центральным процессором для совместного хранения данных и...
Создать новую электронную почту на яндексе бесплатно
Всем привет! С вами снова я, Алексей. В этом посте я расскажу вам о том, как создать электронную почту на...
Создать канал на ютубе регистрация бесплатно
Добрый день, уважаемые читатели и гости моего блога! Если вы попали на эту статью, значит хотите узнать, как зарегистрироваться в...
Статусы сообщений в whatsapp
Cтатусы показывают, используют ли ваши контакты WhatsApp в настоящий момент или то время, когда они были онлайн в последний раз....
Adblock detector