読者です 読者をやめる 読者になる 読者になる

Corredor

ウェブ、プログラミングの勉強メモ。

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 を使いがちなのだが、可読性も処理速度も上がるようなので、改善していかなくては…。