ユーザーがExcelで遭遇する最も一般的なエラー警告の1つは、「循環参照」です。何千人ものユーザーが同じ問題を抱えており、数式が直接または間接的に独自のセルを参照している場合に発生し、計算の無限のループを引き起こします。
たとえば、セルB1とB2に2つの値があります。式= B1 + B2をB2に入力すると、循環参照が作成されます。 B2の数式は、計算するたびにB2値が変更されるため、繰り返し再計算します。
ほとんどの循環参照は意図しない間違いです。 Excelはこれらについて警告します。ただし、反復計算を行うために使用される循環参照も意図されています。ワークシート内の意図しない循環参照により、数式が正しく計算されない場合があります。
したがって、この記事では、循環参照について知っておく必要のあるすべてのことと、Excelで循環参照を検索、修正、削除、および使用する方法について説明します。
Excelで循環参照を検索して処理する方法
Excelを使用しているときに、数式が存在するセルを含む数式を入力すると、循環参照エラーが発生することがあります。基本的に、それはあなたの数式がそれ自身を計算しようとしているときに起こります。
たとえば、セルA1：A4に数値の列があり、セルA5でSUM関数（= SUM（A1：A5））を使用しているとします。セルA5は、それ自体のセルを直接参照していますが、これは誤りです。したがって、次の循環参照警告が表示されます。
上記の警告メッセージが表示されたら、[ヘルプ]ボタンをクリックしてエラーの詳細を確認するか、[OK]または[X]ボタンをクリックしてエラーメッセージウィンドウを閉じ、結果として「0」を取得します。
循環参照ループにより、計算がクラッシュしたり、ワークシートのパフォーマンスが低下したりする場合があります。循環参照は、他の多くの問題につながる可能性もありますが、すぐには明らかになりません。したがって、これらは避けるのが最善です。
直接および間接循環参照
循環参照は、直接循環参照と間接循環参照の2つのタイプに分類できます。
直接参照
直接循環参照は非常に単純です。数式が自身のセルを直接参照している場合、直接循環参照の警告メッセージがポップアップ表示されます。
次の例では、セルA2の数式は、それ自体のセル（A2）を直接参照しています。
警告メッセージが表示されたら、[OK]をクリックできますが、結果は「0」になります。
間接循環参照
Excelの間接循環参照は、数式の値がそれ自体のセルを参照しているが、直接参照していない場合に発生します。言い換えると、循環参照は、2つのセルが相互に参照することによって形成できます。
この簡単な例で説明しましょう。
これで、値は値20のA1から始まります。
次に、セルC3はセルA1を参照します。
次に、セルA5はセルC3を参照します。
次に、セルA1の値20を次の式に置き換えます。他のすべてのセルはセルA1に依存しています。 A1で他の以前の数式セルの参照を使用すると、循環参照警告が発生します。なぜなら、A1の数式はC3を参照するセルA5を参照し、セルC3はA1を参照しているため、循環参照です。
[OK]をクリックすると、セルA1の値が0になり、Excelは、以下に示すように、トレースの前例とトレースの依存関係を示すリンクされた線を作成します。この機能を使用すると、循環参照を簡単に検索して修正/削除できます。
Excelで循環参照を有効/無効にする方法
デフォルトでは、Excelでは反復計算がオフ（無効）になっています。反復計算は、特定の条件を満たすまで反復計算です。無効にすると、Excelは循環参照メッセージを表示し、結果として0を返します。
ただし、ループを計算するために循環参照が必要になる場合があります。循環参照を使用するには、Excelで反復計算を有効にする必要があります。これにより、計算を実行できるようになります。それでは、反復計算を有効または無効にする方法を説明しましょう。
Excel 2010、Excel 2013、Excel 2016、Excel 2019、およびMicrosoft 365では、Excelの左上隅にある[ファイル]タブに移動し、左ペインの[オプション]をクリックします。
[Excelのオプション]ウィンドウで、[数式]タブに移動し、[計算オプション]セクションの[反復計算を有効にする]チェックボックスをオンにします。次に、[OK]をクリックして変更を保存します。
これにより、反復計算が可能になり、循環参照が可能になります。
以前のバージョンのExcelでこれを実現するには、次の手順に従います。
- Excel 2007では、[Office]ボタン> [Excelオプション]> [数式]> [反復領域]をクリックします。
- Excel 2003以前のバージョンでは、[メニュー]> [ツール]> [オプション]> [計算]タブに移動する必要があります。
最大反復回数と最大変更パラメーター
反復計算を有効にすると、以下のスクリーンショットに示すように、[反復計算を有効にする]セクションで使用可能な2つのオプションを指定することにより、反復計算を制御できます。
- 最大反復回数 –この数値は、最終結果が得られるまでに数式を再計算する回数を指定します。デフォルト値は100です。これを「50」に変更すると、Excelは最終結果を返す前に計算を50回繰り返します。反復回数が多いほど、計算にかかるリソースと時間が増えることに注意してください。
- 最大の変化 –計算結果間の最大変化を決定します。この値は、結果の精度を決定します。数値が小さいほど、結果はより正確になり、ワークシートの計算に時間がかかります。
反復計算オプションが有効になっている場合、ワークシートに循環参照がある場合でも警告は表示されません。どうしても必要な場合にのみ、インタラクティブな計算を有効にしてください。
Excelで循環参照を検索
大規模なデータセットがあり、循環参照の警告が表示された場合でも、エラーを修正するには、エラーが発生した場所（セル内）を確認する必要があります。 Excelで循環参照を見つけるには、次の手順に従います。
エラーチェックツールの使用
まず、循環参照が発生したワークシートを開きます。 [数式]タブに移動し、[エラーチェック]ツールの横にある矢印をクリックします。次に、[循環参照]オプションにカーソルを合わせると、Excelに、以下に示すように循環参照に関係するすべてのセルのリストが表示されます。
リストで必要なセルアドレスをクリックすると、問題を解決するためにそのセルアドレスに移動します。
ステータスバーの使用
循環参照はステータスバーにもあります。 Excelのステータスバーに、「循環参照：B6」などの循環参照を含む最新のセルアドレスが表示されます（下のスクリーンショットを参照）。
循環参照を処理するときに知っておくべき特定のことがあります。
- 反復計算オプションが有効になっている場合、ステータスバーには循環参照セルのアドレスが表示されないため、循環参照のブックを確認する前に、ステータスバーを無効にする必要があります。
- アクティブシートに循環参照が見つからない場合、ステータスバーにはセルアドレスのない「循環参照」のみが表示されます。
- 循環参照プロンプトは1回だけ表示され、[OK]をクリックすると、次回はプロンプトが表示されなくなります。
- ブックに循環参照がある場合は、循環参照を解決するまで、または反復計算をオンにするまで、ブックを開くたびにプロンプトが表示されます。
Excelで循環参照を削除する
循環参照の検索は簡単ですが、修正はそれほど簡単ではありません。残念ながら、Excelには、すべての循環参照を一度に削除できるオプションはありません。
循環参照を修正するには、各循環参照を個別に検索して変更するか、循環式を完全に削除するか、別の循環式に置き換える必要があります。
単純な数式では、数式がそれ自体を参照しないように、数式のパラメーターを再調整するだけでよい場合があります。たとえば、B6の式を= SUM（B1：B5）* A5に変更します（B6をB5に変更します）。
計算結果は「756」として返されます。
Excelの循環参照を見つけるのが難しい場合は、先例のトレース機能と依存関係のトレース機能を使用して、ソースまでさかのぼって1つずつ解決できます。矢印は、アクティブセルの影響を受けるセルを示しています。
数式とセルの関係を表示することで循環参照を削除するのに役立つ2つのトレース方法があります。
トレース方法にアクセスするには、[式]タブに移動し、[式の監査]グループの[前例のトレース]または[依存のトレース]をクリックします。
先例をたどる
このオプションを選択すると、アクティブセルの値に影響を与えるセルが追跡されます。現在のセルに影響を与えるセルを示す青い線を描画します。トレースの前例を使用するためのショートカットキーは次のとおりです。 Alt + T U T.
以下の例では、青い矢印はB6値に影響を与えるセルがB1：B6とA5であることを示しています。以下に示すように、セルB6も数式の一部であるため、循環参照になり、数式は結果として「0」を返します。
これは、SUMの引数= SUM（B1：B5）でB6をB5に置き換えることで簡単に修正できます。
トレース依存
トレース依存機能は、選択したセルに依存しているセルをトレースします。この機能は、選択したセルの影響を受けるセルを示す青い線を描画します。つまり、アクティブセルを参照する数式がどのセルに含まれているかが表示されます。扶養家族を使用するためのショートカットキーは Alt + T U D.
次の例では、セルD3はB4の影響を受けます。結果を生成するための値はB4に依存しています。したがって、トレース依存はB4からD3に青い線を引き、D3がB4に依存していることを示します。
Excelで循環参照を意図的に使用する
循環参照を意図的に使用することはお勧めしませんが、必要な出力を取得するために循環参照が必要になる場合がまれにあります。
例を使って説明しましょう。
まず、Excelブックで「反復計算」を有効にします。反復計算を有効にすると、循環参照の使用を開始できます。
あなたが家を購入していて、その家の総費用の2％の手数料をエージェントに与えたいとしましょう。総コストはセルB6で計算され、手数料率（エージェント料金）はB4で計算されます。手数料は総費用から計算され、総費用には手数料が含まれます。セルB4とB6は相互に依存しているため、循環参照を作成します。
セルB6に合計コストを計算する数式を入力します。
= SUM（B1：B4）
総費用には代理店手数料が含まれているため、上記の式にはB4を含めています。
2％のエージェント料金を計算するには、次の式をB4に挿入します。
= B6 * 2％ここで、セルB4の数式はB6の値に依存して合計料金の2％を計算し、B6の数式はB4に依存して合計コスト（エージェント料金を含む）を計算するため、循環参照になります。
反復計算が有効になっている場合、Excelは結果に警告または0を表示しません。代わりに、セルB6とB4の結果は上記のように計算されます。
反復計算オプションは通常、デフォルトで無効になっています。オンにしない場合、およびB4に数式を入力すると、循環参照が作成されます。 Excelが警告を発行し、[OK]をクリックすると、トレーサーの矢印が表示されます。
それでおしまい。 Excelの循環参照について知っておく必要があるのはこれだけです。