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

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

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

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


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

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

Як ввести формулу масиву

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

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

Отже, для того щоб ввести у форму фінансового звіту, в комірку G22, формулу, показану на мал. 1, активізуйте на аркуші "Отчет" комірку G22. У рядку формул введіть перші функції "=СУММ(ЕСЛИ(".

Малюнок 1.

Після того як ви введете другу відкриваючу круглу дужку (мал. 2), у меню "Вставка" виберіть рядок "Имя", а потім рядок "Вставить". У вікні "Вставка имени", що відкрилося, виберіть ім'я "ДатаОтчета" і натисніть "ОК" (мал. 2). У рядку формул з'явиться аргумент "ДатаОтчета". Аналогічно вводяться інші імена-аргументи.

Малюнок 2

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

Якщо у вашому робочому аркуші у блоці "Незавершене будівництво" будуть введені дані, то в комірці G22 з'явиться загальна сума, записана в комірці L8 (за умови, що дата балансу, введена в комірку G5 на аркуші "Отчет", відповідатиме періоду комірки В8 на робочому аркуші, див. мал. 1). Якщо в робочий аркуш дані ще не введені, в комірці G22 буде записано 0.

Помилки у формулах

Часом буває, що після введення формули замість обчисленого значення в комірці з'являється повідомлення про помилку, яке починається зі знака діез "І". Excel має кілька категорій таких повідомлень, які змінюються залежно від допущеної помилки. Найпоширеніше повідомлення - "ІИМЯ?".

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

Може з'явиться повідомлення "ІН/Д". У цьому випадку Excel попереджає, що не може виконати обчислення у формулі. У вас таке повідомлення з'явиться, якщо, наприклад, інтервали комірок на аркуші "РЛОтчет", яким присвоєні імена, мають неоднаковий розмір.

Переконайтеся, що:

ім'я "ДатаРЛОтчета" має посилання

"=РЛОтчет!$B$7:$B$263",

ім'я "КодРядРЛОтчета" - "=РЛОтчет!$A$7:$A$263",

ім'я "ВсегоРЛОтчет" - "=РЛОтчет!$L$7:$L$263".

Тобто всі інтервали комірок, які обробляються у формулі масиву на одному аркуші (у цьому випадку - на аркуші "РЛОтчет"), повинні починатися на одному рядку (рядок 7) і закінчуватися також на одному (рядок 263). У вас інтервали можуть бути іншими. Однак, якщо цифрові значення в наведених посиланнях будуть різнитися між собою, з'явиться повідомлення про помилку виду - "ІН/Д".

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

Якщо після введення в комірку формули масиву (наприклад, комірка G22 аркуша "Отчет") в ній з'явиться повідомлення про помилку, скористайтеся функцією відстеження залежностей.

У меню "Сервис" виберіть рядок "Зависимости", а потім рядок "Источник ошибки" (мал. 3). Після цього на аркуші "Отчет" з'являться стрілки, спрямовані до активної комірки з кількох комірок робочого аркуша, що є аргументами формули. Аргументи, розташовані на інших аркушах, схематично позначаються значком таблиці (мал. 3). Клацніть двічі ЛКМ, наприклад, на лінії, що виходить з такого значка таблиці. На екрані з'явиться вікно "Переход", у полі якого будуть подані всі інтервали комірок, що використовуються у формулі масиву.

Малюнок 3

Це значно полегшує пошук помилки. У цьому випадку можна відразу помітити, що не всі інтервали комірок мають однаковий розмір (посилання "[Баланс.xls]РЛОтчет!$B$7:$B$262" має іншу адресу останньої комірки: $B$262, а не $B$263). Клацніть двічі ЛКМ на рядку з цим посиланням. Вибраний інтервал комірок виділиться інверсним кольором, а в полі імен ви зможете визначити його ім'я (у цьому випадку "ДатаРЛОтчета"). Тепер ви знаєте джерело помилки, яку можна легко виправити, відкоригувавши посилання (спосіб зміни посилання ми розглядали на попередньому уроці, приклад з посиланням "КодРядкаОтчетаТек=Отчет!$Е21").

Після усунення помилки можна видалити стрілки, вибравши рядок "Убрать все стрелки" (мал. 3).

Ви можете зіткнутися також з помилкою іншого роду.

Після введення в комірку G22 наведеної формули ви можете побачити в комірці нульове значення (0 або 0,00) замість значення 725,00 (мал. 1). Перевірте, чи всі аргументи у формулі задані правильно, чи міститься в комірці L8 робочого аркуша значення 725,00. Якщо у такий спосіб помилки не знайшли, зробіть ось що.

Відкрийте список у полі імен і виберіть у ньому ім'я "КодРядОтчета", клацнувши на ньому ЛКМ. На аркуші "Отчет" інверсним кольором виділиться інтервал комірок, якому ви привласнили це ім'я. Далі відкрийте вікно "Формат ячеек" і в полі "Числовые форматы" виберіть рядок "Текстовый". Таким самим способом встановіть текстовий формат і для інтервалу комірок, яким присвоєне ім'я "КодРядРЛОтчета". Якщо після цього в комірці G22 не з'явиться значення 725,00, перевірте на аркушах "Отчет" і "РЛОтчет" нумерацію коду рядків - вона повинна збігатися (мал. 4).

Малюнок 4

Відредагуйте на робочому аркуші вміст комірок стовпчика "Код рядка". У відповідних блоках робочого аркуша додайте нуль до кодів 20, 30, 31, 32, 40, 70 і 80 (у цьому разі комірки цього рядка повинні мати текстовий формат). Після цих операцій введена формула масиву дасть потрібний результат.

Може статися, що після введення останнього аргументу формули і підтвердження її введення у вас на екрані з'явиться вікно (мал. 5), що інформує про помилку у введеній формулі.

Малюнок 5

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

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

В останньому випадку з'явиться вікно, в якому буде зазначено причину помилки (мал. 5).

Після натиснення кнопки "ОК" курсор знову переміститься в рядок формул, і вам доведеться усувати помилку самостійно.

Увага! Логічні вирази, що вводяться після функції "ЕСЛИ", обов'язково повинні бути взяті в круглі дужки, наприклад: "ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета;1;0)".

Якщо не дотриматися цієї вимоги і не поставити, наприклад, круглу дужку після 0, після введення всієї формули також з'явиться повідомлення про помилку з пропозицією прийняти виправлення (мал. 5). У цьому випадку програма запропонує поставити ще одну круглу дужку наприкінці формули. Якщо прийняти таке виправлення, результат обчислення формулою масиву буде неправильним. Тому натисніть кнопку "Нет" і виправте формулу вручну.

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

Синтаксис формули масиву

Враховуючи важливість введеної формули, розглянемо докладніше всі її аргументи.

Значення функції "СУММ" у наведеній формулі буде розглянуто нижче.

А зараз розберемо значення формули пофрагментно.

Отже, перший фрагмент формули: "ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета;1;0)".

Він означає:

- якщо дата, введена в комірку з іменем "ДатаОтчета" і зменшена на 1, збігається зі значенням будь-якої комірки, що входить в інтервал під іменем "ДатаРЛОтчета", у комірку G22 буде повернено значення 1. Якщо рівняння не виконується, в комірку G22 повертається значення 0.

У нашому випадку, обчислюючи формулу масиву, Excel запам'ятає всі рядки на аркуші "РЛОтчет", які містять дату, що відповідає даті, на яку складений баланс (якщо в комірці "ДатаОтчета" введено "1 квітня 2001 року", буде вибрано комірки, що містять дату "31.03.2001"; у завданні це рядки 8, 16, 23, 30, 38, 46 тощо, мал. 6).

Малюнок 6

Може видатися дивним, що нібито в цій формулі виконуються математичні дії з нечисловими значеннями ("ДатаОтчета-1"). Однак це не так. У Excel дати, введені в комірки, запам'ятовуються у вигляді послідовності чисел, і тому їх можна віднімати, додавати і використовувати в інших обчисленнях. Наприклад, якщо в комірку G5, що має ім'я "ДатаОтчета", внесено "1 квітня 2001 року", Excel оперує значенням 36982, яке використовується в обчисленнях (відлік ведеться з 01.01.1900 р.).

Наступний фрагмент формули:

ЕСЛИ(КодРядОтчетаТек=КодРядРЛОтчета;1;0)

означає:

- якщо код рядка, в якому міститься введена формула масиву, збіжиться під час сканування інтервалу комірок з іменем "КодРядРЛОтчета" принаймні з одним таким самим кодом, то в комірку повернеться значення 1. Якщо в інтервалі комірок "КодРядРЛОтчета" не буде знайдено такий самий код, у формулу повернеться значення 0. У нашому завданні дві наведені умови задовольняє рядок 8 на аркуші "РЛОтчет" (мал. 6).

Увага! Якщо ви в цей фрагмент формули замість аргументу "КодРядОтчетаТек" вставите ім'я "КодРядОтчета", в комірку буде записаний 0.

Так станеться тому, що програма порівнюватиме два масиви, а не шукатиме збіги одного критерію в одному масиві. Причому в комірці, в яку ви введете таку формулу, не буде автоматичної вказівки на помилку типу "ІИМЯ?" або "ІН/Д".

Ми розглянули два фрагменти формули, в яких використовується оператор порівняння "ЕСЛИ". Ще один аргумент у формулі - "ВсегоРЛОтчет" - ім'я, присвоєне інтервалу комірок, що містять дані для заповнення графи 4 балансу "На кінець звітного періоду". Він представлений у формулі окремим співмножником.

Формула масиву вибере значення тих комірок інтервалу "ВсегоРЛОтчет", які міститимуться в рядках, що задовольняють дві розглянуті умови: "ДатаОтчета-1=ДатаРЛОтчета" і "КодРядОтчетаТек=КодРядРЛОтчета".

У завданні, яке ми розглядаємо, цим критеріям відповідає комірка L8 на аркуші "РЛОтчет" (мал. 6). Якщо припустити, наприклад, що в комірці А46 (мал. 6) стояло б значення 020, то формула масиву підсумувала б комірки L8 і L46, в балансі у комірці G22 (мал. 1) з'явилось би значення 1023,00 (725,00+298,00).

І нарешті, у формулі масиву використовується функція "СУММ". Розташування цієї функції на початку даної формули обов'язкове.

Функція "СУММ" враховує тільки числа, пропускаючи пусті комірки, логічні значення і тексти. Тому при виконанні у формулі масиву умови (наприклад, "ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета") обов'язково треба використати числове значення 1 замість "ИСТИНА" при виконанні умови і 0 замість "ЛОЖЬ" - при невиконанні умови.

Таким чином, цю формулу можна подати у вигляді добутку трьох співмножників у такому вигляді:

=a*b*(еn), де

a, b - значення, отримані при виконанні першої і другої умов "ЕСЛИ";

n - значення комірок з інтервалу "ВсегоРЛОтчет", що задовольняють виконані вище умови.

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

Отже, у вас вже введена в комірку G22 формула масиву. Скопіюйте її в інші комірки фінансового звіту. Для цього виділіть комірку G22 (у рядку формул з'явиться введена формула масиву) і клацніть ЛКМ на піктограмі "Копіювати". Після цього рамка комірки виділиться рухомою пунктирною лінією. Потім виділіть групу комірок, у які ви спочатку введете формулу (наприклад, G25:G29), і клацніть на піктограмі "Вставити" (мал. 7). Формула масиву запишеться у виділені комірки. Аналогічно скопіюйте формулу в інші послідовні групи комірок, використовуючи піктограму "Вставити" (вона доступна, поки комірка-джерело виділена рухомою пунктирною лінією).

Малюнок 7

Тепер введіть формули в комірки 4-ї графи з підсумковими даними.

У комірки активу фінансового звіту введіть формули:

- в комірку G24 (Залишкова вартість основних засобів) - "=G25-G26";

- в комірку G29 (Усього за розділом I) - "=G22+G24+G27+G28";

- в комірку G34 (Чиста реалізаційна вартість) - "=G35-G36";

- в комірку G44 (Усього за розділом II) - "=СУММ(G31:G34;G37:G43)";

- в комірку G46 (Баланс) - "=G29+G44+G45".

У комірки пасиву фінансового звіту введіть формули:

- в комірку G56 (Усього за розділом I) - "=СУММ(G51:G54)-G55";

- в комірку G68 (Усього за розділом IV) - "=СУММ(G60:G67)";

- в комірку G70 (Баланс) - "=СУММ(G56:G58)+G68+G69".

На цьому введення формул у графу 4 фінансового звіту закінчено.

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

Для того щоб у фінансовому звіті заповнити графу 3 ("На початок звітного року"), у комірку F22 потрібно ввести формулу, яка мало чим відрізняється від введеної в комірку G22 (мал. 8).

Малюнок 8

Формула має такий вигляд:

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

Як бачимо, відмінність тільки у першій умові:

ЕСЛИ(МЕСЯЦ(ДатаРЛОтчета)=1;1;0).

Суть її полягає ось у чому:

- якщо місяць будь-якої комірки, що входить в інтервал з іменем "ДатаРЛОтчета", дорівнює 1, то виконуватиметься наступна умова. В іншому випадку в комірку F22 буде записане нульове значення.

Щоб спростити введення наведеної формули, скопіюйте в комірку F22 формулу з комірки G22 і відредагуйте в ній першу умову.

Зверніть увагу на те, що аргумент функції "МЕСЯЦ" має бути взятий у круглі дужки: "МЕСЯЦ(ДатаРЛОтчета)".

Після редагування формули підтвердьте її введення натисненням клавіш Ctrl + Shift + Enter.

Тепер можна скопіювати цю формулу у всі комірки графи 3. Відразу видаліть формулу з "порожніх" комірок, таких як, наприклад, F23.

І нарешті, в комірки, в яких підсумовуються дані (F24, F29, F34, F44, F46, F56, F68, F70) з сусідніх комірок сусідньої графи 4.

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

Як змінювати розмірність даних у фінансовій звітності

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

Щоб забезпечити у фінансовому звіті автоматичне перемикання розмірності даних, досить внести незначні зміни у формулу масиву.

Ми вже з'ясували, що введені у фінансовий звіт формули являють собою добуток кількох співмножників. Тому для зменшення одиниці вимірювання даних на 3 порядки досить зменшити у стільки разів один із співмножників. Наприклад, перший співмножник формули можна записати в такому вигляді: "СУММ(ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета;0,001;0)ѕ".

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

Введіть, наприклад, у комірку I17 значення 0,001, а перший фрагмент формули, записаної в комірку G22, відредагуйте таким чином:

=СУММ(ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета);I17;0)ѕ

Після такого редагування (не забудьте натиснути комбінацію клавіш Ctrl + Shift + Enter) значення, записане в комірку G22, зменшиться в тисячу разів, тобто буде подане в тисячах гривень.

Однак не поспішайте копіювати цю формулу в інші комірки 4-ї графи балансу. Для того щоб вона працювала і в інших комірках, потрібно відносне посилання на комірку I17 замінити абсолютним:

=СУММ(ЕСЛИ(ДатаОтчета-1=ДатаРЛОтчета;$I$17;0)ѕ

Аналогічно відредагуйте формулу масиву, введену в графу 3 фінансового звіту. Її змінена частина матиме такий вигляд:

=СУММ(ЕСЛИ(МЕСЯЦ(ДатаРЛОтчета)=1;$I$17;0)ѕ

Тепер для зміни розмірності валюти балансу досить ввести необхідний коефіцієнт тільки в комірку I17 (мал. 9).

Малюнок 9

Увага! Для введення коефіцієнта можна вибрати будь-яку комірку-джерело, звертаючи увагу тільки на те, щоб вона не потрапила в поле друку документа.

На наступному уроці ми розглянемо особливості автоматизації складання звіту про фінансові результати і друку документів фінансової звітності.


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

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