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

この記事は約7分で読めます。

この記事で分かること

  • 2つのリストを比べて「増えた・消えた・変わった」行だけを抜き出す方法
  • 片方向だけの突合で消えたレコードを見落とすという典型的な失敗と、その防ぎ方
  • 目視・COUNTIF・XLOOKUP・FILTERのどれを使えばよいかの判断

「先月のリストと今月のリストを並べて、目で追いながら差分を探している」という作業を、数式に置き換えます。

どんな場面で使うか

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

基本説明

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

今月リスト

A B C
1 商品コード 商品名 数量
2 A-1001 ボールペン 120
3 A-1002 ノート 80
4 A-1003 クリップ 200
5 A-1005 付箋 60
6 A-1006 マーカー 45

先月リスト

A B C
1 商品コード 商品名 数量
2 A-1001 ボールペン 120
3 A-1002 ノート 95
4 A-1003 クリップ 200
5 A-1004 消しゴム 30
6 A-1006 マーカー 45

この2枚の間にある差異は、次の3種類です。突合でつまずくのは、この3種類を1つの見方でまとめて拾おうとするときです。

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

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

手順

まず存在するかどうかだけを見る（COUNTIF）

一番単純な確認です。今月リストのD2に次の数式を入れ、6行目までコピーします。

=IF(COUNTIF(先月リスト!$A$2:$A$6, A2)=0, "今月から増えた", "")

COUNTIFは「その値が範囲の中にいくつあるか」を数える関数です。0件なら先月に無かった、ということになります。この式ではA-1005の行だけに「今月から増えた」と表示されます。

参照範囲を $A$2:$A$6 と$付きで書いているのは、下にコピーしても参照先がずれないようにするためです。

逆方向も同じように見る（ここを飛ばさない）

先月リストのD2に、参照先を入れ替えた同じ形の数式を入れ、6行目までコピーします。

=IF(COUNTIF(今月リスト!$A$2:$A$6, A2)=0, "今月から消えた", "")

こちらではA-1004の行に「今月から消えた」と表示されます。この2本をセットにして初めて、増減の両方が拾えます。

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

存在の有無だけでなく、数量の変化も見ます。今月リストのE2に入れて6行目までコピーします。

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

先月の数量が引けます。A-1002なら95、A-1005なら先月に無しが返ります。差を出したい場合は、F2に次を入れて6行目までコピーします。

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

A-1002のF列は -15 になります。ISNUMBERで囲んでいるのは、E列が「先月に無し」という文字列のときに引き算をさせないためです。

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

作業列を作らず、差異のある行だけをまとめて別の場所に出したい場合はこちらです。今月リストの空いているセル（H2、データの入っていない列）に入れます。

=FILTER(A2:C6, C2:C6<>SUMIF(先月リスト!$A$2:$A$6, A2:A6, 先月リスト!$C$2:$C$6), "差異なし")

この数式はH2を起点に、結果をスピル（自動で複数セルに展開）します。見出し行は表示用で、実際にセルへ返るのは次の2行分のデータだけです。

商品コード 商品名 数量
A-1002 ノート 80
A-1005 付箋 60

ここでSUMIFを使っているのは、商品コードごとの先月の数量を、一度に配列として取り出すためです。先月リストに存在しない商品コードでは合計が0になるので、「増えた行（先月0）」と「数量が変わった行」を、1本の数式で同時に拾えます（数量が0の場合の注意点は後述の「よくあるエラー」を参照）。

先月側も同じように書きます。先月リストの空いているセル（H2、データの入っていない列）に入れます。

=FILTER(A2:C6, C2:C6<>SUMIF(今月リスト!$A$2:$A$6, A2:A6, 今月リスト!$C$2:$C$6), "差異なし")

こちらも同様にスピルし、見出し行は表示用で、実際に返るのは次の2行です。A-1004が拾えていることを確認してください。

商品コード 商品名 数量
A-1002 ノート 95
A-1004 消しゴム 30

サンプルコードまたは例

どれを使うかは、リストの性質で決めると迷いません。

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

Excel 2013〜2019ではXLOOKUPが使えないため、同じE2にVLOOKUPとIFNAを組み合わせた次の式を入れ、6行目までコピーします。

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

3は「先月リストのA列から数えて3列目（＝C列の数量）」という意味です。列を挿入すると壊れるので、可能であればXLOOKUPを使ってください。

毎月同じ突合を繰り返すのであれば、数式ではなくPower Queryのマージ機能を使ったほうが、翌月は更新ボタン1つで済みます。

よくあるエラー

  • 数量が0の行と、存在しない行が区別できない：上のSUMIFを使った書き方は、リストに無い商品コードを0として扱います。数量に本当に0が入りうるデータでは、COUNTIFによる存在チェックと組み合わせて、「無い」のか「0なのか」を分けてください。
  • 同じ商品コードが2行以上ある：SUMIFは重複を合計してしまい、XLOOKUPは最初の1件しか返しません。突合の前に、今月リストの空いているセル（G2など、H列のFILTER結果と重ならない列）に=COUNTIF($A$2:$A$6, A2)を入れて6行目までコピーし、2以上になる行がないか確認してください。
  • 見た目は同じなのに一致しない：前後の空白、全角と半角の違い、数値と文字列の違いが原因です。見分け方はVLOOKUPが一致しない原因と直し方にまとめています。
  • COUNTIFで意図しない一致が起きる：COUNTIFは*や?をワイルドカードとして解釈します。商品コードにこれらの記号が含まれる場合、別のコードまで数えてしまうことがあります。
  • FILTERが#SPILL!・#CALC!になる：原因と直し方はFILTER関数で「条件に合う行だけ抽出」にまとめています。#CALC!は上の数式のように第3引数（"差異なし"）を指定しておけば防げます。

まとめ

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

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

関連記事

サンプルファイル

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

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