doodle-on-web

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

Excelで"リスト漏れ"を一瞬で発見する方法【出荷ミス・名簿照合にも使える】

Excel COUNTIF データ照合 業務効率化 リスト管理

スポンサーリンク

Excelで"リスト漏れ"を一瞬で発見する方法【出荷ミス・名簿照合にも使える】

「注文リストと出荷リストを見比べたら、どれが漏れているかわからなくなった…」そんな経験、ありませんか？

数十件なら目視でも何とかなりますが、数百・数千件になると手作業は現実的ではありません。でも大丈夫です。Excelの数式を1本入れるだけで、リスト漏れを1秒で洗い出せます。

この記事では、COUNTIF・IF・MATCH・ISNUMBER を使った「欠損値の検出テクニック」を、実務に即した具体例とともにわかりやすく解説します。

本記事での「欠損値」について 統計学でいう欠損値（NaN）とは異なり、本記事では「リストAには存在するが、リストBには存在しない値」という意味で使っています。混同しないようご注意ください。

今回使うシナリオ：注文リストと出荷リストの照合

以下のようなデータを想定します。

注文ID（リストA・マスタ） 出荷済みID（リストB・チェック対象）
1001（りんご） 1001
1002（みかん） 1003
1003（ぶどう） 1005
1004（バナナ）
1005（メロン）

注文は5件ありますが、出荷済みは3件だけです。1002（みかん）と1004（バナナ）が出荷漏れになっています。これを数式で自動検出するのがゴールです。

方法① COUNTIF＋IFで"欠損"を即ラベリング

まず最もシンプルな方法から始めましょう。

基本のしくみ

=COUNTIF(範囲, 検索値)

COUNTIF は指定した範囲の中に、検索値が何回登場するかを数えます。

  • 結果が 0 → リストBに存在しない（= 出荷漏れ）
  • 結果が 1以上 → リストBに存在する

IF関数と組み合わせて見やすくする

数字の0と1では直感的に判断しにくいので、IF関数を組み合わせて「欠損」「OK」のラベルを表示させましょう。

=IF(COUNTIF($C$2:$C$10, A2)=0, "欠損", "OK")

C列に出荷済みIDが入っている場合の例です。A列の注文IDがC列に存在しなければ「欠損」、存在すれば「OK」と表示されます。

注文ID 商品名 判定
1001 りんご OK
1002 みかん 欠損
1003 ぶどう OK
1004 バナナ 欠損
1005 メロン OK

一目でリスト漏れが把握できますね。

方法② MATCH＋ISNUMBERで柔軟に検出する

COUNTIFは手軽ですが、英語データで大文字・小文字を区別したい場面や、複雑な処理と組み合わせたい場合には限界があります。そこで登場するのが MATCH＋ISNUMBER の組み合わせです。

MATCHのしくみ

=MATCH(検索値, 検索範囲, 0)
  • 値が見つかれば → 位置番号（数値） を返す
  • 見つからなければ → エラー（#N/A） を返す

第3引数を 0 にすることで完全一致で検索します。

ISNUMBERで「存在する／しない」を判定

MATCHが数値を返せば「存在する」、エラーを返せば「存在しない」のですが、エラーのままでは使いにくいです。ISNUMBER を使うと、数値かどうかをTRUE/FALSEで判定できます。

=ISNUMBER(MATCH(A2, $C$2:$C$10, 0))
  • TRUE → C列に存在する
  • FALSE → C列に存在しない（= 欠損）

IFと組み合わせた完成形

=IF(ISNUMBER(MATCH(A2, $C$2:$C$10, 0)), "OK", "欠損")

方法①と同じラベル表示になりますが、こちらは応用の幅が広いです。

COUNTIFとMATCH＋ISNUMBERの使い分け

どちらを使うか迷ったら、この表を参考にしてください。

観点 COUNTIF＋IF MATCH＋ISNUMBER＋IF
記述のシンプルさ ◎ 短くて読みやすい △ やや長い
ワイルドカード（*）対応 ○ 使える × 使えない
大文字・小文字の区別 × 区別しない △ EXACTと組合せで対応可
他の関数との組み合わせ △ 限定的 ○ 柔軟に拡張できる

基本はCOUNTIF＋IFで十分です。英語データを扱う場合や、より複雑な照合ロジックが必要な場合にMATCH＋ISNUMBERを使いましょう。

ちなみに：大文字・小文字を厳密に区別したいときは？ MATCH 単体では区別できませんが、EXACT 関数と SUMPRODUCT を組み合わせることで対応できます。詳しくは別記事で解説予定です。

方法③ フィルタリングで欠損値だけを一覧表示する

判定列（「欠損」「OK」が入った列）を作ったら、次はフィルタ機能と組み合わせて欠損値だけを抽出しましょう。判定列はそのままにして、担当者への報告やコピー用に使います。

操作手順：

  1. 判定列を含む表全体を選択
  2. データ → フィルター をクリック
  3. 判定列のプルダウンから「欠損」だけにチェックを入れてOK

これで出荷漏れのIDと商品名だけが一覧表示されます。そのまま別シートにコピーして担当者に共有するのが実務での典型的な使い方です。

応用：大量データで処理が重いと感じたら

基本の数式に慣れてきたら、大量データを扱う際の最適化も意識しましょう。

① 検索範囲を必要最小限に絞る

列全体（B:B）を指定すると100万行以上が検索対象になり、動作が重くなります。

# 避けるべき書き方（列全体を指定）
=COUNTIF(C:C, A2)

# 推奨する書き方（データ範囲のみ）
=COUNTIF($C$2:$C$1000, A2)

② 絶対参照（$）を必ず付ける

数式を下の行にコピーするとき、検索範囲がズレないよう $ で固定するのは必須です。

=COUNTIF($C$2:$C$100, A2)
      ↑ $を付けることで範囲が固定される

まとめ：まずCOUNTIF＋IFから試してみよう

今回紹介した方法を整理します。

方法 数式の例 こんな時に使う
COUNTIF＋IF =IF(COUNTIF($C$2:$C$10,A2)=0,"欠損","OK") まずはこれでOK。シンプルで速い
MATCH＋ISNUMBER＋IF =IF(ISNUMBER(MATCH(A2,$C$2:$C$10,0)),"OK","欠損") 英語データや複雑な処理に対応したいとき

リスト漏れの検出は、在庫管理・出荷チェック・名簿照合・受発注管理など、あらゆる業務で使える汎用スキルです。まずは COUNTIF＋IF の組み合わせを自分のデータで試してみてください。使えるようになったら、MATCH＋ISNUMBER にもぜひ挑戦してみましょう。

次回は、さらに複雑なデータ照合に役立つ関数テクニックを紹介します。お楽しみに！

この記事が役に立ったら、ブックマークやシェアをしていただけると励みになります🙌