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

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

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

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

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

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

• エラー値 #NUM! を修正する

• エラー値 #VALUE! を修正する

• エラー値 #N/A を修正する

• エラー値 #DIV/0! を修正する

• エラー値 #REF! を修正する

• エラー値 #NAME? を修正する

• エラー値 #NULL! を修正する

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

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

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

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

• スプレッドシートで数式を表示するように 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,"無効",B5*1.05) のようになります。

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

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

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

特定の関数の構文をすばやく検索する必要がある場合は、「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? エラー.

数式内には、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) というようになります。

値がゼロ (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. [ホーム] タブの [クリップボード] グループで、[貼り付け] を選択し、[値の貼り付け] をクリックします。または、Windows の場合は、Alt キー、E キー、S キー、V キー、Enter キーの順に押し、Mac の場合は、Option キー、Command キー、V キー、V キー、Enter キーの順に押します。

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

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

2. [数式]、[数式の検証] の順に選択します。

   

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

   

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

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

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

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

   注: 

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

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

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