#VALUE!エラーの原因と対応法
Excel(エクセル)の数式で#VALUE!というエラーが出てることがあります。
これはExcelで入力した数式の参照する値が正しくないという意味です。
Excelで#VALUE!エラーが出る要因・原因
以下のようにExcelの数式に文字列の入ったセルが含まれていると#VALUE!というエラーが出てしまいます。
#VALUE!エラーが出ている計算式に使われているセルを確認してみるとセル「Ｂ８」の入力が「30個」となっていることが確認できます。
数式の参照セルに[文字列]があると#VALUE!エラーが表示
セルの入力に数字以外の単位(文字)まで入力されているため、セル全体が文字列と認識され、計算結果に#VALUE!エラーが表示されます。
上下のセルも同様に単位の表示がされているので、上下のセルとの違いを確認し、なぜ#VALUE!エラー判定がされてしまったかを説明します。
書式設定でExcelが単位を表示上,補完している場合と混同しない！
その他のセルも「○○個」と表示されていますが、これらは「セルの書式設定」→「表示形式」→「ユーザー定義」から
種類→「#,##0 “個”」
つまりこのセルは表示上、数字の千桁に「,」(カンマ)をいれ、単位に[個]を自動補完するようExcelで定義されています。
従ってセル「Ｂ７」の入力内容を確認すると「10」で”個”は入力されていないことが確認できます。
※Excelが書式設定に従って表示上,単位を補完して表示された状態
→ Excelで単位付きの数字を計算式で利用する方法
Excelの計算式に#VALUE!エラーが出る原因を探すヒント
書式設定の配置を変更していなければ、(Excelの初期値は)数字は右寄せ、文字列は左寄せになっているので、入力された値が文字列で#VALUE!エラーになっている場合、セルの入力内容が左寄せになっていたら文字列として扱われていると推察できるのではないでしょうか。
次に#VALUE!エラーが表示されないように修正する方法を・・
計算式を直してを#VALUE!エラーが出ないように修正する
1まずは文字列と認識されてしまう原因となる単位「個」を削除Delete
2正しく数字と認識されているセル選択し
3コピーCtrl＋C
4#VALUE!エラーが出ていたセルを選択し、右クリック。
5「形式を選択して貼り付け」
6[書式]だけを貼り付けます。
これで全て数字の正しいExcelシートになります。
ExcelのIFERROR関数を使って#VALUE!エラーを消す方法
※IFERROR関数はExcel 2007以降から使える関数です。
単純に単位を付けた数字を誤入力してしまった場合は値を直せば解決しますが、以下のように数字ではないものを入力したい場合は数字に置き換えることができません。
この場合はExcelのIFERROR関数を使って#VALUE!エラーを非表示にする(もしくは他の指定した文字に置き換える)事ができます。
まず1#VALUE!エラーが出ているセルの計算式を削除します。
2fxボタンから[関数の挿入]ウィンドウを表示。
※関数名一覧に[IFERROR]が見当たらなければ3すべて表示を選択して
4[IFERROR]を選択します。
値に、計算式の5 ＊ 6のセルを選択します。
この場合、商品Aの単価は全ての計算式で参照されることになるので、オートフィル(ドラッグ)で数式をコピーした時に、計算対象となるセルが相対参照で変わってしまうと結果がおかしくなってしまう為、6のセルをクリックした後でF4を押します。
そうすると数式で選択されたセルＣ３が＄Ｃ＄３(列Ｃ固定を意味する＄Ｃと行３を固定を意味する＄３)が追加されます
→ Excelの計算式で参照する値(セル)を固定する方法
7エラーの場合の値として”定休日”を設定します。
これで数式にエラーがあった場合は[定休日]という文字が入力されるようになります。
もちろん[定休日]ではなく[-](ハイフン)などエラーになった際の文字は任意に変えることも可能です。
IFERROR関数を使った数式を上下のセルにドラッグしてコピーします。
先ほどF4で商品単価の参照セルを固定したので、
個数はドラッグで下に向かうと行番号が加算されていきますが、商品A単価($C$3)は数式上固定されたままになります。
この数式をコピーしておけば、今後、個数欄に文字列(例えば定休日)が出てきても数式は#VALUE!エラーにならなくなります。
IFERROR関数の数式を修正したい場合はAｆｘボタンから「関数の引数」ダイアログを表示して行うか、B数式を見ても、大体の予想は付くと思うので、直接数式から修正しても構いません。
#VALUE!エラーの原因と対応法へのコメント