Автоматизації бухобліку: Excel

Урок 26
Автоматизація складання проведень операцій з допомогою EXCEL.

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

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

Додатковий блок - генератор проведень операції переоцінки ОЗ

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

Вирішити таку задачу можна за допомогою додаткового блоку (U2:AB9, мал. 1), розташованого над розділом "Переоцінка". У блоці є дві області: для ручного введення даних (U2:Y2) та області автозаповнення (АВ2 - індекс переоцінки, Z4:AB9 - робоча частина). Генерування проведень у блоці відбувається після введення в комірку V2 реєстраційного номера об'єкта ОЗ, а в комірку Х2 - дати проведення переоцінки.

Малюнок 1

Автозапис проведень операції переоцінки

Значення індексу переоцінки, проведеної на зазначену в комірці Х2 дату, записується у блок автоматично за допомогою формули масиву¹:

АВ2 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата; ИндБ)))}.

Введення формули масиву закінчіть натисненням клавіш Ctrl - Shift - Enter.

Автоматичне введення проведень забезпечується формулами (див. таблицю 1), які записуються в робочу частину блоку (Z4:AA9). Нехай вас не бентежить наявність такої їх кількості. Робота з введення формул оправдає себе, оскільки в результаті ви позбудетеся необхідності визначати і порівнювати численні критерії і умови, вказані Положенням (Стандартом) 7.

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

Таблиця 1

Автоматичний запис суми проведень забезпечується також формулами масиву.

АВ4 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ABS(Изн-(Изн/ИндБ));ЕСЛИ(AB2=0;0))))};
АВ5 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ABS(ДК);0)))};
АВ6 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ЕСЛИ(AB2>1;ABS(ВД);0))))};
АВ7 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ЕСЛИ(AB2<1;ABS(ВД);0))))};
АВ8 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ЕСЛИ(AB2>1;ABS(ВД);0))))};
АВ9 - {=СУММ(ЕСЛИ(V2=Номер;ЕСЛИ(X2=Дата;ЕСЛИ(AB2<1;ABS(ВД);0))))}.

Доти, поки відповідним інтервалам комірок не будуть присвоєні імена, що використовуються у формулах, після введення цих формул у комірках блоку буде значення помилки "#ИМЯ?".

Отже, у формулах масиву аргументами є інтервали комірок, позначені іменами. На мал. 2 показано, яким областям таблиці їх треба присвоїти. Кожен інтервал має починатися з першого (робочого) рядка таблиці (на мал. 2 це рядок 12). Оскільки в таблицю вносяться дані об'єктів ОЗ різних груп (без попереднього групування їх за групами), бажано вибрати інтервал, що забезпечує обробку даних якомога більшої кількості об'єктів. Наприклад, якщо ви під іменем "Номер" позначите інтервал комірок А12:А1000, це дасть можливість автоматизувати обробку 75 об'єктів основних засобів (за умови, що ви для кожного об'єкта виділяєте в таблиці для облікових даних 12 рядків, тобто на 1 рік). Врахуйте, що й інші імена інтервалів, показаних на мал. 2, повинні мати формули типу "='Таблиця ОЗ'!$C$12:$C$1000" (це формула для імені "Дата").

Малюнок 2

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

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

Малюнок 3

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

Приклад 1. На підприємстві проведено переоцінку (в таблицю в комірку D13 записано нове значення справедливої вартості - 1400 грн, мал. 2), внаслідок якої на 1 січня 2001 р. первісна вартість об'єкта групи і його знос були уцінені з індексом 0,87 (V13).

Для створення проведень за цією операцією введіть у комірку V2 порядковий номер об'єкта, а в комірку Х2 - дату періоду, на початок якого проведено переоцінку (01.01.2001). У результаті будуть згенеровані три проведення.

Приклад 2. Попередньо уцінений об'єкт ОЗ при черговій переоцінці 01.04.2001 р. було дооцінено з індексом 1,33 (мал. 4). Ця операція відобразилася у зміні не тільки первісної вартості об'єкта і накопиченого зносу, а й додаткового капіталу, а також доходів звітного періоду. Згенеровані у блоці проведення та суми, що їм відповідають, легко перевірити за даними, наведеними в основній таблиці.

Малюнок 4

За проведеннями, показаними в блоці, первісна вартість об'єкта (W15, мал. 4) збільшилася на 216,67 грн (66,67 + 20,77 + 129,23), (АВ4:АВ6). Перевірте: на таку ж суму змінилася і первісна вартість об'єкта в таблиці (W16 - W15) - 866,67 - 650,00 = 216,67.

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

Приклад 3. На малюнку 5 видно, що в цій самій групі є ще один об'єкт - сканер.

Малюнок 5

На дату поточної переоцінки (01.04.2001 р.) його справедлива вартість становила 280 грн (D119, мал. 5), що становило 89% залишкової вартості на цю дату.

Тобто первісна вартість сканера має бути уцінена до 404,44 грн (W119), накопичений знос - до 124,44 грн.

Тепер введіть у комірку V2 обліковий номер сканера - 9. У блоці з'являться проведення, що відображають уцінку сканера.

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

Це пов'язано з тим, що до поточної уцінки сканер був попередньо дооцінений.

Аналогічно можна отримати проведення за кожним об'єктом, який задовольняє критерій переоцінки - на дату балансу його залишкова вартість більш ніж на 10 відсотків відрізняється від справедливої вартості.

Як зберегти згенеровані проведення

Отже, у вас з'явилася можливість генерувати бухгалтерські проведення операцій переоцінки. Однак проведення, отримані для одного об'єкта, будуть автоматично замінені іншими, якщо ви введете номер іншого об'єкта або нову дату переоцінки. Як бути? Вихід простий - зберегти створені проведення в Журналі господарських операцій².

Нехай на підприємстві переоцінці підлягають два об'єкти групи. Згенеруйте проведення операції переоцінки першого об'єкта і скопіюйте комірки робочої зони блоку (U4:AB9, мал. 5). Тепер відкрийте Журнал господарських операцій (аркуш "ЖурналОп", книга "Баланс") і за допомогою команди "Специальная вставка" (на цій вкладці встановіть перемикач "значення") вставте скопійовані комірки в нижню вільну комірку графи "Зміст операції" (Е54, мал. 6). Ті ж дії повторіть для згенерованих операцій переоцінки другого об'єкта групи.

Малюнок 6

Порожні рядки, що з'явилися між проведеннями, сміливо видаліть. Зверніть увагу, що суми перенесених проведень розміщені у графі "Перевірка". Перемістіть їх у графу "Сума операції". Таким чином дані, отримані в таблиці обліку основних засобів, будуть оброблені в Журналі операцій і враховані при автоматичному створенні бухгалтерської звітності. Як це відбувається, було вже розказано в попередніх уроках.

---

Примітки:

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

²Про створення Журналу господарських операцій було розказано в уроці №8 (див. "ДК" №12/2001).