Excelで「循環参照」が出ているのに、式を見ると自分自身を参照しているように見えない。そんなときは、セルそのものではなく名前定義（名前付き範囲・定義された名前）が原因になっていることがあります。結論から言うと、[数式] の [循環参照] で起点セルを開き、[名前の管理] でその式に使われている名前の参照範囲・スコープ・相対参照を確認し、最後にトレース矢印か [数式の検証] で確定するのが最短です。特に見落としやすいのは、相対参照で作った名前、シート/ブックで同名の名前、名前マネージャーに出ない非表示の名前です。 (Microsoft サポート)
まずはこの順で見れば迷いにくい
- [数式] → [エラー チェック] → [循環参照]
循環参照の起点セルを順に開きます。ステータスバーにも循環参照が表示され、別シートが原因の場合はアドレスなしで「循環参照」とだけ出ます。 (Microsoft サポート) - [数式] → [名前の管理]
名前の参照範囲、値、スコープを確認します。フィルターで「ワークシート内にある名前」「ブック内にある名前」「エラーのある名前」「定義された名前」だけに絞れます。 (Microsoft サポート) - [数式の監査] → [参照元のトレース] / [参照先のトレース]
依存関係を矢印で追えます。別シートや別ブックは黒い矢印で示され、ダブルクリックでジャンプできます。 (Microsoft サポート) - [数式] → [数式の検証]
名前や参照がどの値・セルに展開されるかを1段ずつ確認できます。循環参照を含む式は期待どおりに評価されないことがあるので、最終確認用と考えると使いやすいです。 (Microsoft サポート)
名前定義が原因だと見つけにくい理由
循環参照メニューが教えてくれるのは「問題が表面化したセル」であって、必ずしも「原因になっている名前」ではありません。左下のステータスバーは通常 1 件のセル番地しか出さず、原因が別シートにある場合はアドレスなしで「循環参照」とだけ表示されます。さらに、名前の参照範囲にはセル範囲だけでなく =SUM(Sheet1!A1,Sheet2!B2) のような数式も入れられるため、見た目は普通の式でも、名前をたどると遠回りで元のセルに戻っていることがあります。 (Microsoft サポート)
さらにやっかいなのは、[名前の管理] に表示されるのは通常の定義名やテーブル名が中心で、VBA で定義した名前や非表示の名前は表示されない点です。名前マネージャーで候補が見当たらないのに名前定義が怪しい場合は、「見えていない名前があるかもしれない」という前提で切り分ける必要があります。 (Microsoft サポート)
Excelで名前定義が原因の循環参照を見つける手順
循環参照メニューで最初のセルを開く
最初にやることは、[数式] タブの [エラー チェック] の横にある矢印から [循環参照] を開き、一覧に出る最初のセルへ移動することです。そこで数式バーを見て、名前定義が使われていないかを確認します。1回で原因が分からなければ、同じメニューで次のセルを順に開いていきます。公式の手順でも、ステータスバーから「循環参照」が消えるまで確認と修正を繰り返す流れが案内されています。 (Microsoft サポート)
ここで数式が =売上*税率 のように見えても安心はできません。セル参照が直接見えなくても、税率 や 売上 の中身が別の数式や別シート参照になっていて、最終的に元セルへ戻るケースがあるからです。見た目が単純な式ほど、次の [名前の管理] で中身を開く価値があります。 (Microsoft サポート)
[名前の管理] で参照範囲とスコープを洗う
次に、[数式] → [名前の管理] を開き、式に登場した名前の 参照範囲 と スコープ を確認します。ここでは「その名前がどのセルや数式を指しているか」「ブック全体で有効なのか、特定シートだけなのか」を見ます。フィルターを使えば、ワークシートレベルの名前、ブックレベルの名前、エラーを含む名前だけに絞れます。テーブルが多いブックでは、「定義された名前」だけに絞って Table1 などのノイズを外すと見やすくなります。 (Microsoft サポート)
特に注目したいのは、参照範囲が単なるセル番地ではなく、=SUM(Sheet1!A1,Sheet2!B2) のような数式になっている名前です。名前定義は「別名のセル範囲」だけではなく、「別名の数式」にもできるため、循環参照の経路が1段深くなります。セルだけ見ても分からないときは、名前の中身を見ない限り真因にたどり着けません。 (Microsoft サポート)
相対参照の名前は最優先で疑う
名前定義が原因の循環参照で、とても見落としやすいのが相対参照のまま作られた名前です。Microsoft の説明では、名前作成時に B1 のように $ なしで入力すると相対参照になり、A1 をアクティブにして作成した場合、その B1 は「次の列のセル」という意味になります。そして、その名前を C10 の式で使うと、参照先は B1 ではなく D10 として解釈されます。つまり、名前は「作ったときの見た目のセル番地」ではなく、「使う場所に応じてずれる設計」になり得ます。 (Microsoft サポート)
そのため、参照範囲に $ が付いていない名前は、意図がない限り要注意です。たとえば、A1 基準の相対参照で作った名前を別セルの数式で使うと、使用位置によっては現在セル自身を指す設計になり、循環参照の原因になります。意図して相対参照を使っていないなら、=B1 ではなく =$B$1 のように絶対参照へ直すのが基本です。 (Microsoft サポート)
典型例
- 名前
基準セルの参照範囲が=A1のような相対参照になっている。 - 任意のセルに
=基準セル+1を入れる。 - その名前が「現在のセル自身」を指す位置で使われると、見た目は普通でも循環参照になる。
同じ名前が複数スコープに存在していないか確認する
もう1つの典型が、同名の名前がシートレベルとブックレベルに重複しているケースです。名前にはスコープがあり、ワークシート限定のローカル名と、ブック全体で有効なグローバル名があります。Excel は同じ名前を異なるスコープで持てますが、同名が競合したときはローカル ワークシート レベルの名前が優先されます。結果として、あるシートだけ別の参照先に化け、そこで循環参照が起きます。 (Microsoft サポート)
たとえば、ブック全体の Rate はマスターシートの定数セルを指しているのに、特定シートに同名の Rate があり、そのシートの式セル付近を指していた、というケースです。この場合、問題のシートではローカル名が優先されるため、「全体の Rate を使っているつもり」でも、実際はそのシート専用の Rate を読んでしまいます。名前の一覧に同名が並んでいないか、まずスコープ列で確認してください。 (Microsoft サポート)
トレース矢印で「どこへ戻っているか」を確定する
[名前の管理] で怪しい名前を見つけたら、次は [参照元のトレース] / [参照先のトレース] で依存関係を可視化します。アクティブセルへ直接データを渡すセルを見るなら参照元、反対にそのセルを参照している式を見るなら参照先です。青い矢印は通常セル、赤い矢印はエラー原因セル、黒い矢印は別シートや別ブックを示します。矢印はダブルクリックでたどれます。 (Microsoft サポート)
この操作で警告音が鳴ったり、思ったように追えなかったりする場合もあります。Microsoft は、監査ツールで追跡できない対象として、テキストボックス、埋め込みグラフ、ピボットテーブル、名前付き定数、閉じた別ブックへの参照などを挙げています。つまり、トレースできないこと自体が「名前付き定数か外部参照を疑うサイン」になります。 (Microsoft サポート)
なお、行や列の挿入・削除、セルの移動や削除、数式変更を行うと、トレース矢印は消えます。修正しながら追うと迷いやすいので、まず矢印で経路を確認し、その後に直す順番のほうが失敗しにくいです。 (Microsoft サポート)
[数式の検証] で名前の展開先を1段ずつ見る
トレース矢印だけでは確定しにくいときは、[数式] → [数式の検証] を使います。下線が付いた参照を 1 段ずつ評価でき、他の数式への参照なら [ステップ イン] で中へ入れます。長い式の中で、どの名前がどの値やセルへ展開されているかを見るのに向いています。 (Microsoft サポート)
ただし、循環参照を含む数式は期待どおりに評価されない場合があります。また、同じ参照が式に2回出てくる場合や、他ブック参照では [ステップ イン] が使えないことがあります。つまり、[数式の検証] は万能ではありませんが、「名前がどこを指しているのか分からない」状態を抜けるにはかなり有効です。 (Microsoft サポート)
よくある詰まり方と対処
- 循環参照メニューでセルは見つかるのに、数式が普通に見える
名前の参照範囲が数式になっていて、間接的に元セルへ戻っている可能性があります。[名前の管理] の参照範囲をまず確認してください。 (Microsoft サポート) - ステータスバーに「循環参照」だけ出て、アドレスが出ない
原因が別シートにある可能性が高い状態です。スコープ列と黒いトレース矢印を優先して見ます。 (Microsoft サポート) - トレースがうまく出ない、警告音が鳴る
名前付き定数、ピボット、閉じた外部ブックなど、監査ツールで追えない対象が混ざっている可能性があります。[数式の検証] に切り替え、必要なら外部ブックを開いて確認します。 (Microsoft サポート) - [名前の管理] に候補が出ない
VBA 定義名や非表示の名前は標準の名前マネージャーに出ません。通常の UI だけでは見えない候補を疑ってください。 (Microsoft サポート)
修正するときの注意点
修正の基本は、参照範囲を本来のセルへ戻すことです。相対参照が原因なら絶対参照へ直し、同名のローカル名が原因なら名前を変えるか不要な方を削除します。Microsoft の説明では、定義された名前やテーブル名を変更すると、その名前の使用箇所もブック内でまとめて変わります。つまり、安易に名前を削除・改名すると、別シートの式まで一気に影響します。影響範囲が見えないブックでは、いきなり本番ファイルを触らず、コピーで検証するほうが安全です。 (Microsoft サポート)
名前の編集途中に迷ったら、[名前の管理] の [参照先] / [参照範囲] ボックスで変更を保存するか破棄できます。保存前なら Cancel や Esc で戻せますが、いったんコミットした変更は「閉じる」だけでは取り消せません。修正前後で参照範囲がどう変わったかは、必ず確認してから閉じるのがおすすめです。 (Microsoft サポート)
反復計算は「見つからないから有効化」しない
循環参照が意図されたモデルなら、[ファイル] → [オプション] → [数式] から反復計算を有効にできます。ただし、Excel は通常これをオフにしており、有効にするなら最大反復回数と変化の最大値を設定できます。さらに、計算オプションの変更は開いているすべてのブックに反映されます。警告を消すためだけに有効化すると、別ブックの計算挙動まで変わるので注意が必要です。 (Microsoft サポート)
Microsoft は、反復計算の結果として「収束する」「発散する」「2つの値を行き来する」可能性を示しています。つまり、名前定義の循環参照が分からないからとりあえず反復計算をオンにする、という対処は本質的な解決になりません。まずは参照範囲とスコープの誤りを直すのが先です。 (Microsoft サポート)
Excel for web で探すのが難しい理由
Web 版の Excel でも、Windows や Mac で作成済みの名前付き範囲を使ったり、名前ボックスから既存の名前へ移動したりはできます。ただし、現時点では Web 版で新しい名前付き範囲を作成できません。さらに、Excel for the web では数式のエラーをチェックするルールが使えないため、循環参照の切り分けはデスクトップ版のほうが確実です。 (Microsoft サポート)
迷ったら、最後はこの3点だけ見れば十分
名前定義が原因の循環参照は、起点セル、名前の参照範囲、スコープ の3点でほぼ絞れます。まず [循環参照] で最初のセルを開く。次に [名前の管理] で参照範囲に $ がない名前、数式を返す名前、同名で複数スコープの名前を探す。最後に [参照元/参照先のトレース] か [数式の検証] で、実際にどこへ戻っているかを確定する。この順で進めれば、セルだけ見て迷い続ける状態から抜けやすくなります。 (Microsoft サポート)
今まさに詰まっているなら、最初の1手は [数式] → [エラー チェック] → [循環参照] です。そこから出たセルの式にある名前を [名前の管理] で開き、参照範囲に相対参照や想定外のスコープがないかを見てください。それだけで、名前定義が原因の循環参照を特定するところまで進みやすくなります。 (Microsoft サポート)
コメント