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

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

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

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


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

На останніх уроках ми досить докладно розглянули принципи створення і спосіб зберігання різних платіжних документів. Ще раніше ви ознайомилися з варіантом підготовки фінансової звітності підприємства, в якому для скріплення Журналу господарських операцій зі звітними формами використовувався штучно створений регістр "Головна книга".

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

Каса і касові операції. Облік коштів і грошових документів

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

Записи в Журналі 1 і відомостях здійснюються на підставі первинних і зведених облікових документів. До таких документів належать:

- касова книга і звіти касира;

- прибуткові і видаткові касові ордери, додані до звітів касира;

- виписки банку і додані до них документи (наприклад оголошення про внесення готівкових коштів).

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

Перед нами стоїть завдання автоматично заповнити:

- касові документи,

- Журнал реєстрації касових документів,

- касову книгу,

- регістр бухгалтерського обліку - Журнал 1.

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


Створення і зберігання видаткового касового ордера

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

Малюнок 1

Для зручності її створення ви можете скористатися розмірами, поданими по периметру форми. Зверніть увагу на те, що ця форма створена в стилі "Обычный Arial Cyr 10". Встановіть також на вкладці "Поля" (меню "Вид" - "Колонтитулы") нульові значення у всіх вікнах.

Як і в розглянутому рішенні щодо платіжних документів, касові ордери найпростіше створювати за допомогою спеціального реєстру, в який заздалегідь вносяться необхідні для заповнення касових документів реквізити. У принципі, можна скористатися вже створеним вами Журналом реєстрації платіжних документів, доповнивши його необхідними графами. Нехай вас не бентежить збільшення розміру Журналу - його можна структурувати, а також згортати не потрібні для конкретної операції графи. Однак щоб не ускладнювати пояснення, проілюструю цю операцію на спеціально створеному журналі - так званому Реєстрі касових документів (мал. 2). Відкрийте його в книзі "Каса" на аркуші "Реєстр каси" (мал. 2).

Малюнок 2

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

Для автоматичної ідентифікації номера касового документа в середовищі Excel краще використати текстовий номер (типу П267). Це дозволить вам не тільки візуально знаходити потрібний документ, але й автоматизувати обробку та Аналіз документів. Текстовий номер складається з символу виду документа, наприклад "П" - тобто прибутковий касовий ордер або "В" - видатковий касовий ордер, який записується в графу 2 Реєстру і цифрової частини номера, записаного в графу 3.

Для безпомилкового введення номера касового документа записуйте тільки символ виду документа ("П" або "В") в графу "Вид докум.", а повний номер документа генеруватиметься автоматично. Для цього заздалегідь:

- у комірку А5 введіть цифру "1",

- у комірку А6 введіть формулу "=ЕСЛИ(ЕПУСТО(B6);"";A5+1)"
і скопіюйте її в інші комірки графи;

- у комірку С5 введіть формулу "=ЕСЛИ(ЕПУСТО(B5);"";A5+100)"
і також скопіюйте її в інші комірки графи (якщо ви використовуватимете 4-значне кодування, останнім аргументом у формулі буде "А5+1000").

Захистіть комірки граф "№" і "№ докум." від змін. (Не забудьте, що за умовчанням всі комірки захищені, тому заздалегідь зніміть захист з усіх комірок аркуша, а потім виділіть тільки потрібні й увімкніть захист.)

Дані графи 9 "Символ" використовуються для заповнення Журналу касових документів (форма КО-3), порядок якого буде розглянуто нижче.

Для автоматичного введення даних фізичної особи, на яку оформляється касовий документ, використовуються комірки графи 10, куди вноситься порядковий номер фізичної особи. Порядковий номер вибирається зі списку фізичних осіб, перелік яких ви можете зберігати на окремому аркуші "Список" (мал. 3). Присвойте коміркам А4:Н23 ім'я "Список" (розмір умовний).

Малюнок 3

Для автозапису прізвищ й ініціалів у комірки графи 11 введіть у комірку такі формули:

К5 =ЕСЛИ(ЕПУСТО(J5);"";ВПР(J5;Список;2;ЛОЖЬ)&" "&ЛЕВСИМВ(ВПР(J5;Список;3;ЛОЖЬ)) &". "&ЛЕВСИМВ(ВПР(J5;Список;4;ЛОЖЬ))&"."),

для запису повного імені в комірки графи 12:

L5 =ЕСЛИ(ЕПУСТО(J5);"";ВПР(J5;Список;2;ЛОЖЬ)&" "&(ВПР(J5;Список;3;ЛОЖЬ))&" "&ВПР (J5;Список;4;ЛОЖЬ));

для запису паспортних даних (графа 14):

N5 =ЕСЛИ(ЕПУСТО(J5);"";ВПР(J5;Список;2;ЛОЖЬ)).

Ще одна особливість Реєстру - заповнення графи "Сума словами". Для її заповнення ви можете використати блок "Сума словами", структура якого була розглянута в уроці 12 ("ДК" №22). Скопіюйте з Журналу реєстрації платіжних документів комірки R6:АР6 (виділяйте комірки при натиснутій клавіші Ctrl). Потім, використовуючи команду "Специальная вставка" (щоб не переносити формати, встановіть опцію "формула"), вставте їх у комірку R5 Реєстру (мал. 2). Не знімаючи виділення з комірок, скопіюйте їх в інші комірки Реєстру. Зі стовпчиків S і T видаліть скопійовані з журналу-джерела числові значення і формули.

Увага! Скопійований масив комірок повинен бути обов'язково вставлений у комірку стовпчика R, а числове значення суми за касовим документом треба вносити у комірки стовпчика Q. В іншому випадку вам доведеться у всіх формулах блоку "Сума словами" міняти посилання.

Створений Реєстр буде одночасно і архівом касових документів, і джерелом даних для автоматичного їх заповнення. Для цього передусім присвойте ім'я масиву комірок A5:R104 (такий розмір дозволить створити і зберігати 100 касових первинних документів), наприклад ім'я "РеестрКас".

Увага! Встановіть захист для комірок Реєстру, в яких записані формули.

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

Визначте для себе, що комірка-джерело буде розташована, наприклад, за адресою А12 (щоб при друці не було видно введене в неї значення, встановіть для комірки-джерела білий шрифт). Формули автозаповнення будуть записані в таких комірках (див. мал. 1, "Видатковий касовий ордер"):

В12 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;2;ЛОЖЬ)&ВПР(A12;РеестрКас;3;ЛОЖЬ)";

С12 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;4;ЛОЖЬ)";

F12 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;6;ЛОЖЬ))";

H12 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;7;ЛОЖЬ))";

I12 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;17;ЛОЖЬ))";

C14 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;12;ЛОЖЬ))";

C16 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;13;ЛОЖЬ))";

B17 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;18;ЛОЖЬ))";

C25 "=ЕСЛИ(ЕПУСТО(A12);"";ВПР(A12;РеестрКас;14;ЛОЖЬ))".

Реквізити-константи (В1, D4, J5) введіть у форму бланка, інші реквізити повинні заповнюватися посадовими особами й одержувачем вручну.


Журнал реєстрації прибуткових і видаткових касових документів. Касова книга

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

Форма Журналу, створена на аркуші "ЖурналКасДок", - стандартна (мал. 4). Всі дані в нього вносяться автоматично за допомогою нескладних формул.

Малюнок 4

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

Запишіть у комірки:

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

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

D17 "=ЕСЛИ(ЕПУСТО('Реєстр каси'!J5);"";ЕСЛИ($D$14='Реєстр каси'!J5;'Реєстр каси'!Q5;""))";

Е17 "=ЕСЛИ(ЕПУСТО('Реєстр каси'!J5);"";ЕСЛИ($Е$14='Реєстр каси'!J5;'Реєстр каси'!Q5;""))".

Залежно від кількості видів цільового використання готівкових коштів, у комірки F17 і G17 введіть формули, подібні до двох останніх.

Вищенаведені формули скопіюйте до 30­го рядка включно (мал. 4).

Увага! У зведеному Журналі останній рядок табличної частини перед рядком "Разом" повинен бути порожнім.

Це дасть можливість вставляти необхідну кількість порожніх рядків у міру заповнення Журналу. У додані комірки копіюються формули з комірок В17:G17.

Для підрахунку суми у зведеному Журналі й окремо в кожному режимі використовуються формули масиву. Присвойте коміркам С17:С31 ім'я "НомерДок". Залежно від прийнятого обліку руху готівкових коштів за цільовим призначенням присвойте імена масивам комірок, розташованим у графах 3, 4, 5, 6. Оскільки в наведеному прикладі в Журналі використовуються тільки графи 3 і 4, комірки D17:D31 назвіть "Сума3", а комірки Е17:Е31 - "Сума4".

D32 "{=СУММ(ЕСЛИ(ЛЕВСИМВ(НомерДок)=C32;Сума3;ЕСЛИ(C32=0;Сума3)))}";

E32 "{=СУММ(ЕСЛИ(ЛЕВСИМВ(НомерДок)=C32;Сума4;ЕСЛИ(C32=0;Сума4)))}".

Якщо облік цільового використання ведеться і в інших розрізах, для комірок F17:F31 і G17:G31 введіть додатково імена "Сума5", "Сума6", а у комірки F32 і G32 - формули масиву, що їм відповідають.

У комірку C32 введіть формулу "=С16". Якщо тепер у комірку С16 записати символ "В" або символ "П", то в підсумкових комірках з'явиться відповідно сума за видатковими ордерами або за прибутковими. Якщо комірка С16 залишиться порожньою, то в Журналі буде відображена сума за всіма ордерами.

Чому зазначені символи не вводяться безпосередньо у комірку C32, буде показано нижче.

Отже, ви вже маєте зведений Журнал усіх касових ордерів, але коли потрібно роздрукувати окремо Журнал видаткових або прибуткових ордерів, виникне питання...


Як розділити зведений Журнал на два спеціальні. Використання фільтра

Оскільки ведення Журналу реєстрації касових ордерів із застосуванням форми №КО-3 передбачає Відкриття окремо Журналу на видаткові і прибуткові ордери, можна використати можливості Excel для перемикання зведеного Журналу в один із цих режимів. Для цього ви можете використати фільтр, за допомогою якого ховаються дані, які не відповідають встановленим критеріям. Основна ознака, за якою ми фільтруватимемо Журнал, - символ виду документа (тобто в номерах документів, розміщених у графі 2 Журналу, буде перевірятися перший символ - "В" або "П").

Активізуйте фільтр: виділіть перший рядок над табличною частиною Журналу (В16:Н16) і введіть команду "Автофильтр" (меню "Данные" - "Фильтр", мал. 5). У кожній виділеній комірці праворуч з'явиться стрілка, яка Відкриває список значень комірок відповідного стовпчика. У цьому списку можна задати необхідний критерій, після чого комірки цього списку, що не задовольняють вибраному критерію (і всі рядки, в яких містяться такі комірки), будуть приховані.

Малюнок 5

Наприклад, виберіть у списку першої графи рядок "16.07.01", і в Журналі будуть показані тільки ордери, оформлені 16 липня 2001 року. Номери рядків і стрілка автофільтра в стовпчику, за значенням якого Відбиралися рядки, виділяються блакитним кольором. Для того щоб зі всього списку документів Журналу показати тільки прибуткові ордери, виберіть рядок "(Условие...)" (мал. 6). З'явиться вікно "Пользовательский автофильтр", в лівому полі якого виберіть відповідний оператор, наприклад "начинается с", а в праве поле введіть символ "П". У результаті в Журналі буде видно тільки дані за прибутковими касовими ордерами (такий самий результат вийде, якщо в лівому полі вибрати оператор "равно", а в праве поле ввести "П*").

Малюнок 6

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

- зведений Журнал - режим "Всі документи";

- режим "Прибуткові ордери";

- режим "Видаткові ордери".

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

Створіть три кнопки керування, присвойте їм імена "Прибуткові ордери", "Видаткові ордери" та "Всі документи" і розташуйте їх не нижче 16­го рядка (інакше при виконанні одного з макросів вони можуть опинитися в зоні прихованих рядків, і ви їх не побачите).

Запис макросів розглянемо на прикладі першого макросу "Прибуткові_ордери".

Крок 1. Після початку запису клацніть ПКМ на кнопці "Видаткові ордери" і в меню виберіть команду "Формат объекта". Встановіть для кнопки формат напису "неактивна" (варіант: зображення - "обычный", колір - білий) і натисніть ОК.

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

Крок 2. Клацніть ПКМ на кнопці "Всі документи" і Відформатуйте напис кнопки для варіанта "неактивна" (як у кроці 1).

Крок 3. Клацніть ПКМ на кнопці "Прибуткові ордери" і Відформатуйте її назву як "активна" (наприклад, зображення - "полужирный", колір - "Авто").

Крок 4. Клацніть на комірці С16 і введіть у неї символ "П".

Крок 5. Клацніть на стрілці автофільтра, розташованій у графі 2 "Номер", і виберіть правило Відбору записів таблиці "(Условие...)". У вікні "Пользовательский автофильтр" у лівому полі виберіть оператор "начинается с", а в праве введіть символ "П" (регістр не має значення). Натисніть ОК.

Крок 6. Зупиніть запис макросу.

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

Макрос "Видаткові_ордери" > Макрос "Всі_документи"

Крок 1 Встановіть формат "неактивна" > Встановіть формат "неактивна"

для кнопки "Прибуткові ордери" для кнопки "Прибуткові ордери"

Крок 2 Встановіть формат "неактивна" Встановіть формат "неактивна"

для кнопки "Всі документи" для кнопки "Видаткові ордери"

Крок 3 Встановіть формат "активна" Встановіть формат "активна"

для кнопки "Видаткові ордери" для кнопки "Всі документи"

Крок 4 У комірку С16 введіть символ "В" З комірки С16 видаліть символ

Крок 5 Виберіть правило Відбору записів Виберіть правило Відбору записів

таблиці "(Условие...)". У вікні таблиці "Все"

"Пользовательский автофильтр" в лівому

полі виберіть оператор "начинается с",

а в праве введіть символ "В"

Крок 6 Зупинка запису Зупинка запису

Тепер поясню, чому у комірку C32 була введена формула "=С16", а при записі макросів значення записувалися саме у комірку С16. Річ у тому, що якщо в макроси записати значення символів ("В", "П", "пусто") в С32, то при збільшенні розміру табличної частини макрос записуватиме і прочитуватиме символ тільки з цієї комірки. Рядок "Разом" при перемиканні режимів стане невидимий.

І, нарешті, ще один крок в автоматизації заповнення Журналу. При перемиканні режимів можна відповідно міняти і назви граф Журналу, якщо ввести у комірки такі формули:

В12 "=ЕСЛИ(C16="П";"Прибутковий документ";ЕСЛИ(C16="В";"Видатковий документ";"Всі прибуткові та видаткові документи"))",

D12 "=ЕСЛИ(C16="П";"Надійшло грошей";ЕСЛИ(C16="В";"Витрачено грошей";"Надійшло (витрачено) грошей"))".

Увага! Введення в Журнал нових даних за касовими ордерами здійснюйте тільки копіюванням записаних формул у Додаткові рядки.

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

- виділіть рядок 31 і додайте потрібне число рядків (наприклад 10);

- виділіть комірки B30:G30 і скопіюйте їх до рядка 40 включно.

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


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

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