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

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

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

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

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

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

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

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

どうすればいいかわからない場合、または必要なヘルプの種類がわからない場合は、Excel コミュニティ フォーラムで同様の質問を検索するか、自分で質問を投稿します。

Excel コミュニティ フォーラムへのリンク

作業を続ける場合、次のチェックリストは、数式の間違いを確認するトラブルシューティングの手順を提供します。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

循環参照の詳細については、「循環参照を見つけて修正する」を参照してください。

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

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),"Not valid",B5*1.05) は、右かっこが 2 つあり、左かっこが 1 つしかないため機能しません。正しい数式は、=IF(B5<0,"Not valid",B5*1.05) のようになります。

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

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

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

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

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

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

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

引数に書式が設定された数値を使用すると、意図しない結果が得られますが、#NUM! エラーを返す場合もあります。たとえば、-2134 の絶対値を取得するために =ABS(-2,134) という数式を入力した場合、#NUM! エラーが表示されます。これは ABS 関数が引数を 1 つしか受け付けないためです。

注: 書式が設定されていない数値 (定数) を使って数式を入力したであれば、桁区切り記号や通貨記号を使って、数式の結果を書式設定することができます。一般的には、数式に定数を入れることはお勧めできません。定数は後から更新が必要になった場合に見つけにくく、誤って入力されやすいからです。定数は、表面化され参照しやすいセルに配置することをお勧めします。

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

  1. セルを選びます。

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

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

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

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

乗算の x を * と置換することを確認するメッセージ ボックス
乗算の定数で * の代わりに x を使用した場合のエラー メッセージ

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

セル参照で乗算に * ではなく x を使用した場合の #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. [ホーム] タブの [クリップボード] グループで、[貼り付け] ボタンの画像 をクリックし、[値の貼り付け] をクリックします。または、Windows の場合は、Alt キー、E キー、S キー、V キー、Enter キーの順に押し、Mac の場合は、Option キー、Command キー、V キー、V キー、Enter キーの順に押します。

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

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

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

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

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

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

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

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

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

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

    注記: 

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

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

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

補足説明

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

関連項目

Excel の数式の概要

Excel 2016 で数式のエラーを検出する

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

Excel 関数 (機能別)

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

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

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

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

×