Как рассчитать рабочее время excel

MulTEx »

5 Март 2017              35372 просмотров

Данная функция является частью надстройки MulTEx

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

Вызов команды через стандартный диалог:
Мастер функций—Категория «MulTEx»— РАБ_ЧАС
Вызов с панели MulTEx:
Сумма/Поиск/Функции — Полезности — РАБ_ЧАС
Синтаксис:
=РАБ_ЧАС(A1;B1;60)
=РАБ_ЧАС(B2:I2;B3:I3;B4:I4)
=РАБ_ЧАС(A1;B1;C1)
=РАБ_ЧАС(A1;B1)

ВРЕМЯ_Н(A1) — указывается ссылка на ячейку, в которой записано время начала рабочего дня в формате даты-времени Excel. Читать подробнее про дату и время в Excel — Как Excel воспринимает данные

ВРЕМЯ_К(B1) — указывается ссылка на ячейку, в которой записано время окончания рабочего дня в формате даты-времени Excel.

ОБЕД(60) — Необязательный аргумент. Указывается ссылка на ячейку, диапазон ячеек либо непосредственно число, означающее общее количество минут, отведенных для обеденного перерыва. Если не указан, то рабочими часами будет считаться все время между ВРЕМЯ_Н и ВРЕМЯ_К. Если указано значение, отличное от числового(например, текст «45 мин»), то функция вернет значение ошибки — #ЗНАЧ!(#VALUE!)
Текстовые и ошибочные данные игнорируются. Если любой из аргументов ВРЕМЯ_Н или ВРЕМЯ_К содержит значение ошибки или текстовое значение, то рабочие часы для периода не будут просчитаны.

Пример работы с функцией
В ячейке A1 записано время начало рабочего дня — 9:00, а в ячейке B1 записано время окончания рабочего дня — 18:00. Для расчета количества рабочих часов функцию необходимо записать следующим образом:
=РАБ_ЧАС(A1;B1)
в результате получим число «0,375», что будет равно 9 часам. Чтобы это число отображалось в привычном временном виде(9:00), необходимо выделить ячейку с функцией -правая кнопка мыши —Формат ячеек(Format cell) -вкладка Число(Number) -группа Время(Time). Выбрать там формат «37:30:55». Либо на той же вкладке выбрать группу «(все форматы)» и выбрать формат «[ч]:мм:сс»:

Чтобы исключить из подсчета время, затраченное сотрудником на обед, формулу необходимо записать в таком виде:
=РАБ_ЧАС(A1;B1;60)

Функция так же корректно рассчитает время и для ночных смен. Например, в ячейке A1 записано 21:00, а в ячейке B1 — 9:00. Чтобы рассчитать разницу в часах для таких периодов в Excel стандартно пришлось прибегать к различным трюкам. Функция РАБ_ЧАС не требует каких-либо дополнительных манипуляций — количество рабочих часов будет рассчитываться так же, как и для обычных дневных смен:
=РАБ_ЧАС(A1;B1;60)

Если же необходимо рассчитать не просто количество рабочих часов в день для одного сотрудника, а общее количество рабочих часов для сотрудника за месяц или рабочие часы сотрудников всего отдела — то в функции необходимо будет указать все ячейки начала рабочего дня и все ячейки окончания:
=РАБ_ЧАС(B2:I2;B3:I3;60)

Функция в этом случае рассчитает количество рабочих часов для каждого дня и сложит их в одну сумму

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

Если среди ячеек встречаются ошибочные или текстовые значения — рабочие часы для периодов, содержащих такие значения не будут рассчитаны. Например, если в таблице выше для 5 декабря вместо «9:00» будет записано «9 часов», то пятое декабря будет пропущено и сумма будет не 64:00, а 56:00. Тоже самое будет, если вместо 9:00 будет значение ошибки (#Н/Д, #ЗНАЧ!, #ИМЯ?, #ЧИСЛО!, #ДЕЛ/0!, #ССЫЛКА!, #ПУСТО!, #N/A, #VALUE!, #NAME?, #NUM!, #DIV/0!, #REF!, #NULL!)

Значения могут располагаться как горизонтально(как на картинке выше), так и вертикально:

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

Первый способ: сумма времени

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

1. Отформатируем весь диапазон заполненных данных. Для этого выделите область, в которой указаны отработанные часы, и вызовите меню форматирования. Чтобы это сделать, после выделения необходимо кликнуть правой кнопкой мыши и перейти в пункт «Формат ячеек».

1. Далее откроется окно форматирования. Во вкладке «Число» выберите «Все форматы», затем найдите в списке обозначение «[ч]:мм:сс», нажмите кнопку «ОК», чтобы все изменения были сохранены.

1. Далее посчитаем количество отработанных часов, для этого снова выделяем весь диапазон ячеек с часами и одну пустую ячейку. Затем во вкладке «Главная» находим символ «Автосумма» и нажимаем его. Чтобы ускорить процесс, сделайте нажатие комбинации клавиш «Alt+=».

1. В последней ячейке получается сумма всех часов. Дальнейшие вычисления можно проводить с учетом полученного результата.

Второй способ: определение часов через временной промежуток

Предположим, нам дано время 11 часов 45 минут, требуется узнать, сколько будет по истечении 2 часов 15 минут. Будем действовать по инструкции, чтобы получить результат.

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

1. Активируем пустую ячейку, переходим в строку формул, где устанавливаем знак «=» без кавычек. Нажимаем на ячейку с показателем часов на данный момент, ставим знак «+», добавляем соседнюю ячейку. Нажимаем кнопку «Enter». В итоге получится время, которое будет после определенного промежутка (в нашем случае по истечении 2 часов 15 минут).

Третий способ: пользуемся встроенными функциями

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

1. Активируем пустую ячейку на листе с внесенными числами, затем переходим в строчку формул.

1. Нажимаем на кнопку «Функция», попадаем в окошко, где необходимо «Категорию» сменить на «Полный алфавитный перечень» и отыскать среди всех аргументов значение «Сумм». Затем нажимаем кнопку «ОК».

1. Программа перебрасывает в следующее окошко. В поле «Число 1» нажимаем на ячейку с основным временем, а в поле «Число 2» на ячейку с промежуточным. Подтвердим действия нажатием на кнопку «ОК».

В результате получается число, которое будет по истечении 2-х часов 15 минут. В нашем случае это «14:00».

Если у вас остаются вопросы и непонятки, советуем изучить статью Формулы в Excel, чтобы понять принципы работы формул и легко оперировать ими.

При планировании рабочего времени сотрудников предприятия оперируют такими понятиями, как «календарное время», «табельное», «максимально возможное», «явочное», «фактическое».

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

Составляющие баланса рабочего времени

Данный анализ содержит ожидаемые показатели и фактическое использование рабочего времени. Единицы измерения – человеко-дни / человеко-часы.

Элементы анализа:

• календарный фонд = количеству дней в году;
• номинальный (табельный) = все дни минус выходные и праздники;
• максимально возможный = номинальный минус дни отпуска;
• явочный = максимально возможный минус неявки;
• фактический = явочный минус простои.

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

• факт. время /макс. возможное;
• неявки /табельное или макс. возможное;
• явочн. /макс. возможное.

Таким образом, основная задача баланса:

1. Повышение эффективности использования фонда рабочего времени.
2. Определение плановой численности специалистов.



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

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

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

«Шапка» учебного баланса выглядит следующим образом:

1. Табельные значения берутся из графика работ конкретного специалиста. Например, в декабре 2015 г 31 календарный день. Отнимаем праздники – получаем 28. Сорокачасовая рабочая неделя, пятидневка – 20 рабочих дней или 160 рабочих часов.
2. Количество «отпускных» дней берется из графика отпусков. Если у работника на анализируемый период запланирован отпуск, то для расчета максимально количества рабочих часов из табельных значений отнимаются плановые отпуска.
3. Плановый отпуск сопоставляется с рабочими днями по графику. В таблице отражаются дни и часы, приходящиеся на рабочие смены.
4. Чтобы рассчитать явочное время, необходимо спрогнозировать неявочные дни. Максимально точно это сделать невозможно. Поэтому возьмем средний процент неявок за предыдущий квартал. Полученное значение вычтем из максимально возможного фонда. Результат – плановый явочный фонд.

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

В столбце «Максимально возможное время» расчет производится автоматически. Вводим формулу: = F4 – H4 (табельные значения минус плановый отпуск). Для дней и часов соответственно.

Для расчета не явочных часов нужны средние показатели неявок. Удобнее всего, если учет времени тоже ведется в Excel. Ежемесячно подсчитываются «явки» и «неявки» по каждому сотруднику.

Тогда при составлении баланса на новый период можно в графу «Явочное время» ввести формулу со ссылкой на «Учет раб.вр.» за предыдущие 3 месяца.

Например, такую: =J4-((‘Учет раб.вр. Март’!B3+’Учет раб.вр. Апрель’!B3+’Учет раб.вр. Май’!B3)/3). Или с использованием функции СРЗНАЧ: =K4-(СРЗНАЧ(‘Учет раб.вр. Март’!C3;’Учет раб.вр. Апрель’!C3;’Учет раб.вр. Май’!C3)).

По такому же алгоритму заполняются данные по всем сотрудникам и подразделениями. Формулы копируются на весь столбец.

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

Баланс времени одного среднесписочного рабочего

На одного человека составляют балансы, как правило, на промышленных предприятиях.

Этапы составления:

1. Определение количества рабочих дней. От числа календарных дней отнимаются праздники и выходные, неявки на работу по уважительным причинам, простои (если они заняли целый день).
2. Расчет продолжительности трудового дня. От номинальной продолжительности отнимаются установленные законодательством временных потерь (для кормящих матерей, для работающих в тяжелых условиях и т.д.).
3. Расчет эффективного фонда времени одного среднесписочного рабочего.

Внесем показатели и известные величины в таблицу Excel:

Все остальные числовые значения по показателям найдем с помощью формул:

1. Нерабочие дни (строка 2): праздничные + выходные (=C4+C5).
2. Номинальный фонд (строка 3): календарные дни – нерабочие (=C2-C3).
3. Неявочные дни (строка 4): сумма всех дней, пропущенных по уважительным и неуважительным причинам (=СУММ(C9:C15)).
4. Явочные дни (строка 5): номинальный фонд– неявки (=C6-C7).
5. Временные потери (строка 7): сумма всех «сокращений» трудового дня, связанных с праздниками и льготными условиями труда (=СУММ(C20:C24)).
6. Средняя продолжительность рабочего дня (строка 8): среднеарифметическая величина, взвешенная с учетом установленной государством продолжительности рабочей смены. Формула: =(C17*(C17*C16)+5*C18)/((C17*C16)+C18)).

Разделим по «математическим» действиям:

1. Сначала найдем бюджет рабочего времени: продолжительность смены * явочные дни.
2. При пятидневной рабочей неделе в числителе: установленная продолжительность смены * бюджет раб. час + 5 * временные потери.
3. В знаменателе: сумма бюджета и потерь.

Полезный фонд (строка 9): бюджет рабочего времени (продолжительность раб. дня * явочный фонд) – временные потери. Формула: =(C17*C16)-C18.

Результат расчетов:

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

Расширяем таблицу с нормативным балансом – добавляем столбец с фактически использованным рабочим временем за период и с отклонениями.

Скачать баланс рабочего времени
Скачать табель учета рабочего времени

Вносим фактические значения. Отклонения находим с помощью формул. Анализ готов.

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

Использование функции ЧИСТРАБДНИ

Сначала предлагаю остановиться на простой функции с названием ЧИСТРАБДНИ, синтаксис которой подразумевает подсчет количества рабочих дней в диапазоне дат с учетом стандартных выходных (субботы и воскресенья), а также указанных вручную праздников, если такие есть. Используйте следующую инструкцию, чтобы разобраться с этой задачей.

1. Для начала обратитесь к листу, где ведете учет дат. Обязательно сделайте так, чтобы первая и последняя дата в промежутке были одинакового формата.

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

3. В пустом месте объявите создание формулы при помощи знака = и напишите ЧИСТРАБДНИ.

4. В качестве первого аргумента объявите ячейку, где указано начало даты вычислений.

5. После знака ; укажите конечную дату.

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

7. Если праздники есть, задайте их по очереди как следующие аргументы формулы.

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

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

Использование функции ЧИСТРАБДНИ.МЕЖД

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

1. Вместо описанной выше функции на этот раз объявите ЧИСТРАБДНИ.МЕЖД.

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

3. Выходные указываются как число, где 1 – суббота, воскресенье, 2 – воскресенье, понедельник, 3 – понедельник, вторник и так далее. Об этих числах я еще расскажу позже.

4. Праздники обозначаются точно так же, как и в предыдущем случае.

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

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

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