Excelの循環参照の警告が出る時｜原因の見つけ方と直し方

この記事が対象にするExcel

本記事の操作画面と手順は、Windows版のExcel for Microsoft 365を対象にしています。参照した公式ページの適用先には、Excel for Microsoft 365 for MacなどMac版も含まれています。

公式ページには、プラットフォームによる違いについての記載もあります。Excel for the webは数式の計算をサポートしているが、循環参照のトラブルシューティングツールはデスクトップよりも制限される可能性がある、という説明です。iOSとAndroidについても、完全なトレースと修正のためにはデスクトップアプリを使うよう案内されています。

ブラウザーやスマートフォンで開いていて操作が見当たらない場合は、デスクトップ版のExcelでファイルを開いてから作業してください。（参考：Microsoft公式サポート「Excel で循環参照を削除または許可する」）

循環参照が起きる3つのパターン

Microsoftは、循環参照が作成される代表的なパターンを3つ挙げています。どれも「たどっていくと自分に戻ってくる」という点が共通しています。

※図解は、Microsoftの公式情報をもとに作成した説明用の画像です。実際の画面表示は、Excelのバージョン、設定、画面幅などにより異なる場合があります。

実務で起きやすいのは、2番目のパターンです。合計を入れるセルまで、合計する範囲に含めてしまっている状態を指します。行を追加したときに範囲を広げすぎて、答えのセルまで巻き込んでしまう、という形で起こります。

3番目の間接ループは、1つの数式を見ただけでは気づきにくい形です。A1とB1のように、離れた2か所が互いを参照していると、どちらの数式も単体では自然に見えます。

原因のセルを一覧で見つける

勘で探す必要はありません。Excelには、循環参照のあるセルを一覧で示す機能があります。

1. 「数式」から「エラー チェック」、「循環参照」へ移動します。
2. 一覧表示されている各セルアドレスを選択し、数式を更新して、それ自体をポイントしないようにします。
3. ステータスバーに循環参照が表示されなくなるまで、この手順を繰り返します。

先ほどの =SUM(A3:F3) の例であれば、範囲の終わりを1つ手前にして、自分のセルを含まない形にします。数式の書き方そのものは正しくても、範囲の指定に自分が入っているために計算が完了しない、というのがこのパターンです。

複数のセルにまたがる場合

間接ループのように、原因が1か所に収まっていない場合の方法も案内されています。Microsoftは、問題が複数のセルにまたがる場合は、「数式」から「参照元の追跡」または「依存項目の追跡」を使って、関係をたどるよう説明しています。

2つの機能は、たどる向きが逆です。参照元をたどれば「このセルは、どこを見に行っているか」が分かり、依存項目をたどれば「このセルは、どこから見られているか」が分かります。行き先と来た道の両方を確認すると、輪になっている経路が見えてきます。

移動を助けるショートカットも案内されています。Ctrl＋G（Windows）またはControl＋G（Mac）を押すと、参照元のセルへすばやくジャンプできます。

数式が参照するセルの固定については「Excelの絶対参照と相対参照」で解説しています。

警告と、セルに表示される値

メッセージの出方にも決まりがあります。Microsoftは、Excelでは、セッションで初めて循環参照が作成されると警告メッセージが表示され、それ以降の循環参照では同じ警告ダイアログが表示されない場合があると説明しています。1回きりの表示で終わることがあるため、後から作った循環参照に気づきにくい、という性質があります。

警告を閉じたあとについては、Excelはセルに0または最後に計算された値を表示する場合があると記載されています。必ず0になるとは限りません。0が表示されていないセルに循環参照があると思われる場合は、数式バーで数式を選択してEnterキーを押してみるよう案内されています。

ステータスバーの表示についても説明があります。ステータスバーには「循環参照」と1つのセルアドレスが表示されることがあります。ただし、別のワークシートに循環参照が存在する場合、アドレスが表示されず「循環参照」だけが表示されることがあるとされています。アドレスが出ていないときは、別のシートを探すことになります。

直してもメッセージが消えない場合

1つ直したのにメッセージが続く場合、原因が別の場所にあります。Microsoftは、編集後もアラートが続く理由として4つを挙げています。

• 別のワークシートに、別の循環参照が残っている
• 別の開いているブックに、まだ循環参照がある
• 数式チェーンが、引き続き間接的にそれ自体を指している
• 反復計算が有効になっているため、循環参照が意図的に許可されている

見落としやすいのが、2番目の「別の開いているブック」です。今見ているファイルだけを確認しても解決しない場合があります。同時に開いている他のブックも確認の対象になります。

3番目の数式チェーンは、間接ループが残っている状態です。この場合は、参照元の追跡と依存項目の追跡で経路をたどります。4番目については、次の章で扱います。

反復計算で意図的に許可する場合

循環参照は、常に誤りとは限りません。Microsoftは、再計算の繰り返しが意図的である特定の財務モデルまたはエンジニアリングモデルの循環参照は許可できると説明しています。ただしあわせて、ほとんどのワークシートでは、反復計算をオフのままにしますとも明記しています。

設定の経路は次のとおりです。Excel for Windowsデスクトップでは、「ファイル」から「オプション」、「数式」へ移動します。Excel for Macデスクトップでは、「Excel」から「環境設定」、「計算」へ移動します。

1. 「反復計算を有効にする」（Windows）または「反復計算を使用する」（Mac）を選びます。
2. 「最大反復回数」を設定して、再計算の回数を制御します。
3. 「変更の最大値」を設定して、再計算を停止するタイミングを制御します。

既定値についても記載があります。既定では、Excelは反復回数が100回の繰り返し後、または値の変化が0.001未満の場合に停止します。