Shimesaba-ba blog

大手日系企業で経営管理や経理業務を行い、英語や海外とは無縁な状態から海外で業務を行い、グローバル企業へ転職。英語、経営管理、ビジネスについての経験や学んだことをアウトプットします。

VLOOKUP関数の実務的な使い方、よくあるエラー値の原因と対処法とは?

VLOOKUP関数は実務をやる上で

よく使われる関数の1つです。

 

職種にもよると思いますが、

SUM関数やIF関数の次によく使う

と言っても過言ではないでしょう。

 

しかし意外と難しい関数でもあると思います。

なぜならエラーが起こりやすいからです。

 

ネットで調べて自分で式を入力しても

#N/Aになってしまうことないでしょうか?

 

または、1度目はうまく出来たが、

式をコピーしたり、

別の機会で使ってみたらうまくいかかった

ってことないでしょうか?

 

この関数の重要な点はVLOOKUP関数

そのものの使い方だけではなく、

この関数を使う時によくあるエラーの原因と

その対処法をセットで覚えることです。

 

この記事を読むことで

VLOOKUP関数でうまくいかない、

"あるある"の悩みとその解決方法を

がわかるようになります。

 

f:id:Shimesaba-ba:20200920145312p:plain

下記に無料のサンプルエクセルを

置いたのでダウンロードして下さい。

 

このサンプルを使って解説をするので、

ダウンロードすることで、

よりわかりやすくなると思いますし、

あなたの業務にも

そのまま流用することが

できると思います。

 

Excelは解説を読みこんで

理解するよりも、

 

いかに正解を手元に置いて、

パクって自分で手を動かして使い、

壁に当たったら

調べるという繰り返しが

一番の上達の近道です。

 

なぜならわからないことや、

悩んでいたことがわかることで、

インパクトがあり

記憶に残りやすいからです。

drive.google.com

 

VLOOKUP関数とは

 

検索値したい文字列と

データ検索範囲を指定します。

するとデータ範囲の一番左の列

検索されます。

 

そして合致したらその範囲の

左から何番目の列のデータ

を拾ってきてね!という関数です。

 

ポイントは2つです。

縦(列)で検索するという点と

データ範囲の一番左は検索したい

文字列がある項目にすること

(この例だとオーダー番号)です

 

ここで例です。

とあるオーダー番号(A)を

データ範囲から探して合致したら

その1Qの個数を拾いたいとします。

f:id:Shimesaba-ba:20200927154817p:plain

こちらが計算式です。

f:id:Shimesaba-ba:20200927152601p:plain
最後の検索範囲は

通常完全一致の"0"を選択する

で大丈夫です。

 

計算式の中身の解説 

 

オーダー番号7E1-6431-211を

グレーのデーター範囲の一番左

から探してきてね。

 

合致したらデータ範囲の左から8番目の列の

データを拾ってきてね。

答えは900になりました。

 

よくあるエラーの原因

1.範囲が動いてしまっている

Vlookupの計算式を入れた時、

その式をすぐ横や下にコピーすると

 

データ範囲や検索値も

1列また1行ずれてしまい

エラーが起こってしまいます。

 

試しに新しく検索したいオーダー番号を

入力して計算式を上から下にコピーしてみると

#N/Aになってしまいました。最悪なやつです。

 

f:id:Shimesaba-ba:20200927155931p:plain

 

絶対参照

そういう時のためにデータ範囲

を絶対参照にしましょう。

 

絶対参照とは計算式の中で

行または列の前に$マークをいれることで

固定するという意味です。

 

こうすることで式を

横や下にコピーした時に

データー範囲が壊れることを

防ぐことができます。

 

例えば絶対参照を使って

下に式をコピーしてみると

 

絶対参照を使った計算式

f:id:Shimesaba-ba:20200927153646p:plain

絶対参照の$を使うことで

検索したいものはK列で固定、

データ範囲はBからI列で固定するという

設定をしました。

 

なので例えば下に式をコピーしても

データ範囲が変わることなく

正しくデータ検索ができました。

f:id:Shimesaba-ba:20200927160145p:plain

 

テーブル化する

もしデータが今後増えいくことが確実なら

テーブル化してしまうことをオススメします。

なぜならテーブル化してしまえはデータが追加されても

範囲が可変するので数式のメンテが不要になるからです。

 

テーブルのやり方

 

1.データ範囲を選択してから"CNTR+T"を押して下さい。

下記ダイアログボックスが出てきます。

選んだ範囲が問題なければOKを押して下さい。

f:id:Shimesaba-ba:20201004144200p:plain

2.そうしたら、テーブルデザインのメニュータブの中の

テーブル名を適当に変更します。

 

変更前

f:id:Shimesaba-ba:20201004144259p:plain

変更後

f:id:Shimesaba-ba:20201004144509p:plain

 

3.VLOOKUP関数の範囲指定の時に

先ほど指定した"price"と打ちます。

残りは通常と同じように入力すれば完成です。

f:id:Shimesaba-ba:20201004144725p:plain

 

テーブル化することで

通常のセル番地よりも視覚的にみやすく、

かつデータを追加しても範囲が自動で

可変されることが大きなメリットです。

 

2.データに半角スペースが混ざっている 

パッと見では区別できないエラーがあります。

データに半角スペースが入っている場合です。

 

例ですが下記の場合、

一見7E1-6343-211というデータで

合致しているにも関わらず

データを拾ってきてくれません。

f:id:Shimesaba-ba:20200927164929p:plain

半角スペースの恐ろしさ

実はグレーのデータ範囲の最後に半角スペースを

いれたため、完全一致にならず、

データを拾ってくれませんでした。

 

実務において

何らかのエラーでデータに半角スペースが

入ってしまうことはよくあると思います。

 

しかしそのままにするとVLOOKUPする時、

一見すると同じデータなのに拾ってくれない

といった問題が起こるのです。

 

目視では到底わからないのが厄介な点ですが、

よく遭遇してきました。

そこで使われるのがTRIM関数です。

 

TRIM関数

 そんな時はデータのお掃除が必要です。

その時に使うのがTRIM関数です。

 

TRIM関数はそのセルの余計な空白を

削除してくれる関数です。

 

計算式

f:id:Shimesaba-ba:20200927164640p:plain

計算式はシンプルです。

掃除したいセルを選ぶだけです。

 

こうしてTRIM関数を使って

余計な空白をの除くと、

7E1-6343-211の該当する

データ1500を拾ってきて

くれるようになりました。

 

f:id:Shimesaba-ba:20200927164213p:plain

TRIM関数で余計な空白を削除

 

エラー値を非表示にする

条件一致しない時に出てしまう

エラー値を非表示にしたい

時はどうすれば良いのでしょうか?

 

IFERROR関数との組合わせ

 そんな時はIFERROR 関数の中に

Vlookup関数を入れ込んで使います。

f:id:Shimesaba-ba:20200927161700p:plain

IFERROR関数とVLOOKUP関数の組み合わせ

こうすることでエラー値を

非表示にすることができました。

 

f:id:Shimesaba-ba:20200927162001p:plain

 

尚計算式の""が空白にする意味になります。

もし0と表示したければ下記のようにします。

f:id:Shimesaba-ba:20200927162225p:plain

 

もし、エラーの時「要確認」という

文字列を表示させたければ、

f:id:Shimesaba-ba:20200927162428p:plain

つまり、文字列を表示するためには

""で文字を挟みます。

文字がないつまり空白にするなら

""そのままとします。

 

 まとめ

VLOOKUP関数は、縦列条件で一致した場合、

特定の列から該当するデータを拾ってくるという関数です。

 

この関数の計算式のポイントは2つです。

横(行)の検索はできず、

縦(列)で検索するという点と

データ範囲の一番左は

検索したい文字列がある項目にする点です。

 

またこの関数にはよくエラー値が

出てしまいます。

なのでそのエラーの典型例を把握して

事前に対応策を知っていることが

VLOOKUP関数を使いこなすポイントです。

 

その具体例として絶対参照を使って

範囲を固定すること、

 

TRIM関数を使って

データの余計な余白を削除する

ことをお伝えしました。

 

表の見た目でエラー値をそのまま表現させない

やり方としてIFERROR関数との組み合わせも

ご紹介しました。

 

次回の記事では

VLOOKUP関数の続きを書きたいと思います。

 

今日は単純に条件一致したものを

拾ってくる純粋なやり方でしたが、

様々な検索方法があるので

よく使う事例をご紹介するつもりです。

 

例えば左から4文字で検索したり、

複数セルを合算した条件検索をしたりすることもできます。

 

それでは良い週末を!