【Excel】エクセルでA列にあってB列にないデータを抽出する方法

Excelで名簿、商品コード、顧客番号などを照合するとき、A列にはあるのにB列には存在しないデータだけを抽出したい場面があります。

たとえば、旧リストと新リストを比較して削除された項目を見つける、申込者一覧と入金者一覧を突き合わせる、在庫表と出荷表の差分を確認するといった作業です。

手作業で一件ずつ確認すると、件数が増えるほど見落としや転記ミスが起こりやすくなります。

そこでこの記事では、関数、フィルター、条件付き書式、Power Queryを使い、A列にあってB列にない値を効率よく抽出する方法を解説します。

 

エクセルでA列にあってB列にないデータを抽出する方法1【COUNTIF関数】

それではまず、COUNTIF関数でA列の値がB列に存在するかを判定し、抽出する方法について解説していきます。

A列 商品コード	B列 登録済みコード	C列 判定
P001	P002	未確認
P002	P004	未確認
P003	P005	未確認
P004	P007	未確認

 

COUNTIF関数による存在件数の確認

COUNTIF関数は、指定した範囲の中に条件と一致するセルが何件あるかを数える関数です。

A列の各値をB列から探し、その件数が0であれば、A列にしかないデータだと判断できます。

この方法はExcel 2010やExcel 2016などでも利用できるため、幅広い環境で使いやすい方法です。

C2セルをクリックし、数式バーに次の数式を入力します。

数式内の$B$2:$B$100は、検索対象となるB列の範囲です。

ドル記号を付けることで、数式を下方向へコピーしても検索範囲がずれません。

A2は、現在判定したいA列のデータです。

たとえばA2がP001で、B2からB100の中にP001がなければ、COUNTIF関数の結果は0になります。

反対に、B列にP001が1件あれば結果は1、同じコードが2件あれば結果は2です。

A列にあってB列にないデータを探す目的では、結果が0かどうかを見ることが重要です。

検索範囲の最終行が100行を超える場合は、実際のデータ量に合わせて$B$2:$B$1000などへ変更します。

列全体を対象にしたいときは$B:$Bとも書けますが、巨大なブックでは計算量が増えることがあります。

日常的な一覧表なら、必要な行数に絞った範囲指定のほうが扱いやすいでしょう。

また、B列に空白セルがあること自体は問題ありません。

ただしA列の空白セルも判定対象になるため、空白を抽出したくない場合は後でフィルターから除外します。

数字に見えるコードでも、先頭の0を含む場合は文字列として統一することが大切です。

00125と125は、表示形式だけではなくセル内の値の種類によって別データとして扱われることがあります。

 

IF関数を組み合わせた抽出対象の表示

COUNTIF関数で件数だけを表示しても、件数0の行を探す作業が残ります。

そこでIF関数を組み合わせると、対象行に未登録や抽出対象といった分かりやすい文字を表示できます。

C2セルには次の数式を入力します。

IF関数は、最初の条件が成り立つ場合と成り立たない場合で表示内容を分ける関数です。

COUNTIF($B$2:$B$100,A2)=0の部分では、B列にA2と同じ値が1件もないかを確認しています。

条件が真ならB列にない、偽ならB列にあると表示されます。

結果を文字で表示しておくと、後からフィルターや並べ替えを行う際にも判断しやすくなります。

数式をC2に入力したら、セル右下に表示される小さな四角形のフィルハンドルをダブルクリックします。

隣接するA列のデータが連続していれば、Excelが最終行まで数式を自動でコピーします。

データ途中に空白行がある場合は、フィルハンドルを必要な行までドラッグするか、コピーと貼り付けを使いましょう。

数式の表示結果が予想と違う場合は、B列の値に全角と半角の違い、余計なスペース、改行が含まれていないかを確認します。

見た目が同じでも、ABCとＡＢＣは別の文字列です。

コピー元がWebページや基幹システムの場合、末尾に見えない空白が入っていることもあります。

そのような場合はTRIM関数やCLEAN関数で別列に整形してから照合すると安定します。

判定列の表示文は、差分、対象外、未登録など、作業の目的に合う言葉へ変更して構いません。

 

オートフィルとフィルターによる対象行の抽出

判定列にB列にないという表示が作成できたら、オートフィルターで該当行だけを表示します。

一覧のどこか1セルを選択し、Excel上部のデータタブからフィルターをクリックします。

1行目の見出しセルに下向きの矢印が表示されたら、C列の矢印をクリックします。

表示された一覧で、B列にあるのチェックを外し、B列にないだけにチェックを入れてOKを押します。

これでA列にあるもののB列にはないデータだけが画面に残ります。

フィルターは行を削除する機能ではなく、条件に合わない行を一時的に隠す機能です。

抽出した結果を別シートへ渡す場合は、表示されているセル範囲を選択してコピーします。

そのまま貼り付けると非表示の行まで対象になる場合があるため、ホームタブの検索と選択から条件を選択してジャンプを使い、可視セルを選択してからコピーする方法が確実です。

ショートカットではAltキー、セミコロンキーの順に押すと可視セルだけを選択できます。

ただし、ノートパソコンのキーボードや日本語配列では操作しづらいことがあるため、画面操作を覚えておくと便利です。

貼り付け先では、数式ごと移すのではなく値だけ貼り付けを選ぶと、元表への参照を持たない差分リストを作成できます。

毎月同じ処理を行うなら、判定列とフィルター設定を残したテンプレートを用意する方法も実用的です。

データを追加した後は、検索範囲が新しい最終行まで含まれているかを確認しましょう。

 

エクセルでA列にあってB列にないデータを抽出する方法2【FILTER関数】

続いては、Microsoft 365やExcel 2021以降で利用できるFILTER関数を使い、別の場所へ自動抽出する方法を確認していきます。

A列 比較元	B列 比較先	E列 抽出結果
東京	大阪	東京
大阪	福岡	名古屋
名古屋		札幌
札幌

 

FILTER関数による差分リストの自動作成

FILTER関数は、指定した条件に一致するデータだけを別の範囲へ表示する関数です。

COUNTIF関数とフィルターを組み合わせる方法では判定列が必要ですが、FILTER関数なら1つの数式で抽出結果を作成できます。

E1に抽出結果などの見出しを入力し、E2セルを選択します。

次の数式を入力してください。

最初のA2:A100は、抽出したい元データの範囲です。

COUNTIF(B2:B100,A2:A100)=0は、A列の各値についてB列内の件数が0であるかを一括で判定しています。

最後の該当なしは、条件を満たすデータが1件もない場合に表示する文字です。

FILTER関数では、数式を入力した1セルから結果が下方向へ自動展開されます。

この自動展開はスピルと呼ばれます。

抽出結果の下側や右側に文字や数式が入力されていると、スピルの範囲にデータがあるというエラーが表示されることがあります。

エラーが出た場合は、E2より下のセルにある不要なデータを削除するか、十分に空いている列へ数式を移動します。

FILTER関数の結果は元のA列の入力内容に連動します。

A列へデータを追加したり、B列へ登録済みの値を入力したりすると、抽出結果も自動で変化します。

そのため、差分を定期的に監視したい業務では特に便利です。

古いExcelではFILTER関数が使えないため、数式を入力しても名前エラーになる場合があります。

その際はCOUNTIF関数の判定列方式へ切り替えるのが現実的です。

 

空白セルを除外する条件の追加

A列に空白行が含まれていると、FILTER関数の結果に空白が混ざることがあります。

これは空白もB列にない値として判定される可能性があるためです。

空白を除外して、実際に入力されているA列のデータだけを抽出したい場合は、条件を追加します。

E2セルの数式を次のように変更します。

アスタリスクに見える記号は、数式の中では複数条件を同時に満たすという意味で使われます。

COUNTIFの判定が0であることに加え、A列が空白ではないことを確認しています。

数式中の<>””は、空文字ではない、つまり空白ではないという条件です。

空白行が混ざる一覧では、抽出条件に空白除外を加えると結果の見やすさが大きく変わります。

また、A列に数式が入っていて表示上は空欄に見える場合もあります。

そのセルが実際に空なのか、数式の結果として空文字を返しているのかによって、扱いが異なることがあります。

数式による空欄が多い表では、実際の抽出結果を確認しながら条件を調整しましょう。

先頭と末尾の空白が原因で一致しない場合は、別列でTRIM関数を使って整形したデータ同士を比較する方法が安全です。

TRIM関数は連続する余分な空白を整理しますが、全角スペースには別の処理が必要になる場合があります。

置換機能で全角スペースを半角スペースまたは空欄へ変更してから整える方法もあります。

部署ごとに入力ルールが異なるデータを集計する場合ほど、比較前の整形が重要です。

 

UNIQUE関数との組み合わせによる重複の整理

A列に同じデータが複数回入力されている場合、FILTER関数だけでは同じ値が複数行抽出されます。

たとえば注文番号ではなく商品名を比較していると、同じ商品名が複数回並ぶこともあるでしょう。

抽出結果を1件ずつに整理したい場合は、FILTER関数の外側をUNIQUE関数で囲みます。

次の数式をE2セルへ入力します。

UNIQUE関数は、重複する値を1つにまとめて一覧表示する関数です。

この数式では、まずB列に存在しないA列の値をFILTER関数で取り出し、その結果から重複を除いています。

差分の有無だけを確認したいならUNIQUE関数、各レコードをすべて確認したいならFILTER関数だけを使うのが基本です。

商品の件数や顧客ごとの申請履歴など、同じ名称が複数あるデータでは使い分けが必要です。

重複を除くと件数の情報は失われるため、何回出現したかも確認したいときは別途COUNTIF関数で件数を表示します。

抽出結果を並べ替えたい場合は、SORT関数を組み合わせることもできます。

文字の昇順で並べるなら、SORT(UNIQUE(FILTER(…)))のように外側へSORT関数を追加します。

ただし数式が複雑になるほど、修正時に参照範囲を間違えやすくなります。

まずはFILTER関数だけで正しい結果が出ることを確認し、その後でUNIQUE関数やSORT関数を追加すると理解しやすいでしょう。

 

エクセルでA列にあってB列にないデータを抽出する方法3【詳細フィルター】

続いては、判定列を使って詳細フィルターから別の場所へ結果をコピーする方法を確認していきます。

A列 氏名	B列 照合先氏名	C列 照合結果
佐藤	鈴木	抽出対象
鈴木	田中	対象外
高橋	高橋	対象外
伊藤	山本	抽出対象

 

抽出用の判定列と条件範囲の準備

詳細フィルターは、条件に合うレコードを別の位置へコピーできるExcelの機能です。

数式結果をそのまま別表へ出力したいときや、重複しないレコード一覧を作りたいときに役立ちます。

最初にC列へ判定列を用意し、C2へ次の数式を入力します。

数式を最終行までコピーし、A列からC列の各行に照合結果を表示します。

次に、たとえばE1セルへ判定、E2セルへ抽出対象と入力します。

このE1:E2が詳細フィルターの条件範囲になります。

条件範囲の見出しは、元表のC1にある見出しと完全に同じ文字にする必要があります。

詳細フィルターでは、条件範囲の見出し名が元データの見出しと一致していないと、意図した抽出になりません。

条件範囲は元表から少し離れた場所に作ると、範囲指定を誤りにくくなります。

判定という見出しをC1に入れているなら、E1にも判定と入力します。

判定結果の表示文字は抽出対象で統一し、全角半角やスペースの違いがないように注意してください。

詳細フィルターは条件設定の仕組みを覚える必要がありますが、一度作れば同じ様式の表で繰り返し活用できます。

 

詳細フィルターによる別の場所への抽出

条件範囲を準備したら、元表のA1:C100など、見出しを含むデータ範囲内のセルを選択します。

上部リボンのデータタブを開き、並べ替えとフィルターのグループにある詳細設定をクリックします。

詳細フィルターのダイアログが表示されたら、指定した範囲を選択を選びます。

リスト範囲には、A1:C100のように見出しを含めた元表全体を指定します。

検索条件範囲には、先ほど作成したE1:E2を指定します。

抽出範囲には、結果を表示したい場所の先頭セルを指定します。

たとえばG1を指定すると、G1から右方向と下方向へ抽出結果が展開されます。

抽出範囲には見出しも出力されるため、結果表示用に十分な空きスペースを確保しておきます。

重複するレコードは無視するにチェックを入れると、同一内容の行を1行にまとめることもできます。

ただし、氏名だけではなくA列からC列までの行全体が同じ場合に重複と判断されます。

商品名だけの重複を消したい場合は、商品名だけを別の抽出対象範囲として扱う方法を検討しましょう。

詳細フィルターは、元表の更新に合わせて自動では更新されません。

データを書き換えた後は、もう一度詳細設定を開いて抽出を実行します。

更新頻度が高い表ではFILTER関数、確定した一覧を別の場所へ作成したい表では詳細フィルターという使い分けが向いています。

 

エクセルでA列にあってB列にないデータを抽出する方法4【条件付き書式】

続いては、抽出前の一覧でA列にしかないデータを色付けして確認する、条件付き書式の使い方を確認していきます。

A列 比較対象	B列 参照データ
A-101	A-102
A-102	A-104
A-103	A-105
A-104

 

数式ルールによる不一致セルの強調

条件付き書式は、セルの値や数式の結果に応じて自動的に色を付ける機能です。

差分だけをすぐ目視したい場合や、元表のレイアウトを崩したくない場合に便利です。

まずA2:A100など、色を付けたいA列のデータ範囲を選択します。

ホームタブの条件付き書式をクリックし、新しいルールを選びます。

ルールの種類では、数式を使用して、書式設定するセルを決定を選択します。

数式欄に次の式を入力してください。

書式ボタンから塗りつぶしを選び、淡い赤色や黄色など、確認しやすい色を指定します。

OKを押すと、B列に存在しないA列のセルだけが自動で色付けされます。

条件付き書式は元データを変更せず、見た目だけで差分を知らせてくれる点が特徴です。

数式中のB列の範囲にはドル記号を付け、A2だけは相対参照にします。

これにより、A3ではA3を、A4ではA4を比較対象にしながら、検索先のB列範囲は固定されます。

適用先の範囲をA2:A100にしたなら、数式の起点も先頭セルのA2に合わせる必要があります。

選択範囲がA3から始まっているのに数式をA2としてしまうと、1行ずれた判定になることがあります。

色付けだけでは抽出一覧になりませんが、担当者が画面上で確認する業務では十分な場合もあります。

 

色付け結果からのフィルター抽出

条件付き書式で色を付けた後は、フィルターの色による抽出を使って該当行だけを表示できます。

表にフィルターが設定されていない場合は、データタブからフィルターを有効にします。

A列見出しの下向き矢印をクリックすると、色でフィルターという項目が表示されます。

セルの色でフィルターを選び、設定した赤色や黄色を選択すると、色が付いたセルを含む行だけに絞り込めます。

この方法なら判定列を増やさずに、視覚的な確認から抽出まで進められます。

色によるフィルターは、数式ではなく設定した書式の結果を利用して対象行を絞り込む機能です。

条件付き書式のルールを変更すれば、フィルターの対象も連動して変わります。

ただし、手作業で同じ色を付けたセルがあると、意図しない行まで抽出される可能性があります。

差分確認専用の色は、通常の入力や注記には使わないように決めておくと安全です。

また、複数の条件付き書式ルールが重なっている表では、優先順位によって表示色が変わることがあります。

ホームタブの条件付き書式からルールの管理を開くと、適用先、数式、優先順位を確認できます。

色が付かない場合は、適用先の範囲に新しい行が含まれているかも見直しましょう。

テーブル形式に変換しておくと、データ追加時に書式や数式が自動拡張されるため、継続管理に向いています。

 

エクセルでA列にあってB列にないデータを抽出する方法5【Power Query】

続いては、大量データや定期的な照合作業に向くPower Queryを使った差分抽出について確認していきます。

比較元テーブル	比較先テーブル	結合後の結果
社員001	社員002	社員001
社員002	社員004	社員003
社員003
社員004

 

テーブル化とPower Queryへの読み込み

Power Queryは、Excel内のデータを取り込み、整形、結合、抽出するための機能です。

数千行から数万行のデータを定期的に比較する場合や、毎月同じ手順で差分表を作る場合に適しています。

まずA列側の一覧とB列側の一覧を、それぞれ見出し付きの表として準備します。

片方ずつ範囲内を選択し、挿入タブのテーブルをクリックします。

先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認してOKを押します。

テーブル名は、テーブルデザインタブからA一覧、B一覧など、意味が分かる名称へ変更しましょう。

次にA一覧のテーブル内を選択し、データタブからテーブルまたは範囲からをクリックします。

Power Queryエディターが開いたら、ホームタブの閉じて読み込むから接続の作成のみを選びます。

B一覧についても同じ操作を行います。

Power Queryでは、元データをテーブルとして準備しておくと、後から行を追加しても読み込み範囲を広げやすくなります。

列見出しに空白や重複があると操作しにくくなるため、商品コード、社員番号、顧客IDなど分かりやすい名前を付けます。

比較に使う列のデータ型も確認しましょう。

一方が文字列で、もう一方が数値になっていると、画面上の見た目が同じでも結合できないことがあります。

先頭に0があるコードは、両方ともテキスト型へそろえるのが安全です。

 

左外部結合と空白行による差分の判定

2つのクエリを準備したら、Power QueryエディターでA一覧を選択し、ホームタブのクエリのマージをクリックします。

上側のテーブルにはA一覧、下側のテーブルにはB一覧を指定します。

それぞれの比較対象となる列見出しをクリックします。

複数列をキーにする場合は、Ctrlキーを押しながら同じ順番で列を選びます。

結合の種類では、左外部を選択します。

左外部結合は、A一覧のすべてを残し、B一覧側に一致するデータがある場合だけ結び付ける方法です。

結合後に作られるB一覧の列で空白になっている行が、A列にあってB列にないデータに相当します。

Power Queryでは左外部結合を使うと、比較元にだけ存在するレコードを体系的に確認できます。

結合後の列には、テーブル形式のデータが表示されます。

列見出しの展開ボタンを押して必要な項目を展開するか、行数が0であることを条件に絞り込みます。

不要なB側の列を削除し、A側の情報だけを残せば差分一覧になります。

処理が終わったら、ホームタブの閉じて読み込むをクリックし、新しいワークシートまたは既存シートへ結果を出力します。

翌月に元テーブルへ新データを追加した後は、結果表を右クリックして更新を選ぶだけで、同じ比較手順を再実行できます。

関数のコピーやフィルター設定を毎回やり直す必要がないため、定型業務の時間短縮につながります。

 

まとめ エクセルでB列にないA列データを抽出する方法

エクセルでA列にあってB列にないデータを抽出するには、データ量、Excelのバージョン、結果をどのように使いたいかに応じて方法を選ぶことが大切です。

古いExcelを含めて幅広く使いたい場合は、COUNTIF関数で判定列を作成し、フィルターでB列にない行だけを表示する方法が基本になります。

Microsoft 365やExcel 2021以降では、FILTER関数を使うと、差分リストを別の場所へ自動表示できます。

重複のない一覧が必要ならUNIQUE関数を組み合わせると便利です。

画面で差分を確認したいだけなら、条件付き書式でA列の不一致セルを色付けし、色によるフィルターを使う方法が向いています。

大量データや定期的な照合には、更新だけで再実行できるPower Queryが役立ちます。

どの方法でも、比較前に空白、全角半角、先頭の0、データ型をそろえることが、正確な抽出の土台になります。

まずは小さなサンプルデータで結果を確認し、自分の業務に合う手順をテンプレート化していきましょう。