Будем делать отчет по этим данным
1С обычно выдает отчеты в таком формате, в них есть вся необходимая информация, но при большом количестве договоров работать неудобно. Вывод ОСВ только по договорам задачу не облегчит, так как удобней работать и сданными, где есть и наименование поставщика и номер договора.
Облегчить задачу можно при помощи реестра договоров. Реестр договоров заносится на отдельный лист файла Excel. Написание наименований поставщиков и договоров в реестре Excel должны быть точно такими как в ОСВ, иначе формулы работать не будут. Лучше взять файл который выгрузился из учетной системы и прям по его данным сделать реестр. Для нашего примере реестр по январю будут таким.
На другом листе этого же файла будем хранить данные ОСВ, максимально близко к той форме, которую получаем из 1С (или любой другой учетной программы).
При переносе данных из ОСВ в расчетный файл нужно:
- убрать все объединения столбцов, так как это затрудняет работу с формулами, сводными таблицами и фильтрами;
- убрать итоговые строки;
- добавить столбец с названием месяца за период отчета (столбец можно добавить в любое место в таблице). Я добавила в столбец "В", так как это позволяет не менять формат отчета из ОСВ.
В реестр договоров добавим столбцы: "остаток на 01.01", обязательства, "оплата".
Далее при помощи формул "подтянем" данные с листа с ОСВ на лист с реестром договоров и получим такой отчет
Остаток на начало года добавляем при мощи формулы ВПР (эта формула будет ссылаться только на один месяц - январь, данные обновляться не будут). Для отчета эти данные данные можно занести даже вручную.
Обязательства и оплата подтягиваются из данных с листа "ОСВ" при помощи формулы СУММЕСЛИ.
Вот формулы к таблице
Обратите внимание, что в формулах ссылка идет сразу на весь столбец
=СУММЕСЛИ(ОСВ!A:A;реестр!B2;ОСВ!E:E), это связано с тем, что данные по ОСВ у нас будут добавляться ежемесячно, если ссылаться на конкретный диапазон, то данные придется обновлять ежемесячно.
В нашем отчете обязательства заносятся по данным оборота по дебету ОСВ, если обязательства начисляются по другому принципу, т.к. договор предусматривает индивидуальные схемы расчетов, то расчет обязательств нужно делать в дополнительных столбцах или на дополнительных листах. Это будет уже индивидуальная схема и в рамках данной статьи мы ее рассматривать не будем.
Далее добавим в таблицу данные за февраль. Из первого рисунка с данными с ОСВ видно, что добавились новые договоры которых не было в реестре. Их нужно вручную добавить в реестр.
Чтобы быстро найти различия между данными ОСВ за предыдущий период и отчетный месяц можно сделать вспомогательную таблицу (в отдельном файле, который не нужно хранить)
По нашим данным такую
По этой таблице сделать сводную
Из сводной таблицы видно, что состав поставщиков не поменялся, но добавились договоры 10 и 11. Измененный реестр будет выглядеть так
Переносим копирование данным из ОСВ за февраль на лист ОСВ
В таблице с реестром договоров формулы для столбцов "обязательства", "оплата", "остаток" копируем и строк выше.
В нашем реестре данные по обязательства, оплате и остатком, после добавления на лист ОСВ данных за новый месяц обновятся, и будут соответствовать данным бухгалтерского учета.
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке