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

作成しようとしている数式を Excel が解決できない場合、次のようなエラー メッセージが表示されることがあります。

残念ながら、これは Excel が何をしようとしているのかを理解できないことを意味するため、数式を更新するか、関数が正しく使用されているかを確認する必要があります。

壊れた数式のあるセルに戻ると、編集モードになり、Excel で問題が発生している場所が強調表示されます。 解決方法がわからずに最初からやり直したい場合は、 Esc キーをもう一度押すか、数式バーの [キャンセル ] ボタンを選択して、編集モードを終了します。

先に進む場合は、次のチェックリストにトラブルシューティング手順が記載されています。原因は何が問題だったかの特定に役立ちます。 見出しを選択して詳細を確認します。

数式で正しいリストの区切り記号を使用していますか?

複数の引数を含む数式では、リストの区切り記号を使用して引数を区切ります。 使用する区切り記号は、OS ロケールと Excel の設定によって異なる場合があります。 最も一般的なリストの区切り記号は、コンマ "," とセミコロン ";" です。

関数のいずれかで誤った区切り記号が使用されている場合、その数式の処理は中断されます。

詳細については、以下を参照してください。 リストの区切り記号が正しく設定されていない場合の数式エラー

Excel の数式にポンド (#) エラーが表示されていませんか?

Excel は、#VALUE!、#REF!、#NUM、#N/A、#DIV/0!、#NAME?、#NULL! など、さまざまなポンド (#) エラーをスローします。 数式内の何かが正しく機能していないことを示します。 たとえば、#VALUE! エラーは、正しくない書式設定、または引数でサポートされていないデータ型が原因で発生します。 または、削除されたか他のデータに置き換えられたセルを数式が参照している場合には、#REF! エラーが表示されます。 トラブルシューティングのガイダンスはエラーごとに異なります。

表示されるポンド エラーに応じて、次のトピックを参照してください。

• エラー値 #NUM! を修正する
• エラー値 #VALUE! を修正する
• エラー値 #N/A を修正する
• エラー値 #DIV/0! を修正する
• エラー値 #REF! を修正する
• エラー値 #NAME? を修正する
• エラー値 #NULL! を修正する

Excel の数式で壊れたリンクを修正する

他のスプレッドシートの値を参照する数式が含まれるスプレッドシートを開くたび、参照を更新するか、そのままにするかの選択を求められます。

Excel では、参照値が変更された場合に、現在のスプレッドシートの数式が常に最新の値を指していることを確認する上のダイアログ ボックスが表示されます。 参照を更新する、または更新しない場合はスキップを選ぶことができます。 参照を更新しないことを選択した場合でも、スプレッドシート内のリンクは、手動でいつでも更新できます。

このダイアログ ボックスが起動時に毎回表示されないように無効にすることができます。 これを行うには、[ ファイル > オプション] > [詳細設定] > [全般] に移動し、[ リンクの自動更新前にメッセージを表示する] チェック ボックスをオフにします。

Excel で数式に値ではなく構文が表示される

数式が値を示さない場合は、次の手順を実行します。

• スプレッドシートで数式を表示するように Excel が設定されていることを確認します。 これを行うには、[数式] タブを選択し、[ワークシート分析] で [数式の表示] を選択します。

  ヒント

  キーボード ショートカット (Ctrl + ` キー (Tab キーの上にあるキー) を押す) を使用することもできます。 これを行うと、列が自動的に広がって数式が表示されますが、標準ビューに戻すと列のサイズが変更されますのでご安心ください。

• 上の手順でも問題が解決しない場合は、セルがテキストとして書式設定されている可能性があります。 セルを右クリックし、[ セルの書式設定] > [全般 ] (または Ctrl + 1) を選択し、 F2 キー > Enter キーを押して書式設定を変更します。

• 文字列として書式設定されたセルの範囲が広い列がある場合は、範囲を選択し、任意の表示形式を適用して、[ データ > テキストを列に] > [完了] の順に進むことができます。 選択したセルのすべてに書式が適用されます。

  

Excel で数式が計算されない場合にブックの自動計算を有効にする

数式が計算されない場合には、Excel で自動計算が有効になっているかどうかを確認する必要があります。 手動計算が有効になっていると、数式が計算されません。 次の手順に従って、自動計算を確認します。

1. [ファイル] タブを選択し、[オプション] を選択し、[数式] カテゴリ選択します。

2. [ 計算オプション] セクションの [ブックの計算] で、[ 自動] オプションが選択されていることを確認します。

   

計算の詳細については、「数式の再計算、反復計算、または精度を変更する」を参照してください。

数式に 1 つ以上の循環参照がある

循環参照は、数式が配置されているセルを参照するときに発生します。 修正するには、数式を別のセルに移動するか、数式の構文を循環参照を回避するものに変更します。 ただし、循環参照は、関数の繰り返し (特定の数値条件が満たされるまで繰り返す) を引き起こすため、必要な場合もあります。 このような場合は、[削除を有効にするか、循環参照を許可する必要があります。

循環参照の詳細については、「循環参照を削除または許可する」を参照してください。

関数が等号 (=) で開始されていますか?

エントリの先頭が等号でない場合、そのエントリは数式ではないため、計算されません。これはよくある間違いです。

SUM(A1:A10) のように入力すると、Excel では数式の結果ではなくテキスト文字列 SUM(A1:A10) が示されます。 あるいは、11/2 を入力すると、Excel では 11 を 2 で除算する代わりに、11 月 2 日 や 11/02/2009 などの日付が示されます。

これらの予期しない結果を避けるため、関数は常に等号 (=) で開始します。 たとえば、=SUM(A1:A10) と =11/2 と入力します。

左かっこと右かっこは一致していますか?

数式で関数を使用する場合、関数が正しく機能するためには、各左かっこに右かっこが必要です。 すべてのかっこが数式で一致するペアの一部となっていることを確認します。 たとえば、閉じかっこは 2 つあるのに、開きかっこは 1 つしかないため、数式 =IF(B5<0),"無効",B5*1.05) は機能しません。 正しい数式は、 =IF(B5<0,"無効",B5*1.05) のようになります。

構文に必要な引数がすべてありますか?

Excel 関数には引数 (関数が機能するために提供しなければならない値) があります。 引数を必要としない関数 (PI や TODAY など) は、ごくわずかです。 関数を入力すると表示される数式の構文をチェックして、その関数に必要な引数を入力したか確認します。

たとえば、UPPER 関数には次のように引数として 1 つの文字列またはセル参照のみを指定します。=UPPER("こんにちは") または =UPPER(C2)

関数また、SUM のように引数として数値を必要とする関数もあれば、REPLACE のように、少なくとも 1 つの引数でテキスト値を必要とする関数もあります。 間違ったデータ型を使うと、意図しない結果や #VALUE! エラーを返す場合があります。

特定の関数の構文をすばやく検索する必要がある場合は、「Excel 関数 (機能別)」をご覧ください。

Excel の数式で書式設定されていない数値を処理する

ドル記号は 絶対参照 を示し、コンマは引数の区切り記号であるため、数式にドル記号 ($) または小数点区切り記号 (,) で書式設定された数値を入力しないでください。 数式では $1,000 と入力するのではなく、1000 と入力します。

引数に書式設定された数値を使用すると、予期しない計算結果が得られますが、 #NUM! エラーが表示される場合もあります。 たとえば、-2134 の絶対値を求めるために数式 =ABS(-2,134) を入力すると、Excel では #NUM! エラーが表示されます。 これは、ABS 関数は引数を 1 つだけ受け入れ、-2 と 134 を別個の引数として認識するためです。

参照先のセルは適切なデータ型に属していますか?

セルのデータ型を計算に使用できない場合、数式は期待どおりの結果を返さない可能性があります。 たとえば、テキストとして書式設定されたセルに「=2+3」というシンプルな数式を入力した場合、Excel では入力したデータを計算できません。 セルに =2+3 と表示されるだけです。 これを解決するには、次のようにセルのデータ型を [テキスト] から [ 全般 ] に変更します。

1. セルを選びます。
2. [ホーム] を選択し、矢印を選択して [数値] または [数値形式] グループを展開します (または Ctrl + 1 を押します)。 次に、[全般] を選択します。
3. F2 キーを押してセルを編集モードに切り替えて、Enter キーを押して数式を確定します。

数値データ型を使用しているセルに入力した日付が、日付ではなく数値の日付値として表示される場合があります。 数値を日付として表示するには、[表示形式] ギャラリーで [日付] を選びます。

* 記号を使用せずに乗算をしようとしていませんか?

数式では乗算演算子として x を使いたくなるところですが、Excel の乗算ではアスタリスク (*) しか使用できません。 数式で定数を使用すると、エラー メッセージが表示されます。x をアスタリスク (*) で置き換えることで、数式を修正できます。

しかし、セル参照を使用すると、Excel は #NAME を返しますか?エラー。

数式内の文字列を二重引用符で囲んでいますか?

文字列を含む数式を作成するときには、文字列を二重引用符で囲みます。

たとえば、="今日は " & TEXT(TODAY(),"m 月 dd 日 aaaa") という数式の TEXT 関数と TODAY 関数の結果を、"今日は" というテキストと組み合わせると、「今日は 5 月 30 日月曜日」といった結果が返されます。

この数式では、"今日は" の右側の二重引用符の前にスペースがありますが、これは、"今日は" と "5 月 30 日月曜日" の間にスペースが必要だからです。テキストを二重引用符で囲まないと、数式が #NAME? エラーを返す場合があります。

1 つの数式で 64 を超える関数がありませんか?

数式内には、64 レベルまでの関数を組み合わせることや、ネストすることができます。

たとえば、数式 =IF(SQRT(PI())<2,"Less than two!","More than two!") 3レベルの機能があります。 PI 関数 は SQRT 関数内に入れ子になり、 SQRT 関数は IF 関数内にネストされています。

シート名を単一引用符で囲んでいますか?

別のシート内の値またはセルへの参照を入力し、そのシートの名前に英数字以外 (スペースなど) が含まれる場合は、名前を単一引用符 (') で囲みます。

たとえば、同じブック内の "四半期データ" というシートのセル D3 から値を返すには、='四半期データ'!D3 という数式を使用します。 シートの名前を引用符で囲まないと、数式は #NAME? エラーを示しています。

別のシートの値またはセルを選択して、数式内でそれらを参照することもできます。 その後、Excel によって自動的にシートの名前が引用符で囲まれます。

Excel の数式の外部ブック パスを修正する

別のブック内の値またはセルへの参照を入力する場合は、角かっこ ([]) で囲まれたブックの名前に続けて、値またはセルのあるシートの名前を指定します。

たとえば、Excel で開いている Q2 Operations ブックの売上シートのセル A1 から A8 を参照するには、 =[Q2 Operations.xlsx]Sales! と入力します。A1:A8. 角かっこで囲まないと、数式は #REF! エラーを示しています。

ブックが Excel で開かれていない場合は、ファイルへのフル パスを入力します。

たとえば、=ROWS('C:\My Documents\[第 2 四半期営業活動.xlsx]セールス'!A1:A8) というようになります。

ゼロで数値を除算していませんか?

値がゼロ (0) または値のない別のセルでセルを除算すると、#DIV/0! エラーが返されます。

このエラーを回避するため、それを直接指定して、分母の存在をテストできます。 次を使用できます。

=IF(B1,A1/B1,0)

すなわち、IF(B1 が存在するならば、A1 を B1 で除算し、存在しない場合は 0 を返す) という意味です。

数式が削除されたデータを参照していませんか?

何かを削除する前には、セル、範囲、定義された名前、シート、またはブック内のデータを参照している数式がないかどうかを常に確認してください。 その後、参照先データを削除する前に、数式を数式の結果で置き換えます。

数式を結果で置き換えることができない場合は、エラーと考えられる解決策に関する次の情報を確認してください。

• 削除または他のデータに置き換えられたセルが数式で参照されていて、 #REF! エラーが返される場合は、#REF! エラーのあるセルを選びます。 数式バーで、#REF! を選んで それを削除します。 次 に、数式の範囲をもう一度入力します。
• 定義された名前がなく、その名前を参照する数式が #NAME? エラーを返す場合は、希望する範囲を参照する新しい名前を定義するか、セル範囲を直接参照する (A2:D8 など) ように数式を変更します。
• シートがなく、それを参照する数式が #REF! エラーを返す場合は、 エラー。残念ながら、この問題を解決する方法はありません—削除されたワークシートは復元できません。
• ブックがない場合、それを参照する数式は、数式が更新されるまでそのままです。
  たとえば、数式が =[Book1.xlsx]Sheet1'!A1 で、ファイル Book1.xlsx が削除されているとしても、そのブックで参照されている値は引き続き使用可能です。 ただし、数式を編集し、そのブックを参照している数式を保存しようとすると、[値の更新] ダイアログ ボックスが表示され、ファイル名を入力するように求められます。 [ キャンセル] を選択し、見つからないブックを参照する数式を数式の結果で置き換えて、このデータが失われないようにします。

スプレッドシートで数式に関連付けられたセルをコピーして貼り付けていませんか?

セルの内容をコピーするときに、数式バーに表示されている数式ではなく、値だけを貼り付けたい場合があります。

たとえば、数式の結果の値を別のワークシート上のセルにコピーする場合や、 ワークシートの別のセルに結果の値をコピーした後に、数式で使用した値を削除する場合などです。 これらの操作両方とも、数式で使用した値を含むセルを参照できなくなるため、無効なセル参照エラー (#REF!) がリンク先セルに表示されます。

数式なしで数式の結果の値だけをコピー先のセルに貼り付けることで、このエラーを回避できます。

1. ワークシート上で、コピーする数式の結果の値を含むセルを選択します。

2. [ ホーム ] タブの [クリップボード ] グループで、[画像の コピー 。
   
   キーボード ショートカット: Ctrl + C キーを押します。

3. 貼り付け領域の左上隅のセルを選択します。

   ヒント

   選択範囲を別のワークシートやブックに移動またはコピーするには、別のワークシート タブを選択するか、別のブックに切り替え、貼り付け領域の左上隅のセルを選択します。

4. [ホーム] タブの [クリップボード] グループで、[の貼り付け] を選択し、[値の貼り付け] を選択するか、Alt > E > S > V > Enter キー (Windows の場合) Option > Command > V > V > Enter キーを押します。

ネストした数式がある場合は、1 レベルずつ検証する

複雑な、またはネストされた数式が最終結果をどのように計算するか理解するために、数式を検証できます。

1. 検証する数式を選びます。

2. [ 数式]>[数式を評価する] を選択します。
   

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

4. 数式の下線が表示されている部分が他の数式への参照の場合、[ステップ イン] を選択すると、その数式が [検証] ボックスに表示されます。 [ステップ アウト] を選択すると、前のセルと数式に戻ります。
   同じ参照が数式で二度目に表示されるとき、または数式で他のブックのセルが参照されている場合、[ ステップ イン] ボタンは使用できません。

5. 数式の各レベルが検証されるまで、この操作を繰り返します。
   数式の検証ツールでは、数式が壊れている理由まではわからないことがありますが、壊れている場所はわかります。 他の方法では問題の特定が困難な大きな数式で非常に便利なツールです。

   注

   • IF 関数と CHOOSE 関数の一部は評価されず、#N/A エラーが評価ボックスに表示される場合があります。
   • 空白の参照は、[検証] ボックスではゼロ値 (0) として表示されます。
   • シートが変更されるたびに再計算される関数があります。 そのような関数 (RAND、AREAS、INDEX、OFFSET、CELL、INDIRECT、ROWS、COLUMNS、NOW、TODAY、RANDBETWEEN などの関数を含む) では、[数式の検証] ダイアログ ボックスに、シート上のセルの実際の結果とは異なる結果が表示されることがあります。

補足説明

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

関連項目

Excel の数式の概要

Excel のヘルプとラーニング