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

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

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

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

Автозаполнение данных о сотрудниках

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

Однако, обратим внимание, нам необходимо указать не только имя сотрудника, но и его должность. В этом случае можно просто объединит данные с двух ячеек через оператор «&«. После того, как мы указали ссылку на данные с ФИО, щелкаем мышкой в строке формулы, чтобы здесь появился мигающий курсор, и вносим необходимые операторы. Затем ссылаемся на вторую ячейку. В конечном итоге должна получиться вот такая формула: =Учет!B6&» «&Учет!C6

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

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

Должно получиться следующее (см. скриншот)

Расчет продолжительности работ и автозаполнение кодов

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

Внесем формулу в ячейку, соответствующую времени, отработанного Ивановым первого числа месяца: =Учет!F6-Учет!E6-Учет!G6

Теперь мы можем протянуть эту формулу до ячейки, соответствующей пятнадцатому числу. Все ссылки переместятся правильно, поскольку количество колонок, объединяющих одну ячейку в табеле совпадает с количеством колонок, соответствующих одному дню на листе «Учет«. Приятное совпадение.

Аналогичным образом вносим формулу в ячейку, соответствующую шестнадцатому числу (=Учет!BN6-Учет!BM6-Учет!BO6), и протягиваем ее до 31-го числа. Во всех этих ячейках необходимо установить формат времени с указанием часов и минут.

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

  • уменьшить размер шрифта до 5 пунктов;
  • увеличив ширину ячеек;
  • задать формат с указанием только часов (при выборе формата ячейки, в поле «Тип» вносим одну букву «ч» см. картинку).

Я решил уменьшить размер шрифта.

Заполняем все ячейки с указанием отработанных часов табеля.

С указанием кода рабочего времени проблем быть не должно — просто ссылаемся на нужную ячейку. Однако, нам не нужны нули в случаях, когда ячейка с кодам на листе «Учет» пуста. Поэтому закладываем следующее условие: =ЕСЛИ(ЕПУСТО(Учет!H6);»»;Учет!H6).

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

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

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