数式・関数

【Excel】VLOOKUP覚えたての頃に遭遇するエラー事例

スポンサーリンク
この記事は約6分で読めます。

VLOOKUP(ブイルックアップ)は実用的でとても面白い関数ですが、ちょっとしたことで「#N/A」(ノーアサインエラー)が出やすいものでもあります。今回は、最近VLOOKUPを使い始めました・・・という方向けに、かつての私も悩まされたエラーになる事例を3つばかり取り上げてみたいと思います。

※事前にVLOOKUP関数の基本的な使い方を見たいわーという方は【Excel】検索して表示する!VLOOKUP関数を使ってみようの回をご覧ください。

※解説画像はMicrosoft365のExcelを使用しています

スポンサーリンク

「検索値」と「範囲の先頭列」の形式が違う

表示形式トラップ

こちらの例は、セルC2に社員番号を入力すると、「社員マスタ」から該当者の氏名と所属を取得してC3、C4に表示させる式を入れています。しかし、①②どちらも#N/Aのエラーになっています。数式は合っているはずなのに何故でしょう?

表示形式エラー例

実はこれ、C2に検索値として入力した「0001」と社員マスタの社員番号列に入力された「0001~0005」の表示形式が異なっているのがエラーの原因です。

頭が「0」で始まる数字を「標準」や「数値」の書式で入力すると0が外され整数部分しか表示されません。そのため、数式を作成する際に範囲となる表のデータをぱっと見て『社員番号が頭ゼロではじまるという事は文字列にしておかなくちゃダメだよね』と思い込み、C2の表示形式を「文字列」に変更しました。

しかし、C2とE3それぞれにカーソルを合わせて数式バーを見てみると、C2は文字列で入れた「0001」がそのまま表示されていますが、E3の方は「1」となっています。これは何を表すかというと、E3の実態はただの数値の「1」なのですが、表示形式で頭に0をつけているという事が考えられます。

検索値のセルと範囲のセルの形式が異なっている状態

E3を選択して「セルの書式設定」ダイアログボックスを開き、表示形式を確認すると「ユーザー定義」で「0000」という書式が適用されています。4桁に満たない整数を入力すると頭に0を補完する形式ですね。

セルの書式設定ダイアログボックス

このような場合は、セルC2は「標準」に戻し、「1」や「2」の整数を入力する事で結果は正しく表示されます。ただ、『リストは「000×」だけど入力は頭のゼロ入れなくていいから』としてしまうと、入力する側にとってはとてもわかりづらい仕様になってしまうので、入力するセルC2にも同じユーザー定義の形式を適用しておくのがベターです。

範囲のセルと入力用セルの形式を合わせたところ

この例に限らず、基本「検索値を入力するセル」と「範囲で検索値が含まれる列」の表示形式は合わせると覚えておくと、エラーになるのを防ぐことができます。

文字列トラップ

もうひとつの形式違いの事例です。

先ほどと同じ表で、社員番号は整数に変えたパターンです。入力するセルC2は「標準」のままですが、社員マスタの社員番号列が「文字列」になっています。これも結果はエラーになります。

入力セルが標準、検索値が文字列のためエラーになった状態

先の例にならい、セルC2の表示形式も文字列にすれば結果は正しく表示されます。ただ、根本的な事を言うと、このケースは「社員マスタ」の作りの方に問題あり、と言わざるを得ません。

Excelは「数値と特定できる値」と「数値なのか何なのか曖昧な値」は別物として扱いますので、整数のデータをあえて文字列形式にしてしまうと予期せぬ数式エラーを招く元となります。

文字列にしなくてはならない(それこそ先の例のように頭にゼロを入れたいからというような)理由がない限り、セルに文字列形式を適用するのは極力避けるようにしましょう。

※文字列の数字を他の形式に変えたい時はこちらの回で書いていますのでご参考にどうぞ。

【Excel】文字列で入力された数字を「標準」や「数値」に直したい
Excel(エクセル)のセルに「文字列」の表示形式で数字が入力されている時、「標準」や「数値」の形式に変更してもすぐに反映してくれません。文字列以外の形式にすぱっと直したい時は「エラーチェックオプション」や「区切り位置」を活用すると簡単に修正できます。

範囲の選択誤り

次は引数でいうところの「範囲」の設定誤り例です。B3に商品コードを入力し、「商品マスタ」にある商品名をC3に表示させるという式ですが、結果はエラー値「#N/A」になっています。

範囲の設定誤りエラー例

これは単純にVLOOKUPの「検索値が含まれる列を範囲の先頭列に指定する」というお約束が守られていない事によるエラーです。範囲に「E3:H7」と入れていますが、そうすると先頭列は「No.」の列になりますので、どの商品コードを打っても「値なし」でエラーになってしまいます。

「範囲」は表示したい値が含まれる表全体を指定しなければならない、という事ではありません。「検索値が含まれる列」から「表示したい値が入力されている列」までを指定するのが正しい考え方です。

この例では「商品コード」を入力して「商品名」を表示したいだけなので、範囲は「商品マスタ」のF3からG7までの2列を指定すればOKです。また、列の数についても表の左端から数えるのではなく、「範囲で指定した先頭列から数えて何列目」となりますので、「2」が正解です。

範囲と列を正しく修正したところ

商品マスタのスタイルを変えておさらいしてみましょう。下の表では検索値が含まれる列はF列、表示したい値(商品名)が入力された列がI列になりますので、範囲はF3からI7までを指定します。先頭の「商品コード」の列から数えて4列目が商品名になるので、列は「4」と入力するのが正解です。

商品マスタの列構成を変更した応用例

なんとなくイメージはつかめたでしょうか。範囲について、ついでにもうひとつ気に留めておきたいのが、商品リストや社員番号など、あとから行が追加される事が想定される表を範囲として使っているケースです。参照する表に項目が追加された場合、数式の範囲も拡張しておかないと、追加されたコードを入力しても範囲内に該当する値がないため「#N/A」になってしまうので注意しましょう。

マスタの表に行が追加され範囲から漏れてしまったところ

数式をコピーする時に範囲を「絶対参照」にしていない

VLOOKUPの数式を他のセルにコピーする時につい忘れがちなのが「範囲を絶対参照で固定しておく」ことです。固定を忘れると、数式をコピーした時に最初は調子が良くてもある段階から結果が正しく表示されなくなります。

範囲を相対参照にして数式コピー後にエラーになった例

1本目の数式の入ったセルを確認すると、範囲は「F3:G7」と相対参照になっている状態です。

数式を表示して確認したところ
はにわ先生
はにわ先生

数式を入れたセルは、ダブルクリック もしくは 選択してF2キーを押すと、上の画像のように参照先のセルやセル範囲がカラーでマークされるので目視で確認しやすくなりますよ。カラーの枠をドラッグで動かして数式を修正する事もできます。

このまま数式を下方向にコピーすると範囲も1行ずつ下にずれていきますので、3つめに入力した商品コードが範囲から外れてエラーになった、という事です。

検索範囲が表外に出てしまったところ

下図のように、最初に入れた数式で範囲の列記号と行番号の前に「$」をつけて絶対参照にすると・・・

範囲に絶対参照を設定したところ

数式をコピーしても範囲は動かないので、正しい結果が表示されます。

数式エラーが解消されたところ

・・・慌てて数式を作っているとつい忘れちゃうんですよね絶対参照って。で、式をずべ───っとコピーした時に「ぅおあっ」となるのです。私は今でもたまにやります(笑)

スポンサーリンク
でじログ部