Excel で数式エラーを検出する

数式では、予期しない結果が返されたり、エラー値が表示されることがあります。 これらのエラーの原因を検出および調査し、解決方法を決定するために使用できるいくつかのツールを紹介します。

簡単な数式を入力する方法を学ぶ

数式とは、ワークシートの値を計算する等式のことです。 数式は、等号 (=) で始まります。 たとえば、次の数式は 3 を 1 に加算します。

=3+1

数式には、関数、セル参照、演算子、定数のいずれか、またはすべてを含めることもできます。

のパーツ

1. 関数: Excel に含まれる関数は、特定の計算を実行するエンジニアリングされた数式です。 たとえば、PI() 関数は円周率の値 (3.142...) を返します。

2. 参照: 個々のセルまたはセルの範囲を参照します。 A2 はセル A2 の値を返します。

3. 定数: 数式に直接入力される数値や文字列の値です (例: 2 など)。

4. 演算子: ^ (キャレット) 演算子はべき算を実行し、* (アスタリスク) 演算子は乗算を実行します。 値の加算と減算には + と - を使用し、除算には / を使用します。

   注

   一部の関数は、引数と呼ばれるものを必要とします。 引数は、その関数が計算を実行するために使用する値です。 必要に応じて、関数のかっこ () の間に引数が配置されます。 PI 関数には引数は必要ないため、空白です。 一部の関数では、1 つ以上の引数が必要であり、追加の引数の余地を残すことができます。 引数は、場所の設定に応じてコンマまたはセミコロン (;) で区切る必要があります。

たとえば、SUM 関数の場合、必須の引数は 1 つのみですが、合計 255 個の引数を指定できます。

=SUM(A1:A10) は、1 つの引数の例です。

=SUM(A1:A10, C1:C10) は、引数が複数の場合の例です。

数式の入力時によくあるエラーを修正する

次の表は、ユーザーが数式を入力するときに最も多く発生するエラーの一部と、その修正方法をまとめたものです。

確認事項	詳細情報
すべての関数が等号 (=) で始まっている	等号を省略すると、入力した内容がテキストまたは日付として表示される可能性があります。 たとえば、「 SUM(A1:A10)」と入力すると、テキスト文字列 SUM(A1:A10) が表示され、計算は実行されません。 「11/2」と入力すると、11 を 2 で割る計算は実行されず、代わりに "11 月 2 日" という日付 (書式が [標準] の場合) が表示されます。
すべてのかっこを正しく組み合わされている	すべてのかっこに、対応するかっこ (左かっこと右かっこ) が存在することを確認します。 数式で関数を使用する場合は、各かっこが正しく機能するために正しい位置に配置することが重要です。 たとえば、式 =IF(B5<0),"Not valid",B5*1.05) は機能しません。 数式は次のようになります :=IF(B5<0,"Not valid",B5*1.05)。
セル範囲の指定にコロンが使用されている	セル範囲を参照するときは、範囲内の先頭のセルへの参照と範囲内の末尾のセルへの参照との間をコロン (:) で区切ります。 たとえば、 =SUM(A1:A5)、 =SUM(A1 A5)ではなく、#NULL を返します。 エラー。
必要な引数がすべて入力されている	一部の関数には必須の引数があります。 また、引数の数が多すぎないかを確認します。
正しい型の引数が入力されている	SUM 関数などの一部の関数は、数値の引数を必要とします。 また、REPLACE 関数のように、1 つ以上の文字列の引数を必要とする関数もあります。 間違った種類のデータを引数として使用すると、Excel から予期しない結果が返されたり、エラーが表示されたりする可能性があります。
64 レベルを超える関数をネストしない	関数内に、64 レベルを超える関数を入力またはネストすることはできません。
他のシートの名前を単一引用符で囲む	数式が他のワークシートまたはブックの値またはセルを参照し、他のブックまたはワークシートの名前にスペースまたはアルファベット以外の文字が含まれている場合は、その名前を単一引用符 ( ' ) で囲む必要があります ( 例: ='Quarterly Data'!D3、または ='123'!A1。
数式内でワークシート名を参照する場合、その名前の後に感嘆符 (!) が付けられている	たとえば、同じブック内の "四半期データ" というシートのセル D3 から値を返すには、='四半期データ'!D3 という数式を使用します。
外部参照に参照先のブックへのパスが含まれている	それぞれの外部参照に、ブック名とそのブックへのパスが含まれていることを確認します。 ブックへの参照にはブックの名前が含まれており、角かっこで囲む必要があります (例: [Workbookname.xlsx])。 参照には、ブック内のワークシートの名前も含まれている必要があります。 参照するブックが Excel で開かれていなくても、ブックへの参照を数式に含めることができます。 =ROWS('C:\My Documents\[Q2 Operations.xlsx]Sales'!A1:A8) のように、ファイルへの完全なパスを指定します。 この数式は、他のブックのセル A1 から A8 までを含む範囲内の行数 (8) を返します。 メモ： 前の例と同様に、完全なパスに空白文字が含まれている場合は、パスを単一引用符で囲む必要があります (パスの先頭と、感嘆符の前のワークシートの名前の後)。
数値を書式なしで入力する	数式に指定する数値に書式を設定することはできません。 たとえば、"¥1,000" という数値を指定する場合、数式には「1000」と入力します。 数値の一部としてカンマを入力すると、そのカンマは区切り記号と見なされます。 数値が桁区切り記号または通貨記号を表すように表示するには、数値を入力した後にセルの書式を設定します。 たとえば、セル A3 の値に 3100 を追加し、数式 =SUM(3,100,A3) を入力する場合、Excel は数値 3 と 100 を追加し、A3 に 3100 を追加するのではなく、その合計を A3 の値に追加します。これは =SUM(3100,A3) になります。 または、数式 =ABS(-2,134) を入力すると、ABS 関数が 引数 =ABS(-2134) を 1 つだけ受け取るため、エラーが表示されます。

一般的な数式エラーを修正する

特定のルールを実装して数式のエラーを確認することができます。 これらのルールは、ワークシートにエラーがないことを保証するものではありませんが、一般的なエラーの検出には非常に役立ちます。 使用するルールは、個別にオンまたはオフにすることができます。

エラーを検出して修正するには、2 通りの方法があります。エラーを一度に 1 つずつ修正する方法 (スペル チェックなど) と、ワークシートにデータを入力したときにその場でチェックする方法です。

Excel に表示されるオプションを使用してエラーを解決することも、[エラーの無視] を選択してエラーを 無視することもできます。 特定のセルでエラーを無視すると、そのセルと同じエラーは以降のエラー チェックで表示されなくなります。 ただし、無視したエラーのすべてをリセットして、もう一度チェックすることもできます。

エラー チェック ルールをオンまたはオフにする

1. Windows 上の Excel の場合は、 File>Options>Formulas に移動します。
   [Excel on Mac] の場合は、[ Excel] メニュー > [基本設定] > [エラー チェック] を選択します。

2. [エラー チェック] で、[バックグラウンドでエラー チェックを行う] チェック ボックスをオンにします。 見つかったエラーは、セルの左上隅に三角形でマークされます。

   

3. エラーが発生したセルを示す三角形の色を変更するには、[次の色でエラーを示す] ボックスで、目的の色を選びます。

4. [エラー チェック ルール] で、以下のルールのチェック ボックスをオンまたはオフにします。

   • エラーが発生する数式を含むセル: 数式では、予期される構文、引数、またはデータ型が使用されません。 エラー値には、#DIV/0!、#N/A、#NAME?、#NULL!、#NUM!、#REF!、および #VALUE! が含まれます。 これらのエラー値にはそれぞれ異なる原因があり、さまざまな方法で解決されます。

     注

     エラー値をセルに直接入力すると、そのエラー値として格納されますが、エラーとしてマークされません。 ただし、別のセルの数式がそのセルを参照している場合、数式はそのセルのエラー値を返します。

   • テーブル内の一貫性のない計算列の数式: 計算列には、例外を作成するマスター列の数式とは異なる個々の数式を含めることができます。 次のいずれかの操作を行うと、集計列の例外が作成されます。

     • 集計列のセルに数式以外のデータを入力する。
     • 計算列セルに数式を入力し、Ctrl + Z キーを押すか、クイック アクセス ツール バーの を選択します。
     • 1 つ以上の例外が既に含まれている集計列に新しい数式を入力する。
     • 集計列の数式と一致しないデータを集計列にコピーする。 コピーするデータに数式が含まれている場合、この数式で集計列のデータが上書きされます。
     • 集計列の行の 1 つによって参照されている別のワークシート領域にあるセルを移動または削除する。

   • [2 桁の年が含まれるセル]: 世紀を誤って解釈される可能性のある 2 桁の日付文字列が数式に含まれています。 たとえば、数式 =YEAR("1/1/31") の日付は、1931 年または 2031 年のどちらにも解釈できます。 このルールは、あいまいな日付文字列の確認に使用します。

   • テキスト形式またはアポストロフィの前に書式設定された数値: セルには、テキストとして格納された数値が含まれます。 他のソースからデータをインポートすると、数値が文字列として扱われる場合があります。 テキストとして格納された数値は、予期しない並べ替え結果を引き起こす可能性があるため、数値に変換することをお勧めします。 '=SUM(A1:A10) はテキストと見なされます。

   • 数式が領域内の他の数式と一致しない: 数式は、その近くにある他の数式のパターンと一致しません。 多くの場合、他の数式に隣接する数式は、使用される参照でのみ異なります。 次の 4 つの隣接する数式の例では、隣接する数式が 1 行ずつ増加し、1 行ずつインクリメントされるため、セル D4 の数式 =SUM(A10:C10) の横にエラーが表示されます。Excel では数式 =SUM(A4:C4) が必要です。

     

     数式で使用される参照が隣接する数式の参照と一致しない場合、エラーが表示されます。

   • [領域内のセルを除いた数式]: データの元のセル範囲と数式を格納するセルの間に挿入したデータへの参照が、数式に自動的に組み込まれていない可能性があります。 このルールは、数式内の参照と、数式を格納するセルに隣接したセルの実際の範囲を比較します。 隣接したセルに追加の値が格納されていて空白ではない場合、Excel は、数式の横にエラーを表示します。
     たとえば、セル D5、D6、D7 は数式で参照されているセルと数式 (D8) を含むセルに隣接しており、それらのセルには数式で参照されている必要があるデータが含まれているため、このルールを適用すると、数式 =SUM(D2:D4) の横にエラーが挿入されます。

     

   • 数式を含むロックされていないセル: 数式は、保護のためにロックされていません。 既定では、ワークシート上のすべてのセルはロックされているため、ワークシートが保護されている場合は変更できません。 これは、数式の偶発的な削除や変更など、不注意によるミスを回避するのに役立ちます。 このエラーは、セルがロックを解除するように設定されているが、シートは保護されていないことを示します。 セルがロックされていないことを確認します。

   • [空白セルを参照する数式]: 数式に空白セルへの参照が含まれています。 次の例で示すように、これにより、意図しない結果が発生する場合があります。
     次のセルの列内にある数値の平均を計算するとします。 3 番目のセルが空白の場合、計算には含まれず、結果は 22.75 になります。 3 番目のセルに 0 が格納されている場合、結果は 18.2 になります。

     

   • [テーブルに入力されたデータが無効]: テーブル内に入力規則のエラーがあります。 [データ] タブ > [データ ツール] グループ > [データの検証] に移動して、セルの検証設定を確認します。

一般的な数式エラーを一度に 1 つずつ修正する

1. エラーをチェックするワークシートを選びます。

2. 計算方法に手動での計算が設定されている場合、F9 キーを押して再計算を実行します。
   [ エラー チェック ] ダイアログ ボックスが表示されない場合は、 数式>Formula Auditing>Error Checking を選択します。

3. 以前にエラーを無視したことがある場合は、次の手順を実行して、それらのエラーを再度チェックできます。[ファイル>Options>Formulas] に移動します。 Excel on Mac の場合は、[ Excel] メニュー > [基本設定] > [エラー チェック] を選択します。
   [ エラー チェック ] セクションで、[ 無視されたエラーのリセット>OK] を選択します。

   

   注

   無視したエラーをリセットすると、作業中のブック内のすべてのシートのすべてのエラーがリセットされます。

   ヒント

   [エラー チェック] ダイアログ ボックスを数式バーのすぐ下に移動すると、操作しやすくなる場合があります。

   

4. ダイアログ ボックスの右側にあるいずれかのアクション ボタンを選択します。 使用できる処理は、エラーの種類によって異なります。

5. [次へ] を選択します。

一般的な数式エラーを個別に修正する

1. セルの横にある [ エラー チェック を選択し、目的のオプションを選択します。 使用できるコマンドはエラーの種類によって異なり、最初の項目にエラーが示されます。
   [エラーの無視] を選択した場合、エラーは連続するチェックごとに無視されるようにマークされます。

   

# エラー値を修正する

数式で結果を正しく評価できない場合は、#####、#DIV/0!、#N/A、#NAME?、#NULL!、#NUM!、#REF!、#VALUE! などのエラー値が表示されます。 エラーの種類ごとに原因が異なり、解決策が異なります。

次の表では、このエラーの詳細が記載されている記事へのリンクと、作業を開始するための簡単な説明を示します。

トピック	説明
#### エラーを修正する	このエラーは、セルのすべての文字を表示するのに列幅が足りないか、またはセルで負の日付か時刻が使用されている場合に表示されます。 たとえば、=2008/06/15-2008/07/01 のように、過去の日付から将来の日付を減算する数式では、負の日付が返されます。 ヒント： 列ヘッダー間をダブルクリックして、セルを自動的に収めてみてください。 Excel で修正されたすべての文字を表示できないため、### が表示される場合。
エラー値 #DIV/0! を修正する	このエラーは、数値をゼロ (0) または値が含まれていないセルで除算した場合に表示されます。 ヒント： 次の例のようなエラー ハンドラーを追加します。これは =IF(C2,B2/C2,0)
エラー値 #N/A を修正する	このエラーは、関数または数式に使用できない値の場合に表示されます。 VLOOKUP のような関数を使用している場合、参照しようとしているものがルックアップ範囲で一致していますか? ほとんどの場合、それはしません。 #N/A が表示されないようにするために IFERROR を使用してみてください。 ここでは、次の数式を使用しています。 =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)
エラー値 #NAME? を修正する	このエラーは、数式の文字列が認識されない場合に表示されます。 たとえば、範囲名や関数の名前のスペルが正しくない可能性があります。 メモ： 関数を使用している場合は、関数名のスペルが正しいことを確認します。 次の場合、SUM のスペルが正しくありません。 "e" を削除すると、Excel によって修正されます。
エラー値 #NULL! を修正する	このエラーは、指定した 2 つのセル範囲に共通部分がない場合に表示されます。 共通部分を参照する場合は、演算子として数式の参照間にスペースを入力します。 メモ： 範囲が正しく分離されていることを確認します。領域 C2:C3 と E4:E6 が交差しないため、数式 =SUM(C2:C3 E4:E6) を入力すると、#NULL が返されます。 エラーを返します。 C と E の範囲の間にコンマを置くと、=SUM(C2:C3,E4:E6)が修正されます。
エラー値 #NUM! を修正する	このエラーは、数式または関数に無効な数値が含まれている場合に表示されます。 IRR や RATE などの反復処理を行う関数を使用していますか? その場合は、#NUM! error は、おそらく関数が結果を見つけることができないためです。 解決手順については、ヘルプ トピックを参照してください。
エラー値 #REF! を修正する	このエラーは、セル参照が無効な場合に表示されます。 たとえば、他の数式で参照されたセルを削除した場合や、他の数式で参照されたセルの上に移動したセルを貼り付けた場合などです。 行や列を誤って削除したことはありますか。 列 B を削除すると、数式 =SUM(A2,B2,C2) はどうなるでしょうか。 [元に戻す] (Ctrl + Z) を使用して削除を元に戻すか、数式を再構築するか、列 B が削除されたときに自動的に更新される =SUM(A2:C2) のような連続範囲参照を使用します。
エラー値 #VALUE! を修正する	異なるデータ型を含むセルが数式に含まれている場合、Excel はこのエラーを表示することがあります。 さまざまなデータ型で算術演算子 (+、-、*、/、^) を使用していますか? その場合は、代わりに 関数を使用してみてください。 この場合、=SUM(F2:F5) は問題を修正します。

ウォッチ ウィンドウを使用して数式と計算結果を確認する

ワークシートにセルが表示されない場合は、[ウォッチ ウィンドウ] ツール バーでそれらのセルとその数式を確認できます。 ウォッチ ウィンドウは、大きなワークシートで数式の計算と結果を検査、分析、確認するときに便利です。 ウォッチ ウィンドウを使用すると、ワークシートを何度もスクロールしたり、ワークシートのさまざまなパーツに移動したりする必要がなくなります。

できますこのツール バーは、他のツール バーと同様に移動またはドッキングできます。 たとえば、ウィンドウの下部に固定することができます。 このツール バーは、セルのプロパティ 1) ブック、2) シート、3) 名前 (セルに対応する名前付き範囲がある場合)、4) セル アドレス、5) 値、および 6) 数式を追跡します。

ウォッチ ウィンドウにセルを追加する

1. 確認するセルを選びます。
   数式を含むワークシートのすべてのセルを選択するには、[ホーム>編集] に移動し> [検索] & [選択] を選択します (または、Mac で Ctrl + G、または Control + G を使用できます) >[Go To Special>Formulas] を選択します。

   

2. [数式>Formula Auditing] に移動し>ウォッチ ウィンドウを選択します。

3. [ ウォッチの追加] を選択します。

   

4. 監視するすべてのセルが選択されていることを確認し、[ 追加] を選択します。

   

5. ウォッチ ウィンドウの列幅を変更するには、列見出しの右の境界をドラッグします。

6. ウォッチ ウィンドウのツール バーにある参照元のセルを表示するには、その項目をダブルクリックします。

   注

   他のブックへの外部参照が含まれているセルは、そのブックが開いている場合にのみウォッチ ウィンドウのツール バーに表示されます。

ウォッチ ウィンドウからセルを削除する

1. [ウォッチ ウィンドウ] ツール バーが表示されない場合は、[数式>Formula Auditing] に移動し>ウォッチ ウィンドウを選択します。

2. 削除するセルを選びます。
   複数のセルを選択するには、Ctrl キーを押してセルを選択します。

3. [ ウォッチの削除] を選択します。

   

ネストした数式を 1 レベルずつ検証する

ネストされた数式では、中間の計算や論理テストが複数あるため、最終的な計算結果がどのように算出されたかを理解することが難しい場合があります。 ただし、Excel for Windows で 数式の評価 を使用すると、数式の計算順序で評価された入れ子になった数式のさまざまな部分を確認できます。 たとえば、数式 =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) は、次の中間結果を確認できる場合に理解しやすくなります。

[数式の検証] ダイアログ ボックス内	説明
=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)	ネストされた数式が最初に表示されます。 この例では、AVERAGE 関数と SUM 関数が IF 関数内にネストされています。 セル範囲 D2:D5 には、55、35、45、25 の値があるため、AVERAGE(D2:D5) 関数の結果は 40 になります。
=IF(40>50,SUM(E2:E5),0)	セル範囲 D2:D5 には、55、35、45、25 の値があるため、AVERAGE(D2:D5) 関数の結果は 40 になります。
=IF(False,SUM(E2:E5),0)	40 は 50 より小さいため、IF 関数の最初の引数 (論理式引数) の式は False になります。 IF 関数が 3 番目の引数 (偽の場合の引数) を返します。 SUM 関数は、IF 関数の 2 番目の引数 (真の場合の引数) に当たり、式が True の場合しか結果が返されないため、この場合は評価されません。

1. Excel for Windows で、評価するセルを選択します。 一度に 1 つのセルのみが検証できます。
2. 数式>Formula Auditing>Evaluate Formula に移動します。
3. [検証] を選択し、下線が表示されている参照の値を確認します。 検証結果は斜体で表示されます。
   数式の下線付きの部分が別の数式への参照である場合は、[ ステップ イン ] を選択して、[ 評価 ] ボックスに他の数式を表示します。 [ステップ アウト] を選択すると、前のセルと数式に戻ります。
   同じ参照が数式で二度目に表示されるとき、または数式で他のブックのセルが参照されている場合、[ステップ イン] ボタンは使用できません。
4. 数式の各部分が評価されるまで、[ 評価 ] を選択し続けます。
5. 評価をもう一度表示するには、[ 再起動] を選択します。
6. 評価を終了するには、[ 閉じる] を選択します。

補足説明

Excel Tech Community の専門家にいつでも依頼したり、コミュニティでサポートを受けたりすることができます。

関連項目

数式とセルの関係を表示する

壊れた数式のエラーを回避する方法