【Excel】2つのリストを突合して差異だけを洗い出す方法 — COUNTIF・XLOOKUP・FILTERの使い分け

Excel
2026.09.23
この記事は約9分で読めます。

先月と今月のリストを並べて、目で追いながら差分を探している。数式に置き換えたいが、COUNTIF と XLOOKUP のどちらを使えばよいか分からない。この記事は、その困りごとを扱います。

結論は、行があるか無いかは COUNTIF、数量などの値が変わったかは XLOOKUP で見ることです。いちばん大事なのは、COUNTIF を今月→先月と先月→今月の両方向で入れることです。片方向だけだと、今月から消えた行を見落とします。

この記事で分かること

  • 2つのリストを比べて「増えた・消えた・変わった」行を洗い出す方法
  • 片方向だけの突合で消えた行を見落とす失敗と、その防ぎ方
  • COUNTIF・XLOOKUP・FILTER のどれを使えばよいかの判断

今月リストに突合の数式を入れた完成図

D5 に「今月から増えた」、F3 に先月との差 -15 が出ています。H2 からは、差異のある2行だけが一覧で出ています。

COUNTIF・XLOOKUP・FILTER の使い分け

3つの関数は、返すものが違います。組み合わせて使うのが基本です。

やりたいこと 使うもの 返すもの 対応バージョン
行があるか無いかを知りたい COUNTIF 件数（0なら無い） すべて
対応する値を持ってきたい XLOOKUP（無ければVLOOKUP） 相手側の値 Microsoft 365 / 2021以降
差異のある行だけ一覧にしたい FILTER + SUMIF 差異のある行 Microsoft 365 / 2021以降
毎月繰り返す・列が多い Power Query（マージ） 突合した表 2016以降

COUNTIF で増減を見て、XLOOKUP で値の変化を見る。この2つを並べて使うのが、この記事の手順です。

どんな場面で使うか

  • 在庫表・台帳の月次比較（先月と今月で何が変わったか）
  • システムから出力した一覧と、手作業で管理している一覧の照合
  • 送ったデータと返ってきたデータの突き合わせ
  • 名簿・マスターの更新差分の確認

使うデータ

サンプルファイル（記事末尾）には「今月リスト」「先月リスト」の2つのシートがあります。どちらも A列が商品コード、B列が商品名、C列が数量で、データは2行目から6行目までです。

今月リストシート（A1:C6）

先月リストシート（A1:C6）

2枚の間にある差異は、次の3種類です。

種類 該当 気づきやすさ
今月に増えた A-1005 付箋 気づきやすい
今月から消えた A-1004 消しゴム 見落としやすい
両方にあるが数量が違う A-1002 ノート（95 → 80） 目視ではまず気づけない

つまずきやすい点：今月リストだけを見ると、消えた行は出てこない

今月リストを1行ずつ先月と照合すると、今月リストに無い A-1004 は照合の対象にならず素通りします。これが片方向突合の落とし穴です。突合は必ず両方向で見ます。

手順

今月に増えた行を見る（COUNTIF）

  1. 「今月リスト」シートの D2 を選ぶ
  2. 次の数式を入力して Enter を押す
=IF(COUNTIF(先月リスト!$A$2:$A$6, A2)=0, "今月から増えた", "")

D2 を D6 までコピーします。

今月リストの D2:D6 に COUNTIF を入れた画面

A-1005 の行（D5）だけに「今月から増えた」と出ます。

COUNTIF は、その値が範囲の中にいくつあるかを数える関数です。0件なら先月に無かった行です。範囲を $A$2:$A$6 と $ 付きで書くのは、コピーしても参照先がずれないようにするためです。

今月から消えた行を見る（ここを飛ばさない）

  1. 「先月リスト」シートの D2 を選ぶ
  2. 参照先を入れ替えた次の数式を入力して Enter を押す
=IF(COUNTIF(今月リスト!$A$2:$A$6, A2)=0, "今月から消えた", "")

D2 を D6 までコピーします。

先月リストの D2:D6 に COUNTIF を入れた画面

A-1004 の行（D5）に「今月から消えた」と出ます。この2本をセットにして初めて、増減の両方が拾えます。

数量が変わった行を見る（XLOOKUP）

  1. 「今月リスト」シートの E2 を選ぶ
  2. 次の数式を入力して Enter を押す
=XLOOKUP(A2, 先月リスト!$A$2:$A$6, 先月リスト!$C$2:$C$6, "先月に無し")

E2 を E6 までコピーします。

今月リストの E2:E6 に先月の数量を引いた画面

先月の数量が並びます。A-1002 は 95、先月に無い A-1005 は「先月に無し」です。

差を出すには、F2 に次の数式を入れて F6 までコピーします。

=IF(ISNUMBER(E2), C2-E2, "")

今月リストの F2:F6 に先月との差を出した画面

A-1002 の F3 が -15 になります。ISNUMBER で囲むのは、E列が「先月に無し」の行で引き算をさせないためです。

差異のある行だけを抜き出す（FILTER）

作業列を作らずに、差異のある行だけを別の場所へまとめて出す方法です。

  1. 「今月リスト」シートの H2（データの入っていない列）を選ぶ
  2. 次の数式を入力して Enter を押す
=FILTER(A2:C6, C2:C6<>SUMIF(先月リスト!$A$2:$A$6, A2:A6, 先月リスト!$C$2:$C$6), "差異なし")

今月リストの H2 から差異のある2行がスピルした画面

結果は H2 から H2:J3 に自動で広がります（スピル）。A-1002 ノート 80 と、A-1005 付箋 60 の2行です。

SUMIF は、商品コードごとの先月の数量をまとめて取り出すために使っています。先月に無い商品コードは合計が 0 になるので、増えた行と数量が変わった行を1本の数式で拾えます。

先月側も同じように書きます。

  1. 「先月リスト」シートの H2 を選ぶ
  2. 次の数式を入力して Enter を押す
=FILTER(A2:C6, C2:C6<>SUMIF(今月リスト!$A$2:$A$6, A2:A6, 今月リスト!$C$2:$C$6), "差異なし")

先月リストの H2 から差異のある2行がスピルした画面

A-1002 ノート 95 と、A-1004 消しゴム 30 の2行が返ります。消えた A-1004 が拾えていることを確認してください。

Excel 2019 以前で使う場合

XLOOKUP が使えない Excel 2013〜2019 では、今月リストの E2 に VLOOKUP と IFNA を組み合わせた次の式を入れ、E6 までコピーします。

=IFNA(VLOOKUP(A2, 先月リスト!$A$2:$C$6, 3, FALSE), "先月に無し")

VLOOKUP と IFNA で先月の数量を引いた画面

XLOOKUP と同じ値が並びます。3 は「先月リストのA列から数えて3列目（数量）」という意味です。列を挿入すると壊れるので、使えるなら XLOOKUP を選んでください。

毎月同じ突合を繰り返すなら、Power Query のマージ機能を使うと、翌月は更新ボタン1つで済みます。

よくあるエラー

数量が0の行と、存在しない行が区別できない

  • 原因: SUMIF を使った FILTER の書き方は、相手側に無い商品コードを 0 として扱います
  • 直し方: 数量に本当に 0 が入りうるデータでは、COUNTIF の存在チェックと組み合わせて「無い」のか「0」なのかを分けます

同じ商品コードが2行以上ある

  • 原因: SUMIF は重複した行を合計し、XLOOKUP は最初の1件しか返しません
  • 直し方: 突合の前に重複を確かめます。今月リストの G2（H列の FILTER の結果と重ならない列）に次の数式を入れ、G6 までコピーします
=COUNTIF($A$2:$A$6, A2)

今月リストの G2:G6 で重複を確かめた画面

すべて 1 なら重複はありません。2以上の行があれば、その商品コードを先に整理します。

見た目は同じなのに一致しない

COUNTIF で意図しない一致が起きる

  • 原因: COUNTIF は * や ? をワイルドカードとして解釈します
  • 直し方: 商品コードにこれらの記号が含まれる場合は、別のコードまで数えていないかを確かめます

FILTER が #SPILL!・#CALC! になる

まとめ

突合でいちばん怖いのは、エラーが出ることではなく「消えた行に気づかないまま作業が終わること」です。行があるか無いかは COUNTIF、値が変わったかは XLOOKUP で見て、COUNTIF は必ず今月側・先月側の両方向に入れてください。差異のある行だけを一覧で出したい場合は、FILTER と SUMIF を組み合わせると、増えた行と値が変わった行を1本の数式でまとめて拾えます。

対応バージョン: XLOOKUP・FILTERは Microsoft 365 または Excel 2021 以降。Excel 2013〜2019 では XLOOKUP は本文の IFNA＋VLOOKUP で置き換えます

関連記事

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

タイトルとURLをコピーしました