Excelの押えておきたい関数 Excel書籍の補足 Excel(エクセル)活用術

VLOOKUP関数ではできない、右側の値を参照する方法

投稿日:2015年2月27日 更新日:


書籍P40のVLOOKUP関数の後に入れたかった内容ですが、ページの関係で抜いた内容です。

こちらからデータをダウンロードしてください。
経理の仕事がサクサク進むExcel「超」活用術 2013/2010/2007対応 ダウンロード

利用するデータは「chapter2_7.xlsx」のシート「取引額2」です。

VLOOKUPは、INDEXとMATCHの複合で置き換えることができます。
これを利用すればVLOOKUPでできなかった右側にある値も参照値にできます。

VLOOKUP関数の場合
左側の値参照 → VLOOKUP(E3,$A$3:$B$5,2,FALSE())
左側の値参照 → ×

image001

INDEX関数とMATCH関数の組み合わせ
左側の値参照 → INDEX($A$3:$B$5,MATCH(E3,$A$3:$A$5,0),2)
右側の値参照 → INDEX($A$3:$B$5,MATCH(I3,$A$3:$A$5,0),2)

image002

INDEX関数とMATCH関数の複合関数の説明

INDEX($A$3:$B$5,MATCH(E3,$A$3:$A$5,0),2)
この関数はINDEX関数とMATCH関数の複合関数です。

わかりにくいので、それぞれの関数に分解して説明します。

MATCH関数

指定した『照合の種類』に従い『検査範囲』を検索し、『検索値』と一致する要素を配列内での相対的な位置を表す数値を返します
数式
MATCH(検査値,検査範囲,照合の種類)

実際の数式
セルG1 = MATCH(E3,$A$3:$A$5,0) = 1

『検査値』
E3→「お茶」

image003

『検査範囲』
$A$3:$A$5

image004

『照合の種類』
照合の種類は、1,0,-1のいずれかがあります。
※照合の種類は基本「0」と考えておいて問題ありません。

まとめ
セルE3の「お茶」と完全に一致する値をA3:A5から検索し、A3:A5内の行目をセルG3に返すという関数になります。
image005

関数の引数ダイアログボックス
image006

INDEX関数

指定した行と列が交差する位置にある値またはセルの参照を返す関数です。
数式
INDEX(参照,行番号,列番号,領域番号)

実際の数式
セルG1 → INDEX($A$3:$B$5,MATCH(E3,$A$3:$A$5,0),2)
※今回は領域番号は設定していません。

MATCH(E3,$A$3:$A$5,0)は先ほど「1」であると説明したので、ここでは「1」とします。
なので、置き換え後
→ INDEX($A$3:$B$5,1,2)

『参照』
参照で指定した範囲の最左上(お茶)がスタート位置になります。
($A$3:$B$5,1,2)
image008

『行番号』
行番号は参照範囲の行
image009

『列番号』
列番号は参照範囲の列
image010

『領域番号』
※今回は領域番号を設定しないので、詳しい説明は省きます。

まとめ
$A$3:$B$5の1行目(MATCH関数での戻り値)である「お茶」の2列目である「100」が参照されるとなります。
image011
関数の引数ダイアログボックス
image012


フォローしていただけると嬉しいです

follow us in feedly Twitterボタン

-Excelの押えておきたい関数, Excel書籍の補足, Excel(エクセル)活用術
-, , ,

Copyright© 経理と事務の効率化 , 2016 AllRights Reserved.