【エクセル】循環参照とは？対象のセルを確認する方法とエラーが発生する原因と対処方法

Excel(エクセル)のファイルを開くと、「１つ以上の循環参照が発生しています。」という警告メッセージが表示されたことは無いでしょうか。

この循環参照は、セルを正しく参照出来ていない数式セルに発生しているエラーです。

「#N/A」「#DIV/0!」などのエラーは表示されないので、この循環参照が警告メッセージで表示されても放置する方もいるのではないでしょうか。

しかし、このエラーを放置していると、数式の結果に重大な問題を起こす場合もあるので、循環参照エラーが発生している場合は、必ずエラーを解消するようにしましょう。

この記事では、「循環参照とは何のか？」「循環参照が発生しているセルの確認方法と解決方法」を紹介します。

エクセルの循環参照とは何なのか？

簡単にいうと、循環参照とは『数式が自らのセルの値を参照しているため、数式がぐるぐる循環し答えを出せない不具合』のことをいいます。

それでは、一番シンプルな循環参照の例を使って、その発生原因を紹介します。

循環参照が発生する原因

循環参照エラーが発生するのは、シンプルに数式の参照セルが間違っていることが原因です。

例えば、下の図のB3セルには「=D3」の数式が入力されています。一方、参照先のD3セルには「=B3」の数式が挿入されてます。

B3セルとD3セルがお互いに数式で参照し合っているので、エクセルが答えを出すことができず、仮の結果として、『0』と表示しています。

この場合、B3セルもD3セルも循環参照なので、2つの循環参照セルが発生しているという状態です。

循環参照が発生した場合、セルに表示される値は、 「0」またはそのセルで最後に計算された値が表示されます。

循環参照となる数式や関数を「Enter」キーで確定させた際、下の警戒メッセージが表示される場合もあります。

しかし、「OK」ボタンや右上の「×」ボタンを押すことで、循環参照エラーを許可することができてしまいます。

そのため、許可したことに気づかず、ファイルを保存して閉じた場合、このExcelファイルを開き直したときに、この警告メッセージが再表示されてしまいます。

循環参照エラーが発生しやすいパターン１(SUM関数)

循環参照が発生しやすいパターンを２つ紹介します。

まず１つ目は、SUM関数のセルの参照範囲がSUM関数のセル自体も含んでしまっている場合です。

例えば、下の表のF列の合計を集計するために、F9セルにSUM関数を挿入します。

ドラッグでF3セルからF8セルを選択するところ、エクセル操作を誤って、F3セルからF10セルまで選択します。つまり、SUM関数を挿入しようとしているF9セルまで選択した状態です。

セル範囲の選択ミスに気付かず、このままSUM関数を確定すると、SUM関数の結果は「0」となり循環参照のセルが発生してしまいます。

循環参照の警告メッセージが表示されないこともあるので気付かないことも多いよ。

私

循環参照エラーが発生しやすいパターン２(絶対参照の数式)

２つ目のパターンは、絶対参照を使った数式セルを別のセルに移動したり並べ替えする場合です。

例えば、下の表のD9セルに合計数量を集計するために、SUM関数「=SUM($D$3:$D$8)」を挿入します。

次に、この表のD列をフィルターを使って降順で並べ替えすると、合計行は表の一番上に移動しています。

SUM関数のセルは表の並べ替えによって移動しましたが、絶対参照を設定しているため、数式は「=SUM($D$3:$D$8)」から変化しません。

そのため、SUM関数は、SUM関数自体のセルを選択することとなり、循環参照が発生してしまいます。

このように絶対参照を利用した数式や関数を別のセルにコピペしたり、移動したりすると、循環参照を発生する場合があるので注意が必要です。

循環参照が発生しているセルを確認し対処する方法

循環参照が発生しているセルを確認する方法を紹介します。

もし、「１つ以上の循環参照が発生しています。」という警告メッセージが表示されたら、以下の作業を行って、対象のセルの数式を修正してください。

まず、「数式」タブの中から「エラーチェック」ボタンの下矢印ボタンを押して、「循環参照」をクリックします。そうすると、循環参照のセルがある場合は、対象のセル番号が表示されるので、クリックしてください。

そうすると、循環参照セルが発生しているF10セルに瞬時に移動します。数式を確認すると、「=SUM(F3:F10)」となっていて、SUM関数がSUM関数自体を参照してしまっていることが分かります。

上記であれば、「=SUM(F3:F10)」を「=SUM(F3:F9)」に修正すれば、循環参照の問題が解消されます。

「循環参照」に気づくためのコツは？

上で紹介したとおり、「循環参照」はSUM関数のセル範囲の選択ミスによって起こることが多いです。

循環参照が発生したセルは値が「0」、もしくは数式の値が更新されず、前回の計算結果が表示されています。

さらに、循環参照セルを参照しているセルは、その値も異常値となるため、複雑な循環参照は気付かないことが多くなります。

そのため、表の数式エラーに気づくためのコツとして、縦の合計と横の値の合計が一致しているかチェックするようにしてください。そうすると、循環参照に限らず、数式が正しく使用されているかのチェックに役立ちます。

(補足)エクセルの数式の参照方法を理解しよう

循環参照を発生させないためにも、数式や関数を絶対参照に設定するか、相対参照に設定するか正しく判断する必要があります。

例えば、数式を相対参照の「=B5*C2」とすべきか、それとも絶対参照の「=$B$5*$C$2」とすべきかは、その後の数式の用途によって変わります。

下の記事で、絶対参照と相対参照、またそれらを組み合わせた複合参照について、詳しく紹介していますので、参考にしてみてください。