lisz-works

技術と興味の集合体

ExcelのVLOOKUPを補助する3つの手法

【スポンサーリンク】

Excel

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)

あとはこの数式を、対象のセルにコピペするだけ。

f:id:liszworks:20181201144650p:plain数式をコピペ

すると一撃で対象の値が全て取れます!

結果

MATCHを使うことで「データの何番目」を指定しなくても、勝手に処理してくれます。

ちなみにMATCHについて、詳細が知りたい方はコチラをどうぞ。

www.lisz-works.com

参考

コチラを参考にしました。ありがとうございました!

Office TANAKA - Excel Tips[VLOOKUPで0を返さない]

あとがき

ということでVLOOKUPを補助するための手法についてでした。

ちょっとしたTipsを知っていると、便利なVLOOKUPが更に便利になったりします。

VLOOKUPばかり使われがちですが、HLOOKUPでも使える方法のはずです。

「そういえばこんなことできたような?」という断片だけでも覚えて、Excelの機能で楽しちゃいましょう!

*1:厳密にはセルの位置はさほど関係ありません