Уважаемые пользователи Голос!
Сайт доступен в режиме «чтение» до сентября 2020 года. Операции с токенами Golos, Cyber можно проводить, используя альтернативные клиенты или через эксплорер Cyberway. Подробности здесь: https://golos.io/@goloscore/operacii-s-tokenami-golos-cyber-1594822432061
С уважением, команда “Голос”
GOLOS
RU
EN
UA
tdots
6 лет назад

3 способа подсчитать итоги по условию в Excel

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

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

  

Сегодня мы рассмотрим 3 способа это сделать: 

1) Функция СУММЕСЛИМН

2) Функция СУММПРОИЗВ

3) Сводная таблица

Но сначала поделимся с Вами небольшой хитростью. В итоговом своде в ячейках с названиями месяцев на самом деле введен не текст ("Январь", "Февраль" и т.д.), а даты первого дня каждого месяца (01.01.2017, 01.02.2017 и т.д.), а уже после к этим ячейкам применен пользовательский формат "ММММ". 

Чтобы применить такой формат, выделите нужные ячейки, нажмите Ctrl+1, выберите вкладку Число, найдите пункт "(все форматы)" и в строку Тип введите четыре прописные буквы М. 

Этот небольшой трюк впоследствии нам очень поможет при подсчете итоговых сумм. 

Способ 1. Функция СУММЕСЛИМН

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

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

=МЕСЯЦ(A2)

и протянем ее вниз на всю высоту таблицы. Получим столбец с порядковым номером месяца (определяется по дате в первом столбце).  

Теперь в ячейку для Статьи 1 месяца Январь (в примере это ячейка H3) введем формулу:

=СУММЕСЛИМН($C:$C;$D:$D;МЕСЯЦ(H$2);$B:$B;$G3)

где:

$C:$C - столбец "Сумма затрат" (первым указывается тот диапазон, итоги по которому нужно подсчитать).

$D:$D - столбец проверки первого условия ("Номер месяца").

МЕСЯЦ(H$2) - первое условие. Ячейка H2 это "Январь". Так как мы вначале ввели туда 01.01.2017, а потом просто применили числовой формат, мы можем обработать эту ячейку функцией МЕСЯЦ и узнать порядковый номер месяца (и тогда функция сможет сравнить порядковый номер месяца в H2 и порядковые номера в столбце "Номер месяца").

$B:$B - столбец проверки второго условия ("Статья").

$G3 - второе условие. Ячейка с названием статьи затрат, по которой подводим итог. 

Обратите внимание на закрепление ссылок. Это сделано для того, чтобы формулу можно было копировать. После ввода формулы, в ячейке H3 будет подсчитан итог по Январю и статье 1. Скопируйте формулу в другие ячейки и получите нужный результат (при копировании в другие кварталы, не забудьте перетянуть ссылку на строку месяцев, как показано на гифке ниже).  

 

Способ 2. Функция СУММПРОИЗВ

Избежать создания доп.столбца (как в первом способе) можно путем применения функции СУММПРОИЗВ. Ее особенность в том, что она может обрабатывать внутри себя массивы данных, но при этом не требует ввода через Ctrl+Shift+Enter, то есть формально не является формулой массива. 

В ячейку для Статьи 1 месяца Январь (в примере это ячейка H3) введем формулу:

=СУММПРОИЗВ((МЕСЯЦ($A$2:$A$1000)=МЕСЯЦ(H$2))*($B$2:$B$1000=$G3)*$C$2:$C$1000)

Закрепления ячеек, как и в первом примере, настроены для дальнейшего копирования. Только при переходе на другой квартал, не забудьте перетянуть строку с месяцем. 

Разберем пошагово, как эта формула работает: 

  • Первая часть формулы (МЕСЯЦ($A$2:$A$1000)=МЕСЯЦ($H2)) делает следующее. Она берет диапазон А2:A1000 и к каждой ячейке применяет функцию МЕСЯЦ, то есть из каждой даты получает номер месяца. Затем каждый из полученных номеров сравнивается с номером месяца в ячейке H2 (это наш Январь в итоговом своде). Результат такого сравнения - столбец из значений ИСТИНА (если номера совпали) и ЛОЖЬ (если не совпали). Все эти вычисления происходят внутри формулы и не выносятся на лист. Обратите внимание, что сравнение нужно обязательно заключать в скобки!

 

  • Вторая часть формулы ($B$2:$B$1000=$G3) делает то же самое, но для колонки Статьи (здесь нам не требуется применение функции МЕСЯЦ, названия статей сравниваются напрямую). Сравнение также берется в скобки и на выходе так же дает столбец значений ИСТИНА и ЛОЖЬ. 
  • На третьем этапе столбцы перемножаются между собой. В Excel при умножении логических значений ИСТИНА и ЛОЖЬ на выходе получается ноль или единица.

 Полученный столбец нулей и единиц умножается на столбец, сумму по которому нужно свести. В итоге в тех строках, где оба условия совпали, сумма умножится на 1, то есть не изменится. А в тех строках, где хотя бы одно условие неверно, сумма умножится на 0 и станет равна 0. 

 

  • На завершающем этапе функция складывает значения полученного столбца и выдает итоговую сумму. 

Правила работы с функцией при подобных расчетах:

  • не указывать целые столбцы и строки в качестве аргументов;
  • перемножаемые диапазоны-аргументы должны быть равны (А2:А1000 и B2:B1000, например);
  • все выражения сравнения нужно заключать в скобки. 

 

Способ 3. Сводная таблица

Третий способ - подсчет итогов через сводную таблицу. Поместите курсор в любую ячейку исходной базы, далее нажмите "Вставка" - "Сводная таблица". Для удобства, можно вставить таблицу на лист с базой и итоговым сводом. 

Поместите поле "Дата" в область строк, поле "Статьи" в область столбцов, а поле "Сумма затрат" в область значений, как показано на рисунке ниже.  

Если Excel сразу не сгруппировал даты помесячно, то кликните на столбце с датами в сводной таблице правой кнопкой мыши и выберите "Группировать" с шагом "Месяцы". Для красоты можете применить к таблице встроенный или собственный стиль. В результате получится примерно такая таблица. 

Собственно, итоги подсчитаны. Проблема лишь в том, что настроить макет сводной так, чтобы она выглядела как итоговый свод, не получится. Нужно перенести данные из сводной в нашу таблицу. Есть следующие способы:

  • простое копирование и вставка. Подходит, если сводная не будет обновляться и менять размеры (не будут добавляться новые статьи);
  • использование функции ИНДЕКС, для извлечения данных из сводной. Про эту функцию мы недавно подробно рассказывали. Этот способ подойдет, если сводная будет обновляться, но новые статьи не будут появляться;
  • использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Самый интересный способ, который позволяет гибко менять сводную и всё равно подтягивать данные. Разберем его подробнее. 

Встаньте в ячейку Января и Статьи 1 итогового свода и попробуйте сослаться на соответствующую ячейку сводной таблицы. Скорее всего, Excel вместо простой ссылки, вроде =А15, вставит огромную формулу 

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма затрат";$M$2;"Дата";1;"Статья";"Статья 1")

Она то нам и нужна. Если формула не появилась, Вы можете ввести ее вручную или включить в настройках. Кликните на сводной таблице, найдите на ленте вкладку "Анализ", нажмите маленькую стрелочку рядом с кнопкой "Параметры" и поставьте галочку "Создать GetPivotData": 

 

Вернемся к функции. Она имеет 2 обязательных аргумента и дополнительные. 

1) Поле сводной таблицы, которое находится в области значений. В нашем случае это поле "Сумма затрат". Можете оставить в аргументе этот текст, а можете сослаться на шапку соответствующего столбца базы, чтобы сократить формулу; 

2)  Адрес любой ячейки сводной таблицы. Указывается на случай, если на листе их несколько и Excel должен понять, с какой именно работать;

3) Дополнительные аргументы парные. Они состоят из названия поля, по которому задается условие отбора, и самого условия (похоже на функцию СУММЕСЛИМН). 

В нашем случае нужно указать поле "Дата", в качестве условия для него - месяц. Так как в сводной месяц представлен в виде текста "янв", "фев" и т.д., нам нужно превратить наш заголовок в такой же текст. Так как мы вводили туда даты (делали это в начале статьи), получить нужное сочетание поможет формула 

=ТЕКСТ(H2;"МММ"), где H2 - ячейка с месяцем в итоговом своде

Вторая пара условий - поле "Статья" и ссылка на название статьи в итоговом своде. В результате для ячейки Января и Статьи 1 получим формулу:

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма затрат";$M$2;"Дата";ТЕКСТ(H$2;"МММ");"Статья";$G3)

В данном примере сводная начинается в ячейке $M$2. Формулу можно копировать, как и все предыдущие.  

Файл-пример, в котором реализованы все 3 способа подсчета, Вы можете скачать по этой ссылке

Для закрепления материала, можете посмотреть ролик по данному уроку на нашем YouTube канале.

 Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru 

680
0.000 GOLOS
На Golos с December 2017
Комментарии (0)
Сортировать по:
Сначала старые