• 公開日:
  • 更新日:

エクセルで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セルのフィルハンドルをドラッグして下の表にも関数をコピーします。

A列にしか存在しない値をC列に抽出できた

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

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

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

別シートに表を2つ用意する

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

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に存在しないデータを抽出できた

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

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

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

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

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

上記の画像は、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関数を使った数式をお使いください。