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

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

Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2020 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.

Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.

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

Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20

Скачать заметку в формате Word, примеры в формате Excel

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

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

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

Рис. 2. Суммирование ячеек, отвечающих одному условию

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

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

Рис. 3. Суммирование ячеек, отвечающих нескольким условиям

Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.

В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).

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

Рис. 4. Использование функций баз данных

Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите <=20. В ячейке, где должен быть результат, введите следующую формулу:

=БДСУММ($А$1:$А$101,1,Критерий)

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.

Дополнение от 29 марта 2020 г.

Читая книгу Джона Уокенбаха Excel 2020. Профессиональное программирование на VBA я узнал, что, начиная с версии Excel 2020 в VBA появилось новое свойство DisplayFormat (см., например, Range.DisplayFormat Property). Т.е., VBA может считывать формат, отображаемый на экране. При этом не важно, как он был получен, прямыми настройками пользователя, или с помощью условного форматирования. К сожалению, разработчики MS сделали так, что свойство DisplayFormat работает только в процедурах, вызываемых из VBA, а пользовательские функции на основе этого свойства выдают ошибку #ЗНАЧ! Тем не менее, получить сумму значений в диапазоне по ячейкам определенного цвета, можно с помощью процедуры (макроса, но не функции). Откройте Excel-файл с примером (содержит код VBA). Пройдите по меню Вид -> Макросы -> Макросы; в окне Макрос, выделите строку СумЦветУсл, и нажмите Выполнить. Запустится макрос, выберите диапазон суммирования и критерий. Ответ появится в окне.

ЧИТАТЬ ДАЛЕЕ:  ТОП 10 лучших препаратов железа при анемии: описания, свойства, побочные эффекты, отзывы, цены

Код процедуры

Хотя пользовательская функция и дает ошибку, но в процессе ее вызова можно «подсмотреть» ответ. В ячейке начните набирать формулу =su…

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

Воспользуйтесь подсказкой, кликнув на нее, а затем нажмите знак функции в строке формул:

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

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


[1] Массив я создал с помощью функции =СЛУЧМЕЖДУ(1;100)

Задача 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 аргумента. Но иногда последний может быть исключен, и тогда команда будет работать только по диапазону и критерию.

Оцените статью
ObzorTop15
Adblock
detector