ПРИМІТКА. На листі ОСВ можна вести облік максимум за чотири фінансові роки.

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

Перші чотири стовпці таблиці є точною копією блока “План_рахунків”, який переноситься методом спеціальної вставки.

Для кожного облікового періоду використовується група стовпців:

- сальдо початкове по дебету – Д;

- сальдо початкове по кредиту – К;

- дебетовий оборот - ДО;

- кредитовий оборот – КО.

Імена стовпців будуються як сполучення базового імені (вказано вище) і номеру облікового періоду, наприклад Д1, Д2, …, К1, К2, …, ДО1, ДО2, …, КО1, КО2, … .

Для початку обліку вводиться вступний баланс у вигляді сальдо по рахунках, субрахунках, проводиться контроль правильності вводу:

- суми дебетових сальдо дорівнюють сумі кредитових сальдо;

- сальдо рахунку, який вводиться відповідає типу рахунку:

o якщо тип рахунку А, вводиться тільки дебетове сальдо, кредитове сальдо повинно бути пустим;

o якщо тип рахунку К, вводиться тільки кредитове сальдо, дебетове сальдо повинно бути пустим;

o якщо тип рахунку Р, дебетове і кредитове сальдо повинно бути пустими;

o якщо тип рахунку А/П, може вводитись будь-яке сальдо.

o Таку перевірку забезпечує формула логічного типу, яка записується в окремому стовпці.

o Розрахунок сальдо на початок чергового облікового періоду також виконується за допомогою формул. Враховується тип рахунку, наприклад для облікового періоду за номером 2:

o якщо тип рахунку – А, Д2 = Д1+ДО1-КО1; К2 не задається;

o якщо тип рахунку – П, К2 = К1+КО1-ДО1; Д2 не задається;

o якщо тип рахунку – А/П, Д2 = Д1+ДО1; К2 = К1+КО1;

o якщо тип рахунку – Р, сума к закриттю рахунка (по дебету або по кредиту) визначається в залежності від знака виразу: Д1+ДО1-К1-КО1. Якщо більше 0, Д2: = Д1+ДО1-К1-КО1, К2: = (Д1 + ДО1 – К1 – КО1). Якщо результат 0, Д2 і К2 не задаються.

Обороти за кожним рахунком розраховуються за допомогою функції категорії “Робота з БД” – БДСУММ. У якості бази даних використовується список листа “ЖГО”.

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

Після утворення всіх формул лист захищається від внесення змін і показу формул. Всі розрахунки на листі виконуються автоматично. Будь-які зміни облікового регістру “Журнал господарських операцій (ЖГО)” відображаються на листі “ОСВ”. Дані листа не можна безпосередньо редагувати (всі зміни тільки через лист “ЖГО”). Лист “ОСВ” є зведеним регістром бухгалтерського обліку, який відображає як сальдо, так і обороти кожного рахунку. Дані листа можуть використовуватись для формування звітних форм, зокрема бухгалтерського балансу тощо.

Послідовність виконання

1. Вставити за необхідністю новий лист та перейменувати його в ОСВ.

2. Вставити блок План _рахунків на лист ОСВ:

- виділити блок План _рахунків за допомогою команди меню Правка – Перейти та скопіювати його в буфер обміну: Правка - Копировать;

- встановити курсор в ячейку А4 на листі ОСВ;

- виконати команду меню Правка - Специальная вставка - Вставить связь.

3. Сформувати загальний заголовок стовпців:

- виділити суміжні ячейки А1 – D3 і ввести загальну назву План рахунків, об’єднати ячейки.

4. Утворити іменований блок для ячейок стовпця Тип сальдо:

- виділити ячейки стовпця разом з назвою та виконати команду меню Вставка - Имя – Создать, по тексту, в рядку выше.

5. Виділити підсумковий рядок таблиці і за допомогою команди меню Формат ® Ячейки на вкладці Вид змінити колір заливки ячейок.

6. Заповнити шапку таблиці для першого облікового періоду, як на рис.2:

- ввести в Е2 дату початку першого облікового періоду фінансового року, наприклад 01.01.2005;

- виділити блок Е2:Н2 і об’єднати;

- ввести в Е3 – Сальдо;

- виділити блок Е3:F3 і об’єднати;

- ввести в G3 – Обороти;

- виділити блок G3:H3 і об’єднати;

- ввести відповідно в E4-H4: Д1, K1, ДО1, КО1.

-

01.01.07
Сальдо	Обороти
Д1	К1	ДО1	КО1

Рис. 2. Шапка першого облікового періоду

ПРИМІТКА. Імена (Д, К, ДО, КО) повинні бути зроблені тільки на російській мові, в протилежному випадку порушуються правила з приводу імен блоків ячейок.

7. Ввести в Е1 формулу для розрахунку кроку зміщення в області критеріїв, який використовується в формулі розрахунку оборотів по рахункам:

= (МЕСЯЦ(Е2) - 1) * 7+ 84 * (ГОД(Е2) – ГОД($Е$2)),

де 7 – крок зміщення по горизонталі в області критеріїв при переході до чергового облікового місяця;

84 – крок зміщення по горизонталі в області критеріїв при переході до чергового фінансового року. При копіюванні формули, якщо число облікових періодів перевищує один фінансовий рік, може відбутися зміна року, величина РІК ($Е$2) прив’язана до дати початку обліку.

8. Тиражування шапки таблиці для першого облікового періоду на інші періоди:

- виділити блок ячейок Е1:Н4; виконати команду контекстного меню Заповнити по місяцям.

9. Створити іменовані блоки для стовпців сальдо і оборотів по дебету і кредиту рахунків, субрахунків для кожного облікового періоду:

- виділити діапазон ячейок, починаючи з Е4:AZ і закінчуючи ячейкою в стовпці кредитового обороту для останнього рахунку останнього облікового періоду останнього фінансового року;

- виконати команду меню Вставка - Имя – Создать, по тексту, в рядку вище. Автоматично створюються імена блоків:

- Д1, Д2, Д3, ... – сальдо дебетове початкове для облікового періоду 1, 2, ...;

- К1, К2, К3, ... – сальдо кредитове початкове для облікового періоду 1, 2, ...;

- ДО1, ДО2, ДО3, ... – дебетовий оборот за обліковий період 1, 2, ...;

- КО1, КО2, КО3, ... – кредитовий оборот за обліковий період 1,2, ...

10. Скрити стовпці І – ВД: команда Формат - Столбцы - Скрытъ.

11. Введіть формули для контролю відповідності початкових сальдо типу рахунку:

- в ячейку ВЕ5 ввести формулу:

=ЕСЛИ(И(Тип_сальдо="А"; ЕПУСТО(К1)); ""; ЕСЛИ(И(Тип_сальдо="П"; ЕПУСТО(Д1)); ""; ЕСЛИ(И(Тип_сальдо="Р"; ЕПУСТО(Д1); ЕПУСТО(К1)); ""; ЕСЛИ(Тип_сальдо="А/П"; ""; "Помисла"))))

- скопіювати формулу ячейки ВЕ5 по всіх ячейках контрольного стовпця.

12. Ввід формул для перевірки вступного балансу для дебетових і кредитових сальдо:

- ввести формулу підсумку в ячейку підсумкової строки стовпця Д1 для дебетового сальдо: =СУММ(Д1);

- аналогічно виконати для підсумкової строки стовпця з ім’ям К1: = СУММ (К1).

13. Відобразити скриті стовпці таблиці за допомогою команди меню Формат®Столбец®Показать.

14. Ввести формули для обчислення дебетового та кредитового сальдо по кожному рахунку, субрахунку на початок чергового періоду. На прикладі другого облікового періоду: встановити курсор в ячейку І5, ввести формулу:

=ЕСЛИ(И(Тип_сальдо="А";Д1+ДО1-КО1>=0);Д1+ДО1-КО1;ЕСЛИ(Тип_сальдо="П";0;ЕСЛИ(Тип_сальдо="А/П";Д1+ДО1;ЕСЛИ(Тип_сальдо="Р";ЕСЛИ(Д1+ДО1-КО1-К1=0;0;ЕСЛИ(Д1+ДО1-К1-КО1>0;Д1+ДО1-К1-КО1;"Сума до закриття")); НД()))))

В ячейку J5, ввести формулу:

=ЕСЛИ(Тип_сальдо="А";0;ЕСЛИ(И(Тип_сальдо="П";К1+КО1-ДО1>=0);К1+КО1-ДО1;ЕСЛИ(Тип_сальдо="А/П";К1+КО1;ЕСЛИ(Тип_сальдо="Р";ЕСЛИ(К1+КО1-Д1-ДО1=0;0;ЕСЛИ(К1+КО1-ДО1>0;К1+КО1-Д1-ДО1;"Сума до закриття"));НД()))))

Розмножити формули ячейок І5 і J5 відповідно по стовпцям І і J.

ПРИМІТКА.Якщо рахунок має непередбачений тип сальдо або не виконуються умови перевірки: для активного рахунку сальдо дебетове кінцеве від’ємне; для пасивного рахунку сальдо кредитове кінцеве від’ємне, за допомогою вбудованої функції НД () в ячейці формується константа #Н/Д. Наявність #Н/Д призводить до помилки в підсумковому рядку.

15. Скопіювати формули блоків Д2 і К2 в інші облікові періоди:

- виділити ячейки блоків Д2 і К2; виконати команду меню Правка - Копировать;

- виконати аналогічно команду меню Правка - Вставить по всім обліковим періодам.

ПРИМІТКАВ результати виконання команди меню Правка - Вставитьвсі блоки Д3, Д4, ..., К3, К4, ... містять однакові формули.

16. Внести зміни в формули сальдо на початок кожного облікового періоду, починаючи з періоду 3:

- виділити блок ячейок Д3 за допомогою команди меню Правка - Перейти; виконати команду меню Правка - Заменить, вказати заміну 1 на 2 (для поточного облікового періоду), далі Заменить все;

- виділити блок ячейок К3; виконати команду меню Правка - Заменить, вказати заміну 1 на 2 (для поточного облікового періоду), натиснути кнопку Заменить все.

- Повторити попередній пункт для всіх облікових періодів, замінюючи 1 на 3 для періоду 4, 1 на 4 для періоду 5, 1 на 6 для періоду 7 і т.п.

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

- в ВF5 ввести значення 0; в ВF6 ввести значення 2;

- за допомогою автозаповнення заповнити діапазон ВF5:BF6 (до підсумкової строки).

18. Закрити файл.

ПРИМІТКА.Формули для розрахунку дебетових і кредитових оборотів по рахункам поки що не введені.