いまさら聞けないExcelの使い方講座

【Excel】数式は正しいのに「#VALUE!」エラーがでるのはなぜ? エラー回避のテクニック

数式は正しいのに「#VALUE!」エラーが!

「#VALUE!」エラーが表示される原因とは?

 正しい数式を入力しているのに、エラーが表示されてしまうことがありませんか? 数式の入力時によく見かけるエラーは「#VALUE!」エラーでしょう。数式内に不適切な値が含まれていると「#VALUE!」と表示されます。

 例えば「数値を入力するべきセルに文字列が入力されていた」「数値の前後にスペースがあった」「数字を全角で入力した」などが主な原因です。つまり、計算対象とするセルに「文字列」が含まれていると「#VALUE!」エラーが表示されることがあるわけです。これが原因追及のヒントになります。

 以下の表を見てください。単価と数量を掛け合わせて売上を求めるセルE11の数式「=C11*D11」は問題ないようですが「#VALUE!」と表示されています。また、セルE11の「#VALUE!」エラーにより、セルE2からE11までの売上を集計するセルG2も「#VALUE!」エラーとなっています。

セルE11の数式「=C11*D11」(①)は問題ないようですが、「#VALUE!」エラー(②)が表示されています。セルE2からE11までの売上を集計するセルG2も「#VALUE!」エラー(③)が表示されています。

 セルD2からD10にも「○個」と入力されているのに「#VALUE!」エラーが表示されないのはなぜでしょう。対策方法を順番に見ていきましょう。

四則演算を利用した数式に「文字列」はNG

 セルE11の「#VALUE!」エラーは、セルD10に「個」と文字列が入力されていることが原因です。[売上]列に入力された「*」を利用した数式では、数値のみが計算対象となります。文字列を含めてはいけません。「*」だけでなく、「+」「-」「/」を利用した四則演算でも同様のルールです。

 セルD11を選択してみれば一目瞭然です。「2個」と入力されています。一方、セルE2からE10では「個」はセルの表示形式で制御しており、数値のみが入力されているため「#VALUE!」エラーは表示されません。

セルD11(④)には「2個」と入力されています
セルD9(⑤)には「7」と数値のみが入力されています

 単位などの表記は[セルの書式設定]ダイアログボックスで簡単に指定できます。該当のセルを選択して[Ctrl]+[1]キーで呼び出し、「#"個"」と入力してください。表示形式で指定する文字列は「"」(ダブルクォーテーション)で囲みます。

該当のセルを選択して[Ctrl]+[1]キーで[セルの書式設定]ダイアログボックスを呼び出します。[ユーザー設定](⑥)をクリックして、「#"個"」と入力(⑦)してください
表示形式の修正後は「#VALUE!」エラーが解消されました

 表示形式を設定するのが面倒なら、設定済みのセルを選択して[ホーム]タブの[書式のコピー/貼り付け]ボタンで書式を変更しても構いません。「文字列」の直接入力はNGと覚えておいてください。

IFERROR関数でエラーを検知する

 セルに入力する数式を工夫してエラーを検知することも可能です。エラーの解決には、原因となるセルの値を修正する必要がありますが、「#VALUE!」といったエラー値よりもわかりやすいメッセージを表示できるので、エクセルに不慣れな人が入力作業をしている時などに役立ちます。

 エラーを判定したい数式にIFERROR関数を組み合わせて、エラー時の値(メッセージ)を「"」で囲んで指定するだけです。

セルE11に「=IFERROR(C11*D11,"入力値を確認")」(⑧)と入力します
エラー時に「入力値を確認」(⑨)と表示されます

関数を利用すればエラーは回避できるものの多用は危険

 「+」の代わりにSUM関数、「*」の代わりにPRODUCT関数を利用することも可能です。これらの関数は文字列を無視して「#VALUE!」エラーを回避します。ただし、「無視する」ということに注意してください。

 以下はセルE11に「=PRODUCT(C11,D11)」と入力した状態です。本来は「980×2」で「1960」が正しいのですが、「980」となっています。入力対象のセルに注釈を入力するようなシーンでは便利ですが、多用するのは避けたほうがいいと思います。

セルE11に「=PRODUCT(C11,D11)」と入力した状態(⑩)。文字列を含むセルは計算対象として扱われません

エクセルの設定を変更する

 [Excelのオプション]でエクセルの設定を変更して「#VALUE!」エラーを回避する方法もあります。この設定は、文字列を「0」として扱います。設定を変更した場合の動作を見てみましょう。

[ファイル]タブから[オプション]をクリックして[Excelのオプション]ダイアログボックスを表示しておきます。[詳細設定](⑪)をクリックして、[計算方法を変更する](⑫)にチェックを付けます。[OK](⑬)をクリックします
セルD11には文字列が含まれているため、「0」と見なされ、セルE11の数式の結果も「0」(⑭)となりました

状況に応じて対処しよう

 今回は「#VALUE!」エラーを回避する方法を紹介しました。「#VALUE!」と表示されると驚いてしまいますが、数値が入力されるべきセルに「文字列」はNG。対処方法により結果が異なることを覚えておけば大丈夫です。状況に応じて落ち着いて対処してください。