Excel で循環参照を検索する方法

Microsoft Excelを使用しているときに、いわゆる循環参照が発生することがあります。これは、セル内の数式がそれ自体を参照し、計算プロセスで無限ループを引き起こす場合です。

Excel で循環参照を見つけるには、[数式] タブの [エラー チェック] ツールを使用して、循環依存関係のあるセルを特定します。

この記事では、Excel で循環参照を見つけて修正する方法を説明します。また、大規模なワークシートまたは複雑なワークシートを操作するときに、それらを防ぐ方法も学習します。

目次

• 循環参照とは
• 循環参照エラー メッセージ ウィンドウ
• Excel で循環参照を見つける方法
• Excel ステータス バーを使用して循環参照を検索する方法
• Excel で隠れた循環参照を見つける方法
  • トレース依存ツールの使用方法
  • トレース先例ツール
• Excel で循環参照を削除する 2 つの方法
  • 2. 代替の公式を使用する
• 循環参照を防ぐ 5 つのヒント
  • 1. 範囲を選択するときは注意してください
  • 2. 処方を慎重に計画する
  • 3. 代替の公式を使用する
  • 4. 複雑な式を小さな部分に分割する
  • 5. 問題を解決しながら解決する
• 反復計算を有効にする方法となぜわざわざ行うのか?

循環参照とは

Excel では、セル内の数式計算がセル自体の値に依存する場合、循環参照が発生します。これによりループが発生し、不正確または予測不可能な結果が生じます。

これは、簡単な例で理解するのが簡単です。

セル A1 と B1 に 2 つの数値があるとします。セル A3 で 2 つの数値を加算する数式を使用するとします。セル A3 の数式は次のようになります。

=A1 + B1

ただし、間違って計算に C1 を含めると、循環参照が発生します。

=A1 + B1 + C1

これは、2 つのセルを扱う場合には避けるべき明らかな問題かもしれません。ただし、広範囲のセルを操作する場合は、間違いが発生する可能性が高くなります。

ありがたいことに、Excel は循環参照が検出されたときに警告メッセージを表示することで、このような状況に対処できるように設計されています。

ただし、場合によっては、警告を受信せずに循環参照が発生する可能性があります。したがって、これらの問題を手動で見つけて解決する方法を知ることが重要です。

循環参照エラー メッセージ ウィンドウ

デフォルトでは、Excel はセルに数式を入力すると循環参照を監視します。問題のあるセルで Enter キーを押すと、ポップアップ ウィンドウに循環参照エラー メッセージが表示されます。

説明文はこうです。

数式がそれ自身のセルを直接的または間接的に参照する循環参照が 1 つ以上あります。これにより、計算が正しく行われない可能性があります。これらの参照を削除または変更するか、数式を別のセルに移動してみてください。

このダイアログ ボックスが表示されたら、[OK] をクリックし、この記事の方法を使用して問題を解決してください。

ただし、エラーをすぐに修正する必要はありません。後で問題を解決することを目的として、スプレッドシートの作業を続けることができます。

その場合、警告ボックスを閉じた後に Excel で循環参照を見つける方法を知っておく必要があります。

Excel で循環参照を見つける方法

Excel には、循環参照を含む数種類のエラーを検出する組み込みのエラー チェック ツールがあります。このツールには [数式] タブからアクセスします。

この図は、式監査グループ内の場所を示しています。

ツールの見つけ方はわかりました。使用するための詳細な手順は次のとおりです。

1. [数式] タブに移動します。

1. [式の監査] グループの [エラー チェック] ドロップダウンをクリックします。

2. ドロップダウンリストから「循環参照」を選択します。

3. リンクをクリックして、問題のある最初のセルにカーソルを移動します。

下の図は、ツールがセル G1 で循環エラーを検出したことを示しています。表示されているセルはハイパーリンクです。

リンクをクリックすると、カーソルがセルに移動します。これは、大量のデータの場合に非常に役立ちます。

最初の循環参照を解決したら、エラー チェック ツールに戻ってシート上の次の循環参照を見つけることができます。

この記事の後のセクションの手順を使用して問題を解決し、ツールを使用して次の問題に進みます。

循環問題がなくなった場合は、ドロップダウン メニューの [循環参照] メニュー項目がグレー表示になります。これ以上やるべきことはありません。

Excel ステータス バーを使用して循環参照を検索する方法

循環参照を見つけるもう 1 つの方法は、Excel ワークシートの下部にあるステータス バーを見ることです。

ワークシートに 1 つ以上の問題がある場合、ステータス バーには 1 つの問題セル参照の位置が表示されます。

たとえば、セル C1 に循環参照があり、スプレッドシートに他に問題がない場合、ステータス バーには「循環参照: C1」と表示されます。

この図は、ディスプレイが表示される場所を示しています。

Excel で隠れた循環参照を見つける方法

Excelの非表示の循環参照は、組み込みのエラー機能によってすぐには検出されない循環参照です。つまり、Excel では標準の警告は表示されません。

これらは、セルが一連の中間セルを介して別のセルを間接的に参照し、最終的に元のセルを参照するときに発生します。

Excel には、関係を追跡し、これらの問題を調査するのに役立つ 2 つのツールがあります。

1. 依存関係のトレースツール

2. トレース先例ツール

トレース依存ツールの使用方法

経験豊富な Excel ユーザーは、依存関係の追跡ツールを利用して、これらの問題を調査し、循環参照を修正します。1 つ以上のセルを選択してこのツールを使用すると、Excel は依存セルを表示します。

このツールは、[数式] タブの [数式監査] セクションで利用できます。

このツールを使用するには、次の手順に従います。

1. 依存関係をトレースしたいセルをクリックします。

2. Excel リボンの [数式] タブに移動します。

3. 「式の監査」グループの「依存関係のトレース」ボタンをクリックします。

4. 依存関係を示すトレーサーの矢印を確認します。

Excel では、アクティブ セルの値からそれに依存するセルへの矢印が表示されます。下の図の青い矢印は、セル D6 がセル B2 に依存していることを示しています。

トレース先例ツール

先例は扶養の逆です。[前例の追跡] ツールを使用すると、数式が含まれるセルを選択し、それが依存するすべてのセル、つまり前例を表示できます。

このツールは、[数式] タブの [数式監査] セクションで利用できます。

このツールを使用するには、次の手順に従います。

1. 調査する数式が含まれるセルをクリックします。

2. Excel リボンの [数式] タブに移動します。

3. 「式の監査」グループの「前例のトレース」ボタンをクリックします。

4. Excel に表示されるトレース矢印を確認します。

下の図の青い矢印は、セル D6 がセル A2:B5 の範囲に依存していることを示しています。

両方のツールを使用すると、すぐには分からない Excel の循環参照を見つけるのに役立ちます。次のステップは、選択したセルの問題を修正することです。その後、他のセルで同じ問題を引き続き検索できます。

Excel で循環参照を削除する 2 つの方法

循環参照を解決するには、主に 2 つの方法があります。

• 循環参照を破る

• 代替の公式を使用する

両方の方法について説明しましょう。

1. 循環参照を破る

Excel で循環参照を解除することは、問題を解決する効率的な方法です。これには、循環参照に関係するセルを特定し、それに応じて数式を変更する必要があります。

これを行うには、前述の手順に従ってセル アドレスを見つけます。

次に、数式バーで数式を調べる必要があります。セル自体への参照を探します。

循環参照の最初の例では、セル C1 の加算式にセル自体への参照が含まれていました。

=A1 + B1 + C1

これは、最後の追加を削除することで修正できます。この問題が解決されたら、他の循環参照を見つけて対処します。

2. 代替の公式を使用する

循環参照の最も一般的な原因は、セルの合計または平均化を行う場合です。間違いは、結果セルを数式に含めることです。

前の例では、数式は加算演算子を使用してセル範囲の合計を計算しました。別の方法は、SUM Excel 関数を使用することです。

この例を修正するには、式を次のように置き換えます。

=SUM(A1:B1)

同様に、=(A1+B1)/2 のような数式を使用して範囲を平均することができます。ただし、AVERAGE 関数を使用すると間違いが発生する可能性が低くなります。

=平均(A1:B1)

循環参照を防ぐ 5 つのヒント

Microsoft Excel で循環参照を防ぐには、Excel シート内で数式がどのように相互作用するかを明確に理解することが重要です。

セル間の関係を特定し、これらの接続がどのように連携するかを認識することで、循環式の作成を回避できます。

ここでは、循環参照を防ぐための 5 つのヒントを紹介します。

1. 範囲を選択するときは注意してください

私の経験では、循環参照の最も一般的な原因の 1 つは、ユーザーが数式に含める範囲をすばやく選択している場合です。

垂直または水平にスワイプしてセルをグループ化する場合、正しい範囲に隣接するもう 1 つのセルを簡単に選択できます。そのセルが結果セルでもある場合は、誤って循環参照を作成したことになります。

この間違いは通常、カイ二乗検定の実行など、大規模なデータセットを扱うときに発生します。解決策は、範囲の選択に注意することです。

2. 処方を慎重に計画する

数式を入力する前に、計算の背後にあるロジックと、計算がセル間でどのように流れるかを考えてください。

作図ツールやホワイトボードを使用して、事前に計算を計画することができます。

これは、循環参照につながる可能性のあるループを特定するのに役立ちます。

3. 代替の公式を使用する

INDIRECTやOFFSETなどの特定の関数は、不適切に使用すると、誤って循環参照を作成する可能性があります。

循環参照のリスクなしに同様の機能を提供する代替関数の使用を検討してください。

4. 複雑な式を小さな部分に分割する

複数の入れ子関数を含む単一の複雑な数式を作成する代わりに、計算をより小さく管理しやすい部分に分割することを検討してください。

これにより、ワークブックの理解と保守が容易になるだけでなく、循環参照が導入される可能性も減ります。

5. 問題を解決しながら解決する

大規模なブックや複雑なブックを操作する場合は、循環参照を監視することも重要です。大規模なデータと数式が組み立てられるまで問題を無視するのではなく、エラーが発生したときにそれを修正する方が良いでしょう。

循環参照の警告メッセージをしばらく無視することもできます。ただし、エラー チェック機能を使用して、すぐに既存の循環参照に追いつく必要があります。

これにより、修正が容易になり、ワークブックへの影響を防ぐことができます。

反復計算を有効にする方法となぜわざわざ行うのか?

通常、ワークシート内での循環参照は避けたいと考えます。ただし、それらが必要となるシナリオもいくつかあります。

たとえば、一部の工学または科学計算では、以前の計算の結果を使用する必要があります。これは反復解法として知られています。

各ループは、特定のしきい値または目標に達するまで計算の結果を調整します。

このような場合、意図した循環参照を許可するには、Excel 設定で反復計算を有効にすることが重要です。これにより、ソフトウェアは指定された回数だけ計算を繰り返し、解に収束します。

Excel ファイルで循環参照を機能させるには、次の手順に従います。

1. 「ファイル」タブをクリックします。

2. オプションを選択。

3. [数式] カテゴリを選択します。

4. 「計算オプション」セクションで、「反復計算を有効にする」チェックボックスをオンにします。

5. [最大反復回数] ボックスの数値を変更するか、デフォルトの 100 のままにします。

6. 要件に応じて反復回数を減らすには、最大変更ボックスを使用します。