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)」と入力すると、Excel にはテキスト文字列 SUM(A1:A10) が表示され、計算は実行されません。 「11/2」と入力すると、11 を 2 で割る計算は実行されず、代わりに "11 月 2 日" という日付 (書式が [標準] の場合) が表示されます。
すべてのかっこを正しく組み合わされている	すべてのかっこに、対応するかっこ (左かっこと右かっこ) が存在することを確認します。 数式で関数を使用する場合、関数が正しく機能するためには、各かっこが正しい位置にあることが重要です。 たとえば、閉じかっこが 2 つあり、開きかっこが 1 つしかないため、数式 =IF(B5<0),"無効",B5*1.05) は機能しません。それぞれ 1 つだけであるはずなのに、閉じかっこが 1 つだけだからです。 数式は、 =IF(B5<0,"無効",B5*1.05) のようになります。
セル範囲の指定にコロンが使用されている	セル範囲を参照するときは、範囲内の先頭のセルへの参照と範囲内の末尾のセルへの参照との間をコロン (:) で区切ります。 たとえば、=SUM(A1 A5) ではなく =SUM(A1:A5) と指定すると、#NULL! が返されます。 エラー。
必要な引数がすべて入力されている	一部の関数には必須の引数があります。 また、引数の数が多すぎないかを確認します。
正しい型の引数が入力されている	SUM 関数などの一部の関数は、数値の引数を必要とします。 また、REPLACE 関数のように、1 つ以上の文字列の引数を必要とする関数もあります。 間違ったデータ型を引数として使用すると、Excel は予期しない結果を返したりエラーを表示したりする可能性があります。
64 レベルを超える関数をネストしない	関数内に、64 レベルを超える関数を入力またはネストすることはできません。
他のシートの名前を単一引用符で囲む	数式が他のワークシートやブックの値やセルを参照していて、他のブックやワークシートの名前にスペースやアルファベット以外の文字が含まれている場合は、その名前を単一引用符 (') で囲む必要があります ( 例: ='四半期データ)。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 では A3 に 3100 を加算する代わりに、数値 3 と 100 を加算し、その合計を A3 の値に加算します。これは =SUM(3100,A3) となります。 または、数式 =ABS(-2,134) を入力すると、ABS 関数は 1 つの引数 = ABS(-2134) のみを受け取るため、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 つの隣接する数式の例では、Excel ではセル D4 の数式 =SUM(A10:C10) の横にエラーが表示されます。これは、隣接する数式が 1 行ずつ増加し、その数式が 8 行ずつ増加するためです。Excel では数式 =SUM(A4:C4) が想定されます。

     

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

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

     

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

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

     

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

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

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

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

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

   

   注

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

   ヒント

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

   

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

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

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

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

   

# エラー値を修正する

数式で結果を正しく評価できない場合、Excel では、#####、#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) を使用して削除を取り消すか、数式を再構築するか、または列 B が削除されたときに自動的に更新される = SUM(A2:C2) のような連続範囲参照を使用します。
エラー値 #VALUE! を修正する	異なるデータ型を含むセルが数式に含まれている場合、Excel はこのエラーを表示することがあります。 異なるデータ型を持つ数学演算子 (+、-、*、/、^) を使用していますか? その場合は、代わりに関数を使用してみてください。 この場合は、=SUM(F2:F5) で問題が修正されます。

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

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

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

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

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

   

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

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

   

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

   

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

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

   注

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

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

1. 「ウォッチ・ウィンドウ」ツールバーが表示されない場合は、「数式」>「数式監査」の順に進み>「ウォッチ・ウィンドウ」を選択します。

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. 数式>数式の監査>数式を評価する順に移動します。
3. [検証] を選択し、下線が表示されている参照の値を確認します。 検証結果は斜体で表示されます。
   数式の下線が表示されている部分が他の数式への参照の場合、[ ステップ イン ] を選択すると、その数式が [評価] ボックスに表示されます。 [ステップ アウト] を選択すると、前のセルと数式に戻ります。
   同じ参照が数式で二度目に表示されるとき、または数式で他のブックのセルが参照されている場合、[ステップ イン] ボタンは使用できません。
4. 数式の各部分が評価されるまで、[ 評価 ] の選択を続けます。
5. 評価版をもう一度表示するには、[ 再起動] を選択します。
6. 評価を終了するには、[ 閉じる] を選択します。

補足説明

Excel 技術コミュニティの専門家にいつでも質問するか、コミュニティでサポートを受けることができます。

関連項目

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

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