こんにちは。
前回、VLOOKUPの使いどころとして、二つのデータを結合する場合について紹介しました。今回は、もう一つのVLOOKUPの使いどころである、条件表と判定列について解説をしていきたいと思います。
「探す」とは逆の発想
以前、IFSとSWITCHを取り上げた際にも少し触れましたが、ずばり、VLOOKUPの使いどころの二つ目は、冒頭で書いた通り、条件表と判定列を使った、結果の入力です。
これは、LOOKUP系関数の「探す」という発想とは、全く逆の考え方によるもので、それぞれが一意になる条件を列の中に設定することで、結果を自動的に入力するというものです。結果から逆算して、Excelに「探す」という作業をさせることによって、自分の思う結果を自動的に入力するといった感じです。
このような方法による結果の入力は、IFSやSWITCH、あるいはIFの組み合わせで作成する場合と大きく異なり、次の二つの特徴があります。
- 条件表を見れば、どのような条件に基づいて結果を入力しているか一目瞭然で分かりやすい
- 条件が変更された場合、条件表の内容を変更するだけで良く、汎用性が高い
少々入力内容は増えるのと、使いこなせるようになるまで時間がかかりますが、ここまでできれば、胸を張って「VLOOKUPを使えます」と言えると思います。
考え方の整理と下準備
一意な条件を設定する
それではさっそく解説に移るわけですが、VLOOKUPを使った結果の入力の考え方の第一歩として、「一意な条件を設定する」ということが必要になります。
例によって、次のような前提と条件表を用いて、考えていきたいと思います。この時、VLOOKUP関数を使って、I2以下にそれぞれのボーナス金額を入れるわけですが、11行目から14行目のどの条件に合致しているか（あるいはどの条件にも合致しないか）を判定し、その条件に合う結果を入れていく必要があります。この時、各条件について、それぞれが同じ意味合いとして解釈されないように、一意になる名称を設定する必要があります。これが、「一意な条件を設定する」という行為です。
仮にうまく名称を付けられれば、前提からどの名称に合致しているかを判定する列を作成し、それを検索値として、VLOOKUPで完全一致となるような式を入れてあげれば、結果が出てくるということになります。
一意な名称のつけ方
そこで、VLOOKUPで計算をする前に、条件表の一行一行に、一意な名称を付けます。この時、A～Dといった名称を付けてしまえば、結局、IFなどを使ってどの名称に合致するかを判定しなければならず、あまり意味はありません。
G列、例えばG11に次のような式を入れて名称を付けます。
=A11&”|”&B11&”|”&C11&”|”&D11&”|”&E11
その結果として、G11には、「首都圏|〇|〇|〇|〇」という名称が付きます。この名称なら、上の前提条件の表のB列およびE列からH列の結果から、どの名称が最もふさわしいか、判定ができそうです。
実際にLOOKUP関数で式を入れてみる
下準備は以上で完了です。あとはI2以下に次のような式を入れれば、結果が入力されます（VLOOKUPは、以前解説した通り、より右側にある列のみ結果として取り出せるため、H列にF列と同じ、ボーナス金額がコピーされているという前提で記載をしています。）。
=XLOOKUP(B2&”|”&E2&”|”&F2&”|”&G2&”|”&H2,G11:G14,F11:F14)
=VLOOKUP(B2&”|”&E2&”|”&F2&”|”&G2&”|”&H2,G11:H14,2,0)
もちろん、この時、前提表のE列からH列には、例えば、「=IF(C2>150000,”〇”,””)」といった形で、条件に合致するかどうかを判定するための式を入れる必要はあります。ただ、この場合でも、ネストしていないので他の人が見てもわかりますし、何より、カラム名称から、実施している内容は明白です。
仮に条件が変わったりしても、条件表の部分を調整してあげるだけで結果が自動で反映されるので、汎用性が高くなります。例えば担当エリアが増えて、「北関東」が増えたとしても、条件用をつぎ足しして、結果入力のVLOOKUPやXLOOKUPの検索範囲や参照範囲を広げてあげるだけで、結果が自動入力される、といった感じです。
さいごに
この時、より汎用性の高いエクセルファイルにしてあげるときに重要なのは、条件変更を見越した条件表を作るということと、できれば条件表は別シートで作るという二点です。
上の例のように、担当エリアが増えれば、上記のような条件表で問題ないですが、例えば取扱金額の基準が変わる場合には、上記のような例だと少し対応が難しいです。また、条件が増えたときに、いちいちVLOOKUPなどの検索範囲を増やすのは大変なので、できれば条件表は別シートにして、VLOOKUPなどの検索範囲を、「G:H」といった形で、列だけで指定してあげれば、条件が増えても対応が簡単になります。
このあたりの細かいテクニックはさておき、ここまでVLOOKUPを使いこなすことができれば、「Excelを高いレベルで使える」といっても問題ないと思います。
VLOOKUPは、意外と、「知ってはいるけどしょっちゅうは使ってない」という人が多い関数の代表だと思いますので、試行錯誤しながら、活用していただければと思います。
コメント