Расчетная область

Область табеля с формулами, в которой производятся расчеты, находится в диапазоне AI:AS23 (рис. 7.3). На примере формул для первого служащего рассмотрим, какие величины определяются в расчетной области.

Расчет отработанного времени в часах

Сумма отработанных часов определяется в ячейке АПЗ с помощью следующей формулы: 

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

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

Расчетная область

Рис. 7.3. Расчетная область

Количество часов, отработанных в ночное время, вычисляется в ячейке АК13 как сумма значений во второй строке, где фиксируются эти часы: 

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

Расчет отработанного времени в днях

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

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

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

Количество дней, проведенных служащим в командировке, подсчитывается в ячейке AM13 (рис. 7.4): 

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

Критерием является буква "к", которая вносится в табель, если работник находится в командировке.

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

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

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

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

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

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

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

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

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

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

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

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

Формулы в области вычислений

Рис. 7.4. Формулы в области вычислений

Мы описали все формулы, кроме той, которая находится в ячейке AJ13 и рассчитывает количество часов, отработанных в выходные и праздничные дни. Эта формула требует отдельного анализа.

Формирование дат праздников

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

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

Итак, наименования праздников введите в ячейки столбца В. Месяцы и дни укажите раздельно (в столбцах С и D), а номер текущего года - в ячейке С2 (рис. 7.5).

Даты официальных праздников на листе Праздники

Рис. 7.5. Даты официальных праздников на листе Праздники

Преобразование элементов введенных дат в формат Excel можно произвести при помощи модуля, созданного в главе 6. Для его внедрения в рабочий лист Праздники выполните следующие действия:

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

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