Основные принципы использования функции "СУММ" мы рассмотрели в данной статье. Но часто случается так, что суммировать данные в диапазоне нужно в зависимости от некоторых условий. Для выполнения таких задач существуют функции:

• "СУММЕСЛИ" - для суммирования данных по одному условию;
• "СУММЕСЛИМН" - для суммирования данных по нескольким условиям.

Пример использования функции "СУММЕСЛИ" в Excel.

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

Исходные данные:

1. Конкретный перечень сотрудников.
2. Общая выгрузка из базы данных, которая содержит информацию обо всех сотрудниках компании, за все периоды.

Что требуется:

1. Посчитать сумму проданного товара конкретными сотрудниками.

   ---

Такой вид имеют наши данные в Excel:

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

1. Убедиться в том, что все необходимые данные для отчета присутствуют в выгрузке на вкладке "Данные о продажах".  

Да, действительно, на вкладке "Данные о продажах" присутствуют столбы "Фамилия И.О. сотрудника" и "Стоимость товара за единицу". 

2. Убедиться в совпадении формы записи данных в столбе "Фамилия И.О. сотрудника" в выгрузке базы данных и того же столбца на вкладке отчета. 

Для данной проверки необходимо выполнить следующие манипуляции:

• На вкладке "Данные о продажах" присвойте фильтр строке с наименованием столбцов и обязательно проверьте (через выпадающий список) нет ли различных вариаций записи данных в столбце "Фамилия И.О. сотрудника". Это важно. Это поможет Вам избежать одну из самых популярных ошибок при работе с функциями Excel. Также, необходимо проверить столбец с данными о стоимости товара. Значения в данном столбце должны быть определены как числовые и поддаваться математическим вычислениям.
•  

• Данные в выгрузке действительно не содержат различных вариаций записей в интересующих нас столбцах (например, нет записей "Результирующий Р.О." и "Результирующий РО" в разных местах выгрузки).

3. Написание функции для расчета суммы продаж для конкретных сотрудников.

Необходимо перейти на вкладку "Перечень сотрудников" и прописать функцию: 

"=СУММЕСЛИ('Данные о продажах'!$B:$B;'Перечень сотрудников'!$A3;'Данные о продажах'!$G:$G)", где

• "   СУММЕСЛИ  " - функция суммирования данных по заданному условию;
• "   ('Данные о продажах'!$B:$B;   " - диапазон, в котором содержится условие;
• "   'Перечень сотрудников'!$A3;   " - само условие;
• "   'Данные о продажах'!$G:$G)   " - диапазон, в котором содержатся данные для суммирования.

4. Завершение формирования отчета.

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

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

Усложним задачу, добавив ещё один критерий в отчет.

Исходные данные:

1. Конкретный перечень сотрудников.
2. Общая выгрузка из базы данных, которая содержит информацию обо всех сотрудниках компании, за все периоды.

Что требуется:

1. Посчитать сумму проданного мыла конкретными сотрудниками.

   ---

Ход выполнения задачи:

Начало выполнения данной задачи равно такое же, как и в первом случае. Обязательно нужно проверить данные столбцов "Фамилия И.О. сотрудника", "Стоимость товара (за единицу)". Но нас также интересует конкретный товар, поэтому проверку данных необходимо сделать и по столбцу "Наименование товара".

Ключевое различие решения поставленной задачи будет заключаться в используемой функции.  Для решения задачи пишем следующую функцию: =СУММЕСЛИМН('Данные о продажах'!$G:$G;'Данные о продажах'!$B:$B;$A3;'Данные о продажах'!$F:$F;$B3), где

• "   СУММЕСЛИМН   " - функция суммирования данных по нескольким заданным условиям;

• " ('Данные о продажах'!$G:$G; " - диапазон суммирования;

• " 'Данные о продажах'!$B:$B; " - диапазон условия 1;

• " $A3; " - условие 1;

• " 'Данные о продажах'!$F:$F " - диапазон условия 2;

• " $B3) " - условие 2.

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

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

Принцип трех "П": Приспосабливайся, пробуй, проверяй.

Всем добра!