数式のエラーを検出する

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

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

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

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

1. Windows 版 Excel の場合はFileOptionsFormulas
   、[ファイル > オプション > 数式] をクリックします。 Excel on Mac の場合は、[ excel] メニューの [> 環境設定 > エラーチェック] をクリックします。 

   Excel 2007で、Microsoft Office ボタン 、[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つの隣接した数式の例では、セル D4 の数式「= SUM (A10: C10)」の横にエラーが表示されます。隣接する数式は1行ずつ増加し、1つは8行ずつ増加するため、Excel では数式 = SUM (A4: C4) と見なされます。

     

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

   • [領域内のセルを除いた数式]: データの元のセル範囲と数式を格納するセルの間に挿入したデータへの参照が、数式に自動的に組み込まれていない可能性があります。 このルールは、数式内の参照と、数式を格納するセルに隣接したセルの実際の範囲を比較します。 隣接したセルに追加の値が格納されていて空白ではない場合、Excel は、数式の横にエラーを表示します。

     たとえば、このルールを適用した場合、Excel は、数式 =SUM(D2:D4) の横にエラーを挿入します。これは、セル D5、D6、D7 が、数式で参照されるセルと数式を格納するセル (D8) に隣接しており、データを格納するそれらのセルを数式で参照する必要があるためです。

     

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

   • [空白セルを参照する数式]: 数式に空白セルへの参照が含まれています。 次の例で示すように、これにより、意図しない結果が発生する場合があります。

     次のセルの列内にある数値の平均を計算するとします。 3 番目のセルが空の場合、このセルは計算に含まれないため、結果は 22.75 になります。 3 番目のセルに 0 が格納されている場合、結果は 18.2 になります。

     

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

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

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

   [エラー チェック] ダイアログが表示されない場合、[数式] タブ、[ワークシート分析]、[エラー チェック] ボタンの順にクリックします。

3. 以前にエラーを無視した場合、[ファイル]、[オプション]、[数式] の順にクリックすると、それらのエラーをもう一度確認できます。 Excel on Mac の場合は、[ excel] メニューの [> 環境設定 > エラーチェック] をクリックします。

   [エラー チェック] セクションの [無視したエラーのリセット]、[OK] の順にクリックします。

   

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

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

   

4. ダイアログ ボックスの右側にある処理ボタンの 1 つをクリックします。 使用できる処理は、エラーの種類によって異なります。

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。 エラーの原因として考えられるのは、関数が結果を見つけることができないためです。 解決手順については、ヘルプトピックを参照してください。
エラー値 #REF! を修正する	このエラーは、セル参照が無効な場合に表示されます。 たとえば、他の数式が参照しているセルが削除されている場合や、他の数式が参照しているセルに別のセルが貼り付けられている場合などです。 行や列を誤って削除したことはありますか。 列 B を削除すると、数式 =SUM(A2,B2,C2) はどうなるでしょうか。 [元に戻す] (Ctrl+Z) を使用して削除を取り消して、数式を再作成するか、連続する範囲の参照を使用します。たとえば、=SUM(A2:C2) は、列 B が削除されると、自動的に更新されます。
エラー値 #VALUE! を修正する	異なるデータ型を含むセルが数式に含まれている場合、Excel はこのエラーを表示することがあります。 さまざまなデータ型で算術演算子 (+、-、*、/、^) を使用していますか? その場合は、代わりに関数を使ってみてください。 この場合は = SUM (F2: F5) で問題が修正されます。

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

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

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

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

   ワークシートで、数式が入力されているすべてのセルを選択するには、[ホーム] タブの [編集] グループで、[検索と選択] (または Ctrl + G キー、Mac の場合は Control + G キー)、[選択オプション]、[数式] の順にクリックします。

   

2. [数式] タブの [ワークシート分析] で [ウォッチ ウィンドウ] をクリックします。

3. [ウォッチ式の追加] をクリックします。

   

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

   

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

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

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

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

1. ウォッチ ウィンドウのツール バーが表示されない場合、[数式] タブの [ワークシート分析] グループで、[ウォッチ ウィンドウ] をクリックします。

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

   複数のセルを選ぶには、Ctrl キーを押しながら、セルをクリックします。

3. [ウォッチ式の削除] をクリックします。

   

ネストされた数式では、中間の計算や論理テストが複数あるため、最終的な計算結果がどのように算出されたかを理解することが難しい場合があります。 ただし、[数式の検証] ダイアログ ボックスを使用すると、ネストされた数式の各部分を数式が計算される順序で確認できます。 たとえば、数式 =IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) は、次の中間結果を表示すると理解しやすくなります。

1. 数式を検証するセルを選びます。 一度に 1 つのセルのみが検証できます。

2. [数式] タブ、[ワークシート分析]、[数式の検証] の順に選択します。

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

   数式の下線が表示されている部分が他の数式への参照の場合、[ステップ イン] をクリックすると、その数式が [検証] ボックスに表示されます。 [ステップ アウト] をクリックすると、前のセルと数式に戻ります。

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

4. 数式の各パーツが検証されるまで、[検証] のクリックを繰り返します。

5. 検証結果を再確認するには、[再び開始] をクリックします。

6. 検証を終了するには、[閉じる] をクリックします。