【業務自動化】Chapter3-4：残業集計ツールを作ろう
一つ前のページでは グラフ付きレポート作成ツール を作成しました。
今回は 残業集計自動化ツール を作成していきましょう。
Chapter１：PythonによるExcel自動化の基本を身につけよう
Chapter２：自動化ツールを.exe化して配布しよう
Chapter３：現場で使える便利な自動化ツールを4つ作ろう
・Chapter3-1：Excelファイルをフォルダ丸ごと結合しよう
・Chapter3-2：テンプレートを使った請求書自動作成
・Chapter3-3：グラフ付き売上レポート自動生成ツールを作ろう
・Chapter3-4：勤怠の丸め・残業集計自動化ツールを作ろう ◁今回はここ
Chapter４：Webスクレイピングでデータを自動で集めよう
Chapter５：自動化ツールを作ってお金を稼ごう
Excelで管理している勤怠データを、手作業で集計するのはとても大変です。
毎月の勤務時間や残業時間を電卓で計算したり、SUM関数で集計したりしていると、時間がかかるだけでなくミスも起きやすくなります。
この記事では Pythonとopenpyxlを使って勤怠データを自動で丸め・集計するツール を作ります。
作成したツールを使えば、社員ごとの残業時間や平均勤務時間を一瞬で可視化できるようになり、手作業による負担を大幅に減らせます。
<<前のページ
業務自動化の記事一覧
次のページ>>
Python×openpyxlでExcel勤怠を自動化｜丸め処理と残業集計の実装ガイド
この記事で作成するツールでは、複数の社員の勤怠データ（出勤・退勤時刻、休憩時間）をまとめて処理し、次のような自動化を実現します。
- 時間データを15分単位に丸め、勤怠管理用のデータを作成
- 社員ごとの勤務時間・残業時間を計算し、月ごと/年間のサマリーをExcelにまとめる
- グラフを自動で生成し、視覚的に残業傾向を把握できる仕組み作り
このツールで実現できること｜丸め単位設定・社員別/月次集計・グラフ自動作成
今回作成するツールは、社員ごとの勤怠データをまとめて処理し、次のようなことを自動で行えるようになります。
まず、勤怠表から「出勤時刻」「退勤時刻」「休憩時間」を読み取り、勤務時間を計算します。
その際、1日の労働時間を15分単位で切り下げて集計する仕組みを取り入れています。これは実際の勤怠管理システムでもよく使われる方法で、より現場に即した処理が可能です。
さらに、1日8時間を超えた分を残業時間として自動計算し、以下のように整理します。
- 総勤務時間：社員が働いた時間の合計
- 総残業時間：8時間を超えた部分の合計
- 稼働日数：勤務日数（休日は除外）
- 平均勤務時間：稼働日あたりの勤務時間の平均
- 月ごとの勤務時間・残業時間：1月〜12月の集計結果
最後に、これらの結果をExcelファイルとしてまとめ、社員ごとの残業時間を比較できるグラフも自動で生成します。
グラフには全体平均を示すラインも追加されるため、誰が残業を多くしているのか、全体の傾向はどうなのかをひと目で把握できます。
サンプル勤怠データの作り方｜テスト用Excelを自動生成
本格的にツールを試すには、まず集計対象となる勤怠データが必要ですので、テスト用サンプル勤怠データを自動生成する仕組みを用意しました。
このスクリプトを実行すると、あらかじめ用意された複数の従業員名（例：従業員A〜E）ごとにExcelファイルを作成し、1月から6月までの勤怠データをランダムに生成してくれます。
これにより、実際の業務に近い勤怠データを再現しながら、自動化ツールのテストが可能になります。
特に「残業が日によって増減する」「社員によって残業時間にばらつきが出る」といった現実的な状況を再現できる点がポイントです。
import openpyxl, pathlib
from datetime import datetime, timedelta
import random
import calendar
# === 出力先フォルダ ===
base_dir = pathlib.Path(__file__).parent
input_dir = base_dir / "input"
input_dir.mkdir(exist_ok=True)
# === サンプル従業員（5人） ===
employees = ["従業員A", "従業員B", "従業員C", "従業員D", "従業員E"]
# === 勤怠データ生成関数 ===
def generate_attendance(sheet, year, month, employee_name):
# ヘッダー
sheet.append(["氏名", "日付", "出勤時刻", "退勤時刻", "休憩時間（分）"])
# 1〜6月はデータを生成、それ以降は空欄
if month > 6:
return
# 月の日数を取得
days_in_month = calendar.monthrange(year, month)[1]
for day in range(1, days_in_month + 1):
date_obj = datetime(year, month, day)
# 土日を除外
if date_obj.weekday() >= 5:
continue
# 出勤時間 9:00〜9:30
start_time = datetime(year, month, day, 9, 0) + timedelta(minutes=random.randint(0, 30))
# 退勤時間 18:00〜20:00
end_time = datetime(year, month, day, 18, 0) + timedelta(minutes=random.randint(0, 120))
rest = 60
sheet.append([
employee_name,
date_obj.strftime("%Y-%m-%d"),
start_time.strftime("%H:%M"),
end_time.strftime("%H:%M"),
rest
])
# === 従業員ごとにブックを作成 ===
year = 2025
for name in employees:
book = openpyxl.Workbook()
# 最初のシートを削除してから作り直す
book.remove(book.active)
for month in range(1, 13):
sheet_name = f"{year}-{month:02d}"
sheet = book.create_sheet(title=sheet_name)
generate_attendance(sheet, year, month, name)
# 保存
output_file = input_dir / f"{name}_attendance.xlsx"
book.save(output_file)
print(f"{output_file} を作成しました")サンプルスクリプト
ここまでで勤怠データを生成する準備ができました。
次はいよいよ、勤怠データを読み込み、丸め処理や残業時間の集計を行うスクリプトを紹介します。
このスクリプトを実行すると、input フォルダにある勤怠ファイル（*_attendance.xlsx）をすべて読み込み、まとめた結果を output フォルダに出力します。
内部処理としては、出勤時刻と退勤時刻をもとに労働時間を計算し、休憩時間を差し引いた後、15分単位で切り下げる丸め処理を行います。
そこから1日8時間を超えた部分を残業時間として記録します。
これにより、「誰がどの月にどれくらい残業しているか」を一目で確認できるレポートが自動で完成します。
Excelでの手作業が不要になるだけでなく、グラフによって傾向を直感的に把握できる点が大きなメリットです。
import openpyxl, pathlib, sys, datetime, math
from tkinter import messagebox
from openpyxl.chart import BarChart, LineChart, Reference
# === 実行環境に応じた基準ディレクトリ ===
if getattr(sys, 'frozen', False):
BASE_DIR = pathlib.Path(sys.executable).parent
else:
BASE_DIR = pathlib.Path(__file__).parent
input_dir = BASE_DIR / "input"
output_dir = BASE_DIR / "output"
output_dir.mkdir(exist_ok=True)
attendance_files = list(input_dir.glob("*_attendance.xlsx"))
if not attendance_files:
messagebox.showerror("エラー", "勤怠ファイルが見つかりません (input/*_attendance.xlsx)")
sys.exit(1)
# === 出力用ブック ===
summary_book = openpyxl.Workbook()
summary_sheet = summary_book.active
summary_sheet.title = "all_summary"
# === ヘッダー作成（all_summary 用） ===
header = ["氏名", "総勤務時間(h)", "総残業時間(h)", "稼働日数", "平均勤務時間(h)"]
for m in range(1, 13):
header.append(f"{m}月勤務時間(h)")
header.append(f"{m}月残業(h)")
summary_sheet.append(header)
# === ユーティリティ ===
def parse_time(tstr):
if not tstr:
return None
try:
return datetime.datetime.strptime(str(tstr), "%H:%M").time()
except:
return None
def round_minutes(minutes, unit=15):
return math.floor(minutes / unit) * unit
# === 集計用データ構造 ===
all_data = {} # {社員名: {total_hours, total_overtime, work_days, monthly_hours[], monthly_overtime[]}}
# === ファイル処理 ===
for attendance_file in attendance_files:
employee_name = attendance_file.stem.split("_attendance")[0]
book = openpyxl.load_workbook(attendance_file)
total_hours = 0
total_overtime = 0
work_days = 0
monthly_hours = [0] * 12
monthly_overtime = [0] * 12
for sheet_name in book.sheetnames:
try:
month = int(sheet_name.split("-")[1])
except:
continue
sheet = book[sheet_name]
for row in sheet.iter_rows(min_row=2):
if not row[0].value:
continue
start = parse_time(row[2].value)
end = parse_time(row[3].value)
rest = row[4].value if row[4].value else 0
if not start or not end:
continue
if end <= start:
continue
start_dt = datetime.datetime.combine(datetime.date.today(), start)
end_dt = datetime.datetime.combine(datetime.date.today(), end)
work_minutes = (end_dt - start_dt).seconds / 60 - rest
work_minutes = round_minutes(work_minutes, 15)
work_hours = work_minutes / 60
overtime = max(0, work_hours - 8)
total_hours += work_hours
total_overtime += overtime
monthly_hours[month - 1] += work_hours
monthly_overtime[month - 1] += overtime
work_days += 1
all_data[employee_name] = {
"total_hours": total_hours,
"total_overtime": total_overtime,
"work_days": work_days,
"avg_hours": total_hours / work_days if work_days > 0 else 0,
"monthly_hours": monthly_hours,
"monthly_overtime": monthly_overtime,
}
# === all_summary 出力 ===
for name, vals in all_data.items():
row_data = [
name,
vals["total_hours"],
vals["total_overtime"],
vals["work_days"],
vals["avg_hours"],
]
for m in range(12):
row_data.append(vals["monthly_hours"][m])
row_data.append(vals["monthly_overtime"][m])
summary_sheet.append(row_data)
# === グラフ作成関数 ===
def add_overtime_chart(sheet, data_col, title, pos="A8"):
max_row = sheet.max_row
bar_chart = BarChart()
bar_chart.title = title
bar_chart.x_axis.title = "社員"
bar_chart.y_axis.title = "残業時間(h)"
cats = Reference(sheet, min_col=1, min_row=2, max_row=max_row)
data_overtime = Reference(sheet, min_col=data_col, min_row=1, max_row=max_row)
bar_chart.add_data(data_overtime, titles_from_data=True)
bar_chart.set_categories(cats)
# 平均残業時間を横線で追加
overtime_values = [sheet.cell(row=r, column=data_col).value for r in range(2, max_row + 1)]
if overtime_values:
avg_overtime = sum(overtime_values) / len(overtime_values)
avg_col = data_col + 20 # 空き列を利用
sheet.cell(row=1, column=avg_col, value="平均残業時間")
for r in range(2, max_row + 1):
sheet.cell(row=r, column=avg_col, value=avg_overtime)
line_chart = LineChart()
data_avg = Reference(sheet, min_col=avg_col, min_row=1, max_row=max_row)
line_chart.add_data(data_avg, titles_from_data=True)
bar_chart += line_chart
sheet.add_chart(bar_chart, pos)
# === all_summary グラフ ===
add_overtime_chart(summary_sheet, data_col=3, title="社員ごとの残業時間と全体平均", pos="A8")
# === 月ごとのシート出力 ===
for m in range(1, 13):
sheet = summary_book.create_sheet(title=f"{m}月")
sheet.append(["氏名", f"{m}月勤務時間(h)", f"{m}月残業(h)"])
for name, vals in all_data.items():
sheet.append([name, vals["monthly_hours"][m - 1], vals["monthly_overtime"][m - 1]])
add_overtime_chart(sheet, data_col=3, title=f"{m}月 社員ごとの残業時間と平均", pos="A8")
# === 保存 ===
output_file = output_dir / "summary_2025.xlsx"
summary_book.save(output_file)
messagebox.showinfo("完了", f"勤怠サマリーを作成しました！\n保存先: {output_file}")Chapter2-1 で解説した、PyInstallerを用いたツール化（exe化）も忘れずに実行しましょう。
まとめ
この記事では、Pythonとopenpyxlを使って勤怠データを自動的に丸め・集計するツールを作成しました。
この仕組みを使えば、毎月の勤怠集計にかかっていた手作業を大幅に削減できるだけでなく、数値をグラフでわかりやすく確認できるようになります。
この仕組みを応用して、より複雑な集計や差戻し候補の可視化などに発展させていくことも可能です。
「勤怠の丸め・残業集計」は、Excel業務自動化の中でも特に実務で役立つ題材です。
ぜひこの記事を参考に、自分の職場や学習環境に合わせて活用してみてください。
<<前のページ
業務自動化の記事一覧
次のページ>>