ExcelでVLOOKUP関数を使ってデータ比較・重複を探す方法

Excel(エクセル)でデータ処理をしていて、「A列にB列のデータがあるかないか」「A列とB列に重複データはあるか」調べたい時はありませんか?

それは、VLOOKUP関数を使うと簡単にできます。

VLOOKUP関数は「指定したデータが範囲の中にあるかどうか」を調べることができます。そしてこんなパターンの問題を解決することができるんです。

  • 2つのデータ列を比較して重複があるかどうかを調べたい
  • 指定した範囲の中に指定したデータがあるかないかを確認したい
  • 一方のデータにもう一方のデータが含まれているかを知りたい
  • 範囲の中からデータを探して指定データの隣の列のデータを抽出したい

だいたい同じようなことを言い換えただけですが、やりたいことがこの中にある場合はVLOOKUP関数が使えるのでこの記事を見ていってくださいね。

本記事の内容

  • 「2つのデータ列を比較して重複を探したい」問題をVLOOKUP関数を使って、例題を用いて解決する
  • VLOOKUP関数応用パターンを解説する

本題に入る前に、まずVLOOKUP関数の公式を少しだけおさらいしておきましょう!

VLOOKUP関数の公式

= VLOOKUP(検索値 , 範囲 , 列番号 [ , 検索方法 ])

指定した「範囲」の左端の列で「検索方法」に従って「検索値」を検索して一致した行から「列番号」で指定した位置の値を抽出する


 検索方法とは
0」または「FALSE」:完全一致で検索する
「1」または「TRUE」または「省略」:「検索値」以下で最も大きい値を検索する


 注意

検索値を探す列が「指定範囲」のなかで最左端列、もしくは「列番号(取り出したいデータ)」より左列になければ使うことができない

縦に並ぶデータのみに使える、横並びのデータには使えない

 

VLOOKUP関数を使えない例としてはこんなパターンがあります。

VLOOKUP関数を使用できない例

  • 検索値を探す列が「範囲」の中で左端列にない
  • 対象のデータが横方向に並んでいて、横方向に検索して該当する列から値を抽出したい場合

以上を念頭においてさっそくVLOOKUP関数で重複を探してみましょう~!

VLOOKUP関数で2つのデータ群を比較して重複を探す

VLOOKUP関数でD列・B列を比較して重複があるかないか確認する

例題
D列に一覧されている参加者がB列のエントリー名簿にいるかいないかを確認したい

VLOOKUP関数で2つのデータ群を比較して重複を探す(例のイメージ)

入力した数式

=VLOOKUP(D2,B:B,1,0)

公式にあてはめると
検索値 = D2
範囲 = B:B
列番号 = 1
検索方法 = 0(完全一致で検索)

さらにこの式を言葉で表すと
「D列のデータをB列から完全一致で探して、データがあれば範囲B:Bの1列目のデータを返してください」という指示式になっています。

セルE2に上の数式を入れたらあとはオートフィル(フィルダウン)でE8まで数式をコピーしちゃいます。
VLOOKUP関数でデータの重複を調べる

 

範囲B:Bの1列目はB列なので、E列にはB列のデータが返ってきます。

オートフィル機能で関数をコピー

#N/Aってなに?失敗?大丈夫です、正常反応です。VLOOKUP関数では探したデータに該当がなければエラーを返す仕組みになっています。

「#N/A」はエラー値なんですね。この文字が返ってきたということは、つまり指定した範囲の中に指定したデータが存在しなかった・重複なしということになります。

#N/A = エラー値 = 指定したデータは重複がなかった

今回の例題にそって言えば、
愛染健十くんや不二周助くんはエントリー名簿の中にはいなかったということになります。飛び入り参加だったんでしょうか?

#N/Aを違う文字の置き換えで表示させたい

このエラー値#N/Aを違う文字に置き換えて表示させたい場合は、次のような方法があります。

  • IFERROR関数を使う方法
  • IF関数とISNA関数を使う方法

IFERROR関数は、Excel2007以降のバージョン(Excel2007/2010/2013/2016バージョン)でしか使えないのでそれ以前のバージョンの方は②を使ってくださいね。

IFERROR関数を使う方法

IFERROR関数の公式

=IFERROR(値  , エラーの場合の値 )

エラーの場合の値とは:
エラー時に置き換えたい文字を入力する

このIFERROR関数を使うと、「値」がエラーになる場合は「エラーの場合の値」に置き換えて表示して、エラーにならない場合はそのまま「値」を表示します。また「値」は関数式でも計算式でもOKです。

さっき上で使った数式は
=VLOOKUP(D2,B:B,1,0) でしたね?

この式にIFERRORを使うとして、「エラーの場合の値」を今回は「」にしてみましょう、公式にあてはめると・・・

=IFERROR(VLOOKUP(D2,B:B,1,0),"×")
エラー表示を違う文字の置き換えで表示させる方法(IFERROR関数を使った方法)

 関数内で文字を指定する場合は文字列を「””(ダブルクウォーテーションマーク)」で囲みましょう

これでエントリー名簿にある人はちゃんと名前が入り、名簿にないひとには「×」が添えられるようになります
エラー表示の置換(IFERRORを使った方法)

IF関数とISNA関数を使う方法

IF関数の公式

=IF(論理式 , 真の場合  [, 偽の場合] )

「論理式」で指定した条件を満たすときは「真の場合」を、条件を満たさない場合は「偽の場合」を表示する

ISNA関数の公式

=ISNA(テストの対象)

「テストの対象」がエラー値(#N/A)ならTRUEと表示され、エラー値じゃなければFALSEと表示する

この2つの関数式を組み合わせて以下の形の式にして使います

=IF(ISNA(テストの対象), " 真の場合 " , " 偽の場合 " )

 

そして上の式に、さきほどと同じようにVLOOKUP(D2,B:B,1,0) の数式を当てはめると・・・

=IF(ISNA(VLOOKUP(D2,B:B,1,0)),"×",VLOOKUP(D2,B:B,1,0))
エラー表示を違う文字の置き換えで表示させる方法(IF関数とISNA関数を使った方法)

すると、エントリー名簿にある人の欄には名前が、名簿にない人には「×」が添えられるようになりました
エラー表示の置換(IF関数・ISNA関数を使った方法)

で出た結果と同じようになっていますね?どちらを使っても大丈夫です!使いやすい方を使ってくださいね。

範囲を列ではなくエリア指定する場合は

さきほど紹介した例では、関数式は「=VLOOKUP(D2,B:B,1,0)」のように指定範囲を列で指定しましたが、ピンポイントで範囲をエリア指定することもできます。エリア指定する場合は以下のように指定します。

=VLOOKUP(D2,$B$1:$B$9,1,0)

「$」はなに?と思いますよね。
これはオートフィル機能で関数式をコピーするとき指定範囲をずらしたくない場合に使う範囲固定のマークで、これを「絶対参照」といいます。

 

絶対参照で指定したセルやセル範囲はいかなる場合にも固定されます。

$マークのつけ方は

指定したい範囲を選択した状態で
キーボードの「Fn」+「F4」キーを押す

B1:B9」  →  「$B$1:$B$9」 

そうすることによって、オートフィル機能で下方向・横方向に関数式をコピーした場合にも指定した範囲はずれることはありません。

ちなみに、例題①のB列指定でB:Bとだけ指定したのは、オートフィルで下にコピーして参照がずれでも縦方向にずれるだけなので列指定の場合は$があってもなくても変わらないからですね。

VLOOKUP関数で重複を探したうえで一致したデータの隣接データを取り出してくる

例題①ではデータを比較して重複しているかどうかだけを判定したかったので、取り出すデータとして列番号を「1」とし一致したデータそのものを取り出してきていました。(指定範囲の1行目を取得)

今度は、少し応用してVLOOKUP関数で重複を探したうえで一致したデータの隣接データを取り出してくる例を紹介したいと思います。

例えば「VLOOKUP関数でE列のデータがB列にあれば隣のC列を取り出してくる」といったパターンです。

例)D列の参加者がB列のエントリー名簿にいるかいないかを判定して該当があれば隣の登録メンバーを返したい、また名簿にいなければ空欄にしたい
IFERROR関数とVLOOKUP関数を使った応用編

検索値が一致したときに、今度はデータ範囲の2列目を取り出しくるかたちにすればいいので・・・

以下の要素を公式にあてはめると
検索値 = E2
範囲 = $B:$C(B:Cの絶対参照)
列番号 = 2
検索方法 = 0(完全一致で検索)
エラーの場合の値 = ""(空白)

=IFERROR(VLOOKUP(E2,$B:$C,2,0),"")
IFERROR関数とVLOOKUP関数を使った応用編2

ちなみに、この式を言葉で表すと
「E列のデータをB列から完全一致で探して、データがあれば絶対参照B:Cの2列目のデータを返してください」という指示式になっています

範囲B:Cの2列目はC列なので、F列にはC列のデータが返ってきます。

あとはセルE2に入力した関数式をオートフィル機能でセルE8までコピーすればOK
IFERROR関数とVLOOKUP関数を使った応用編3

ちゃんと名簿にいる人には「登録ナンバー」が表示され、名簿にいない人は「空欄」になっていますね

また絶対参照した範囲$B:$Cは、エリア指定で「$B$1:$C$9」と指定してもOK。最終的に表示されるデータに変わりはありません!


いかがでしたか?VLOOKUP関数の使い方と重複チェック&削除する方法はこちらでも解説しています。

WordPress初心者さんにおすすめの本(Amazonレビュー: