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

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

Регістри бухгалтерського обліку і касові документи. Заповнення і зберігання з допомогою EXCEL.

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


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

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

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

Касова книга з багатьма обличчями
Стандартна й універсальна водночас

Оформлення касових операцій передбачає ведення на кожному підприємстві касової книги. Електронна касова книга (ЕКК), яка показана на малюнку 1, відрізняється від свого паперового аналога тим, що вона дає змогу відобразити не лише касові операції, здійснені за певний день, а й показати структуру касових операцій за більш тривалий період.

Малюнок 1

Відповідно до вимог Постанови Правління НБУ від 19.02.2001 р. №72 "Про затвердження Положення про ведення касових операцій у національній валюті в Україні", ведення касової книги в електронній формі має забезпечувати створення і роздрукування таких документів, як "Вкладний аркуш касової книги" і "Звіт касира". Ці документи за формою і змістом повинні відтворювати касову книгу в паперовій формі.

Побудова форми касової книги нескладна, тому розміри комірок не наводимо.

"Шапку" книги і таблиці можна заповнити відразу, ввівши необхідні дані.

Для спрощення структури касової книги пропонований варіант касової книги передбачає відображення на одному аркуші за один операційний день не більше 41­го документа (при зменшенні розміру рядка може бути більше). Для компактності касова книга показана на малюнку 1 зі згорнутими порожніми рядками. При роздрукуванні в розгорнутому режимі вона набере вигляду, показаного на малюнку 2.

Малюнок 2

Таблична частина ЕКК заповнюється за принципом автоматичного копіювання даних з Реєстру (аркуш "Реєстр каси").

Буде правильніше, якщо ви відкриєте ЕКК з початку року, щоб дотриматися вимог Положення №72. Однак можна користуватися створеною ЕКК з будь-якого періоду, дотримуючись прийнятої нумерації паперової касової книги, сторінки якої мають бути пронумеровані в порядку зростання з початку року.

Для забезпечення автозаповнення табличної частини ЕКК введіть у комірки її першого рядка такі формули:

В13 "=ЕСЛИ(ИЛИ(ЕПУСТО(A13);ЕПУСТО('Реєстр каси'!B5));
"";'Реєстр каси'!B5&'Реєстр каси'!C5)";

С13 "=ЕСЛИ(ИЛИ(ЕПУСТО(A13);ЕПУСТО('Реєстр каси'!K5));
"";'Реєстр каси'!K5)";

Е13 "=ЕСЛИ(ИЛИ(ЕПУСТО(A13);ЕПУСТО('Реєстр каси'!F5));
"";'Реєстр каси'!F5)";

F13 "=ЕСЛИ(ИЛИ(ЕПУСТО(A13);ЕПУСТО('Реєстр каси'!Q5);
ЛЕВСИМВ(B13)="В");"";ЕСЛИ(ЛЕВСИМВ(B13)="П";
'Реєстр каси'!Q5))";

Н13 "=ЕСЛИ(ИЛИ(ЕПУСТО(A13);ЕПУСТО('Реєстр каси'!Q5);
ЛЕВСИМВ(B13)="П");"";ЕСЛИ(ЛЕВСИМВ(B13)="В";
'Реєстр каси'!Q5))".

Використання в цих формулах посилань на комірки 5­го рядка Реєстру (наприклад "'Реєстр каси'!B5") залежить тільки від того, з якої дати ви починаєте заповнювати ЕКК (мал. 3).

Малюнок 3

Якщо ЕКК відкривається з серпня 2001 року, то в наведених формулах посилання будуть на комірки 11­го рядка (наприклад "'Реєстр каси'!B11").

Скопіюйте ці формули на інші комірки табличної частини ЕКК, крім останнього рядка №54 (на мал. 1 виділений фоном).

Касова книга довжиною в рік на одному аркуші. Заповнення ЕКК і фільтрація даних

Використання ЕКК для накопичення даних про всі здійснені касові операції і забезпечення можливості відображення станів каси за певний день можна реалізувати, фільтруючи дані за датою. Хоч цей реквізит і не присутній у табличній частині ЕКК, він буде використаний не тільки для фільтрації даних, а й для заповнення ЕКК.

Введіть у комірку А13 формулу "='Реєстр каси'!D5", яка поверне дату документа з першого рядка Реєстру. Оскільки дані комірок стовпчика А допоміжні і не передбачені формою касової книги, їх треба приховати, встановивши для них білий колір шрифту. Тепер для того, щоб заповнити ЕКК, досить буде скопіювати формулу з А13 в нижні комірки до 53­го рядка включно (мал. 4). В інших комірках табличної частини ЕКК будуть відображені дані, внесені до Реєстру.

Малюнок 4

Увага! Обов'язково введіть у комірки А54:А63 формулу "=$C$8" (посилання на комірку, в яку записується дата складання каси). Це дасть можливість при ввімкненні фільтра відображати нижню частину ЕКК - комірки D55:I63 (мал. 4).

Тепер виділіть А12:В12 і ввімкніть команду "Автофильтр". На малюнку 1 показана ЕКК з датою каси на 16.07.2001 р., хоча в табличній частині подані дані всіх скопійованих з Реєстру документів, оскільки на фільтрі встановлене правило відбору "(Непустые)". Щоб зміст ЕКК відповідав даті, введеній у комірку С8, виберіть із записів таблиці, відкритої стрілкою фільтра, рядок "16.07.2001". У результаті ЕКК міститиме дані тільки на зазначену дату.

Автовведення залишків касового дня

Порядок ведення касової книги передбачає запис залишку в касі коштів на початок дня. У паперовій касовій книзі ця сума переноситься з попереднього аркуша з рядка "Залишок на кінець дня". В електронному варіанті книги така інформація не зберігається, а генеруватиметься у разі потреби. Тому для автоматичного запису залишків коштів на початок і кінець дня, а також сумарних розмірів прибутків і витрат зробіть таким чином:

- у Реєстрі (аркуш "Реєстр каси") внесіть доповнення (мал. 5) - в комірку G1 запишіть суму залишку каси на дату відкриття Реєстру (дата першого документа Реєстру).

Малюнок 5

Для більшої інформативності в комірку О1 введіть посилання на першу дату Реєстру - "=D5";

- у комірку G2 запишіть суму залишку на зарплату на дату відкриття Реєстру;

- присвойте на аркуші "Реєстр каси":

ім'я "СимволДок" - коміркам В5:В105;

ім'я "ДатаДок" - коміркам D5:D105;

ім'я "СумаДок" - коміркам Q5:Q105 (розраховано на зберігання в Реєстрі 101­го документа - умовний розмір, можна за потреби збільшити);

ім'я "ВсьогоКасКн" - коміркам Е5:Е105,

ім'я "Рахунок" - коміркам F5:F105.

Щоб отримати залишок на початок будь-якого касового дня, введіть у комірку F12 формулу масиву (після закінчення її введення не забудьте натиснути Ctrl - Shift - Enter):

{=СУММ(ЕСЛИ(СимволДок="П";ЕСЛИ(C8>ДатаДок;
СуммаДок)))-СУММ(ЕСЛИ(СимволДок="В";
ЕСЛИ(C8>ДатаДок;СуммаДок)))+'Реєстр каси'!Q2}.

Якщо ви не введете дату каси, в ЕКК буде показана сума залишку на дату відкриття Реєстру (у прикладі, наведеному на мал. 5, залишок - 185,90). Якщо в комірці С8 зазначена дата, за яку не здійснювалися касові операції, то буде показано залишок на початок останнього касового дня.

Значення, яке відображає суму залишку на кінець касового дня, може бути різним, залежно від введеної в комірку С8 дати:

- якщо введена дата, протягом якої не здійснювалися касові операції, в комірці F55 ("Усього Прибуток") буде записаний залишок на попередній щодо введеної дати касовий день, а в комірку H55 ("Усього Видаток") - 0;

- якщо у зазначену комірку дата не введена, у підсумкових комірках будуть показані дані на день відкриття ЕКК;

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

Ці умови будуть забезпечені при введенні таких формул масиву:

комірка F55 - {=СУММ(ЕСЛИ(СимволДок="П";
ЕСЛИ(C8=ДатаДок;СуммаДок)))+F12};

комірка H55 - {=СУММ(ЕСЛИ(СимволДок="В";
ЕСЛИ(C8=ДатаДок;СуммаДок)))}.

Залишок на кінець дня, комірка F56, визначається формулою "=F55-H55".

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

{=СУММ(ЕСЛИ(СимволДок="П";ЕСЛИ(C8>=ДатаДок;ЕСЛИ(Рахунок=661;
СуммаДок))))-СУММ(ЕСЛИ(СимволДок="В";ЕСЛИ(C8>=ДатаДок;
ЕСЛИ(Рахунок=661;СуммаДок)))))+'Реєстр каси'!Q2}.

Якщо ви не введете в комірку C8 дату, то в касовій книзі буде записаний початковий залишок на дату відкриття Реєстру.

Маленькі хитрощі в ЕКК

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

Малюнок 6

Перемикання режиму касової книги

Як було сказано вище, використання ПК для ведення касової книги потребує підготовки документів "Вкладний аркуш касової книги" і "Звіт касира", які за формою і змістом не відрізняються від касової книги.

Щоб мати можливість роздруковувати і звіт касира, і вкладний аркуш касової книги, у комірку Е8 запишіть формулу

=ЕСЛИ(K8="З";"ЗВІТ КАСИРА";
"ВКЛАДНИЙ АРКУШ КАСОВОЇ КНИГИ").

Внесення в комірку К8 (ця адреса вибрана умовно) символу "З" або його видалення перемикатиме назву цього касового документа. Для виконання такої процедури ви можете записати найпростіші макроси і зв'язати їх з кнопками "Касова книга" і "Звіт касира" (мал. 6).

  Макрос "Вимкнути_аркуші" Макрос "Всього_аркушів"
Крок 1 Призначте перемикачу "поточний день" Призначте перемикачу "місяць (рік)"
  макрос "Вимкнути_аркуші". макрос "Всього_аркушів".
Крок 2 Встановіть курсор у комірку L4 і очистіть Встановіть курсор у комірку L4 і введіть
  її вміст (натисніть клавішу Del) у неї будь-який символ (наприклад "Х")
Крок 3 Зупиніть запис Зупиніть запис

Увага! Розміщуйте керуючі елементи макросів поза зоною дії фільтра (рядки з 13­го до 53­го включно).

Автонумерація аркушів

Згідно з Положенням №72, сторінки касової книги повинні автоматично нумеруватися в порядку зростання з початку року. Таку нумерацію можна забезпечити формулою масиву, введеною в комірку Е8:

{=СУММ(ЕСЛИ(C8>=ДатаДок;
ВсьогоКасКн))))+'Реєстр каси'!E2}.

Оскільки ви можете відкрити Реєстр не обов'язково з початку року, то в одну з його комірок (наприклад, Е2, мал. 5) введіть загальне число аркушів паперової касової книги на дату його відкриття.

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

Малюнок 7

Ця вимога виконується за допомогою формули масиву. Введіть її в комірку I8:

{=ЕСЛИ(ИЛИ(K8="З";ЕПУСТО(L4));"";ЕСЛИ(МЕСЯЦ(C8)=12;
СУММ(ВсьогоКасКн)+
'Реєстр каси'!E2;СУММ(ЕСЛИ(МЕСЯЦ(C8)=МЕСЯЦ(ДатаДок);ВсьогоКасКн))))}.

Інформація про загальну кількість аркушів з'являтиметься тільки в режимі "Касова книга", саме тому в наведеній формулі записана умова "K8="З"". Оскільки потреба роздрукувати вкладний аркуш касової книги із зазначенням загальної кількості аркушів виникає тільки наприкінці кожного місяця, активізація режиму "кінець місяця" відбувається введенням символу в комірку L4.

Щоб не заплутатися в цій простій процедурі, раджу скористатися оригінальним перемикачем між двома режимами "Вкладного аркуша касової книги": "поточний день" і "кінець місяця". Для цього за допомогою вікна "Формы" помістіть на аркуш два керуючі елементи "Перемикач", присвойте їм імена "поточний день" і "місяць (рік)" (мал. 7), а потім запишіть для них два найпростіші макроси.

  Макрос "Касова книга" Макрос "Звіт касира"
Крок 1 Встановіть формат "неактивна" для кнопки "Звіт касира" Встановіть формат "неактивна" для кнопки "Касова книга"
Крок 2 Встановіть формат "активна" для кнопки "Касова книга" Встановіть формат "активна" для кнопки"Звіт касира"
Крок 3 Очистіть комірку К8, зупиніть запис Введіть у К8 символ "З", зупиніть запис

Після запису макросів розташуйте перемикачі над керуючою коміркою.

Тепер, якщо клацнути ЛКМ, наприклад, на перемикачі "місяць (рік)", перемикач "поточний день" вимкнеться, а в комірці I8 з'явиться загальна кількість аркушів за місяць, зазначений у комірці С8 (якщо введено "грудень", буде підрахована загальна кількість аркушів за поточний рік).

При перемиканні в режим "Звіт касира" ця інформація з'являтися не буде.

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

Щоб при підготовці звіту касира або вкладного аркуша касової книги приховувати рядок "Усього аркушів за", введіть у комірку G8 формулу:

=ЕСЛИ(ИЛИ(K8="З";ЕПУСТО(L4));";
"Усього аркушів за").

Для цієї комірки встановіть вирівнювання по правому краю.

Поява в комірці Н8 назви поточного місяця або порядкового номера поточного року з текстом "рік" виконується формулою:

=ЕСЛИ(ИЛИ(K8="З";ЕПУСТО(L4));"";
ЕСЛИ(МЕСЯЦ(C8)=12;ГОД(C8)&" рік";C8)).

Для комірки Н8 встановіть формат дати типу "М" (мал. 8).

Малюнок 8

Скільки сьогодні заповнено прибуткових ордерів?

І ще одна підказка при заповненні касової книги - підрахунок кількості прибуткових і витратних касових документів, опрацьованих за поточний касовий день.

Невеликий блок, створений на аркуші ЕКК, підкаже вам необхідну інформацію про створені касові документи (мал. 9).

Малюнок 9

Присвойте на аркуші "Реєстр" коміркам S5:S105 ім'я "ВсьогоДок". У всі комірки цього масиву введіть формулу "=ЕСЛИ(ЕПУСТО(B10);"";1)". Вона повертатиме значення "1", якщо в Реєстр внесені дані за касовим документом.

Тепер у комірки блоку запишіть такі формули:

L59 "=ЕСЛИ(ЕПУСТО(C8);"";C8);"

K61 "{=СУММ(ЕСЛИ(СимволДок="П";
ЕСЛИ(ДатаДок=C8;ВсегоДок)))};"

K62 "{=СУММ(ЕСЛИ(СимволДок="В";
ЕСЛИ(ДатаДок=C8;ВсегоДок)))}".

День, коли закрита каса

Що станеться, якщо в комірку С8 ввести дату, відсутню в Реєстрі? У підсумкових комірках ЕКК з'являться інші величини, а дані табличної частини не зміняться. Щоб неправильне введення дати не викликало у вас додаткових запитань, можна організувати на аркуші автоматичну перевірку наявності введеної дати в Реєстрі (мал. 10).

Малюнок 10

Введіть у комірку L8 (як приклад) формулу "=ЕСЛИ(ЕПУСТО(C8);"";
ПОИСКПОЗ(C8;ДатаДок;0))".

У результаті, якщо введена дата відсутня, в комірку L8 повернеться значення помилки "ІН/Д".

Тепер встановіть для комірки С8 умовне форматування, використовуючи як умову формулу "=ЕОШИБКА($L$8)=ИСТИНА". Формат виберіть за бажанням (мал. 11).

Малюнок 11

 

Для появи підказки в комірки С7 і С9 можна записати такі формули:

=ЕСЛИ(ЕОШИБКА(L8)=ИСТИНА;
"Введи в С8 іншу дату ";"");

=ЕСЛИ(ЕОШИБКА(L8)=ИСТИНА;
"На цю дату даних немає";"").

Порядок роботи з ЕКК

Введені в ЕКК формули дозволять максимально автоматизувати її заповнення й отримання всієї регламентованої інформації. Запропоновану систему ведення касових операцій найраціональніше застосовувати на малих підприємствах, з невеликою кількістю касових операцій. При роботі з ЕКК слід пам'ятати деякі правила:

- у табличну частину ЕКК мають бути заздалегідь введені формули;

- введіть у комірку, розміщену зліва від першого рядка табличної частини ЕКК (стовпчик А), посилання на першу комірку графи "Дата складання" Реєстру;

- заповнення чергового рядка табличної частини ЕКК здійснюється тільки (!) копіюванням попередньої комірки стовпчика А (мал. 12).

Малюнок 12

Дані в табличній частині ЕКК можуть не з'явитися, якщо в ній відсутні формули або не заповнений Реєстр;

- щоб підготувати "Вкладний аркуш касової книги", введіть дату в комірку С8;

- для фільтрації даних табличної частині ЕКК у списку, відкритому стрілкою фільтра (комірка А12, мал. 12), виберіть рядок з датою, що збігається з датою комірки С8.

У наступному уроці ви дізнаєтеся, як створити й автоматично заповнювати перший регістр бухгалтерського обліку Журнал 1 і його відомості.


АНОНС

"Excel для бухгалтера - чудово, але, погодьтеся, більшість бухгалтерів працюють нині з 1С, це всеосяжніша програма, хоча й складніша. Чи є можливість розпочати уроки 1С, з описом підводних каменів і навчанням конфігурування та програмування? Хоча б для типових конфігурацій. Було б дуже цікаво!"

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

Але життя не стоїть на місці. Безумовно, Excel не вирішить усіх проблем сучасного бухгалтера. Є потужні, гнучкі, адаптовані програми для бухгалтера, серед яких провідне місце займає серія програмних продуктів фірми 1С. Враховуючи численні побажання наших читачів, з жовтня 2001 року ми починаємо публікувати цикл статей, присвячених секретам використання програми "1С:Підприємство".

Чекаємо від вас запитань стосовно проблем, з якими ви зіткнулися при користуванні програмою, пропозицій щодо тематики майбутніх статей. Поділіться своїми враженнями про "1С:Підприємство".

Гаряча лінія: тел/факс 294-6013,
e-mail: debet-kredit@gc.kiev.ua, lavrenov@gc.kiev.ua.

Керівник проекту "Автоматизація бухгалтерського обліку" В. Лаврєнов


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

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