今日はこんなお困りごとを解決します。
Excelエクセルで「VLOOKUP関数を使ってデータを比較して重複を探す方法」を紹介していきますよ。
- CASE1：B列とD列を比較して重複があるかを確認する
- CASE2：B列とE列で重複を探して、B列のとなりのデータ（重複一致したデータの同行データ）を取り出してくる
こんなシチュエーションで役立つ
● 〇列に✕列のデータがあるかないかを調べたい
● 〇列と✕列に一致するデータはあるかを知りたい
● 指定した範囲の中に指定したデータがあるかないかを確認したい
● 一方のデータにもう一方のデータが含まれているかを知りたい
● 特定の範囲の中からデータを探して指定したデータの隣接データを抽出したい
● 検索値を探す列が「指定範囲」のなかで最左端列、もしくは「列番号（取り出したいデータ）」より左列になければ使うことができない
● 縦に並ぶデータのみに使える、横並びのデータには使えない
VLOOKUP関数の公式
本題に入る前に、まずVLOOKUP関数の公式を確認しておきましょう。
= VLOOKUP（検索値 , 範囲 , 列番号 [ , 検索方法 ]）
●検索値・・・検索する値
●範囲・・・検索をかける範囲
●列番号：取り出したいデータが範囲中の左から数えて何列目か
●検索方法：検索を完全一致であるなら「0」または「FALSE」。近似値検索(※)にするなら「1」または「TRUE」または何も記述せず省略
※近似値検索とは…検索値以下で最も大きい値を検索する
VLOOKUPは、指定した「範囲」の最左列から「検索方法」に従って「検索値」を探し一致したデータと同行かつ指定した「列番号」に位置する値を抽出するものです。
VLOOKUPは「検索値を探す範囲が一番左の列になければならない」「縦の列方向に並んだデータにしか使えない」点に注意。
VLOOKUP関数で2つのデータ群を比較して重複を探す方法
CASE1：VLOOKUP関数でB列とD列を比較して重複があるか確認する
D列データがB列にあった場合は、B列データをE列に返すことで重複ありなしを判定する方法です。
例題）D列の参加者がB列のエントリー名簿にいるかいないかを確認したい
（E列にVLOOKUP関数を入力して重複判定していく）
重複がある場合は、D列と同じ名前が返ってきて、
重複がない場合は、E列に返す値が存在しないので「#N/A（エラー値）」で返ってきます。
- E列に名前が返ってくる ➔ 重複あり
- E列にエラー値が返ってくる ➔ 重複なし
1E列先頭にVLOOKUP関数を入力していく
「検索する値＝D2」「検索する対象の範囲＝B列」「取り出してきたいデータ＝1列目」「検索方法は完全一致でいいので＝0」と入力すればOK。
したがって、セルE2に入力する関数は「=VLOOKUP(D2,B:B,1,0)」となる
2セルE2の関数を下段までオートフィルでコピーする（フィルダウン）
※オートフィルとは… 簡易コピーの機能。コピーしたいセル右下にカーソルを合わせ「+」が表示された状態で左クリックを押し、そのまま任意のセルまでカーソルを動かすことで自動コピーが可能。
3E列に返ってきた値がB列と一致する名前なら「重複あり」、エラー値が返ってきていれば「重複なし」と判断できる
これで、D列参加者がエントリー名簿にいるかいないかを確認することができましたね！
愛染くんや不二くんはエントリー名簿の中にはいなかったということになります。
飛び入り参加だったのかな？笑
CASE2：B列とE列で重複を探してB列のとなりのデータ（重複一致したデータの同行データ）を取り出してくる
CASE1では、ただ重複しているかを知りたかったので「列番号＝1」で一致データそのものを取り出しましたが、列番号の指定を変えれば隣接したデータも取り出すことができます。
「列番号=2」➔1つ隣のデータが取り出せる、「列番号=3」➔2つ隣のデータが取り出せる
例）E列データがB列にあれば隣のC列を取り出してくる
1 F列にVLOOKUP関数を入力していく
「検索する値＝E2」「検索する対象の範囲＝B2:C8の絶対参照」「取り出してきたいデータ＝2列目」「検索方法は完全一致でいいので＝0」と入力すればOK。
※絶対参照とは・・・指定範囲を固定すること。絶対参照については本記事【便利な小ワザ②】で書いてます
従って、セルE2に入力する関数式は「=VLOOKUP(E2,$B$2:$C$8,2,0)」となる
2 セルF2に入力した関数式をオートフィル機能で下段までコピーする
3 名簿（B列）にいる人には「登録ナンバー」が表示され、名簿にいない人はエラー値（#N/A）になりました
便利な小ワザ①：「#N/A」のエラー値を違う文字に置き換えて表示させる
VLOOKUP関数などで、返ってきたエラー値「#N/A」を違う文字に置き換えて表示させることもできます。
方法は大きく２つあります。
● エラー値を別の文字に置き換える方法
- IFERROR関数を使う方法
- IF関数とISNA関数を使う方法
IFERROR関数は、Excel2007以降のバージョン（Excel2007/2010/2013/2016バージョン）でしか使えないのでそれ以前のバージョンの方はIF + ISNA関数の方法を使ってくださいね。
IFERROR関数を使う方法
=IFERROR（値 , エラーの場合の値 ）
●「値」・・・エラー値を算出した元の関数式（計算式でもOK）
●「エラーの場合の値」・・・エラーが返ってくる際に代わりに置き換えたい文字
このIFERROR関数を使うと、値がエラーになる場合は「エラーの場合の値」に置き換えて表示し、エラーにならない場合はそのまま「値」を表示します。
CASE1を使って実際にやってみたいと思います。
CASE1で使った関数式は「=VLOOKUP(D2,B:B,1,0) 」でしたね。この式にIFERROR関数を組み合わせてみたいと思います。
1E列にIFERRORとVLOOKUPを組み合わせた以下の関数式を入力していきます
=IFERROR(VLOOKUP(D2,B:B,1,0),"✕")
関数内で文字を指定する場合は文字列を「"（ダブルクウォーテーションマーク）」で文字を囲みましょう
2E列に、名簿にある人は「名前（値）」が入り、名簿にない人には「✕」が添えられるようになりました
IF関数とISNA関数を使う方法
Excel2007以降のバージョンを使っている人はこの方法を使いましょう。
IF関数は、「論理式」で指定した条件を満たすときは「真の場合」を条件を 満たさない場合は「偽の場合」を表示させる関数。
ISNA関数は、「テストの対象」がエラー値（#N/A）ならTRUEと表示し エラー値じゃない場合はFALSEと表示させる関数。
この2つの関数を組み合わせることで、指定の関数式がエラー値のときとそうじゃない時に表示される文字を出し分けすることができます。
=IF（論理式 , 真の場合 [, 偽の場合] ）
● 論理式・・・真偽を判定するための関数式または計算式
● 真の場合・・・論理式で指定した条件を満たす場合に表示する値、または関数式・計算式
● 偽の場合・・・論理式で指定した条件を満たさない場合に表示する値、または関数式・計算式
=ISNA（テストの対象）
● テストの対象・・・エラーを判定したい値、または式
これもCASE1を使ってやってみましょう。
12つの関数式を組み合わせた以下の式をE列へ入力する
=IF(ISNA(VLOOKUP(D2,B:B,1,0)),"✕",VLOOKUP(D2,B:B,1,0))
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キーを押す回数によって絶対参照の種類が切り替わるようになってます
絶対参照の仕組みや使い方など詳しくはこちらの記事をどうぞ。
Excel（エクセル）でよく目にする「$（ドルマーク）」は何？？これは【絶対参照】でセルやセル範囲などセル参照を固定している印です。本記事では、①絶対参照とは？②相対参照との違いは③＄マークの使い方、便利なショートカットキーを紹介しています。絶対参照をこれから学びたいという方にぴったりの初級編です。
WordPress初心者さんにおすすめの本（Amazonレビュー：）
