こんにちは！こんばんは！女性営業マンとして日々奮闘しているなっちゃんです！
ビジネスマンは日々な販売活動でどの程度効果があったのかを分析するため、主婦の方は家計簿を作ったりするなど、常に数字と向き合っています。
可視化してデータを比較しますが、使い勝手の良い関数の1つ「vlookup関数」によって、スムーズに作業することができます。
しかし、思い通りの値が反映されない・エラー表示がでるなどのトラブルがあると思います。原因・解決法を詳細に把握し、ワンランク上の人間になりましょう。
目次
vlookup関数が反映されない時の解決方法！
このようなエラー表示がでて困ったことがあると思います。原因・解決法を一緒に学んで、関数名人になりましょう。
「vlookup」関数を確認
パラメータとして指示するのは［検索値］［範囲］［列番号］［検索の型］です。
【検索値】
検索値とは、検索の対象値を意味し、商品コードなどのデータを探す時のカギを決めます。上記の表では E2 のセル、つまり 商品コード【00010】を指します。
検索値は「全角」と「半角」の文字は認識されますが、英字の大文字・小文字は認識されず同じ値として判断されます。
セルを指定するだけではなく、「“数字”」や「“文字列”」を直接指定することもでき、特に文字列を指定するときには式の中で文字列を「“」 (ダブルコロン) で、「”00010“」のように囲む必要があります。
【範囲】
検索値を探す範囲を指定し、上記の表では、A1からC11になります。
【列番号】
上記の表で、獲得したい値が左から数えて何列目かを指定します。Ａ列のときは「1」を入れ、Ｂ列のときは「2」を入れ、Ｃ列の値のときは「3」を入れるといったぐあいで入れます。
商品の名前の欄が、範囲のうちＢの列にあるので「2」と入れます。
【検索方法】
「TRUE」「1」や「FALSE」「0」を入れてみます。「TRUE」「1」と入れると、列の近かしい値が表示されます。
「FALSE」「0」と入れると、正しい値が表示されます。一般的には「FALSE」「0」をチョイスしましょう。
例では、商品コード【00010】とまったく同じ値を表示したいので「FALSE」「0」を入れます。
vlookupの詳しく解説した関連記事です。あわせて読んでみてください。
初心者でも理解できるVLOOKUPの分かりやすい使い方を解説した動画です。
VLOOKUP（E2, A1：C11,２.０）
これは、E2に文字を入力したら、範囲「A1からC11」までの表を検索して、列番号「２」のE2とつながりのある値を摘出し、「FALSE」「0」完全に一致する値を「F2」に読みとります。
#N/A（ノー・アサイン）エラーが出てしまう
#REF（リファレンス）エラーが出てしまう
2パターンのエラーについて解説します。
#N/Aエラーが出てしまったケースの対処法
計算の結果が「#N/A」になっている時は、参照先のセルに値がないために「#N/A」（読み：ノー・アサイン）というエラーがでているのです。
数値が入っていない
この場合、E欄に何も入力されていないためのエラー表示です。
そこで、E欄に文字を入力します。
これでひとまず解決ですが、IF関数を組み入れE欄に値がない際はF欄も空白にする設定をします。
まずはIF関数を設定します。
次にvlookup関数を中に入れるために、「値が偽の場合」にカーソルをあわせ、クリップボードの下にある「IF」を「vlookup」にかえると設定ができます。
OKを押すと、IF関数の中にvlookup関数を挿入することができます。
＝IF（E2＝“”,“”,VLOOKUP(E2,A2:C11,2,0)）
これの意味は、E2の数値が空欄だったら空欄、空欄でなかったら、範囲「A1からC11」までの表を検索して、列番号「２」のE2とつながりのある値を摘出し、「0」完全に一致する値を「F2」に読みとります。
名前空間で、空欄であるという意味合いです。
参照範囲・場所に差異がでる
表を下まで「オートフィル」でドラッグしたり、別の場所にコピペしたりする場合は、参照する範囲・場所に$（ドル）マークを付け絶対参照にしないと、参照する範囲・場所に差異が生じエラーになってしまいます。
絶対参照とは、数式を入力したセルをコピーすると通常は参照したセルが自動的に変化しますが、絶対参照で固定しておけば変化しません。同じ数値を何度も使いたい場合に便利です。
これを絶対参照をせずにオートフィル機能を使用すると
参照の範囲・場所がかわったことがわかります。これを防ぐために絶対参照を使用します。
F4キーを押すことで簡単に絶対参照をすることができます。
これを絶対参照を設定してオートフィルを使用してコピーをすると
参照範囲が移動していないことがわかります。
＄マークを使わないで範囲固定をする
扱う商品が増える度に参照範囲を変更することが億劫な人のオススメの方法です。
参照範囲を条件付けする時に、A列からC列をすべてドラッグすることで、商品が増えても関数を変更することなく使用し続けることができます。
関数構文が変わっていないことがわかると思います。
セル書式の相違がある
入れたデータはあるのに、#N/Aとなるのは「表示形式」の違いです。
左の商品コードにいれてある”1”を指定しているにも関わらず、結果は「りんご」ではなく#N/Aエラーになってしまっています。
同じ”1”という数字でも「数値」として入っているか、「文字列」として入っているかの相違があるためです。
しかし、セルの書式に相違があるかを意識すれば、データはあるのに結果に表れないといった事象はなくせます。よって
- A列の「表示形式」を数値に変更する
- E列の「表示形式」を文字列に変更する
のどちらかを行います。
セルの書式を変更する方法
ここでは、A2セルの「表示形式」を数値にする方法をご紹介します。
まず、A2セルで右クリックし、セルの書式設定を開きます。
次に、表示形式の分類（C）：「数値」を選択し、OKをクリックします。
最後に、セルに対して値を入れてみます。ここでは「1」を入れていますが、入力した後は数値として表示され、商品を読み込むことができます。
文字列から数値へ変換する一括設定方法
セルを1つずつ「数値に変換する」を行うのは手間がかかるためなので、複数のセルをまとめて数値に変換したい場合は次のように行います。
数値にかえたいセルを範囲選択します。「文字列のセル」！【ビックリマーク】が出てますので、「数値に変換する」を選択します。
以上でOKです。
VLOOKUP関数の使い方、完全マスター講座！は基本的な使い方から応用レベルの操作まで様々なシーンに対応するための解説動画です。
これで＃N/Aエラーの大半は解消できます。
#REF（リファレンス）エラーが出てしまった時の対処法
列の指定が選択範囲を超えて設定され、参照先がなかった場合に表示されます。
この関数構文は、E2の数値が「00001」で範囲「A1からC11」までの表を検索して、列番号「4」のE2とつながりのある値を摘出し、「0」完全に一致する値を「F2」に読み取ります。
しかし、実際には4列目がないので#REF（リファレンス）エラーつまり「列番号が範囲を超えている」と表示されます。
列番号をなおして、正しく表示されるように変更しましょう。
vlookup関数のことがわかれば、数字を扱うことは楽しくなってくると思います。
オススメ本の紹介
これ何が原因でトラブルエラー表示になっているのだろう？ちょっと調べればわかるようなことも多いので、1家に1冊関数関連の本を置くことをオススメします。
エクセル時短ワザ＆関数テク300＋α
| 出版社 | ワン・パブリッシング |
| 価格 | 1,078円（税込）送料無料 |
| 内容紹介 | 仕事がはかどる時短ワザと関数テクを300以上収録 仕事がはかどるエクセルの時短ワザ技に加え、ミスを防ぎ効率化を図る関数など300以上のテクニックを収録。機能を探せるKeyword、知りたいことが見つかるQuestion、やるべき操作がわかるAnswerなどを、各テクニック説明に見やすく配置しました。 また知りたい操作がすぐに探せる操作別、関数別、キーワード別のインデックスをそれぞれ掲載。本誌を読めば、仕事がスムーズに進みます！ |
Excel 最強の教科書［完全版］
| 出版社 | SBクリエイティブ |
| 価格 | 1,738円（税込）送料無料 |
| 内容紹介 | 世界最高峰の職場で徹底的に叩き込まれた基本ルールと実践テクニックを余すことなく一挙に解説。あらゆる分野のビジネスで即活用できる珠玉のテクニックが満載です。 「自己流でなんとなく使っている」から脱却し、「正確に」「早く」「使いまわしができる」エクセル表を作れるようになりましょう！ |
「エクセル時短ワザ＆関数テク300＋α」は初級者・中級者向けの本で「Excel 最強の教科書［完全版］」は中級者・上級者向けの本になっています。
まとめ
vlookup関数が反映されないときの解決方法を詳しく解説しました。
- パラメータの確認をしました。
- 「#N/Aエラー」・「#REFエラー」がでる理由・できる対処法をお伝えしました。
- オススメの本を紹介しました。
関数を使いこなし、仕事・家庭に役立ててください。
最後まで読んでいただきありがとうございました。