Excel約5分で読めます

Excelで集計表に0件の項目も出す|一覧表との照合

Excelで集計表に0件の項目も出すを表したイメージ
記事の内容を表したイメージ(AI生成)

申込者の一覧を集計したら、参加者がいない会場や区分が表から消えてしまった。ピボットテーブルでも関数でも、データに存在しない項目は自動では現れません。「0件でも一覧に出したい」という要求は、集計の仕組み上、一工夫が必要です。

この記事では、マスター一覧(全項目を網羅した表)と集計結果をCOUNTIFやVLOOKUPで照合する方法と、ピボットテーブルで0件項目を表示させる設定の両面を扱います。どちらを使うかの判断基準も示します。


なぜ0件の項目が消えるのか

ExcelのCOUNTIFやSUMIF、ピボットテーブルはいずれも「実際に存在するデータ」を起点に集計します。申込データに「会場C」が1件もなければ、集計結果の行や列に「会場C」は現れません。

これは正しい動作ですが、報告書や確認作業では困ります。「会場Cは0件だった」という事実を記録したい場合、消えてしまうと見落としが起きます。毎月同じ様式で提出する集計表なら、項目の有無が月によって変わると、受け取る側が比較できません。

解決の方向は二つあります。

  1. マスター一覧を用意して照合する(COUNTIF+マスター一覧)
  2. ピボットテーブルの設定で0を表示させる

どちらが適切かは、データの構造と更新頻度によって変わります。


方法の選び方

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

状況向いている方法
全項目のマスター一覧がすでにあるマスター照合(COUNTIF)
項目が今後増える可能性があるマスター照合(随時追加できる)
ピボットテーブルをすでに使っているピボットの設定変更
項目が固定で少ないどちらでも可
別シートの様式に貼り付ける必要があるマスター照合(式の結果を貼れる)

マスター照合は「全項目を自分で管理する」手間がかかりますが、ピボットテーブルに依存しないため、集計方法を変えても流用できます。ピボットテーブルの設定は手順が少ない反面、後述の制約があります。


マスター照合でCOUNTIFを使う手順

作業前に元のファイルをコピーして保存しておいてください。

準備:マスター一覧シートを作る

全項目(0件になりうる項目を含む)を1列に並べたシートを用意します。以下は架空の例です。

「会場マスター」シート(架空の例です)

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

A列:会場名
会場A
会場B
会場C
会場D

この一覧が「すべての項目が必ず出る」ことを保証する起点になります。項目が増えたときはここに追加します。

集計シートにCOUNTIFを置く

集計用シートのA列にマスターの会場名を参照し、B列にCOUNTIF式を入れます。

「集計」シートの構成(架空の例です)

  • A2:=会場マスター!A2(以下オートフィルで引っ張る)
  • B2:=COUNTIF(申込データ!$B:$B, A2)

「申込データ」シートのB列に会場名が入っている想定です。列の位置はご自身のデータに合わせてください。

この式は、申込データのB列全体からA2の値(会場名)を数えます。会場Cが1件もなければB列に0と表示されます。行が消えることはありません。

迷いやすい例外:会場名の表記ゆれ

申込データに「会場 C」(スペースあり)と「会場C」が混在すると、COUNTIFは別の値として数えます。集計前に申込データのB列をフィルターで確認し、表記が統一されているかチェックしてください。TRIM関数でスペースを除去してから集計する方法もあります。

複数条件で集計したい場合

「会場×区分」のような2軸の集計はCOUNTIFSを使います。

=COUNTIFS(申込データ!$B:$B, A2, 申込データ!$C:$C, B2)

この場合、マスター一覧には会場名と区分の組み合わせをすべて列挙しておきます。組み合わせが多いと管理が大変になるため、この規模になったらピボットテーブルとの併用も検討してください。


ピボットテーブルで0件項目を表示する設定

すでにピボットテーブルで集計している場合、設定を変えるだけで0件の項目を表示できることがあります。ただし、条件があります。

前提条件:項目がピボットのキャッシュに含まれている必要がある

ピボットテーブルは元データから一度「キャッシュ」を作り、そこから集計します。元データに一度も現れない項目(たとえば「会場C」がデータに全く入力されたことがない)は、キャッシュに存在しないため、設定を変えても表示されません。この場合はマスター照合の方法を使います。

「以前はあったが今月はゼロになった」項目であれば、以下の手順で対応できます。

手順

  1. ピボットテーブル内のセルを選択します。
  2. 行ラベルまたは列ラベルのいずれかの項目を右クリックします。
  3. 「フィールドの設定」を開きます。
  4. 「レイアウトと印刷」タブ(または「集計とフィルター」タブ)の中に、「データのないアイテムを表示する」というチェックボックスがあります。これをオンにします。

画面の表示は版や設定により異なります。見当たらない場合は公式のヘルプで確認してください。

この設定を有効にすると、キャッシュに存在する項目であれば0件でも行や列に現れます。ただし、ピボットテーブルを更新するたびに設定が維持されるかどうかは、Excelの版や設定によって変わることがあります。毎回確認する習慣をつけてください。


照合結果を様式に転記するときの注意

報告書の様式が別途決まっている場合、集計結果を手で貼り付けたり、VLOOKUP・XLOOKUPで引っ張ったりすることが多いと思います。

COUNTIFの結果は数値なので、そのまま別シートへVLOOKUPで参照できます。

=VLOOKUP(A2, 集計!$A:$B, 2, FALSE)

マスター一覧のA列の会場名を検索値にして、集計シートのB列の件数を取り出します。会場名が一致しない場合は#N/Aエラーになるので、IFERRORで0を返すか、表記ゆれを修正します。

XLOOKUPはExcelの比較的新しい版から使えます。手元の環境で使えるかどうかは公式のヘルプか関数一覧で確認してください。


次の行動

まず手元のファイルをコピーしてから作業を始めてください。マスター一覧が手元にある場合はCOUNTIF照合を、ピボットテーブルがすでにある場合は「データのないアイテムを表示する」設定を先に試してみてください。設定を変えても項目が出ない場合は、元データに一度も現れない項目である可能性が高いため、マスター照合に切り替えてください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

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

記事一覧へ