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

Excel. подсчет и суммирование ячеек, отвечающих критерию условного форматирования

Задача 7 (условия отбора, созданные в результате применения формулы)

Просуммируем продажи, которые выше среднего.

В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы. Формула должна возвращать результат ИСТИНА или ЛОЖЬ.

Для этого введем в ячейку

С3

файла примера формулу

=C9>СРЗНАЧ($C$9:$C$13)

, а в

С2

вместо заголовка введем произвольный поясняющий текст, например, «

Больше среднего

» (заголовок не должен повторять заголовки исходной таблицы).

Обратите внимание на то, что диапазон нахождения среднего значения введен с использованием

абсолютных

ссылок (

$C$9:$C$13

), а среднее значение всех продаж таблицы

СРЗНАЧ($C$9:$C$13)

сравнивается с первым значением диапазона, ссылка на который задана относительной адресацией (

C9

).

При вычислении функции

БДСУММ()

EXCEL увидит, что

С9

— это относительная ссылка, и будет перемещаться по диапазону вниз по одной записи и возвращать значение либо ИСТИНА, либо ЛОЖЬ (больше среднего или нет). Если будет возвращено значение ИСТИНА, то соответствующая строка таблицы будет учтена при суммировании. Если возвращено значение ЛОЖЬ, то строка учтена не будет.

Записать формулу можно так

=БДСУММ(C8:C13;C8;C2:C3)

Альтернативное решение

—

=СУММЕСЛИ(C9:C13;»>»&СРЗНАЧ($C$9:$C$13))

Задача

2 (с одним текстовым критерием)

Просуммируем все значения продаж продавца

Белов

.

Алгоритм следующий:

• Создадим новую табличку критериев, состоящую из заголовка Продавец (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора);

• Условие отбора должно быть записано в специальном формате: =»=Белов» (будут суммироваться
  
  Продажи
  
  только строк, у которых в столбце
  
  Продавец
  
  содержится
  
  точно
  
  слово
  
  Белов
  
  (или
  
  белов
  
  ,
  
  беЛОв
  
  , т.е. без
  
  учета РЕгиСТра
  
  ). Если имеются строки с
  
  Продавцами
  
  «
  
  Иван
  
  Белов», «Белов Иван»
  
  и пр., то суммирование по ним производиться не будет.
  
  Примечание
  
  : Если в качестве критерия указать не
  
  =»=
  
  Белов
  
  »
  
  , а просто
  
  Белов
  
  , то, будут суммироваться
  
  Продажи
  
  строк, у которых в столбце
  
  Продавец
  
  содержатся значения,
  
  начинающиеся
  
  со слова Белов (например, «
  
  Белов Иван
  
  »,
  
  Белов
  
  ,
  
  белов
  
  ). Чтобы просуммировать продажи, в том числе и для продавца «
  
  Иван Белов
  
  », необходимо в качестве критерия указать =»=*Белов». Этот критерий учитывает значения,
  
  заканчивающиеся
  
  на
  
  Белов.
  
  Звездочка (
  
  *) — это
  
  подстановочный знак
  
  .Если в качестве критерия указать
  
  *Белов
  
  (или =»=*Белов*»)
  
  ,
  
  то будут подсчитаны числа, в соответствующих ячейках которых
  
  содержится
  
  слово
  
  Белов.
  
• Теперь можно наконец записать саму формулу
  
  =БДСУММ(B8:C13;C8;B2:B3)
  
  Предполагая, что База_данных (исходная таблица) находится в
  
  B8:C13
  
  (столбец А (
  
  Товар
  
  ) можно в данном случае не включать в Базу_данных, т.к. он не участвует в формировании условия и по нему не производится суммирование).
  
  С8
  
  – это ссылка на заголовок столбца по которому будет производиться суммирование (т.е. столбец
  
  Продажи
  
  ).
  
  B2:B3
  
  – ссылка на табличку критериев.

Альтернативное решение

— =

СУММЕСЛИ(B9:B13;»белов»;C9:C13)

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

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

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

Прописываем аргументы.

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

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

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

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

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

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

Синтаксис функции

бдсумм()

Для использования этой функции требуется чтобы:

• исходная таблица имела заголовки столбцов;
• критерии должны были оформлены в виде небольшой таблицы с заголовками;
• заголовки таблицы критериев совпадали с заголовками исходной таблицы (если критерий не задается формулой).

БДСУММ(

база_данных;поле;условия

)

База_данных

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

Поле

— Заголовок столбца, по которому производится суммирование (т.е. столбец с числами). Аргумент

Поле

можно заполнить введя:

• текст с заголовком столбца в двойных кавычках, например «Возраст» или «Урожай»,
• число (без кавычек), задающее положение столбца в таблице (указанной в аргументе
  
  база_данных
  
  ): 1 — для первого столбца, 2 — для второго и т.д.
• ссылку на заголовок столбца.

Условия

— интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев). Структура таблицы с критериями отбора для

БДСУММ()

аналогична структуре для

Расширенного фильтра

.

Способ 2. формула массива для суммирования каждой 2-й, 3-й … n-й строки

Если удобного отдельного столбца с признаком для выборочного суммирования нет или значения в нем непостоянные (где-то «Выручка», а где-то «Revenue» и т.д.), то можно написать формулу, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный, т.е. кратен двум:

Давайте подробно разберем формулу в ячейке G2. «Читать» эту формулу лучше из середины наружу:

• Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
  
• Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
• Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
• И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
• Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl Alt Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.
  

Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:

А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:

И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:

Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.

Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.

Если над таблицей могут в будущем появляться новые строки (шапка, красивый заголовок и т.д.), то лучше слегка модернизировать формулу для большей универсальности:

Суммесли и ее синтаксис

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

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

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