Excelの週合計（土曜締め）が短い週で壊れる原因とSUMIFS・FILTERによる解決方法

Excelで勤務表や工数管理表を作っていて、「週合計を毎週土曜に表示したいのに、月初が水曜スタートなどの短い週になると #REF! や 00:00 になってしまう…」という悩みはとてもよくあります。この記事では、その原因を分解しつつ、行削除にも強く、短い週でも壊れない「土曜締め週合計」の作り方を、コピペで使える数式付きで丁寧に解説します。

想定するシート構成と問題のイメージ

まずはこの記事で想定するシート構成を整理しておきます。実際のレイアウトとは多少違っていても、列の意味が対応していればそのまま応用できます。

サンプルの列構成

列	項目	入力例	説明
B列	日付	2025/4/1 など	一日につき「S行（予定）」「R行（実績）」の2行構成
C列	区分	S / R	S＝予定行、R＝実績行
D列	曜日	月, 火, …, 土	見た目用。関数で自動表示でも手入力でもOK
E～G列	始業・終業・休憩	9:00 / 18:00 / 1:00 など	勤務時間を計算するための元データ
H列	実績時間	8:00 など	実績行（C列=R）のみ計算結果を入れる補助列
J列	週合計	40:00 など	各週の土曜の行に、その週の月〜金の合計時間を表示

さらに、シート上部に「月」を選択するプルダウンがあり、選択した月の平日だけが表示されるような作りを想定します（前月・翌月は表示しない）。

このとき、土曜のセルに「H4,H6,H8,H10,H12…」といったように固定の行番号を指定して合計していると、

月によって表示行数が変わる
行削除をすると参照がずれて #REF! になる
月初が水〜金スタートの短い週だと 00:00 になってしまう

という症状が発生しやすくなります。

なぜ「短い週」で #REF! や 00:00 になるのか

トラブルの原因は、大きく分けて次の2つです。

原因1：固定セル参照（H4,H6…）が行削除で崩れる

よくあるパターンが、土曜のセルにこういった式を入れているケースです。

=SUM($H4,$H6,$H8,$H10,$H12)

このやり方は、一見シンプルですが、

ある月では「平日が4日だけ」「5週目が存在しない」などで行を削除したくなる
行を削除すると H10 が #REF! になり、週合計も #REF! になる
月を切り替えたときに「4週構成」「5週構成」が混在すると、式を毎回修正する羽目になる

といった問題が起きます。「行を消す前提」の設計と、「行番号を直接指定する式」は相性が最悪というわけです。

原因2：週の開始日が前月に食い込むのに、日付条件で前月を除外している

週合計を SUMIFS などで出そうとして、

週の開始日を「土曜 – 5日」で求める
しかし、月初が水〜金スタートだと、その週の「月曜」は前月になる
同時に「今月分だけ」に絞るために 月初以上 という条件を付けてしまう

こうすると、短い週では「前月の日付が条件から外れる」ため、該当データがなくなり、結果が 00:00 になる、というパターンがよく起こります。

このように、

固定セル参照＋行削除
前月をどう扱うかをあいまいにした日付条件

が組み合わさることで、短い週でだけ問題が表面化しているのです。

壊れない「土曜締め週合計」の設計方針

そこで、この記事では次のような設計をおすすめします。

行は基本的に削除しない（月によらず行構造を固定）
週合計は「土曜の日付」を基準に、日付条件で動的に集計する
前月分を含めるか除外するかは、日付条件の範囲で制御する

この設計にしておけば、短い週でも長い週でも、月によって表示行数が変わっても、式を使い回せます。

実績時間は補助列Hに集約する

まず、週合計の元になる「実績時間」は、1つの補助列にまとめておくと管理が楽です。ここでは H列を使います。

たとえば、

C列＝区分（S/R）
G列＝その日の実績時間（または「終業-始業-休憩」で計算）

となっているとき、H3セルに次のような式を入れ、下方向にコピーします。

=IF($C3="R", IFERROR($G3,""), "")

ポイントは次の通りです。

C列が「R」（実績）の行だけ、G列の値をH列にコピーする
エラーが出た場合は空欄にしておく
S行（予定行）はH列を空にしておくことで、「実績だけを合計」しやすくする

H列の表示形式は、必ず [h]:mm または [h]" 時間 "mm" 分" のような形式にしておきましょう。hh:mm のままだと 24時間を超えると一周してしまい、週合計や月合計が狂って見えます。

短い週でも壊れない SUMIFS の書き方

ここからが本題です。土曜行の J列に、「その週の月〜金の合計」を表示する式を入れていきます。

ここでは、例として「13行目が土曜の実績行（C13=R）」だとします。J13セルに入れるイメージです。

パターン1：前月分を除外し、当月に入ってからの平日だけ合計する

「月初の短週は前月分を含めないで合計したい」場合のパターンです。多くの勤務表では、この考え方が自然です。

=IF(WEEKDAY($B13,2)&lt;&gt;6,"",
  LET(
    d, $B13,                                  /* d = 土曜の日付 */
    start, MAX(EOMONTH(d,-1)+1, d-5),         /* 週の月曜 or 月初のうち遅い方 */
    SUMIFS($H$3:$H$72, $C$3:$C$72, "R",
                        $B$3:$B$72, "&gt;="&amp;start,
                        $B$3:$B$72, "&lt;="&amp;d-1)  /* 金曜まで */
  )
)

ここで使っている関数の意味を整理します。

WEEKDAY( ,2) … 月曜=1, 火曜=2, …, 土曜=6, 日曜=7
EOMONTH(d,-1)+1 … dの1ヶ月前の月末＋1日、つまり「月初」
LET … 変数のように値に名前を付けて読みやすくする機能

この式の動きは次の通りです。

WEEKDAY($B13,2) が 6（=土曜）以外なら空欄を返す（週合計は土曜行だけに表示）
d に「土曜の日付」を代入
start として、MAX(月初, d-5) を計算
- 通常の週：d-5 がその週の月曜になるので、それが start
- 月初の短週：d-5 が前月に食い込むので、「月初」の方が大きくなり、start=月初になる
start 〜 (土曜の前日=金曜) の間かつ C列="R" の行にある H列の値を合計

このように、「週の月曜」と「月初」のうち遅い方を集計開始日とすることで、短い週でも前月分をきれいに除外できます。

パターン2：週を月またぎで丸めたい場合（前月分も含める）

一方、「週単位で見たいので、短週の前月分も含めて、月曜〜金曜を丸ごと合計したい」というケースもあります。このときは、日付条件をシンプルに「土曜-5〜土曜-1」にすればOKです。

=IF(WEEKDAY($B13,2)&lt;&gt;6,"",
  LET(
    d, $B13,
    SUMIFS($H$3:$H$72, $C$3:$C$72, "R",
                        $B$3:$B$72, "&gt;="&amp;d-5,
                        $B$3:$B$72, "&lt;="&amp;d-1)
  )
)

このパターンでは、

短い週の月曜が前月であっても、そのまま対象になる
完全に「週締め」で考えるので、週合計の数字がカレンダーと一致しやすい

「月の中だけで完結させたい」か「週という単位を優先したい」かで、パターン1と2を使い分けてください。

パターン3：FILTER関数を使った動的配列版（Microsoft 365）

Microsoft 365 や Excel 2021 以降で使える FILTER 関数を使うと、条件に合った行だけを抜き出してから合計する書き方もできます。

=IF(WEEKDAY($B13,2)&lt;&gt;6,"",
  LET(
    d, $B13,
    start, MAX(EOMONTH(d,-1)+1, d-5),
    SUM(FILTER($H$3:$H$72,
               ($C$3:$C$72="R")*
               ($B$3:$B$72&gt;=start)*
               ($B$3:$B$72&lt;=d-1)))
  )
)

ここでのポイントは、

FILTER(範囲, 条件) の条件部分で、複数条件の「かけ算（*）」=論理積（AND）を使っていること
SUM(FILTER(...)) で、条件に合った行だけを取り出して合計していること

よくあるミスは次の2つです。

括弧の閉じ忘れ … とくに LET＋FILTER＋SUM をネストするときは括弧の数が増えるので注意
論理条件の「*」を忘れる … 条件1 + 条件2 と書くと「OR」的な動きになり、意図しない行まで合計されてしまいます

FILTER版は、条件を増やしたいときに書き足しやすく、式の読みやすさも高いので、Microsoft 365 環境なら積極的に検討してよい書き方です。

3つのパターンの比較

パターン	集計対象	短い週での扱い	おすすめ用途
1. 当月のみ合計	月初〜その週の金曜	前月分は切り捨て	「月次の勤務時間」をきっちり月内だけで見たい
2. 週を丸ごと合計	その週の月曜〜金曜	前月分も含める	週単位の工数管理・プロジェクト管理
3. FILTER版	1と同じ条件だが柔軟	条件追加がしやすい	365環境で、条件を後から増やす可能性がある場合

さらに堅牢にする「週締めキー方式」

ここまでの式でも十分実用的ですが、行削除や列追加にさらに強くしたい場合は、「週締めキー」を持たせる方法がおすすめです。

やることはシンプルで、各行に「その行が属する週の土曜の日付（=週締め日）」を計算して持たせ、そのキーで SUMIFS するだけです。

週締めキーをK列に計算する

新しい補助列 K を用意し、3行目に次の式を入れて下へコピーします。

=IF($C3="R", $B3 + (6 - WEEKDAY($B3,2)), "")

この式の意味は、

WEEKDAY($B3,2) で「その日の曜日（1〜7）」を取得
6（=土曜）から引いた差分だけ日付を進めることで、「その週の土曜」の日付を求める
C列が「R」の行だけ値を入れ、それ以外は空欄にしておく

例えば、

2025/4/7（月）の行なら → その週の土曜は 2025/4/12
2025/4/12（土）の行なら → そのまま 2025/4/12

というように、「同じ週に属する行はすべて同じ値」になります。

土曜行で週締めキーを使って合計する

次に、土曜行のJ列（例：J13）に次の式を入れます。

=IF(WEEKDAY($B13,2)&lt;&gt;6,"",
   SUMIFS($H$3:$H$72, $K$3:$K$72, $B13)
)

たったこれだけで、「K列の週締めキーが、土曜行のB列の日付と一致する行」の H列をすべて合計できます。行構造が変わっても、K列が正しければ常に正しい週合計が得られるのが大きなメリットです。

さらに、「月内だけ」に絞りたいときは、条件を追加します。

=IF(WEEKDAY($B13,2)&lt;&gt;6,"",
   SUMIFS($H$3:$H$72,
          $K$3:$K$72, $B13,
          $B$3:$B$72, "&gt;="&amp;EOMONTH($B13,-1)+1,
          $B$3:$B$72, "&lt;="&amp;EOMONTH($B13,0))
)

これにより、

週締めキー（K列）で「その週に属する行」を選び
B列の日付で「当月分だけ」に絞り込む

という二段絞りができます。短い週で前月分を含めるか、含めないかを明確にコントロールできるのがポイントです。

週締めキー方式のメリット

観点	従来の固定セル参照	週締めキー方式
行削除への強さ	弱い。簡単に #REF! になる	強い。行を追加・削除しても、キーと日付が合っていればOK
式の読みやすさ	行番号だらけで意図が分かりにくい	「週締めキー=土曜の日付」で直感的
条件の追加	範囲をいちいち書き換える必要がある	SUMIFSの条件を足すだけで柔軟に対応
月またぎ対応	式が複雑になりやすい	キーと日付条件の組み合わせで簡単に制御

特に、「シートをテンプレート化して毎月コピーする」「月によって週の数が変わる」といった運用を考えると、週締めキー方式は長期的な保守性の面で非常におすすめです。

実際に設定するときの手順まとめ

ここまでの内容を、「今からシートを修正する」視点で手順にまとめておきます。

行削除をやめる
- 平日行・土曜行は、月によらず一定の行構造に整える
- 使わない日付は空欄のまま残す（表示制御は別の方法で行う）
H列に実績時間を集約する
- C列がRの行だけ、実績時間を計算してH列に入れる
- H列の表示形式を [h]:mm または [h]" 時間 "mm" 分" に変更
K列に週締めキー（その週の土曜日）を計算する
- K3 に =IF($C3="R", $B3 + (6 - WEEKDAY($B3,2)), "") を入力し下までコピー
土曜行のJ列に週合計を表示する式を設定
- 週締めキー方式なら SUMIFS($H$3:$H$72, $K$3:$K$72, $B13) をベースにする
- 月内だけにしたい場合は、B列の日付を条件に追加
月初が水〜金スタートの月で動作確認
- あえて「短い週」を含む月を選択し、前月分が正しく含まれる／除外されるかを確認
- 一部の行を削除・追加しても週合計が崩れないかをチェック

SUMIFS や FILTER がうまくいかないときのチェックポイント

「SUMIFS でやろうとしてもうまくいかない」「FILTER を試したがよく分からなかった」というケースでは、次のようなポイントを確認してみてください。

条件範囲と合計範囲の行数・列数が一致しているか

SUMIFS / FILTER ともに、

合計範囲（H列）
条件範囲（B列・C列など）

の行数・列数が一致していないと、思わぬ結果が出ます。特に、行を削除したあとで範囲指定が途中までになってしまっていることが多いので注意しましょう。

日付が「文字列」になっていないか

日付を手入力している場合、「2025/4/1」のように見えていても、セルの中身が文字列の場合があります。日付として扱われていないと、

WEEKDAY がエラーになる
EOMONTH で月初・月末が取得できない
SUMIFS の日付条件 ">="&start が正しく動作しない

などの問題が起こります。怪しい場合は、

セルの表示形式を「標準」に変えてみる
=ISNUMBER(B3) で TRUE になるか確認する

といった方法でチェックしてみてください。

FILTER の条件に「*」を使い忘れていないか

FILTER で複数条件を使うときは、必ず かけ算（*） で論理積（AND）にします。

FILTER($H$3:$H$72,
       ($C$3:$C$72="R")*
       ($B$3:$B$72&gt;=start)*
       ($B$3:$B$72&lt;=d-1))

ここを + にしてしまうと、条件1 または条件2 のどちらかを満たす行すべてが対象になってしまい、「想定より多く合計される」という現象につながります。

パフォーマンスと保守性を意識した運用のコツ

勤務表・工数表は、1年・2年と使い続けることも多いので、パフォーマンスや保守性も意識して設計しておくと安心です。

INDIRECT や OFFSET は極力使わない

「月ごとに範囲を切り替えたい」「土曜の位置を自動で探したい」といった理由で INDIRECT や OFFSET を使うと、一見便利に見えますが、これらは揮発関数のため、ブック全体が重くなりがちです。

今回紹介したように、

日付を基準にした SUMIFS
週締めキー（K列）方式

などを組み合わせると、揮発関数を使わなくても柔軟な週集計が可能です。

元データと集計は分けておくと安心

可能であれば、

1枚目のシート：日付・区分・始業・終業・休憩・実績時間（H列）までの「明細」だけを持つ
2枚目のシート：週合計、月合計、部署別集計などの「集計専用」

のように、明細と集計を分離しておくと、後からピボットテーブルやグラフを追加する際にも柔軟に対応できます。今回の「週締めキー」は、そうした集計シート側でも使い回せる便利なキーになります。

まとめ：土曜の日付を軸にした「動く式」で短い週問題を解消する

Excelの週合計（土曜締め）が、月初の短い週でだけ #REF! や 00:00 になってしまう原因は、

H4, H6, H8… のような固定セル参照＋行削除
前月に食い込む日付をどう扱うかを考慮していない日付条件

といった設計上の問題でした。

これに対して、

行構造は固定し、実績時間はH列にまとめる
週合計は、土曜の日付を基準に SUMIFS / FILTER で日付条件を付けて集計する
さらに堅牢にするなら、各行に「週締めキー（その週の土曜）」を持たせて SUMIFSする

という方針に切り替えることで、月初が水〜金スタートの短い週でも、毎月の行削除・行追加があっても、式を壊さずに運用できるようになります。

勤務表でも工数管理表でも、週単位の集計が必要になる場面は非常に多いので、一度この「土曜基準の動的集計＋週締めキー方式」に作り替えておけば、今後のシート作成のベースとして長く使い回せるはずです。ぜひ、ご自身のシートの列構成に合わせて、この記事の数式をコピー＆カスタマイズしてみてください。

この記事を書いた人

佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

Excelの週合計（土曜締め）が短い週で壊れる原因とSUMIFS・FILTERによる解決方法

想定するシート構成と問題のイメージ

サンプルの列構成

なぜ「短い週」で #REF! や 00:00 になるのか

原因1：固定セル参照（H4,H6…）が行削除で崩れる

原因2：週の開始日が前月に食い込むのに、日付条件で前月を除外している

壊れない「土曜締め週合計」の設計方針

実績時間は補助列Hに集約する

短い週でも壊れない SUMIFS の書き方

パターン1：前月分を除外し、当月に入ってからの平日だけ合計する

パターン2：週を月またぎで丸めたい場合（前月分も含める）

パターン3：FILTER関数を使った動的配列版（Microsoft 365）

3つのパターンの比較

さらに堅牢にする「週締めキー方式」

週締めキーをK列に計算する

土曜行で週締めキーを使って合計する

週締めキー方式のメリット

実際に設定するときの手順まとめ

SUMIFS や FILTER がうまくいかないときのチェックポイント

条件範囲と合計範囲の行数・列数が一致しているか

日付が「文字列」になっていないか

FILTER の条件に「*」を使い忘れていないか

パフォーマンスと保守性を意識した運用のコツ

INDIRECT や OFFSET は極力使わない

元データと集計は分けておくと安心

まとめ：土曜の日付を軸にした「動く式」で短い週問題を解消する

この記事を書いた人

コメント

コメントするコメントをキャンセル

Excelの週合計（土曜締め）が短い週で壊れる原因とSUMIFS・FILTERによる解決方法

想定するシート構成と問題のイメージ

サンプルの列構成

なぜ「短い週」で #REF! や 00:00 になるのか

原因1：固定セル参照（H4,H6…）が行削除で崩れる

原因2：週の開始日が前月に食い込むのに、日付条件で前月を除外している

壊れない「土曜締め週合計」の設計方針

実績時間は補助列Hに集約する

短い週でも壊れない SUMIFS の書き方

パターン1：前月分を除外し、当月に入ってからの平日だけ合計する

パターン2：週を月またぎで丸めたい場合（前月分も含める）

パターン3：FILTER関数を使った動的配列版（Microsoft 365）

3つのパターンの比較

さらに堅牢にする「週締めキー方式」

週締めキーをK列に計算する

土曜行で週締めキーを使って合計する

週締めキー方式のメリット

実際に設定するときの手順まとめ

SUMIFS や FILTER がうまくいかないときのチェックポイント

条件範囲と合計範囲の行数・列数が一致しているか

日付が「文字列」になっていないか

FILTER の条件に「*」を使い忘れていないか

パフォーマンスと保守性を意識した運用のコツ

INDIRECT や OFFSET は極力使わない

元データと集計は分けておくと安心

まとめ：土曜の日付を軸にした「動く式」で短い週問題を解消する

この記事を書いた人

関連記事

コメント

コメントする コメントをキャンセル

コメントするコメントをキャンセル