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

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

Заполнение, учет и хранение платежных документов с помощью EXCEL

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


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

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

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

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

С подготовки этого документа начинаем сегодняшний урок.

Форма одинаковая, содержание - различно

Как определено в Постановлении Правления НБУ от 29.03.2001 г. №135 "Об утверждении Инструкции о безналичных расчетах в Украине в национальной валюте" (далее - Инструкция №135), платежное требование для принудительного списания денежных средств используют "взыскатели", как имеющие счет в банке, так и не имеющие такового.

Распоряжение о принудительном списании оформляется на бланке платежного требования, имеющем две формы:

1-я форма заполняется в случае, когда "взыскатель" и получатель - одно лицо;

2-я форма заполняется, когда "взыскатель" и получатель - разные лица.

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

Рисунок 1

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

Для того чтобы трансформировать готовую форму платежного поручения в платежное требование, необходимо:

- несколько изменить реквизиты документа и его графику;

- изменить формулы, используемые для заполнения реквизитов "взыскателя" и получателя.

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

Рисунок 2

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

Как с помощью формулы изменить форму документа

Ключевой ячейкой, которая будет управлять переключением режима документа, определим W5. В зависимости от того, какой документ создается - платежное поручение или платежное требование, в эту ячейку будет записываться какой-либо символ или она (ячейка) будет оставаться пустой.

Прежде чем записывать макросы, в ячейки, определяющие форму документа (см. рис. 2), запишем формулы.

Для изменения названия документа в ячейку D4 введите:

=ЕСЛИ(W5=0;"ПЛАТІЖНАВИМОГА №";"ПЛАТІЖНЕ ДОРУЧЕННЯ №").

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

Для изменения наименования реквизитов "взыскателя" и "получателя" запишите в ячейку В16:

=ЕСЛИ(W5=0;"Стягувач";"Одержувач")

а в ячейку В20:

=ЕСЛИ(W5=0;"Банк стягувача";"Банк одержувача").

Согласно Государственному классификатору управленческой документации (ДКУД), платежное поручение имеет код 410001, а платежное требование - 410007. При изменении кода документа изменяется и его месторасположение, поэтому запишите в ячейку R2:

=ЕСЛИ(W5=0;410007;""),

а в ячейку R4:

=ЕСЛИ(W5=0;"банком";410001).

Объедините ячейки R2:S2 и ячейки R4:S4 и установите для них горизонтальное и вертикальное выравнивание по центру.

В платежном требовании в строке, где помещается надпись "Одержано банком", в той же строке в платежном поручении находится код ДКУД, поэтому название этого реквизита разбито на две части. В ячейке Q4 запишите формулу

=ЕСЛИ(W5=0;"Одержано";"").

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

В ячейку N5 введите формулу:

=ЕСЛИ(W5=0;"' __ '____________200__ р.";"Одержанобанком").

Пробелы, выделенные фоном, установите такими, чтобы в платежном требовании верхний реквизит даты был одинаковым с нижним реквизитом (рис. 2).

Рисунок 3

Теперь достаточно в ячейку W5 ввести какой-либо символ, и в окне появится форма платежного поручения. Если из ячейки W5 убрать записанное в нее значение, форма платежного поручения трансформируется в форму платежного требования.

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

Рисунок 4

Условное форматирование: переключаем оформление ячеек

Итак, необходимо менять формат ячейки при изменении определенного условия - при переключении режима с "платежного поручения" на "платежное требование" рамка реквизита "код ДКУД" должна "перескакивать" из ячейки R4 в ячейку R2. Как это сделать?

Установите курсор в ячейку R2 и откройте окно "Условное форматирование" (меню "Формат", рис. 5). Для установки определенного формата ячейки можно использовать не более трех условий. Условия можно устанавливать с помощью интервалов значений или с помощью формул. Для установки формата в ячейках R2 и R4 (внешних границ ячеек R2:S2 и R4:S4) достаточно будет двух условий, заданных с помощью формул.

Рисунок 5

В первом поле из списка типов условных форматов выберите "формула". Соседние поля объединятся в одно поле для ввода формулы, которая должна начинаться со знака равенства "=".

1. Введите для первого условия формулу "=$W$5=0". Вы можете ввести знак равенства и затем щелкнуть ЛКМ по ячейке W5 - абсолютная ссылка $W$5 вводится автоматически.

2. Щелкните на кнопке "Форматѕ" и в открывшемся окне на вкладке "Границы" установите формат "внешние". Нажмите на кнопку ОК.

3. Щелкните на кнопке "А также >>" и введите формулу второго условия: "=$W$5<>0".

4. В окне "Формат ячеек" на вкладке "Граница" установите формат "Нет". Нажмите ОК.

5. Закройте окно "Условное форматирование", нажав ОК.

Активизируйте ячейку R4. Установите для нее такие же форматы первого и второго условия, как и для ячейки R2. Отличие будет заключаться в формулах. Для первого условия введите "=$W$5<>0", а для второго - "=$W$5=0".

Теперь при введении или удалении символа в ячейке W5 форма документа будет полностью соответствовать требованию Инструкции №135.

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

Рисунок 6

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

Запись макроса

Перед записью макроса осталось ввести в ячейки реквизитов документа сводные формулы:

D8 =ЕСЛИ(W5=0;ВПР(G4;Регистр;6;ЛОЖЬ);ВПР(1;Контрагент;3;ЛОЖЬ)
C10 =ЕСЛИ(W5=0;ВПР(G4;Регистр;7;ЛОЖЬ);ВПР(1;Контрагент;4;ЛОЖЬ)
E12 =ЕСЛИ(W5=0;ВПР(G4;Регистр;8;ЛОЖЬ);ВПР(1;Контрагент;5;ЛОЖЬ)
I13 =ЕСЛИ(W5=0;ВПР(G4;Регистр;10;ЛОЖЬ);ВПР(1;Контрагент;7;ЛОЖЬ)
М13 =ЕСЛИ(W5=0;ВПР(G4;Регистр;11;ЛОЖЬ);ВПР(1;Контрагент;8;ЛОЖЬ)
E14 =ЕСЛИ(W5=0;"в м. "&ВПР(G4;Регистр;9;ЛОЖЬ);"в м. "&ВПР(1;Контрагент;6;ЛОЖЬ))

Формулы реквизитов получателя ("взыскателя") абсолютно одинаковы, за исключением логического выражения "W5=0", которое изменилось на "W5<>0".
D16 =ЕСЛИ(W5<>0;ВПР(G4;Регистр;6;ЛОЖЬ);ВПР(1;Контрагент;3;ЛОЖЬ)
C18 =ЕСЛИ(W5<>0;ВПР(G4;Регистр;7;ЛОЖЬ);ВПР(1;Контрагент;4;ЛОЖЬ)
E20 =ЕСЛИ(W5<>0;ВПР(G4;Регистр;8;ЛОЖЬ);ВПР(1;Контрагент;5;ЛОЖЬ)
I21 =ЕСЛИ(W5<>0;ВПР(G4;Регистр;10;ЛОЖЬ);ВПР(1;Контрагент;7;ЛОЖЬ)
М21 =ЕСЛИ(W5<>0;ВПР(G4;Регистр;11;ЛОЖЬ);ВПР(1;Контрагент;8;ЛОЖЬ)
E22 =ЕСЛИ(W5<>0;"в м. "&ВПР(G4;Регистр;9;ЛОЖЬ);"в м. "&ВПР(1;Контрагент;6;ЛОЖЬ))

Внимание! Для корректного выполнения формул типа "ВПР(1;Контрагент;3;ЛОЖЬ)" в массиве "Контрагент" (лист "Контрагент") в его первой строке должны находиться реквизиты вашего предприятия.

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

Рисунок 7

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

Шаг 1. Щелкните ПКМ на кнопке "для друку доручення", и в контекстном меню выберите команду "Назначить макрос".

Шаг 2. В открывшемся окне введите имя макроса, например "Доручення", - и можно начинать запись, нажав на кнопку "Записатьѕ", а затем ОК (рис. 8).

Рисунок 8

Шаг 3. Щелкнув ПКМ на кнопке "платіжна вимога", с помощью контекстного меню откройте окно "Формат элемента управления" и установите следующие форматы для надписи кнопки: размер шрифта - 10, написание - "обычный", цвет - "белый". И нажмите кнопку ОК.

Шаг 4. Щелкните ПКМ на кнопке "платіжне доручення" и, повторив вышеприведенные действия, установите для нее такие параметры: размер шрифта - 10, написание - "полужирный", цвет - "Авто". Вы можете выбрать любые параметры для формата надписей кнопок.

Шаг 5. Щелкните ЛКМ на ячейке W5 и введите в нее любой символ.

Шаг 6. Щелкните на любой ячейке рабочего листа (например G4) и остановите запись макроса.

Запись макроса для подготовки платежного требования лишь незначительно отличается от предыдущего.

Шаг 1. То же.

Шаг 2. То же.

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

Шаг 4. Щелкните ПКМ на кнопке "платіжна вимога" и отредактируйте формат надписи кнопки: размер шрифта - 10, начертание - "полужирный", цвет - "Авто".

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

Шаг 5. Активизируйте ячейку W5 и нажмите клавишу Del (для удаления содержимого ячейки).

Шаг 6. То же.

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

Подсказка для директора

Может сложиться ситуация, когда в отсутствие бухгалтера директору необходимо будет срочно подготовить платежный документ. Он открывает нужное экселевское окно "Платеж", нажимает на нужную кнопку, но документ не создается. Оказывается, нужно знать, что первым шагом в подготовке документа является ввод номера документа. Чтобы исключить эту казусную ситуацию, достаточно в верхнюю ячейку пульта управления (рис. 9), V2, ввести, например, такую формулу:

=ЕСЛИ(ЕПУСТО(G4);"Введи № документа в G4";"Підготовлено").

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

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

Рисунок 9

Реестр платежных требований

Платежное требование и сопроводительные документы подаются в обслуживающий банк вместе с реестром платежных требований. Форма реестра определена Инструкцией №135. Поэтому будет не лишним иметь готовый бланк реестра. Вы сможете быстро его подготовить, воспользовавшись размерами, показанными на рисунке 10.

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

"Стягувач" "=Контрагент!C6";
"Код" "=Контрагент!D6";
"Рахунок №" "=Контрагент!H6";
"Банк стягувача" "=Контрагент!E6";
"в м." "=Контрагент!F6";
"Код банку" "=Контрагент!G6".

Внимание! Эти формулы проще вводить не вручную, а используя связь между листами. Установите в целевую ячейку (например D2) знак равенства, откройте лист "Контрагент" и щелкните на ячейке с нужным реквизитом.

Правилами заполнения реквизитов расчетных документов определено, что реквизит "Дата составления" документа заполняется в формате ДД/ММ/РРРР.

Выберите необходимый формат в поле "Тип" окна "Формат ячейки". Если такой формат у вас не установлен, в поле "Числовые форматы" выберите строку "все форматы", а затем в редактируемой строке поля "Тип" установите нужный формат (рис. 10).

Рисунок 10

Заполнение графы "Номер платіжних вимог" производится вручную. А вот внесение сумм, соответствующих определенному платежному требованию, можно тоже автоматизировать.Запишите в верхнюю ячейку графы "Сума" формулу:

=ЕСЛИ(C16=ВПР(C16;Регистр;1;ЛОЖЬ);ВПР(C16;Регистр;17;ЛОЖЬ);"").

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

- буквенная часть номера представляет собой аббревиатуру названия документа. Например, платежное поручение - ПП (или ПД), платежное требование - ПТ (или ПВ);

- цифровая часть может определять как порядковый номер данного вида документов (ПД/01, ПВ/01), так и дату составления документа. Например, ПД/10625 будет означать, что этот документ является платежным поручением, которое составлено 25 июня 2001 года.

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

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

Рисунок 11


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

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