Функции SUMIF и SUMIFS в Google (Excel) таблице

В Google таблицах, как и в Excel применяются функции SUMIF (СУММЕСЛИ) и SUMIFS (СУММЕСЛИМН) в целом ряде ситуаций и при решении многочисленных повседневных аналитических задач.

• Принцип действия: находит и отображает сумму содержимого ячеек, соответствующих определенному условию.
• Синтаксис:

  =SUMIF(диапазон; условие; [сумма_диапазона])

Если назначение функции VLOOKUP (ВПР) в том, чтобы просто отобразить данные из одного места Google таблиц (Excel) в другое, то SUMIF (СУММЕСЛИ) используют, чтобы просуммировать числовые данные по заданному сценарию.

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

Хорошо, если данных мало и можно быстро отсортировать товары по группам и в уме посчитать общие продажи, но когда массив с данными превышает несколько десятков строк — просуммировать в уме уже проблематично и неудобно.

В ячейке G2 (напротив товарной группы с овощами) ставим знак «равно» («=») и пишем формулу следующего вида:

 =SUMIF(диапазон; условие; [сумма_диапазона])

 =SUMIF($A:$A;F2;$D:$D)

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

 =SUMIF($A:$A - где мы ищем? Столбец с данными по группам, на которые мы ссылаемся при суммировании;F2 - что мы ищем? Конкретный критерий для поиска в массиве данных из диапазона;$D:$D - что мы суммируем? Числовые данные по продажам товаров из товарных групп)

Если пройти по таблице глазами и в уме сложить продажи по товарным группам, то выйдет абсолютно такой же результат. Таким образом, при помощи функции SUMIF (СУММЕСЛИ) на обработке больших массивов данных с продажами можно экономить уйму времени.

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

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

 =SUMIFS(сумма_диапазона; диапазон_критерия1; критерий1; [диапазон_критерия2; …]; [условие2; …])

Рассмотрим пример, где стоит задача: вывести сумму проданных товаров по месяцам, которые будут отфильтрованы по товарным группам. Для этой процедуры мы сначала создаем вспомогательный столбец (потом его можно будет удалить / очистить) и выводим месяцы из указанных дат формулой MONTH (МЕСЯЦ). Синтаксис прост:

 =MONTH(дата)

Мы уже с вами знаем функцию IF(ЕСЛИ), давайте воспользуемся ею для красоты и заменим числа месяцев, выводимых формулой MONTH (МЕСЯЦ), на названия этих месяцев:

=IF(MONTH(E2)=5;"Май";IF(MONTH(E2)=4;"Апрель";IF(MONTH(E2)=3;"Март";IF(MONTH(E2)=2;"Февраль";IF(MONTH(E2)=1;"Январь";"")))))

Когда мы из общей даты «изъяли» месяц из столбца с датами и отобразили его во вспомогательной колонке, мы можем ссылаться на эти месяцы при формировании отчета продаж по месяцам товаров, отфильтрованных по группам.

 =SUMIFS($D:$D;$A:$A;H2;$F:$F;"Январь")

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

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