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

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

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

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


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

Кнопкою - клац, один момент...
бери готовий документ

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

Цей складний простий макрос

У непосвяченого читача згадка слова "макрос" викликає якщо не трепет, то відчуття невпевненості. Дуже часто слово "макрос" асоціюється з програмними вірусами. Таке повідомлення, як показане на малюнку 1, ви регулярно бачите, запускаючи в Excel вашу бухгалтерську програму (якщо у вас на вкладці "Общие" меню "Сервис" - "Параметры" активізована опція "Защита от макровирусов"). Поки ви не записали жодного макросу, можна не замислюючись натискати кнопку "Отключить".

Малюнок 1

Ми ж матимемо справу з корисними макросами. Для чого вони потрібні бухгалтерові, який створює свою програму? Передусім для заміни рутинних, часто повторюваних операцій з ведення бухобліку простими, "в один дотик", діями. Працюючи з ПК, ви звикли виконувати деякі операції натисненням на потрібну кнопку. Ми не замислюємося, що кожне таке натиснення запускає невелику програму під назвою "макрос".

Саме за допомогою таких програмок ви зможете значно прискорити свою щоденну роботу. Не хвилюйтеся, вам не доведеться заглиблюватися в хащі програмування, основну роботу за вас зробить Excel. Вам досить буде один раз правильно виконати потрібну послідовність дій - і Excel з абсолютною точністю її повторить. А тепер розпочинаємо.

Що ховається під кнопкою

Не маючи навіть приблизного уявлення про програмування, з використанням можливостей Excel можна записати досить складну процедуру. Все, що для цього потрібно, - відкрити вікно "Запись макроса" (меню "Сервис" - "Макрос"), натиснути кнопку ОК (мал. 2) і повторити ті дії у вашій Excelівській робочій книзі, які ви хочете автоматизувати.

Малюнок 2

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

Наше завдання сьогодні - забезпечити автоматичне перемикання режиму створення вимоги-доручення. Досі ви це робили, заносячи який-небудь символ у комірку W5 або W7. Ці "перемикачі" залишаться, однак керувати ними стане набагато простіше. Щоб ви відразу уявили, який вигляд матиме ваш робочий аркуш, погляньте на малюнок 3.

Малюнок 3

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

- заповнена тільки верхня частина документа для відправлення платникові (режим - "для вимоги");

- заповнена верхня і нижня частини документа для оплати на вимогу (режим - "для доручення"). Цей режим платник може використовувати для зберігання повністю оформленого документа;

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

Записаний макрос можна буде запускати різними способами:

- з меню "Макрос" (мал. 2);

- з Excelівського редактора Visual Basic (VBA);

- за допомогою кнопки, мальованого об'єкта або іншого елемента управління графічного об'єкта.

Для перемикачів режимів краще вибрати один з видів керуючих елементів - відому вам кнопку.

Як записати макрос

Повний вибір керуючих елементів, які використовуються у всіх програмах Microsoft, міститься на панелі "Формы" (меню "Вид" - "Панели инструментов", мал. 4).

Малюнок 4

Клацніть на елементі "кнопка" в панелі "Формы" і, перевівши курсор на робоче поле аркуша, розтягніть курсор до потрібного розміру. Рамка перетвориться на об'ємну кнопку з написом на ній "Кнопка1" (порядковий номер може бути й іншим), і водночас з'явиться вікно "Назначить макрос объекту", в якому натисніть "Отмена" (мал. 6).

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

Першу "Кнопку1" перейменуйте і назвіть "для вимоги", другу назвіть "для доручення", третю - "для друку доручення" (мал. 5).

Малюнок 5

Увага! Для входу в режим редагування імені треба двічі клацнути на кнопці. Для виходу - клацніть поза полем кнопки.

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

Як було сказано, під час запису макросу важливо дотримуватися чіткої послідовності дій, виключати зайві повторення. Тому не поспішайте клацати мишкою: кожна ваша операція буде записана в макросі. Отже, починаємо запис.

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

Крок 2. У вікні, що відкрилося (мал. 6), введіть ім'я макросу. За умовчанням вам буде запропоноване ім'я на кшталт "Кнопка31_Щелкнуть". Можна залишити це ім'я без зміни, але краще присвоїти ім'я, яке характеризуватиме дію, що виконуватиметься кнопкою (наприклад "Для_друку_доручення").

Малюнок 6

Увага! Ім'я макросу не може містити пропусків.

Натисніть на кнопку "Записать...".

Крок 3. У вікні "Запись макроса" ви можете призначити для запуску даного макросу поєднання "гарячих" клавіш. У цьому випадку треба ввести букву англомовної клавіатури. У поле "Описание" можна ввести додаткові відомості про операцію, що автоматизується. Після натиснення ОК на робочому аркуші з'явиться панель "Останов записи макроса", за допомогою якої можна припинити записування у будь-який момент. Тепер кожна операція на аркуші буде записана в макрос.

Крок 4. Клацніть ПКМ на кнопці "для вимоги" і в контекстному меню виберіть команду "Формат объекта" (мал. 7). У вікні "Формат элемента управления", яке відкрилося, встановіть такі формати для напису кнопки: розмір шрифту - 10, написання - "обычный", колір - "белый". Натисніть кнопку ОК. Ці параметри можуть бути іншими залежно від ваших колірних уподобань. Врахуйте тільки, що ці установки визначатимуть неактивну кнопку.

Малюнок 7

Увага! Не використовуйте для редагування формату тексту кнопки панелі інструментів, оскільки під час записування макросу запам'ятовується дія, а не її результат. Наприклад, кнопка "Ж" у головному меню може бути натиснута до запису, і якщо клацнути на ній, буде записане саме натиснення. При виконанні записаного макросу, якщо попередньо кнопка "Ж" відпущена, буде відтворена команда "клацнути" і виконається команда "форматувати напівжирним".

Крок 5. Клацніть ПКМ на кнопці "для доручення" і повторіть ті ж дії.

Крок 6. Клацніть ПКМ на кнопці "для друку доручення". Увійдіть в режим редагування тексту (аналогічно попереднім крокам) і встановіть такі параметри: розмір шрифту - 12, написання - "полужирный", колір - "Авто". Це будуть параметри активної кнопки.

Крок 7. Клацніть ЛКМ на комірці W5 і видаліть її вміст (натисніть клавішу Del).

Ця операція обов'язкова, незалежно від того, записаний в комірці символ (перемикач режиму) чи ні.

Крок 8. Клацніть ЛКМ на комірці W7 і натисніть клавішу Del (ця операція обов'язкова, незалежно від того, чи був заздалегідь записаний у комірці символ, чи ні). Введіть в комірку W7 будь-який друкарський символ (наприклад, х).

Крок 9. Виділіть комірки А1:Т39 (межі вимоги-доручення), відкрийте меню "Цвет шрифта" і встановіть у палітрі білий колір (мал. 8). Не знімаючи виділення, відкрийте меню "Границы" і виберіть варіант "без меж".

Малюнок 8

Крок 10. Виділіть комірки F26:K27 (зона для запису суми словами), M26:Q27, M32:Q33 (розрахункові рахунки платника і одержувача), R26:S27 (сума платежу), відкрийте меню "Цвет шрифта" і встановіть колір "Авто". При виділенні цих комірок тримаєте клавішу Ctrl натиснутою.

Крок 11. Клацніть на будь-якій комірці робочого аркуша (наприклад, G2) і зупиніть записування макросу (на панелі "Останов записи макроса" натисніть кнопку з чорним квадратом).

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

Отже, кнопка "для друку доручення" ожила. Тепер при натисненні на неї, незалежно від режиму, в якому перебував документ (заповнений "для вимоги" або "для доручення"), залишаться дані, які будуть вдруковані в отриману вимогу-доручення.

Послідовність кроків під час записування макросів "для вимоги" і "для доручення" буде подібна до описаної вище. Основна відмінність полягає в тому, що додатково будуть потрібні кроки для відновлення графіки форми документа. Як видно на малюнку 3, на полі документа в режимі "для друку доручення" відсутні рамки реквізитів.

Макрос "для доручення"

Щоб не повторюватися, наведу тільки відмінності при його записуванні від наведеної вище послідовності дій (описи всіх допоміжних однакових дій пропущено). Застосовані скорочення:

- "те ж" - з попереднього макросу;

- "повторіть дії" - з поточного макросу.

Крок 1. Клацніть ПКМ на кнопці "для доручення" і далі те саме.

Крок 2. Призначте ім'я макросу. Наприклад, "Для_доручення".

Крок 3. те саме.

Крок 4. те саме.

Крок 5. Клацніть ПКМ на кнопці "для друку доручення" і повторіть дії кроку 4.

Крок 6. Клацніть ПКМ на кнопці "для доручення" і далі те саме.

Крок 7. те саме.

Крок 8. те саме.

Крок 9. Виділіть комірки А1:Т39, відкрийте меню "Цвет шрифта" і встановіть у палітрі колір "Авто".

Крок 10. Виділіть комірки C7:E8, I9:K10, M9:Q10, R9:S17, C14:E15, M14:Q15, I16:K17, M16:Q17, M26:Q27, R26:S33, M30:Q31, M32:Q33 (якщо комірки цих діапазонів об'єднати, то їх виділення значно прискориться, див. мал. 9). Відкрийте меню "Границы" і виберіть варіант.

Малюнок 9

Крок 11. Виділіть комірки R2:S2, R19:S20, B23:B24 (місце печатки) і виберіть варіант "Границы".

Крок 12. Виділіть комірки G2:I2, B17:H17, B21:P21, F23, F24, F37, F39 і виберіть варіант "Границы".

Крок 13. Клацніть на комірці G2 і зупиніть запис макросу, клацнувши на.

Макрос "для вимоги"

Залишилося записати макрос для останньої кнопки "для вимоги".

У принципі, всі кроки під час його записування повторюють запис макросу "Для_доручення". Відмінність полягає тільки у форматуванні назви кнопок і заміні перемикачів в комірках W5 і W7. Проте для коректного перемикання режиму підготовки документа необхідно записати макрос, виконавши всі наведені нижче кроки.

Крок 1. Клацніть ПКМ на кнопці "для вимоги" і далі як у макросі "Для_доручення".

Крок 2. Призначте ім'я макросу. Наприклад - "Для_вимоги".

Крок 3. Як у макросі "Для_доручення".

Крок 4. Клацніть ПКМ на кнопці "для доручення" і далі як у макросі "Для_доручення".

Крок 5. Клацніть ПКМ на кнопці "для друку доручення" і далі повторіть дії кроку 4.

Крок 6. Клацніть ПКМ на кнопці "для вимоги". Увійдіть у режим редагування тексту (аналогічно попереднім крокам) і встановіть такі параметри: розмір шрифту - 12, написання - "полужирный", колір - "Авто". Це будуть параметри активної кнопки.

Крок 7. Клацніть ЛКМ на комірці W5 і натисніть клавішу Del (ця операція обов'язкова незалежно від того, чи був заздалегідь записаний у комірці символ, чи ні). Введіть у комірку W5 будь-який друкарський символ (наприклад, х).

Крок 8. Клацніть ЛКМ на комірці W7 і видаліть її вміст, натиснувши клавішу Del. Ця операція обов'язкова, незалежно від того, записаний в комірці символ (перемикач режиму) чи ні.

Крок 9. Як у макросі "Для_доручення".

Крок 10. Як у макросі "Для_доручення".

Крок 11. Як у макросі "Для_доручення".

Крок 12. Як у макросі "Для_доручення".

Крок 13. Клацніть на комірці G2 і зупиніть записування макросу.

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

Центр управління вимоги-доручення

Створені кнопки-перемикачі краще розмістити в зручному місці аркуша. Заздалегідь встановіть необхідний розмір для кнопки, для чого клацніть на кнопці ПКМ і розтягніть рамку кнопки. Не виходячи з режиму редагування, перемістіть кнопки в зону, показану на малюнку 10. Те саме повторіть і з іншими кнопками. Для встановлення однакового розміру для всіх перемикачів скористайтеся вкладкою "Размер" вікна "Формат элементов управления" (мал. 10).

Малюнок 10

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

а потім об'єднайте комірки V2:X3.

Порівняйте те, що у вас вийшло, з показаним на малюнку 3, і можете працювати.

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

=ЕСЛИ(ЕПУСТО(G2); “Платіжна вимога-доручення не підготовлена”;

“Платіжна вимога-доручення”&” №”&G2&” підготовлена”),


Виправлення помилки

У 12-му уроці ("ДК" №22, с. 37) було допущено помилки у формулах, введених у блок "Сума словами". Під час запису формули в комірку AG6 слід ввести додаткову умову: "ЕСЛИ(Z6=1;"";". Формула повинна мати вигляд:

=ЕСЛИ(И(Q6>0;Q6<1);"Нуль";ЕСЛИ(Z6=1;"";
ЕСЛИ(AA6>0;ВПР(AA6;Табло;2;ЛОЖЬ);""))).

У комірці AP6 має бути ще одна умова, виділена фоном:

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

ЕСЛИ(ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)<10;
"грн "&"0"&ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)&" коп.";"грн
"&ЦЕЛОЕ(Q6*100-ЦЕЛОЕ(Q6)*100)&" коп.")))

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

Увага! Зміни у файл 12-го уроку внесено.


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

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