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

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

Заповнення, облік і зберігання платіжних документів з допомогою EXCEL

Володимир ЛАВРЄНОВ


Попередні уроки "Автоматизації бухобліку: Excel"

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

Серед розрахункових документів можна виділити ще один, який використовується на підприємстві, хоч і не так часто, як платіжне доручення. Це - платіжна вимога. Кожне підприємство в процесі своєї діяльності стикається з такими обставинами, коли його контрагенти не виконують зобов'язання, передбачені договором. Позитивним результатом розв'язання проблеми, що виникла, може бути або визнання контрагентом виставленої претензії, або арбітражне врегулювання претензії з подальшим примусовим списанням відповідної суми з рахунка боржника. Розрахунковим документом, на підставі якого банк списує суму боргу, є платіжна вимога (не плутайте з вимогою-дорученням).

З підготовки цього документа розпочинаємо сьогоднішній урок.

Форма однакова, зміст - різний

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

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

1-шу форму заповнюють у випадку, якщо "стягувач" і одержувач - одна особа;

2-гу форму заповнюють, якщо "стягувач" і одержувач - різні особи.

Ми розглянемо заповнення 1-ї форми платіжної вимоги, оскільки саме її використовують підприємства для розв'язання питання повернення боргу контрагентами. На малюнку 1 показано форму документа. Як бачимо, поля з реквізитами цього документа майже повторюють платіжне доручення. То чому б не використати цю форму для створення платіжного доручення? Отже, перед нами стоїть завдання - забезпечити просте і швидке перетворення платіжного доручення на платіжну вимогу за допомогою кнопкових перемикачів.

Малюнок 1.

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

Щоб трансформувати готову форму платіжного доручення в платіжну вимогу, потрібно:

- дещо змінити реквізити документа і його графіку;

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

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

Малюнок 2.

Для переходу з режиму "платіжне доручення" в режим "платіжна вимога" також будуть використані макрокоманди, керовані відповідними кнопками. Однак трансформацію однієї форми документа в іншу, на відміну від вимоги-доручення, виконаємо іншим способом - за допомогою формул. У цьому випадку використання такого способу більш раціональне, оскільки графічні зміни у формах документів незначні.

Як за допомогою формули змінити форму документа

Ключовою коміркою, яка керуватиме перемиканням режиму документа, визначимо W5 (див. мал. 3). Залежно від того, який документ створюється - платіжне доручення чи платіжна вимога, в цю комірку записуватиметься який-небудь символ або вона (комірка) залишатиметься порожньою.

Малюнок 3.

Перш ніж записувати макроси, в комірки, які визначають форму документа (див. мал. 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).

Тепер досить у комірку W5 ввести який-небудь символ, і у вікні з'явиться форма платіжного доручення. Якщо з комірки W5 забрати записане в неї значення, форма платіжного доручення трансформується у форму платіжної вимоги. Однак, як ви помітили, після переходу в режим платіжної вимоги рамка коду ДКУД залишилася на місці (мал. 4).

Малюнок 4

Річ у тому, що введеними в комірки R2 і R4 формулами не можна перенести формат комірки. Однак для розв'язання цього завдання Excel має у своєму арсеналі дуже корисну властивість - команда "умовне форматування". Надалі ви ознайомитеся з різними варіантами використання цієї властивості для автоматизації бухгалтерського обліку, а поки що розглянемо її у застосуванні до конкретного завдання.

Умовне форматування: перемикаємо оформлення комірок

Отже, необхідно міняти формат комірки при зміні певної умови - при перемиканні режиму з "платіжного доручення" на "платіжну вимогу" рамка реквізиту "код ДКУД" повинна "перескакувати" з комірки 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, ввести, наприклад, таку формулу:

Текст підказки може бути будь-яким і обмежений тільки вашою фантазією.

Малюнок 9.

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

Реєстр платіжних вимог

Платіжна вимога і супровідні документи подаються в обслуговуючий банк разом із реєстром платіжних вимог. Форма реєстру визначена Інструкцією №135. Тому буде не зайвим мати готовий бланк реєстру. Ви зможете швидко його підготувати, скориставшись розмірами, показаними на малюнку 10.

Малюнок 10.

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

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

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

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

Виберіть необхідний формат у полі "Тип" вікна "Формат ячейки". Якщо такий формат у вас не встановлено, у полі "Числовые форматы" виберіть рядок "все форматы", а потім у редагованому рядку поля "Тип" встановіть потрібний формат (мал. 11).

Малюнок 11.

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

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

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

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

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

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

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


Наступний урок

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