Ведение бухгалтерского учета в Excel, часть третья

Рассмотрим, как можно построить оборотную ведомость на основе информации из шахматки, описанной в предыдущей части статьи, но вначале добавим описание плана счетов, чтобы использовать названия счетов в оборотной ведомости. Переименуйте оставшийся пустым лист в План счетов и перейдите на него. Введите заголовок План счетов и заголовки столбцов Счет и Наименование. Щелкните правой кнопкой мыши на заголовке столбца A и выберете во вспомогательном меню команду Формат ячеек. Откроется диалог настройки формата, в котором вы должны перейти на вкладку Число. В списке выберите Текстовый и нажмите кнопку OK. Теперь числа в первом столбце будут восприниматься как текст, и вы сможете ввести значения 01, 02 и аналогичные. Заполните план счетов, введя номера и описания используемых счетов. Субсчета можно не вводить.
Далее можно приступать к созданию оборотной ведомости. Добавьте лист в рабочую книгу и назовите его Оборотная ведомость. Введите в первой строке заголовок ведомости, после чего следует ввести заголовки столбцов. В ячейку A3 введите заголовок Счет, а в ячейку B3 - Наименование счета. Объедините ячейки C2 и D2, и введите в них название Сальдо на начало периода. Аналогично объедините попарно следующие четыре столбца и введите названия Обороты за период и Сальдо на конец периода. В третьей строке введите в столбцах C, E и G название Дебет, а в столбцах D, F и H введите название Кредит. Заголовок таблицы сформирован, и можно приступать к введению формул. Следует ввести всего одну строку, после чего размножить ее средством автозаполнения. Итак, заполним ячейки четвертой строки таблицы.
Введите в ячейку A4 формулу =ТЕКСТ(Шахматка!A6;"00"). В ячейку будет вставлен номер счета из первой строки шахматки, причем номер будет выглядеть как 01, 02 и так далее, а не 1 или 2. Чтобы по номеру счета определить его наименование, в ячейку B2 введите формулу =ВПР(A4;'План счетов'!$A$3:$B$63;2). Взяв номер счета из ячейки A4, Excel найдет на листе с планом счетов список, начинающийся в ячейке A3 и заканчивающийся в ячейке B63. Если в вашем плане счетов размер списка иной, поставьте нужное число с номером последней строки в списке вместо 63, например $B$55 или $B$101. В обнаруженном списке Excel найдет строку, в первом столбце которой заданный номер счета, и выдаст значение из второй ячейки таблицы, то есть название счета. Следует помнить, что для правильной работы план счетов должен быть отсортирован в порядке возрастания номеров. Если вы вводили последовательно все счета, он так и будет отсортирован. Кстати, если при добавлении вспомогательных проводок в журнал операций, в которых указан одинаковый счет по дебету и кредиту, вы описали не все счета, указанные в плане счетов, оборотная ведомость будет рассчитываться неверно. Далее следует заполнить расчетные ячейки оборотной ведомости. В столбцах Дебет и Кредит, объединенных заголовком Сальдо на начало периода, ничего вводить не надо, так как начальное сальдо будет в дальнейшем вводиться вручную.
В ячейку E4 введем формулу для расчета дебетовых оборотов за период =ВПР(ЗНАЧЕН(A4);Шахматка!$A$6:$AA$28;27). Конкретные значения в формуле зависят от количества используемых вами счетов. Взяв из поля A4 значение номера счета, Excel ищет его в первом столбце сводной таблицы, то есть созданной ранее шахматки. Сводная таблица у нас располагается до ячейки $AA$28. В этой ячейке выводятся дебетовые обороты по счету с наибольшим номером. Возможно, вам потребуется ввести иной адрес ячейки, например $Y$26. Будьте внимательны, так как правильное указание адреса важно для выполнения верных вычислений. Последнее число в формуле указывает на номер столбца в списке, из которого следует взять нужное значение. Если вы все делали правильно, сводная таблица должна содержать одинаковое количество строк и столбцов, и данное число на единицу меньше номера последней используемой строки. В нашем случае, при ссылке $AA$28, число равно 27. Если у вас получилась ссылка $Y$26, то следует указать число 25. Если после ввода формулы вы увидите в ячейке вместо числа текст #Н/Д, #ЗНАЧ! или #ССЫЛКА!, то вы неверно указали параметры. Исправьте формулу в соответствии с вашими конкретными условиями.
Далее следует ввести кредитовые обороты. Для этого в ячейку F4 введите формулу =ГПР(ЗНАЧЕН(A4);Шахматка!$C$4:$Y$30;27). Функция ГПР работает почти так же, как и ВПР, но ищет не по строкам, а по столбцам. В отличие от расчета дебетовых оборотов, в этой формуле ищется столбец в сводной таблице, в первой строке которого указан нужный счет. В качестве результата подставляется значение из последней строки сводной таблицы. Обратите внимание, что в качестве списка выделена другая часть сводной таблицы. При расчете дебита из сводной таблицы не были взяты первые две строки, а теперь не принимаются в расчет первые два столбца. Кроме того, последней ячейкой считается не та, в которой выводятся дебетовые обороты по счету с наибольшим номером, а ячейка, в которой выводятся кредитовые обороты по данному счету. Если в первом случае вы подставили в формулу значение $Y$26, то теперь следует подставить значение $W$28. Последнее число в формуле то же, что и в предыдущей формуле.
Наиболее сложная часть оборотной ведомости описана. Вы ввели формулы для расчета оборотов по дебету и кредиту. Если хотите, можете выделить ячейки E4 и F4, после чего задать формат для вывода целых чисел, чтобы в оборотной ведомости не выводились копейки. Далее следует ввести формулы для расчета конечного сальдо. Введите в ячейку G4 формулу =C4+E4-D4-F4, а в ячейку H4 формулу =D4+F4-C4-E4. В результате в ячейках отображаются одинаковые числа, но с разными знаками. Чтобы не отображать нулевые и отрицательные суммы, выделите ячейки G4 и H4, после чего выберите команду меню Формат - Ячейки. В открывшемся диалоге перейдите на вкладку Число и выберите в списке Числовые форматы элемент (все форматы). В поле Тип введите шаблон формата 0;;. Ноль означает, что положительное число должно выводиться как целое число. Два знака точки с запятой, после которых ничего не указано, означают, что нулевые и отрицательные значения не отображаются. Нажмите кнопку OK, и диалог закроется. Теперь из двух сумм отображается только положительная. Если эта сумма в столбце дебета, то в столбце кредита ничего не отображается, и наоборот.
Мы ввели формулы для одной строки, а теперь следует размножить их. Выделите ячейки с A4 по H4, подведите указатель мыши к маркеру заполнения, расположенном в правом нижнем углу области выделения, после чего нажмите кнопку мыши и переместите указатель вниз на несколько строк. Следует заполнить столько строк, сколько счетов используется в вашем плане счетов. Если в нижних строках в ячейках появились сообщения #Н/Д, #ЗНАЧ! или #ССЫЛКА!, вы заполнили слишком много ячеек, и лишние строки следует очистить.
Нам осталось только ввести формулы для подсчета сумм по столбцам. Введите в ячейку на пересечении столбца C и строки, расположенной через одну за последней строкой таблицы, выражение =СУММ(C4:C26). Необходимо отметить, что вместо C26 вы должны подставить номер последней строки в вашей таблице, например C24. Перетащив маркер заполнения вправо, заполните ячейки в трех следующих столбцах. Суммы по начальному сальдо и оборотам рассчитаны.
Для расчета сумм на конец периода следует ввести более сложную формулу. Как вы помните, мы с помощью задания формата чисел не отображаем отрицательные суммы, однако при расчетах отрицательные числа из ячеек также будут суммироваться. Поэтому в ячейку суммы в столбце G следует ввести формулу =СУММЕСЛИ(G4:G26;">0"). Так же, как и в предыдущей формуле, вам следует изменить значение G26 на адрес последней ячейки в столбце. В данной формуле указывается, что следует суммировать только положительные значения из ячеек. С помощью автозаполнения введете аналогичную формулу в столбец H. Теперь осталось ввести начальные остатки, и оборотная ведомость готова.
Вы можете изменить проводки в журнале операций или ввести новые операции. После этого следует перейти на лист Шахматка, щелкнуть правой кнопкой мыши на сводной таблице и выбрать команду меню Обновить данные. Сводная таблица будет построена заново, при этом автоматически будет пересчитана оборотная ведомость. На основе информации из оборотной ведомости можно построить множество полезных отчетов, но мы не будем сейчас этого делать. Вы можете самостоятельно создать стандартные бланки в Excel, воспользовавшись приемами, описанными в предыдущей главе, и добавить ссылки на нужные ячейки оборотной ведомости.
При ссылке на ячейки оборотной ведомости совершенно не обязательно помещать стандартные бланки в ту же рабочую книгу, в которой организован бухгалтерский учет. Excel позволяет ссылаться на ячейки таблицы, расположенной в другом файле. Например, если вы хотите вставить ссылку на ячейку G4 оборотной ведомости, введите формулу ='[Бухгалтерский учет.xls]Оборотная ведомость'!$G$4. обратите внимание, что для использования в именах пробелов нужно поместить названия в одинарные кавычки.
При открытии файла со ссылками на ячейки таблицы из другого файла, на экране появится диалог с предупреждением. Вы можете обновить связи, получив последние данные. Для этого нужно нажать кнопку Обновить в диалоге. При этом диалог закроется, а в ячейках появятся последние данные из бухгалтерского учета. Если же вы нажмете кнопку Не обновлять, то после закрытия диалога информация в ячейках останется неизменной.
Рассмотренный пример показывает, что с помощью Excel можно автоматизировать различные участки бухгалтерского учета, причем качество автоматизации ничем не хуже, чем при использовании специализированных бухгалтерских программ. При этом следует помнить, что возможности Excel значительно шире, и в нем можно использовать программы, написанные на языке программирования Visual Basic. Однако написание таких программ требует определенных знаний, и лучше, чтобы этим занимался программист, а не бухгалтер.