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」と入力します。

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

数式で関数を使用する場合、関数が正しく機能するためには、各左かっこに右かっこが必要です。 すべてのかっこが数式で一致するペアの一部となっていることを確認します。 たとえば、数式 =IF(B5<0)、「無効」、B5*1.05) は機能しません。2 つの閉じかっこがありますが、開始かっこは 1 つだけであるためです。 正しい数式は、 =IF(B5<0,"Not valid",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 を入力した場合、入力したデータは計算できません。 セルに =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,"2 未満!","2 より大きい!") には 3 レベルの関数があります。 PI 関数 は SQRT 関数内に入れ子になり、その関数は IF 関数内に入れ子になります。

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

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

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

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

Excel 数式での外部ブック パスの修正

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

たとえば、Excel で開いている Q2 Operations ブックの Sales シートのセル 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 > キーを押 > [S > V] > [Windows 用に Enter ] キーを押すか、[ Option > Command > V > V > Mac で Enter キー を押します。

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

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

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

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

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

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

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

   注

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

補足説明

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

関連項目

Excel の数式の概要

Excel のヘルプとラーニング