Функция СУММЕСЛИ. Первый из трех китов Excel

Начинаю обзор главных инструментов любимой нами программы от Microsoft. И конечно в начале хочу рассказать о моей самой частоиспользуемой функции («формуле»). А точнее функция СУММЕСЛИ. Если Вы не представляете, что это такое и как это использовать - я Вам завидую! Для меня это было настоящее открытие.

Вам приходилось суммировать данные по сотрудникам или клиентам из большой таблицы, выбирать сколько выручки было по той или иной номенклатуре? Вы фильтровали по фамилиям/позициям, а затем вносили числа руками в отдельные ячейки? Может быть считали на калькуляторе? А если строк больше тысячи? Как посчитать быстро?

Вот тут пригодится СУММЕСЛИ!

Задача1. Существует статистика по товарам, городам и какие показатели были достигнуты по этим позициям. Необходимо рассчитать: « на какую сумму продано номенклатуры Товар1?»

Прежде, чем преступить к решению 1ой задачи, разберем из чего состоит функция СУММЕСЛИ:

• Диапазон. Диапазон, в котором содержатся условия поиска. Заполнять обязательно. Для 1ой задачи столбец Товар.
• Критерий. Можно заполнить числом (85), выражением («>85″), ссылкой на ячейку (B1), функцией (сегодня()). Определяет условие по которому суммируются (!). Все текстовые условия заключаются в кавычки (« ) «>85″. Заполнять обязательно. Для 1ой задачи столбец =Товар1
• Диапазон_суммирования. Ячейки, для суммирования, если они отличаются от ячеек в Диапазоне. Для 1ой задачи столбец Выручка.

Итак запишем формулу, предварительно занеся аргумент условия в ячейку F3

Не забудьте проверить Посчиталось? Верно? Отлично!

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

А также для подсчета количества повторений той или иной позиции используйте функции СЧЁТЕСЛИ (СЧЁТЕСЛИМН)

Решается она еще проще, чем предыдущие две.

Часть 2

Что такое СУММЕСЛИ?

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

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

Проблема

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

Тем не менее, есть как минимум три альтернативы:

1. Использовать формулы массивов, чтобы суммировать данные по нескольким критериям.
2. Использовать функцию СУММПРОИЗВ
3. Использовать функцию СУММЕСЛИМН

Есть, правда, еще одно решение, для которого даже Excel не понадобится — съездить в Северную Америку и посчитать яхты, но это, к счастью, не наш метод!

Решение

Итак, воспользуемся третьим из предложенных вариантов решения и запишем вот такую формулу:

Обратите внимание, каким образом соотносятся диапазоны данных и аргументы этой функции:

Как работает СУММЕСЛИМН?

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

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

Бонус

И не один, а целых два!

Во-первых, в Excel 2007 и более поздних версиях существует небольшое семейство подобных функций, включающее в себя СРЗНАЧЕСЛИМН (для вычисления средних значений) и СЧЁТЕСЛИМН (для подсчета количества значений).

Во-вторых, функции этого семейства могут работать со операторами отношения (>, <, = и т.п.) и знаками подстановки, такими как «*» — любое количество любых символов, и «?» — один любой символ. Так, например, чтобы подсчитать количество самолетов, проданных в Северной и Южной Америке, можно воспользоваться такой формулой:

Произведем сложение значений находящихся в строках, поля которых удовлетворяют сразу двум критериям (Условие И). Рассмотрим Текстовые критерии, Числовые и критерии в формате Дат. Разберем функцию СУММЕСЛИМН() , английская версия SUMIFS().

В качестве исходной таблицы возьмем таблицу с двумя столбцами (полями): текстовым «Фрукты » и числовым «Количество на складе » (См. файл примера ).

Задача1 (1 текстовый критерий и 1 числовой)

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики (ячейка D 2 ), у которых остаток ящиков на складе >=6 (ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

1. =СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;">="&E2)

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

• B2:B13 Интервал_суммирования - ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
• A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
• D2 и ">="&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

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

2. другой вариант =СУММПРОИЗВ((A2:A13=D2)*(B2:B13);--(B2:B13>=E2))
Разберем подробнее использование функции СУММПРОИЗВ() :

• Результатом вычисления A2:A13=D2 является массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в A2:A13=D2 , а затем нажав ;
• Результатом вычисления B2:B13 является {3:5:11:98:4:8:56:2:4:6:10:11}, т.е. просто значения из столбца B ;
• Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является {0:0:0:0:4:8:56:0:0:0:0:0}. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
• Разберем второе условие: Результатом вычисления --(B2:B13>=E2) является массив {0:0:1:1:0:1:1:0:0:1:1:1}. Значения в столбце «Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
• Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

При необходимости даты могут быть введены непосредственно в формулу =СУММЕСЛИМН(B6:B17;A6:A17;">=15.01.2010";A6:A17;"<=06.03.2010")

Чтобы вывести условия отбора в текстовой строке используейте формулу ="Объем продаж за период с "&ТЕКСТ(D6;"дд.ММ.гг")&" по "&ТЕКСТ(E6;"дд.ММ.гг")

В последней формуле использован .

Задача4 (Месяц)

Немного модифицируем условие предыдущей задачи: найдем суммарные продаж за месяц(см. файл примера Лист Месяц ).

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

Для решения 3-й задачи таблица с настроенным автофильтром выглядит так (см. файл примера Лист 2 Даты ).

Предварительно таблицу нужно преобразовать в и включить строку Итогов.

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

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

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

Диапазон - диапазон ячеек, в котором ищутся значения соответствующие аргументу Условие . Диапазон может содержать числа, даты, текстовые значения или ссылки на другие ячейки. В случае, если другой аргумент - Диапазон_суммирования - опущен, то аргумент Диапазон также является диапазоном, по которому производится суммирование значений удовлетворяющих аргументу Условие (в этом случае Диапазон должен содержать числа).

Условие - критерий в форме числа, выражения или текста, определяющий, какие ячейки должны суммироваться. Например, аргумент Условие может быть выражен как 32, "яблоки" или ">32".

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

Примеры

Рассмотрим случай, когда аргумент Диапазон_суммирования опущен. В этом случае суммирование будет производиться по диапазону ячеек, указанному в первом аргументе Диапазон (т.е. он должен содержать числа). В нем же будет производиться поиск значений соответствующих аргументу Условие , которые затем и будут просуммированы. Пусть это будет диапазон B5:B15 , см. Файл примера .

Решим задачи:

• найти сумму всех чисел больше или равных 10. Ответ: 175. Формула: =СУММЕСЛИ(B5:B15;">=10")
• найти сумму всех чисел меньше или равных 10. Ответ: 42. Формула: =СУММЕСЛИ(B5:B15;"<=10")
• найти сумму всех положительных чисел в диапазоне. Формула: =СУММЕСЛИ(B5:B15;">0") . Альтернативный вариант с использованием функции СУММПРОИЗВ() выглядит так: =СУММПРОИЗВ((B5:B15)*(B5:B15>0))

Форма задания условия (критерия) достаточно гибка. Например, в формуле =СУММЕСЛИ(B5:B15;D7&D8) критерий <=56 задан через ссылку D7&D8 : в D7 содержится текстовое значение <=, а в D8 – число 56 (см. рисунок ниже). Пользователь, например, может легко изменить критерий с помощью в ячейке D7 . Эквивалентная формула =СУММЕСЛИ(B5:B15;"<=56") или =СУММЕСЛИ(B5:B15;"<="&56) или =СУММЕСЛИ(B5:B15;"<="&D8) или =СУММЕСЛИ(B5:B15;D7 &56) .