Power Queryで勤怠CSVを一括集計する方法｜日跨ぎ勤務にも対応

先に結論：勤怠集計は正常行と例外行を分けて作る

勤怠CSVを1つの表へ結合するだけでは、安全な集計になりません。正常に計算できる明細と、打刻漏れ、社員未登録、異常な勤務時間を分け、例外を人が確認できる状態にします。

社員番号は識別子としてテキスト型、勤務日は日付型、出勤・退勤は時刻または日時型にそろえます。日を跨ぐ勤務は退勤が出勤より小さい場合に翌日を加えるなど、ルールを明示します。

手作業集計とPower Query前処理では、エラーの扱いがどう変わるかを見てください。

完成形は、全社勤怠明細、例外一覧、更新チェックの3つです。例外を削除して見えなくするのではなく、給与や人事の担当者が確認できる形で残します。

勤怠CSVを取り込む前に決めること

1行の粒度と必須列を定義する

基本の粒度は『社員1人・勤務日1日につき1行』です。複数回の入退室や中抜けを別行で出すシステムでは、先に打刻イベントを勤務日単位へまとめる設計が必要です。

最低限、社員番号、勤務日、出勤、退勤、休憩、勤務区分、拠点を確認します。CSVごとに列名が違う場合は標準列へ名前をそろえ、欠落列を例外として検出します。

社員番号は数値にすると先頭ゼロが消えるため、最初からテキスト型にします。勤務日と時刻を文字列のまま計算すると、地域設定や表記差で変換エラーが起こりやすくなります。

元CSVを直接上書きしない

受領したCSVは原本フォルダーへ保存し、Power Queryは読み取り専用の入力として扱います。手修正した値が必要なら、修正理由と元値を持つ訂正表を別に作り、クエリで適用します。

ファイル名には対象月と拠点を含めます。ファイル名から拠点列を作る場合、命名規則がデータの一部になるため、途中の空白や表記を統一します。

複数拠点の勤怠ファイルが1つの明細になる流れです。

各行へ元ファイル名を残しておくと、打刻漏れや社員未登録を発見した際に、どの拠点・月の原本へ戻るか判断できます。

Power Queryで勤怠CSVを集計する7ステップ

入力から例外分離までの流れを先に確認します。

各ステップの途中で行数を記録すると、どこで行が除外・増加したかを追跡できます。勤怠では、エラー行を無条件に削除しないことが特に重要です。

手順1：勤怠CSV用フォルダーを指定する

データの取得からフォルダーを選び、月別・拠点別CSVが入る専用フォルダーを指定します。隠しファイルと一時ファイルを除外します。 ここで大切なのは、画面上で一度だけ正しい結果を作ることではなく、翌月の更新でも同じ条件が再現されるように、操作を適用したステップとして残すことです。

対象外のバックアップや再出力ファイルが混ざると、勤務日数や時間が二重計上されます。 Power Queryは上から順に処理を実行するため、列名やデータ型を整える位置が後続処理の安定性を左右します。目的が分かるステップ名へ変更しておくと、引き継ぎ時にも判断しやすくなります。

ファイル数、対象月、拠点の一覧を受領管理表と照合します。 更新後はプレビューの数行だけで判断せず、行数、null件数、合計値など、変換前に決めた基準と照合してください。

手順2：列名とデータ型をそろえる

社員コードを社員番号、出社を出勤など標準名へ変更し、社員番号はテキスト、勤務日は日付、時刻は時刻型へ変換します。 ここで大切なのは、画面上で一度だけ正しい結果を作ることではなく、翌月の更新でも同じ条件が再現されるように、操作を適用したステップとして残すことです。

ファイルごとの列名・型の差をサンプル変換で吸収すると、同じ関数を全CSVへ適用できます。 Power Queryは上から順に処理を実行するため、列名やデータ型を整える位置が後続処理の安定性を左右します。目的が分かるステップ名へ変更しておくと、引き継ぎ時にも判断しやすくなります。

型変換エラーだけを抽出し、日付表記や時刻表記の例外を確認します。 更新後はプレビューの数行だけで判断せず、行数、null件数、合計値など、変換前に決めた基準と照合してください。

手順3：ファイル名から対象月と拠点を付ける

ソース名列を残し、区切り記号で分割して対象月と拠点を作ります。元ファイル名も監査用に保持します。 ここで大切なのは、画面上で一度だけ正しい結果を作ることではなく、翌月の更新でも同じ条件が再現されるように、操作を適用したステップとして残すことです。

明細内に拠点列がないCSVでも出所を追跡でき、エラーを元ファイルへ戻して確認できます。 Power Queryは上から順に処理を実行するため、列名やデータ型を整える位置が後続処理の安定性を左右します。目的が分かるステップ名へ変更しておくと、引き継ぎ時にも判断しやすくなります。

ファイル名から取得した対象月と勤務日の月が矛盾していないか確認します。 更新後はプレビューの数行だけで判断せず、行数、null件数、合計値など、変換前に決めた基準と照合してください。

手順4：社員マスターを左外部結合する

社員番号の空白と型をそろえ、社員マスターから氏名、部署、雇用区分を展開します。 ここで大切なのは、画面上で一度だけ正しい結果を作ることではなく、翌月の更新でも同じ条件が再現されるように、操作を適用したステップとして残すことです。

氏名で結合すると同姓同名や表記変更の影響を受けるため、安定した社員番号をキーにします。 Power Queryは上から順に処理を実行するため、列名やデータ型を整える位置が後続処理の安定性を左右します。目的が分かるステップ名へ変更しておくと、引き継ぎ時にも判断しやすくなります。

未一致社員、マスター重複、結合前後の行数を確認します。 更新後はプレビューの数行だけで判断せず、行数、null件数、合計値など、変換前に決めた基準と照合してください。

手順5：日跨ぎを考慮して勤務時間を計算する

勤務日と出勤・退勤から日時を作り、退勤が出勤より前なら退勤日時へ1日を加えます。そこから休憩時間を差し引きます。 ここで大切なのは、画面上で一度だけ正しい結果を作ることではなく、翌月の更新でも同じ条件が再現されるように、操作を適用したステップとして残すことです。

時刻だけを単純に引くと、22時出勤・翌6時退勤が負の時間になります。 Power Queryは上から順に処理を実行するため、列名やデータ型を整える位置が後続処理の安定性を左右します。目的が分かるステップ名へ変更しておくと、引き継ぎ時にも判断しやすくなります。

通常勤務、日跨ぎ、退勤空欄のテスト行で結果を照合します。 更新後はプレビューの数行だけで判断せず、行数、null件数、合計値など、変換前に決めた基準と照合してください。

手順6：打刻漏れと異常値を分類する

出勤空欄、退勤空欄、社員未登録、勤務時間が負、上限超過などを条件列で分類します。 ここで大切なのは、画面上で一度だけ正しい結果を作ることではなく、翌月の更新でも同じ条件が再現されるように、操作を適用したステップとして残すことです。

すべてを1つのエラーにすると対応者が原因を調べ直すため、修正方法につながる分類名を付けます。 Power Queryは上から順に処理を実行するため、列名やデータ型を整える位置が後続処理の安定性を左右します。目的が分かるステップ名へ変更しておくと、引き継ぎ時にも判断しやすくなります。

各例外の件数と代表行を確認し、正常行に例外が付いていないかも確認します。 更新後はプレビューの数行だけで判断せず、行数、null件数、合計値など、変換前に決めた基準と照合してください。

手順7：正常明細と例外一覧を分けて出力する

元クエリを参照し、正常フラグだけの勤怠明細と、例外フラグがある確認一覧を作ります。 ここで大切なのは、画面上で一度だけ正しい結果を作ることではなく、翌月の更新でも同じ条件が再現されるように、操作を適用したステップとして残すことです。

例外を除外して集計値だけ出すと、未処理の勤務が見えなくなります。 Power Queryは上から順に処理を実行するため、列名やデータ型を整える位置が後続処理の安定性を左右します。目的が分かるステップ名へ変更しておくと、引き継ぎ時にも判断しやすくなります。

元行数が正常件数と例外件数の合計に一致することを確認します。 更新後はプレビューの数行だけで判断せず、行数、null件数、合計値など、変換前に決めた基準と照合してください。

日跨ぎ勤務と休憩時間を計算する考え方

Power Queryでは日時の差が期間として返ります。給与計算用の丸めや深夜区分を作る前に、まず原打刻から経過時間を一貫して計算します。

日跨ぎを含む勤務時間の例

退勤日時が出勤日時より前なら翌日を加え、休憩分を差し引く考え方です。列名は実際のCSVに合わせて変更します。

let
    出勤日時 = DateTime.From([勤務日]) + Duration.From([出勤]),
    退勤候補 = DateTime.From([勤務日]) + Duration.From([退勤]),
    退勤日時 = if 退勤候補 < 出勤日時 then 退勤候補 + #duration(1,0,0,0) else 退勤候補,
    実働 = 退勤日時 - 出勤日時 - [休憩時間]
in
    実働

空欄打刻を先に判定しないと計算エラーになるため、実際のクエリではnullチェックを加えます。24時間を超える勤務や複数日にまたがる勤務は別ルールで扱います。

退勤が翌日の場合は、時刻だけでなく勤務日と組み合わせます。

23時から翌朝7時の勤務は、時刻だけを引くと負になります。日付と時刻を結合した日時で経過時間を計算します。

勤怠集計の検算ポイント

元CSVの行数を、正常明細と例外一覧の合計へ一致させます。社員単位、拠点単位、対象月単位の件数も確認し、特定ファイルだけ欠けていないかを見ます。

勤務時間合計だけでなく、勤務日数、打刻漏れ人数、社員未一致人数を前月と比較します。繁忙期などで増減する場合も、理由を説明できる状態にします。

更新後に確認する項目を6つへまとめます。

例外件数が0であることだけを成功条件にすると、例外行を誤って削除しても気付けません。元行数との収支と、例外の内訳を同時に確認します。

給与システムへ渡す前に、担当者が例外を修正・承認する締め時点を決めます。更新のたびに値が変わる作業表と、承認後の確定データを分けて保存します。

勤怠CSVでよくあるエラーと対処法

社員番号の先頭ゼロが消える

00125が125になり、社員マスターと一致しません。 この状態で更新を続けると、見た目は完成していても集計軸が分裂したり、明細が欠落したりするため、結果だけを手直ししてはいけません。

CSVの自動判定や型変換で数値型になっています。 原因を特定するときは、エラーが見えた最終ステップから戻るのではなく、値が初めて想定外になったステップを探します。

取り込み直後に社員番号をテキスト型へ設定し、必要なら桁数ルールでゼロ埋めします。 修正ルールは元データを直接書き換えるのではなく、クエリの変換または管理可能なマスター表として残します。

マスター未一致件数と社員番号の文字数分布を確認します。 修正後は正常行だけでなく、例外行が0件になったか、または意図した監査表へ分離されたかまで確認します。

退勤時刻が負の勤務時間になる

夜勤の勤務時間がマイナスまたは異常値になります。 この状態で更新を続けると、見た目は完成していても集計軸が分裂したり、明細が欠落したりするため、結果だけを手直ししてはいけません。

勤務日と時刻を分離したまま単純減算しています。 原因を特定するときは、エラーが見えた最終ステップから戻るのではなく、値が初めて想定外になったステップを探します。

出勤・退勤の日時を作り、退勤が前なら翌日を加えるルールを適用します。 修正ルールは元データを直接書き換えるのではなく、クエリの変換または管理可能なマスター表として残します。

日跨ぎテスト行の経過時間を手計算と照合します。 修正後は正常行だけでなく、例外行が0件になったか、または意図した監査表へ分離されたかまで確認します。

CSVによって列がずれる

一部拠点だけ部署や休憩の値が別列へ入ります。 この状態で更新を続けると、見た目は完成していても集計軸が分裂したり、明細が欠落したりするため、結果だけを手直ししてはいけません。

列順を前提に結合し、列名や区切り文字の差を見逃しています。 原因を特定するときは、エラーが見えた最終ステップから戻るのではなく、値が初めて想定外になったステップを探します。

列名で標準化し、必須列の有無を確認してから追加します。 修正ルールは元データを直接書き換えるのではなく、クエリの変換または管理可能なマスター表として残します。

拠点別にnull率と型エラーを比較します。 修正後は正常行だけでなく、例外行が0件になったか、または意図した監査表へ分離されたかまで確認します。

同じ勤務が二重に入る

社員の勤務日数や総時間が約2倍になります。 この状態で更新を続けると、見た目は完成していても集計軸が分裂したり、明細が欠落したりするため、結果だけを手直ししてはいけません。

再出力ファイルや修正版と旧版が同じフォルダーにあります。 原因を特定するときは、エラーが見えた最終ステップから戻るのではなく、値が初めて想定外になったステップを探します。

ファイル版管理を決め、社員番号・勤務日・勤務区分などの複合キーで重複を監査します。 修正ルールは元データを直接書き換えるのではなく、クエリの変換または管理可能なマスター表として残します。

重複キー一覧が0件で、受領ファイル数と一致することを確認します。 修正後は正常行だけでなく、例外行が0件になったか、または意図した監査表へ分離されたかまで確認します。

統合後の行を削除せず、状態別の出力へ流します。

例外行を単に除外すると勤務時間が欠落します。訂正と承認が完了するまで、件数と影響時間を例外一覧で管理します。

Power Queryで計算する範囲を決める

Power Queryは原打刻の整形、日跨ぎの経過時間、マスター付与、例外抽出に向きます。一方、法定内・法定外、深夜、休日、端数処理などの給与ルールは複雑で改定もあるため、給与システムとの役割分担を明確にします。

分析目的の残業傾向と、給与支給額を決める計算では必要な精度と承認が違います。Power Queryの結果を参考値とするのか、正式連携データとするのかを文書化します。

個人情報を含むため、保存場所、閲覧権限、出力列を最小限にします。ブログや手順書の画面例では、実在の氏名、社員番号、ファイルパスを使用しません。

実務で継続運用するためのポイント

クエリ名とステップ名を引き継げる形にする

入力、中間処理、完成データ、監査データの役割が名前で分かるようにします。自動生成された変更された型やカスタム1だけが並ぶ場合は、業務上の目的を表す名前へ変更すると、更新エラーの確認が速くなります。

元データの列名、型、保存場所など、クエリが前提とする条件を簡潔に記録します。操作手順を長く書くより、変えてよい入力と変えてはいけない契約を明示する方が、担当者変更やファイル改訂へ対応しやすくなります。

変更はテスト用ファイルで確認してから本番へ反映する

列追加、命名規則、マスター改訂などの変更があるときは、本番更新の前にコピーしたテスト環境で実行します。正常な既存ファイルと変更後ファイルを同時に試し、過去分を壊さず新形式も処理できるかを確認します。

変更前後の行数、主要合計、未一致、エラー件数を比較し、意図した差だけが生じたことを記録します。見た目が同じでもデータ型やnullの扱いが変わる場合があるため、監査値を変更承認の根拠にします。

更新履歴と復旧方法を残す

更新日、対象期間、採用ファイル、実行者、確認結果を残しておくと、後から数値が変わった理由を追跡できます。エラー時に前回確定版へ戻す方法、元ファイルを再取得する窓口、変換表やマスターの管理者も決めておきます。

自動化の品質は、正常時の速さだけでなく、異常を早く見つけて安全に復旧できるかで決まります。監査クエリを非表示にせず、担当者が更新のたびに確認する標準手順へ組み込みます。

よくある質問

休憩時間が『1:00』と『60』で混在する場合は？

単位を先に定義します。時刻文字列、期間、分数を別々に判定し、最終的に期間または分へ統一します。変換できない値は0へ置換せず例外へ分離します。

複数回の出退勤がある日はどうしますか？

打刻イベントのまま取り込み、社員番号・勤務日でグループ化して最初の入場と最後の退場を取るだけでは中抜けを失う場合があります。勤務区分や打刻種別に基づく会社ルールが必要です。

勤怠CSVを上書き修正してもよいですか？

原本は保持し、訂正表または勤怠システム側で修正します。Power Queryの出力を直接直すと次回更新で消えるため、修正経路を明確にしてください。

UdemyでPower Queryをさらに学びたい方へ

勤怠CSVのような複数ファイルの集計は、フォルダー取り込み、型変換、結合、例外処理をまとめて練習すると身につきやすくなります。

Udemyは動画の再生速度を調整でき、必要な箇所を繰り返し確認できます。価格は講座やセール時期によって変わるため、申込み前に講座ページの内容、対応環境、最新価格をご確認ください。

※本記事にはアフィリエイトリンクが含まれます。リンク経由で申込みがあった場合、運営者に報酬が発生することがあります。

まとめ：例外を見える化した勤怠更新にする

Power Queryでは、拠点別・月別の勤怠CSVをフォルダーから一括取得し、社員番号、勤務日、時刻、休憩の型をそろえて全社明細へ統合できます。

日跨ぎ勤務は退勤日時が出勤より前なら翌日を加えるなど、明示したルールで計算します。打刻漏れや社員未登録を削除せず、正常明細と例外一覧へ分けます。

更新後は、受領ファイル、元行数、正常・例外の収支、社員未一致、異常時間、総勤務時間を検算します。給与や法令判断との境界を決め、原本と承認履歴を残すことが安全な運用につながります。