【Excel】数式で使用されるデータの形式が正しくありませんとVALUE!エラーが発生 数値はあってるけど何が原因？

こんにちは！　イマジネットPCサポートの橋崎です。

今回は知り合いに作成してもらったExcelファイルが急にエラーが発生して計算しなくなり、その修正を依頼されました。

はたして修正が出来たのでしょうか？

早速、見てみましょう。

計算式が設定されているセルに「#VALUE!」と表示された。

ファイルはそれほど複雑なものではなく、家計簿のような仕組みになっていました。

列の構造は

• 一番左が日付
• その右横に項目名
• 黄色に入金額
• 緑に支出額
• 肌色が残高

前行の残高から入金額を足して出金額を引く単純な数式が肌色の列に設定されていました。

ちなみに行は5000行を超えており結構な数です。上の画像では肌色の残高の一番上（モザイク部分）までは正常に計算されており、その次の行から「#VALUE!」が表示されてしまっています。

ちなみに計算式を入れ替えてもエラーは消えませんでした。

「#VALUE!」はどのような時に表示されるのか

まず先にこの「#VALUE!」エラーはどのような時に表示されるのか確認しましょう。

ファイルのエラーチェックを見ると「数式で使用されるデータの形式が正しくありません。」と表示されていますが、これはどのような意味なのでしょう？

わかりやすく単純なサンプルを作成しました。

数量×単価＝合計

非常にシンプルですね。セル「A2」に数量10を入力、セル「B2」に単価1000を入力するとセル「C2」に設定されている数式「=A2*B2」の計算結果10000が自動計算されます。

では次に単価に数値ではなく、文字「a」を入力してみましょう。

すると計算結果の合計セル「C2」に「#VALUE!」とエラーが表示されました。

エラーチェックを確認すると先程と同じく「数式で使用されるデータの形式が正しくありません。」と表示されています。

これでエラーの原因がわかりましたね。

計算式の対象であるセルに数値ではなく文字が入力された事が原因です。

では、それらを踏まえてお客様のファイルを確認してみます。

しかし計算式の対象セルに入力されている数値は問題ありません。

計算対象セルに入力されているのは数値でした。書式設定は通貨で数値ではありませんが、計算対象としては問題ありません。また再度、数値を再入力しても結果は変わりませんでした。

どうやら数値以外を入力した事で発生しているエラーではないようです。

ねんの為に再度、計算式も確認したのですが、問題はなさそうです。

「はーっ　何でしょうか？　わかりませんね！」

再度、このエラーについては調べてみました。当店が調べた範囲内ですが、サンプルで説明したような内容以外の解決方法を解説した記事は見当たりませんでした。

ファイルの構造が単純なので調べる範囲も狭く、正直言って原因が今のところわかりません。

こんな場合は確認と色々と試すに限る！

では実際にファイルの構造を再確認してみましょう。

これは問題が発生しているお客様のファイルに当店がサンプル数値を入力したものです。

一番上の右に残高「100000」があり、次の行黄色の列、入金額は空白で緑色の列、出金額が「100」と入力されています。したがって残高は「99900」となり、ここまでは正常に計算がされてる事がわかります。

しかし問題は次の行です。

エラーが発生しているセルには上からの数式と同じ構造が引き継がれています。勿論、対象のセルは異なり次行に移行されているので問題ありません。そしてこの行以降エラーが発生し続けています。

繰り返しになりますが、書式設定や入力されている数値に問題はありません。

しかしよく見ると数式に基本的な事がされていないようです。

それを確認してみましょう。

あれっ　これってもしかして良くないんじゃないかな？

黄色の入金列に一切数値が入力されていませんね。但し、ここまでは入金額は入力していたり、されていなかったりしても計算はされています。

しかしこの列のセルも計算対象になっていたはずなので本来、入力する数値がない場合は0（ゼロ）を入力すべきですよね。

ではエラーが発生している入金の黄色列に「0」を入力してみましょう。

スバリ予想は的中したようで、「0」を入力した以降の行がまるで水の流れをせき止めていたゴミを取り除いたように計算結果が表示されて行きました。

とりあえず解決しましたが･･･

あとがき

今回のトラブルの原因は間違って文字を入力した訳ではありませんでした。

原因は数式の対象であるセルにデータがない事が引き金となり発生していたようでした。（多分？）これはLookup関数を設定された事がある方なら経験済だと思いますが、Lookup関数の対象セルにデータがない場合には必ずエラーが発生します。

そのエラーを回避するためにIF関数の入れ子でLookup関数を設定します。

しかし今回はそのような処理はされていませんでした。だって最初から今までエラーは発生しなかったんですからね！

エラーが発生しなければ気が付きませんよ。

しかも今回の処理後に「0」を削除しても、その後の操作でエラーが再発する事はありませんでした。

しかし数式を設定する時には空白が数式の対象となる可能性があるならば、空白に対して回避する数式も基本的には必要であろうと思います。

またこれは別件ですが入力が5000行を超えて途中の集計もできない仕組みでは計算が合わなくなった時に問題です。そのあたりも含めて修正が必要ですね。

その内容もお客様にお伝えしましょう。

それでは、お疲れ様でした。