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

循環参照は非常に難しい場合があるため、循環参照を見つける方法を知ることが重要です。Excel には、循環参照を検出し、計算が無限ループになるのを防ぐメカニズムが組み込まれています。この機能を有効にして有利に使用する方法を知る必要があるだけです。

この記事では、Excel で循環参照を見つける方法を説明します。さらに、そもそも循環参照とは何なのか、なぜ循環参照を避けるべきなのかについても説明します。

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

Excel ブックに数式を入力しようとして何らかの問題が発生した場合、循環参照が発生している可能性があります。これは、数式が独自の値を使用して計算しようとしているときに発生します。この時点で、Excel から次の警告メッセージが送信されます。

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

無限ループが永遠に続いたり、正しい答えに到達する前に終了したりする可能性があるため、Excel では循環参照を避けることが最善です。それだけでなく、循環参照により、ワークブック内の計算プロセス全体が大幅に遅くなる可能性があります。ただし、ほとんどの場合、循環参照に関する最大の問題は、循環参照を識別することです。

循環参照には、非意図的、意図的、非表示の 3 つのタイプがあります。意図的な循環参照を作成するには Excel の使用に熟練した人が必要となるため、循環参照の大部分は意図的ではありません。最後に、非表示の循環参照があります。偶発的な循環参照は簡単に見つけることができますが、Excel では隠れた循環参照を常に検出できるわけではないため、他の方法を使用する必要があります。

警告メッセージが表示されたら、「OK」または「ヘルプ」ボタンをクリックします。後者では、循環参照に関する詳細情報のみが提供され、ワー​​クブック内の循環参照がどこにあるかは示されません。一方、「OK」を選択した場合、またはメッセージをオフにした場合は、最後のセルに最後に計算された値またはゼロが表示されます。この通知は常に表示されるわけではないことに注意することも重要です。たとえば、意図的か非意図的かにかかわらず、さらに多くの循環参照を作成し続けると、Excel はこの問題についての通知を停止します。

ごくまれに、自己計算メカニズムが動作する前に、1 つの循環参照を含む式が完了することがあります。その場合、最後に成功した値のみが結果として表示されます。つまり、循環参照によりシステムが応答しなくなる可能性があります。だからこそ、それを特定することが最も重要なステップです。

Excel で循環参照エラーを修正するには、まずそれを見つける必要があります。その方法を確認するには、以下の手順に従ってください。

1. Excel に表示される警告メッセージをオフにします。
2. トップメニューの「数式」タブに移動します。
   
3. 「エラーチェック」タブに移動してクリックします。
   
4. ドロップダウン メニューの [循環参照] をクリックします。ここで、循環参照が明らかになります。
   
5. ポップアップ リストの値をクリックすると、その循環参照に直接移動します。
   

循環参照を含むセルをクリックすると、シートの下部にあるアドレス バーにも循環参照が表示されます。

循環参照に関してさらにヘルプが必要な場合は、先行例のトレースと依存関係のトレースという 2 つのツールが役に立ちます。最初のツールである先例の追跡は、クリックしたセルに影響を与えるセルを示す青い線を Excel ブックに表示します。一方、トレース依存はその逆を行います。線をトレースして、クリックしたセルの影響を受けるセルを示します。これら 2 つの機能は、Excel では検出できない循環参照を見つけるのに役立ちます。これらのトレーサは、循環参照の正確な場所を示すものではなく、循環参照をより速く見つけるための手がかりにすぎないことに注意してください。

トレース先行とトレース依存を有効にする場合は、次の手順に従います。

1. スプレッドシートの上部にある「数式」タブに移動します。
   
2. 「式の監査」カテゴリに進みます。
   
3. 「前例をトレース」または「依存関係をトレース」のいずれかを選択します。
   

一度に選択できるのは 1 つだけです。これをより迅速に行う方法は、次のショートカットを使用することです。トレース先行の場合は「Alt + TUT」、トレース依存の場合は「Alt + TUD」です。

Excel ユーザーの中には、反復計算を行うために意図的に循環参照を作成する人もいます。ただし、シートに循環参照を組み込むのは一般的に良い考えではありません。

追加のよくある質問

循環参照を削除する方法

Excel ブック内のすべての問題の原因となっている循環参照を最終的に見つけたら、それを削除する必要があります。これを自動的に修正する方法はありませんが、数式のどの部分を削除または置換する必要があるかを把握することはできます。セルをクリックすると、アドレス バーに「循環参照」タグが表示されなくなり、問題が解決したことがわかります。

循環参照は、反復計算機能が有効になっている場合にのみ、Excel シートで作成できます。この機能はデフォルトでは無効になっているため、通常は特に問題はありません。ただし、反復計算機能が何らかの方法で有効になっているかどうかを確認したい場合は、次のようにする必要があります。

1. 画面の左上隅にある「ファイル」タブに移動します。

2. メニューの下部にある「オプション」セクションに進みます。

3. ポップアップウィンドウの左側のサイドバーで「数式」を選択します。

4. [計算オプション] で、[反復計算を有効にする] ボックスにチェックが入っているかどうかを確認します。

5. 反復を無効にするには、チェックを外します。

6. 「OK」ボタンをクリックします。

この方法は、Excel 2010、Excel 2013、Excel 2016、および Excel 2019 に適用できます。Excel 2007 を使用している場合は、Office ボタンをクリックして [Excel オプション] に移動すると、反復計算機能が表示されます。「反復領域」セクションは「式」タブにあります。Excel 2003 以前を使用している場合は、「メニュー」から「ツール」タブに移動する必要があります。「計算タブ」は「オプション」セクションにあります。

Excel ワークブック内のすべての循環参照を見つける

ほとんどの場合、循環参照は偶然に作成されますが、非常に問題になる可能性があります。式全体が台無しになるだけでなく、計算プロセス全体が遅くなる可能性もあります。そのため、できるだけ早く見つけて交換することが重要です。幸いなことに、Excel は、何かが検出されるとすぐに通知します。また、トレース先行とトレース依存を使用して、循環参照と他のセルの間の関係を発見するのに役立ちます。

以前に Excel シート内で循環参照を見つけようとしたことがありますか? どうやって分かったの？以下のコメントセクションでお知らせください。