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

Автоматизации бухучёта: 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);"Зроби архівування";
"Архів зарплати")
.

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


Следующий урок

План урока

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