VLOOKUPは数値か文字列かでErrorになる

VLOOKUP関数使っている時に、検索値が範囲の中に絶対あるはずなのに#N/Aになる!っていうことありませんか?

実はそれ検索値や範囲が数値に見えるのに、Excelは値として扱っている可能性があります。

例えば下の状態です。A6セルに「5」という数字があるからVLOOKUPの結果は「お」が返ってこないとオカシイです。

2014120901

数値なのか文字列なのか

Excelって数値と文字列の取扱いがキチンと分けられているんです。上の例ではA列の数字を文字列で入力しました。文字列で入力するのは、先にセルの書式設定で表示形式を「文字列」に選択してから1と入力します。

通常、数字なのに文字列として扱われている場合、Excelは以下のように知らせてくれます。セルの左上に緑の三角形が出ます。これはこのセル間違い無いですか?大丈夫ですか?という印です。

2014120902

セルを選択すると、どういう内容かメッセージが出てくる。今回は「数値が文字列として保存されています」です。

2014120903

 

数値へ戻し方(エラーメッセージがある場合)

このメッセージの「数値に変換する(C)」を選ぶと自動的に数値に変換してくれ、VLOOKUPもエラーから元に戻ります。

2014120904

複数行・列ある場合は複数行選択したうえで、「数値に変換する」をクリックすれば一気に直ります。

2014120905

数値への戻し方(エラーメッセージが無い場合)

ややこしいのはエラーメッセージが消えている場合です。この場合先ほどの手法が出来ません。

セルの書式設定で数値に変えても、実はダメなんです。

2014120906

これはセルの書式設定は表示形式を変更するだけで、セルの内容編集は行った事になっていないからです。ということはVLOOKUPで計算している範囲内も再計算されない為、文字列のままの認識です。

これではエラーが解消されません。

面倒くさいですが、いちいちセルを再編集する必要があります。

F2 → Enter → F2 → Enter の繰り返し

キーボードのF2を押すとセルの編集モードになります。そしてそのままEnterキーを押すと、再編集したことになりますので、数値として認識されます。

マクロで走らせる

もう一つの手法は、マクロでF2→Enterを繰り返しさせる方法です。

何万行という行数があれば手動でやるのは流石に大変なので、マクロで全セルを再編集させる事です。

再度エラーメッセージを表示させる

これが一番やりやすいです。

一回だけF2 → Enter を行い、その後に「元に戻す」をします。するとエラーメッセージを再度表示させる事が出来ます。

2014120907

2014120908

2014120909

後は複数行選択し、「数値に変換する」をすれば一気に変換出来ます。

&で数値を結合させている場合

上の例では、ただ単純に入力時に文字列になってしまった場合です。それに対して、数式で文字列になってしまっている場合があります。

それは数値と数値を&で結合した場合です。

2014120910

この場合もC列の結果は文字列として扱われます。検索値「15」はC6セルにあるので、VLOOKUPの結果は「お」になって欲しいですが、やはり#N/Aとなります。

この時は「数値が文字列として保存されています」メッセージが出ないのです。ただ、文字列なので標準だと左寄せになっているので、そこで気付けるかもしれません。

数値にする

文字列を数値に変換する関数があります。それはVALUE関数です。

2014120911

そうすればC列の結果は数値として扱われるのでVLOOKUPが正常になります。

検索値を文字列にする

もしくは、C列の計算式が変更出来ないのであれば、検索値を文字列で検索すれば良いです。

この例ですと「15」を文字列にしてしまいます。関数はTEXT関数を使います。

2014120912

2つ目の引数は表示形式を指定するところですので、文字列の場合 ”@” になります。

こうすればVLOOKUPはエラーになりません。

Comment

  1. 木村ライム より:

    もう一つの手法は、マクロでF2→Enterを繰り返しさせる方法です。

    何万行という行数があれば手動でやるのは流石に大変なので、マクロで全セルを再編集させる事です。
    この箇所を説明に従って実施しても、うまく処理できないのですが
    詳しく教えてください。

  2. 匿名 より:

    * 1 すれば良いのでは

  3. 匿名 より:

    文字列に変換するのは
    「区切り位置」で変換できます。
    元々コンマ区切りとかのデータを列を別々に分配する機能ですが分配後に書式を指定できるので
    区切り文字を何も指定しない>データ列はそのまま
    最後の書式設定で文字列にするとF2>Enterと同じ効果が得られます。

コメントをどうぞ

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

  • ▼お小遣い稼ぎしませんか?▼ 日々の生活にhappyをプラスする|ハピタス

    クレジットカード・FX口座を作るだけで簡単に3万円GETです。一切支出はありません。

テンプレート 無料 COUNTIF 作業効率UP MATCH コピペ XLSTART 図形 目標 INDEX 日付 バーコード SUMIF DATE VLOOKUP 置換 SUMIFS HLOOKUP ワイルドカード グラフ 変換 ユーザー定義関数 フロー図 条件付き書式 COLUMN NA() 印刷 検索 TODAY SUM
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑