今回はvlookupを使って、
「並べ替えをしながらデータを抽出したい」
「並べ替えられていないデータから値を抽出したい」
「複数の条件を抽出したいけどvlookupより簡単に抽出したい」
といった課題に対して解説をしていきます。
LARGE関数とVLOOKUP関数を組み合わせる
※サンプルデータを無料公開しています。こちらをみながら読んで頂くと理解しやすいと思うので、ぜひ見てみて下さい。
一番簡単かつ使いやすい関数から解説をします。
今回は下のようなデータを例題として扱います。
データの中で購入金額が高い10人の性を抽出して下さい
この場合、使う関数はVLOOKUPとLARGE関数です。
LARAGE関数＝大きい順に並び替えてくれる関数
ですので、購入金額が高いものをLARGE関数を使って抽出をして、
VLOOKUP関数でその購入金額の人を出力する、という方法を取ります。
まずはLARGE関数を使って購入金額が高いもの上位10個を抽出してみましょう。
=LARGE( $E$2:$E$24 , H3 )
※LARGE関数の指定の仕方：
＝LARGE（対象となるデータ範囲 , 順位を知りたいデータ）
このようにLARGE関数で購入金額が高いもの10個を抽出できました。
あとはこれをVLOOKUP関数で抽出すれば終了です。
（とてもシンプル…）
注意点は、VLOOKUP関数の検索値は最左値を指定しなければ、ならないため
性を購入金額列の右に持ってくるようにしましょう。
最終的なVLOOKUPの関数はこちらです。
=VLOOKUP(LARGE($E$2:$E$24,$H3),$E$2:$F$24,2,FALSE)
vlookup関数の基本
いや、まだVLOOKUP関数すら怪しい…そんな人はこちらの記事を読んでみて下さい。
Excelの初心者でvlookupの使い方がよくわからない…課題に直面しているけど、周りに聞く人もいないし…でも解決できない。 今回は、Excel／スプレッドシートで使うvlookup関数を解説していきます。 基本〜少し …
vlookup関数を一言でいうと、
「任意の値を指定の範囲から検索できる」関数です。
そのため、関数内では、
・任意の値（検索したい値）
・指定の範囲とその列
を指定して上げる必要があります。
基本はマストで覚えておくと色々と応用がききます。
vlookup関数以外の並び替え方法
今回は並べ替え方法をvlookup関数以外でもご紹介したいと思います。
indexとmatch関数を使う
※vlookup関数の例題と同じくサンプルデータを無料公開しています。
まずはindexとmatch関数です。
例題はVLOOKUP関数と同じものを扱います。
データの中で購入金額が高い10人の性を抽出して下さい
詳しくは別記事でまとめているので、今回は例題の回答のみを解説していきますね。
今回は、 ・vlookup関数を組んだけど、他の人が入力をしていくと壊れてしまう… ・vlookup関数よりもっと柔軟に検索ができる方法を知りたい という悩みに答えていきます。 紹介するのは、 INDEX関数とMATCH …
index関数の場合、
①LARGE関数を使って順位を判別
②①をMATCH関数に入れ込む
③index関数で抽出
の2ステップです。
基本的にVLOOKUPとステップは同じです。
まずは同じようにLARGE関数で順位判別をします。
LARGE($E$2:$E$24,G3)
となりますね。
次にLARGE関数をMATCH関数に入れ込みます
MATCH(LARGE($E$2:$E$24,G3)
こうすることで、1番大きい購入金額の人の「行番号」を抽出することができます。
あとはこれをINDEX関数の行指定の引数にいれていきましょう。
抽出したい性はB列なので、範囲はB列を指定します。
=index($B$2:$B$24, MATCH(LARGE($E$2:$E$24,G3),$E$2:$E$24,0))
これで抽出をすることができました。
xlookup関数を使う
Excelのみ、になりますが、XLOOKUP関数という便利な関数もあるので紹介をしておきます。
XLOOKUP関数はVLOOKUP関数をパワーアップさせたような関数で
=XLOOKUP(検索値,検索範囲,戻り配列,[見つからない場合],[一致モード],[検索モード])
と引数は多いのですが、かなり多くのオプションを付けて検索することが可能です。
例題で使ってみましょう。
手順としては、
①LARGE関数でN番目の購入金額を抽出
②XLOOKUP関数で①を検索値と指定して検索
まずはLAGE関数。
ここは今までと同じです。
LARGE($E$2:$E$24,$G3)
となります。
あとはこれをXLOOKUP関数に入れ込んでいきます。
検索値＝N番目の購入金額。LARGE($E$2:$E$24,$G3)
検索範囲＝検索値を探したい場所。今回は購入金額の列
戻り配列＝抽出したいデータがある場所。今回は性の列
見つからない場合＝今回は”なし”と設定します
一致モード＝完全一致の場合0。今回は0
検索モード＝特殊ではない場合1を指定しましょう。検索方法を変更することができます。
それぞれの引数を入れ込んでいくと…
=XLOOKUP(LARGE($E$2:$E$24,$G3),$E$2:$E$24,$B$2:$B$24,”なし”,0,1)
この様になります。
今までと同じ出力結果をだすことができましたね。
まとめ
今回は並べ替えを使ったデータ抽出方法を3パターン紹介しました。
関数が長くなってくるとどうしてもわかりにくくなってしまうので、自分が使いやすい！と思うものから使っていきましょう！
