一見、何も問題がないエクセルファイルです。
ところが編集を開始すると、下記のようなメッセージが表示されます。
「循環参照」とは
「1つ以上の循環参照が発生しています。循環参照とは、数式が直接的または間接的に自信のセルを参照している状態を指します。これにより、計算が正しく行われない可能性があります。
循環参照を削除または変更するか、数式を別のセルに移動してください。」
というメッセージとともに、数式にエラーを含むシートであることを表しています。
「循環参照」とはセルの計算式の中に、そのセル自体の数値を計算するような状態になっていることが原因です。
ですから、いつまでたっても計算が終わらず、ループしている状態になっているということです。
それでは「循環参照」エラーが表示されたときの解決法のご紹介です。
まずは、「循環参照」を起こしているセルの場所を特定する方法の紹介からです。
「循環参照」しているセルを探すには「エラーチェック」から
「循環参照」を探すには、「数式」「エラーチェック」「循環参照」をクリックします。
下図では、「$G$2」セルが循環参照をしていることが分かります。
G2セルを確認すると、確かに合計欄であるG2セルまで足し算をしていました。
このままでは、B2セルからF2セルを合計し、さらにまだ値の決まっていないセルG2の合計も計算することになりますね。だから「循環参照」というエラーになっていたのです。
複雑な数式の場合は「循環参照」の「参照元のトレース」で参照範囲が確認できる
ちなみに、今回はすぐに「循環参照」と分かる程度のミスでしたが、複雑な数式を入力している場合には、「参照元のトレース」が便利です。
「循環参照」をしているセルを選択後、「数式」「参照元のトレース」をクリックすると、青い矢印が伸び、参照元が目視で確認できます。
下図のように、明らかに参照元が間違っていることがすぐにわかります。
参照元の確認後、もう一度「参照元のトレース」をクリックすると青い矢印は消えます。
「循環参照」のセルが複数ある場合は一つ一つ確認・修正する必要がある
「循環参照」は一度に一つしか表示されません。
ですから、複数の「循環参照」が存在する場合は、一つ一つ解決していく必要があります。
「循環参照」は、エクセル画面下のステータスバーにも表示されます。
下図の場合は、次の「循環参照」は「G12」セルということが分かります。
もう一つの「循環参照」である「G12」セルを確認すると、やはり数式（参照範囲）が間違っていますね。
数式を修正すると、ステータスバーの「循環参照」の表示は消えました。
数式を修正後、「数式」「エラーチェック」をクリックすると、「循環参照」がグレーアウトしていることが確認できます。
これで「循環参照」はなくなったことが確認できます。
計算方法が「手動」になっていると「循環参照」の存在もわからない
今回、最初に「循環参照」であることに気が付かなかったのは、計算方法が「手動」となっていて「再計算」がステータスバーに表示されていたためです。
計算方法を「自動」に変更したとたんに、先ほどのメッセージが表示され、「循環参照」の存在に気がつきました。
「循環参照」を無理やり計算させるには
最後に、あまり需要はないかもしれませんが、「循環参照」を無理やり計算させる方法もあります。
「循環参照」エラーをしながらも計算させる方法の紹介です。
下記のようなシンプルな構成のシートで説明します。
「C1」セルには、「循環参照」である数式、「=SUM(A1:C1)」と、C1セルに自身を含む計算式が入力されています。当然計算結果は0になっています。
その状態で「オプション」「Excel のオプション」「数式」で、「計算方法の設定」「反復計算を行う」にチェックを入れ、「最大反復回数」を「1」とします。
そして、キーボードの「F9」キー（再計算）を押すと、下記のように計算されます。
一度目は、A1セルの「1」とB2セルの「2」の合計「3」がC1セルに表示されています。
そしてもう一度キーボードの「F9」キーを押して再計算させるとC1セルには「1+2+3」の合計「6」が表示されます。
さらにもう一度、F9キーを押すとどうなるかお分かりでしょうか。
答えは「1+2+6」の「9」が表示されます。もう訳が分からなくなりますよね。
「循環参照」の警告が表示されたときは、素直に「循環参照」のセルを見つけて修正するようにしましょう。
コメント