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

Excel で循環参照が発生するのは、数式を含むセルが何らかの方法でそれ自体の結果に依存し、解決できないサイクルを作成する場合です。このエラーを停止するには、これらのリンクを見つけて削除し、Excel が計算を完了できるようにする必要があります。方法は次のとおりです。

Excel の循環参照とは何ですか?

Excel の循環参照について詳しく説明するために、シナリオを想像してみましょう。サンプルの Excel スプレッドシートには、値を持つ 3 つのセル (A2、A3、および A4) があります。

これらの各セルには、単純な合計計算で作成された値があります。A2 は A3 と A4 の値を合計し、A3 は A2 と A4 の合計、A4 は A2 と A3 の合計です。残念ながら、これら 3 つの計算は、循環参照なしでは共存できません。

A3 と A4 の両方に A2 を含む計算が含まれているため、A2 は A3 と A4 の合計を見つけることができません。

これは、数式または計算を含むセルは、循環参照エラーを発生させずに (直接または間接的に) それ自体を参照できないためです。処理できない無限の計算ループが作成されます。

これは、探している値が表示されないことを意味します。Excel がフリーズし、続行できません。

ほとんどの Excel ユーザーにとって、計算を完了する必要があるため、これは意図しない結果です。この問題を解決するには、リンクを見つけて修正する必要があります。

Excel で循環参照を見つける

ブックで循環参照が問題を引き起こしている場合、Excel は警告を表示します。自分でエラーを見つけられない場合は、[エラー チェッカー] メニューを使用して、ブック内のすべての循環参照を見つけることができます。

エラーチェックメニューの使用

既存の循環参照を見つけるには、ワークブックを開き、リボン バーの [数式] タブを選択します。次に、「エラーのチェック」ボタンをクリックします。

ドロップダウン メニューから [循環参照] オプションの上にマウスを置くと、ワークブック内のすべての循環参照のリストが表示されます。それを含むセルにジャンプするには、メニューでセル参照を選択します。

Excel が移動して、循環参照を含むセルを強調表示し、手動で解決できるようにします。

循環参照の追跡

複雑な数式を扱っている場合、多数の先行セルまたは依存セルが原因で、Excel で循環参照のソースを特定するのが難しい場合があります。先行セルは、選択したセルの値を変更するセルであり、従属セルは、計算を完了するために選択したセルの値を必要とするセルです。

それらに対処するために、Use Case Tracker ツールと Dependency Tracker ツールを使用して循環参照をソースまで追跡できます。Excel で循環参照を追跡するには、ブックを開き、循環参照を含むセルを選択します。

次に、[式] > [式を表示] を選択します。これにより、ワークブックが数式ビューに切り替わり、個々のセルを選択しなくても、使用中のすべての数式を表示できます。

Excel では矢印を使用してリンクの循環ループが視覚的に表示されるため、これは必要ありませんが、大きなブック内の問題のあるセルのリンクをすばやく特定するのに役立ちます。

数式ビューがアクティブで、循環参照を含むセルが選択されている状態で、[ケースの追跡] ボタンをクリックします。

他のセルを指す矢印が表示されます。矢印の軌跡に注意して、問題の原因を特定してください。

完了したら、[Trace Dependents] をクリックし、それが指す各セルを確認します。

前と同じように、問題を引き起こしている細胞に注意を払います。Excel は、円形のサポート ループを矢印と丸いアイコンで視覚的に識別します。

問題を解決するための完全な回答を得るには、循環リンクごとにこれらの手順を繰り返す必要がある場合があります。

Excel での循環参照の修正

Excel で循環参照を実際に解決する唯一の方法は、原因となった計算を編集することです。ループが壊れている場合、Excel は計算を完了できます。これを行う最も簡単な (そして最善の) 方法は、セル参照を含む数式を適切な値に置き換えることです。

ここに例があります。ブック内の 3 つのセル (A2、A3、および A4) すべてに、相互に参照する単純な計算が含まれているとしましょう。循環参照をなくすには、A2 と A4 の計算を同じ値に置き換える必要があります。

A3 の計算条件が満たされ、Excel がループを作成せずに値を計算できるようになります。直接値を使用したくない場合は、選択したセルへの既存の参照がない別のセル参照を参照する必要があります。

これは単純な例ですが、大規模で複雑な Excel ブックにも同じ原則が当てはまります。