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

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

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

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


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

Как известно, постановлением Правления Нацбанка от 29.03.01 г. №135 утверждена новая Инструкция о безналичных расчетах в Украине в национальной валюте. Этим же постановлением разрешено использовать в расчетах до 1 октября 2001 года бланки расчетных документов, которые были изготовлены в соответствии с требованиями Инструкции №7 "О безналичных расчетах в хозяйственном обороте Украины", при условии дополнения их реквизитами, предусмотренными новой Инструкцией.

Редактируем бланк платежного поручения

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

Рисунок 1

Бланк платежного поручения, приведенный в новой Инструкции, не претерпел заметных изменений по сравнению с прежним (который мы создали на прошлом уроке).

Чтобы воспроизвести требуемую новой Инструкцией форму платежного поручения (рис. 1), сделайтете следующие изменения:

- замените текст "Код за ЄДРПОУ" текстом "Код";

- выделите столбцы M:S и снимите объединение ячеек;

- из ячеек М11, М17, М19 удалите текст "рах. №";

- вместо текстов "ДЕБЕТ", "КРЕДИТ" введите "ДЕБЕТ рах. №", "КРЕДИТ рах. №";

- вместо текста "сума літерами" введите "сума словами";

- ячейки R23:S24 объедините и обведите рамкой. Этот реквизит заполняется, если плательщик или получатель - нерезиденты. Сюда вносится код операции - семизначный цифровой код (первые четыре знака - код операции по стандартной классификации платежного баланса, последние три - код страны получателя платежа по Классификатору стран ДК007-96). Если расчетный документ создается в электронном виде, этот реквизит является частью реквизита "Назначение платежа";

- выделите столбец В и уменьшите его ширину с 9 до 4 (см. строку 32 на рис. 1);

- выделите столбец С и увеличьте его ширину с 3,2 до 8,2. Если вы не отменили объединение ячеек С8:Е9 и С16:Е17, то, выделив столбец С, в окне "Формат ячеек" снимите флажок из окошка "объединение ячеек" (рис. 2);

- формулы, записанные в ячейки N11, Q11, N19, скопируйте соответственно в ячейки M11, R11, M19, а затем удалите их из ячеек N11, Q11, N19. Если в скопированных формулах вы использовали условие "ЕСЛИ(ЕПУСТО(G2);””", после их копирования восстановите аргумент G2 (при копировании в формулах он изменится соответственно на F2, Н2, F2);

- объедините ячейки M11:Q12, R11:S20, M17:Q18, M19:Q20.

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

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

Рисунок 2

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

"ЕСЛИ(AG6=”Нуль”;”гривень ”&ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)&” коп.”".

Рисунок 3

В результате формула должна принять следующий вид:

=ЕСЛИ(Q6-ЦЕЛОЕ(Q6)=0;” грн 00 коп.”;
ЕСЛИ(AG6=”Нуль”;” гривень”&ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)&” коп.”;
”грн ”&ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)&” коп.”))

С целью исключения необходимости склонения слова "гривня" в суммах, превышающих 0,99 грн, в формуле используется логическое условие, которое записывает сокращение "грн" - "” грн”&ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)&” коп.”". Такая возможность, кстати, предусмотрена в новой Инструкции.

Осталось скопировать отредактированную формулу в ячейки АР7:АР106 (106­я строка - условная граница объема Журнала регистрации, принятая на прошлых уроках).

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

Как трансформировать платежное требование в требование-поручение

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

Используя первый способ, вставьте в книгу "Документ" новый лист и присвойте ему имя, например, "Требование".

Как и при создании платежного поручения, прежде всего установите необходимые параметры листа "Требование". В окне "Параметры страницы" обнулите значения во всех окнах, а в окне "Стиль" установите шрифт Times New Roman 10 (рис. 4).

Рисунок 4

На листе "Требование" установите размеры столбцов в соответствии с данными, показанными на рисунке 5 в строке 42, а размеры строк - в соответствии с данными из столбца V.

Рисунок 5

Для ускорения процесса установки размеров ячеек можно выделять смежные строки или столбцы, имеющие одинаковые размеры, и одновременно ввести необходимое значение. Например, выделите строки с 18 до 24 и в окне "Высота строки" введите значение "10" (рис. 6).

Рисунок 6

Созданная вами форма требования-поручения занимает ровно половину стандартного печатного листа (210 х 297). Поэтому можно на одном листе печатать одновременно два поручения. Для этого выделите ячейки А1:Т39, скопируйте их и вставьте выделенный фрагмент в ячейку А42.

Так как форма требования-поручения имеет общие элементы с платежным поручением, для его создания можно использовать уже имеющуюся форму платежки. Вставьте на лист "Требование" скопированные на листе "Платеж" ячейки А1:Т31.

Это позволит вам автоматически перенести на лист "Требование" все необходимые формулы (их местоположение будет рассмотрено ниже). Ну, а для установки размеров ячеек воспользуйтесь рисунком 5.

Форма платежного поручения создана, и можно приступать к внесению в нее формул.

Двуликое требование-поручение

Как известно, требование-поручение - это расчетный документ, состоящий из двух частей:

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

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

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

- получателя платежа;

- плательщика.

Особенности заполнения требования-поручения получателем платежа

Итак, ваше предприятие выставляет требование-поручение своему контрагенту. В этом случае необходимо заполнить верхнюю часть документа. Однако, в отличие от платежного поручения, в раздел "Плательщик" вносятся реквизиты не вашего предприятия, а дебитора. И наоборот, в разделе "Получатель" должны находится данные вашего предприятия. Это отличие, которое необходимо учитывать при вводе формул в соответствующие ячейки, проиллюстрировано на рисунках 7 и 8.

Рисунок 7

Рисунок 8

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

Например, если реквизиты плательщика в ячейке D6 (лист "Платеж", рис. 7) записываются с помощью формулы: =ЕСЛИ(ЕПУСТО(G2);””;ВПР(1;Контрагент;3;ЛОЖЬ)), то на листе "Требование" (рис. 7) такая же формула используется для записи названия получателя.

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

D12: =ЕСЛИ(ЕПУСТО(G2);””;ВПР(1;Контрагент;3;ЛОЖЬ))
C14: =ЕСЛИ(ЕПУСТО(G2);””;ВПР(1;Контрагент;4;ЛОЖЬ));
I16: =ЕСЛИ(ЕПУСТО(G2);””;ВПР(1;Контрагент;7;ЛОЖЬ));
М16: =ЕСЛИ(ЕПУСТО(G2);””;ВПР(1;Контрагент;8;ЛОЖЬ)).

Как вы заметили, поле 3 (Банк получателя) осталось без формулы. Дело в том, что размеры требования-поручения не позволяют в этом поле использовать две формулы (для названия банка и города, в котором он расположен, как было сделано в платежном поручении). Можно заполнить этот реквизит иначе, введя в ячейку Е16 такую формулу:

=ЕСЛИ(ЕПУСТО(G2);””;
ВПР(1;Контрагент;5;ЛОЖЬ)&”в м. ”&
ВПР(1;Контрагент;6;ЛОЖЬ)).

Заполнение раздела "Плательщик" осуществляется с помощью таких же формул, как и при заполнении раздела "Получатель" в платежном поручении.

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

D5: =ЕСЛИ(ЕПУСТО(G2);””;
ВПР(G2;Регистр;6;ЛОЖЬ));
C7: =ЕСЛИ(ЕПУСТО(G2);””;
ВПР(G2;Регистр;7;ЛОЖЬ));
I9: =ЕСЛИ(ЕПУСТО(G2);””;
ВПР(G2;Регистр;10;ЛОЖЬ));
М9: =ЕСЛИ(ЕПУСТО(G2);””;
ВПР(G2;Регистр;11;ЛОЖЬ));
R9: =ЕСЛИ(ЕПУСТО(G2);””;
ВПР(G2;Регистр;17;ЛОЖЬ)).

Назначение платежа записывается в ячейку F18:

=ЕСЛИ(ЕПУСТО(G2);””;

ЕСЛИ(ЛЕВСИМВ(ВПР(G2;Регистр;3;ЛОЖЬ);1)=”Б”;
ВПР(G2;Регистр;12;ЛОЖЬ)&
ВПР(G2;Регистр;13;ЛОЖЬ)&
ВПР(G2;Регистр;14;ЛОЖЬ)&”;”&
ВПР(G2;Регистр;15;ЛОЖЬ)& ВПР(G2;Регистр;16;ЛОЖЬ);
ЕСЛИ(ВПР(G2;Регистр;3;ЛОЖЬ)=”К”;
ВПР(G2;Регистр;16;ЛОЖЬ)
&ВПР(G2;Регистр;20;ЛОЖЬ)))).

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

=ЕСЛИ(ЕПУСТО(G2);””;
ВПР(G2;Регистр;8;ЛОЖЬ)&”в м. ”&
ВПР(G2;Регистр;9;ЛОЖЬ)).

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

Рисунок 9

Чтобы у вас не возникало недоразумений, измените название раздела на "Одержувач (платник)".

Особенности заполнения требования-поручения плательщиком

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

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

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

На рисунке 10 показаны зоны требования-поручения (выделенные фоном), формулы в которых соответствуют формулам платежного поручения. Можно последовательно копировать их и вставлять в ячейку:

F26: =ЕСЛИ(ЕПУСТО(G2);””;
ВПР(G2;Регистр;18;ЛОЖЬ));
M26: =ЕСЛИ(ЕПУСТО(G2);””;
ВПР(1;Контрагент;8;ЛОЖЬ));
R26: =ЕСЛИ(ЕПУСТО(G2);””;
ВПР(G2;Регистр;17;ЛОЖЬ));
М32: =ЕСЛИ(ЕПУСТО(G2);””;
ВПР(G2;Регистр;11;ЛОЖЬ)).

Рисунок 10

На приведенном рисунке воспроизведено требование-поручение в том виде, в котором бухгалтер должен его подготовить для окончательного оформления (подпись, печать).

Специфика окончательного оформления требования-поручения заключается в том, что в данном случае нужно будет разделить этот процесс на два этапа:

- создание виртуального документа со всеми заполненными реквизитами верхней и нижней части (как показанный на рисунке 10, естественно, без подписей и печати), который будет храниться в электронном виде на вашем ПК. На этом этапе сначала вы заполняете журнал регистрации в том порядке, в котором создается платежное поручение. Затем в ячейку G2 вводите номер платежного документа;

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

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

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

Как использовать одну форму одновременно для двух целей

Суть варианта заключается в следующем:

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

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

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

Рисунок 11

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

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

Смотрите - в "платежке" наименование плательщика записано формулой

"ВПР(1;Контрагент;3;ЛОЖЬ)",

а получателя - "ВПР(G2;Регистр;6;ЛОЖЬ)".

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

=ЕСЛИ(И(ЕПУСТО(W5);ЕПУСТО(W7));””;
ЕСЛИ(ЕПУСТО(W5);ВПР(1;Контрагент;3;ЛОЖЬ);
ЕСЛИ(ЕПУСТО(W7);ВПР(G2;Регистр;6;ЛОЖЬ)))).

Первое логическое условие в формуле означает:

- если в ячейках W5 и W7 отсутствуют символы (одновременно в двух), то в ячейках D5 и D12 данных не будет.

Два других условия означают:

- если в одну из ячеек (W5 или W7) введен какой-либо символ, то выполнится одна из функций ВПР (например, введение в ячейку W7 символа "Х", т. е. выполняется функция "ЕПУСТО(W5)" и в ячейке D5 появится наименование плательщика).

В ячейку D12 введите формулу, подобную приведенной выше, которая отличается от нее только ссылками на ячейки-переключатели (они поменялись местами - выделены фоном):

=ЕСЛИ(И(ЕПУСТО(W5);ЕПУСТО(W7));””;
ЕСЛИ(ЕПУСТО(W7);ВПР(1;Контрагент;3;ЛОЖЬ);
ЕСЛИ(ЕПУСТО(W5);ВПР(G2;Регистр;6;ЛОЖЬ)))).

Дальше приведены формулы, которые нужно ввести в ячейки раздела "Плательщик" верхней части документа

В ячейку С7:

"=ЕСЛИ(И(ЕПУСТО(W5);ЕПУСТО(W7));””;
ЕСЛИ(ЕПУСТО(W5);ВПР(1;Контрагент;4;ЛОЖЬ);
ЕСЛИ(ЕПУСТО(W7);ВПР(G2;Регистр;7;ЛОЖЬ));

в ячейку Е9:

=ЕСЛИ(И(ЕПУСТО(W5);ЕПУСТО(W7));””;
ЕСЛИ(ЕПУСТО(W5);ВПР(1;Контрагент;5;ЛОЖЬ)&”в м.”&
ВПР(1;Контрагент;6;ЛОЖЬ);
ЕСЛИ(ЕПУСТО(W7);ВПР(G2;Регистр;8;ЛОЖЬ)&”в м.”&
ВПР(1;Контрагент;6;ЛОЖЬ);

в ячейку I9:

=ЕСЛИ(И(ЕПУСТО(W5);ЕПУСТО(W 7));””;
ЕСЛИ(ЕПУСТО(W5);ВПР(1;Контрагент;7;ЛОЖЬ);
ЕСЛИ(ЕПУСТО(W7);ВПР(G2;Регистр;10;ЛОЖЬ);

в ячейку М9:

=ЕСЛИ(И(ЕПУСТО(W5);ЕПУСТО(W 7));””;
ЕСЛИ(ЕПУСТО(W5);ВПР(1;Контрагент;8;ЛОЖЬ);
ЕСЛИ(ЕПУСТО(W7);ВПР(G2;Регистр;11;ЛОЖЬ).

Формулы соответствующих ячеек раздела "Получатель" верхней части требования-поручения отличаются лишь ссылками на ячейки-переключатели (W5 и W7). Их можно просто скопировать, вставить в ячейки C14, E16, I16, M16 и отредактировать, как в рассмотренном выше случае для ячейки D12.

При заполнении требования-поручения получателем платежа нижняя часть документа остается незаполненной и заполняется только плательщиком. Объединяя эти два условия в одной формуле с помощью функции "ЕСЛИ(ИЛИ(ЕПУСТО(G2);ЕПУСТО(W7))", для обоих вариантов запишите такие формулы (рис. 11):

в ячейку F26:

=ЕСЛИ(ИЛИ(ЕПУСТО(G2);ЕПУСТО(W7));””;
ВПР(G2;Регистр;18;ЛОЖЬ))

в ячейку М26:

=ЕСЛИ(ИЛИ(ЕПУСТО(G2);ЕПУСТО(W7));””;
ВПР(1;Контрагент;8;ЛОЖЬ))

в ячейку R26:

=ЕСЛИ(ИЛИ(ЕПУСТО(G2);ЕПУСТО(W7));””;
ВПР(G2;Регистр;17;ЛОЖЬ))

в ячейку М32:

=ЕСЛИ(ИЛИ(ЕПУСТО(G2);ЕПУСТО(W7));””;
ВПР(G2;Регистр;11;ЛОЖЬ)).

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

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


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

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