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

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

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

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

残念ながら、これは Excel が実行しようとしていることを理解できないので、最初から始める必要がある場合があります。

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

編集モードで壊れた数式のセルに戻り、問題のある箇所が強調表示されます。 そこから何を行うのかまだ分からなかった場合は、もう一度Escキーを押すか、数式バーの [キャンセル] ボタンを選択すると、編集モードが解除されます。

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

前に進む場合は、次のチェックリストにトラブルシューティング手順が表示されます。問題の原因を特定するのに役立ちます。

注: アプリを使用している場合Web 用 Office同じエラーが表示されないか、解決策が適用されない可能性があります。

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 で割る代わりに、2009 年 11 月 2 日や 2009/11/02 などの日付が表示されます。

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

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

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

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

注: 数式を入力すると、数式の下に関数の引数が表示されます。

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

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

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

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

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

注: 書式が設定されていない数値 (定数) を使用して数式を入力した後で、数式の結果を小数点記号と通貨記号で書式設定できます。 定数は後で更新する必要がある場合は見つけにくいため、数式に定数を入力するのは一般的に良い方法ではありません。 定数は、開いた状態で簡単に参照できるセルに入力する方が良いです。

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

  1. セルを選びます。

  2. [ホーム]を選択し、矢印を選択して [数値] または [表示形式] グループを展開します (または、Ctrl キーを押しながら 1 キーを押します)。 次に、[全般] を選択します

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

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

数式で乗算演算子として x を使用するのはかなり一般的ですが、乗算にはアスタリスク (*) のみを受け入れ可能です。 数式で定数を使用すると、Excel にエラー メッセージが表示され 、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関数内にネストされ、さらに 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 > E > S > V > Enter for Windows、または Mac の Option > コマンド > V > V > Enterキーを押します。

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

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

  2. 数式を選択し > を評価します

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

  3. [検証] を 選び、下線付き参照の値を調べる。 検証結果は斜体で表示されます。

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

  4. 数式の下線が引いた部分が別の数式への参照である場合は、[ステップ イン] を選び、[評価] ボックスに他の数式を表示します。 [ステップ アウト] を 選び、前のセルと数式に戻る。

    数式 に参照 が 2 回目に表示された場合、または数式が別のブックのセルを参照している場合、[ステップ イン] ボタンは使用できません。

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

    数式の評価ツールでは、数式が壊れている理由は必ずしも分かりませんが、数式が壊れている場所を示すのに役立ちます。 他の方法では問題の特定が困難な大きな数式で非常に便利なツールです。

    注: 

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

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

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

補足説明

Excel 技術コミュニテでは、いつでも専門家に質問できます。また、Answers コミュニティでは、サポートを受けられます。

関連項目

Excel の数式の概要

数式のエラーを検出する

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

Excel 関数 (機能別)

ヘルプを表示

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

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

×