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

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

РЕГИСТРЫ БУХГАЛТЕРСКОГО УЧЕТА И КАССОВЫЕ ДОКУМЕНТЫ. ЗАПОЛНЕНИЕ И ХРАНЕНИЕ С ПОМОЩЬЮ EXCEL

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


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

На последних уроках мы достаточно подробно рассмотрели принципы создания и способ хранения различных платежных документов. Еще раньше вы ознакомились с вариантом подготовки финансовой отчетности предприятия, в котором для связывания журнала хозяйственных операций с отчетными формами использовался искусственно созданный регистр "Главная книга".

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

Касса и кассовые операции. Учет денежных средств и денежных документов

В соответствии с Методическими рекомендациями, хозяйственные операции должны отражаться в учетных регистрах во время поступления первичных документов или итогами за месяц, в зависимости от характера и содержания операций. Информация о наличии и движении денежных средств и денежных документов как в национальной, так и в иностранной валюте, которые находятся в кассе предприятия, обобщается в Журнале 1 и в прилагаемых к нему ведомостях.

Записи в Журнале 1 и ведомостях осуществляются на основании первичных и сводных учетных документов. К таким документам относятся:

- кассовая книга и отчеты кассира;

- приходные и расходные кассовые ордера, прилагаемые к отчетам кассира;

- выписки банка и прилагаемые к ним документы (например, объявление о внесении наличных средств).

Создание и заполнение этих документов в автоматическом режиме проиллюстрируем на примере заполнения Журнала 1, а также внесения этой операции в Журнал хозяйственных операций.

Перед нами стоит задача автоматически заполнить:

- кассовые документы,

- Журнал регистрации кассовых документов,

- кассовую книгу,

- регистр бухгалтерского учета - Журнал 1.

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

Рисунок 1.


Создание и хранение расходного кассового ордера

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

Для удобства ее создания вы можете воспользоваться размерами, указанными по периметру формы. Обратите внимание на то, что данная форма создана в стиле "Обычный Arial Cyr 10". Установите также на вкладке "Поля" (меню "Вид" - "Колонтитулы") нулевые значения во всех окнах.

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

Рисунок 2.

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

Для автоматической идентификации номера кассового документа в среде Excel лучше использовать текстовый номер (типа П267). Это позволит вам не только визуально находить нужный документ, но и автоматизировать обработку и анализ документов. Текстовый номер состоит из символа вида документа, например "П" - т. е. приходный кассовый ордер или "В" - расходный кассовый ордер, который записывается в графу 2 Реестра и цифровой части номера, записанного в графу 3.

Для безошибочного ввода номера кассового документа записывайте только символ вида документа ("П" или "В") в графу "Вид докум.", а полный номер документа будет генерироваться автоматически. Для этого предварительно:

- в ячейку А5 введите цифру "1",

- в ячейку А6 введите формулу "=ЕСЛИ(ЕПУСТО(B6);"";A5+1)" и скопируйте ее в другие ячейки графы;

- в ячейку С5 введите формулу "=ЕСЛИ(ЕПУСТО(B5);"";A5+100)" и также скопируйте ее в другие ячейки графы (если вы будете использовать 4-значную кодировку, последним аргументом в формуле будет "А5+1000").

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

Данные графы 9 "Символ" используются для заполнения Журнала кассовых документов (форма КО-3), порядок которого будет рассмотрен ниже.

Для автоматического ввода данных физического лица, на которое оформляется кассовый документ, используются ячейки графы 10, куда вносится порядковый номер физического лица. Порядковый номер выбирается из списка физических лиц, перечень которых вы можете хранить на отдельном листе "Список" (рис. 3). Присвойте ячейкам А4:Н23 имя "Список" (размер условный).

Рисунок 3.

Для автозаписи фамилий и инициалов в ячейки графы 11 введите в ячейку такие формулы:

К5 =ЕСЛИ(ЕПУСТО(J5);"";ВПР(J5;Список;2;ЛОЖЬ)&" "&ЛЕВСИМВ(ВПР(J5;Список;3;ЛОЖЬ)) &". "&ЛЕВСИМВ(ВПР(J5;Список;4;ЛОЖЬ))&"."),

для записи полного имени в ячейки графы 12:

L5 =ЕСЛИ(ЕПУСТО(J5);"";ВПР(J5;Список;2;ЛОЖЬ)&" "&(ВПР(J5;Список;3;ЛОЖЬ))&" "&ВПР (J5;Список;4;ЛОЖЬ));

для записи паспортных данных (графа 14):

N5 =ЕСЛИ(ЕПУСТО(J5);"";ВПР(J5;Список;2;ЛОЖЬ)).

Еще одна особенность Реестра - заполнение графы "Сума словами". Для ее заполнения вы можете использовать блок "Сумма прописью", структура которого была рассмотрена в уроке 12 ("ДК" №22). Скопируйте из Журнала регистрации платежных документов ячейки R6:АР6 (выделяйте ячейки при нажатой клавише Ctrl). Затем, используя команду "Специальная вставка" (чтобы не переносить форматы, установите опцию "формула"), вставьте их в ячейку R5 Реестра (рис. 2). Не снимая выделения с ячеек, скопируйте их в остальные ячейки Реестра. Из столбцов S и T удалите скопированные из журнала-источника числовые значения и формулы.

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

Созданный Реестр будет являться одновременно и архивом кассовых документов, и источником данных для автоматического их заполнения. Для этого прежде всего присвойте имя массиву ячеек A5:R104 (такой размер позволит создать и хранить 100 кассовых первичных документов), например, имя "РеестрКас".

Внимание! Установите защиту для ячеек Реестра, в которых записаны формулы.

Непосредственное заполнение кассового ордера сводится к вводу в выбранную вами ячейку-источник порядкового номера документа из графы 1 (не путайте с данными графы 3 "№ докум."). Положение ячейки-источника не имеет значения, можно использовать даже поле документа, установив для этой ячейки цвет шрифта "белый" (в этом случае вводимое значение можно контролировать в строке формул).

Определите для себя, что ячейка-источник будет находиться, например, по адресу А12 (чтобы при печати не было видно введенное в нее значение, установите для ячейки-источника белый шрифт). Формулы автозаполнения будут записаны в таких ячейках (см. рис. 1, "Видатковий касовий ордер"):

В12 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;2;ЛОЖЬ)&ВПР(A12;РеестрКас;3;ЛОЖЬ)";

С12 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;4;ЛОЖЬ)";

F12 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;6;ЛОЖЬ))";

H12 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;7;ЛОЖЬ))";

I12 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;17;ЛОЖЬ))";

C14 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;12;ЛОЖЬ))";

C16 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;13;ЛОЖЬ))";

B17 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;18;ЛОЖЬ))";

C25 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;14;ЛОЖЬ))".

Реквизиты-константы (В1, D4, J5) введите в форму бланка, остальные реквизиты должны заполняться должностными лицами и получателем вручную.


Журнал регистрации приходных и расходных кассовых документов. Кассовая книга

В электронном виде удобнее вести Журнал регистрации кассовых ордеров по форме №КО-3, в котором можно не только обобщить больший объем информации, но и соединить преимущества формы №КО-3а (при ведении "бумажного" журнала по форме №КО-3 необходимо отдельно открывать его и для приходных, и для расходных ордеров).

Форма Журнала, созданная на листе "ЖурналКасДок", - стандартная (рис. 4). Все данные в него вносятся автоматически с помощью несложных формул.

Рисунок 4.

Введите их в табличную часть Журнала, первой ячейкой которой является В17 (не забудьте, что при вводе в формулу ссылки на ячейку другого листа можно воспользоваться приемом связывания листов).

Запишите в ячейки:

В17 "=ЕСЛИ(ЕПУСТО('Реєстр каси'!C5);"";'Реєстр каси'!D5)";

С17 "=ЕСЛИ(ЕПУСТО('Реєстр каси'!B5);"";'Реєстр каси'!B5&'Реєстр каси'!C5)";

D17 "=ЕСЛИ(ЕПУСТО('Реєстр каси'!J5);"";ЕСЛИ($D$14='Реєстр каси'!J5;'Реєстр каси'!Q5;""))";

Е17 "=ЕСЛИ(ЕПУСТО('Реєстр каси'!J5);"";ЕСЛИ($Е$14='Реєстр каси'!J5;'Реєстр каси'!Q5;""))".

В зависимости от количества видов целевого использования наличных средств в ячейки F17 и G17 введите формулы, подобные двум последним.

Вышеприведенные формулы скопируйте до 30­й строки включительно (рис. 4).

Внимание! В сводном Журнале последняя строка табличной части перед строкой "Разом" должна быть пустой.

Это даст возможность вставлять необходимое число пустых строк по мере заполнения Журнала. В добавленные ячейки копируются формулы из ячеек В17:G17.

Для подсчета суммы в сводном журнале и отдельно в каждом режиме используются формулы массива. Присвойте ячейкам С17:С31 имя "НомерДок". В зависимости от принятого учета движения наличных средств по целевому назначению присвойте имена массивам ячеек, расположенным в графах 3, 4, 5, 6. Так как в приведенном примере в Журнале используются только графы 3 и 4, ячейки D17:D31 назовите "Сума3", а ячейки Е17:Е31 - "Сума4".

D32 "{=СУММ(ЕСЛИ(ЛЕВСИМВ(НомерДок)=C32;Сума3;ЕСЛИ(C32=0;Сума3)))}";

E32 "{=СУММ(ЕСЛИ(ЛЕВСИМВ(НомерДок)=C32;Сума4;ЕСЛИ(C32=0;Сума4)))}".

Если учет целевого использования ведется и в других разрезах, для ячеек F17:F31 и G17:G31 введите дополнительно имена "Сума5", "Сума6", а в ячейки F32 и G32 соответствующие им формулы массива.

В ячейку C32 введите формулу "=С16". Если теперь в ячейку С16 записать символ "В" или символ "П", то в итоговых ячейках появится соответственно сумма по расходным ордерам или по приходным. Если ячейка С16 останется пустой, то в Журнале отразится сумма по всем ордерам.

Почему указанные символы не вводятся непосредственно в ячейку C32, будет показано ниже.

Итак, вы уже имеете сводный Журнал всех кассовых ордеров, но когда потребуется распечатать отдельно Журнал расходных или приходных ордеров, возникнет вопрос...


Как разделить сводный Журнал на два специальных. Использование фильтра

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

Активизируйте фильтр: выделите первую строку над табличной частью журнала (В16:Н16) и введите команду "Автофильтр" (меню "Данные" - "Фильтр", рис. 5). В каждой выделенной ячейке справа появится стрелка, которая открывает список значений ячеек соответствующего столбца. В этом списке можно задать необходимый критерий, после чего ячейки этого списка, не удовлетворяющие выбранному критерию (и все строки, в которых находятся такие ячейки), будут скрыты.

Рисунок 5

Например, выберите в списке первой графы строку "16.07.01", и в Журнале будут показаны только ордера, оформленные 16 июля 2001 года. Номера строк и стрелка автофильтра в столбце, по значению которого отбирались строки, выделяются голубым цветом. Для того чтобы из всего списка документов Журнала показать только приходные ордера, выберите строку "(Условие...)" (рис. 6). Появится окно "Пользовательский автофильтр", в левом поле которого выберите подходящий оператор, например "начинается с", а в правое поле введите символ "П". В результате в Журнале будут видны только данные по приходным кассовым ордерам (такой же результат получится, если в левом поле выбрать оператор "равно", а в правое поле ввести "П*").

Рисунок 6

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

- сводный Журнал - режим "Все документы";

- режим "Приходные ордера";

- режим "Расходные ордера".

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

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

Запись макросов рассмотрим на примере первого макроса "Прибуткові_ордери".

Шаг 1. После начала записи щелкните ПКМ на кнопке "Видаткові ордери" и в меню выберите команду "Формат объекта". Установите для кнопки формат надписи "неактивная" (вариант: начертание - "обычный", цвет - белый) и нажмите ОК.

Внимание! Не начинайте операцию форматирования с кнопки, для которой вы записываете макрос.

Шаг 2. Щелкните ПКМ на кнопке "Всі документи" и отформатируйте надпись кнопки для варианта "неактивная" (как в Шаге 1).

Шаг 3. Щелкните ПКМ на кнопке "Прибуткові ордери" и отформатируйте ее название как "активная" (например, начертание - "полужирный", цвет - "Авто").

Шаг 4. Щелкните на ячейке С16 и введите в нее символ "П".

Шаг 5. Щелкните на стрелке автофильтра, расположенной в графе 2 "Номер", и выберите правило отбора записей таблицы "(Условие...)". В окне "Пользовательский автофильтр" в левом поле выберите оператор "начинается с", а в правое введите символ "П" (регистр не имеет значения). Нажмите ОК.

Шаг 6. Остановите запись макроса.

Последовательность действий при записи макросов для двух других кнопок подобна. Отличия приведены в таблице.

Макрос "Видаткові_ордери" Макрос "Всі_документи"

Шаг 1 Установите формат "неактивная" Установите формат "неактивная"

для кнопки "Прибуткові ордери" для кнопки "Прибуткові ордери"

Шаг 2 Установите формат "неактивная" Установите формат "неактивная"

для кнопки "Всі документи" для кнопки "Видаткові ордери"

Шаг 3 Установите формат "активная" Установите формат "активная"

для кнопки "Видаткові ордери" для кнопки "Всі документи"

Шаг 4 В ячейку С16 введите символ "В" Из ячейки С16 удалите символ

Шаг 5 Выберите правило отбора записей Выберите правило отбора записей

таблицы "(Условие...)". В окне таблицы "Все"

"Пользовательский автофильтр" в левом

поле выберите оператор "начинается с",

а в правое введите символ "В"

Шаг 6 Остановка записи Остановка записи

Теперь поясню, почему в ячейку C32 была введена формула "=С16", а при записи макросов значения записывались именно в ячейку С16. Дело в том, что если в макросы записать значения символов ("В", "П", "пусто") в С32, то при увеличении размера табличной части макрос будет записывать и считывать символ только из этой ячейки. Строка "Разом" при переключении режимов станет невидимой.

И наконец, еще один шаг в автоматизации заполнения Журнала. При переключении режимов можно соответственно менять и названия граф Журнала, если ввести в ячейки следующие формулы:

В12 "=ЕСЛИ(C16="П";"Прибутковий документ";ЕСЛИ(C16="В";"Видатковий документ";"Всі прибуткові та видаткові документи"))",

D12 "=ЕСЛИ(C16="П";"Надійшло грошей";ЕСЛИ(C16="В";"Витрачено грошей";"Надійшло (витрачено) грошей"))".

Внимание! Ввод в Журнал новых данных по кассовым ордерам осуществляйте только копированием записанных формул в дополнительные строки.

Например, если кассовых ордеров создано (на листе "Реєстр каси") больше, чем позволяет табличная часть Журнала (на рис. 4 максимальный объем - 14 ордеров), то ваши действия должны быть такими:

- выделите строку 31 и добавьте нужное число строк (например 10);

- выделите ячейки B30:G30 и скопируйте их до строки 40 включительно.


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


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

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