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

Бухгалтерський облік за допомогою EXCEL

Урок 9-ий
Автоматизація складання фінансової звітності малого підприємства

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


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

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

Створення Головної книги

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

Щоб спростити процес створення Головної книги, спочатку створимо її структуру для одного місяця. Вона матиме вигляд, показаний на мал. 2.

Оскільки кожна комірка граф "Оборот за дебетом" (стовпчик F), "Оборот за кредитом" (стовпчик G), "Сальдо за дебетом" (стовпчик Н) і "Сальдо за кредитом" (стовпчик і) міститиме формулу, введемо їх у відповідні комірки першого блоку, а потім скопіюємо їх у складі блоку в інші комірки Головної книги. Як видно з мал. 1, комірки "шапки" таблиці, розташовані в стовпчиках Е, J, O, T тощо, містять інформацію про дату звітного місяця. Наразі залиште комірки Е3 і Е4 незаповненими, ми внесемо в них дані після створення структури таблиці.

Увага! Наявність порожнього стовпчика Е обов'язкова, оскільки в нього буде внесено дату відповідного періоду.

А тепер введіть у комірку Н6 формулу:

"=ЕСЛИ((C6-D6+F6-G6)>0;(C6-D6+F6-G6);0)".

Значення її зрозуміле без коментарю.

Для отримання кредитового сальдо субрахунка в комірку і6 введіть формулу:

=ЕСЛИ((C6-D6+F6-G6)<0;ABS(C6-D6+F6-G6);0).

Вона означає:

- якщо сума дебетового сальдо на початок звітного періоду і дебетового обороту субрахунка звітного періоду менша за суму кредитового сальдо на початок звітного періоду і кредитового обороту субрахунка звітного періоду, у комірку буде записано абсолютну величину (АВS) цього виразу;

- в іншому випадку в комірку і6 буде записано 0.

Увага! У наведених формулах другим значенням, яке повертається в комірку після виконання логічного виразу, має стояти обов'язково нуль "0". Якщо формулу записати у вигляді "=ЕСЛИ((C6-D6+F6-G6)>0;(C6-D6+F6-G6);””)", при її копіюванні у відповідну комірку іншого місяця завжди буде повертатися значення помилки "#ЗНАЧ!".

Як визначити оборот за субрахунком

Для визначення дебетових і кредитових оборотів за субрахунками за звітний період, в комірки розділу "Оборот за рахунком" необхідно ввести такі формули масиву:

- у комірку F6

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ(Е3);ЕСЛИ(ДебетЖ=СчетГКТек;ВсегоЖ)))}

- у комірку G6

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ(Е3);ЕСЛИ(КредитЖ=СчетГКТек;ВсегоЖ)))}

Як видно, ці формули мають однаковий синтаксис і лише незначно відрізняються одна від одної. Зверніть увагу - у них використовується аргумент "МЕСЯЦ(Е3)", в якому аргументом для функції "МЕСЯЦ" використовується посилання на комірку Е3. У таку комірку кожного блоку (кожного місяця) введіть базову дату, яка визначатиме конкретний звітний період.

Так, для першого блоку такою датою нехай буде, наприклад, 1 січня (не принципово, який день місяця буде встановлено). Виділіть комірки Е3 і Е4 і відформатуйте їх, як показано на мал. 3. Це дасть можливість при згорнутій структурі Головної книги легко перейти до потрібного місяця (мал. 5). Для більшої інформативності запишіть у комірку F3 назву відповідного місяця, для чого введіть у неї формулу "=Е3", а на вкладці "Число" встановіть такий же формат, як і для комірки Е3.

Після введення всіх формул у перший блок виділіть комірки Е3:I6, скопіюйте їх і введіть ці дані в комірку J3 (мал. 2). Ви отримаєте блок даних для другого звітного місяця. Цю операцію повторіть ще 10 разів (за кількістю місяців), послідовно вводячи скопійований блок у комірки O3, T3 тощо.

Треба зазначити, що під час копіювання блоку у відповідні комірки створених для кожного місяця блоків були скопійовані і введені в перший блок формули. Щоб формули масиву, за допомогою яких визначаються обороти за рахунком, працювали коректно, в комірках кожного блоку, в які скопіювалася базова дата першого блоку, відкоригуйте її. Для другого блоку в комірці J3 встановіть дату 01.02, для третього блоку в комірці O3 - 01.03 і т. д.

Увага! Оскільки ім'я "СчетГКТек", що використовується у формулах масиву, ще не має конкретного посилання, у всіх комірках, у які введено ці формули, ви побачите значення помилки "#ИМЯ!".

Для зручнішого користування даними Головної книги структуруйте її по горизонталі. За умовчанням при горизонтальному структуруванні Excel створює символи структури праворуч від виділеної групи комірок. Тобто якщо виділити стовпчики F:I (мал. 1) і створити структуру першого блоку, використовуючи команду "Группировать" в меню "Данные" - "Группа и структура", кнопка згортання буде розташована над стовпчиком J (який належить до другого блоку).

Тому в меню "Данные" - "Группа и структура" за допомогою команди "Настройка" відкрийте вікно "Структура документа" і заберіть перемикач рядка "в столбцах справа от детальных" (мал. 4).

Потім виділіть стовпчики К:N і створіть структуру другого блоку. Аналогічно структуруйте блоки інших місяців.

Отже, ви створили перший робочий рядок Головної книги на 12 місяців, який тепер достатньо скопіювати для інших рахунків, що використовуються на вашому підприємстві. Однак перед цим зробіть ще одне редагування. Як ви помітили, у формулах масиву кожного місяця аргументом функції "МЕСЯЦ" є відносне посилання на відповідну комірку. Оскільки під час копіювання формул масиву відносні посилання, що містяться в них, змінять первинну адресацію, кожен їх аргумент виду "МЕСЯЦ(E3)" відкоригуйте таким чином - "МЕСЯЦ($E$3)".

Наприклад, для лютневого блоку в комірці К6 формула повинна мати такий вигляд:

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ($J$3);ЕСЛИ(ДебетЖ=СчетГКТек;ВсегоЖ)))},

а в комірці L6:

{=СУММ(ЕСЛИ(МЕСЯЦ(ДатаОпер)=МЕСЯЦ($J $3);ЕСЛИ(КредитЖ=СчетГКТек;ВсегоЖ)))}.

Аналогічно відкоригуйте формули для інших 11 блоків.

Тепер визначте для себе, яка кількість субрахунків другого порядку використовується на вашому підприємстві. Приймемо умовно, що використовуватиметься 101­й субрахунок. А тепер згорніть структуру, клацнувши ЛКМ на кнопці з цифрою 1. Виділіть комірки А6:ВL6 і, встановивши курсор на маркері заповнення (чорний квадрат у правому нижньому кутку виділення), протягніть його до комірки ВL106 (мал. 5). Усі дані, введені в перший рядок, успішно скопіювалися у всі комірки Головної книги.

Найбільш трудомістка частина створення Головної книги - заповнення графи "Назва рахунка" і графи "№ рах.". Способи заповнення можуть варіюватися залежно від вашої програмно-технічної оснащеності та вільного часу. Для цього можна скористатися інформаційними ресурсами "ЛіГИ", вводячи скопійовані з її бази дані, або заповнити ці графи вручну.

При заповненні Головної книги дотримуйтеся таких вимог:

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

- у графу "№ рах." вводіть субрахунки другого порядку (під час введення даних у журнал господарських операцій використайте такі ж субрахунки, як і в Головній книзі).

Продовження цієї публікації - у наступному файлі > > >

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