Excelでよく使われる関数の1つ「VLOOKUP」。
単純に使うだけでも便利ですが、コレを補助して更に使える子にするための、3つのTipsをご紹介します!
エラー対処で「#N/A」表示をなくす
このような表に対して
このような数式をセットします。
=VLOOKUP(5,$D$2:$F$4,2,FALSE)
とすると「#N/A」となってしまいます。
このような場合、IFERRORでエラー対処する。
=IFERROR(VLOOKUP(5,$D$2:$F$4,2,FALSE),"エラーだよ")
のようにすると、IFERRORの第1引数の結果がエラーだった場合、第2引数の数式が適用されます。
「空セル」を空セルとして取りたいとき
通常空セルは「0」が返ってきてしまいます。
しかし
=数式 & ""
とすることで、「空セルであること」を表現することができます。
衝撃的すぎた。
なので例えばこのような表に対して、
このようになります。
数式と結果の対応は、こんな感じ。
数字を含むセルを取得する場合
この処置は弊害として、数値データの場合、「文字列」扱いとなってしまいます。
きちんと「数字」を取り扱ってもらうために、こんな感じの数式にします。
=IFERROR(VALUE(VLOOKUP($A2,$A$1:$Q$450,MATCH(I$1,$A$1:$Q$1,0),FALSE)&""),"")
「この列は数値」というのがわかっていれば、VALUEを使うことで数値にすることができます。
VALUEの有無による違いはこんな感じ。
また数値と文字列数字を比較すると、異なるものとして取り扱われるので、このような処置が必要となります。
空セルも数値も両方対処する方法があればいいんですが……
セル位置の取得を自動化する
VLOOKUPの難点は「何番目のデータ」というのを指定して、データを取らないといけません。
なのでそこが手打ちになりがちです。
MATCHと組み合わせることによって、ヘッダーを同じにすれば自動化することができます。
こんな感じの表があるとします。
このとき、対象のNoに対応する各値がほしいとします。
MATCHを使って数式1つで値取得
こんなとき、まず「B2(Value)」の位置でこのような数式を1つ作ります*1。
=VLOOKUP($A2,$E$2:$H$9,MATCH(B$1,$E$1:$H$1,0),FALSE)
あとはこの数式を、対象のセルにコピペするだけ。
すると一撃で対象の値が全て取れます!
MATCHを使うことで「データの何番目」を指定しなくても、勝手に処理してくれます。
ちなみにMATCHについて、詳細が知りたい方はコチラをどうぞ。
参考
コチラを参考にしました。ありがとうございました!
Office TANAKA - Excel Tips[VLOOKUPで0を返さない]
あとがき
ということでVLOOKUPを補助するための手法についてでした。
ちょっとしたTipsを知っていると、便利なVLOOKUPが更に便利になったりします。
VLOOKUPばかり使われがちですが、HLOOKUPでも使える方法のはずです。
「そういえばこんなことできたような?」という断片だけでも覚えて、Excelの機能で楽しちゃいましょう!
*1:厳密にはセルの位置はさほど関係ありません