Порядок выполнения лабораторной работы

Основные сведения

 

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

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

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

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

 

Порядок выполнения лабораторной работы

 

1 Создание бланка табеля на рабочем листе электронной таблицы

 

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

Информация может быть представлена в числовом (количество отработанных часов) или текстовом виде. Текст (одна или две буквы) является условным обозначением, например:

«к» – командировка;

«от» – отпуск;

«у» – учебный отпуск;

«б» – дни временной нетрудоспособности;

«п» – дни неявки на работу по неуважительной причине;

«в» – выходные дни.

Одним из главных параметров в табеле является период его заполнения: год, месяц и дни. Эти сведения располагаются в шапке табеля.

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

 

Рисунок 1 – Электронный табель рабочего времени (двухстрочный)

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

 

Формулы для данного расчета должны быть созданы на основе функции СЧЕТЕСЛИ (рисунок 2).

 

Рисунок 2 – Применение функции СЧЕТЕСЛИ

 

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

 

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

Наименования праздников, месяцев и дней введите в ячейки (рисунок 3, столбцы B, C и D соответственно). Для автоматического формирования дат праздников (рисунок 3, столбец F) в колонки под общим заголовком «Модули даты» необходимо ввести формулы. Для расчета порядкового номера месяца (рисунок 3, столбец E) будем использовать встроенную логическую функцию ЕСЛИ (рисунок 4). В ячейку E3 нужно ввести формулу:

 

=ЕСЛИ(C8="Январь";1;ЕСЛИ(C8="Февраль";2;ЕСЛИ(C8="Март";3;ЕСЛИ(C8="Май";5;ЕСЛИ(C8="Июнь";6;ЕСЛИ(C8="Ноябрь";11;ЕСЛИ(C8="Декабрь";12))))))), затем скопировать ее для всего столбца E.

 

Рисунок 3 – Применение встроенной функции ЕСЛИ

 

Преобразование элементов введенных дат в формат Excel (рисунок 3, столбец F) можно произвести при помощи модуля ДАТА (рисунок 4).

 

 

Рисунок 4 – Применение встроенной функции ДАТА

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

 

 

Рисунок 5 – Присвоение имени Датапраздн

 

4 Формирование дат в формате Excel в табеле

В табель под номерами дней вставить три строки, в которые в дальнейшем будут записаны формулы.

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

- адрес ячейки с номером года замените именем Год (рисунок 6);

- адрес ячейки с именем месяца – именем Месяц (рисунок 6).

 

В табель под номерами дней вставить три строки, в которые в дальнейшем будут записаны формулы (рисунок 7).

 

 

Рисунок 6 – Присвоение имен ячейкам в электронном табеле

 

 

Рисунок 7 – Преобразование электронного табеля

 

В первую строку табеля (рисунок 7, диапазон C13:AG13) из листа Праздники перенести формулу расчета номера месяца.

С учетом присвоенных имен формула примет вид:

=ЕСЛИ(месяц="Январь";1;ЕСЛИ(месяц="Февраль";2;ЕСЛИ(месяц="Март";3;ЕСЛИ(месяц="Май";5;ЕСЛИ(месяц="Июнь";6;ЕСЛИ(месяц="Ноябрь";11;

ЕСЛИ(месяц="Декабрь";12))))))).

Во второй строке табеля (рисунок 7, диапазон C14:AG14) с использованием функции ДАТА необходимо с формировать даты месяца, указанного в табеле (рисунок 8).

 

 

Рисунок 8 – Формирование дат в электронном табеле

         

В результате всех преобразований табель должен выглядеть так, как указано на рисунке 9.

 

5 Определение праздничных дней в табеле

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

Функция ВПР ищет значение, заданное в первом аргументе, в крайнем левом столбце диапазона, указанного во втором аргументе.

Синтаксис функции имеет такой вид:

ВПР (искомое значение; инфо_таблица; номер столбца; интервальный просмотр).

 

         

Рисунок 9 – Преобразование электронного табеля (2)

 

Искомым значением может быть число, ссылка или текстовая строка. Аргумент инфо_таблица – это таблица, в которой производится поиск данных. Значения в первом ее столбце могут быть текстовыми строками, числами или логическими значениями. Регистр при поиске не учитывается (то есть строчные и заглавные буквы не различаются). Аргумент номер столбца – это номер столбца в диапазоне инфо_таблица, из которого выбирается возвращаемое значение. Аргумент интервальный просмотр – это логическое значение, которое определяет, должна ли функция ВПР искать точное соответствие. Если он не задан, то ищется точное соответствие. При отсутствии искомого значения возвращается ошибка #Н/Д.

1. Активизируйте ячейку, находящуюся в третьей строке под номером первого дня в табеле (рисунок 9, ячейка C15) и отобразите панель функции ВПР.

2. Убедитесь в том, что курсор установлен в поле Искомое_значение, и выделите ячейку с наименованием даты.

3. Поместите курсор в поле Табл_массив.

4. Ввод имени диапазона ДатаПраздн в поле панели функций можно произвести с клавиатуры (нажать функцио­нальную клавишу [FЗ]). Выделите в полеИмя элемент ДатаПраздн и нажмите кнопку ОК.                 

5. В поле Номер_индекса _столбца введите значение 1, а в поле Диапазон_просмотра — значение ЛОЖЬ.   

6. В завершение нажмите кнопкуОК (рисунок 10).

 

 

Рисунок 10 – Использование встроенной функции ВПР

         

Если функция ВПР не найдет в списке с праздниками указанную дату, она выдаст ошибку # Н/Д (рисунок 11).

 

 

Рисунок 11 – Результат использования функции ВПР

 

Это значение попадет в последующие формулы, и они, в свою очередь, также возвратят ошибку #Н/Д. Вам же нужно получить либо значение ИСТИНА, если день, указанный в табеле, является праздничным, либо значение ЛОЖЬ, если этот день не числится в списке праздников.

Избавиться от ошибки # Н/Д позволяет функция ЕНД, которая при­надлежит к категории Проверка свойств и значений. Данная функция возвращает значение ИСТИНА, если значение предыдущих вычислений является ошибоч­ным.

Сформируйте формулу следующим образом:

=ЕНД(ВПР(C14;ДатаПраздн;1;ЛОЖЬ)).

Однако результат использования функции ЕНД нас не совсем устраивает (рисунок 12). Нам нужно, чтобы в случае от­сутствия даты в списке праздников возвращалось значение ЛОЖЬ, а не ИСТИНА.

 

 

 

Рисунок 12 – Результат использования функции ЕНД

 

Поэтому воспользуемся логической функцией НЕ, которая относится к категории Логические и имеет следующий синтаксис:

НЕ(логическое_значение).

Здесь логическое_значение — это значение или выражение, в результате вычисле­ния которого получается значение ИСТИНА или ЛОЖЬ. Если параметр логическое_значение имеет значение ЛОЖЬ, функция НЕ возвращает значение ИСТИНА, в противном случае данная функция возвращает значение ЛОЖЬ.

Сформируйте следующую формулу:

=НЕ(ЕНД(ВПР(C14;ДатаПраздн;1;ЛОЖЬ)))

Проанализируем созданные формулы. Если дата в формате Ехсel, указанная в ячейке с формулой ДАТА, найдена в списке праздников на рабочем листе Праздники, ячейка с формулой НЕ будет содержать значение ИСТИНА. Когда дата не найдена, функция ВПР возвращает значение #Н/Д, а функция ЕНД исправляет ошибку, выдавая значе­ние ИСТИНА. Функция НЕ инвертирует это значение, вследствие чего мы по­лучим в ячейке с формулой НЕ значение ЛОЖЬ.

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

 

 

Рисунок 13 – Результат использования функции НЕ

         

     6 Определение выходных дней в табеле

 

     Для определения выходных дней в табеле (суббот и воскресений) можно воспользоваться функцией ДЕНЬНЕД, которая устанавливает номер дня недели. Эту функцию введите в ячейке С16: =ДЕНЬНЕД(C14) (рисунок 14).

 

 

Рисунок 14 – Использование функции ДЕНЬНЕД

     В следующую ячейку – C17 – введите функцию ЕСЛИ (рисунок 15):

=ЕСЛИ(C16=7;ИСТИНА;ЕСЛИ(C16=1;ИСТИНА;ЛОЖЬ))

 

 

Рисунок 15 – Определение выходных дней с помощью функции ЕСЛИ

         

В данном случае функция ЕСЛИ действует по описанной ниже схеме:

- день недели, определенный в ячейке C16, – суббота?

- если да, возвращается значение ИСТИНА;

- если нет, переходим к следующему ЕСЛИ;

- если днем недели является воскресенье, возвращается значение ИСТИНА;

- в противном случае возвращается ЛОЖЬ.

С помощью пользовательского формата выделите цветом выходные дни (суббота, воскресенье) и праздники (Главная/Стили/Условное форматирование).

 

Пример конечного варианта электронного табеля рабочего времени изображен на рисунке 16.

Контрольные вопросы

 

1. Что является табелем?

2. На каких предприятиях применяется двухстрочный табель рабочего времени?

3. В чем смысл применения функций MS Excel ВПР, ЕНД, НЕ?

4. В таблице 1 измените месяц «Июнь» на другой месяц, содержащий праздничные дни. Посмотрите, что изменилось в табеле.

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

6. Изменяя дни недели, проверьте, как изменяется табель.

 

 

Рисунок 16 – Электронный табель рабочего времени