エクセルで「循環参照」のセルを探す方法 「循環参照」エラーが表示された! 365・2019・2016・2013・2010・2007対応

一見、何も問題がないエクセルファイルです。

何も問題がないように見えるエクセルファイル

ところが編集を開始すると、下記のようなメッセージが表示されます。

「循環参照」メッセージ
スポンサーリンク

「循環参照」とは

「1つ以上の循環参照が発生しています。循環参照とは、数式が直接的または間接的に自信のセルを参照している状態を指します。これにより、計算が正しく行われない可能性があります。

循環参照を削除または変更するか、数式を別のセルに移動してください。」

というメッセージとともに、数式にエラーを含むシートであることを表しています。

「循環参照」とはセルの計算式の中に、そのセル自体の数値を計算するような状態になっていることが原因です。

ですから、いつまでたっても計算が終わらず、ループしている状態になっているということです。

まずは、「循環参照」を起こしているセルの場所を特定する方法の紹介からです。

スポンサーリンク

「循環参照」のセルを探すには「エラーチェック」から

「循環参照」を探すには、「数式」「エラーチェック」「循環参照」をクリックします。

下図では、「$G$2」セルが循環参照をしていることが分かります。

「循環参照」を探す

G2セルを確認すると、確かに合計欄であるG2セルまで足し算をしていました。

このままでは、B2セルからF2セルを合計し、さらにまだ値の決まっていないセルG2の合計も計算することになりますね。だから「循環参照」となっていたのです。

セル自身の値も「SUM関数」で計算している
スポンサーリンク

「循環参照」の「参照元のトレース」で参照範囲が確認できる

ちなみに、今回はすぐに「循環参照」と分かる程度のミスでしたが、複雑な数式を入力している場合は、「参照元のトレース」が便利です。

「循環参照」をしているセルを選択後、「数式」「参照元のトレース」をクリックすると、青い矢印が伸び、参照元が目視で確認できます。

「数式」「参照元のトレース」

確認後、もう一度「参照元のトレース」をクリックすると青い矢印は消えます。

「参照元のトレース」で参照元が目視できる
スポンサーリンク

「循環参照」のセルが複数ある場合は一つ一つ確認・修正する必要がある

「循環参照」は一度に一つしか表示されません。ですから、複数の「循環参照」が存在する場合は、一つ一つ解決していく必要があります。

「循環参照」は、ステータスバーにも表示されています。下記の場合は、次の「循環参照」は「G12」セルということが分かります。

もう一つの「循環参照」である「G12」セルを確認すると、やはり数式が間違っています。

「循環参照」はステータスバーにも表示される
エクセルのステータスバーのオートカルクで平均・個数・合計を数式を使わずに手軽に計算 最小値や最大値も一目で確認可能! 365・2019・2016・2013・2010・2007対応
エクセルのステータスバーに自動で表示される「オートカルク」ってご存知でしょうか。セルを選択するだけで、選択したセルの「平均」「個数」「合計」などを手軽...

数式を修正後、ステータスバーの「循環参照」表示は消えました。

ステータスバーの「循環参照」は消えた

さらに、「数式」「エラーチェック」をクリックすると、「循環参照」がグレーアウトしていることが確認できます。

「循環参照」はなくなった
スポンサーリンク

計算方法が再計算になっていると「循環参照」の存在もわからない

今回、最初に「循環参照」であることに気が付かなかったのは、計算方法が「手動」となっていて「再計算」表示になっていたためです。

知らない間に計算方法が「再計算」に
エクセルで知らない間に勝手に自動計算から手動計算になる? 恐ろしい「再計算病」 自動計算に戻す方法と原因を解明 計算方法の設定方法も 365・2019・2016・2013・2010・2007対応
ときどきエクセルのファイルを開いていて、マクロを実行しているとうまく動作しないときがあります。マクロにミスはないのですが、原因がわからず調べてみると、身に覚え...

計算方法を「自動」に変更したとたんに、先ほどのメッセージが表示され、「循環参照」の存在に気が付きました。

スポンサーリンク

「循環参照」を無理やり計算させるには

最後に、あまり需要はないかもしれませんが、「循環参照」を無理やり計算させる方法もあります。

下記のようなシートで説明します。「C1」セルには、「循環参照」である数式、「=SUM(A1:C1)」と、C1セルに自身を含む計算式が入力されています。当然計算結果は0になっています。

自身を含む計算式は「循環参照」である

その状態で「オプション」「Excel のオプション」「数式」で、「計算方法の設定」「反復計算を行う」にチェックを入れ、「最大反復回数」を「1」とします。

「反復計算を行う」にチェックを入れる

そして、キーボードの「F9」キー(再計算)を押すと、下記のように計算されます。

一度目は、A1セルの「1」とB2セルの「2」の合計「3」がC1セルに表示されています。

C1セルにはA1セルとB1セルの合計が表示される

そしてもう一度キーボードの「F9」キーを押して再計算させるとC1セルには「1+2+3」の合計「6」が表示されます。

1+2+3の合計が表示される

さらにもう一度、F9キーを押すとどうなるかお分かりでしょうか。

答えは「1+2+6」の「9」が表示されます。もう訳が分からなくなりますよね。

「循環参照」の警告が表示されたときは、素直に「循環参照」のセルを見つけて修正するようにしましょう。

スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言をフォローする
リーダーの独り言

コメント

タイトルとURLをコピーしました