【Excel】VLOOKUP関数が永遠に#N/A
#N/Aはもう見飽きた
このイライラを収めるために、今後自分が見返せるように、そしてもしかしたら誰かの役に立つかもしれないという可能性を考えて、VLOOKUP関数の#N/Aの対処法をここに記す。ただ、解決すべき問題が明確になった場合は、Microsoftサポートサイトが最も丁寧に解説してくれているため、この記事を読む必要はない。それでも、解決のきっかけになれば幸いである。
OS：Windows
Excel：2022
以下、文中では、VLOOKUP(検索値、範囲、列番号、検索方法)を引数の呼び方とする。
もし、Excel自体に慣れておらず、VLOOKUP関数の使い方を知りたい場合は、VLOOKUP関数の使い方から、とにかく原因解決のタネを探している方は原因：「検索値」が参照先に含まれていないからご覧いただきたい。
関連する関数一覧
- JIS関数：半角に変換
- =JIS(半角に変換したい文字列)
- ASC関数：全角に変換
- =ASC(全角に変換したい文字列)
- TEXT関数：表示形式を変更
- =TEXT(書式設定したい値、"表示形式コード")
- 表示形式コードは「セルの書式設定」=>「表示形式」=>「ユーザー定義」から一覧を確認できる。
- 表示形式コードは" "で囲むことを忘れずに。
- =TEXT(書式設定したい値、"表示形式コード")
- IF関数：条件によって返り値を分岐させる
- =IF(論理式、値が真の場合、値が偽の場合)
VLOOKUP関数の使い方
VLOOKUP(検索値、範囲、列番号、検索方法)
検索値：キーとなる値 (範囲の左端列の中に含まれていること)
範囲：検索したい表の範囲
列番号：取り出したい列の番号 (B列なら2、C列なら3に対応)
検索方法：TRUEかFALSE
- TRUE：近似一致、「範囲」の左端列は昇順にする必要あり
- FALSE：完全一致
VLOOKUP関数を使った例
使用例として、名前と科目、そして点数の記載された下の図のような表を用意した。目的は、B9セルに名前(A～D)を入力すると、英語の点数が取得できることである。
下の図では、B8セルの中身が[= VLOOKUP(B9,A2:F6,4,FALSE)] となっている。
検索値：B9セルの値
範囲：点線で囲まれた範囲
列番号：4 (英語の列に固定)
検索方法：完全一致
B9の値「B」をA列から探し、範囲左から4列目の値を返す。
次に、名前に加えて、科目を選択して点数を取得する関数を作成してみる。
下の図では、B8セルの中身が[=VLOOKUP(B9,A2:F6,IF(B10="国語",2,IF(B10="数学",3,IF(B10="英語",4,IF(B10="理科",5,6)))),FALSE)] となっている。
検索値：B9セルの値
範囲：点線で囲まれた範囲
列番号：B10セルの科目によって2～6の値を取る
検索方法：完全一致
B9の値「B」をA列から探し、科目「数学」(= 3)の指定により範囲左から3列目の値
を返す。
このようにVLOOKUP関数を用いると、表からの値の取得が簡単に行えるわけである。しかし、このままだと、この記事の趣旨にそぐわないので#N/Aの紹介をしておきたい。
関数は1つめの例と変更していない。今回は、名前の欄(B9)の値を全角にすると、#N/Aが返ってきた。
原因：「検索値」が参照先に含まれていない
VLOOKUP関数が正しく記述できていても、検索値が範囲の中に含まれていない場合、#N/Aが出る。このエラーの多くは、「検索値」が参照先にないことが根本的な原因である可能性が高い。そして、参照先にない理由として以下の2点が考えられる。
- 範囲が間違っている
- 表示形式が異なる
1.「範囲」が間違っている
検索値は、「範囲」の先頭列(左端の列)に含まれていなければならない。したがって、「範囲」の先頭列に検索したい値がきているか確認するとよい。
2. 表示形式が異なる
範囲の先頭列(左端列)を参照している上に、検索値も範囲に含まれている。しかし、エラーとなってしまう。このような場合、検索値と参照範囲の値の表示形式(書式設定)が異なる可能性がある。
解決策：参照元と検索値の書式を統一
1. 全角と半角
最も煩わしいのが、全角と半角の混在である。検索条件を「FALSE」(完全一致)にした場合、全角と半角は区別されるため、検索値が範囲にないというエラーをはかれてしまう。どちらかに統一することで解消される。
方法1：「全角 => 半角」JIS関数を用いる
方法2：「半角 => 全角 」ASC関数を用いる
ただし、元となる文字列を消去すると、結果も消えてしまうため、表の作り直し作業が必要となる。
2. 文字列
Webサイトなどから表を読み込んだ場合、数値が文字列として保存されていることがある。この場合は、検索値を文字列に変換するか、表を数値に変換することで解消する。また、文字列は左寄せ、数値は右寄せとなることからも判断できる。
方法1：「セルの書式設定」から書式を変更する (ショートカット：Ctrl + 1)
方法2：TEXT関数を用いる
参照元の表に文字列として保持された数値がある場合、数値に変換することをお勧めする。もし、数値を文字列に変換する場合は、[=TEXT(文字列にしたい値,"@")]で変更可能。
おわりに (問題解決とは関係がない)
#N/Aは解消されただろうか。私の手元で生じる#N/Aは、そのほとんど数値が文字列になっていることが原因であった。この書式による問題のおかげで、文字コードに関する知識が少し増えたので、#N/Aには感謝している。
話は変わるが、全角で入力する、もしくは全角に統一することなどあるものか、と思った方も多いのではないだろうか。私も同感である。しかし、初めてGoogle Formを用いて情報収集を行った際に、ワクワクしながらSpread Sheetを開くと全角の数字がうじゃうじゃしていたことを思い出し、記載することにした。あの時は、すべて打ち直した。紙に書いてもらったのと同じである。
後から調べると、Formは半角数値に制限する機能があった。早くそれを言ってくれ。
初めて失敗して、学ぶことは多い。ひたすら検索し続けて解決策は見つからず、結局自分でいじくりまわして解決することの方が多い。ほとんどの記事は、内容よりも、広告の方が多い。かく言う私も、「おわりに」の文字数が一番多い。
