• レポート作成
  • 「並べ替え」機能の活用術③~LARGE&VLOOKUP関数で自動並べ替え
おすすめコンテンツ
home

2020年5月28日

「並べ替え」機能の活用術③~LARGE&VLOOKUP関数で自動並べ替え

pixta_38408243_M

Excelの並べ替えは関数を用いることで、多様なケ-スに対応することができます。

例えば、下の表で購入額の上位5名を表示させたい場合、購入金額の列内のセルをクリックし、【並べ替えとフィルター】の降順ボタンで「購入金額」の大きい順(降順)に並べ替えるのが基本の手段といえます。

差し替え1

<降順ボタンを利用した並べ替え>
差し替え2

しかし、この方法では表内のデータに変更が出ると結果(順位)が変わってしまうため、変更の度に並べ替えの作業を行う必要があります。方法として間違いではありませんが、作業が2工程となり少し手間がかかりますね。

そこで、今回はLARGE関数VLOOKUP関数を用いて、変更と同時にリアルタイムで表を自動的に並べ替える、より効率的な方法をご紹介します。

<LARGE関数とVLOOKUP関数を使った並べ替え 完成例>
WS00002

では、数値の変更入力と同時に購入金額の多い上位5名が常に表示される表を実際に作成していきましょう。

1.並べ替え結果を表示させるための表を作成する

まず下準備として、表の右側にもう一つ表を作成します。
右側に作成した表は、左側の表データを元に常に購入金額の多いトップ5を表示するための表になります。

表の右側に1列以上の空白を空けてタイトルを入力し、G列には1~5の数字を入力しておきます。
差し替え3

2.関数の挿入(LARGE関数・VLOOKUP関数) 

右側の表にLARGE関数VLOOKUP関数の順で関数を挿入して表を完成させます。

LARGE関数:
セル範囲の中で「X番目に大きい数」を求める関数です。RANK関数は「セル範囲の中の順位」を求めますが、LARGE関数はX番目(順位)に相当する「値」を求めることができます。
LARGE関数の書式:=LARGE(配列,順位)
「配列」にはセルの範囲を指定し、その中で何番目の大きさの値を求めたいかを「順位」に指定します。
WS00004

VLOOKUP関数:
VLOOKUP関数のVは「Vertical(垂直、縦)」、LOOKUPは「探す」を示します。指定した範囲の左端列(※一列だけ)を縦方向に上から下へ検索し、検索条件に一致したデータがあれば、そこから右方向にあるデータを取り出す関数です。
VLOOKUP関数の書式:=VLOOKUP(検索値,範囲,列番号,[検索方法])
WS00005

①VLOOKUP関数は対象の値から右方向にあるデータの範囲しか検索ができないので、まず元表に少し手を加えておきます。E列にB列の「名前」をコピーします。
WS00006

②右側の表にLARGE関数を挿入します。
セルH3にLARGE関数を挿入し、購入金額のトップ5を取り出します。
WS00007

【数式】タブ→【その他の関数】→【統計】→【LARGE】
WS00008

配列:$D$:$D$22
順序:G3
セルH3の数式はコピーをするので、「配列」はセルD3:D22をドラッグで選択した後に、F4キーを押して絶対参照にしておきます。WS00009(正)

作成した数式はオートフィル機能を使ってコピーします。
WS000010

VLOOKUP関数を利用して、取り出した購入金額トップ5に対応する氏名を取り出します。
セルI3にVLOOKUP関数を挿入します。
【数式】タブ→【検索/行列】→【VLOOKUP】
WS000011

 

検索値:H3
範囲:$D$3:$E$22
列番号:2
検索方法:0
セルI3の数式もH3の数式同様にコピーするので、「範囲」はセルD3~E22をドラッグで選択後にF4キーを押して絶対参照にしておきます。「範囲」は検索値を含むD列だけでなく、検索結果として表示したい値を含むE列も選択するようにします。
WS000012

◆プラスワン豆知識ーVLOOKUPの引数(ひきすう)◆

VLOOKUP関数の引数(ひきすう:関数を使うために必要な情報)は4つです。省略できるものもあります。
WS000012_3

作成した数式をオートフィル機能を使って下方向にコピーします。差し替え5

E列は「列を非表示」にしてもいいのですが削除はしないようにします。
差し替え6

元の表の購入金額を変更したら、上記の並べ替えの結果が自動で変わるようになりました。
例えばセルD8(水野啓之さんの購入金額)を125000→170000に変更するとトップ5の結果もすぐに変わります。
追加分

いかかでしたでしょうか?
表データを変更すると、それが数式によって反映され、結果が自動的にリアルタイムで並び変わる。
LARGE関数とVLOOKUP関数を組み合せると、こうした上級者向けの並べ替えを行うことができます。

ここでは、購入金額の多い順に並べ替えるという設定を例にご説明しました。
項目は違っても、実際の業務で応用できるケースはたくさんあると思います。操作手順を参考にして、ぜひ実務に取り入れてみてくださいね。

 

 

関連記事

関連記事
おすすめコンテンツ

※1 Excelは、米国Microsoft Corporationの、米国およびその他の国における登録商標です。

※2 「エクセルサプリ」 は、独立のコンテンツであり、マイクロソフトコーポレーションと提携しているものではなく、また、マイクロソフトコーポレーションが許諾、後援、その他の承認をするものではありません。

ヒューマンリソシア
Copyright © 2015 Human Resocia Co., Ltd. All Rights reserved.
smp-menu
  • ヒューマンリソシア