Как вести бюджет по данным ОСВ я рассказывала в этой статье.
В этой статье расскажу как добавить в файл с бюджетом данные за новый месяц. Начнем.
Наша книга с бюджетом содержит 7 листов (см. рисунок):
лист "Сводный бюджет" - на котором данные собираются в сводную форму.
листы "сч. 26", "сч. 44", "распределение" - на этих листах расположены данные о расходах, которые потом аккумулируются на листе "Сводный бюджет"
листы с бюджетами ЦФО: Администрация, Коммерческая служба, Производство - данные на эти листы попадают из листа сводный бюджет.
Данные на листы "сч. 26" и "сч. 44" заносят из стандартных отчетов бухгалтерской программы при помощи функции ВПР, как это делать я рассказывала в статье, ссылка на которую дана выше. (При переносе данных из бухгалтерских отчетов в файл с бюджетом важно, чтобы названиям статей в бухучете и нашем файле были написано абсолютно одинаково).
После заполнения, листы с данными счетов будут выглядеть так
Теперь эти данные нужно перенести на лист "Сводный бюджет". Формулы на листе сводный бюджет для данных за январь у нас уже есть, попробуем их просто скопировать в соответствующую ячейку за февраль.
На рисунке ниже приведен результат этих действий - данные не подтянулись.
Чтобы исправить эту ситуацию, подтянуть нужные данные и в следующих месяцах просто копировать формулы в новый месяц, посмотрим формулы.
В нашем случае, часть данных с листов счетов переносится при помощи формулы СУММЕСЛИ
Чтобы исправить ошибки работы формулы СУММЕСЛИ проверим составные части формулы:
1) Ссылка на диапазон данных работает верно
в январе
=СУММЕСЛИ('сч. 26'!$B$3:$B$10;'сводный бюджет '!A6;'сч. 26'!$F$3:$F$10)
в феврале
=СУММЕСЛИ('сч. 26'!$B$3:$B$10;'сводный бюджет '!E6;'сч. 26'!$F$3:$F$10)
она ссылается на диапазон с кодом статьи
2) ссылка на критерий поиска (в нашем случае - это код статьи на листе "Сводный бюджет". Проверяем, она скопировалась неправильно
в январе
=СУММЕСЛИ('сч. 26'!$B$3:$B$10;'сводный бюджет '!A6;'сч. 26'!$F$3:$F$10)
в феврале
=СУММЕСЛИ('сч. 26'!$B$3:$B$10;'сводный бюджет '!E6;'сч. 26'!$F$3:$F$10)
Чтобы при копировании ссылка на код статьи не менялась ее нужно закрепить при помощи символов $, так 'сводный бюджет '!$A$6. Для добавления символов нужно поставить курсор на ссылку и нажать клавишу F4
3) Теперь проверяем ссылку на данные, которые должны быть просуммированы при помощи формулы СУММЕСЛИ. Этот блок
в январе
=СУММЕСЛИ('сч. 26'!$B$3:$B$10;'сводный бюджет '!A6;'сч. 26'!$F$3:$F$10)
в феврале
=СУММЕСЛИ('сч. 26'!$B$3:$B$10;'сводный бюджет '!E6;'сч. 26'!$F$3:$F$10)
Очевидно, что ссылка в данной части формулы для февраля должна ссылаться на столбец с данными февраля, т.е. на столбец F (см. рисунок выше). Чтобы формула копировалась правильно нужно убрать символы $ из ссылки (убрать также можно при помощи клавиши F4).
Таким образом, чтобы формулу января можно было копировать в следующий месяц она должна иметь следующий вид
=СУММЕСЛИ('сч. 26'!$B$3:$B$10;'сводный бюджет '!$A$6;'сч. 26'!F3:F10). Изменить ссылки (добавить/ убрать знак $) нужнл во всех формулах СУММЕСЛИ в столбце за январь.
Теперь проверим структуру таблиц на листах "Сводный бюджет" и с данными счетов.
Структура таблиц на листе "Сводный бюджет" и "сч. 26" разная
Между данными факта за январь и февраль на листе "Сводный бюджет" находится три столбца: "откл.(руб.)", "откл, %", "план"
На листе "сч. 26" (см. рис. выше) данные за январь и февраль находятся в соседних столбцах, чтобы формула копировалась правильно на листах "сч. 26", "сч. 44", " распределение", между данными по месяцам нужно добавить такое же количество столбцов как и на листе "Сводный бюджет".
После добавлений пустых столбцов лист "сч. 26" будет меть такой вид.
Так как пустые столбцы носят технический характер и не нужны для работы, их можно просто скрыть на экране.
Чтобы формула работала и при добавление данных за следующие месяцы, нужно добавить пустые столбцы и между всеми остальными месяцами. (Между столбцами с фактическими данными по месяцам на листе "Сводный бюджет" и на листах с данными счетов должно быть одинаковое количество столбцов)
После внесения всех изменений в формулы января, просто копируем еще раз из столбеца январь в столбец февраль на листе "Сводный бюджет"
Должно получиться так
Незаполненные ячейки ссылаются на лист "распределение", он у нас пока не заполнен, т.к. заполнение этого листа осуществляется вручную. Лист "распределение" создан на тот случай если статья делиться между ЦФО и в ОСВ эти данные отражаются, они рассчитываются при помощи отдельных расчетов или отчетов.
После заполнения данных на листе "Распределение" лист "Сводный бюджет" будет иметь такой вид
Для удобства внизу добавим строку с проверкой. Фактическая сумма расходов за месяц в строке итого должна быть равна сумме расходов по "сч. 26" и "сч. 44".
Для переноса данных с листа "Сводный бюджет" на листы с бюджетами ЦФО, приводим структуру таблиц в соответствие и просто копируем формулы.
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке
