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

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

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

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


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

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

А вот ее электронный вариант гораздо доступнее и проще в пользовании и, что самое главное, имеет несравненно больше возможностей. И в этом вы сейчас убедитесь.

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

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

Рисунок 1

В соответствии с требованиями Постановления Правления НБУ от 19.02.2001 г. №72 "Об утверждении Положения о ведении кассовых операций в национальной валюте в Украине", ведение кассовой книги в электронной форме должно обеспечивать создание и распечатывание таких документов, как "Вкладной лист кассовой книги" и "Отчет кассира". Данные документы по форме и содержанию должны воспроизводить кассовую книгу в бумажной форме.

Построение формы кассовой книги несложно, поэтому я не привожу размеры ячеек.

"Шапку" книги и таблицы можно заполнить сразу, введя необходимые данные.

Для упрощения ее структуры предлагаемый вариант кассовой книги предусматривает отражение на одном листе за один операционный день не более 41­го документа (при уменьшении размера строки может быть больше). Для компактности кассовая книга показана на рисунке 1 со свернутыми пустыми строками. При распечатывании в развернутом режиме она примет вид, показанный на рисунке 2.

Рисунок 2

Табличная часть ЭКК заполняется по принципу автоматического копирования данных из Реестра (лист "Реєстр каси").

Будет правильнее, если вы откроете ЭКК с начала года, с тем чтобы соблюсти требования Положения №72. Однако можно пользоваться созданной ЭКК с любого периода, придерживаясь принятой нумерации бумажной кассовой книги, страницы которой должны быть пронумерованы в порядке возрастания с начала года.

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

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

С13 "=ЕСЛИ(ИЛИ(ЕПУСТО(A13);ЕПУСТО('Реєстр каси'!K5));
"";'Реєстр каси'!K5)";

Е13 "=ЕСЛИ(ИЛИ(ЕПУСТО(A13);ЕПУСТО('Реєстр каси'!F5));
"";'Реєстр каси'!F5)";

F13 "=ЕСЛИ(ИЛИ(ЕПУСТО(A13);ЕПУСТО('Реєстр каси'!Q5);
ЛЕВСИМВ(B13)="В");"";ЕСЛИ(ЛЕВСИМВ(B13)="П";
'Реєстр каси'!Q5))";

Н13 "=ЕСЛИ(ИЛИ(ЕПУСТО(A13);ЕПУСТО('Реєстр каси'!Q5);
ЛЕВСИМВ(B13)="П");"";ЕСЛИ(ЛЕВСИМВ(B13)="В";
'Реєстр каси'!Q5))".

Использование в этих формулах ссылок на ячейки 5­й строки Реестра (например "'Реєстр каси'!B5") зависит только от того, с какой даты вы начинаете заполнять ЭКК (рис. 3). Если ЭКК открывается с августа 2001 года, то в приведенных формулах ссылки будут на ячейки 11­й строки (например "'Реєстр каси'!B11").

Рисунок 3

Скопируйте эти формулы на другие ячейки табличной части ЭКК, кроме последней строки №54 (на рис. 1 выделено фоном).

Кассовая книга длиной в год на одном листе. Заполнение ЭКК и фильтрация данных

Использование ЭКК для накопления данных обо всех проведенных кассовых операциях и обеспечение возможности отражения состояний кассы за определенный день можно реализовать, фильтруя данные по дате. Хотя этот реквизит и не присутствует в табличной части ЭКК, он будет использован не только для фильтрации данных, но и для заполнения ЭКК.

Введите в ячейку А13 формулу "='Реєстр каси'!D5", которая возвратит дату документа из первой строки Реестра. Так как данные ячеек столбца А являются вспомогательными и не предусмотрены формой кассовой книги, их надо скрыть, установив для них белый цвет шрифта. Теперь для того, чтобы заполнить ЭКК, достаточно будет скопировать формулу из А13 в нижние ячейки до 53­й строки включительно (рис. 4). В остальных ячейках табличной части ЭКК отразятся данные, внесенные в Реестр.

Рисунок 4

Внимание! Обязательно введите в ячейки А54:А63 формулу "=$C$8" (ссылка на ячейку, в которую записывается дата составления кассы). Это даст возможность при включении фильтра отражать нижнюю часть ЭКК - ячейки D55:I63 (рис. 4).

Теперь выделите А12:В12 и включите команду "Автофильтр". На рисунке 1 показана ЭКК с датой кассы на 16.07.2001 г., хотя в табличной части представлены данные всех скопированных из Реестра документов, так как на фильтре установлено правило отбора "(Непустые)". Для того чтобы содержание ЭКК соответствовало дате, введенной в ячейку С8, выберите из записей таблицы, открытой стрелкой фильтра, строку "16.07.2001". В результате ЭКК будет содержать данные только на указанную дату.

Автоввод остатков кассового дня

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

- в Реестре (лист "Реєстр каси") внесите дополнение (рис. 5) - в ячейку G1 запишите сумму остатка кассы на дату открытия Реестра (дата первого документа Реестра).

Рисунок 5

Для большей информативности в ячейку О1 введите ссылку на первую дату Реестра - "=D5";

- в ячейку G2 запишите сумму остатка на зарплату на дату открытия Реестра;

- присвойте на листе "Реєстр каси":

имя "СимволДок" - ячейкам В5:В105;

имя "ДатаДок" - ячейкам D5:D105;

имя "СуммаДок" - ячейкам Q5:Q105 (рассчитано на хранение в Реестре 101­го документа - условный размер, можно при необходимости увеличить);

имя "ВсегоКасКн" - ячейкам Е5:Е105,

имя "Рахунок" - ячейкам F5:F105.

Чтобы получить остаток на начало любого кассового дня, введите в ячейку F12 формулу массива (при завершении ее ввода не забудьте нажать Ctrl - Shift - Enter):

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

Если вы не введете дату кассы, в ЭКК будет показана сумма остатка на дату открытия Реестра (в примере, приведенном на рис. 5, остаток - 185,90). Если в ячейке С8 указана дата, за которую не осуществлялись кассовые операции, то будет показан остаток на начало последнего кассового дня.

Значение, которое отражает сумму остатка на конец кассового дня, может быть различным, в зависимости от введенной в ячейку С8 даты:

- если введена дата, в течение которой не совершались кассовые операции, в ячейке F55 ("Усього Прибуток") будет записан остаток на предыдущий по отношению к введенной дате кассовый день, а в ячейку H55 ("Усього Видаток") - 0;

- если в указанную ячейку дата не введена, в итоговых ячейках будут показаны данные на день открытия ЭКК;

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

Эти условия будут обеспечены при вводе следующих формул массива:

ячейка F55 - {=СУММ(ЕСЛИ(СимволДок="П";
ЕСЛИ(C8=ДатаДок;СуммаДок)))+F12};

ячейка H55 - {=СУММ(ЕСЛИ(СимволДок="В";
ЕСЛИ(C8=ДатаДок;СуммаДок)))}.

Остаток на конец дня, ячейка F56, определяется формулой "=F55-H55".

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

{=СУММ(ЕСЛИ(СимволДок="П";ЕСЛИ(C8>=ДатаДок;ЕСЛИ(Рахунок=661;СуммаДок))))-СУММ(ЕСЛИ(СимволДок="В";ЕСЛИ(C8>=ДатаДок;ЕСЛИ(Рахунок=661;СуммаДок)))))+'Реєстр каси'!Q2}.

Если вы не введете в ячейку C8 дату, то в кассовой книге будет записан начальный остаток на дату открытия Реестра.

Маленькие хитрости в ЭКК

Работа с кассой станет проще и приятнее, если применить дополнительные приемы автоматизации. После завершения такой доработки лист "Касова книга" примет вид, показанный на рисунке 6.

Рисунок 6

Переключение режима кассовой книги

Как было сказано выше, использование ПК для ведения кассовой книги требует подготовки документов "Вкладной лист кассовой книги" и "Отчет кассира", которые по форме и содержанию не отличаются от кассовой книги.

Чтобы иметь возможность распечатывать и отчет кассира, и вкладной лист кассовой книги, в ячейку Е8 запишите формулу

=ЕСЛИ(K8="З";"ЗВіТ КАСИРА";
"ВКЛАДНИЙ АРКУШ КАСОВОЇ КНИГИ").

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

Макрос "Касова книга" Макрос "Звіт касира"
Шаг 1 Установите формат "неактивная" Установите формат "неактивная"
для кнопки "Звіт касира" для кнопки "Касова книга"
Шаг 2 Установите формат "активная" Установите формат "активная"
для кнопки "Касова книга" для кнопки "Звіт касира"
Шаг 3 Очистите ячейку К8, остановите запись Введите в К8 символ "З", остановите запись

Внимание! Располагайте управляющие элементы макросов вне зоны действия фильтра (строки с 13­й по 53­ю включительно).

Автонумерация листов

Согласно Положению №72, страницы кассовой книги должны автоматически нумероваться в порядке возрастания с начала года. Такую нумерацию можно обеспечить формулой массива, введенной в ячейку Е8:

{=СУММ(ЕСЛИ(C8>=ДатаДок;
ВсегоКасКн))))+'Реєстр каси'!E2}.

Так как вы можете открыть Реестр не обязательно с начала года, то в одну из его ячеек (например, Е2, рис. 5) введите общее число листов бумажной кассовой книги на дату его открытия.

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

Рисунок 7

Это требование выполняется с помощью формулы массива. Введите ее в ячейку I8:

{=ЕСЛИ(ИЛИ(K8="З";ЕПУСТО(L4));"";ЕСЛИ(МЕСЯЦ(C8)=12;СУММ(ВсегоКасКн)+
'Реєстр каси'!E2;СУММ(ЕСЛИ(МЕСЯЦ(C8)=МЕСЯЦ(ДатаДок);ВсегоКасКн))))}.

Информация об общем количестве листов будет появляться только в режиме "Касова книга", именно поэтому в приведенной формуле записано условие "K8="З"" Так как необходимость распечатывания вкладного листа кассовой книги с указанием общего количества листов возникает только в конце каждого месяца, активизация режима "конец месяца" производится введением символа в ячейку L4.

Чтобы не запутаться в этой простой процедуре, рекомендую воспользоваться оригинальным переключателем между двумя режимами "Вкладного листа кассовой книги": "текущий день" и "конец месяца". Для этого с помощью окна "Формы" поместите на лист два управляющих элемента "Переключатель", присвойте им имена "поточний день" и "місяць (рік)" (рис. 7), а затем запишите для них два простейших макроса.

Макрос "Вимкнути_аркуші" Макрос "Всього_аркушів"
Шаг 1 Назначьте переключателю "поточний день" Назначьте переключателю "місяць (рік)"
макрос "Вимкнути_аркуші". макрос "Всього_аркушів".
Шаг 2 Установите курсор в ячейку L4 и очистите Установите курсор в ячейку L4 и введите в
ее содержимое (нажмите клавишу Del) нее любой символ (например "Х")
Шаг 3 Остановите запись Остановите запись

После записи макросов расположите переключатели над управляющей ячейкой.

Теперь, если щелкнуть ЛКМ, например, на переключателе "місяць (рік)", переключатель "поточний день" выключится, а в ячейке I8 появится общее количество листов за месяц, указанный в ячейке С8 (если введен "декабрь", будет подсчитано общее количество листов за текущий год).

При переключении в режим "Отчет кассира" эта информация появляться не будет.

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

Для того чтобы при подготовке отчета кассира или вкладного листа кассовой книги скрывать строку "Усього аркушів за", введите в ячейку G8 формулу:

=ЕСЛИ(ИЛИ(K8="З";ЕПУСТО(L4));"";
"Усього листів за").

Для этой ячейки установите выравнивание по правому краю.

Появление в ячейке Н8 названия текущего месяца или порядкового номера текущего года с текстом "рік" выполняется формулой:

=ЕСЛИ(ИЛИ(K8="З";ЕПУСТО(L4));"";
ЕСЛИ(МЕСЯЦ(C8)=12;ГОД(C8)&" рік";C8)).

Для ячейки Н8 установите формат даты типа "М" (рис. 8).

Рисунок 8

Сколько сегодня заполнено приходных ордеров?

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

Небольшой блок, созданный на листе ЭКК, подскажет вам необходимую информацию о созданных кассовых документах (рис.9).

Рисунок 9

Присвойте на листе "Реєстр" ячейкам S5:S105 имя "ВсегоДок". Во все ячейки этого массива введите формулу "=ЕСЛИ(ЕПУСТО(B10);"";1)". Она будет возвращать значение "1", если в Реестр внесены данные по кассовому документу.

Теперь в ячейки блока запишите такие формулы:

L59 "=ЕСЛИ(ЕПУСТО(C8);"";C8);"

K61 "{=СУММ(ЕСЛИ(СимволДок="П";
ЕСЛИ(ДатаДок=C8;ВсегоДок)))};"

K62 "{=СУММ(ЕСЛИ(СимволДок="В";
ЕСЛИ(ДатаДок=C8;ВсегоДок)))}".

День, когда закрыта касса

Что произойдет, если в ячейку С8 ввести дату, отсутствующую в Реестре? В итоговых ячейках ЭКК появятся другие величины, а данные табличной части не изменятся. Для того чтобы неправильный ввод даты не вызывал у вас дополнительных вопросов, можно организовать на листе автоматическую проверку наличия введенной даты в Реестре (рис. 10).

Рисунок 10

Введите в ячейку L8 (в качестве примера) формулу

"=ЕСЛИ(ЕПУСТО(C8);"";
ПОИСКПОЗ(C8;ДатаДок;0))".

В результате, если введенная дата отсутствует, в ячейку L8 вернется значение ошибки "#Н/Д".

Теперь установите для ячейки С8 условное форматирование, используя в качестве условия формулу "=ЕОШИБКА($L$8)=ИСТИНА". Формат выберите по желанию (рис. 11).

Рисунок 11

Для появления подсказки в ячейки С7 и С9 можно записать такие формулы:

=ЕСЛИ(ЕОШИБКА(L8)=ИСТИНА;
"Введи в С8 іншу дату";"");

=ЕСЛИ(ЕОШИБКА(L8)=ИСТИНА;
"На цю дату даних немає";"").

Порядок работы с ЭКК

Введенные в ЭКК формулы позволят максимально автоматизировать ее заполнение и получение всей регламентированной информации. Предложенную систему ведения кассовых операций наиболее рационально использовать на малых предприятиях, с небольшим количеством кассовых операций. При работе с ЭКК необходимо помнить некоторые правила:

- в табличную часть ЭКК должны быть предварительно введены формулы;

- введите в ячейку, расположенную слева от первой строки табличной части ЭКК (столбец А), ссылку на первую ячейку графы "Дата складання" Реестра;

- заполнение очередной строки табличной части ЭКК осуществляется только (!) копированием предыдущей ячейки столбца А (рис. 12).

Рисунок 12

Данные в табличной части ЭКК могут не появиться, если в ней отсутствуют формулы или не заполнен Реестр;

- чтобы подготовить "Вкладной лист кассовой книги", введите дату в ячейку С8;

- для фильтрации данных табличной части ЭКК в списке, открытом стрелкой фильтра (ячейка А12, рис. 12), выберите строку с датой, совпадающей с датой ячейки С8.

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


АНОНС

"Excel для бухгалтера - отлично, но, согласитесь, большинство бухгалтеров сейчас работают с 1С, это более всеохватывающая программа, хоть и более сложная. Существует ли возможность начать уроки 1С, с описанием подводных камней и обучением по конфигурированию и программированию? Хотя бы для типовых конфигураций? Было бы очень интересно!"

И это не единственное письмо из приходящих в нашу рубрику с такими предложениями. Когда мы начинали публикации по Excel, не тешили себя иллюзиями насчет перспектив использования бухгалтерами этой программы. Главное, чего удалось добиться, - благодаря этим публикациям многие наши читатели получили действенную помощь в автоматизации бухучета, они поняли, как просто можно облегчить их нелегкий труд, используя компьютер. Но жизнь не стоит на месте.

Безусловно, Excel не решит всех проблем современного бухгалтера. Существуют мощные, гибкие, адаптированные программы для бухгалтера, среди которых ведущее место занимает серия программных продуктов фирмы 1С. Учитывая многочисленные пожелания наших читателей, с октября 2001 года мы начинаем публиковать цикл статей, посвященных секретам использования программы "1С:Предприятие".

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

Горячая линия, тел/факс 294-6013,
e-mail: debet-kredit@gc.kiev.ua, lavrenov@gc.kiev.ua.
Руководитель проекта "Автоматизация бухгалтерского учета" В. Лавренов

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