VLOOKUP と数値と文字列と #N/A

Excel の VLOOKUP 関数を使ったときに「#N/A」と表示されて困ったことはありませんか?

例えば下の図のように機器のシリアル番号と機器種別の入った表を VLOOKUP 関数で参照してみましょう。

Excel

セルの G6 に「104100」を入力し、これに対応する機器種別を参照するために H6 に以下の数式を入力します。

=VLOOKUP(G6, $A$1:$B$4, 2, FALSE)

これで H6 には「サーバ」と表示されるはずなのに、「#N/A」のエラーが表示されてしまいます。

Excel

良く見ると「104100」が右詰めになっています。 どうやら数値として扱われているので、セルの書式設定で G6 の表示形式を「文字列」に設定してみます。

Excel

すると、G6 の表示は左詰めになったものの、やはり H6 には #N/A が表示されてしまいました。

Excel

私はここでハマってしまいました。 「再計算」しても状況は変わりません。 どうすれば良いのでしょう?

実はセルの書式設定を変えただけでは、セルに入力されている値は数値のままなのです。 どうすれば良いかというと、とりあえず一番手軽にできるのは再入力です。 G6 をダブルクリックしてセルを編集する状態にしてから、そのまま内容を変えず Enter キーを押します。 するとうまく表示されるようになります。

Excel

既に数値として入力してしまったセルが大量にあった場合、全てを再入力するのは面倒です。 その場合、入力した列をコピーして一旦テキストエディタなどに貼り付け、それをさらにコピーして Excel に戻せば文字列として認識されます。「形式を選択して貼り付け」等でできそうな気もしますが、結局 Excel で完結して実施する方法はよくわかりませんでした (後から見つけました。追記参照)。 なお、これらの動作は Excel 2002、2003 で確認しています。

ちなみにテキストから数値への変換は以下のサポート情報で紹介されています。

VLOOKUP 関数といえば、最後のパラメータのデフォルトが「TRUE」になっている点にも注意が必要ですね。(ハマったのは私だけ??)


2012.7.22 追記:
「教えて! goo」で見つけたのですが、Excel の中のみで 1列分の数値入力済みセルを文字列に変える方法がありました。「データ」-「区切り位置」メニュー (Excel 2007) を使うのです。この「区切り位置」は本来カンマ等で区切られている値を複数の列に分割するためのものですが、分割はせずに最後に指定する「列のデータ形式」を「文字列」にして実行すれば型変換に使えます。なるほどね。

「VLOOKUP と数値と文字列と #N/A」への6件のフィードバック

  1. wさん、
    2007での確認&コメントありがとうございます。表示形式を変えるだけでは数値は数値のまま、というのがわかりづらい動作ですね。最近の Excel でも同じ動作ということは、メーカーにとっては仕様なのでしょう。

  2. 同じ問題にぶつかっていました。大変助かりました。

  3. 海大好きさん、コメントありがとうございます。励みになります!

  4. ピンバック: Time

コメントは停止中です。