エクセルでA列にあってB列にない値を抽出する方法

この記事では、エクセルでA列にあってB列にない値を抽出する方法をご紹介します。

データの比較を手作業で行うのは大変ですが、関数を使うとA列とB列の値に相違がないことや、値に重複がないことが簡単に確認できます。

以下では、関数を組み合わせてA列にあってB列にない値を別の列に表示する方法と別シートの表の値を比較する方法をご紹介します。

エクセルでA列にあってB列にない値を抽出する方法

A列にあってB列にない値を抽出する際、手作業で確認するのは大変なので関数を組み合わせてA列にあってB列にない値を別の列に表示する方法をご紹介します。

A列に対象のデータ、B列に比較用のデータ、C列に作業用の列を追加した表を用意します。

C2セルに「=INDEX(A:A,SMALL(IF(COUNTIF(B:B,A$2:A$8),9999,ROW(A$2:A$8)),ROW(C1)))&””」を入力し、Enterを押します。数式の詳細については下記「A列にあってB列にない値を抽出する数式について」セクションをご参照ください。

C2セルに検索結果が表示されました。C2セルのフィルハンドルをドラッグして下の表にも関数をコピーします。

C1セルからC8セルに検索結果が表示されました。表示された「d」「e」「f」「g」の値がA列にあってB列にない値です。

別シートの値を比較して片方のシートにない値を抽出する方法

以下では、関数を組み合わせて片方のシートにない値を抽出する方法をご紹介します。

上記の画像のような表を二枚のシートに用意します。左の表がSheet1で、右の表がSheet2です。

表の最終行を確認して、データ数の多いシート（例：Sheet2) に作業用の列（例：C1セルからC8セル）を追加します。

C2セルを選択し、「=VLOOKUP(A2,Sheet1!A:A,1,FALSE)」を入力し、Enterを押します。

Sheet2のA2セルの値をSheet1のA列全体で検索して、完全に一致する値を一列目から取り出すという意味です。

「VLOOKUP関数」は指定した条件のデータを縦方向に検索する関数です。

書式は「=VLOOKUP（検索値,範囲,列番号,検索の型）」です。

C2セルに検索結果が表示されました。

C2セルのフィルハンドルをドラッグして下の表にも関数をコピーします。

C1セルからC8セルに検索結果が表示されました。

「#N/A」と表示された7行目と8行目の値がSheet1には存在しない値です。

①見出し行（例：C1セル）、②データタブ、③フィルターの順に選択します。

①C2セルのプルダウンを選択し、②「すべて選択」チェックボックスのチェックを外し、③「#N/A」チェックボックスにチェックを入れます。

④OKボタンを押します。

フィルター機能を使ってSheet1に存在しない値を抽出することができました。

A列にあってB列にない値を抽出する数式について

上記「エクセルでA列にあってB列にない値を抽出する方法」セクションで使用している数式は、A列にあってB列にない値をC列に表示する方法をご紹介しています。

以下ではそれぞれの数式の詳細をご紹介します。数式をアレンジする際の参考にしてください。

上記の画像は、A列にあってB列にないデータを抽出する数式の解説です。

①のINDEX関数とSMALL関数の組み合わせで、SMALL関数内の②の数式で指定された行番号にある値を上から順番にINDEX関数でC列に抽出します。

INDEX関数の書式は「INDEX(参照,行番号,[列番号],[領域番号])」、SMALL関数の書式は「SMALL(範囲,順位)」です。

②はIF関数とCOUNTIF関数の組み合わせで、A列の値がB列に含まれていれば9999、含まれていない場合はROW関数で行番号を返します。INDEX関数で行番号に対応するA列の値をC列に抽出します。9999はダミー値なので、対象データや比較データのデータ数より大きい値であれば他の数値でも構いません。

③はROW(C1)と入力することで、SMALL関数を使って何番目に小さい値を抽出するかを指定しています。下にコピーすることで、2行目、3行目とずれていくので対応する行の値を順番に抽出することができます。

④は該当しない行を空白で表示するために追加していますが無くても構いません。「&""」がない場合は該当しないセルに「0」が表示されます。

もっと簡単な数式にしたい場合はFILTER関数を使った以下の数式でも、同じようにA列にあってB列にないデータを抽出できます。

=FILTER(A$2:A$8,ISNA(MATCH(A$2:A$8,B:B,0)))

ただし、FILTER関数はOffice365でのみ使用できる関数ですので、買い切り型のExcelをお使いの場合は上記INDEX関数を使った数式をお使いください。