Excelで数式を使用していると、参照先の値を変更しても計算結果が変わらなかったり、正しい結果が表示されなかったりすることがあります。
原因として、計算方法が「手動」になっている、循環参照が発生している、数値が文字列として保存されているなどが考えられます。
この記事では、数式が正常に計算されない原因と対処法を、症状別に紹介します。
症状１：参照先のセルの値を変更しても計算結果が変わらない
【原因1】計算方法が「手動」になっている
計算方法が「手動」になっていると、数式の計算結果が自動で更新されません。そのため、参照先のセルに入力した値が、計算結果に反映されないことがあります。
計算方法の設定を確認する
計算方法の設定を確認するには、［数式］タブ → ［計算方法の設定］の順にクリックします。表示されたメニューのうち、左側にチェックが付いている項目が、現在設定されている計算方法です。
下記の画像は、計算方法が「手動」に設定されています。
計算方法の「自動」と「手動」の違い
Excelの計算方法は、初期設定では「自動」になっています。
計算方法が「自動」の場合、数式が参照しているセルの値を変更すると、計算結果も自動的に更新されます。
一方、計算方法が「手動」の場合は、参照しているセルの値を変更しても、計算結果は自動で更新されません。計算結果を一時的に更新するには、［F9］キーなどで再計算をする必要があります。常に数式の計算結果を最新の状態にしたい場合は、計算方法を「自動」に戻してください。
例えば、C3セルに「=SUM(E:E)」と入力されているとします。
計算方法が「自動」の場合、E列の空白セルに「100」と入力すると、C3セルの計算結果も自動的に100増えます。
計算方法が「手動」の場合、E列に「100」と入力しても、C3セルの計算結果は変わりません。［F9］キーを押して再計算するか、C3セルの数式を編集して［Enter］キーで確定すると、最新の計算結果が反映されます。
分かりやすく言うと、計算方法が「自動」ならセルの値を変更するたびに必要な数式が再計算されるため、常に最新の計算結果が表示されます。しかし、数式が大量に入力されているブックでは、処理が重くなることがあります。
計算方法が「手動」だと自動的に再計算されないため、処理が重くなりにくいですが、古い計算結果が表示されたままになる可能性があります。
【解決法1-1】計算方法を「自動」に変更する
計算方法を「自動」に変更して、セルへの入力が完了するたびに再計算が実行されるようにします。
設定を変更する手順は、次の通りです。
［数式］タブ → ［計算方法の設定］ボタン → ［自動］ボタン の順にクリックします。
【解決法1-2】一時的に更新する
［数式］タブ → ［再計算実行］をクリックするか、［F9］キーを押して再計算します。
計算方法を「手動」にすると、Excelの処理が極端に重い場合など、入力時の処理を軽くできます。ただし、シートに表示されている計算結果を最新にするには、その都度再計算する必要があります。
症状２：計算結果が正しくない
参照先のセルの値を変更しても正しい計算結果にならない場合は、数式の内容や参照先に問題がないか確認してください。
なお、参照先のセルの値を変更しても計算結果が変わらない場合は、前述した計算方法が［手動］となっている可能性が高いです。
【原因2】循環参照が発生している
循環参照が発生すると、数式が自分自身の計算結果を必要とするため、通常の設定では正しく計算できません。そのため、意図した計算結果が表示されないことがあります。
そもそも、循環参照とは何か？
循環参照とは、数式が直接または間接的に自分自身を参照し、計算が循環して完了できなくなる状態です。
例えば、A1セルに「=A1+1」と入力すると、A1セルがA1セル自身を参照するため、循環参照になります。また、A1セルがB1セルを参照し、B1セルがA1セルを参照している場合も循環参照です。このように、複数のセルを経由して元のセルに戻る場合も含まれます。
【解決法2】循環参照を解消する
［数式］タブ → ［エラーチェック］の▼ → ［循環参照］の順にクリックすると、循環参照が発生しているセルの位置が表示されます。表示されたセルの数式を修正してください。
循環参照の詳しい確認方法や注意点は、下記記事で紹介しています。
【原因3】参照先の数字が文字列として保存されている
セルの表示形式が［文字列］の状態で数字を入力すると、「100」などの数字が文字列として保存されます。
文字列として保存された数字は、SUM関数などの計算対象に含まれないことがあります。
【解決法3】文字列として保存された数字を数値に変換する
修正対象のセルの表示形式を［標準］に変更します。
［ホーム］タブ → ［表示形式］の▼ → ［標準］の順にクリックしてください。
次に修正対象のセルをダブルクリックするか、［F2］キーを押して編集モードにします。
［Enter］キーを押して確定すると、文字列として保存されていた数字が数値として認識されます。
【原因4】文字列の抽出関数で数字を抽出している
LEFT関数やRIGHT関数、MID関数などで抽出した数字も、文字列として扱われます。
そのため、前述した文字列として保存された数字と同様に、SUM関数の合計対象にならないことがあります。
例えば、A1セルに文字列として「1」、A2セルに数値の「2」が入力されているとします。
この状態で「=SUM(A1:A2)」と入力すると、文字列として保存されているA1セルの「1」は計算に含まれないため、「2」が返ります。
【解決法4】文字列を数値に変換する関数を使用する
文字列として抽出された数字は、VALUE関数を使用して数値に変換できます。
- VALUE関数
引数の文字列を数値に変換する。
引数を数値に変換できない場合は、「#VALUE!」を返す。 - IFERROR関数
第1引数がエラーでない場合は、第1引数をそのまま返す。
第1引数がエラーの場合は、第2引数を返す。
※VALUE関数でエラーが発生した場合に、「0」を表示するために使用しています。
数値に変換できる文字列の場合は、次のようにVALUE関数で数値へ変換します。
=VALUE(変換するセル)
数値に変換できない文字列が含まれる可能性があり、エラーの場合に「0」を表示したいときは、IFERROR関数を併用します。
=IFERROR(VALUE(変換するセル),0)
VALUE関数で数値に変換できない場合は「0」が表示され、変換できる場合は数値が返ります。
症状３：想定した値にならない
【原因5】数式の参照先が間違っている
数式を作成するときにセルの指定を間違えたり、数式をコピーしたときに参照先がずれたりすると、想定した値にならないことがあります。
【解決法5】数式の参照先を確認して修正する
数式が参照しているセルを確認し、間違っているセル番地を修正してください。
数式をコピーした後に参照先がずれた場合は、相対参照と絶対参照の指定が正しいかも確認しましょう。
症状４：計算結果ではなく数式がそのまま表示される
セルに計算結果ではなく「=A1+B1」などの数式がそのまま表示されている場合は、計算方法が「手動」になっていることとは別の原因が考えられます。
数式がそのまま表示される原因と対処法については、下記記事をご覧ください。
まとめ
Excelで数式が正常に計算されない場合は、まず症状を確認しましょう。
参照先の値を変更しても計算結果が変わらない場合は、計算方法が［手動］になっていないか確認してください。計算結果が正しくない場合は、循環参照が発生していないか、数字が文字列として扱われていないかを確認します。
また、計算結果は表示されていても想定した値にならない場合は、数式の参照先が間違っていないか確認しましょう。症状に合った原因を確認して修正すれば、正しい計算結果が表示されます。
