Автоматизация расчета зарплаты

Расчет зарплаты в соответствии с действующим законодательством доста­точно сложный процесс, учитывающий множество нюансов. В небольшом при­мере невозможно описать все ситуации, которые могут возникнуть, но большин­ство стандартных ситуаций учесть можно.

Для расчетов зарплаты будем использовать рабочую книгу Excel с множеством листов. На отдельном листе расположены неизменные исходные данные — список сотрудников и некоторые дополнительные данные. Расчеты в каждом месяце ведутся на отдельных листах. Стандартные бланки — расчетная и платежная ведомости также расположены на отдельных листах и заполняются автоматически.

Рис 8.1. Список сотрудников и праздники

Вначале нужно ввести исходные данные, неменяющиеся от месяца к меся­цу, то есть список сотрудников предприятия. Отметим, что при принятии на ра­боту или увольнении работника, описанные формулы нужно усложнить, добавив дополнительную проверку, но в нашем примере такие варианты не предусмотре­ны. При поступлении нового сотрудника он просто добавляется в список, а при увольнении можно удалить сотрудника из списка. В первой строке введите заго­ловок списка, а во второй — заголовки столбцов. В остальных строках нужно вве­сти данные о сотрудниках (Рис.8.1).

Обязательно нужно ввести табельный номер, фамилию и оклад сотрудника.
Количество и состав остальной информации, помещаемой в следующие столбцы
таблицы, зависит от того, какую часть расчетов зарплаты вы хотите автоматизировать, а также от того, какой состав сотрудников на предприятии, то есть какие особенности расчета зарплаты учитыва­ются для сотрудников. Создайте список сотрудников и введите информацию о

нескольких сотрудниках, чтобы использовать ее в дальнейших расчетах. Чтобы не путаться в дальнейшем с названиями листов, переименуйте текущий лист, на­звав его Сведения. В правой части листа мы располо­жили список праздников текущего года. Он нужен для автоматического подсчета рабочих дней в любом месяце. Если вы не хотите определять автоматически эту информацию, а ввести ее вручную, вводить список праздников не нужно. Для тех, кто решил автоматизировать определение рабочих дней, необходимо учесть несколько правил. Все даты вводятся как дата, а не как текст! Хотя в примере в ячейке вы видите 01 января, в действительности в ячейку введена да­та 01.01.02, после чего применен формат [$-FC19]ДДММММ.Совсем не обяза­тельно применять именно этот формат, но вводить нужно именно дату, а не про­сто текст. Даты необходимо вводить с учетом выходных дней в текущем году. Ес­ли вы укажите, что, например, праздником является 1 января 2010 года, то это не значит, что праздником будет считаться 1 января 2011 года. Также, если вы­ходной день перенесен на другое число, программа не может самостоятельно догадаться об этом. Вы должны ввести в список праздников все дополнитель­ные выходные дни, чтобы Excel смог вычислить количество рабочих дней в нуж­ном месяце.

Чтобы работало автоматическое определение количества рабочих дней, дол­жен быть установлен Пакет анализа.. Для установки Пакета анализа нужно выбрать ко­манду Сервис → Надстройки и установить в появившемся диалоге флажок рядом с названием Пакет анализа. Вслучае, когда данного названия нет в диалоге, нуж­но выполнить дополнительную установку Excel, чтобы добавить в систему нуж­ную надстройку.

После того, как введены исходные данные, нужно ввести формулы в лист расчетов зарплаты. Как отмечалось выше, на листе выполняется расчет зарплаты в конкретном месяце, а скопировав содержимое листа на другой лист, вы може­те рассчитать зарплату за другой месяц. Перейдите на новый лист и приготовь­тесь вводить расчетные формулы.

В заголовке, кроме названия таблицы, нужно ввести дату, для которой вы­полняются расчеты. При этом нужно ввести именно дату, например 1.01.10, а не слова «Январь 2010». Чтобы в таблице выводилось название месяца и год, нужно выбрать соответствующий формат даты. В нашем примере дата введена в ячейку J1,и именно эта ячейка используется в формулах.

Далее нужно определить количество рабочих дней. Если вы не ввели ранее список праздников или не установили пакет анализа или просто не доверяете Excel в определении количества рабочих дней, введите вручную нужное число в ячейку D2. В противном случае введите в эту ячейку формулу

=ЧИСТРАБДНИ(ДАТА(ГОД(J1);МЕСЯЦ(J1); 1);КОНМЕСЯЦА(J1;0);

Сведения!G3:G13)

Функция ЧИСТРАБДНИ вычисляетколичество рабочих дней между датами ДАТА(ГОД(J1);МЕСЯЦ(J1);1)иКОНМЕСЯЦА(J1;0), то есть между первым и по­следним числом нужного месяца, который введен в ячейку J1. Аргумент Сведения!G3:G13 задает список праздников, о котором было рассказано выше.

С четвертой по шестую строку вводятся заголовки расчетного листа, а начи­ная с седьмой строки - расчетные формулы и исходные данные. Первые 11 столбцов предназначены для определения величины начислений (Рис.8.2).

Рис.8.2. Левая часть расчетного листа

 

Введите названия столбцов, после чего приступим к вводу нужных формул.

Для автоматической нумерации по порядку применим прием, описанный в практической работе №6. Введите в ячейку А7 формулу =ЕСЛИ(В7< >"";1;""), тоесть, если введен табельный номер, в ячейке выводится единица, а в противном случае выводится пробел. Введите в ячейку A8формулу=ЕСЛИ(В8<>"";А7+1;"”) ипротянитеее на расположенные ниже строки, чтобы количество строк с формулами было не меньше, чем количество работников предприятия. Табельный номер в следующем столбце вводится вручную, и автоматически по­является порядковый номер в первом столбце.

Фамилия вставляется с помощью функции ВПР, которая используется в предыдущих практических работах. Введите в ячейку С7формулу =ЕСЛИ(В7<>"";ВПР(В7;Сведения!$А$3:$Е$100;2;ЛОЖЬ);””)и протяните ее на следующие строки. Если введен табельный номер, из списка сотрудни­ков берется фамилия, соответствующая этому номеру. Аналогично в ячейку D7введите

=ЕСЛИ(В7< > "";ВПР(В7;Сведения!$А$3:$Е$100;3;ЛОЖЬ)и протяните эту формулу на следующие строки. . В данный столбец помещает­ся оклад.

Количество отработанных дней и выданный аванс вводятся вручную, а зар­плата вычисляется по простой формуле =D7/$D$2*E7. Введите ее в ячейку G7иразмножьте на расположенные ниже ячейки. В нашем примере отпускные и оп­лата больничного листа заполняется вручную, хотя вы можете добавить форму­лы для их вычислений. Также вручную заполняются прочие доплаты, например премии сотрудникам. В итоге для получения начисленной суммы нужно в ячей­ку К 7ввести формулу =CYMM(G7:J7)и размножить ее на остальные ячейки дан­ного столбца.

Для лучшего внешнего вида скроем нулевые значения ячеек. Для этого вы­делите ячейки, в которых расположены разные суммы, и выберите команду ме­ню Формат → Ячейки. Появится диалог настройки формата, в котором перей­дите на вкладку Число и выберите в списке все форматы. Введите тип формата 0,00;0,00;#.Теперь положительные и отрицательные числа будут выводиться в формате с двумя знаками после запятой, а нули не будут отображаться.

После того, как вы ввели данные о начислениях, нужно заполнить ячей­ки, рассчитывающие вычеты и сумму к выдаче (Рис.8.3). В столбцах с Lпо Y определяется не только сумма к выдаче, но и различные налоги. Введите за­головки столбцов и сгруппируйте ячейки, чтобы получить такую же таблицу, как у нас.

 

Рис.8.3. Правая часть расчетного листа

Вначале рассчитывается ЕСН. Никаких сложностей при вычислении частей этого налога нет. Например, для определения части налога в ФОМС, нужно вве­сти формулу =К7*0,2%.При желании, проценты можно ввести на листе сведе­ний, а при расчете ссылаться на нужную ячейку, но проще ввести процент прямо в формулу. Так же, как и ЕСН, рассчитывается и страхование травматизма. Для расчета подоходного налога учитываются различные вычеты из налогооблагае­мой базы. В ячейку U7вводится формула

=ЕСЛИ(В7< >"";ВПР(В7;Сведения!$А$3:$Е$100;5;ЛОЖЬ);"")позволяющаявставить количество детей сотруд­ника. Отметим, что если по закону выплата на ребенка удваивается, нужно про это указать, что у сотрудника двое детей. Сумма вычета за детей рассчитывается по формуле =U7*300. В ячейке W7 вводятся дополнительные льготы, установлен­ные для отдельных категорий граждан. Для остальных льгота в 400 рублей вводит­ся в столбец Т.Введите в ячейку T7 формулу =ЕСЛИ(И(В7< >"";W7=0);400;0). Так как эта льгота прекращается при получении 20000, в столбец Sвводится суммар­ный полученный доход. В принципе, его также можно рассчитать автоматически, но в нашем простом примере это не делается.

Чтобы получить сумму налога на доходы физических лиц, введите в ячейку Х7формулу

=ЕСЛИ(В7< > "";ЕСЛИ(S7<20000;(K7-T7-V7- W7) *13%;

(K7-V7-W7)*13%);"").

Как и в других формулах, результат выводится только, если введен табель­ный номер. Начисленная сумма без сумм вычетов умножается на 13 процентов. При этом если сумма выплат в этом году не превысила 20000 рублей дополни­тельно используется вычет в размере 400 рублей. Сумма к выдаче вычисляется в ячейке Y7 по простой формуле =ЕСЛИ(B7< >"";K7-F7-X7; ""), то есть из начис­ленной суммы вычитается выданный аванс и сумма подоходного налога.

Далее установите форматы, которые скрывают нулевые значения, как и для первой части таблицы, и размножьте формулы на нужное количество строк. Еще правее, в столбцах АА и АВ, можно ввести названия и общие суммы по разным столбцам, например, Всего начислено и =СУММ(K7:K100) или Всего травматизм и=CУММ(R7:R100).

Вы можете изменить таблицу в соответствии с особенностями расчета зар­платы на вашем предприятии, но основные приемы, изложенные выше, подходят практически везде. В результате вы получите все нужные данные, на основании которых можно автоматически заполнить разные стандартные до­кументы.

Расчеты выполняются на разных листах, каждый для своего месяца. Стан­дартные формы вряд ли нужно размножать, поэтому необходимо вставлять ин­формацию с различных листов в одни и те же бланки. Для того чтобы знать, какой лист используется для текущих расчетов, его имя нужно ввести в какую-либо ячейку. Мы выделили для этого ячейку H1 листа Сведения. Вы можете назы­вать листы так, как вам больше нравится, но перед тем, как заполнять стандарт­ные формы, введите в эту ячейку имя текущего расчетного листа, например, Лист5или Октябрь.

Чтобы получить данные из нужной ячейки, например табельный номер пер­вого сотрудника в списке, следует воспользоваться формулой =ДВССЫЛ(АДРЕС(7;2;;;Сведения!$Н$1)). Функция ДВССЫЛ возвращаетзначение ячейки, ад­рес которой указан в качестве аргумента. Функция АДРЕС возвращает адрес ячейки, расположенной на листе, имя которого задано в ячейке Сведения!$Н$1.Первые два аргумента задают номер строки и столбца, так что с помощью опи­санной формулы можно получить содержимое ячейки В7 нужного листа. Возможно, формулы в ячейках стандартных бланков получатся громоздкие, но до­статочно простые, и вам не понадобится вручную заполнять множество доку­ментов.

Заполнение нужных полей простое и однообразное занятие. Вы берете поле стандартного бланка и находите аналогичное поле листа расчетов. Вставляя фор­мулу, аналогичную описанной выше, вы заполняете поле и переходите к следую­щему. При этом могут понадобиться дополнительные вычисления. Например, если на расчетном листе в ячейке АВ10выводится общая сумма выплаты, вам мо­жет понадобиться вывести ее прописью. Для этого введите формулу

=СуммаПрогшсью(ДВССЫЛ(АДРЕС(10;28;;;Сведения!$Н$1)))

Функция СуммаПрописью должна быть введена дополнительно. Аналогично вы можете применять и другие функции, используя в качестве аргумента описанную формулу. А какие стандартные бланки можно заполнить автоматически на основе полученной информации? Таких бланков достаточно много.

Так как в расчетном листе вычисляются различные налоги, можно сразу за­полнить платежные поручения для их перечисления. О заполнении бланка пла­тежного поручения рассказывалось в практической работе №6-Автоматизация создания платежных поручений. При уплате налогов нужно заполнить ячейки информацией о плательщике и получателе, а сумму взять из расчетного листа. Аналогично можно заполнить расходный кассовый ордер, по которому из кассы выдаются деньги на зарплату. Бланк расходного кассового ордера очень похож на бланк приходного кассового ордера, рассмотренного выше в практической работе №2-Объединение таблиц с автоматизацией расчетов.

Также можно автоматически заполнить расчетную ведомость, то есть стан­дартную форму № Т-51, создание которой описывалось в практической работе №1, и пла­тежную ведомость, то есть стандартную форму № Т-53. Для создания платежной ведомости следует применить точно те же приемы, что и при создании расчетной ведомости. Так как платежная ведомость содержит некоторые поля расчетной ведомости, и не содержит никакой дополнительной информации, ее заполнение не представляет никаких трудностей, если вы сумели заполнить расчетную ведо­мость. Рассмотрим некоторые особенности описания формул для добавления нужных значений в обе ведомости.

Вначале нужно ввести порядковый номер. Для этого используется очень длинная, но чрезвычайно простая формула. Если, как в нашем примере, номер в расчетную ведомость вводится в первый столбец, начиная с пятой строки, в ячейку А5введите формулу

=ЕСЛИ(ДВССЫЛ(АДРЕС(СТРОКА() +2;1;;;Сведения!$Н$1))< > "";

ДВССЫЛ(АДРЕС(СТРОКА() +2;1;;;Сведения!$Н$1));"")

Если в первом столбце расчетного листа на строке с номером, вычисляемым по формуле CTPOKA() +2, есть номер, то он проставляется в расчетную ведо­мость. Так как мы ввели формулу в ячейку пятой строки, то результатом вычис­ления будет семь, то есть первая строка списка в расчетном листе. Если у вас спи­ски расположены в других ячейках, измените соответственно формулу. Размно­жив формулу по другим ячейкам первого столбца расчетной ведомости, вы добавите автоматическую нумерацию для всех запол­ненных строк.

Остальные ячейки заполняются еще проще. Например, чтобы вывести в ячейке СЗ фамилию сотрудника, введите в нее формулу =ЕСЛИ(А5<>” “;ДВССЫЛ(АДРЕС(6+А5;3;;;Сведения!$Н$1));"). Еслиномер по порядку отсутст­вует, выводится пустое место. В противном случае выводится значение из строки 6+А5, то есть из седьмой строки. При этом используется третий столбец, то есть берется нужная фамилия. Размножив формулу, вы заполните все ячейки нужно­го столбца. Аналогично, для вывода выплачиваемой суммы введите в нужную ячейку расчетной ведомости формулу

=ЕСЛИ(А5<>"";ДВССЫЛ(АДРEC(6+А5;25;;;Сведения!$Н$1));"").

Если для некоторых столбцов отсутствуют исходные данные, можно ввес­ти их вручную, но лучше добавить нужную информацию на лист сведений или на расчетный лист. Например, можно добавить профессию сотрудника, доба­вив еще один столбец в список сотрудников. Например, профессия расположе­на в столбце F. В этом случае в ячейку расчетной ведомости нужно вставить формулу

=ЕСЛИ(А5< > "";ВПР(В5;Сведения!$А$3:$Е$100;6;ЛОЖЬ);"")

Как и в расчетном листе, профессия определяется по табельному номеру. Будьте особо внимательны! Придобавлении строк и столбцов в таблицу нужная информация может оказаться в ячейке с другим адресом. Например, вначале название используемого расчетного листа располагалось в ячейке H1,но при добавлении столбца для профессии ячейка поменяла своя адрес на I1.Ссылки в формулах изменились автоматически, но если вы будете вводить новые форму­лы, в них нужно указывать уже Сведения!$I$1, а не Сведения!$Н$1.

Создайте 11листов и скопируйте на них данные с первого расчетного лис­та. Так вы можете выполнять расчет зарплаты для каждого месяца. В новом го­ду лучше создать новую копию рабочей книги. Вы можете добавить к рабочей книге дополнительные листы по количеству сотрудников, и автоматически вес­ти расчеты начислений и удержаний для каждого работника. Все данные для этого имеются, и нужно просто сложить нужные суммы с разных расчетных ли­стов. Вы без труда сможете самостоятельно выполнить эту работу при необходи­мости.

Описанные приемы работы позволяют создать сложную, практически пол­ностью автоматизированную систему расчета заработной платы с помощью эле­ктронных таблиц Excel.