広告
- #VALUE! エラーの原因を知りたい
- 解決方法を知りたい
VLOOKUPでの #VALUE! エラーは、列番号の指定に誤りがある場合に発生します。訂正しない限りいつまでも出続けるので、放置してはいけません。
この記事では、#VALUE! エラーの原因と解決方法を解説します。
本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルもダウンロード可能です。
≫ PDFファイル参照ページはこちら
VLOOKUP を基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ VLOOKUPの使い方｜基礎から完璧マスター＆便利技20選を超図解！
マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft｜Officeのサポート～エラー値 #VALUE! を修正する
1. VLOOKUPの #VALUE! エラーの原因
VLOOKUPの#VALUE!エラーは、列番号の指定に誤りがある場合に発生します。
誤った指定の例は、次のようなケースです。
- 列番号が1以上の数字になっていない
- 列番号が数式の場合に計算結果が1以上の数値になっていない
- 検索値の文字数が255文字以内になっていない
1-1. 列番号が1以上の数字になっていない
VLOOKUPの列番号の指定が１より小さいと、#VALUE!エラーとなります。
必ず1以上の数字でなければなりません。
（参考）小数を含む場合は小数点以下切り捨てになる
列番号の指定が小数を含む場合は、小数点以下は切り捨てになります。
切り捨て後の数字が１より小さくなると、#VALUE! エラーとなります。
1-2. 列番号が数式の場合に計算結果が1以上の数値になっていない
列番号を数式で指定している場合も、計算結果が1より小さい場合は#VALUE!エラーとなります。
列番号の指定でよく使われるのはCOLUMNです。(※)COLUMNは下記の(参考)を参照ください。
「=COLUMN()」に加減算することで列番号を作り出します。このとき調整数値を間違えると#VALUE!エラーとなります。
（参考）列番号を数式で指定する方法
参考に、VLOOKUPの列番号をCOLUMNで指定する方法を解説します。
VLOOKUPは通常、数式をコピーしても列番号は変化しません。そのためコピーした後、列番号を手で書き換える必要がありました。
そこで横方向へのコピーで連動して変化するCOLUMN()を使う方法があります。
=COLUMN()
数式のあるセルの列番号が表示される
COLUMN()は、数式があるセルの列番号を表示します。数式がA列のセルにあるなら「1」が表示されます。（下図参照）
| セルの列番号 | A | B | C | D | E | F | G | ・・・ |
| =COLUMN() | 1 | 2 | 3 | 4 | 5 | 6 | 7 | ・・・ |
このCOLUMN()を加減算して、VLOOKUPに必要な列番号を作り出します。VLOOKUPを右へコピーすると、列番号もあわせて変化することを利用します。列番号が一つずつ変化して欲しいケースであれば、一つの数式で足りるわけです。
=VLOOKUP($H2,$A:$F,COLUMN()-7,FALSE)
数式を右へコピーすると連動して列番号が変化するので、式を共通化することができます。
1-3. 検索値が255文字以内になっていない
検索値が255文字を超えると#VALUE!エラーが発生します。
上図では、検索値を1文字ずつ増やしてみました。255文字までは正常な値を返していますが、256文字でVALUE! エラーが発生しています。
(※)行数は関係ありません。255行を超えても大丈夫です。VLOOKUPは100万行まで検索できます。
詳しくはこちら
≫ エクセルの商品マスタ作成を自動転記で入力効率化＆検索100万件でも楽々｜
2. VLOOKUPの #VALUE! エラーの解決方法
この章では、VLOOKUPの#VALUE!エラーの解決方法を解説します。
列番号を数字で直接書き込んでいる場合は、目で確認するだけです。1以上の数字になっているかチェックしましょう。
問題は数式で指定している場合です。少々テクニックが必要ですので、図を用いて解説します。
2-1. 数式の列番号以外の部分を削除してチェックする方法
(1) 数式を念のため丸ごとコピー
チェックしたい数式を念のため丸ごとコピーし、Wordやメモ帳などにコピーしておきます。
(2) 数式のあるセルを選択
数式の入っているセルを選択します。数式窓に数式が表示されます。
(3) 列番号の部分以外を削除
列番号の部分を残し、それ以外を削除します。
今回の例では「＝」と「column()-7」を残し、それ以外を削除します。
(4) チェック完了なら「Ctrl+Z」で元に戻す
列番号が問題なく表示されたらチェック完了です。「Ctrl+Z」で元に戻しましょう。
今回の例では「2」が表示されます。
2-2. 数式の一部を切り出して別のセルに貼り付けてチェックする方法
(1) 数式のあるセルを選択
チェックしたい数式の入っているセルを選択します。
数式窓に数式が表示されるので、クリックします。
すると、検索値と検索範囲のセルが色分けして表示されます。
(2) 列番号を指定している部分だけをドラッグしてコピー
列番号を指定している部分だけをドラッグしてコピーします。
今回の例では「COLUMN()-7」です。
ここで「Esc」キーを押し、数式の選択状態を解除します。
検索値と検索範囲の色分けが消えたことを確認してください。
数式窓にあるを数式をさわっているときに、他のセルをクリックしてはいけません。そのセルが数式に組み込まれてしまう恐れがあります。
(3) 同じ列に貼り付けし先頭に「＝」を追加
真下の空いているセルに貼り付けます。
そのままでは数式が文字列としてそのまま表示されてしまうので、先頭に「＝」を追加します。
想定通りの列番号が表示されているかチェックしましょう。
今回の例では「2」が表示されています。
必ず同じ列に貼り付けてください。COLUMNは列の番地によって値が異なります。真下以外の列に貼り付けると、異る結果を表示してしまいチェックになりません。
まとめ｜#VALUE! エラーが発生したら列番号を確認
この記事では、#VALUE! エラーの原因と解決方法を解説しました。
#VALUE! エラーは列番号の指定に誤りがある場合に発生します。データをむやみにいじらず、列番号の部分を中心にチェックしましょう。計算式で列番号を作り出している場合は、そのままでは確認が難しいので、列番号の部分だけ切り出すなどの工夫が必要です。
修正しない限りいつまでも出続けるので、放置しないようにしましょう。
本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルもダウンロード可能です。
≫ PDFファイル参照ページはこちら
VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事も参照ください。
≫ 超図解！VLOOKUP｜基礎から応用まで完璧マスター＆便利技20選
エクセルでお困りのことがありましたら、『エクセルの窓口』で検索してください。
ブックマークやお気に入りに登録していただけたら幸いです。