Как рассчитать рабочие часы в месяц в Excel?
Во многих организациях сотрудники следуют стандартному рабочему графику, например, 8 часов в день и 5 дней в неделю. Точный расчет общего количества рабочих часов в месяц имеет важное значение для обработки заработной платы, отслеживания проектов и управления рабочей силой. Однако ручной подсчет общего количества часов в каждом месяце может быть подвержен ошибкам и занимает много времени, особенно если нужно учитывать выходные дни, государственные праздники или сотрудников с различными графиками. Excel предоставляет практические инструменты и методы, которые помогут вам оптимизировать и автоматизировать этот расчет, обеспечивая точность и эффективность ваших операций.
Ниже приведено несколько эффективных решений для расчета ежемесячных рабочих часов в Excel, подходящих для различных структур данных и потребностей:
Расчет общего количества рабочих часов в месяц с помощью формул
Код VBA – Автоматический расчет ежемесячных рабочих часов для нескольких строк
Расчет общего количества рабочих часов в месяц с помощью формул
Формульные функции Excel предоставляют простой и надежный подход для расчета общего количества рабочих часов в месяц, когда известны начальная и конечная даты, а также ежедневная квота рабочих часов. Этот метод лучше всего работает, когда каждый день имеет фиксированный рабочий график, и вы хотите учесть выходные дни и, при необходимости, праздники. Вот как можно настроить ваш расчет:
1. Выберите пустую ячейку, в которой вы хотите отобразить результат, и введите следующую формулу:
=NETWORKDAYS(A2,B2) *8После ввода формулы нажмите клавишу Enter. Изначально результат может быть отформатирован как дата. На скриншоте ниже это продемонстрировано:
2. Чтобы отобразить значение в виде числа (рабочих часов), выберите ячейку с результатом формулы. Затем на вкладке Главная найдите выпадающий список Формат Числа и выберите Общий. Теперь ячейка будет отображать общее количество рабочих часов как обычное число, как показано:
Совет: Чтобы точно отразить периоды, включающие праздники, вы можете исключить эти дни, добавив диапазон праздников в вашу формулу. Используйте:
=NETWORKDAYS(A2,B2, C2:C4) *8где A2 – это начальная дата, B2 – это конечная дата, а C2:C4 перечисляет праздничные даты, которые вы хотите исключить. Отформатируйте ячейку как Общую, как описано выше.
Примечание: Измените ссылки на ячейки (например, A2, B2, C2:C4) в соответствии с расположением ваших данных. Этот метод лучше всего подходит для стандартного количества рабочих часов в день. Если сотрудники работают неполный рабочий день или количество рабочих часов варьируется, вам может понадобиться адаптировать формулу или использовать один из альтернативных методов ниже.
Одним из преимуществ использования формул является то, что после настройки они автоматически обновляются, когда вы изменяете даты или праздники. Однако для наборов данных, содержащих несколько сотрудников или более сложные записи посещаемости, вы можете найти следующие продвинутые решения более удобными.
Код VBA – Автоматический расчет ежемесячных рабочих часов для нескольких строк
Если ваша таблица записывает несколько сотрудников или строк — каждая с разными периодами или различными начальными и конечными датами — ручной ввод формул построчно становится утомительным. Использование макроса VBA позволяет автоматически вычислить рабочие часы для каждой строки массово, экономя время и снижая ручные ошибки.
Это решение особенно полезно, когда у вас есть таблица, где каждая строка представляет сотрудника или отдельный рабочий период, с столбцами для Даты Начала, Даты Окончания и, при необходимости, Праздников. Макрос ниже пройдет через все строки и заполнит рассчитанные рабочие часы для каждой записи.
1. Откройте редактор Visual Basic for Applications, нажав Разработчик > Visual Basic. В новом окне нажмите Вставить > Модуль и вставьте следующий код VBA в область кода:
Sub CalculateMonthlyWorkingHours()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim startCol As String, endCol As String, holidayCol As String, resultCol As String
Dim holidays As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Adjust these column letters as needed
startCol = "A" ' Start Date in Column A
endCol = "B" ' End Date in Column B
holidayCol = "C" ' Holiday list in Column C (optional; can be blank)
resultCol = "D" ' Results in Column D
lastRow = ws.Cells(ws.Rows.Count, startCol).End(xlUp).Row
For i = 2 To lastRow
If ws.Cells(i, startCol).Value <> "" And ws.Cells(i, endCol).Value <> "" Then
If ws.Cells(i, holidayCol).Value <> "" Then
holidays = Split(ws.Cells(i, holidayCol), ",")
ws.Cells(i, resultCol).Value = WorksheetFunction.NetworkDays(ws.Cells(i, startCol), ws.Cells(i, endCol), holidays) * 8
Else
ws.Cells(i, resultCol).Value = WorksheetFunction.NetworkDays(ws.Cells(i, startCol), ws.Cells(i, endCol)) * 8
End If
Else
ws.Cells(i, resultCol).Value = ""
End If
Next i
End SubЗаметки и инструкции:
- Настройте буквы столбцов (startCol, endCol, holidayCol, resultCol) по мере необходимости, чтобы они соответствовали макету вашего листа.
- Столбец holidayCol должен содержать даты, разделенные запятыми, например, 7/3/2025,7/18/2025. Если он не нужен, вы можете оставить его пустым.
- Этот макрос предполагает, что данные начинаются со строки 2 (после заголовка).
2. Для запуска макроса нажмите кнопку 
или нажмите F5 при выборе модуля. Код автоматически рассчитает и заполнит общее количество рабочих часов в месяц для всех строк в столбце результатов. Если вы столкнетесь с ошибкой, проверьте правильность ссылок на данные и форматы дат.
Этот метод особенно эффективен для автоматизации расчетов по большим спискам сотрудников или многостраничным данным табеля учета рабочего времени, обеспечивая согласованность и снижение риска пропущенных записей. Если ваша структура данных более гибкая или вы хотите свести ежедневные журналы посещаемости, рассмотрите возможность использования сводной таблицы, как описано ниже.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек