VLOOKUP関数とMATCH関数で行・列項目の交差位置を取り出す

VLOOKUPの構成
Excelの一覧

使用関数:VLOOKUP、MATCH

VLOOKUPは先頭列を縦方向に検索する関数で、列項目の検索はできません。横方向に並ぶ列項目も検索して交差位置の値を取り出すためにMATCH関数で補完した数式を組み立てます。その例として、「来店回数」と「利用メニュー」を軸にした値引き額の参照を図解します。

数式の構成

下図はヘアサロンの施術データ表(テーブル)です。この表に「▲値引き」というフィールドを新設し、別表の「値引き料一覧」から金額を転記したいと思います。
VLOOKUP+MATCHの数式を使う表

「値引き料」を引き出すためには、一覧の先頭列「来店回数」フィールドと、先頭行に並ぶ「施術メニュー」のフィールドB~Iの交差位置を求めることになります。

「来店回数」から行位置を求めるのはVLOOKUP関数でできますね。VLOOKUP関数の構成を見てみましょう。
 VLOOKUP(検索値,範囲,列番号,検索方法)
3番目の引数「列番号」の指定にMATCH関数を使い、他はいつものVLOOKUP。
▶VLOOKUP関数の基本
VLOOKUPの構成
「来店回数」が「初めて」の値引き額がある「列番号」は施術メニューによって違ってきます。「カット」なら500円、「ヘッドスパ」なら0と表示させるために、施術メニューに応じた「列番号」をMATCH関数で呼び出すわけです。

MATCH関数の組み立て

まずは、入れ子するMATCH関数を組み立てておきましょう。

数式ライブラリの検索/行列からMATCHをクリックしてダイアログを開きます。
数式ライブラリのMATCHをクリック
最初の引数「検査値」には、施術データ表「施術メニュー」フィールドの先頭セルをクリックして指定。テーブル化された表なので、[@施術メニュー]と構造化参照による表記でセルが指定されました。テーブル化していない表の場合は「G2」と入ります。
構造化参照で組み立てると数式の意味が解りやすくなる上、絶対参照にすべきか悩まずに済むという利点もあります ▶構造化参照の仕組みを知る
MATCH関数の引数「検査値」を指定
次の引数「検査範囲」には施術メニューの項目名を全て指定するので、値引き料一覧の先頭列=見出しをドラッグで選択します。
ここも構造化参照で「値引き料一覧[#見出し]」と入りました。「値引き料一覧というテーブルの見出し範囲=先頭行の項目全て」を指定したということです。
通常の参照形式で指定するなら、「$A$1:$I$1」となります。
MATCH関数の引数「検査範囲」を指定
「照合の種類」は完全一致の「0」を指定。
MATCH関数の引数「照合の種類」を完全一致「0」に指定
数式は「=MATCH([@施術メニュー],値引き料一覧[#見出し],0)」となります。
施術メニューの入力値値引き料一覧の見出しの中で何番目にあるかを数字で返してくれるということです。
MATCH関数の基本

VLOOKUP関数の組み立て

では、大枠のVLOOKUP関数を組み立てましょう。VLOOKUPのダイアログを開く前に、先程組み立てた数式 MATCH([@施術メニュー],値引き料一覧[#見出し],0) をコピーしておきます。

新設した「▲値引き」フィールドの冒頭セルを選択して、数式ライブラリの検索/行列からVLOOKUPをクリックしてダイアログを開きます。
関数ライブラリの検索/行列からVLOOKUPをクリック
最初の引数「検索値」で、施術データ表「来店回数」フィールドの先頭セルをクリックして指定。[@来店回数]と構造化参照による表記でセルが指定されました。テーブル化していない表の場合は「F2」と入ります。
VLOOKUP関数の引数「検索値」を指定
次の引数「範囲」で、値引き料一覧テーブルを指定。テーブルの左上角にポイントして斜めの小さな黒い矢印が出たらクリックすると指定できます。または、見出し行を除いたテーブル範囲をドラッグで選択してもOK。「値引き料一覧」と入りました。
通常の参照形式なら「$A$2:$I$4」と絶対参照で指定。
VLOOKUP関数の引数「範囲」を指定
「列番号」にカーソルを置いて、Ctrlキー+VでコピーしておいたMATCH関数の数式をペースト。(※VLOOKUPから始めてMATCHへ移動して、またVLOOKUPへ戻るというやり方もできますが、慣れないと途中でミスってやり直しになる確率が高いので、最初に入れ子数式を組んでから大枠数式にコピペする方が簡単だと思います)
「検索方法」には完全一致の「0」または「FALSE」と入れます。
VLOOKUP関数の引数「列番号」にMATCH関数を挿入
「▲値引き」フィールドに値引き額が表示されました。
値引き額が表示された
最終的な数式は「=VLOOKUP([@来店回数],値引き料一覧,MATCH([@施術メニュー],値引き料一覧[#見出し],0),0)」となります。

タイトルとURLをコピーしました