Excelで「循環参照が1つ以上あります」と表示されたら、数式が直接または別のセルを経由して自分自身を参照しています。Microsoft 365のWindowsデスクトップ版を対象に、循環参照の場所を見つけ、参照範囲を直して警告が消えたことを確認する手順を説明します。エクセルで計算結果が0や直前の値になり、原因のセルが分からない場合にも使える方法です。
循環参照は数式が自分の結果を必要とする状態
循環参照があると、Excelは計算を完了できません。たとえば、C2からC5までの金額を合計するC6に「=SUM(C2:C6)」と入力すると、合計を表示するC6自身が合計範囲に含まれます。正しい範囲がC2からC5なら、「=SUM(C2:C5)」へ直す必要があります。
別のセルを経由する場合もあります。D2の数式がE2を参照し、E2の数式がD2を参照していると、見た目では自分自身を含んでいなくても計算が一周します。このような間接的な循環参照は、セル同士のつながりを順に確認して見つけます。
なお、数式をコピーした後に参照先がずれたことが原因なら、循環参照を直した後で絶対参照と複合参照の使い分けも確認すると、同じ問題の再発を防ぎやすくなります。
修正前にブックを保存して元の数式を残す
循環参照を直す作業では数式や参照範囲を変更します。最初に［ファイル］タブから［名前を付けて保存］を選び、元のブックとは別名のコピーを作ってください。修正結果が意図と異なった場合に、変更前の数式へ戻れます。
警告ダイアログは、Excelを開いてから最初に循環参照が見つかったときだけ表示され、その後は表示されないことがあります。警告を閉じた後は、Excelウィンドウ下部のステータスバーを見ます。「循環参照: C6」のようにセル番地が表示されていれば、そのブックに問題が残っています。別のワークシートに問題がある場合は、セル番地が付かず「循環参照」とだけ表示されることもあります。
エラーチェックの一覧から循環参照のセルへ移動する
コピーを保存したら、リボンの［数式］タブから問題のセルを探します。
- ワークシート上のセルを1つ選択します。
- ［数式］タブを開き、［エラーチェック］の右側にある▼をクリックします。
- メニューの［循環参照］にポインターを合わせます。
- 表示されたセル番地をクリックします。該当セルが選択されるので、数式バーで式を確認します。
- 合計範囲に結果セルが含まれている、または参照先が誤っている箇所を修正し、Enterキーで確定します。
先ほどの例なら、C6の「=SUM(C2:C6)」を「=SUM(C2:C5)」へ変更します。式を直した後に［循環参照］の一覧をもう一度開き、別のセル番地が表示されたら同じ順番で確認します。一覧に表示される1件を直しても、ほかのワークシートや開いている別のブックに循環参照が残っている場合があるためです。
数式が長く、どの参照が一周しているのか読み取りにくいときは、式をいきなり作り直さず、次のトレースで関係を確認します。計算の役割を分けて読みやすくしたい場合は、修正後にLET関数で数式を整理する方法を使う選択肢もあります。
間接的な循環参照はトレース矢印でたどる
問題のセルを選択したまま、［数式］タブの［参照元のトレース］をクリックします。選択したセルの計算に使われるセルから矢印が表示されます。さらに前の段階を確認する場合は、同じボタンをもう一度クリックします。
反対に、そのセルを使って計算している場所を調べるときは［参照先のトレース］を使います。矢印を順にたどり、最初のセルへ戻る経路があれば、参照の輪を切る必要があります。どちらのセルが本来の入力値で、どちらが計算結果なのかを決めてから、不要な参照を削除するか正しい入力セルへ変更してください。
トレースの操作を詳しく確認したい場合は、トレース矢印で数式のつながりを確認する手順が役立ちます。別のシートやブックを示す黒い矢印が出たときは、参照先のブックも開いた状態で確認します。調査が終わったら［数式］タブの［矢印の削除］で表示を消せます。
反復計算を有効にして警告だけを消さない
［ファイル］タブの［オプション］から［数式］を開くと、反復計算を有効にする設定があります。ただし、これは循環参照を利用することを前提に計算を繰り返す機能です。通常の合計や参照の入力ミスを直す代わりに有効にすると、誤った数式が残っていても値が表示され、問題を見つけにくくなります。
意図して循環参照を使う計算モデルでない限り、反復計算は無効のままにし、参照範囲を修正します。また、IFERROR関数は通常のエラー値を別の表示に置き換える関数であり、循環参照そのものを直す機能ではありません。数式を修正した後にエラー表示を整える必要がある場合だけ、IFERRORでエラー表示を整える方法を確認してください。
ステータスバーと計算結果で修正を確認する
最後に［数式］タブの［エラーチェック］から［循環参照］を開き、セル番地が表示されないことを確認します。続けてステータスバーから「循環参照」の表示が消えているかを見ます。
表示が残る場合は、ほかのワークシートと、同時に開いている別のブックを順に確認してください。数式が複数セルを経由して自分へ戻っている場合は、1か所を直しても別の輪が残ることがあります。［循環参照］の一覧とトレース矢印を使い、表示が消えるまで残りのセルを特定します。
警告が消えたら、合計や集計の値が元データと合っているかを小さな範囲で確かめます。循環参照がなくなっただけでは、修正した参照範囲が正しいとは限りません。コピーとして保存した変更前のブックと数式を見比べ、必要な入力セルだけを参照していることまで確認して完了です。
この手順で使う数式監査機能は、Windowsデスクトップ版を前提にしています。Excel for the webやモバイル版ではトラブルシューティング用のコマンドが制限される場合があるため、［循環参照］やトレースのボタンが見つからないときは、Microsoft 365のデスクトップアプリでブックを開いて修正します。