Excel。表からTOP3のデータを抽出したいけどどうしたらいいの？
＜INDEX&MATCH&LARGE関数＞
TOP3の値なんて、
LARGE関数を使えば簡単に算出できるでしょう？と思われるでしょうが、
今回は、次の表からTOP3のデータを抽出したいというのがリクエストなのです。
左側のA2:C12までの表があります。
このデータの中から、E:Gにある、
別表にTOP3までのデータを抽出したいということなのです。
オートフィルターを使ってもいいのですが、
今回は、関数を使って抽出を行ってみたいと思います。
【LARGE関数だけでは難しい】
まず、1位だけならば、MAX関数を使うところですが、2位・3位はMAX関数ではダメなので、登場するのがLARGE関数ですね。
しかし、このLARGE関数では、売上高のTOP3を算出することが出来ても、
店舗名を抽出することは出来ません。
仮に、LARGE関数で算出した数値を使ってVLOOKUP関数で
店舗名を算出しようとしても、売上高の左側なので、それも無理ですし、
数値が重複していた場合も考慮する必要がありますので、
LARGE関数単独では難しいようです。
そこで、このようなケースでは、INDEX関数が重宝します。
【INDEX関数とMATCH関数のコンビネーション】
INDEX関数は、どこにデータがあるのかを教えてあげればいい関数なので、
それぞれ、1位・2位・3位の数値がデータの何件目にあるのかを教えてあげれば
データを抽出することが出来ます。
では、どのようにしたら、1位～3位までのデータがそれぞれ、
どこにあるのかを求めるには、MATCH関数を使えば求めることが出来ます。
そこで、今回は、まずはMATCH関数を使って別の列にデータを算出して、
それからINDEX関数を使っていく方法をとります。
E3をクリックして、MATCH関数ダイアログボックスを表示します。
検索値には、LARGE関数をネストしますので、
LARGE関数ダイアログボックスを表示します。
配列には、$C$3:$C$12
順位には、1という数値がある、F3
そして、数式バーを使って、MATCH関数ダイアログボックスに戻りましょう。
検索範囲には、売上高の$C$3:$C$12
照合の種類は、完全一致なので、0
そして、OKボタンをクリックします。
7と算出されました。この表の7件目のデータが1位ということがわかりました。
確かに、秋葉原の480が1位ですね。
では、E5まで数式をコピーしておきましょう。
では、この数値を使って、G3に店舗名を抽出していきます。
そこでINDEX関数を使いますので、
INDEX関数ダイアログボックスを表示します。
INDEX関数は、2種類の引数を持っている関数なのですが、
今回は、『配列・行番号・列番号』を使っていきますので、
選択してOKボタンをクリックします。
配列には、B$3:B$12 複合参照にしてあるのは、
完成した数式をオートフィルでコピーするためです。
行番号は、$E3
列番号は、0
あとは、OKボタンをクリックします。
そして、数式をオートフィルを使ってコピーします。
G3の数式は、
=INDEX(B$3:B$12,$E3,0)
これで、抽出することが出来ましたね。
このように、
INDEX&MATCH&LARGE関数のコンビネーションで抽出することが出来ましたね。
ついでに、数式を一発で算出しようとした場合は、
G3の数式は次のようになります。
=INDEX(B$3:B$12,MATCH(LARGE($C$3:$C$12,$F3),$C$3:$C$12,0),0)
やっぱり結構長い感じになりましたので、
慣れるまでは無理をせずに、
しっかり算出・抽出することを考えていきましょう。
