1_アイキャッチ_ExcelでVLOOKUP関数を使ってデータ比較・重複を探す方法_

 

Q. Excelで「A列とB列に重複データはあるか」を調べるには?


今日はこんなお困りごとを解決します。

Excelエクセルで「VLOOKUP関数を使ってデータを比較して重複を探す方法」を紹介していきますよ。

本記事でわかること
  • CASE1:〇列と✕列を比較して重複があるかを確認する
  • CASE2:〇列と✕列で重複を探したうえで、重複一致したデータ(またはその同行データ)を取り出してくる

1_001_本記事できることCASE1

1_002_本記事できることCASE2

このVLOOKUP関数を使った重複を調べる方法では以下のシチュエーションで役立ちます。

〇列に✕列のデータがあるかないかを調べたい
〇列と✕列に一致するデータはあるかを知りたい
指定した範囲の中に指定したデータがあるかないかを確認したい
一方のデータにもう一方のデータが含まれているかを知りたい
特定の範囲の中からデータを探して指定したデータの隣接データを抽出したい

 

どれも同じようなことを言い換えただけですが、

このパターンに当てはまる場合はVLOOKUP関数が使えるのでこの記事をご参考ください。

注意する点

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

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

 

VLOOKUP関数の公式

本題に入る前に、まずVLOOKUP関数の公式を確認しておきましょう。

把握している人は次項へスキップしてOK!

VLOOKUP関数の公式

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

検索値・・・検索する値
範囲・・・検索をかける範囲
列番号:取り出したいデータが範囲中の左から数えて何列目か
検索方法:検索を完全一致であるなら「0」または「FALSE」。近似値検索(※)にするなら「1」または「TRUE」または何も記述せず省略

※近似値検索とは…検索値以下で最も大きい値を検索する

VLOOKUPは、指定した「範囲」の最左列から「検索方法」に従って「検索値」を探し一致したデータと同行かつ指定した「列番号」に位置する値を抽出するものです。


上で注意点としてあげたように、

「検索値を探す範囲が一番左の列になければならない」「縦の列方向に並んだデータにしか使えない」点に気をつけましょう。

 

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

CASE1:VLOOKUP関数で〇列と✕列を比較して重複があるか確認する

例題を使って説明していきたいと思います。

例)D列の参加者がB列のエントリー名簿にいるかいないかを確認したい

(また、重複の確認は、E列にVLOOKUP関数を入力して判定していく)

1_003_CASE1

この場合、D列のデータがB列にあった場合は、その一致したデータそのものをE列に返すようにすれば重複があるかどうかがわかります。

重複がある場合は、D列と同じ名前が返ってきて、

重複がない場合は、E列に返す値が存在しないので「#N/A(エラー値)」で返ってきます。

  • E列に名前が返ってくる ➔ 重複あり
  • E列にエラー値が返ってくる ➔ 重複なし

 

Excel実践編

1E列にVLOOKUP関数を入れるするために、まずはセルE2に関数を入力していく

「検索する値はD2」「検索する対象の範囲はB列」「取り出してきたいデータは1列目」「検索方法は完全一致でいいので0」と入力すればOK。

したがって、セルE2に入力する関数は「=VLOOKUP(D2,B:B,1,0)」となる

2セルE2の関数を下段までオートフィルでコピーする(フィルダウン)

※オートフィルとは… 簡易コピーの機能。コピーしたいセル右下にカーソルを合わせ「+」が表示された状態で左クリックを押し、そのまま任意のセルまでカーソルを動かすことで自動コピーが可能。

1_005_関数のコピー

3E列に返ってきた値がB列と一致する名前なら「重複あり」、エラー値が返ってきていれば「重複なし」と判断できる

1_006_重複判定

これで、D列参加者がエントリー名簿にいるかいないかを確認することができましたね!

愛染くんや不二くんはエントリー名簿の中にはいなかったということになります。

アシスタント(羊)

飛び入り参加だったのかな?笑

 

CASE2:〇列と✕列で重複を探し重複一致したデータ(またはその同行データ)を取り出してくる

今度は、VLOOKUP関数で重複を探したうえで一致したデータと同じ行のデータを取り出してくるパターンです。

CASE1では、ただ重複しているかだけを知りたかったので、取り出すデータを「列番号= 1(指定範囲の1行目・一致したデータそのもの) 」として対応しましたが、

今度は列番号を「2,3,4・・・」と隣接したデータを取り出してきます。

例)「E列のデータがB列にあれば隣のC列を取り出してくる

1_016_登録ナンバー判定

 

Excel実践編

1 F列にVLOOKUP関数を「セルE2のデータがB列にある場合に指定範囲の2列目データを取り出すという」という条件で入力していく

・検索値はセルE2
・範囲は「B2:C8」 または 「B:C」
 (絶対参照※にしておくといかなる場合も範囲が固定されるのでおすすめ)
・列番号は2
・検索方法は完全一致検索でいいので0   とすると、

※絶対参照については本記事【便利な小ワザ②】で書いてます

セルE2に入力する関数式は「=VLOOKUP(E2,$B$2:$C$8,2,0)」となる

1_014_VLOOKUP関数の入力

3 セルF2に入力した関数式をオートフィル機能で下段までコピーする

1_015_関数コピー

3 名簿にいる人には「登録ナンバー」が表示され、名簿にいない人はエラー値(#N/A)になりました

1_016_登録ナンバー判定

絶対参照がわからない人は、このパターンでは使わなくても大丈夫です!

 

便利な小ワザ①:「#N/A」のエラー値を違う文字に置き換えて表示させる

VLOOKUP関数などで、返ってきたエラー値「#N/A」を違う文字に置き換えて表示させることもできます。

方法は大きく2つあります。

エラー値を別の文字に置き換える方法

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

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

 

IFERROR関数を使う方法

IFERROR関数の公式

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

「値」・・・エラー値を算出した元の関数式(計算式でもOK)
「エラーの場合の値」・・・エラーが返ってくる際に代わりに置き換えたい文字

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

 

CASE1を使って実際にやってみたいと思います。

CASE1で使った関数式は「=VLOOKUP(D2,B:B,1,0) 」でしたね。この式にIFERROR関数を組み合わせてみたいと思います。

Excel実践編

1E列にIFERRORとVLOOKUPを組み合わせた以下の関数式を入力していきます

=IFERROR(VLOOKUP(D2,B:B,1,0),"✕")

1_007_IFERRORで文字置き換え

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

 

2E列に、名簿にある人は「名前(値)」が入り、名簿にない人には「✕」が添えられるようになりました

1_008_IFERRORで文字置き換え_2

 

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

Excel2007以降のバージョンを使っている人はこの方法を使いましょう。

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

ISNA関数は、「テストの対象」がエラー値(#N/A)ならTRUEと表示し エラー値じゃない場合はFALSEと表示させる関数。

この2つの関数を組み合わせることで、指定の関数式がエラー値のときとそうじゃない時に表示される文字を出し分けすることができます。

IF関数の公式

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

論理式・・・真偽を判定するための関数式または計算式
真の場合・・・論理式で指定した条件を満たす場合に表示する値、または関数式・計算式
偽の場合・・・論理式で指定した条件を満たさない場合に表示する値、または関数式・計算式

ISNA関数の公式

=ISNA(テストの対象)

テストの対象・・・エラーを判定したい値、または式

 

これもCASE1を使ってやってみましょう。  

Excel実践編

12つの関数式を組み合わせた以下の式をE列へ入力する

=IF(ISNA(VLOOKUP(D2,B:B,1,0)),"✕",VLOOKUP(D2,B:B,1,0))

1_009_ISNAで文字置き換え

2エントリー名簿にある人の欄には名前が、名簿にない人には「✕」が添えられるようになりました

1_010_ISNAで文字置き換え_2

IFERRORで出した結果と同じようになっています。

どちらを使っても大丈夫です!使いやすい方を使ってくださいね。

 

便利な小ワザ②:絶対参照を使って特定の範囲を固定指定する

関数式内で指定する範囲を固定したい場合は絶対参照を使いましょう。

範囲を絶対参照にするとオートフィルで関数式をコピーしたときにも指定の範囲がずれることがなくなります。

例えば、CASE2で「=VLOOKUP(E2,B2:C8,2,0)」とした場合、関数をオートフィルコピーしたときに範囲指定は「B3:C9」「B4:C10」「B5:C11」どんどんズレていってしまいます。これはExcelの仕組み上仕方ないことです。

一方で以下のように範囲を「$マーク」で囲んで絶対参照にすると
「=VLOOKUP(E2,$B$2:$C$8,2,0)」
関数式をコピーした場合にも指定範囲は固定され何をしてもズレることはありません。

絶対参照の$マークの付け方

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

F4キーを押す回数によって絶対参照の種類が切り替わるようになってます

1_012_絶対参照の使い分け

絶対参照の仕組みや使い方など詳しくはこちらの記事をどうぞ。

 

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