Sumifs excel как пользоваться
На своём сайте я много внимания уделял Excel функциям извлечения данных ( ИНДЕКС , ВПР , ПОИСКПОЗ , ГПР ). Они, безусловно, очень важны, но есть ещё один краеугольный класс формул, без которых просто никуда. Это, конечно же, формулы подсчёта и суммирования.
Эта статья будет посвящена формулам СУММЕСЛИМН (SUMIFS), СЧЁТЕСЛИМН (COUNTIFS) и СРЗНАЧЕСЛИМН (AVERAGEIFS). Если вы раньше использовали формулы СУММЕСЛИ (SUMIF), СЧЁТЕСЛИ (COUNTIF) или СРЗНАЧЕСЛИ (AVERAGEIF), то, ознакомившись со статьёй, можете благополучно забыть об их существовании, так как функционально *ЕСЛИМН формулы кроют *ЕСЛИ формулы, как бык овцу.
Идея формулы
Прежде, чем погружаться в детали функционирования формулы, полезно понимать, что же она делает по самой своей сути. Смотрим на рисунок.
Мы располагаем таблицей, в которой необходимо просуммировать значения из столбца Количество , но с учётом двух критериев или фильтров, если угодно. Первый критерий — столбец Магазин должен содержать значение Центр , второй критерий — столбец Товар должен содержать значение Компьютер . Оба критерия действуют одновременно или, как говорят, по » И » (а не по » ИЛИ «). То есть нас интересует, сколько компьютеров продал центральный магазин.
Для этого в ячейку G7 мы помещаем формулу СУММЕСЛИМН , в которую передаём 5 параметров:
D3:D15 — диапазон, содержащий числа, которые мы собираемся суммировать
B3:B15 — диапазон, содержащий значения для сравнения с критерием 1
G3 — ячейка, содержащая, собственно, критерий 1 — значение, которое нас интересует — » Центр «
C3:C15 — диапазон, содержащий значения для сравнения с критерием 2
G5 — ячейка, содержащая критерий 2 — » Компьютер «
Формула вернёт нам значение 14, так как только 2 строки таблицы удовлетворяют обоим нашим критериям, и обе они содержат число 7. А теперь давайте дадим более формальное описание данной функции.
Синтаксис
Вот синтаксис формулы суммирования:
= СУММЕСЛИМН ( sum_range; criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )
sum_range — первый параметр — всегда диапазон суммирования.
criteria_range_1 — диапазон для тестирования на соответствие критерию 1
- ссылки на ячейку, например E3
- выражения в виде текстовой строки, например » >10 «
- значения в виде числа или текста, например 45 или » Москва «
criteria_range_n и criteria_n — таких критериев, которые описываются всегда двумя параметрами, может быть до 127 штук. Принцип их организации остаётся неизменным.
Некоторые важные замечания
Количество параметров будет всегда нечётным, так как есть диапазон суммирования, а критерии идут парами. Это будет выглядеть как 3, 5, 7, 9 и т.д. параметров. Что будет соответствовать 1, 2, 3, 4 и т.д. критериев отбора строк для суммирования.
Диапазоны суммирования и диапазоны критериев должны всегда иметь одинаковый размер и одинаковую ориентацию (вертикальную или горизонтальную)
Диапазоны суммирования и диапазоны критериев могут не быть векторами (то есть располагаться более чем в одной строке или одном столбце), но опять же они должны соответствовать друг другу. То есть если диапазон суммирования имеет размер 10 на 3, то и все критериальные диапазоны должны быть строго 10 на 3. В целом, я не рекомендую подобную архитектуру ваших таблиц. Вы сэкономите себе массу сил, если критерии будут располагаться в отдельных столбцах. 1 столбец — 1 критерий.
СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН
Не откладывая в долгий ящик, сразу посмотрим на синтаксис формул СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН . У СРЗНАЧЕСЛИМН отличие только в том, что она не суммирует числа, а вычисляет по ним среднюю величину.
= СРЗНАЧЕСЛИМН ( avg_range; criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )
А СЧЁТЕСЛИМН считает строки, а не числа, поэтому она не имеет аналога параметров sum_range или avg_range. Таким образом её параметры описывают только критерии и количество параметров всегда должно быть чётным, в отличие от её коллег.
= СЧЁТЕСЛИМН ( criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )
Файл примера
Скачать
Критерии
Теперь, когда мы получили некоторое представление по механизму работы СУММЕСЛИМН , мы можем погрузиться в более сложные примеры, раскрывающие мощь и полезность этой формулы. В качестве полигона будем использовать вот такую умную таблицу с именем Sales :
СУММЕСЛИМН и умные таблицы
Посмотрите на пример 1 нашего учебного файла, формула в L6 . Обратите внимание, как удобно использовать структурные ссылки умных таблиц в формулах вообще и в СУММЕСЛИМН в частности.
L4 и L5 содержат значения критериев.
Критерий для периода дат
А вот как задать период дат (с. по . ) при суммировании:
Обратите внимание на очень важную конструкцию:
«> db_range»>$O$5
Это не что иное, как выражение в виде текстовой строки. Причём это не статическое выражение, которое обычно приводят в справке по СУММЕСЛИМН , типа » >= 200000 «. Это выражение динамическое, то есть — гораздо более ценное и интересное. Всё что вам нужно — это вставить между знаком операции » >= » и ссылкой на ячейку с параметром — знак операции сложения строк » & «.
Динамическая операция сравнения
А почему бы не дать на откуп пользователю право определять операцию для критерия? В предыдущем примере пользователь мог выбирать порог N в критерии » >=N «. А в этом примере пользователь определяет и порог и саму операцию! А почему нет?
В L12 у нас находится выпадающий список, ссылающийся на 4 возможные операции: >, >=, ? » — заменяет любой символ, » * » — заменяет любое количество символов (в том числе и его отсутствие). Например критерий » *т* » сработает и на слово «Центр», и на слово «Восток». А, если бы существовала такая альтернатива, то сработал бы и на слово «опт» (это, как раз случай, когда вторая звёздочка в «*т*» заменила ноль символов справа от «т»).
Ответы на сложные вопросы
При помщи *ЕСЛИМН формул можно отвечать на довольно заковыристые вопросы. Например, какая доля холодильников в Центре была продана по ценам, превышающим средние цены холодильников по всем магазинам? А вот формула, которое это расчитывает:
Как видите мы использовали 2 формулы СУММЕСЛИМН и 1 СРЗНАЧЕСЛИМН . СРЗНАЧЕСЛИМН вернула нам средние цены по холодильникам. Первая СУММЕСЛИМН вернула количество проданных телевизоров с ценами выше средних, а вторая формула вернула общее количество холодильников, проданных в Центре. Найдя частное между результатами двух СУММЕСЛИМН , мы получили долю от единицы и просто выразили её в процентах.
Набор магазинов
А что делать, если нам нужны продажи двух магазинов? В рамках одной формулы это решить в общем случае нельзя (метод через символы подстановки не универсален, так как магазины могут иметь полностью уникальные имена). Ответ очевиден — надо использовать 2 формулы и просто сложить их результаты. Хотя понятно, что такой метод подойдёт далеко не во всех ситуациях.
Но есть и ограничения.
Надеюсь вы убедились, что *ЕСЛИМН достаточно гибки, но есть один узкий момент, который надо хорошо понимать. Например, я хочу знать, сколько раз я торговал в Центре, предоставляя покупателям скидку более или равную 5%? Как я могу это узнать? Только вычисляя по каждой строке отношение предоставленной скидки к базовой цене. Понимаете — по каждой строке надо делать вычисление и сравнивать с 5%! Вот такое формулы *ЕСЛИМН сами, без вашей помощи сделать не смогут, так как формула один раз вычисляет критерий, а потом сравнивает его со всеми строками критериального диапазона, а нам надо это делать динамически. Но никто нам не мешает организовать дополнительный столбец, который будет предварительно считать отношение скидки к базовой цене, а после этого можно уже остальную работу поручить формуле СЧЁТЕСЛИМН (см. пример 7).
Пустые ячейки
Если вы хотите, чтобы формула *ЕСЛИМН отреагировала на пустые ячейки, то следует использовать критерии «» (пустая строка) или » db_sheet»>Blank нашего учебного файла:
= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; » db_formula»>ЕСЛИ ), то такая ячейка отреагирует только на критерий «», то есть:
= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «» )
Не пустые ячейки
А вот, если критерием является то, что ячейка хоть что-то содержит, то можно использовать такую форму:
= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «<>» )
Ну что ж, я надеюсь, что вы почувствовали всю силу, сосредоточенную в формулах этого семейства. Удачи!
Выборочные вычисления по одному или нескольким критериям
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
- Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3—Условие3), и четвертую, и т.д. — при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2=»Копейка»)*(B2=»Григорьев»)
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:
Excel SUMIFS: как суммировать только значения, отвечающие нескольким критериям
Функция SUMIFS расширяет полезность функции SUMIF, позволяя вам указать от 2 до 127 критериев, а не только один. Например, функция SUMIF может устанавливать критерии для продавцов, которые продали более 250 единиц. Функция SUMIFS может иметь критерии, как продавцы из определенного региона, которые продали более 250 единиц.
Эти инструкции применимы к Excel 2019, 2016, 2013, 2010, Excel 2019 для Mac, Excel 2016 для Mac, Excel для Mac 2011, Excel для Office 365 и Excel Online.
Как работает функция SUMIFS
Обычно SUMIFS работает со строками данных, называемыми записями. В записи связаны все данные в каждой ячейке или поле в строке, например, название компании, адрес и номер телефона.
Аргумент SUMIFS ищет конкретные критерии в двух или более полях в записи, и только если он находит совпадение для каждого указанного поля, данные для этой записи суммируются.
В пошаговом руководстве SUMIF мы соответствовали единственному критерию торговых агентов, которые продали более 250 заказов в год.
В этом уроке мы установим два условия с использованием SUMIFS: для агентов по продажам в восточном регионе продаж, у которых было менее 275 продаж в прошлом году.
Установка более двух условий может быть выполнена путем указания дополнительных аргументов Criteria_range и Criteria для SUMIFS.