Двухстрочный табель

Двухстрочные табели рассчитаны на предприятия, график которых предусматривает ночные смены, сверхурочные часы и т. п.
В двухстрочном табеле для каждого сотрудника отводятся две строки: в нижней указывается количество часов, отработанных в ночное время, а верхняя предназначена для ввода остальных данных. Следует отметить, что наличие нескольких строк усложняет установление связи с базой данных, то есть дальнейшее копирование строк с формулами. Тем не менее на практике существуют случаи применения трех- и даже четырехстрочных табелей.
Функции двухстрочного табеля
С помощью двухстрочного табеля должно быть обеспечено решение следующих задач:
1. Автоматический расчет отработанного времени в часах, в том числе: П всего отработанного времени;
   - времени, отработанного в выходные и праздничные дни (является день праздничным или выходным, также определяется автоматически);
   - времени, отработанного ночью.
2. Учет времени в днях, включая:
   - отработанные дни;
   - дни, которые сотрудник провел в командировке;
   - дни, когда сотрудник был в отпуске;
   - дни, когда сотрудник был в учебном отпуске;
   - дни, пропущенные из-за болезни;
   - дни неявки на работу по неуважительной причине;
   - выходные дни.

 

 

Создание бланка табеля
Первый этап — создание бланка для табеля на рабочем листе электронной таблицы. Сформируйте систему заголовков табеля, как показано на.
Данные о сотрудниках (фамилии и табельные номера) могут быть введены одним из трех способов:
— с клавиатуры;
— путем копирования из других электронных документов (например, из табеля за прошлый месяц);
— с помощью формул, позволяющих взять информацию из других таблиц.
Третий способ будет рассматриваться при создании однострочного табеля. Сейчас же мы предполагаем, что данные вводятся с клавиатуры.
Процесс форматирования рассматривался и не должен вызвать затруднений.
Для столбцов C:AG необходимо подобрать такую ширину, чтобы в строке 6 дни месяца отображались полностью как на экране монитора, так и при выводе на печать. Для достижения желаемого результата выделите столбцы C:AG и поэкспериментируйте с полем Ширина столбца одноименного диалогового окна.
Заполнение области ввода
Область ввода табеля занимает ячейки C12:AG23 . Для каждого сотрудника отведено две строки: в нижней указывается количество часов, отработанных в ночное время, а в верхней осуществляется ввод остальных данных. Информация может быть представлена в числовом (количество отработанных часов) или текстовом виде. Текст (обычно одна или две буквы) является условным обозначением, например: "к" — командировка, "от" — отпуск, "у" — учебный отпуск, "б" — дни временной нетрудоспособности, "п" — дни неявки на работу по неуважительной причине, "в" — выходные дни.
Одним из главных параметров в табеле является период его заполнения: год, месяц и дни. Эти сведения располагаются в шапке табеля.
Указание в формулах только адресов ячеек с этими параметрами усложняет создание и понимание формул. В Excel предусмотрена возможность присвоения имен как ячейкам, так и целым диапазонам. В частности, целесообразно назначать имена ячейкам, предназначенным для указания года, месяца и числа месяца.
Для присвоения имени ячейке, в которую введено название месяца, поместите табличный курсор в ячейку AF3 и активизируйте команду Вставка/Имя/Присвоить или нажмите комбинацию клавиш [Ctrl+F3]. В результате отобразится диалоговое окно Присвоение имени . Введите в соответствующее поле имя (например, Месяц). В поле Формула будет сформирована запись, указывающая, какой ячейке присваивается имя:

=Табель!$АF$3

Завершите операцию присвоения имени нажатием на кнопку ОК и закройте диалоговое окно.
Присвойте имя ячейке AJ3, в которой указан год, а также диапазону C6:AG6, где приведены числа месяца. Когда имя назначается диапазону ячеек, последовательность действий та же, только выделяется не одна ячейка, а весь диапазон.
Расчетная область
Область табеля с формулами, в которой производятся расчеты, находится в диапазоне AI:AS23 . На примере формул для первого служащего рассмотрим, какие величины определяются в расчетной области.
Расчет отработанного времени в часах
Сумма отработанных часов определяется в ячейке АПЗ с помощью следующей формулы:

=СУММ(С12:АG12)

В данном случае мы производим суммирование значений, указанных в диапазоне C12:AG12 (первая строка для первого служащего).
Количество часов, отработанных в ночное время, вычисляется в ячейке АК13 как сумма значений во второй строке, где фиксируются эти часы:

=СУММ(С13:АG13)

Расчет отработанного времени в днях
Количество отработанных дней определяется в ячейке AL13:

=СЧЕТЕСЛИ(С12:АG12;">0")

Формула создана на основе функции СЧЕТЕСЛИ, которая подсчитывает количество ячеек внутри интервала, удовлетворяющих заданному критерию. Критерием могут служить как цифры (количество отработанных часов), так и буквы (рассмотренные выше условные обозначения, такие как "от" — отпуск, "б" — дни временной нетрудоспособности и т. д.). Формула, приведенная в качестве примера, предназначена для подсчета количества ячеек, в которые введены цифры.
Количество дней, проведенных служащим в командировке, подсчитывается в ячейке AM13 :

=СЧЕТЕСЛИ(С12:АG12;"к")

Критерием является буква "к", которая вносится в табель, если работник находится в командировке.
Подсчет дней, в течение которых работник был в отпуске, производится в ячейке AN13:

=СЧЕТЕСЛИ(С12:АG12;"от")

Количество дней учебного отпуска определяется в ячейке АO13:

=СЧЕТЕСЛИ(С12:AG12;"у")

Подсчет дней, в течение которых работник болел, производит формула в ячейке АР13:

=СЧЕТЕСЛИ(С12:AG12;"б")

Количество дней, в течение которых работник отсутствовал на работе без уважительных причин, подсчитывается с помощью формулы из ячейки AQ13:

=СЧЕТЕСЛИ(С12:AG12;"п")

Счет праздничных и выходных дней ведется в ячейке AR13:

=СЧЕТЕСЛИ(С12:AG12;"в")

Наконец, общее количество протабелированных дней определяется в ячейке AS 13 с помощью такой формулы:

=СУММ(АL13:АR13)

Мы описали все формулы, кроме той, которая находится в ячейке AJ13 и рассчитывает количество часов, отработанных в выходные и праздничные дни. Эта формула требует отдельного анализа.
Формирование дат праздников
Для определения чисел, на которые приходятся выходные дни, суббота и воскресенье, можно воспользоваться функциями Excel. А вот чтобы компьютер мог установить, какой день праздничный, а какой нет, он должен иметь соответствующую информацию.
Введем даты праздников в нашу рабочую книгу на отдельном листе, которому присвоим имя Праздники. Дату праздников можно указать в формате Excel. Однако существуют праздники, которые каждый год приходятся на разные даты (к этой категории принадлежат Пасха и Троица). Если праздник выпадает на выходной день, то выходной переносится на следующий будний день. Поэтому перенесенный выходной тоже может быть представлен как праздник.
Итак, наименования праздников введите в ячейки столбца В. Месяцы и дни укажите раздельно (в столбцах С и D), а номер текущего года — в ячейке С2 .
Преобразование элементов введенных дат в формат Excel можно произвести при помощи модуля, созданного в главе 6. Для его внедрения в рабочий лист Праздники выполните следующие действия:
1. Откройте файл с модулем формирования даты в формате Excel .
2. Выделите диапазон ячеек С5:С7 модуля и скопируйте его в буфер обмена.
3. Перейдите на лист Праздники и активизируйте ячейку Е3.
4. Выполните вставку из буфера, для чего откройте диалоговое окно Специальная вставка и установите флажок Транспонировать. Модуль поменяет ориентацию на рабочем листе и его ячейки будут расположены в одной строке.
5. В ячейку Е3 введите ссылку на адрес ячейки С3 (содержит название месяца).
6. В формуле, которая содержится в ячейке G3, замените адрес ячейки С3 (содержит год в модуле) адресом $C$2, а адрес С4 (это день) — адресом D3.
7. Скопируйте диапазон ячеек E3:G3, в котором находится модуль, и выполните вставку из буфера, выделив область Е3:Е14.
В результате этих наших действий все даты (в формате Excel), на которые припадают праздники, будут расположены в диапазоне ячеек G3:G14. Выделите этот диапазон и присвойте ему имя ДатаПраздн.