Дебет-Кредит
Украинский бухгалтерский еженедельник
#38'2001: Практичная бухгалтерия - Автоматически

Автоматизация бухучета:
Бухгалтерский учёт с помощью Excel. 17-й урок

Регистры бухгалтерского учета и кассовые документы. Заполнение и хранение с помощью Excel

Владимир ЛАВРЕНОВ


Предыдущие уроки "Автоматизации бухучёта: Excel"

В соответствии с методическими рекомендациями по применению регистров бухгалтерского учета, записи в ведомости "Касса" Журнала 1 осуществляются в хронологическом порядке на основании сводных учетных документов: приходных, расходных кассовых ордеров, отчетов кассира.

При ведении бумажного Журнала 1 указанные записи формируются итогом за 2-3 дня. Однако вам удобнее будет, используя возможности Excel, заполнять его ежедневно. Собственно говоря, его заполнение будет сводиться к внесению данных в ячейки только одной графы - "Дата звіту касира". Все остальное программа проделает автоматически. Конечно, для этого нужно будет ввести необходимые формулы.

Журнал 1 - чтобы знать, сколько выдано. Мимикрирование Реестра помогает заполнять регистр

Форма Журнала 1, показанная на рисунке 1, абсолютно подобна утвержденной Минфином, поэтому ее построение не требует дополнительных пояснений.

Рисунок 1

Как уже было сказано, ключевой информацией для автоматического заполнения Журнала 1 будет дата отчета кассира (рис. 2). Если вы в созданный электронный Журнал 1 внесете дату кассового дня, в течение которого осуществлялись расходные операции по кассе, в соответствующих ячейках этой строки появятся дебетовые обороты по корреспондирующим счетам за этот день.

Рисунок 2

Внесение даты кассового дня - единственная операция при заполнении регистра, требующая от бухгалтера внимания. В данном случае в Реестре (Журнал регистрации кассовых операций) необходимо выбирать дату проведения по кассе кредитовых операций. В графе "Вид докум." они обозначены символом "В" (рис. 2). Соответствующую этим данным дату необходимо будет визуально находить в Реестре и вносить в Журнал 1. Однако такое неудобство вы можете устранить, если для ячеек графы "Дата складання" применить условное форматирование. Например, можно задать автоматическое закрашивание ячейки в желтый цвет, если в Реестр введены данные по приходному кассовому документу, а в синий - если по расходному документу.

Установите курсор в ячейку D5 (рис. 2), и в окне "Условное форматирование" для первого условия введите формулу "=$B5=”В”", а для второго условия - "=$B5=”П”" (если ссылки на ячейки были введены не вручную, уберите второй символ $, рис. 2). Используя команду "Копировать по образцу" , перенесите установленный формат на остальные ячейки графы. Теперь достаточно будет беглого взгляда, чтобы безошибочно найти в Реестре нужную дату, внести ее в Журнал 1 - и обороты еще одного кассового дня окажутся в регистре. Но прежде, чем это произойдетѕ

Запускаем формулу на все обороты с переключателем курса валют

Как и обещано, все без исключения графы вашего Реестра (естественно, кроме второй) будут заполняться автоматически. Но для этого придется немного потрудиться, внеся в ячейки первой строки регистра несколько однотипных формул.

Графа №1 ("Номер запису"):

- в ячейку А6 введите "1", в А7 введите формулу - "=ЕСЛИ(ЕПУСТО(B7);””;A6+1)". Эту формулу можно скопировать в остальные ячейки графы.

Графа №3:

- в ячейку С6 введите формулу массива (используйте сочетание клавиш Ctrl - Shift - Enter):

{=СУММ(ЕСЛИ(СимволДок=”В”;””;ЕСЛИ($B6=ДатаДок;
ЕСЛИ(ЛЕВСИМВ(Рахунок;2)=”31";СуммаДок))))/$Т6}.

Формулы остальных ячеек, в которых записываются обороты по дням, - подобные, поэтому скопируйте приведенную формулу во все ячейки 6-й строки до графы "Усього". Теперь откройте формулу в ячейку D6 (графа "33 "Інші кошти") и отредактируйте ее следующим образом:

{=СУММ(ЕСЛИ(СимволДок=”В”;””;ЕСЛИ(B6=ДатаДок;
ЕСЛИ(ЛЕВСИМВ(Рахунок;2)=”33";СуммаДок))))/Т6}.

Подобное редактирование проведите во всех ячейках первой строки регистра (кроме графы "Усього"). При этом обращайте внимание на то, что аргумент третьего условия (число, заключенное в кавычки) должен совпадать с синтетическим счетом соответствующей графы (рис. 3).

Рисунок 3

Наличие в формуле последнего аргумента - "Т6" (делителя) - требует пояснения. Дело в том, что для отражения операций, проведенных в иностранной валюте, используется такой же Журнал 1 и его ведомости, но заполненные в инвалюте. Для перехода к отражению оборотов по счетам с исчисления в иностранной валюте в гривневое исчисление и наоборот используется переключатель, расположенный в ячейке Т6. Запишите в эту ячейку формулу "=ЕСЛИ(ЕПУСТО(S6);1;S6)".

Если в Журнале необходимо отобразить обороты по счетам в долларах США, введите в ячейку S6 значение курса $ (например для операций, осуществленных 11.07.2001 г., введите курс $ - 5,3784). Таким образом, отражения оборотов каждого кассового дня в инвалюте в соответствующую ячейку (столбец S) введите нужный курс. Если ячейки "курсового" столбца пустые, данные регистра будут представлены в гривнях.

Формулы итоговых ячеек столбца "Усього" не отличаются оригинальностью и предельно просты. Введите в R6 "СУММ(C6:Q6)". А теперь выделите ячейки С6:Т6 и скопируйте их до строки "Усього".

Последний этап создания Журнала 1 - заполнение строки "Усього". Используйте для этого тоже обычную формулу суммы. В ячейку С32 введите "СУММ(C6:C31)". Скопируйте ее на всю строку 32 до ячейки R32 включительно - и Журнал 1 готов к работе.

Внимание! Чтобы в ячейках, в которых отсутствуют обороты по счетам, не появлялись нули, снимите флажок "нулевые значения" на вкладке "Вид" (меню "Сервис" - "Параметры").

Бухгалтерские ведомости (информация о том, сколько денег пришло)

Еще один лист под именем "Ведомость 1.1" необходимо будет создать для отражения дебетовых оборотов по счету 30 "Касса". Как видно на рисунке 4, табличная часть

Рисунок 4

Ячейка С6 Журнала 1 {=СУММ(ЕСЛИ(СимволДок=”В”;””;ЕСЛИ(B$6=ДатаДок;
ЕСЛИ(ЛЕВСИМВ(Рахунок;2)=”31";СуммаДок))))/Т6}
Ячейка С6 Ведомости 1.1* {=СУММ(ЕСЛИ(СимволДок=”П”;””;ЕСЛИ(B$6=ДатаДок;
ЕСЛИ(ЛЕВСИМВ(Рахунок;2)=”31";СуммаДок))))/Т6}

* Если количество столбиков табличной части в Журнале и в Ведомости не совпадает, соответственно измените в формуле ссылку на переключатель валют - Т6.

Ведомости ничем не отличается от Журнала 1. Формулы тоже подобны своим журнальным аналогам (сравнение приведено в таблице, отличие выделено фоном). Сравните:

Аналогично, как и при создании Журнала 1, скопируйте эту формулу и отредактируйте ее в соответствии с номерами синтетических счетов, записанных в "шапке" таблицы (корректировка затронет только числа, заключенные в кавычки). Окончание редактирования каждой формулы завершайте нажатием клавиш Ctrl - Shift - Enter.

Суммарные формулы табличной части подобны используемым в Журнале 1.

Осталось внести данные по остаткам. Дебетовый остаток по кассе (ячейка R2, "Сальдо на начало месяца") можно определить по формуле:

{=СУММ(ЕСЛИ(СимволДок=”П”;
ЕСЛИ(МЕСЯЦ(B6)>МЕСЯЦ(ДатаДок);СуммаДок)))-СУММ(ЕСЛИ(СимволДок=”В”;
ЕСЛИ(МЕСЯЦ(B6)>МЕСЯЦ(ДатаДок);СуммаДок)))+'Реєстр каси'!Q1}.

Так как в Ведомости первая запись отражает обороты по кассе за первый день отчетного месяца, поэтому и аргументом в формуле является ссылка на первую дату Ведомости (на рис. 4 это ячейка В6). Не забудьте ввести эту формулу как формулу массива (Ctrl - Shift - Enter).

Заполнение строки "Усього за кредитом" сводится к введению в ячейку С35 (рис. 4) формулы "='Журнал 1'!R32" (см. рис. 5), а в соседнюю ячейку Е35 "Усього за дебетом" - формулы "=R32".

Рисунок 5

Остаток на конец месяца по счету 30 определяется как разница между суммой сальдо на начало месяца и дебетовым оборотом по счету на текущий месяц и суммой кредитового оборота за данный месяц. Поэтому в ячейке R35 запишите формулу "=R2+E35-C35".

Доверяй, но проверяй

Методическими рекомендациями по применению регистров бухгалтерского учета определено, что сальдо счетов должно сверяться с соответствующими кассовыми документами: отчетами кассира, выписками из банка и т. п. Проверить это можно как визуально, так и с помощью формул.

Первый способ хотя и не полностью автоматизирован, но зато простой и универсальный. С его помощью вы сможете сделать сверку оборотов за любой период.

Например, вам необходимо сверить дебетовые обороты по кассе за июль 2001 года. Откройте ЭКК (лист "Касова книга" (рис. 6) и установите на первом фильтре (столбец А) условие, которое ограничивает выборку данных из кассовой книги временным интервалом с 01.07.2001 по 31.07.2001 (рис. 7).

Рисунок 6

Рисунок 7

Табличная часть ЭКК будет представлена только данными за июль 2001 года. Выделите ячейки столбца "Прибуток", и в правом углу строки состояния вы увидите суммарный дебетовый оборот по кассе за июль "Сумма=1248,09".

Сверьте его с данными ячеек Е35 и R32 Ведомости 1.1 (рис. 6). Таким же образом, выделив в ЭКК ячейки графы "Видаток", можно проверить суммарный оборот по кредиту счета 30, значение которого записано в ячейке С35.

Как заполнять регистры и "вылавливать" операции других отчетных периодов

Заполнение Журнала 1 и Ведомости 1.1 сводится фактически только к последовательному введению дат проведения кассовых операций. Необходимым условием автоматического заполнения этих регистров по предложенному варианту является предварительное заполнение Реестра.

Итак, чтобы получить заполненный Журнал 1 и Ведомость 1.1, вы должны:

- внести данные в Реестр (по крайней мере в четыре графы - №2, №4, №6, №17 (рис. 8);

- внести в ячейки граф "Дата звіту касира" (Журнал 1 и Ведомость 1.1) дату оформления кассовых документов, указанную в Реестре.

Рисунок 8

Использование условного форматирования для ячеек графы №4 ("Дата складання") подскажет вам, какие операции осуществлялись в этот кассовый день: приходные, расходные или те и другие (рис. 8).

Выборку "приходных" или "расходных" кассовых дней можно осуществить несколько иначе.

1. Установите в Реестре автофильтр для четырех первых столбцов, выделив ячейки А4:D4.

2. В списке второго фильтра (В4) выберите, например, строку "П". После этого Реестр будет показывать только приходные операции по кассе.

3. В графе "Дата складання" вы уже можете легко определить, в какие даты осуществлялись приходные операции.

При заполнении этих регистров вам стоит обратить внимание еще на одно обстоятельство.

Учетные регистры составляются ежемесячно, и поэтому должны содержать информацию об оборотах по счетам только за один определенный месяц. Чтобы исключить возможность внесения в регистр данных, относящихся к другому отчетному периоду, предлагаю использовать такой прием.

В регистрах (Журнале и Ведомости) активизируйте ячейку с датой первой записи (на рисунке 8 это ячейки В6). Вызовите окно "Условное форматирование", и в поле ввода формулы установите условие "=МЕСЯЦ($B6)<>МЕСЯЦ($B$2)". В этой формуле использована ссылка на ячейку В2, в которую необходимо ввести месяц заполнения учетного регистра (рис. 9).

Рисунок 9

Используя команду "Копирование по образцу", перенесите установленный формат на другие ячейки этой графы. Теперь если вы по ошибке внесете дату, не входящую в отчетный период, эта ячейка будет выделена особым форматом (рис. 9).

Учет операций в регистрах по расчетному счету аналогичен учету по кассе

Заполнение любого регистра связано с предварительным оформлением соответствующих документов. Выполнение кассовых операций сопряжено с заполнением целого ряда регламентированных документов, данные которых легко использовать для составления рассматриваемых регистров (кассовая книга, отчет кассира). При заполнении учетных регистров по расчетному счету, в принципе, можно тоже воспользоваться рассмотренным выше вариантом "реестр - регистр".

Что для этого надо? Достаточно в Реестре заполнить нужные графы, связанные с данной операцией по расчетному счету. А чтобы отличить введенные данные от данных, относящихся к кассовым операциям (они обозначены символами "П" и "В"), можно вносить в графу Реестра "Символ документа", например, такие обозначения:

- для приходных операций по расчетному счету символ "Д";

- для расходных операций - символ "К".

Однако будет лучше, если вы разделите ведение Реестра для кассовых и банковских документов и создадите аналог уже созданного Реестра для операций по расчетному счету. Все процедуры заполнения и использования его данных для соответствующих учетных регистров абсолютно одинаковы.

Универсальный способ заполнения регистров

Приведенный вариант, так сказать, стандартный, требующий подготовительной работы для определенной систематизации данных в реестре (заполнение в Реестре необходимых граф). Но есть и универсальный способ заполнения любого регистра. Все, что для этого необходимо, - правильно заполненный Журнал хозяйственных операций с корректно введенными проводками по операциям.

Путь информации о хозяйственной операции до регистра становится предельно коротким (рис. 10). "Транспортным средством" опять выступает формула массива.

Для Журнала 1 "III. З кредиту рахунка 31 “Рахунки в банках” в дебет рахунків" в первую ячейку графы "Каса" (С6) введите такую формулу:

{=СУММ(ЕСЛИ(Баланс.xls!ДатаОпер=B6;ЕСЛИ(ЛЕВСИМВ(Баланс.xls!ДебетЖ;2)=”30";
ЕСЛИ(ЛЕВСИМВ(Баланс.xls!КредитЖ;2)=”31";Баланс.xls!ВсегоЖ))))}.

Эта формула подсчитывает дебетовые обороты по синтетическому счету 30, с кредита счета 31 за дату, указанную в ячейке B6 (на рисунке 10 - за 11.07.2001 г.).

Дебетовые обороты по счету 33 определяются подобной формулой, которая записывается в ячейку D6:

{=СУММ(ЕСЛИ(Баланс.xls!ДатаОпер=B6;ЕСЛИ(ЛЕВСИМВ(Баланс.xls!ДебетЖ;2)=”33";
ЕСЛИ(ЛЕВСИМВ(Баланс.xls!КредитЖ;2)=”31";Баланс.xls!ВсегоЖ))))}.

Отличия этих формул достаточно очевидные и простые, поэтому по остальным счетам вы без труда введете формулы самостоятельно, скопировав предыдущую.

Для заполнения Ведомости 1.3 используются аналогичные формулы.

Для графы "Каса" (ячейка С6, Ведомость 1.3, рис. 10):

{=СУММ(ЕСЛИ(Баланс.xls!ДатаОпер=B6;ЕСЛИ(ЛЕВСИМВ(Баланс.xls!ДебетЖ;2)=”31";
ЕСЛИ(ЛЕВСИМВ(Баланс.xls!КредитЖ;2)=”30";Баланс.xls!ВсегоЖ))))}.

Как видите, в приведенных формулах используются имена ("ДатаОпер", "ДебетЖ", "КредитЖ", "ВсегоЖ"), которые присвоены ячейкам из другой книги ("Баланс"). Поэтому в формулах указан полный адрес каждого имени (например, "Баланс.xls!ДатаОпер"). Порядок присвоения данных имен был рассмотрен в 8­м уроке.

Таким способом вы можете воспользоваться для заполнения любого регистра при определении оборотов по счетам.

На следующем уроке будут рассмотрены приемы создания учетных регистров для микропредприятий, осуществляющих за месяц не более 100 хозяйственных операций. Этот урок может особенно заинтересовать бухгалтеров небольших торговых предприятий и тех, кто работает в сфере общественного питания. Вы узнаете, как в автоматическом режиме заполнять регистр "Книга учета хозяйственных операций" (форма К-1) и "Ведомость учета заработной платы" (форма В-8). Вы также ознакомитесь с приемами автоматизации анализа счетов.


Следующий урок

© 2001
"Дебет-Кредит"
Редакция: debet-kredit@gc.kiev.ua