Excel従業員休暇管理テンプレートで月跨ぎ日数を正しく集計する方法

エクセルで従業員休暇を管理していると、月をまたぐ休暇の集計が思うようにいかず、手作業で集計し直している方も多いのではないでしょうか。そこで今回は、無料テンプレートの「Employee Leave Tracker」を使いつつ、月跨ぎ休暇を正確に計算するためのテクニックや、Power Queryを活用した手法をご紹介していきます。快適に休暇管理を行うための手順やコツをお伝えしますので、最後までぜひご覧ください。

Excelの「Employee Leave Tracker」で起こりがちな月跨ぎ問題

「Employee Leave Tracker」（従業員休暇管理テンプレート）は、MicrosoftがExcel 2016以降向けに提供している無料のツールです。従業員ごとの休暇開始日と終了日、休暇タイプなどを記録し、年間休暇数を簡単に可視化できます。ところが、月別の休暇日数を正しくカウントしようとすると、1月末～2月頭にかけて休暇が続くケースなどで合計日数がズレるという問題が起きる場合があります。

これは、単純なSUMIFS関数で「開始日 >= 1月1日」かつ「終了日 < 2月1日」といった条件設定をすると、月末と翌月初日の継続休暇が中途半端に集計されてしまうからです。もし休暇開始日が1月26日、終了日が2月4日の場合、1月分としては1月26日～1月31日の6日分、2月分としては2月1日～2月4日の4日分を割り振りたいところですが、単純な条件式では「終了日が2月1日より前のもの」というフィルターに引っ掛からず、きちんと計算されません。

問題の原因と従来のSUMIFSによる制限

既定のテンプレートでは、年間を通じた集計は「休暇開始日」「休暇終了日」「日数」をベースにして仕組みづくられています。月単位での集計をしようとすると、以下のような式を用いることが一般的です。

=SUMIFS(
   LeaveTracker[Days],
   LeaveTracker[Employee Name], $B$4,
   LeaveTracker[Start Date], ">=" & DATE(2024,1,1),
   LeaveTracker[End Date], "<" & DATE(2024,2,1),
   LeaveTracker[Type of Leave], 'Leave Types'!$B$5
)

しかし、この式の条件では「休暇終了日が1月31日以前」でなければ1月分に含まれないため、1月31日を超えて休暇が続いているケースの部分的な日数が計算から漏れます。こういったロジックの都合上、「開始日～終了日」をまるごと取り込むかどうかしか判断できず、月をまたいでいる区間を自動的に分割して集計することができません。

従業員の実際の休暇状況との乖離

実際には、業務に必要なシフト管理や年次有給休暇の残日数管理などでは、月をまたぐ休暇の部分的な日数も正確に把握しておくことが重要です。例えば、請求や給与計算を月単位で締めている会社であれば、休暇日数の月ごと計算がずれてしまうと、給与処理や有給付与残数の管理に影響を及ぼします。SUMIFSだけでは対処が難しいケースもあるため、月をまたぐときに「日数を分割集計する」仕組みを組み込む必要があるわけです。

月跨ぎの休暇を正確に日数換算する数式アプローチ

日付が交差する期間を正確に計算するには、「該当する期間の開始日と終了日」を巧みに組み合わせる必要があります。特定の月（たとえば1月）に該当する日数は、下記のように論理的に表せます。

1. 1月の開始日：2024年1月1日（計算式で DATE(2024,1,1) 等）
2. 1月の終了日：2024年1月31日（計算式で DATE(2024,1,31) 等）
3. 休暇開始日が1月1日より前であっても、1月の集計には1月1日から数える
4. 休暇終了日が1月31日より後であっても、1月の集計には1月31日まで数える

論理式で表すと、あるセルに「休暇開始日が含まれる日時点」を startOfMonth 、月末を endOfMonth としたとき、

=MAX(0, MIN(休暇終了日, endOfMonth) - MAX(休暇開始日, startOfMonth) + 1)

という形で、該当月の日数を導けます。この部分式を従業員1人1人の休暇データに対して横展開し、月ごとの合計を算出すると、月をまたぐ休暇にも対応した正確な日数を計算できます。

セルでの具体的な計算例

例えば、A列に「開始日」、B列に「終了日」がある場合で、C列に「該当する月（1月分）の休暇日数」を計算するとしましょう。下記のような式をC2セルに入れ、必要行にコピーしていきます。

=IF(
   OR(A2="", B2=""),
   0,
   MAX(0, MIN($B$2, DATE(2024,1,31)) - MAX($A$2, DATE(2024,1,1)) + 1)
)

ここでは例として2024年1月分を想定し、終了日は MIN(B2, 2024/1/31), 開始日は MAX(A2, 2024/1/1) としています。この式で計算された値を合計すれば、その従業員が1月に取得した休暇日数が正しく求められます。同様に、2月分は開始日 DATE(2024,2,1)、終了日 DATE(2024,2,28)（うるう年なら2月29日）を用いるなど、月ごとの計算式を組み合わせるわけです。

表での分割イメージ

例えば、以下のような休暇データ（1月26日～2月4日）があった場合、数式でどのように分割されるかを表で示してみます。

従業員	開始日	終了日	1月分の日数計算	2月分の日数計算
山田	2024/1/26	2024/2/4	=MAX(0, MIN(2024/2/4, 2024/1/31) – MAX(2024/1/26, 2024/1/1) + 1) = 6	=MAX(0, MIN(2024/2/4, 2024/2/28) – MAX(2024/1/26, 2024/2/1) + 1) = 4

このように、1月は26～31日の6日間、2月は1～4日の4日間として分割され、合計10日になります。シンプルなSUMIFSだけではこの切り分けができないため、こうした数式のアプローチが必要になるのです。

Power Queryとピボットテーブルでデータを日次展開する方法

もう一つの有力な方法は、Power Queryを使って休暇データを日単位に展開し、データモデルやピボットテーブルで集計するというアプローチです。日次データとして「従業員名」「日付」「休暇フラグ」を作成してしまえば、月が変わっても日付ごとに1行ずつ記録されるため、月集計や日集計などが自在に行えます。

Power Queryでの日次展開手順

1. 「データ」タブから「データの取得」を選択し、Excelの既存テーブル（LeaveTracker）を読み込む
2. Power Queryエディター上で、開始日から終了日までの日付を生成するカスタム列を作成
3. テーブルを日付ごとに展開（アンピボットの応用、またはリスト生成機能を使う）
4. 「従業員名」「日付」「休暇タイプ」「休暇フラグ（=1など）」の形に整形し、データを読み込み直す

たとえば、Power Queryエディターで「追加の列」→「カスタム列」を用い、下記のようにリストを生成する式を書きます。

List.Dates(
    [Start Date],
    Duration.Days([End Date] - [Start Date]) + 1,
    #duration(1,0,0,0)
)

これによって、休暇開始日から終了日までの連続した日付のリストが作成されます。その後「リストの展開」を行うと、1行あたり1日ごとのデータに変換されます。この結果、すべての日にちに対して「従業員名」「休暇タイプ」などが紐づく形になるわけです。

ピボットテーブルでの月集計

Power Queryで生成した日次データを「接続のみ」でExcel内に取り込み、データモデルに追加します。次にExcelの「挿入」→「ピボットテーブル」から「このデータをデータモデルに追加する」チェックを選択して、新規ワークシートにピボットテーブルを作成します。ピボットテーブルの行ラベルに「日付」を配置し、グループ化機能で「月単位」に集約すると、各従業員の月別の日数が自然に合計される形になります。

月をまたいだ休暇も、各日単位がばらばらに展開されているため、1月分には1月の日付のみ、2月分には2月の日付のみがカウントされ、正しく分割されるのです。

セキュリティ設定の解除とファイルブロックへの対処

ダウンロードしたテンプレートや外部から入手したExcelファイルには、Windowsのセキュリティ機能により「ブロック」がかかっている場合があります。また、マクロやデータ接続を使うファイルの場合、Excelの「保護ビュー」が働いて正しく更新されないこともあります。以下のポイントに気をつけて、Power Queryやピボットテーブルが正常に動作するようにしましょう。

ファイルのブロック解除

1. ダウンロードしたテンプレートファイルを右クリック
2. [プロパティ] → [全般] タブで「ブロックの解除」にチェックを入れる
3. [OK]をクリックしてウィンドウを閉じる

この操作でWindowsの「ダウンロードファイルは安全ではないかもしれない」ブロックを解除できます。解除しないままでは、Power Queryの更新やマクロの実行がうまく動作しないことがあります。

Excelでコンテンツを有効化する

1. Excelを開き、[ファイル] → [情報] 画面を表示
2. セキュリティ警告などが表示されている場合、「編集を有効にする」または「コンテンツの有効化」ボタンを押す
3. マクロやデータ接続など、必要なコンテンツを有効化する

また、[オプション] → [セキュリティセンター] → [セキュリティセンターの設定] → [信頼できる場所] などを活用し、ファイルを保管するフォルダを「信頼済みフォルダ」に設定する方法もあります。これにより、都度の有効化操作が不要になって作業効率が上がります。

休日設定のカスタマイズ

標準のEmployee Leave Trackerテンプレートでは、土日を自動的に除外するロジックや、アメリカの祝日を登録しているケースが多々あります。これを日本国内向けや、24時間365日運用の組織向けにアレンジするには、次のような修正が必要です。

土日の扱いを変更する

• 「土日は休み」として扱わない場合：テンプレート内の式やPower Queryで、土日判定を削除したり、カレンダー表に「営業日フラグ」を立てて制御する
• 「休日とする曜日を変更」する場合：シフト制のカレンダーを作成し、Power Queryの結合機能などで「休日・平日」列を結合させる

独自の祝日・休暇日を設定する

• 会社独自の特別休暇日、創立記念日などを「休日マスタ」としてExcelシートに定義し、Power Queryで結合して「休日フラグ」を付ける
• テンプレート側で「祝日リスト」を参照している場合は、セル参照を変更して自社用の祝日一覧を反映させる

このように、自社のカレンダーに沿った形で休暇データを処理できるように調整することで、月別・年別・週別など、さまざまな粒度での休暇管理が可能になります。

具体的な実装ポイントと注意点

ここでは、テンプレートの基本構造を崩さずに実装を拡張する際に押さえておきたいポイントをまとめます。

1. データシートの整合性を保つ

Employee Leave Trackerには「LeaveTracker」という名前のテーブルが用意されており、従業員名や開始日、終了日、休暇タイプなどが管理されています。新しい列を追加する場合、行の途中にセル結合をしない、余計な空白行を挿入しないなど、テーブルとしての形を崩さないようにしましょう。

2. 月別集計の出力先を追加する

もともとは年次集計がメインに設定されていますが、月次集計のために新しいシートや集計テーブルを作成することをおすすめします。単純に年次集計の隣に月次の式を並べるよりも、専用シートやピボットテーブルを用意しておくほうが、今後のメンテナンスが楽になります。

3. Power Queryの更新タイミングとパフォーマンス

Power Queryはデータ量が増えると更新に時間がかかる場合があります。日次展開してレコード数が数万行レベルになったら、設定の見直しやインデックス列の作成などでパフォーマンスを向上させる工夫が必要です。加えて、変更を加えたら「更新」操作を手動で行わないと結果が反映されない点にも注意が必要です。

4. ダッシュボードとグラフ化

集計結果を管理層や人事担当などが分かりやすく確認できるように、ピボットテーブルをもとにグラフやダッシュボードシートを作るのも有効です。月別・部門別・従業員ごとの休暇実績を棒グラフや折れ線グラフで可視化すれば、会社全体の休暇動向や休暇集中期間が一目で把握できます。

まとめ：自社ルールに合わせてフレキシブルに休暇管理を

Excelテンプレートの「Employee Leave Tracker」をそのまま使うと、月跨ぎ休暇の集計を正確に行うのが難しい場合があります。しかし、数式で開始日・終了日と月初・月末を比較するロジックを組み込むか、Power Queryで日次データを展開するなどの方法を駆使すれば、かなり柔軟な月別集計を実現できます。

特にPower Queryを使った手法は、一度仕組みを構築してしまえば、毎月の更新時にデータを「更新」ボタンでリフレッシュするだけで集計が自動化できるのがメリットです。一方で、ファイルのセキュリティ設定や信頼済みフォルダの設定をしっかり行わないと、思ったように動かない可能性もあります。
また、休日の定義は企業ごとに異なるため、土日の扱いや祝日リストも併せて柔軟に管理しましょう。自社の就業規則に合った休暇計算をカスタマイズできれば、Excelでの休暇管理がより正確かつ手間のかからない運用へと近づきます。