Те, кто много работает с разными данными, рано или поздно сталкиваются с необходимостью подсчитать итоговые суммы по одному или нескольким условиям. Например, из базы подневных расходов вытащить помесячные суммы. Или из базы продаж собрать итоги по городу, году и менеджеру и т.д.
Рассмотрим небольшой пример. На рисунке ниже Вы видите базу в виде таблицы и итоговый свод, который нужно заполнить данными. В базе есть расходы по датам и по статьям (причем в одну дату может быть несколько строк расходов по любой статье). Задача - свести данные по месяцам и по статьям.
Сегодня мы рассмотрим 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