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

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

確認事項	詳細情報
すべての関数が等号 (=) で始まっている	等号を省略すると、入力した内容がテキストまたは日付として表示される可能性があります。 たとえば、「SUM(A1:A10)」と入力すると、Excel は、"SUM(A1:A10)" という文字列を表示し、数式の計算を実行しません。 「11/2」と入力すると、Excel は、11 を 2 で割る計算を実行しないで、日付 "11 月 2 日" (セルの書式が [標準] の場合) を表示します。
すべてのかっこを正しく組み合わされている	すべてのかっこに、対応するかっこ (左かっこと右かっこ) が存在することを確認します。 数式で関数を使用する場合は、各かっこが正しく機能するために正しい位置に配置することが重要です。 たとえば、数式 =IF(B5<0),"Not valid",B5*1.05) は、右かっこが 2 つあり、左かっこが 1 つしかないため機能しません (必要な左かっこと右かっこはそれぞれ 1 つのみです)。 正しい数式は、=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」と入力します。 数値の一部としてコンマを入力すると、Excel はそのコンマを区切り記号と見なします。 数値が桁区切り記号または通貨記号を表すように表示するには、数値を入力した後にセルの書式を設定します。 たとえば、セル A3 の値に 3100 を追加し、数式 =SUM(3,100,A3) を入力 Excel 、数値 3 と 100 を加算し、その合計を A3 から A3 に加算するのではなく、その合計を A3 の値に追加します。これは =SUM(3100,A3) になります。 または、数式 =ABS(-2,134) と入力すると、ABS関数が引数 =ABS(-2134) の 1 つだけを受け入れるため、 Excel エラーが表示されます。

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

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

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

1. Windows 上の Excel の場合は、[ ファイル > オプション ] > [数式] に移動します
   [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. エラーをチェックするワークシートを選びます。

2. 計算方法に手動での計算が設定されている場合、F9 キーを押して再計算を実行します。

   [ エラー チェック ] ダイアログ ボックスが表示されない場合は、[ 数式 ] >[ 数式の監査 ] > [エラー チェック] を選択します。

3. 以前にエラーを無視した場合は、次の手順を実行して、これらのエラーを再度チェックできます。[ファイル > オプション] > 数式に移動します。 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) を使用して削除を取り消して、数式を再作成するか、連続する範囲の参照を使用します。たとえば、=SUM(A2:C2) は、列 B が削除されると、自動的に更新されます。
エラー値 #VALUE! を修正する	異なるデータ型を含むセルが数式に含まれている場合、Excel はこのエラーを表示することがあります。 さまざまなデータ型で算術演算子 (+、-、*、/、^) を使用していますか? その場合は、代わりに 関数を使用してみてください。 この場合、=SUM(F2:F5) は問題を修正します。

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

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

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

1. 確認するセルを選びます。

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

   

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

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

   

4. watchするすべてのセルを選択したことを確認し、[追加] を選択します。

   

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

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

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

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

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

2. 削除するセルを選びます。

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

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

   

ネストされた数式では、中間の計算や論理テストが複数あるため、最終的な計算結果がどのように算出されたかを理解することが難しい場合があります。 ただし、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. [数式] > [数式の監査] > [数式の評価] に移動します。

3. [検証] を選択し、下線が表示されている参照の値を確認します。 検証結果は斜体で表示されます。

   数式の下線付きの部分が別の数式への参照である場合は、[ ステップ イン ] を選択して、[ 評価 ] ボックスに他の数式を表示します。 [ステップ アウト] を選択すると、前のセルと数式に戻ります。

   同じ参照が数式で二度目に表示されるとき、または数式で他のブックのセルが参照されている場合、[ステップ イン] ボタンは使用できません。

4. 数式の各部分が評価されるまで、[ 評価 ] を選択し続けます。

5. 評価をもう一度表示するには、[ 再起動] を選択します。

6. 評価を終了するには、[ 閉じる] を選択します。