Тема 6. Электронный табель учета рабочего времени.
На основе содержащихся в табеле данных производится расчет заработной платы, табель обычно связывают с базой данных сотрудников и с ведомостью расчета заработной платы.
Табель представляет собой именной список сотрудников подразделения, например, цеха, отдела и т.д., в котором учитывается отработанное каждым сотрудником время.
В табель заносятся данные о каждом дне, а в качестве итога подсчитывается время за месяц. На основе табеля производится расчет заработной платы.Учет использования рабочего времени в табеле осуществляется либо методом сплошной регистрации (для каждого лица фиксируется время прибытия и т.д.), либо путем регистрации отклонений (опозданий, неявок и т.д.).
Различают: двухстрочный и однострочный табели.
Двухстрочный табель
Рассчитаны на предприятия, график которых предусматривает ночные смены, сверхурочные часы и т.п.
Для каждого сотрудника отводится две строки: в нижней указывается количество часов, отработанных в ночное время, а верхняя, предназначена для ввода остальных данных.
Функции двухстрочного табеля
Автоматический расчет отработанного времени в часах, в том числе:
всего отработанного времени;
времени, отработанного в выходные и праздничные дни;
времени, отработанного ночью.
Учет времени в днях, включая:
отработанные дни;
дни, которые сотрудник провел в командировке;
дни, когда сотрудник был в отпуске;
дни, когда сотрудник был в учебном отпуске;
дни, пропущенные из-за болезни;
дни неявки на работу по неуважительной причине;
выходные дни.
Однострочный табель
Предназначен для использования на предприятиях, где не ведутся работы в ночное время, праздничные и выходные дни. Однострочный табель позволяет:
автоматически определять нормативное количество рабочих часов; определять количество календарных дней в месяце; определять коэффициент для начисления заработной платы в зависимости от отработанного времени;
- выводить сообщения в случае возникновения ошибок при вводе.
Структура однострочного табеля показана на рисунке 39:Ш Microsoft Excel - Однострочный табель Введите вопрос
il iffl
Е
А-
^ I Файл Правка Вид Вставка Формат Сервис Данные
но Справка
•isti-
Times New Roman
• ж к ч \\ш\ж
f, =ВПР(Ц4;Праздники!ВЗО:С41;2;ЛОЖЬ) А В с D о н I к L М N о Р о » з т и V W X Y Z АА АВ АС AD АЕ AF AG АН AI 1 ООО "Экспресс" 2 Табель учет рабочего времени 3 4 Март 3 Всего отработано рабочих часов 175 Директор Иваненко ИЛ. 6 Всего отработано дней 31 7 №п.п. Фамилия Табельный номер Должность Числа месяца И1 Р Д1 8 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 9 1 Иванов И. И. 0123 Менеджер от от от от от от от от от от от от от от от ОТ от от от от 8 8 8 8 в в 8 8 8 8 8 10 2 Петров П. П. 1234 Менеджер Б Б Б 8 8 7 Б Б Б 6 6 6 6 Б Б Б 6 6 Б 8 8 в в 8 8 8 8 8 в В 8 И 3 Иваненко И. П. 2343 Н ачалъник Б Б Б 8 8 7 Б Б Б 8 8 8 8 8 от ОТ от от от от от от от от от от от от от от от 12 4 Сидорова Е. С. 3456 Начальник Б Б Б 8 8 7 Б Б Б 8 8 8 8 8 Б Б 8 8 8 8 8 в в 8 8 8 8 8 в в 8 13 3 Петренко П. С. 4367 Н ачалъник Б Б Б 8 8 7 6 6 6 8 8 8 8 8 Б Б 8 8 8 8 8 в в 8 8 8 8 8 в В 8 14 6 Сидоренко С. И. 3678 Инженер Б Б Б 8 8 от ОТ от от от от от от от от ОТ от от от 8 8 8 8 8 в в 8 8 8 8 8 15 7 Карпова Ю М. 6789 Аудитор Б Б Б 8 8 7 Б Б Б 8 8 8 8 8 Б Б 8 8 8 8 8 в в 8 8 8 8 8 в в 8 16 8 ЛещоваТ. И 7890 Секретарь Б Б Б 8 8 7 Б Б Б 8 8 8 8 8 Б Б 8 8 8 8 8 в в 8 8 8 8 8 в в 8 17 9 Щукина Р. Е. 8901 Экспедитор Б Б Б 8 8 7 Б Б Б 8 8 8 8 8 Б Б 8 8 8 8 8 в в 8 8 8 8 8 в Б 8 18 10 Ввюнов П И 9012 Менеджер Б Б Б 8 8 7 Б Б Б 8 8 8 8 8 Б Б 8 8 8 8 8 в в 8 8 8 8 8 в В Р 19 I Irani: т и н > и / Праздники ^Однострочный табель / | < > :
Левая часть однострочного табеля.
ЁЗ Microsoft Excel - Однострочный табель
: Файл Правка Вид Вставка Формат Сервис Данные Окно Справка Введите вопрос »• _ fil X и jd^ji^jjyaia^a-jn-i- -1 & ^ - ЙН; i is - & |
I Times Hew Roman .10 .|ж f Ч | = = = tg | ^ % ООО | iS | ц - - ^ - Д
AQ20 - f. А В с D AD АЕ AF AG АН AI AJ АК AL AM AN АО АР AQ AR 1 ООО "Экспресс" 2 Табель учет1 3 4 5 Всего отработано рабочих часов 175 6 Всего отработано дней 31 7 №п.п. Фамилия Табельный номер Должность Итого раб.
дней Выходн ые дни Больнич ные дни Отпуск Всего дней Итого часов Коэф. Оклад Начи&пено 8 26 27 28 29 30 31 9 1 Иванов ИИ 0123 Менеджер Б 8 8 8 8 8 9 2 0 20 31 72 0,41 5 400,ООр 2 214,ООр 10 2 Петров П. П. 1234 Менеджер 8 8 8 В Б 8 И 12 8 0 31 87 0,49 9 000,00р 4410,00р. И 3 Иваненко И. П. 2343 Н ачапьник от от от от ОТ от 8 6 0 17 31 63 0,36 5 000,ООр 1 800,00р. 12 4 Сидорова Е. С. 3436 Начальник 8 8 8 в Б 8 19 12 0 0 31 151 0,86 6 700,ООр 5 762,ООр 13 5 Петренко П. С. 4367 Н ачапьник 8 8 8 В Б 8 19 9 3 0 31 151 0,86 4 500,ООр 3 870,00р. 14 6 Сидоренко С. И. 3678 Инженер Б 8 8 8 8 8 12 3 0 14 31 96 0,34 9 800,ООр 5 292,00р. 13 7 КарповаЮ М. 6789 Аудитор 8 8 8 в В 8 19 12 0 0 31 151 0,86 5 400,00р 4644,00р. 16 8 ЛещоваТ. И 7890 Секретарь 8 8 8 Б Б 8 19 12 0 0 31 151 0,86 7 000,ООр 6 020,00р. 17 9 Щукина Р. Е. 8901 Экспедитор 8 8 8 Б Б 8 19 12 0 0 31 131 0,86 9 800,ООр 8 428,00р. 18 10 ВВЮНОБ П. И. 9012 Менеджер 8 8 8 Б В ув 18 12 0 0 30 143 0,81 4 000,00р 3 240,00р. 19 Итого: 133 94 И 31 309 1216 6,91 66 600,ООр 45 680,00р. ОП и < > н / Праздники ^Однострочный табель /Готово < | ' - 1 > NUM Рисунок 39
Правая часть однострочного табеля.
Заполнение области ввода
Вставьте новый лист: Однострочный табель.
Начиная с ячейки А8 введите названия столбцов № п/п, Фамилия, Табельный номер, Должность.
Пронумеруйте столбец №п/п.
Выполните связывание книг:
откройте две книги, между которыми будет установлена связь;
расположите их в одном окне: Окно^Расположить.
перейдите в окно Однострочный табель, выделите диапазон ячеек С9:С18, поставьте знак =.
перейдите в окно файла База данных, установите курсор на первый табельный номер. Ссылка автоматически создается абсолютной, поменяйте ее на относительную. Нажмите Ctrl+Enter.
выполните аналогичные действия для столбца Должность, в результате на листе Однострочный табель появятся формулы вида =[База дан- иых.хк] Сотрудники !B2.
Выполните автоматический ввод Ф.И.О.
Используйте функции СЦЕПИТЬ и ЛЕВСИМВ (для написания инициалов).
Формула в ячейке В9 возвращает фамилию, которая находится в ячейке С2 рабочего листа Сотрудники и инициалы, которые берутся из ячеек D2 и Е2. Также формула обеспечивает расстановку между ними точек и пробелов.=СЦЕПИТЬ([База.хк]Сотрудиики!С2;" "; ЛЕВСИМВ([База.хк]СотрудникиЮ2;1); ". "; ЛЕВСИМВ([База.хк]Сотрудники!Е2;1);".") Не забыть: Поменять ссылки с абсолютных на относительные, после окончания ввода формулы нажать Ctrl+Enter.
Действие формулы сводится к следующему: из базы данных извлекается полная фамилия, а от имени и отчества отсекаются первые буквы, после которых ставятся точки и добавляются пробелы.
После закрытия книги, с которой установлена связь, ссылка изменится: в ней будет указан полный путь, по которому находится исходная информация.
Определите нормативное количество рабочих часов.
Нормативное количество рабочих часов для конкретного месяца будет указано в ячейке D5, а количество календарных дней в этом месяце - в ячейке D6. Эти данные находятся на листе Праздники.
Используем функцию ВПР, которая ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Буква «В» в имени функции ВПР означает «вертикальный». В итоге формула в ячейке D5 будет иметь вид: =ВПР(М;Праздники!В30:С41;2;ЛОЖЬ) J4 - ячейка, содержащая название месяца;
Праздники!В30:С41 - диапазон, находящийся на листе Празднике и содержащий
соотношение названий месяцев и количество рабочих часов в них.
ЛОЖЬ - так как диапазон не отсортирован.
Для ячейки D6 создайте аналогичную формулу.
Выполните расчет данных в диапазоне ячеек AJ9:AP19:
столбец Итого рабочих дней содержит формулу: =СЧЁТЕСЛИ(Е9:А19;">0") - подсчитываются ячейки, в которых приведены цифры.
столбец Выходные дни: =СЧЁТЕСЛИ(Е9:А19;"в")
столбец Больничные дни: =СЧЁТЕСЛИ(Е9:А19;"б")
столбец Отпуск: =СЧЁТЕСЛИ(Е9:А19;"от")
столбец Всего дней: =ЕСЛИ(СУММ(AJ9:AM9)=$D$6;СУММ(AJ9:AM9);"Ошибка") - сравнивается количество дней, полученных в области AJ9:AM9, с количеством календарных дней в данном месяце, указанном в ячейке D6. Если условие выполняется, выдается общее количество дней, в противном случае текст - «Ошибка!». Ошибка может быть связана с некорректным вводом данных.
столбец Итого часов: =СУММ(Е9^19)
столбец Коэффициент: =0ТБР(A09/$D$5;2)
в созданном электронном табеле нельзя автоматически определить количество рабочих дней для сотрудников, отработавших неполный месяц по той причине, что они в этом месяце уволены или только что приняты на работу. Это можно исправить модифицировав формулу в ячейке AN9: =ЕСЛИ(СУММ(AJ9:AM9)+СЧЁТЕСЛИ(E9:AI9;"ув")=$D$6; СУММ(AJ9:AM9);" Ошибка!")
Выполните расчет начисленной суммы за месяц.
Добавьте столбец Оклад, с помощью связывания таблиц. Рассчитайте, сколько начислено каждому сотруднику по формуле =Оклад*Коэффициент.