doodle-on-web

自分で調べたことや、仕事の中で質問されたことなどをまとめています。

Excelで抜け漏れを自動チェック！FILTER＋COUNTIFで2リストの差分を一瞬で抽出する方法

Excel 関数 データ分析 業務効率化 データ管理

スポンサーリンク

はじめに：「どのデータが漏れているか」を手作業で探していませんか？

データ管理をしていると、こんな場面に遭遇することはありませんか？

  • 商品マスタにある商品コードが、今月の売上データに含まれていない
  • 社員リストにいる人が、勤怠データに記録されていない
  • 送付先リストのうち、まだ送付が完了していない宛先を確認したい

こうした「2つのリストを照合して、片方にしかないデータを洗い出す」作業を、今も目視やVLOOKUPの手作業で行っていないでしょうか。

この記事では、FILTER・COUNTIF・NOT の3関数を組み合わせることで、どんなデータ量でも数式一つで差分リストを自動抽出する方法を、コピーしてすぐ使えるサンプルとともに解説します。一度設定すれば、データが更新されても結果が自動で変わるため、定期的なチェック業務を大幅に効率化できます。

基本の考え方：COUNTIFで「存在するか」を調べる

まず、数式の核となるロジックを理解しましょう。

「リストBの中にリストAの値が含まれているか」を調べるには COUNTIF が最適です。

=COUNTIF(リストB, 調べたい値)

結果が 0なら「存在しない＝抜け漏れ」、1以上なら「存在する」を意味します。

この数値は、そのまま論理値として扱うことができます。Excelでは 0 は FALSE、0以外は TRUE と解釈されるため、NOT を被せるだけで「存在しない → TRUE」に変換できます。

=NOT(COUNTIF(リストB, 調べたい値))

補足：> 0 は省略できる NOT(COUNTIF(...) > 0) と書いても結果は同じですが、COUNTIF の戻り値は0以上の整数なので > 0 は不要です。本記事では短く読みやすい NOT(COUNTIF(...)) に統一しています。

この TRUE / FALSE の配列を FILTER 関数に渡すことで、抜け漏れに該当する行だけを抽出できます。

実践例：商品コードの抜け漏れを自動抽出する

サンプルデータ

以下のデータで試してみましょう。Googleスプレッドシートのサンプルを用意しています（末尾リンク参照）。ご自身のExcelにコピー&ペーストしてもそのまま動作します。

Sheet1（商品マスタ）：A列

A列（商品コード）
A001
A002
A003
A004
A005

Sheet2（売上データ）：A列

A列（売上商品コード）
A001
A003
A005

マスタには A002 と A004 があるのに、売上データには存在しません。この2件を自動で抽出します。

数式：FILTER＋COUNTIF＋NOT

Sheet1の空きセル（例：C2）に以下を入力してください。

=FILTER(
  A2:A6,
  NOT(COUNTIF(Sheet2!A2:A4, A2:A6))
)

出力結果：

A002
A004

数式を1つ入力するだけで、抜け漏れのコードが縦方向に自動展開（スピル）されます。売上データが更新されても、結果はリアルタイムで反映されます。

数式の構造を分解して理解する

部分 役割
A2:A6 照合元のマスタリスト（全件）
Sheet2!A2:A4 比較対象の売上データ
COUNTIF(...) 各マスタ値が売上データに何件あるかを配列で返す
NOT(...) 0件（未存在）を TRUE、1件以上を FALSE に変換
FILTER(...) TRUE に対応する行だけを抽出して返す

この組み合わせにより、手作業でのVLOOKUPチェックや目視確認が完全に不要になります。

応用：抜け漏れの件数もあわせて表示する

抜け漏れのリストに加えて「合計何件あるか」を把握したい場合は、COUNTA を外側に重ねるだけです。

=COUNTA(FILTER(
  A2:A6,
  NOT(COUNTIF(Sheet2!A2:A4, A2:A6))
))

ダッシュボード的なセルに置いておくと、データを更新するたびに件数が自動で変わり、「今日時点で○件未処理」といった管理に役立ちます。

大文字・小文字を厳密に区別したい場合

COUNTIF は大文字と小文字を区別しません。"A001" と "a001" は同一と見なされます。通常の業務ではこれで問題ありませんが、厳密に区別したい場合は EXACT と MMULT を組み合わせた以下の数式を使います。

=FILTER(
  A2:A6,
  MMULT(
    --(EXACT(A2:A6, TRANSPOSE(Sheet2!A2:A4))),
    SEQUENCE(ROWS(Sheet2!A2:A4), 1, 1, 0)
  ) = 0
)

各引数の役割：

部分 役割
EXACT(A2:A6, TRANSPOSE(...)) マスタ値と売上値を大文字小文字込みで総当たり比較し、TRUE/FALSEの行列を作成
--( ) TRUE/FALSEを1/0の数値に変換
MMULT(..., SEQUENCE(..., 1, 0)) 各行の合計（一致件数）を計算。SEQUENCE(...,1,0) は全要素1の列ベクトルとして機能
= 0 一致が0件（＝どこにも存在しない）行を特定

こちらは上級者向けですが、システムコードやIDの照合など、ケースを厳密に扱いたい場面で有効です。

FILTER関数が使えない環境（Excel 2019以前）の代替案

FILTER 関数はMicrosoft 365またはExcel 2021以降が必要です。古いバージョンをお使いの場合は、作業列を使う方法で代用できます。

Sheet1のB列に判定式を入力：

=IF(COUNTIF(Sheet2!A:A, A2)=0, "抜け漏れ", "")

B2以降の全行に入力したあと、列フィルターで「抜け漏れ」を絞り込むことで同様の結果が得られます。自動スピルはしませんが、既存バージョンでも確実に動作します。

まとめ：3関数の役割と使いどころ

関数 役割
COUNTIF リストB内に値が存在するかを件数で返す
NOT 0（未存在）を TRUE、それ以外を FALSE に変換
FILTER TRUE 条件に一致する行だけを抽出して返す

この3つを組み合わせるだけで、2つのリストの差分を手作業ゼロで自動抽出できます。データ量が100件でも10,000件でも数式は変わらず、メンテナンスコストもほぼゼロです。

定期的なデータ照合・チェック業務をお持ちの方は、ぜひ今日から取り入れてみてください。

📎 サンプルファイル：Googleスプレッドシートで開く（コピーして自由にお使いください）

次回は「重複を除いたユニークリストの自動生成」について解説予定です。お楽しみに！