Excelで循環参照を検出・修正する4つの簡単な方法【一覧表示できない場合の対処法】

この記事では、Excelで一覧表示できない循環参照エラーを修正する方法を解説します。Excelで作業中に循環参照の警告が表示されると、戸惑ってしまう方も多いのではないでしょうか。特に数千ものセルを含む大規模なデータセットでは、1つずつセルを確認して循環参照を見つけるのは非常に困難です。そこで本記事では、データサイズに関わらず循環参照エラーを簡単に特定し、修正する4つの方法を詳しくご紹介します。

循環参照とは?

循環参照(サーキュラーリファレンス)とは、数式が計算過程において自分自身、または自分自身に依存するセルを参照してしまうことで発生する無限ループ状態のことです。これによりスプレッドシートの動作が著しく遅くなり、正しい計算結果が得られなくなります。

具体的な例を使って説明しましょう。以下のデータセットは6か月分の「売上金額」をまとめたもので、セルC11に売上合計を計算することを想定します。

SUM関数で合計を求める際、本来はセル範囲(C6:C10)を選択すべきところ、誤って(C6:C11)を選択してしまうと、期待した結果が得られません。

これは、セルC11の数式が自分自身も計算対象に含めようとしているためです。その結果、循環参照エラーの警告が表示されます。

循環参照エラーには、次の2種類があります。

1. 直接的な循環参照:

セル内の数式が、そのセル自身を直接参照している場合に発生します。

2. 間接的な循環参照:

セル内の数式が、他のセルを経由して間接的に自分自身を参照している場合に発生します。

一覧表示できない循環参照を修正する4つの方法

循環参照エラーが発生したら、早急に対処する必要があります。まずはエラーの場所を特定し、その後数式を修正します。ここでは、循環参照を特定するための4つの異なる方法を紹介し、それぞれ数式を修正してエラーを解消していきます。

方法1:リボンの「エラーチェック」ツールを使う

最初に紹介するのは、Excelリボンの「エラーチェック」ツールを使って循環参照を特定する方法です。ここでは、セルC11に循環参照エラーが含まれるデータセットを使用します。実際の業務では数千のセルから循環参照を見つけ出す必要がありますが、手順は同じです。

「エラーチェック」ツールで循環参照を特定する手順は以下の通りです。

手順:

• まず、数式タブを開きます。

• 次に、数式タブのリボンにある「エラーチェック」のドロップダウンをクリックし、「循環参照」を選択します。
• すると画面の左側にサイドバーが表示され、ワークシートのセルC11に循環参照が発生していることが示されます。
• 操作のまとめ: 数式 > エラーチェック > 循環参照

• 続いて、セルC11を選択します。このセルの数式が自分自身を計算しようとしています。

• セルC11の数式を以下のように修正します。

=SUM(C5:C10)

• Enterキーを押します。
• 最後に、セルC11から循環参照エラーが消えたことを確認できます。売上合計は$17,000と正しく表示されます。

方法2:ステータスバーを使う

「ステータスバー」を使った循環参照の確認は、最も手軽な方法です。前の例と同じデータセットを使って、手順を見ていきましょう。

手順:

• まず、循環参照エラーが含まれるワークシートを開きます。
• 次に、シート名の下にある「ステータスバー」を確認します。
• ステータスバーに「循環参照: C11」と表示されており、セルC11に循環参照エラーがあることがわかります。

• その後、セルC11の数式の範囲を(C5:C11)から(C5:C10)に変更します。

=SUM(C5:C10)

• Enterキーを押します。
• これでセルC11の循環参照エラーが解消され、正しい合計額が返されます。

注意点:

1つのワークシートに循環参照を含むセルが2つ以上ある場合、「ステータスバー」には最新の1件のみが表示される点に注意してください。

方法3:反復計算を有効にする

反復計算を有効にすることでも、循環参照への対処が可能です。通常、循環参照はエラーとして扱われますが、反復計算を有効にすると、指定した回数だけ計算を繰り返して結果を返すようになります。ここでも同じデータセットを使用します。

手順:

• まず、ファイルタブを開きます。

• 次に、オプションを選択します。

• 「Excel のオプション」ダイアログボックスが表示されます。
• 左メニューから数式を選択し、「反復計算を行う」にチェックを入れます。「最大反復回数」を1に設定します。値が1の場合、数式はセルC5からC10を1回だけ計算します。
• OKをクリックします。

• 最後に、セルC11に循環参照エラーが表示されなくなり、売上合計が正しく返されていることを確認できます。

補足: 反復計算は根本的な解決策ではなく、あくまで一時的な回避策として使うのがおすすめです。可能であれば数式自体を修正しましょう。

方法4:「トレース矢印」機能で循環参照を見つけて修正する

循環参照はワンクリックで見つけることができません。そこで、「参照元のトレース」と「参照先のトレース」という2つのトレース機能を使い、1つずつ追跡しながら修正していきます。

4.1 「参照元のトレース」機能を使う

「参照元のトレース」機能は、アクティブなセルに影響を与えているセルを青い矢印で可視化してくれます。以下のデータセットでは、セルC11にセル範囲(C5:C10)の合計を返すため、これらのセルがセルC11に影響しています。

手順:

• まず、セルC11を選択します。

• 次に、数式タブを開きます。
• 「参照元のトレース」オプションを選択します。

• 矢印が描画され、セル範囲(C5:C11)がセルC11に影響していることが表示されます。セルC11が自分自身を計算しようとしているため、循環参照エラーが返されています。

• 続いて、セルC11の数式の範囲を(C5:C11)から(C5:C10)に変更します。修正後の数式は以下の通りです。

=SUM(C5:C10)

• Enterキーを押すと、このセルから循環参照が取り除かれます。
• 最後にもう一度セルC11で「参照元のトレース」を実行すると、今度はセル範囲(C5:C10)のみがセルC11に影響していることが確認できます。

注意点:

「参照元のトレース」のショートカットキー:Alt + T U T

4.2 「参照先のトレース」機能を使う

「参照先のトレース」機能は、アクティブなセルに依存しているセルを見つけるために使用します。矢印を描画することで、どのセルがアクティブセルに依存しているかを確認できます。同じデータセットで「参照先のトレース」を使って循環参照を特定してみましょう。

手順:

• まず、セルC11を選択します。

• 次に、数式タブを開きます。
• リボンから「参照先のトレース」オプションを選択します。

• 矢印が描画され、セル範囲(C5:C10)がセルC11に依存していることが表示されます。

• その後、セルC11の数式の範囲を(C5:C11)から(C5:C10)に変更します。修正後の数式は以下の通りです。

=SUM(C5:C10)

• Enterキーを押します。
• 最後に、セルC11から循環参照が消えたことを確認できます。

注意点:

「参照先のトレース」のショートカットキー:Alt + T U D

まとめ

今回は、Excelで一覧表示できない循環参照を特定・修正する4つの方法をご紹介しました。エラーチェックツールやステータスバーでの特定、反復計算による回避、トレース機能を使った視覚的な確認など、状況に応じて使い分けることで、大規模なデータセットでも効率的に問題を解決できます。ぜひ練習用ワークブックを使って実際に試してみてください。ご不明な点があれば、下のコメント欄でお気軽にお尋ねください。今後も役立つMicrosoft Excelの活用術をお届けしていきます。

関連記事

• Excelで循環参照を削除する方法(2つの方法)
• Excelで循環参照を許可する方法(2つの活用例付き)