［Excel］サヨナラ VLOOKUP、これからステップアップ

Excel の初級心者脱出ネタです。Excel を簡易データベースとして使うときに、すぐに思いつくのが VLOOKUP 関数です。
VLOOKUP 関数の V は「Vertical：縦」という意味らしく、文字通り縦方向に検索をすすめ、該当行の値を返します。

横方向に検索したいときは HLOOKUP 関数があります。H は「Horizon：横」という意味です。

VLOOKUP 関数を簡単マスター

結論から言いますと、VLOOKUP 関数はなれてくるとあまり使わなくなります。後に説明しますが、複雑な表からデータを取り出すときは、関数が単純すぎて使いづらいのです。しかし、単純な２列の表なら、この関数に勝るものはありません。また、他の関数を理解するための基礎知識としても、一度は使ってみた経験ぐらいは必要です。

VLOOKUP 関数の書式を速攻マスター

VLOOKUP 関数の書式

=VLOOKUP(検索する値,調べる表の範囲,左から数えた列番号,FALSE)

検索できるのは、調べる表の範囲の左端の列だけです。
一番最後の引数「FALSE」は完全一致の指定です。検索する値に完全に一致しなければエラーを返します。「TRUE」とやれば一番近いデータを探します。

下の例で示すと、商品を列挙した列（機種）の横の列には商品価格を列挙した列がある、このを検索範囲とします。
この例だと、A2 から C34 までデータが詰まっていますので、調べる表の範囲は A2:C32 とします。

この場合数式を入力したセルをコピーすると、検索範囲も相対的にずれてしまうため、検索範囲は絶対参照させるよう $A$2:$C$32 としておきます。

この範囲の中で、データとしてほしい値は価格なので、範囲の左端の表品名を一列目として、二列目が価格ですので、左から数えた二番目のデータを返してもらうことになります。

F10 に入力した商品名と完全一致するものを、検索範囲を調べて、その二列目にある価格を G10 に表示させるには、

 
=VLOOKUP(F10,$A$1:$C$32,2,FALSE)

と G10 に入力します。

以上が VLOOKUP の速攻マスターです。しかし、上の例のように商品名をダラダラ「New 11 iPad Pro(Wi-Fi Cell) 256GB」の用に入力して、完全一致するデータを検索するというのは非効率であることは誰もが感じます。この例では全角半角スペース、大文字小文字まで完全一致である必要があるため、入力ミスが頻発するはずです。
では、第４引数の「FALSE」を「TRUE」にして、一番近いものを検索結果に返せば良いように思いますが、例の商品リストのように似た商品が多数ある中では、間違った商品に近似されても困るので、実務上も使えません。

この場合は、商品コードを検索範囲の左端に追加して、商品コードを検索し、それに一致した商品名と価格を返すようにするのが普通です。

ポイントは VLOOKUP は範囲の左端しか検索できないため、商品コードから商品名を返すようにするには、左端に追加するしか方法はありません。

この例で、商品名から商品コードを検索するには、範囲の左端に追加できず右端に追加するしかありません。これが VLOOKUP の欠点です。後から元の検索範囲を修正するような場合は、VLOOKUP は修正を多く挟む必要があり、しかも遅いというのが困ったところです。

サヨナラ VLOOKUP

Excel になれてくると VLOOKUP はウザい関数になってきます。というのも、列を追加したり入れ替えたりすることが頻繁に起こると、修正箇所、チェック箇所が増えてしまうからです。よくあるのが間違った列を検索してしまって、それとなく正常らしき値が返ってくるケース、実務では致命的になります。

ここでは脱初級者ならほぼ馴染みの、INDEX 関数と、MATCH 関数を使って VLOOKUP 関数にオサラバします。

Excel の INDEX() 関数を知る

INDEX 関数は、指定された行と列が交差したセルを返します。

INDEX 関数の書式

=INDEX(タテの範囲,ヨコ)
あるいは
=INDEX(範囲,ヨコ,タテ)

１列を指定した場合は、指定した列（タイの範囲）の何行目（ヨコ）の値を返します。

「範囲」のヨコとタテの交点の値を返します。

説明のために、公式な言葉は使っていませんが、ヨコは「行番号」、タテは「列番号のことです」、「範囲」も正確には「配列」もしくは「参照」というのが公式ですが、実務では上のように単純化して覚えておくと自然と指が動きます。

Excel の MATCH() 関数を知る

指定した範囲内で、探したい値の位置を列番号と行番号で返してくれるのが MATCH 関数です。
これを使えば、探したいデータが表のどのセルにあるのか調べることができます。

MATCH 関数の書式

=MATCH(検索する値,タテもしくはヨコの範囲,0)

最後の引数の「０」は完全一致で探すという意味です。「1」にすると「検索値以下」（昇順に並び替え必須）、「-1」にすると「検索値以上」（降順に並び替え必須）という意味になります。

「タテもしくはヨコの範囲」という書き方も直感的ですが、ここには基本、１行もしくは１列しか指定できません。例えば、A1:A32 というタテの１列指定は問題ありませんが、A1:B32 というように、タテとヨコが混ざる範囲は扱えません。この場合は、タテを２列に分解して扱う、もしくはヨコを 32行に分解して扱う必要が出てきます。

返り値は、ヨコの範囲を指定した場合は、左から何列目というように列番号が返ります。
タテの範囲を指定した場合は、上から何行目という行番号が返ります。行と列のそれぞれの番号が返るわけではないので、注意してください。

VLOOKUP の代わりに INDEX と MATCH を組み合わせる

先ほどの例で VLOOKUP 相当の関数が、この２つを組み合わせた

 
=INDEX(B:B,MATCH(F:F,A:A,0))

になります。

「B:B」、「F:F」、「A:A」はそれぞれ B 列、F 列、A 列全体のことです。
INDEX 関数の意味するところは B 列の MATCH(F:F,A:A,0) 行にある値を返してくれと言う意味になります。
MATCH 関数の意味するところは F 列の値が A 列の何行目にあるか出してくれという意味です。

図の例で言えば、「iPhone 11 256GB」は A 列の４行目にあるので MATCH 関数が「４」を返します。
それを INDEX 関数でB 列の４行目の値を返しているだけです。

#N/A エラー対策

最後に見栄え対策を行います。
ある程度なれてくると、エラーを表示してくれた方が、落ち着く、安心することが多いのですが、いざ印刷したりするときはエラーを消しておきたいものです。
このときは IFNA 関数で処理します。

IFNA 関数の書式

=IFNA(調べるセル,#N/A のときに表示するもの)

使い方はシンプルです。「#N/A」が出ると予想される部分を「IFNA(」と「,"")」で囲むだけです。「#N/A」が出たら空白のままでいいというのが普通ですので、その場合は「,"")」で閉じるだけ、それ以外は「,"エラー")」など好きに決めることができます。
以上より、VLOOKUP の代わりに