スキップしてメイン コンテンツへ
Office
壊れた数式のエラーを回避する方法

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

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

Excel の [数式に問題があります] ダイアログ ボックスの画像

これは Excel が数式を理解できないことを意味するので、最初からやり直すことが必要な場合があります。

[OK] をクリックするか Esc キーを押してエラー メッセージを閉じて開始します。

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

数式バーの [キャンセル] ボタンの画像

前に進む場合は、次のチェックリストでトラブルシューティングの手順を実行して、問題が発生している可能性があるものを見つけてください。

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

注: ### は数式に関連するエラーではありません。 これは単に、列の幅が狭くてセルの内容がすべて表示できないことを意味します。 列をドラッグして幅を広げるか、または [ホーム]、[書式設定]、[列幅に自動調整] の順に選択してください。

[ホーム] > [書式設定] > [列の幅の​​自動調整] の画像

表示されるシャープエラーに対応する次のトピックを参照してください。

他のスプレッドシートの値を参照する数式が含まれているスプレッドシートを開くたびに、参照を更新するか、そのまま残しておくかを確認するメッセージが表示されます。

Excel の参照が壊れたダイアログ ボックス

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

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

[リンクの自動更新前にメッセージを表示する] オプションの画像

重要: 数式内の壊れたリンクに初めて対処する場合は、壊れたリンクを解決するリフレッシャーが必要です。または参照を更新するかどうかがわからない場合は、「外部参照 (リンク) の更新方法を制御する」を参照してください。

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

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

    ヒント: キーボード ショートカット (Ctrl + ` キー (Tab キーの上にあるキー) を押す) を使用することもできます。 この操作を行うと、数式を表示するために列の幅が自動的に広がりますが、標準ビューに戻ると、列の幅がサイズ変更されます。

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

  • テキストとして書式設定されている列内のセルの範囲が大きい場合は、その範囲を選択し、選択した数値の書式を適用して、[データ]、[区切り位置]、[終了] の順に移動します。 選択したセルのすべてに書式が適用されます。

    [データ] > [区切り位置] ダイアログの画像

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

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

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

    [自動計算] および [手動計算] オプションの画像

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

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

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

エントリが等号 (=) で開始されていない場合、それは数式ではないため、計算されません。これはよくある間違いです。

SUM(A1:A10) のように入力すると、Excel では数式の結果ではなくテキスト文字列 SUM(A1:A10) が示されます。 また、「 11/2」と入力した場合は、11を2で割る代わりに、11月2日または11/02/2009 のような日付が表示されます。

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

数式で関数を使用する場合は、左かっこのそれぞれに関数が適切に動作するために右かっこが必要であるため、すべてのかっこが一致するペアの一部であることを確認してください。 たとえば、数式= IF (b5<0)、"有効ではありません"、B5 * 1.05)は、2つの右かっこしかありません。 正しい数式は、=IF(B5<0,"無効",B5*1.05) のようになります。

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

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

注: 入力すると、数式の下に関数参照のフリー ツールバーに関数の引数が一覧表示されます。

関数参照ツールバーのスクリーン ショット

また、SUM のように引数として数値を必要とする関数もあれば、REPLACE のように、少なくとも 1 つの引数でテキスト値を必要とする関数もあります。 誤ったデータ型を使用すると、関数によって予期しない結果が返されるか、 #VALUE!エラーが表示されることがあります。

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

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

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

注: 書式設定されていない数値 (定数) を使用して数式を入力した後、小数点区切り記号と通貨記号を使用して数式の結果を書式設定できます。 通常、数式に定数を入力することはお勧めできません。定数は、後で更新する必要があるかどうかを確認するのが難しいため、誤って入力される可能性が高いためです。 定数は、オープンで簡単に参照できるセルに配置する方が適切です。

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

  1. セルを選びます。

  2. [ホーム]、[表示形式] の横にある矢印の順にクリックし (または Ctrl + 1 を押し)、[標準] をクリックします。

  3. F2 キーを押してセルを編集モードに切り替えて、Enter キーを押して数式を確定します。

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

数式では乗算演算子としてxを使うのが一般的ですが、乗算ではアスタリスク (*) のみを受け入れることができます。 数式で定数を使用している場合は、エラーメッセージが表示され、 xをアスタリスク (*) に置き換えることで、数式を修正することができます。

乗算の x を * と置換することを確認するメッセージ ボックス

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

セル参照で乗算に * ではなく x を使用した場合の #NAME? エラー

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

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

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

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

たとえば、数式 =IF(SQRT(PI())<2,"2 未満!","2 以上!") には 3 つのレベルの関数が含まれています。PI 関数SQRT 関数内にネストされ、SQRT 関数は IF 関数内にネストされています。

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

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

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

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

たとえば、Excel で開かれている "第 2 四半期営業活動" ブックの "セールス" シートの A1 ~ A8 のセルを参照するには、「=[第 2 四半期営業活動.xlsx]セールス!A1:A8」と入力します。 角かっこで囲まないと、数式は #REF! エラー.

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

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

注: フル パスにスペース文字が含まれる場合は、パスを単一引用符で囲む (パスの先頭と、シート名と感嘆符の間) 必要があります。

ヒント: 他のブックへのパスを取得する最も簡単な方法は、他のブックを開き、元のブックから "=" を入力し、Alt キーを押しながら Tab キーを押して、他のブックに移動し、シート上の任意のセルを選択することです。 その後、元のブックを閉じます。 数式は自動的に更新され、ファイルの完全なパスとシート名が、必要な構文とともに表示されます。 パスをコピーして必要な場所に貼り付けて使用することもできます。

値がゼロ (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. [ホーム] タブの [クリップボード] で [コピー] ボタンの画像 をクリックします。

    Excel のリボン イメージ

    キーボード ショートカット: Ctrl + C キーを押します。

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

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

  4. [ホーム] タブの [クリップボード] グループで、[貼り付け] をクリックし、[値の貼り付け] をクリック>するか、Alt キーを押しながら > S キーを押し> > enter キーを押します。 ボタンの画像

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

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

  2. [数式]、[数式の検証] の順にクリックします。

    [数式] タブの [ワークシート分析] グループ

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

    [数式の検証] ダイアログ ボックス

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

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

  5. 数式の各レベルが検証されるまで、この操作を繰り返します。

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

    注: 

    • IF 関数と CHOOSE 関数の一部は検証できません。#N/A エラーが [検証] ボックスに表示されることがあります。

    • 空白の参照は、[検証] ボックスではゼロ値 (0) として表示されます。

    • シートが変更されるたびに再計算される関数があります。 そのような関数 (RANDAREASINDEXOFFSETCELLINDIRECTROWSCOLUMNSNOWTODAYRANDBETWEEN などの関数を含む) では、[数式の検証] ダイアログ ボックスに、シート上のセルの実際の結果とは異なる結果が表示されることがあります。

補足説明

Excel Tech Community では、いつでも専門家に質問できます。Microsoft コミュニティでは、サポートを受けられます。また、Excel User Voice では、新機能についての提案や改善案を送信することができます。

関連項目

Excel の数式の概要

数式のエラーを検出する

Excel 関数 (アルファベット順)

Excel 関数 (機能別)

注:  このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。 私たちの目的は、このコンテンツがお客様の役に立つようにすることです。 情報が役に立ったかどうか、ご意見をお寄せください。 参考までに、こちらから英語の記事をお読みいただけます。

Office のスキルを磨く
トレーニングの探索
新機能を最初に入手
Office Insider に参加する

この情報は役に立ちましたか?

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×