Excelの循環参照とは?原因・確認方法・対処法を徹底解説

Excelで循環参照(Circular Reference)が発生するのは、数式が入力されたセルが、直接または間接的に自分自身を参照している場合です。ワークシートに循環参照が存在すると、Excelは自動計算を正しく実行できません。循環参照をそのまま放置すると、Excelは前回の反復計算時の値を使って各セルの計算を繰り返し続けるため、無限ループが発生し、スプレッドシートの動作が大幅に遅くなってしまいます。

この記事では、Excelにおける循環参照の仕組みや種類、そして具体的な確認方法と対処法について詳しく解説します。

循環参照の種類

数式を入力しているセル自体が、その数式内の参照範囲に含まれている場合、それが循環参照となります。循環参照には主に「直接循環参照」と「間接循環参照」の2種類があります。数式を入力中のセルそのものを数式の参照範囲に含めた場合は「直接循環参照」、あるセルが別のセルを参照し、その別のセルがさらに元のセルを参照するような連鎖構造になっている場合は「間接循環参照」と呼ばれます。

1. 直接循環参照

以下のデータセットでは、セルD12にセルD5からD11までの合計売上を求める数式を入力します。

ここで誤って、合計を入力するはずのセルD12自身も合計対象のセルに含めてしまうと、直接循環参照が発生します。

Enterキーを押すと、循環参照に関する警告ダイアログが表示されます。

OKをクリックすると、数式の結果として正しい合計値ではなく「0」が表示されていることがわかります。これは直接循環参照が原因です。

2. 間接循環参照

次の例を見てみましょう。セルD6の数式はセルC4を参照しており、セルB8の数式はセルD6を参照しています。ここで、セルC4の数式がセルB8を参照すると、循環参照が発生します。この場合、セルC4は自分自身を直接参照しているわけではありませんが、「参照元のトレース」機能で辿っていくと、どこかの時点でC4が参照元として使われていることが確認できます。

Enterキーを押すと、セルC4には「0」が表示され、セル同士の関連性が青い矢印で示されます。

Excelで循環参照を見つけて修正する方法

計算結果を正しく得るためには、循環参照を特定して修正することが重要です。ワークシートから循環参照を見つける方法はいくつかありますので、順番に紹介していきます。

1. 「数式の表示」オプションで循環参照を探す

数式を表示させることで、データシート上の循環参照を手動で見つけることができます。

➤ 数式 > ワークシート分析 > 数式の表示を選択します。

これにより、ワークシート内のすべての数式が表示されます。数式を目視で確認しながら評価することで、循環参照となっている箇所を簡単に特定できます。

2. エラーチェック機能を使う方法

エラーチェック機能を使っても、ワークシートの循環参照を特定できます。

➤ 数式 > ワークシート分析 > エラーチェック > 循環参照を選択します。

ここには、循環参照が含まれるすべてのセルが一覧表示されます。

あとは、数式内の参照範囲を修正するなど、必要な対応を行えば計算を正常な状態に戻せます。

関連記事: Excelで循環参照エラーを修正する方法(詳細ガイド)

3. ステータスバーから確認する方法

循環参照を見つける最も簡単な方法は、ステータスバーを確認することです。ワークシートに循環参照が存在する場合、その内容がステータスバーに表示されます。

循環参照を含むセルが特定できれば、あとは適切な対処を行うだけで問題を解消できます。

循環参照の関係をトレースする

循環参照に関わる数式とセルの関係を追跡できるトレース機能には、2つの方法があります。「参照元のトレース」と「参照先のトレース」です。これらのトレースツールは、循環参照に関係するセル同士をつなぐ線を矢印で表示することで、ユーザーが循環参照の解決を進めるのに役立ちます。

1. 参照元のトレース

「参照元のトレース」機能は、現在選択中のセルが依存しているセル(つまり、その数式が必要とするデータを供給しているセル)を追跡します。この機能を使うと、どのセルがアクティブなセルに影響を与えているのかを示す線が描画されます。

参照元をトレースするには、まず以下の手順を実行します。

➤ 循環参照が含まれるセルを選択します。

➤ 数式 > ワークシート分析 > 参照元のトレースを選択するか、Alt + T + U + Tキーを押します。

すると、参照元セルからアクティブセルへ向かう青い矢印が表示されます。

2. 参照先のトレース

「参照先のトレース」機能は、アクティブなセルに依存しているセル(つまり、結果を出すためにアクティブセルのデータを必要としているセル)を追跡します。この機能を使うと、アクティブセルに依存するセルへ向かう線が描画されます。

参照先をトレースするには、まず以下の手順を実行します。

➤ 循環参照が含まれるセルを選択します。

➤ 数式 > ワークシート分析 > 参照先のトレースを選択するか、Alt + T + U + Dキーを押します。

すると、アクティブセルから依存先セルへ向かう青い矢印が表示されます。

反復計算を有効にして循環参照を許可する方法

反復計算は、Excelの興味深い機能の一つです。反復計算が無効になっている場合、Excelは循環参照の警告を表示し、実際の計算結果の代わりに「0」を返します。しかし、意図的に循環参照を活用したい場合は、反復計算を有効にすることで計算を実行できるようになります。また、反復回数の上限を設定することも可能なので、無限ループを防ぐこともできます。

反復計算を有効にするには、以下の手順に従います。

➤ ファイルタブを開き、オプションを選択します。

Excelのオプションウィンドウが開きます。次に、

➤ 数式タブに移動し、反復計算を有効にするチェックボックスにチェックを入れて、OKをクリックします。

これで反復計算が有効になり、循環参照を使用できるようになります。

ワークシートに戻ると、今度は数式セルに「0」ではなく実際の値が表示され、循環参照エラーも発生していません。これは、Excelが循環参照を許可している状態を意味します。

関連記事: Excelで循環参照を許可する方法とその活用術

反復計算は無効のままにしておくべき理由

特別な理由がない限り、Excelの反復計算を有効にすることは推奨されません。反復計算が有効になっていると、Excelは大量のリソースを消費し、単純な計算にも通常よりはるかに多くの時間がかかるようになります。さらに、反復計算がオンの状態では、循環参照による計算ミスが発生しても気づかれないまま残ってしまう危険性があります。そのため、必要がない限りは反復計算を無効のまま維持しておくことをおすすめします。

まとめ

この記事を通じて、Excelにおける循環参照の仕組みと、その確認・対処方法についてご理解いただけたと思います。もし不明な点がありましたら、ぜひコメント欄でお知らせください。

関連記事

• Excelで循環参照を削除する方法(2つの方法)
• Excelで循環参照を見つける方法(簡単な2つのテクニック)