Учет рабочего времени в Excel. II

Учет рабочего времени в ExcelУчет рабочего времени в Excel. часть II.

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

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

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

О возможностях Excel в этом вопросе я писал в соответствующей статье.

Здесь же мы поговорим, как эти возможности применить к табелю учета рабочего времени.

Вот что нам необходимо обеспечить:

  • в поле «Месяц» должен допускаться только ввод одного из 12 наименований месяца;
  • во всех колонках «Начало» и «Окончание» должен до пускаться только ввод в формате времени от 0 часов до 24 часов;
  • Время окончания не должно быть раньше времени начала;
  • во всех колонках «Обед» должен допускаться только ввод в формате времени от 0 часов до 1 часа;
  • во всех колонках «Код» допускается ввод только одного из кодов рабочего времени.

Защита от ошибочного введения месяца

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

На этом листе перечислим все месяцы. Выделяем список и задаем ему имя — «месяц«. О том, как присваивать имя диапазону и работать со списками, я писал здесь.

Возвращаемся на лист «Учет«, и подводим курсор мыши к ячейке, в которой хотим использовать список. Через меню «Проверка данных» задаем тип данных «Список» и источник данных — диапазон с именем «Месяц«.

Ограничение на ввод данных о времени

Выделим ячейки колонок «Начало» и «Окончание«, относящихся к 1-му числу месяца. Также, через «Проверку данных» задаем тип данных «Время«, и выбираем значения между 0:00 и 23:59. В закладке «Сообщение об ошибке» оставляем вид «Останов«, а в заголовке пишем «Неверный формат данных«. В тексте сообщения пишем примерно следующее — «Введите правильное время от 0:00 до 23:59«.

Похожим образом защитим ячейки в колонке «Обед«, только диапазон значений выбираем между 0:00 и 1:00. В тексте сообщения об ошибке я написал — «Введите правильную продолжительность от 0:00 до 1:00«.

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

Для этого выделяем ячейку в колонке «Начало«, и в меню «Условное форматирование» выбираем пункт «Создать правило«. Затем выбираем пункт «Использовать формулу для определения форматированных ячеек«. Устанавливаем курсор в строке формулы и вписываем условие =E6>F6.

Обратите внимание — если вы не вписываете адреса ячеек с клавиатуры, а указывает на эти ячейки мышкой, то Excel автоматически сделает эти адреса абсолютными (со знаками «$»). Необходимо сделать эти адреса относительными (удалить все знаки «$»). В противном случае при копировании этого условия на другие ячейки, ссылки не будут перемещаться.

Затем устанавливаем курсор в ячейку «Окончание«, и создаем второе правило с аналогичной формулой. Выделим обе ячейки, и протянем условие на всю колонку. Проверяем — работает ли условие.

Защита от ошибочного ввода кода

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

В появившемся окошке ставим галочку в пункте «Пустые ячейки«, и нажимаем «ОК«. Теперь подводим курсор мыши к любой выделенной ячейке, и нажимаем правую кнопку мыши. В открывшемся контекстном меню выбираем пункт «Удалить«, а затем ставим галочку на пункте «Ячейки со сдвигом вверх«. После того, как мы нажмем «ОК«, список приобретет требуемый вид. Выделяем его, и задаем имя «Код«.

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

Завершение защиты листа

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

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

  • Поле с наименованием месяца;
  • Колонка «ФИО сотрудника»;
  • Колонка «Должность»;
  • Колонка «Табельный номер»;
  • Все колонки с данными о времени.

Подведя курсор мыши к любой из выделенных ячеек, нажимаем правую кнопку и выбираем в контекстном меню пункт «Формат ячеек«. Заходим на вкладку «Защита» и отжимаем галочку в пункте «Защищаемая ячейка«.

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

Для удобства, предлагаю закрепить колонку с данными о сотрудниках и строки с наименованием колонок. Для этого устанавливаем табличный курсор на ячейку с адресом E6, и открываем закладку меню «Вид«. Находим пункт «Закрепить области» и нажимаем на него-же в выпавшем списке. Теперь, при прокрутке таблицы вниз или вправо, наименования колонок и данные о сотрудниках не будут перемещаться.

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

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

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