今回の記事では
関数を使って並べ替えする方法ってあったりするの？
しかも、一定のルールで順位付けられた結果を自動的に表示してくれる方法があれば教えて欲しい
INDEX関数とMATCH関数を使っても同じ様なことは出来るの？
VLOOKUP関数と良く比較されるみたいだけど、やり方があるなら知っておきたい
こういった疑問に答えます
✔順位の結果を関数を使って自動表示する方法のテーマ
①Excelで順位を自動表示させるならVLOOKUP+LARGE関数で実現出来ます
②INDEXとMATCH関数を組み合わせた方法でも順位を自動表示させられます
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「順位の結果を関数を使って自動表示する方法のテーマ」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
Excelで順位を自動表示させるならVLOOKUP+LARGE関数で実現出来ます
関数を使って順位を柔軟に表示するには、VLOOKUP(ブイルックアップ)とLARGE(ラージ)関数を組み合わえることで実現することが出来ます
なぜなら、検索結果を表示するためのVLOOKUP関数と大きい順に並べ替えてくれるLARGE関数を上手く組み合わせると手間が省けるからです
例えば果物の販売管理一覧表があったとします
この販売管理表から売上のランキング順に自動で並べ替える方法があります
今回は、その方法について詳しく解説していきます
まずは完成版を見ておきましょう
左側の「売上管理表」を基に右側に「売上が上位の一覧表」を自動的に作ります
まず、果物の売上管理表を手動で順位付けして作る方法を見てみましょう
販売合計額を降順で並べ替えたいので「C1のセル」をクリック
「データ」タブの「データの種類グループ」にある「降順」ボタンをクリック
販売合計額が降順で並べ変わります
上位5項目の「B2～C6のセル範囲」をコピー
貼り付け先の「F2のセル」をクリック
「ホーム」タブの「貼り付けオプション」から「値の貼り付け」をクリック
貼り付けが終わったら体裁を整えれば完成です
一見何の問題も無いですが、では販売合計額を変更してもう一度ランキングを作ってみましょう
今回は、オレンジの項目の金額を変更しました
同じように「降順」で並べ替えます
再度、ランキングの表に値を貼り付けます
これでランキングの更新ができました
この手順なら手動でランキングを作ることが出来ますが、作り変えが多いと手間が掛かりますね
では、関数を使ってこの作業を自動化してみましょう
まず、下準備のために「D2のセル」に「=B2」と入力します
これは、VLOOKUP関数を設定する時に「販売合計額を基に果物名を表示」するための設定です
「D15のセル」まで数式をコピーします
「G2のセル」に「=LARGE($C$2:$C$15,F2)」と入力します
LARGE関数の基本的な使い方は「=LARGE(配列,順位)」です
まず、配列(範囲)を指定します
範囲は「販売合計額」の範囲を指定します
この「販売合計額の一覧」から「順位であるF2のセルを指定」します
F2のセルには実際「1・2・3……」という感じで数字が入力されています
それを表示形式を変更して見やすく加工しているだけです
つまり、「F2のセルには1が入力」されていて
関数を日本語化すると「=LARGE(C2～C15の範囲のセルから,1番大きい数字を表示してね)」という意味になります
G列のその他のセルにコピーします
その結果、販売合計額の範囲から「1番目に大きい数字～5番目に大きい数字」を自動で表示してくれるようになりました
次にVLOOKUP関数を設定してみましょう
続いて「H2のセル」に「=VLOOKUP(G2,$C$2:$D$15,2,0)」と入力します
この式を日本語にすると「=VLOOKUP(G2のセルの内容を検索してね,C2～D15のセル範囲内で,指定範囲の2列目の値を表示してね,完全に一致するものがあったら)」
という意味になります
つまり、「G2のセルの金額」を「販売合計金額」から検索します
完全に一致する金額が見つかったら一つ右(果物名(コピー))を表示する
これで「金額」に合わせて自動的に果物名を表示できるという訳です
VLOOKUP関数は指定された範囲の一番左の項目名(金額)を検索し、検索結果の右側しか表示出来ないので「果物名」をコピーしています
式を「H6のセル」までコピーすれば完成です
では試しに金額を変更してみましょう
金額を変更するだけで販売額のランキングが変更されます
こんな風に関数を組み合わせて工夫するだけで並べ替え機能を使わずにランキング表示出来るようになるので、ぜひ挑戦してみて下さい
繰り返しになりますが、関数を使って順位を柔軟に表示するには、VLOOKUP(ブイルックアップ)とLARGE(ラージ)関数を組み合わえることで実現することが出来ます
INDEXとMATCH関数を組み合わせた方法でも順位を自動表示させられます
INDEX関数とMATCH関数の組み合わせでも順位の自動表示をすることは可能です
なぜなら、INDEX関数とMATCH関数の組み合わせは、VLOOKUP関数の代わりなるからです
この方法はVLOOKUP関数の代わりになる方法なのでやり方は似ている部分があります
まずは、完成形を見てみましょう
こんな風に「INDEX関数とMATCH関数を組み合わせる」と果物項目列を追加することなく同じ結果を表示することが出来ます
最初の金額(F列)の部分は「LARGE関数」を設定するところは同じなので解説は省略します
次に知りたいのは知りたい金額が何行目(指定した範囲の)にあるのか調べたいので、MATCH関数で行数を特定します
まず、MATCH関数の基本的な使い方は「=MATCH(検索値,検索範囲,照合の種類)」です
今回の式を日本語にすると「=MATCH(F2のセルの内容を検索してね,C2～C15のセル範囲の中で,完全一致する行数を表示してね)」という意味になります
式をコピーするとこんな感じですが、「14」という数字は選択した範囲が項目列を抜かしているので行数とは1行の違いがあります
これで、指定した範囲の14行目にそれぞれの金額があることが分かりました
次はINDEX関数を使って行列を指定して果物名を取得してみましょう
INDEX関数の基本的な使い方は「=INDEX(配列,行番号,列番号)」です
今回の式を日本語にすると「=INDEX(B2～C15のセル範囲の,G2の行数と,1列目の値を表示してね)」という意味になります
意外と簡単ですよね
つまり、B2～C15のセル範囲の中の「14行1列」つまり「オレンジ」を表示してくれるという訳です
式をコピーすれば目的達成です
ちなみに両方の関数を組み合わせることも出来ます
こんな風にINDEX関数にMATCH関数をネストすることで1列で目的を達成することも出来ます
VLOOKUP関数を使う場合と違ってシンプルに検索したい場合は、こちらの方が便利なので使いこなせると便利です
関数に慣れてきたらチャレンジしてみると楽しいですよ
繰り返しになりますが、INDEX関数とMATCH関数の組み合わせでも順位の自動表示をすることは可能です
コメントを残す