Нове число  Дебет-Кредит
  Український бухгалтерський тижневик
#26'2002: Практична бухгалтерія - Автоматично

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

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

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


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

План уроку:

Архівування виплат, що входять до розрахунку середньої зарплати

Розрахунок бази для обчислення середньої заробітної плати у всіх випадках її збереження, у тому числі й відпускних, можна виразити такою формулою:

Бср.зп = Зосн + ДН + Пр + Відп + Лік

де

Зосн - основна заробітна плата. Встановлюється у вигляді тарифних ставок або окладів і відрядних розцінок для робітників і посадових окладів для службовців;

ДН - доплати і надбавки. Сюди входять виплати за понаднормову роботу, поєднання професій і посад, високі досягнення в праці, особливі умови й інтенсивність праці, керівництво бригадою, вислугу років та інші;

Пр - премії. Сюди входять виробничі премії та премії за економію конкретних видів палива, електроенергії і теплової енергії; винагорода за підсумками річної роботи та вислугу років та інші1;


1Повний перелік доплат, надбавок і премій, які входять до складу додаткової зарплати і враховуються при обчисленні середньої зарплати, наведений в інструкції зі статистики заробітної плати, затвердженій наказом Мінстату від 11.12.95 р. №323.

Відп - сума відпускних за попередній період;

Лік - виплати за лікарняними листками за розрахунковий період.

Згідно з Порядком №1002, усі перелічені вище виплати включаються до розрахунку середньої зарплати в тому розмірі, в якому вони нараховані, без виключення сум відрахувань і податків.


2 Порядок обчислення середньої заробітної плати, затверджений постановою КМУ від 08.02.95 р. №100.

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

Малюнок 1

Малюнок 1

Таким чином, значення, що містяться в комірках G9:I16, T9:T16 і Y9:Y16 (для восьми працівників) мають щомісяця автоматично зберігатися в архіві (мал. 2) для подальшого визначення середньої зарплати.

Малюнок 2

Малюнок 2 - збільшити зображення
Збільшити зображення


Як зберігати дані в архіві

Архів - це таблиця, в комірках якої на загальних з даними кожного працівника рядках записуються суми виплат, що входять до бази середньої зарплати. Наприклад, для працівника Бойка Т. В. за червень 2002 р. до архіву буде внесено суму 600,49 грн (AQ9, мал. 2). Вона включає 375,00 грн (F9, мал. 1) + 25,00 грн (Н9) + 209,49 грн (Y9).

У верхній частині таблиці (мал. 2) розташовані кнопки автоматичного керування записом архіву. Кнопка "Занести до архіву" запускає макрос запису розрахункових даних поточного місяця, а кнопка "Повернути дані місяця" - макрос скасування запису.

Для того щоб забезпечити автоматичне збереження розрахункових даних в архіві, треба:

- ввести в комірку AQ7 формулу =В4;

- ввести в комірку AQ9 формулу =ЕСЛИ(AQ$7=СМЕЩ($AQ$7;0;1);0;G9+H9+I9+T9+Y9) і скопіювати її вниз на таку кількість комірок, що відповідає кількості працівників. Умовний оператор (ЕСЛИ(AQ$7=СМЕЩ($AQ$7;0;1);0) використаний у цій формулі для того, щоб після архівування поточного місяця дані з розрахункової відомості не були повторно занесені до архіву;

- створити макрос "запис" і макрос "скасування запису".


Створення макросу "запис"

Створіть керуючий елемент "кнопка" і виберіть у контекстному меню команду Назначить макрос объекту. Запустіть команду Начать запись, у вікні, що відкрилося, присвойте макросу відповідне ім'я, наприклад ЗанестиДоАрхіву, і виконайте послідовно ряд дій:

1-й крок. Виділіть весь стовпчик AR. Клацнувши на ньому ПКМ, виберіть у контекстному меню команду Добавить ячейки.

2-й крок. Скопіюйте весь стовпчик AQ і вставте його в комірку А1, використовуючи команду Специальная вставка зі встановленим перемикачем значения (обов'язково!).

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

4-й крок. Встановіть курсор у комірку AR7 (цього кроку можна не робити). Зупиніть запис.

Записаний макрос не розпізнає, за який місяць зроблено останнє архівування. і щоб не виникла ситуація, коли ви двічі запишете в архів дані за один і той самий місяць, треба доповнити його ще одним рядком. Для цього у вікні Макрос (меню Сервис - Макрос) виберіть рядок ЗанестиДоАрхіву і натисніть кнопку Изменить. Відкриється вікно VBA, і ви побачите, що записали (мал. 3).

Малюнок 3

Sub ЗанестиДоАрхіву()
' ЗанестиДоАрхіву Макрос
' Макрос записан 09.10.2001 (В.Лавренов)
'
If Range("AQ7") > Range("AR7") Then
Columns("AR:AR").Select
Selection.Insert Shift:=xlToRight
Columns("AQ:AQ").Select
Selection.Copy
Columns("AR:AR").Select
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= False, Transpose:=False
Columns("BD:BD").Select
Selection.Delete Shift:=xlToLeft
Range("AR7").Select
End If
End Sub

У перший робочий рядок макросу вставте таку умову:

If Range("AQ7") > Range("AR7") Then

Тепер архівування даних звітного місяця можна буде зробити тільки за умови, що ви ще не натискали кнопки "Занести до архіву".

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


Макрос "скасування запису"

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

Увага! Виправити помилку в архіві можна тільки в останньому збереженому місяці.

Для виправлення помилки в збережених даних і повернення архіву "статус-кво" можна також використати макрос. Він зовсім простий. Створіть керуючу кнопку, назвіть її, наприклад, "Повернути дані місяця" і призначте їй макрос "СкасуватиЗапис". Увімкніть записування макросу - і вперед:

1-й крок. Виділіть весь стовпчик AR і видаліть його.

2-й крок. Виділіть весь стовпчик BD. Клацнувши на ньому ПКМ, виберіть у контекстному меню команду Добавить ячейки. Цей крок треба зробити для того, щоб забезпечити роботу архіву (ще одного) для розрахунку середньої зарплати при визначенні соціальних виплат (напр. лікарняних). Але про це - в наступному уроці.

3-й крок. Встановіть курсор у комірку AQ7 (цього можна і не робити) і зупиніть запис.

Щоб не вийшло так, що ви, натиснувши кілька разів на кнопку "Повернути дані місяця", безповоротно видалите півархіву, доведеться додати до макросу одну умову.

Як і в розглянутому вище прикладі, відкрийте вікно Макрос, виберіть рядок СкасуватиЗапис і викличте VBA-редактор, натиснувши кнопку Изменить. У записаний макрос (мал. 4) введіть такий рядок:

If Range("AQ7") <= Range("AR7") Then

Малюнок 4

Sub СкасуватиЗапис()
' СкасуватиЗапис Макрос
' Макрос записан 09.05.2002 (В.Лавренов)
'
If Range("AQ7") <= Range("AR7") Then
Columns("AR:AR").Select
Selection.Delete Shift:=xlToLeft
Columns("BD:BD").Select
Selection.Insert Shift:=xlToRight
Range("AQ7").Select
End If
End Sub

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

Увага! Видалення або додання комірок у відомість призведе до втрати працездатності архіву. У цьому випадку відкоригуйте записані макроси, пересунувши в них відповідно вперед або назад посилання на комірки. Наприклад, додавши у відомість ще один стовпчик, у макросах треба всі посилання AR замінити на AS, AQ - на AR, а BD - на BE.


Визначення нарахованої суми відпускних

Отже, архів виплат створено. Він дасть можливість автоматизувати розрахунок середньоденного заробітку (СдЗП) для нарахування відпускних за допомогою введеної в комірки графи формули (показано для комірки S9):

=ЕСЛИ(ИЛИ(МЕСЯЦ(В$4)<>МЕСЯЦ(P9);$AQ$7=$AR$7;P9=0);"";
ЕСЛИ(ДОЛЯГОДА(КОНМЕСЯЦА(P9;-1);КОНМЕСЯЦА(C9;0))>=1;
СУММ(AR9:СМЕЩ(AR9;0;11))/G$2;

(СУММ(AR9:СМЕЩ(AR9;0;(ДОЛЯГОДА(КОНМЕСЯЦА(C9;0);
КОНМЕСЯЦА(P9;-2)))*12))/

(КОНМЕСЯЦА(P9;-1)-КОНМЕСЯЦА(C9;0)-D9)))).

Деякі аргументи цієї формули потребують окремого пояснення.

Розрахунок СдЗП за 12 місяців забезпечується фрагментом СУММ(AR9:СМЕЩ(AR9;0;11))/G$2. Це означає, що, наприклад, для працівника Бойка Т. В. будуть підсумовані значення з комірки AR9 і ще 11-ти наступних за нею.

Розрахунок СдЗП за період менше 12 місяців, наприклад для працівника Гетьмана Н. А., буде забезпечуватися таким фрагментом:

(СУММ(AR10:СМЕЩ(AR10;0;(ДОЛЯГОДА(КОНМЕСЯЦА(C10;0);
КОНМЕСЯЦА(P10;-2)))*12))/

(КОНМЕСЯЦА(P10;-1)-КОНМЕСЯЦА(C10;0)-D10).

У ньому (фрагменті) кількість місяців, за які з архіву будуть взяті дані (не враховуючи звітного місяця - AR10), визначається за допомогою функції ДОЛЯГОДА(КОНМЕСЯЦА(C10;0);КОНМЕСЯЦА(P10;-2)).

Помноживши значення цієї функції на 12, ви отримуєте кількість місяців у заданому інтервалі.

Щоб визначити нараховану суму виплат за час щорічної відпустки, введіть у комірку Т9 формулу (мал. 1):

=ЕСЛИ(AQ$7=СМЕЩ(AQ$7;0;1);0;ЕСЛИ(МЕСЯЦ(В$4)=
МЕСЯЦ(P9);(Q9+R9)*S9;0))
.


ПАМ'ЯТКА ДЛЯ ЗАБУДЬКУВАТИХ

Архів сам може нагадати вам зробити операцію збереження даних звітного місяця. Об'єднайте комірки АР5:АР17 (мал. 2) і введіть таку формулу:

=ЕСЛИ(AQ7<>СМЕЩ(AQ7;0;1);"Зроби архівування";
"Архів зарплати")
.

Тепер доти, поки ви не натиснете кнопку "Занести до архіву", в лівому полі архіву буде "красуватися" напис "Зроби архівування".


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

План уроку

Версiя для друку Версiя для друку  Вiдправити по поштiВiдправити по поштi
© 2002
"Дебет-Кредит"
Редакція: debet-kredit@gc.kiev.ua