Как объединить плановые затраты подразделений в сводный бюджет с помощью Excel

Дмитрий Щербаков, консультант по продуктам Microsoft Office, владелец сайта http://www.excel-vba.ru

Предположим, компания «Альфа» выпускает товары в трех регионах – Москва, Омск и Пермь. Для бюджета на следующий год нужны данные о плановых издержках на производство продукции. Чтобы получить цифры и объединить их в одну таблицу:

  • подготовьте единый шаблон бюджетной заявки;
  • попросите руководителей подразделений заполнить таблицы;
  • скачайте надстройку Power Query;
  • преобразуйте информацию в редакторе запросов.

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

Разработайте шаблон бюджетной заявки. 

Чтобы сформировать сводный бюджет, сделайте форму заявки от подразделений унифицированной. Подготовьте единый шаблон без объединенных ячеек (см. рисунок 1) и с шапкой в одну строку. Добавьте графы для названия статей, месяцев и итоговых цифр. Попросите сотрудников указать суммы без НДС, чтобы не искажать реальный объем издержек.

Скачайте пример заявки по ссылке и доработайте ее. Например, добавьте вспомогательные графы: «Код статьи затрат», «Код центра затрат», «Валюта».

Соберите заполненные файлы от подразделений и сохраните в отдельной папке на компьютере.

Преобразуйте исходные данные от подразделений.

 Скачайте бесплатную надстройку для Excel по ссылке, чтобы объединить данные из столбцов. После установки на панели инструментов появится отдельная вкладка Power Query.

Создайте в Excel новый файл для единой таблицы с затратами подразделений, переименуйте его в «Сводный бюджет». На вкладке «Данные» нажмите «Получить данные» – «Из файла» – «Из папки» (см. рисунок 2). Если у вас Excel 2010 версии, переходите на вкладку Power Query и выбирайте «Из таблицы или диапазона». В диалоговом окне «Папка» укажите путь к папке на компьютере, где лежат файлы с цифрами подразделений.

Рисунок 2. Загрузка бюджетов в редактор запросов

После того как программа загрузит файлы, нажмите кнопку «Изменить». Вы попадете в редактор запросов. Чтобы получить информацию из исходных таблиц, удалите из общего запроса все графы, кроме Content и Name (см. рисунок 3).


Рисунок 3. Запрос с тремя бюджетами в редакторе Power Query

Добавьте пользовательский столбец. Для этого переходите на вкладку «Добавить столбец» и кликните «Пользовательский столбец». В диалоговом окне введите название столбца «Данные», а в строке формул запишите выражение =Excel.Workbook ([Сontent]) (см. рисунок 4).


Рисунок 4. Формула для пользовательского столбца

Программа подтянет всю информацию из трех бюджетов в один столбец. Чтобы увидеть таблицы в исходном виде: нажмите на контекстное меню столбца «Данные», выберите «Развернуть», снимите галочку с поля «Использовать исходное имя столбца как префикс» и нажмите «ОК» (см. рисунок 5). Появятся еще два столбца, из которых так же разверните графу Data.

Рисунок 5. Контекстное меню пользовательского столбца

Удалите графы Content, Name и Name1. Чтобы исключить ошибки в расчетах, раскройте меню фильтра у столбца Column2 и снимите галочку с пункта NULL (см. рисунок 6). Далее перейдите на вкладку «Преобразование», выберите «Таблица» – «Использовать первую строку в качестве заголовков».

Рисунок 6. Фильтр столбца Column2

Чтобы цифры корректно отражались, смените текстовый тип данных на «Десятичное число». Выделите столбцы с месяцами и итоговыми значениями, переходите на вкладку «Преобразование» – группа инструментов «Любой столбец» – «Тип данных» – «Десятичное число» (см. рисунок 7).

Рисунок 7. Выбор типа данных

Преобразования с таблицами закончили. Остался последний шаг – просуммировать расходы по идентичным статьям за каждый месяц из трех бюджетов. На вкладке «Преобразование» кликните «Таблица» – «Группировать по:». В диалоговом окне выберите «Дополнительно», в поле «Группировка» – «Статьи». Ниже создайте 13 столбцов группировки – по одному на каждый месяц и для итоговых цифр. Для всех граф в поле «Операция» выберите «Сумма», а в поле «Столбец» продублируйте названия новых столбцов (см. рисунок 8). Нажмите «ОК», переходите на вкладку «Главная» – «Закрыть и загрузить». Программа закроет редактор запросов и выгрузит общую таблицу на лист файла «Сводный бюджет».

Рисунок 8. Группировка затрат по месяцам

С помощью надстройки Power Query можно объединить любое количество файлов. Если в папку с бюджетными заявками добавили новые файлы, ничего переделывать не нужно. Откройте файл «Сводный бюджет», выделите любую ячейку из таблицы, кликните правой кнопкой мыши и выберите в контекстном меню «Обновить» (см. рисунок 9). Программа загрузит все файлы, которые лежат в папке с бюджетами. Названия файлов не имеют значения. Не храните сводную таблицу в папке с заявками подразделений, иначе цифры задвоятся.

Если подразделения прислали скорректированные бюджеты, удалите старые файлы и сохраните в исходную папку новые. Обновите сводный бюджет.

Рисунок 9. Обновление сводного бюджета расходов на производство продукции


Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.