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

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

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

---

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

---

Відповідно до методичних рекомендацій щодо застосування регістрів бухгалтерського обліку, записи у відомості "Каса" Журналу 1 здійснюються в хронологічному порядку на підставі зведених облікових документів: прибуткових, видаткових касових ордерів, звітів касира.

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

Журнал 1 - щоб знати, скільки видано. Мімікрування Реєстру допомагає заповнювати регістр

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

Малюнок 1

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

Малюнок 2

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

Встановіть курсор у комірку D5 (мал. 2), і у вікні "Условное форматирование" для першої умови введіть формулу "=$B5=”В”", а для другої умови - "=$B5=”П”" (якщо посилання на комірки були введені не вручну, заберіть другий символ $, мал. 2). Використовуючи команду "Копіювати за зразком" , перенесіть встановлений формат на інші комірки графи. Тепер досить буде побіжного погляду, щоб безпомилково знайти в Реєстрі потрібну дату, внести її в Журнал 1 - і обороти ще одного касового дня опиняться в регістрі. Але перш ніж це станеться...

Запускаємо формулу на всі обороти з перемикачем курсу валют

Як і обіцяно, усі без винятку графи вашого Реєстру (природно, крім другої) заповнюватимуться автоматично. Але для цього доведеться трохи попрацювати, внісши в комірки першого рядка регістру кілька однотипних формул.

Графа №1 ("Номер запису"):

- у комірку А6 введіть "1", в А7 введіть формулу - "=ЕСЛИ(ЕПУСТО(B7);””;A6+1)". Цю формулу можна скопіювати в інші комірки графи.

Графа №3:

- у комірку С6 введіть формулу масиву (використайте поєднання клавіш Ctrl - Shift - Enter):

{=СУММ(ЕСЛИ(СимволДок=”В”;””;ЕСЛИ($B6=ДатаДок;
ЕСЛИ(ЛЕВСИМВ(Рахунок;2)=”31";СуммаДок))))/$Т6}.

Формули інших комірок, в яких записуються обороти за днями, - подібні, тому скопіюйте наведену формулу в усі комірки 6­го рядка до графи "Усього". Тепер відкрийте формулу в комірці D6 (графа "33 "Інші кошти") і відредагуйте її таким чином:

{=СУММ(ЕСЛИ(СимволДок=”В”;””;ЕСЛИ(B6=ДатаДок;
ЕСЛИ(ЛЕВСИМВ(Рахунок;2)=”33";СуммаДок))))/Т6}.

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

Малюнок 3

Наявність у формулі останнього аргументу - "Т6" (дільника) - потребує пояснення. Річ у тому, що для відображення операцій, проведених в іноземній валюті, використовується такий самий Журнал 1 і його відомості, але заповнені в інвалюті. Для переходу до відображення оборотів за рахунками від обчислення в іноземній валюті до гривневого обчислення і навпаки використовується перемикач, розташований у комірці Т6. Запишіть у цю комірку формулу "=ЕСЛИ(ЕПУСТО(S6);1;S6)".

Якщо в Журналі необхідно відобразити обороти за рахунками в доларах США, введіть у комірку S6 значення курсу $ (наприклад, для операцій, здійснених 11.07.2001 р., введіть курс $ - 5,3784). Таким чином, для відображення оборотів кожного касового дня в інвалюті у відповідну комірку (стовпчик S) введіть потрібний курс. Якщо комірки "курсового" стовпчика порожні, дані регістру будуть показані в гривнях.

Формули підсумкових комірок стовпчика "Усього" не відрізняються оригінальністю й украй прості. Введіть у R6 "СУММ(C6:Q6)". А тепер виділіть комірки С6:Т6 і скопіюйте їх до рядка "Усього".

Останній етап створення Журналу 1 - заповнення рядка "Усього". Використайте для цього також звичайну формулу суми. У комірку С32 введіть "СУММ(C6:C31)". Скопіюйте її на весь рядок 32 до комірки R32 включно - і Журнал 1 готовий до роботи.

Увага! Щоб у комірках, в яких немає оборотів за рахунками, не з'являлися нулі, зніміть прапорець "нулевые значения" на вкладці "Вид" (меню "Сервис" - "Параметры").

Бухгалтерські відомості (інформація про те, скільки грошей прийшло)

Ще один аркуш під іменем "Відомість 1.1" необхідно буде створити для відображення дебетових оборотів за рахунком 30 "Каса". Як видно на малюнку 4, таблична частина Відомості нічим не відрізняється від Журналу 1. Формули також подібні до своїх журнальних аналогів (порівняння наведене в таблиці, відмінність виділена фоном). Порівняйте:

* Якщо кількість стовпчиків табличної частини в Журналі і у Відомості не збігається, відповідно змініть у формулі посилання на перемикач валют - Т6.

Аналогічно, як і при створенні Журналу 1, скопіюйте цю формулу і відредагуйте її відповідно до номерів синтетичних рахунків, записаних у "шапці" таблиці (коригування стосуватиметься тільки чисел, взятих у лапки). Закінчення редагування кожної формули завершуйте натисненням клавіш Ctrl - Shift - Enter.

Сумарні формули табличної частини подібні до тієї, що використовується в Журналі 1.

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

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

Оскільки у Відомості перший запис відображає обороти за касою за перший день звітного місяця, то й аргументом у формулі є посилання на першу дату Відомості (на мал. 4 це комірка В6). Не забудьте ввести цю формулу як формулу масиву (Ctrl - Shift - Enter).

Малюнок 4

Заповнення рядка "Усього за кредитом" зводиться до введення в комірку С35 (мал. 4) формули "='Журнал 1'!R32" (див. мал. 5), а в сусідню комірку Е35 "Усього за дебетом" - формули "=R32".

Малюнок 5

Залишок на кінець місяця за рахунком 30 визначається як різниця між сумою сальдо на початок місяця і дебетовим оборотом за рахунком на поточний місяць і сумою кредитового обороту за цей місяць. Тому в комірку R35 запишіть формулу "=R2+E35-C35".

Довіряй, але перевіряй

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

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

Наприклад, вам потрібно звірити дебетові обороти за касою за липень 2001 року. Відкрийте ЕКК (аркуш "Касова книга" (мал. 6) і встановіть на першому фільтрі (стовпчик А) умову, яка обмежує вибірку даних з касової книги часовим інтервалом з 01.07.2001р. до 31.07.2001р. (мал. 7).

Малюнок 6

Малюнок 7

Таблична частина ЕКК буде представлена тільки даними за липень 2001 року. Виділіть комірки стовпчика "Прибуток", і в правому кутку рядка стану ви побачите сумарний дебетовий оборот за касою за липень "Сумма=1248,09".

Звірте його з даними комірок Е35 і R32 Відомості 1.1 (мал. 6). Таким самим чином, виділивши в ЕКК комірки графи "Видаток", можна перевірити сумарний оборот за кредитом рахунка 30, значення якого записане в комірку С35.

Як заповнювати регістри і "виловлювати" операції інших звітних періодів

Заповнення Журналу 1 і Відомості 1.1 зводиться фактично тільки до послідовного введення дат проведення касових операцій. Необхідною умовою автоматичного заповнення цих регістрів за запропонованим варіантом є попереднє заповнення Реєстру.

Отже, щоб отримати заповнений Журнал 1 і Відомість 1.1, ви повинні:

- внести дані до Реєстру (принаймні в чотири графи - №2, №4, №6, №17 (мал. 8);

- внести в комірки граф "Дата звіту касира" (Журнал 1 і Відомість 1.1) дату оформлення касових документів, зазначену в Реєстрі.

Малюнок 8

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

Вибірку "прибуткових" або "видаткових" касових днів можна зробити дещо інакше.

1. Встановіть у Реєстрі автофільтр для чотирьох перших стовпчиків, виділивши комірки А4:D4.

2. У списку другого фільтра (В4) виберіть, наприклад, рядок "П". Після цього Реєстр показуватиме тільки прибуткові операції за касою.

3. У графі "Дата складання" ви вже можете легко визначити, в які дати здійснювалися прибуткові операції.

При заповненні цих регістрів вам варто звернути увагу ще на одну обставину.

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

У регістрах (Журналі і Відомості) активізуйте комірку з датою першого запису (на малюнку 8 це комірка В6). Викличте вікно "Условное форматирование", і в полі введення формули встановіть умову "=МЕСЯЦ($B6)<>МЕСЯЦ($B$2)". У цій формулі використано посилання на комірку В2, в яку необхідно ввести місяць заповнення облікового регістра (мал. 9).

Малюнок 9

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

Облік операцій у регістрах за розрахунковим рахунком аналогічний обліку за касою

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

Що для цього треба? Достатньо в Реєстрі заповнити потрібні графи, пов'язані з цією операцією за розрахунковим рахунком. А щоб відрізнити введені дані від даних, які стосуються касових операцій (вони позначені символами "П" і "В"), можна вносити в графу Реєстру "Символ документа", наприклад, такі позначення:

- для прибуткових операцій за розрахунковим рахунком символ "Д";

- для видаткових операцій - символ "К".

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

Універсальний спосіб заповнення регістрів

Наведений варіант, так би мовити, стандартний, що вимагає підготовчої роботи для певної систематизації даних у регістрі (заповнення в Реєстрі потрібних граф). Але є й універсальний спосіб заповнення будь-якого регістру. Все, що для цього необхідно, - правильно заповнений Журнал господарських операцій з коректно введеними проведеннями за операціями.

Шлях інформації про господарську операцію до регістру стає гранично коротким (мал. 10). "Транспортним засобом" знову є формула масиву.

Малюнок 10

Для Журналу 1 "III. З кредиту рахунка 31 "Рахунки в банках" в дебет рахунків" у першу комірку графи "Каса" (С6) введіть таку формулу:

{=СУММ(ЕСЛИ(Баланс.xls!ДатаОпер=B6;
ЕСЛИ(ЛЕВСИМВ(Баланс.xls!ДебетЖ;2)=”30";
ЕСЛИ(ЛЕВСИМВ(Баланс.xls!КредитЖ;2)=”31";Баланс.xls!ВсегоЖ))))}.

Ця формула підраховує дебетові обороти за синтетичним рахунком 30, з кредиту рахунка 31 за дату, зазначену в комірці B6 (на малюнку 10 - за 11.07.2001 р.).

Дебетові обороти за рахунком 33 визначаються подібною формулою, яка записується в комірку D6:

{=СУММ(ЕСЛИ(Баланс.xls!ДатаОпер=B6;
ЕСЛИ(ЛЕВСИМВ(Баланс.xls!ДебетЖ;2)=”33";
ЕСЛИ(ЛЕВСИМВ(Баланс.xls!КредитЖ;2)=”31";Баланс.xls!ВсегоЖ))))}.

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

Для заповнення Відомості 1.3 використовуються аналогічні формули.

Для графи "Каса" (комірка С6, Відомість 1.3, мал. 10):

{=СУММ(ЕСЛИ(Баланс.xls!ДатаОпер=B6;
ЕСЛИ(ЛЕВСИМВ(Баланс.xls!ДебетЖ;2)=”31";
ЕСЛИ(ЛЕВСИМВ(Баланс.xls!КредитЖ;2)=”30";Баланс.xls!ВсегоЖ))))}.

Як бачите, у наведених формулах використовуються імена ("ДатаОпер", "ДебетЖ", "КредитЖ", "ВсегоЖ"), які присвоєні коміркам з іншої книги ("Баланс"). Тому у формулах зазначена повна адреса кожного імені (наприклад, "Баланс.xls!ДатаОпер"). Порядок присвоєння цих імен було розглянуто у 8­му уроці.

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

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

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