・vlookupで1番目のデータしか抽出できなくて困っている…
・データ内に重複があるため、重複を一覧化して削除をしたい…
今回はvlookupの弱点でもある「○番目の○○さんを出力する」「重複を表示する」
という悩みについて解説をしていきます！
yuki
これができればExcel中級者です！と胸を張れると思います。
少し複雑な処理もするのですが、頑張って理解してみてください！
1番目ではなく2番目の鈴木さんを表示する【やり方は3ステップ！】
まずは１つ目の悩み「○番目の○○さんを出力する」をやっていきたいと思います。
論より証拠、ということでまずはどんな手順で作っていくかをご紹介します。
画像の通り、今回は3ステップで関数を組んでいきます。
後で振り替えれるようにスプレッドシートを公開しています。
スプレッドシートはこちらからアクセスをして見比べながら読んでみて下さい。
countifで何番目に存在するデータなのかを特定する
concatで一意のシリアル値をふる
vlookupで出力をする
さて、それぞれのステップを確認していきましょう。
ステップ１：何番目に存在するデータなのかを特定する
まずは「鈴木」が複数あった場合、vlookupでは「これは2番目の鈴木さんだ」という判別ができないため、
1番目の鈴木さん、2番目の鈴木さん、という形で判別できるように関数を組みます。
countif関数を使うのですが、少し工夫が必要です。
ポイントは、
1番目の鈴木さんがでてきたときには「１」
2番目の鈴木さんがでてきたときには「２」
を振る必要があるからです。
この場合、下記のようにするとうまくいきます。
=COUNTIF($C$3:C3,C3)
=COUNTIF($C$3:C4,C4)
=COUNTIF($C$3:C5,C5)
…
countifの参照範囲を先頭行のみ固定し、カウントしていきます。
範囲が下の行にいけばいくほど範囲が拡張するため、1番目の鈴木さんが出現したときには
1番目の鈴木さんが含まれる範囲までをカウント、2番目のときは2番目の鈴木さんが含まれる範囲までをカウント
していくことができます。
これで、性のデータに対して、何番目に存在するデータなのかを特定することができました。
ステップ２：concatで一意のシリアル値をふる
ステップ1では、何番目なのか、まで出力したので、vlookupで検索値を指定できるように
「○番目の○○さん」となるように整形をしていきます。
今回はconcat関数を使っていますが、「&」「CONCATENATE」でもできますので使い慣れているものを使ってみて下さい。
結果はこのようになります。
これでvlookupの検索値を作ることができました。最後に出力をしていきましょう。
ステップ３：vlookupで出力をする
最後は出力になります。
わかりやすくするために、検索する値を設定して検索をしてみました。
=VLOOKUP(G7&G3,B:E,3,false)
（2番目の鈴木さんを検索値として指定して名を出力している）
ここまでできたら、鈴木さん一覧を出力することもできます。
「○番目の」という部分を可変にしてあげることで鈴木さん一覧を出力することができます。
これで重複しているデータをvlookupで出力することができました。
重複を表示する
ここまで使った関数を元に重複表示をしていきたいと思います。
後で振り替えれるようにスプレッドシートを公開しています。
スプレッドシートはこちらからアクセスをして見比べながら読んでみて下さい。
例題：性が重複している人すべてを抽出したい
今回はスプレッドシートの独自関数「filter」を使っていきたいと思います。
filter関数とは、ある範囲に対して、ある条件を指定し、その条件に一致したもののみ抽出できるとても便利な関数です。
指定方法は
=FILTER（範囲 , 条件1 , 条件2 , …）
になり、条件の中に数式を書き込むことで指定ができます。
今回の場合は、
=FILTER(C:E,A:A>1)
となります。
（CからE列を抜き出して下さい。ただし、A列の中から、1より大きいことが条件です。という指定をしています。）
yuki
これだけで出力できてしまうのでめちゃくちゃ便利です。
重複以外にも条件指定で様々なデータを一括で表示できたりします。
これで重複を表示することができました。
まとめ
今回は重複したデータをvlookup関数、filter関数を使って抽出していく方法を紹介しました。
関数の組み合わせは無限大にあるため、一つ一つ覚えていくのに時間はかかりますが、
何回も繰り返し繰り返しチャレンジしてみると、いつのまにか使えるようになっていきます。
難しい！と感じた人はぜひチャレンジし続けてみてください。
