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

Автоматизація бухобліку: Урок 13.
Бухгалтерський облік за допомогою EXCEL.

Заповнення, облік і зберігання платіжних документів з допомогою 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.

Малюнку 2

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

Остання зміна стосується запису суми літерами. У старій формі платіжного доручення, у разі якщо сума платежу менша за 1 гривню, у реквізиті "Сума літерами" необхідно було вносити текст на кшталт "Нуль грн ХХ коп.". У новій редакції Інструкції визначено: якщо сума містить лише копійки, то перед нею обов'язково слід написати слова "Нуль гривень" (без скорочення слова "гривень"). Тому необхідно відредагувати формули, записані в останній графі таблиці "Сума словами". Відкрийте аркуш "Регистр" (мал. 3) і у формулу комірки АР6 введіть додаткову умову:

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

У результаті формула має набрати такого вигляду:

=ЕСЛИ(Q6-ЦЕЛОЕ(Q6)=0;” грн 00 коп.”;

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

”грн ”&ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)&” коп.”))

Малюнку 3

Щоб виключити потребу відмінювати слово "гривня" в сумах, які перевищують 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