Есть простой способ подготовить преднастроенные отчеты в Excel, в которых будут собираться планируемые или фактические расходы предприятий группы (филиалов) в разных аналитических признаках – по статьям, подразделениям и т. д.
- сформировать базу исходных данных в виде таблицы (см. рисунок 2);
- задать условия для выборки (наименование предприятий, подразделения, статьи затрат, периоды) и настроить структуру отчетных таблиц согласно требуемой аналитике;
- автоматизировать расчеты.
Какие данные понадобятся для настройки автоматической выборки затрат в Excel
Для настройки автоматической выборки данных по статьям затрат компаний группы необходимо подготовить таблицу с исходными данными на одном листе книги Excel (см. рисунок 1).
Бюджетные расходы можно планировать по месяцам. Важно, чтобы эта таблица была заполнена унифицированными данными, представленными в формате массива, включающего поля аналитики (например, на рисунке 2 – предприятие, департамент, статья) и поля значений (см. периоды на рисунке 2 – январь, февраль…). Каждое поле должно соответствовать одному столбцу, а количество строк быть одинаковым.
Следует избегать пустых строк в ячейках массива. Число полей в таблице ограничивается числом столбцов в MS Excel. При этом каждому полю должно быть присвоено свое имя, в дальнейшем это облегчит работу с таблицами и придаст наглядность формулам суммирования с условием. Например, чтобы присвоить имя «Январь» полю с плановыми значениями за январь, нужно выделить соответствующий диапазон без заголовка (E$4:$E$29), а затем, кликнув левой клавишей мыши, в контекстном меню – пункт «Имя диапазона». Далее в верхней строке «Имя» указать «Январь», нажать Enter (см. рисунок 2).
По имеющимся исходным данным (см. рисунок 2) должны получиться три таблицы с выборками по бюджетным статьям на одном листе книги (см. рисунок 1).
Далее, чтобы избежать ошибок ручного ввода, на этом же или любом другом листе в книге стоит создать справочник списков уникальных значений по каждому аналитическому признаку (см. рисунок 3).
Как задать условия для автоматической выборки расходов по нескольким компаниям в Excel
Опираясь на унифицированные данные из базы исходных данных, можно создать неограниченное число отчетов.
Например, отчеты трех видов (см. рисунок 1):
- сводный бюджет предприятия в разрезе департаментов (без аналитики по статьям);
- таблица контроля ключевых статей бюджета каждого предприятия (без аналитики по департаментам);
- суммарный бюджет холдинга в разрезе предприятий по периодам (с возможностью выборочного просмотра по департаментам и статьям).
Для настройки автоматической выборки данных по наименованию компании, подразделению, статье затрат (по аналитическим признакам) пригодится справочник (см. рисунок 3). Его данными заполняют созданные формы отчетов. Например, чтобы вставить значения из списков подразделений справочника в ячейку M4 таблицы № 1 (см. рисунок 4), предстоит выделить ячейку, перейти во вкладку «Данные» – «Проверка данных». В условиях проверки тип данных обозначить как «Список», а источником данных указать именованный диапазон справочника – список подразделений (см. рисунок 4).
После переноса всех данных справочника в три таблицы (см. рисунок 1) можно приступить к заключительному этапу настройки, в рамках которого предстоит задать формулу с использованием функции СУММЕСЛИМН, которая позволит сделать выборку расходов по компаниям автоматической (подробнее об этом см. Как задать формулу для автоматической выборки данных о расходах по периодам в Excel). Эту формулу отражают в оставшихся незаполненных столбцах, именованных по месяцам ( рисунок 5).
Как задать формулу для автоматической выборки данных о расходах по периодам в Excel
Формула для автоматической выборки данных о расходах по периодам в Excel должна быть отражена в каждой таблице (на рисунке 1 в ячейках синим шрифтом выделены результаты расчетов, они отображают сумму расходов на пересечении выбранных аналитик).
Например,
при заполнении первой ячейки таблицы № 1 (см.
рисунок 5) нужно
указать:
СУММЕСЛИМН(Январь;Департамент;$M4;Предприятие;$L$4),
где:
- «Январь» – это диапазон суммирования. Это означает, что из таблицы с исходными данными (базы данных) складываются все расходы за январь;
- «Департамент» – диапазон первого условия выборки. Выбирает расходы за январь по всем департаментам;
- $M4 – значение первого условия. Отражает те расходы, что были только у подразделения «Продажи» в январе;
- «Предприятие» – диапазон второго условия выборки. Выбирает расходы за январь по всем департаментам продаж в трех компаниях;
- $L$4 – значение второго условия выборки. Отбирает из расходов за январь по всем департаментам продаж в трех компаниях только сумму по компании «Альфа».
Для таблицы
№ 2 (см.
рисунок 1) формула
примет вид:
СУММЕСЛИМН
(Январь;Статьи;$M15;Предприятие;$L$13),
где вместо
аналитики «Предприятие» фигурирует статья бюджета.
Соответственно,
для таблицы № 3, в которой учтены все четыре условия,
получаем следующее:
СУММЕСЛИМН(Январь;Статьи;$O$20;Предприятие;M$21;Департамент;$M$20).