【数値と文字列】VLOOKUP関数で同じ数字なのにエラーが出るときの対処法とは?

スポンサーリンク
Title_value_string

今回の記事ではVLOOKUP関数を使う際によく起こる、検索値マスタの見た目は同じ数字なのにVLOOKUP関数で#N/Aエラーが出てしまい検索することができない際の対処法を解説したいと思います。

このようなことが起こる原因は同じ数字に見えてもエクセルで内部的に数値と文字列という違う表示形式になっていることが考えられます。
VALUE関数やCONCATENATE関数を使い、検索値とマスタの両方を数値や文字列に統一することによって対処することができます。

なぜ、検索できないかを覚えておくだけで簡単に対処することができますのでぜひ覚えておいていただければと思います。

この記事を読むと分かること
  • VLOOKUP関数で同じ数字なのに検索できない理由について
  • VLOOKUP関数で同じ数字なのに検索できない場合の対処法
  • 文字列と数値の違いについて
  • 文字列→数値、数値→文字列に直す方法

スポンサーリンク

1.VLOOKUP関数で同じ数字なのに検索できない理由

Table_1
表1

上の表を見てください。
このVLOOKUP関数ではD2セルに入力した数字に対応した左側の商品コードに対応した商品名を返す基本的なVLOOKUP関数の計算式です。

しかし、よく見てみると「D2セル」には左の商品コードにもしっかりと存在する「2」という数字を指定しているにも関わらず、結果は「りんご」ではなく「#N/Aエラー」になってしまっています。

なぜこのようなことが起こってしまうかというと、同じ「2」という数字に見えてもエクセルの内部的に数字が数値として入力されているか、文字列として入力されているかという違いがあるからです。

エクセルの内部的には数値としての「2」と文字列としての「2」は別物なので、VLOOKUP関数のマスタに文字列の「2」は無いと判断されて「#N/Aエラー」という結果が出てしまっています。

ここからはしばらく文字列と数値の変換方法の解説が続きます。
対処法をすぐに知りたい方はこちらを押してください。

それでは数値と文字列の違いとはどんなところでしょうか?

1-1.エクセルでの数値とは?

数値とは簡単に説明すると、計算に使える数字のことを指します
エクセルでは数字を数値に指定していないと計算式に使うことができません。
表1をよく見ると、商品コードの数字は右側に揃えられていますがエクセルでは数値は右側に揃えて表示されます。

1-2.エクセルでの文字列とは?

文字列とは文字として並べられた数字のことを指します。文字列として入力した数字は計算式に使うことができません
表1を見るとD2セルの数字は左に揃えられていますがエクセルでは文字列は左側に揃えて表示されます。
また、計算式が表示されているD4セルも文字列として入力することで結果ではなく計算式をそのまま表示させています。

Table_2
表2

上の表は左側が数値で右側が文字列で数字が入力されています。

「A1セル」と「A2セル」には1と2が入力されているので「1+2=3」と結果に3が表示されていますが、右側は「B1セル」と「B2セル」には文字列として1と2が入力されているだけなので計算には使用できず結果は0になってしまっています。

簡単にまとめると、右に寄っている数字は数値、左に寄っている数字は文字列と覚えておきましょう。
また、アルファベットやハイフンなど数字以外の文字が入っているとすべて文字列として扱われます。

2.文字列を数値に変更する方法

それでは実際にVLOOKUP関数のマスタ側が文字列になっている場合に数値に変更する方法を解説したいと思います。

2-1.エラー表示から変換する

Table_3
表3

文字列として入力されているセルにはそのセルにエラーがあるという表示の左上に緑色の三角のマークがつきます。
そのセルを選択すると赤枠のような!(黄色いビックリマーク)が表示されます。

Table_4
表4

!(ビックリマーク)を押すとエラーに対応する項目が出てきます。
今回は数値に直したいので「数値に変換する」を選択します。

Table_5
表5

「B2セル」の文字列だった「1」が数値に変換されました。
それに伴い、計算結果も「1」が数値として認識されたことで「1」に変わりました。

2-2.VALUE関数を使用する

少ないデータでしたらエラーから1つずつ直しても問題ないですが、データの量が多いと1つずつ変換していくと相当な手間がかかってしまいます

そんな時はVALUE関数を使うことで簡単に数値に変換することができます。
使い方もとても簡単で、例えば「B3セル」の「2」を数値に変換する場合、

=VALUE(B3)

変換したいセルを指定するだけで数値に変換することができます。

計算式を入力したらオートフィルでコピーをすることで一度にたくさんの文字列を数値に変換することができます。

2-3.コピーペーストで値として貼り付ける

最後にコピーペーストで数値に変換する方法を解説します。

こちらの変換方法もとても簡単で、数値に変換したい範囲をコピーしてペースト時に値として貼付けを選択するだけです。

Table_6
表6

ペースト時に右下に出るバインダーのようなアイコン(赤矢印)を選択し、値の貼付け(青矢印)を選択します。

この操作で、文字列を数値に変換することができます。

↓コピーペーストを更に便利に使いたい方はこちら↓
【小ワザ】意外と知らない?コピーペーストの種類

3.数値を文字列に変更する方法

ここまでは文字列を数値に変換する方法を解説してきましたがここでは逆に数値を文字列に変換する方法を解説したいと思います。

個人的には、VLOOKUP関数を使う場合は文字列を参照した方が間違いが少なくお勧めです。

3-1.表示形式で変更する

Table_7
表7

最初に解説するのは、セルの表示形式を文字列に変更する方法です。
文字列に変更したいセルを選択し、リボンメニューのホーム→数値→赤枠のウインドウを開き、その中の文字列を選択します。

Table_8
表8

数字が左寄りになりました。
これで文字列に変更されました。

注意点としてはこの方法で数値を文字列に直した場合、VLOOKUP関数の結果は自動的に元に戻りません。
あくまでも数値を文字列に変更する方法として覚えておいてください。

3-2.’(アポストロフィー)を頭につける

数字の前にShift+7キーで’(アポストロフィー)を付けることで数値を文字列に変換することができます。

Table_9
表9

アポストロフィーを頭につけたことで「2」が文字列に変更され、VLOOKUP関数の結果も「りんご」と表示されるようになりました。

3-3.CONCATENATE関数を使う

CONCATENATE関数は通常セルとセルの文字をつなげるときに使用する関数ですが、結果を文字列で返すという特性を利用することで簡単に数値を文字列に変換することができます。
使い方もとても簡単で、

=CONCATENATE(変換したいセル)

と指定するだけです。

例えば「A3セル」を文字列に変換したい場合、

=CONCATENATE(A3)

と入力することで簡単に変換することができます。

こちらも計算式を入力したらオートフィルでコピーをすることで一度にたくさんの文字列を数値に変換することができます。

4.VLOOKUP関数で同じ数字なのに検索できない場合の対処法

それではいよいよ、VLOOKUP関数の結果がうまく反映されないときの対処法を解説したいと思います。
個人的には、VLOOKUP関数のマスタや検索値には文字列を使用することをおすすめします。
その理由は、頭に0が入力されている場合数値表示では頭の0の表示が消えてしまい見た目にも同じマスタを検索しているかわかりづらくなってしまうからです。

また、数値を文字列に置き換える際に3-3で解説したCONCATENATE関数を使うことをおすすめします。
この関数を使用することで、大量のデータがあっても一度に変更することができることと、VLOOKUP関数の結果を自動的に再計算させることができるからです。
また、数字だけのマスタにアルファベットを追加するなどの応用が利く点もおすすめする理由です。

CONCATENATE関数は文字列にしたいセルとコンマで区切って別の英数字などを簡単に追加できます。

例として、A3セルの「3」という数字の前に「A-(エーハイフン)」を追加したい場合、

=CONCATENATE(“A-“,A3)

と計算式に入力することで「A-3」という結果が表示されます。

↓頭の0の表示を使いこなしたい方はこちら↓
Excelで0の表示を使いこなしたい方はこちらからどうぞ!

それでは実際にVLOOKUP関数のエラーを解消してみましょう。

Table_10
表10

まずは、数値で入力されたマスタ側の参照値を空いているセルにコピーペーストします。
今回はA列からC列にコピペしました。

Table_11
表11

文字列に変換したいセルの一番上(今回はA2セル)を指定してCONCATENATE関数を入力します。
指定するセルは先ほどコピぺしたデータの一番上(今回はC2セル)です。

Table_12
表12

Enterを押したら、セルの右下にカーソルを合わせ、+(プラス)の表示になる部分でダブルクリックかそのまま表の一番下までドラッグし、オートフィルですべてのデータにCONCATENATE関数を反映させます。

Table_13
表13

全てのデータが文字列に変換され、VLOOKUP関数の結果もエラーから「りんご」に変更されました。

最後に、コピペした先のデータを消せば作業は完了です。

補足説明としてVLOOKUP関数のマスタの参照値に設定するのは数字だけではなく、アルファベットやハイフンを数字と一緒に入力することで必ず文字列と判定され、数値と文字列という間違いが起こりづらくなります。

↓このページで解決しなかった場合はこちらもお読みください↓
VLOOKUP関数がうまく表示されない時の8つの対処法とは?【エラー対策】


今回は、VLOOKUP関数で同じ数字なのにうまく検索できないときの理由と対処法、数値と文字列の変更時方法について解説しました。

この記事ではVLOOKUP関数を例に解説しましたが、同じように使うことができるINDEX、MATCH関数でも今回と同じ対処をすることでエラーを解消することができます。

Excelの内部的には数値と文字列は同じ見た目でも違うということを、ぜひ覚えておいていただければと思います。

最後までお読みいただきありがとうございました。

タイトルとURLをコピーしました