Расчет показателей Главной книги и оборотов субсчетов
На листе «ГлКнига» зададим необходимые формулы для автоматического расчета итоговых оборотов по проводкам с одинаковой корреспонденцией счетов.
Здесь у нас пока заняты колонки А, В, С. Поэтому итоги по проводкам с одинаковой корреспонденцией счетов сформируем в колонке D, дав ей заголовок «Оборот» (ячейка D1).
Формула расчета оборота, задаваемая в ячейке D2, следующая: =СУММЕСЛИ(Проводки.Код;А2;Проводки.Сумма). Суть выполняемых данной функцией действий состоит в суммировании всех чисел из диапазона «Проводки.Сумма» для тех строк, в которых код проводки-образца на листе «ГлКнига» (ячейка А2) совпадает с кодом проводки на листе «Проводки». Поскольку коды образцов проводок на листе «ГлКнига» уникальны, то функция СУММЕСЛИ() для данной строки будет собирать итоги только по тем проводкам, в которых задан этот код, то есть для корреспонденции субсчетов, заданной в данной строке.
После того как формула введена для второй строки, ее следует скопировать в последующие строки колонки D листа «ГлКнига» для всех используемых образцов проводок. Попробуйте на листе «Проводки» ввести несколько проводок с одинаковой корреспонденцией субсчетов и посмотрите, как работают формулы (рис. 6).
Данные листа «ГлКнига» можно использовать для расчета оборотов субсчетов.
Рис. 6. Проводки
Продолжим формирование листа «Субсчета» (рис. 7). Здесь пока заданы только коды и наименования субсчетов. Они размещены в колонках А и В. В колонку С должны вноситься остатки субсчетов на начало периода. При этом остатки по кредиту субсчетов вводятся со знаком минус. Остатки на начало первого периода введите вручную, для последующих они средствами Excel автоматически переносятся из остатков на конец прошлого периода.
В колонках D и Е будут автоматически рассчитываться, соответственно, дебетовый и кредитовый обороты субсчета данной строки. Для расчета дебетового оборота в ячейку D2 должна быть введена формула: =СУММЕСЛИ(ГлКнига.Дебет;А2;ГлКнига.Сумма). Она суммирует все итоги по проводкам, в которых дебетуемый субсчет совпадает с заданным в текущей строке листа «Субсчета». Аналогично задается формула для расчета кредитового оборота (ячейка Е2): =СУММЕСЛИ(ГлКнига.Кредит;А2;ГлКнига.Сумма).
Формула расчета исходящих остатков для ячейки F2 достаточно очевидна: =C2+D2-E2.
После того как формулы введены во вторую строку, их следует «растиражировать» на все строки, в которых определены субсчета. Мы рекомендуем сразу выполнять копирование формул с некоторым запасом, чтобы потом только пополнять список кодов и названий субсчетов. На этом формирование макета листа «Субсчета» завершено.
Рис.7. Лист «Субсчета»
Контрольные функции
Если на листе «Проводки» используются только допустимые проводки, а все субсчета допустимых проводок определены на листе «Субсчета», то баланс оборотов на листе «Субсчета» будет выполняться автоматически и кредитовый оборот будет равен дебетовому. Строку для расчета соответствующих итогов можно вставить прямо на листе «Субсчета» (рис. 8).
Рис. 8. Итоги расчетов на листе «Субсчета»
Формулы для расчета итогов:
=СУММ(D3:D100) – для ячейки D2;
=СУММ(E3:E100) – для ячейки Е2.
Разместим формулы для расчета итогов по столбцам наверху таблицы, чтобы их сразу можно было увидеть при переходе на этот лист. Кроме формул расчета итогов по оборотам, зададим еще и формулы подсчета итогов входящих и исходящих остатков. Поскольку остатки по кредиту у нас задаются со знаком минус, то о сбалансированности итогов свидетельствует нулевой остаток. Если итоги по входящим остаткам не равны нулю, то это означает, что они заданы неверно либо не полностью.