Заполнение формы Т-12 в Excel. I часть

Заполнение формы Т-12 ExcelЗаполнение формы Т-12 в Excel. Часть I.

В серии статей «Учет рабочего времени в Excel» мы настраивали автозаполнение табеля учета рабочего времени в установленной отчетной форме Т-12.

Правда 3-я и 4-я страницы этой формы остались без внимания. А ведь это страницы расчета с персоналом по оплате труда и статистических итогов по отработанному времени сотрудников. Когда таблица создавалась, мы дали этим страницам имена «Расчеты» и «Итоги«.

В этой серии статей мы продолжим настройку формы таким образом, чтобы ее заполнение занимало минимум рабочего времени а, в идеале, полностью заполнялось автоматически. К слову, напомню, что раздел «Расчет с персоналом по оплате труда» можно не вести, если в организации временной учет и расчеты с персоналом по оплате труда ведутся отдельно.

Внесение первичных данных

Начнем с самого простого — сделаем ссылки на ячейки, которые уже должны быть заполнены на предыдущих страницах. Это колонка «Табельный номер» и графа 22 — «Часы (дни)«. Табельный номер мы заносили на странице «Учет«. Ставим знак «=» и кликаем мышкой на соответствующей ячейке. И так по всему списку.

Расчет отработанных в течение месяца часов осуществляется на странице «Табель«. Это ячейки в колонке «Всего«, являющейся подразделом колонки «Итого отработано за месяц«. Также, как и табельный номер, вносим ссылки на эту колонку.

Далее, в графе 19, пишем установленный для каждого сотрудника оклад (о более сложных схемах оплаты труда мы будем говорить в другой статье). Графа 20 для бухгалтеров. В нее заносится счет БУ, на который относятся расходы по оплате труда.

Собственно, расчеты, начинаются с графы 21. Это окладная сумма, которая рассчитывается пропорционально отработанному времени (как правило, в часах). И вот здесь начинается очень интересная тема. Для ежемесячного учета мы отталкиваемся от нормативов рабочего времени, установленных трудовым законодательством и указанных в ежегодно утверждаемом правительством производственном календаре.

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

Подготовка списка для месячного норматива рабочего времени

Для начала дополним список с месяцами. Откроем страницу «Список«, и добавим колонку справа от наименований месяцев. Назовем эту колонку «Норматив«.

Один важный момент. Поскольку мы будем использовать данные из табеля, внесенные в формате времени, то и здесь норматив часов по месяцам я рекомендую вносить в формате времени. Иначе впоследствии придется очень повозиться, чтобы расчеты были адекватными.

Поэтому выделяем колонку, в которую будем заносить данные, и через правую кнопку мыши вызываем диалоговое окно «Формат ячеек«. Чтобы лишние поля, типа «Минуты» и «Секунды» нам не мешались, я выбираю пункт «Все форматы» и ставлю в поле «Тип» букву «ч» в квадратных скобках (вот так — [ч]), обозначающей отображение только часов.

Найдем в интернете производственный календарь (я использую эту  ссылку) и перепишем с него нормативы по каждому месяцу для 40-часовой рабочей недели. Только записывать нужно будет с минутами через двоеточие, например — 120:00. Иначе программа будет число воспринимать неадекватно.

Во второй части статьи «Учет рабочего времени в Excel» мы давали имя списку месяцев. Сейчас нам необходимо создать еще один поименованный диапазон. Пусть он будет называться «НормативМесяц» (без пробела). Для этого выделяем диапазон с ячейки A2 до ячейки B13 (в него должен попасть список месяцев и нормативы часов в эти месяцы). В адресную ячейку вносим имя «НормативМесяц» (см. картинку выше).

Настройка автоматического расчета окладной части заработной платы

Теперь мы можем рассчитать окладную сумму, рассчитанную пропорционально отработанному времени сотрудника. Формула арифметически проста — отработанное время делим на нормативное время и умножаем на оклад. Но как это реализовать в Excel?

Нормативное время мы можем взять на основании выбранного на странице «Учет» месяца. Для этого используем функцию ВПР, которая будет выводить то количество часов, которое соответствует выбранному месяцу (для этой функции мы и создавали диапазон «НормативМесяц«). Функцию можно сразу ввести в строку формул: ВПР(Учет!C3;НормативМесяц;2;ЛОЖЬ).

Устанавливаем курсор вначале формулы (после знака равно) и вводим частное — адрес ячейки с суммой отработанного времени. Для первой записи это будет ячейка BI11 (я, обычно, не заношу адрес ячейки вручную, а просто кликаю по ней мышкой).

Затем устанавливаем курсор в конце формулы и умножаем все на ячейку с суммой оклада (М11). Конечная формула для первой строки будет выглядеть так: =BI11/ВПР(Учет!$C$3;НормативМесяц;2;ЛОЖЬ)*M11

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

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

Копируем формулу на все прочие ячейки

Графы с 23 по 32 оставляем пустыми. для нашей формы оплаты труда они не используются. С граф 33 и 34 делаем простые ссылки на графы 21 и 22.

Страница «Расчет» заполнена. Страницу итогов начнем настраивать в следующей статье.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *