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

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

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

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


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

Ми далі розглядаємо автоматизацію складання фінансової звітності малого підприємства. Запускайте програму Excel і відкривайте створену вами минулого разу книгу "Баланс".

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

Внесення даних у робочий аркуш

Отже, на попередньому уроці ми створили робочий аркуш фінансового звіту, функцією якого буде збирання і групування даних, що належать до відповідних статей балансу. Робочий аркуш містить однакові за структурою блоки, назви і коди рядків яких відповідають рядкам балансу. У цих блоках розміщені робочі комірки. Наприклад, для блоку "Інші необоротні активи" (А43:L50) такими комірками є D45:L49, що містять дебетове сальдо рахунків 16, 17, 18 і кредитове сальдо рахунка 19, а також комірки L45:L49 з підсумковими даними (мал.1).

Малюнок 1

Комірки В50:L50 є допоміжними і можуть бути використані для систематизації даних за іншими ознаками. Оскільки, згідно із Законом про бухоблік, проміжна звітність складається щокварталу, у робочих комірках кожного блоку дані групуються також за кожен квартал, наростаючим підсумком. Наприклад, у комірки D45:K45, що належать до блоку "Інші необоротні активи", введене сальдо рахунків 16, 17, 18 і 19 на початок року, в комірки D46:K46 введене сальдо на кінець першого кварталу, у комірки D47:K47 - сальдо на кінець другого кварталу, у комірки D48:K48 - сальдо на кінець третього кварталу, а в комірки D49:K49 - сальдо на кінець четвертого кварталу, тобто дані за підсумками за рік.

У робочі комірки блоків, що належать до статей активу балансу, вводяться, як правило, дебетові сальдо, а в комірки, що належать до статей пасиву балансу, - кредитові сальдо.

Однак треба врахувати, що в деякі статті пасиву балансу включаються також і дебетові сальдо, а в деякі статті активу - навпаки, кредитові.

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

У ній використані переважно синтетичні рахунки, проте для складання робочого аркуша введіть субрахунки другого порядку.

Код рядка Найменування статті балансу Сальдо рахунків, що належать до статті балансу
Актив
020 Незавершене будівництво ДС 15
030 Залишкова вартість основних засобів ДС 10 + ДС 11 + ДС 12 - КС 13
031 Первісна вартість основних засобів ДС 10 + ДС 11 + ДС 12
032 Знос основних засобів КС 13
040 Довгострокові фінансові інвестиції ДС 14
070 Інші необоротні активи ДС 16 + ДС 17 + ДС 18 - КС 19
100 Виробничі запаси ДС 20 + ДС 21 + ДС 22 + ДС 23
130 Готова продукція ДС 26 + ДС 27 + ДС 28
160 Чиста реаліз. вартість дебіторської заборгованості ДС 34 + ДС 36 - КС 38
161 Первісна вартість дебіторської заборгованості ДС 34 + ДС 36
162 Резерв сумнівних боргів КС 38
170 Дебіторська заборгованість за розрахунками з бюджетом ДС 641 + ДС 642 + ДС 644
210 Інша поточна дебіторська заборгованість ДС 37 + ДС 63 + ДС 65 + ДС 66 + ДС68
220 Поточні фінансові інвестиції ДС 352
230 Кошти і їх еквіваленти в нац. валюті ДС 301 + ДС 311 + ДС 313 + ДС 331 + ДС 333 + ДС 3511
240 Кошти і їх еквіваленти в інвалюті ДС 302 + ДС 312 + ДС 314 + ДС 332 + ДС 334 + ДС 3512
250 Інші оборотні активи ДС 24 + ДС 25 + ДС 27
270 Витрати майбутніх періодів ДС 39
Пасив
300 Статутний капітал КС 40
320 Додатковий капітал КС 41 + КС 42
340 Резервний капітал КС 43
350 Нерозподілений прибуток (непокритий збиток) КС 441 - ДС 442 - ДС 443
360 Неоплачений капітал ДС 45 + ДС 46
430 Забезпечення майбутніх витрат і цільове фінансування КС 47 + КС 48
480 Довгострокові зобов'язання КС 50 + КС 51 + КС 52 + КС 53 + КС 54 + КС 55
500 Короткострокові кредити банків КС 60
510 Поточна заборгованість за довгостр. зобов'язаннями КС 61
530 Кредиторська заборгованість за товарами, роботами, послугами КС 36 + КС 62 + КС 63
550 Поточні зобов'язання за розрахунками з бюджетом КС 641 + КС 642 + КС 643
570 Поточні зобов'язання за розрахунками зі страхування КС 65
580 Поточні зобов'язання за розрахунками з оплати праці КС 66
610 Інші поточні зобов'язання КС 67 + КС 68
630 Доходи майбутніх періодів КС 69

де ДС - дебетове сальдо за рахунком,

КС - кредитове сальдо за рахунком.

На субрахунку 3511 - еквіваленти коштів у національній валюті.

На субрахунку 3512 - еквіваленти коштів в іноземній валюті.

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

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

Для отримання результуючих даних за кожною статтею в комірки стовпчика "Всього" необхідно ввести формулу суми "=СУММ(Dn:Kn)", де D, K - позначення стовпчика, n - номер рядка.

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

Однак, перш ніж вводити формули в комірки, для прискорення цього процесу сховайте 3-й рівень структури робочого аркуша, клацнувши ЛКМ на кнопці управління рівнем із цифрою 2 (мал.2). Робочі комірки будуть приховані, а символи структури будуть представлені кнопками зі знаком "+" (плюс).

Малюнок 2

Відкрийте перший блок комірок, клацнувши на відповідній кнопці зі знаком "+" (вона розташована навпроти рядка 12).

А тепер активізуйте комірку L7 і введіть у неї формулу "=СУММ(D7:К7)". Потім відомим вам способом скопіюйте її вміст у всі комірки робочого аркуша (клацнувши ЛКМ на маркері заповнення і протягнувши курсор від комірки L7 до комірки L263).

У такий спосіб ви скопіювали цю формулу у всі 254 комірки розділу "Всього" (у вас кількість комірок може бути іншою).

Тепер відкрийте всі рівні структури, клацнувши ЛКМ на кнопці структури з цифрою 3. Ви побачите, що у всіх комірках стовпчика "Всього" з'явилися числові значення. Далі у комірках стовпчика L у кожному блоці видаліть дані, які не стосуються робочих комірок. Наприклад, у блоці "Інші необоротні активи" (мал.1) в комірці L43 розміщене значення 0,00, а в комірці L44 - значення 540,00 (сума показаних на мал.1 номерів рахунків, що належать до цієї статті балансу). Природно, ця інформація нічого не відображає, і її треба видалити.

Як ви вже пересвідчилися, скопійована в інші комірки формула "=СУММ(D7:К7)" змінила аргументи.

Так, наприклад, у комірці L45 (мал.1) вона набрала вигляду "=СУММ(D45:К45)". Однак, враховуючи те, що серед рахунків, які входять у статтю балансу "Інші необоротні активи", є рахунок, кредитове сальдо якого віднімається від валюти цієї статті, треба відредагувати цю формулу.

Для цього активізуйте комірку L45 для редагування (клацніть двічі ЛКМ на комірці) і змініть аргументи записаної в неї формули. Вона повинна мати такий вигляд: "=СУММ(D45:J45)-К45".

Потім скопіюйте цю формулу в комірки L46:L49.

Аналогічне редагування треба зробити і для блоку "Нерозподілений прибуток". Замість введеної в комірку L170 формули "=СУММ(D170:K170)", потрібно ввести формулу "=D170-J170-K170" (мал.3). Після цього скопіюйте комірку L170 в комірки L171:L174. На цьому попереднє редагування формул на робочому аркуші завершено.

Малюнок 3

Як перенести дані у фінансовий звіт

Наступним кроком буде перенесення підсумкових даних робочого аркуша у фінансовий звіт.

Зробити це можна двома способами.

Перший спосіб - ввести в кожну комірку фінансового звіту окрему формулу, аргументом у якій було б посилання на відповідну комірку розділу "Всього" робочого аркуша. Для цього необхідно активізувати, наприклад, комірку G22 на аркуші "Отчет" (мал.4), двічі клацнувши на ній ЛКМ, введіть знак "=" і відкрийте аркуш "РЛОтчет", клацнувши ЛКМ на його ярличку. Потім ЛКМ клацніть на комірці L8 і натисніть клавішу Enter.

Малюнок 4

У результаті в комірці G22 аркуша "Отчет" з'явиться вміст комірки L8 аркуша "РЛОтчет" (725,00), а в рядку формул - введена формула "=РЛОтчет!L8" (мал.4).

Простий і на перший погляд цілком прийнятний варіант. Однак у нього є кілька недоліків.

По-перше, таку операцію треба буде проробити для кожної графи фінансового звіту не менше ніж 31 раз (за кількістю статей балансу).

По-друге, для кожного звітного періоду треба буде наново вводити формули, але вже з іншими аргументами.

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

Другий спосіб - ввести в відповідну комірку графи фінансового звіту таку формулу, яка б використала дані робочого аркуша залежно від її розташування в графі фінансового звіту і дати балансу. Таке завдання вирішує введення формули масиву.

Формула масиву

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

Ми вже використали поняття "масив" для позначення групи комірок. Однак в Excel поняття "масив" використовується також у більш прикладному значенні - у формулі масиву. Введена в кілька комірок формула масиву виконає кілька обчислень і поверне кілька значень, що відповідають коміркам, у які вона введена. Формули масиву звичайно обробляють два і більше набори аргументів, які називаються аргументами масиву. Задавання формули масиву не відрізняється від введення простої формули.

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

Використання формули масиву можна проілюструвати на простому прикладі.

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

Виділіть комірки L7:L11 (мал.5) і в рядок формул введіть формулу, показану в рядку формул на мал.5. Однак перш ніж натиснути клавішу Enter, натисніть клавіші Ctrl і Shift і, утримуючи їх, натисніть клавішу Enter. У всіх виділених комірках з'являться числові значення, що дорівнюють сумі робочих комірок відповідного рядка (наприклад, L7=D7+E7+F7+G7).

Малюнок 5

У комірках L7:L11 буде записано однакову формулу масиву, відмітною ознакою якої буде наявність фігурних дужок, що охоплюють її. Формулу масиву, як і звичайну формулу, можна відредагувати в будь-якій комірці масиву, однак її зміну не забудьте підтвердити натисненням комбінації клавіш Ctrl + Shift + Enter. В іншому випадку у комірках буде записана звичайна формула.

Прикладне до нашого завдання застосування формули масиву буде розглянуто нижче.

Присвоєння імені комірці і інтервалу комірок

Ви звернули увагу, що в наведеній формулі масиву групи комірок використовуються як аргументи формули. Присвоєні коміркам або групам комірок імена дозволяють легко знаходити їх на робочому аркуші для використання і, що важливіше, виключають помилку під час введення їх адрес. Якщо присвоїти коміркам D7:D11, наприклад, ім'я "Дс151" (дебетове сальдо субрахунка 151), коміркам Е7:Е11 - "Дс152", коміркам F7: F11 - "Дс153", а коміркам G7: G11 - "Дс154", формула масиву набере вигляду "{=Дс151+Дс152+Дс153+Дс154}".

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

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

Встановіть у ній курсор (у полі імен з'явиться адреса комірки - G5). Потім клацніть ЛКМ на полі імен, введіть у нього потрібне ім'я і натисніть клавішу Enter.

Оскільки при складанні звітності ми будемо активно використовувати у формулах адресу саме цієї комірки, виберемо для неї впізнаване ім'я, наприклад, "ДатаОтчета".

У комірку "ДатаОтчета" треба записати формулу "=D16" (мал.6), щоб при зміні дати, на яку складається баланс, змінювалося також і її значення.

Малюнок 6

Не забудьте - ці дві комірки повинні мати формат дати. Для комірки D16 виберіть формат, виділений у полі "Тип" діалогового вікна "Формат ячеек", а для комірки G5 - формат, вказаний у першому рядку поля "Тип" (мал. 7).

Малюнок 7

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

Увага! Присвоюючи ім'я, враховуйте, що воно не має містити пробілів. Ім'я має починатися з букви або символу підкреслення (наприклад, ім'я "_код"). Іншими символами, що входять в ім'я, можуть бути букви, числа, крапки і символи підкреслення (наприклад, "Баланс.Итого"). Ім'я може містити не більше ніж 255 символів.

Операція присвоєння імені інтервалу комірок не відрізняється від присвоєння імені одній комірці.

Присвоїмо імена інтервалам комірок, адреси яких входитимуть у формулу масиву, яку ми будемо використовувати для перенесення даних з робочого аркуша у фінансовий звіт. Ця формула масиву містить 6 імен інтервалів комірок, три з яких на аркуші "РЛОтчет" і три - на аркуші "Отчет".

На аркуші "РЛОтчет" присвойте імена таким трьом інтервалам комірок:

"КодРядРЛОтчета" - коміркам А21:А263,

"ДатаРЛОтчета" - коміркам В21:В263,

"ВсегоРЛОтчет" - коміркам L21:L263.

Увага! Для зручності виділення довгих інтервалів комірок можна приховати частину структури, клацнувши ЛКМ на кнопці управління структурою з цифрою 2 (мал.5).

Проконтролювати правильність введення імен і посилань, що використовуються в них, можна таким чином. Клацніть ЛКМ у полі імен на кнопці зі стрілкою (мал.8) і у вікні, що відкрилося, виберіть потрібне ім'я, клацнувши на ньому ЛКМ. Інтервал комірок або одна комірка, яким присвоєно ім'я, виділиться інверсним кольором.

Малюнок 8

Присвоїмо ім'я інтервалу комірок, що містить коди рядків фінансового звіту (друге з трьох імен на аркуші "Отчет"). На аркуші "Отчет" виділіть інтервал комірок Е21:Е69. Для цієї мети можна використати спосіб, відмінний від того, який ви використали раніше. Виділіть першу комірку інтервалу (комірка Е21), клацнувши на ній ЛКМ, потім установіть курсор мишки на бігунок смуги прокрутки і перемістіть його вниз до появи у вікні останнього рядка "Баланс". Натисніть на клавішу Shift і, утримуючи її натиснутою, клацніть ЛКМ на останній комірці інтервалу (комірка Е69).

У результаті потрібний інтервал стане виділеним (мал.9).

Малюнок 9

У полі імен введіть, наприклад, ім'я "КодРядОтчета".

Нехай вас не бентежить, що в інтервал комірок "КодРядОтчета" увійшли комірки, які безпосередньо не містять кодів (Е47:Е50). На роботу формули масиву це не вплине, але зате дотримується умова безперервності інтервалу комірок.

Іноді під час операції присвоєння імені треба змінити деякі аргументи, що входять у посилання на комірку. У таких випадках ім'я комірці треба присвоювати за допомогою меню. Цей спосіб ми розглянемо на прикладі присвоєння комірці Е21 на аркуші "Отчет" імені "КодРядОтчетаТек". Хочу звернути вашу увагу на особливе значення аргументів такого роду при обчисленні формул масиву. Умовно такі аргументи можна назвати позиціонуючими.

Як формула масиву вибирає потрібні дані

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

1 - прив'язка до дати балансу (тобто результат обчислення формули має змінюватися залежно від зміни дати балансу);

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

Прив'язку до дати балансу в нашому завданні будуть здійснювати у формулах масиву аргументи "ДатаОтчета" і "ДатаРЛОтчета", а позиціонування - аргумент формули "КодРядОтчетаТек". Про значення цього аргументу в обчисленнях за допомогою формул масиву буде розказано трохи нижче, а зараз присвоїмо це ім'я комірці інтервалу "КодРядОтчета".

Отже, виділіть на аркуші "Отчет" комірку Е21 (у будь-якому випадку це має бути перша комірка інтервалу "КодРядОтчета"). У меню "Вставка" виберіть рядки "Имя" і "Присвоить". У вікні "Присвоение имени" (мал.10), яке з'явилося, у поле "Имя" введіть ім'я "КодРядОтчетаТек". У принципі, в це поле можна ввести будь-яке інше ім'я, однак бажано відобразити в ньому вміст комірки (у нашому випадку ім'я комірки говорить про те, що вона є поточною (змінною) і розміщена в інтервалі "КодРядОтчета").

Малюнок 10

У полі "Формула" ви побачите посилання на комірку у тому вигляді, в якому вона використовуватиметься у формулі масиву. Скоригуйте її, видаливши другий знак долара "$" після букви Е, і натисніть кнопку "Добавить". Вікно не закриється, і ви зможете ще раз перевірити введену формулу.

Посилання повинне мати такий вигляд:

"=Отчет!$Е21". Після перевірки натисніть кнопку "ОК".

Це коригування посилання на комірку Е21 - дуже важлива операція, значення якої полягає ось у чому.

Формула масиву, що використовує ім'я "КодРядОтчетаТек" і введена в будь-яку комірку аркуша "Отчет", під час прочитування аргументу "КодРядОтчетаТек" буде посилатися на комірку стовпчика Е, причому саме в тому рядку, в якому розташована формула масиву.

Якщо формула імені "КодРядОтчетаТек" введена правильно, ви можете перевірити її дію. Встановіть курсор, наприклад, на комірку G27 і клацніть ЛКМ на стрілці поля імен. У вікні, що відкрилося, яке містить усі введені імена (мал.11), клацніть на імені "КодРядОтчетаТек". Внаслідок цих дій покажчик комірки переміститься в комірку Е27.

Малюнок 11

Якщо, наприклад, у комірку F28 або комірку G28 ввести формулу "=КодРядОтчетаТек", то в них з'явиться значення 70 (значення коду комірки Е28).

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

{=СУММ(ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета;1;0)*
ЕСЛИ(КодРядОтчетаТек=КодРядРЛОтчета;1;0)*ВсегоРЛОтчет}

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


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

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