Google Sheets約5分で読めます

スプレッドシートの月別集計でデータのない月も出す|0件と未確認を分ける

スプレッドシートの月別集計でデータのない月も出すを表したイメージ
記事の内容を表したイメージ(AI生成)

月別の受付件数をまとめるとき、記録がない月が表から消えて、毎月手で行を補っている——という状況を想定して、この記事を書きます。

解決の方針は「月の一覧をデータとは別に用意し、そこへ件数を引き当てる」ことです。受付データに行がない月でも、一覧側には行があるため、集計が抜けません。さらに、確認が終わっていない月には「未確認」と表示し、確認済みで件数がゼロだった月と区別します。

以下では、架空の練習用データを使って手順と式の構成を説明します。式は公式の構文をもとに作成していますが、Excel・Google Sheetsでの実機動作は検証していません。実際のシートで使う前に、お使いのバージョンで動作を確認してください。

用意するシートと列

2枚のシートを使います。

受付シート(events.csv を貼り付ける先)

表は横にスクロールできます

列内容
A受付ID
B日付(日付型で入力)

月一覧シート(months.csv を貼り付ける先)

表は横にスクロールできます

列内容
A年月ラベル(例:2026-01)
B確認状態(確認済み:TRUE、未確認:FALSE)
C月初日(式で生成)
D件数(式で集計)

CSVファイルは値と見出しだけの練習用データです。書式・セル型・入力規則・保護・数式は含んでいませんS6。CSVのセル型は読み込み方で変わります。受付シートのB2・B3は実際の日付として 2026/1/2・2026/1/20 を入力し、月一覧のB列は文字列ではなく論理値のTRUE/FALSEとして用意してください。表示形式だけを変えて、文字列が日付へ変換できたと判断しないでください。

月初日の列を作る(C列)

A列の年月ラベルを日付条件に直接使うと、アプリによって比較が正しく動かない場合があります。C列に日付型の月初日を別途用意し、これを条件の基準にします。

C2 に固定の月初日を入力します。

=DATE(2026,1,1)

DATEは数値の年・月・日から日付値を作りますS4。C3 には次の式を入れ、C13 までコピーします。

=EDATE(C2,1)

EDATEは起算日から指定した月数後の日付を返しますS5。C3 から C13 へコピーすると、2026年2月から12月の月初日が順に並びます。C列の表示形式を日付に変更してください。

上記2式はGoogle Sheetsの公式構文をもとに作成しています。架空例での期待値は独立のローカル検算で確認しましたが、実機では未実行ですS6。

件数列を作る(D列)

D2 に次の式を入れ、D13 までコピーします。

=IF(B2,COUNTIFS(受付!$B$2:$B$3,">="&C2,受付!$B$2:$B$3,"<"&EDATE(C2,1)),"未確認")

式の意味を順に説明します。

  • IF(B2, ..., "未確認") — B列(確認状態)が TRUE なら件数を計算し、FALSE なら「未確認」を返しますS3。
  • COUNTIFS(受付!$B$2:$B$3, ">="&C2, 受付!$B$2:$B$3, "<"&EDATE(C2,1)) — 受付シートのB列から、月初日以上かつ翌月初日未満の行数を数えますS1。

EDATEで翌月初日を求めることで、月末が28日・30日・31日の違いを式側で吸収していますS5。

受付シートの参照範囲($B$2:$B$3)は架空例の行数に合わせています。実際のデータに合わせて範囲を広げてください。

この式も公式構文をもとに作成しており、実機では未実行です。

架空例で期待値を確認する

練習用データの内容は次のとおりですS6。

受付シート(events.csv の値)

表は横にスクロールできます

ID日付
E12026-01-02
E22026-01-20

月一覧シート(months.csv の値)

表は横にスクロールできます

年月確認状態
2026-01TRUE
2026-02TRUE
2026-03〜12FALSE

このデータに上記の式を適用した場合の期待値は次のとおりです。架空例のローカル検算(Python標準ライブラリ使用、2026-10-20 実施)で確認していますS6。

表は横にスクロールできます

月期待される件数
1月2
2月0
3〜12月未確認

2月は受付データに行がありませんが、B列が TRUE のため COUNTIFS が動き、0 を返します。3月以降は確認状態が FALSE のため、式は「未確認」を表示します。

データがない月を 0 とするか未確認とするかは、B列の TRUE/FALSE で切り分けます。確認作業が終わった月だけ TRUE に変更すると、D列の表示が「未確認」から件数に切り替わります。

行を追加したときの確認事項

受付データに行を追加する場合は、月一覧D列のすべての式について、2か所のCOUNTIFS参照範囲が新しい行まで含むか確認してください。範囲が固定のままだと、追加行が集計に入りません。

月一覧に行を追加する場合(年またぎなど)は、C列の EDATE 式と D列の IF/COUNTIFS 式を追加行にコピーし、B列の確認状態に TRUE または FALSE を入力してください。

この手順が使える条件

  • 受付シートのB列が日付型として認識されていること。文字列のまま入力されている場合は比較が機能しません。
  • 月一覧のC列が日付型として認識されていること。表示形式の変更だけでなく、アプリが日付値として保持しているかを確認してください。
  • 参照範囲が実際のデータ行を過不足なくカバーしていること。

これらの条件をシート上で確認してから式を動かしてください。

この記事の確認用ファイル

架空データの記入例や、手元で確認するための素材です。

参考資料

公式資料と架空データを使って編集しています。配布CSVは値と見出しのみで、数式・書式・保護設定を含む完成ブックではありません。Excel・Sheetsの実機操作は未検証です。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ