VLOOKUP 関数を使う時の小技
Excel の VLOOKUP 関数を使う時に、こんな方法があるよー、という小技。他人が作った Excel を見て知り、「発想の転換やな…」と思ったので紹介。
例題
ある表には「列1・列2・列3・列4 (価格)」があり、列1・2・3の組み合わせがユニークになるようなデータが入っているものとする。DB 的に表現すると列1～3に主キー制約が付いている、ということ。例えば「大分類」「小分類」「商品名」といった3列で、3列揃って初めて一意になる、そんなデータがある表だとする。こちらを「マスタ表」とする。
一方、その列1～3に対応するデータが別のところから取得されて列挙されている表があるとする。便宜的に「調査表」と名付ける。今回はこの「調査表」の一番右側に、「マスタ表」に一致する行があれば列4の「価格」を表示させる。一致するものがなければとりあえずエラーのままでいい。
要するに複数列をまとめて見ないと主キーと見なせないとき、選択範囲の左端の列しか見てくれない VLOOKUP 関数を使って、どうやって「価格」列を表示させたら良いか、ということ。
やり方
「マスタ表」の左端に列を追加し、=CONCATENATE(列1, ",", 列2, ",", 列3) といった数式を入れ、列1～3の値をカンマ区切りで結合した列を作る。便宜的に「キー列」と表現すると、「マスタ表」は「キー列 (列1～3の結合)・列1・列2・列3・列4 (価格)」と並ぶ。
「調査表」の右端には、次のような VLOOKUP 関数を設定してやる。
=VLOOKUP(CONCATENATE(調査表.列1, ",", 調査表.列2, ",", 調査表.列3), マスタ表.キー列:マスタ表.列4, 5, false)
つまり、VLOOKUP で見たい範囲の左端にキーとなる列を追加して CONCATENATE 関数でキーを作成し、検索値となる第1引数にも同様にキーとなる値を CONCATENATE 関数で作成してあげれば、列1～3など複数の列をキーとして比較ができる、という算段。
以上
文章だけでの説明だと少し分かりにくかっただろうか。実際に Excel で試してみてほしい。
また、VLOOKUP よりも MATCH と INDEX の組み合わせの方がメリットが多かったりする。自分が先に習得したのが VLOOKUP だったので、今でもついつい VLOOKUP を使いがちなのだが、可読性も処理速度も上がるようなので、改善していかなくては…。