エクセルで数式を入力した直後に循環参照の警告メッセージが表示されると、何を直せばよいのか迷うものです。
循環参照は、計算式が自分自身のセルへ戻ってしまい、通常の計算順序では答えを確定できない状態を指します。
単純な入力ミスで起こる場合もあれば、ローン計算や目標値の計算で意図的に反復計算を使う場合もあります。
循環参照は原則として数式の参照先を修正して解消します。
ただし、計算の目的によっては反復計算を許可して利用するケースもあります。
まずは警告の場所を確認し、数式がどのセルへ戻っているかをたどることが大切です。
この記事では、循環参照の意味、発生原因、場所の探し方、削除できないときの確認点、反復計算の設定まで順に解説します。
エクセルの循環参照を修正する方法
それではまず、警告メッセージが出た循環参照を修正する基本操作について解説していきます。
| A | B | C |
|---|---|---|
| 1 | 売上 | 税額 |
| 2 | 10000 | =B2+C2 |
上の例では、C2の数式がC2自身を参照しているため、循環参照になります。
警告メッセージのセルを確認する手順
循環参照の警告が表示されたら、慌てて数式を削除する前に、どのセルが対象になっているかを確認しましょう。
通常は数式を確定した直後に警告が表示され、ステータスバーにも循環参照という表示が出ます。
表示されたセル番地がある場合は、そのセルをクリックして数式バーを確認してください。
数式内に自分自身のセル番地が含まれていないか、または別のセルを経由して戻っていないかを見ます。
たとえばC2に入力した数式が=B2+C2なら、C2が計算結果であると同時に計算材料にもなっています。
このような式は、加算する税額を求めたい意図でも、エクセルには最初のC2の値を決める材料がありません。
数式バーでは、計算したいセルそのものが参照範囲に混ざっていないかを最初に確認します。
自分自身への参照を外す修正方法
最も多い修正方法は、数式から自分自身のセル参照を外し、正しい元データのセルへ置き換える方法です。
先ほどの例で税額を10パーセントにしたいなら、C2には=B2*10%と入力します。
C2 = B2 × 10%
売上が10000なら、税額は1000となります。
数式を直すときは、計算結果を置くセルと計算の材料になるセルを分けることが基本です。
合計セルで起きた場合は、SUM関数の範囲に合計セル自身が含まれていないかも確認しましょう。
たとえばB10に=SUM(B2:B10)と入力すると、B10を集計範囲に含めてしまいます。
正しくは=SUM(B2:B9)として、明細行だけを範囲に指定します。
数式を削除せずに元へ戻す考え方
循環参照を見つけても、数式をすぐ削除すると、作成途中の計算ロジックまで失うかもしれません。
まず数式バーの内容をコピーしてメモに残し、どのセルを参照するつもりだったかを整理します。
参照先を選択するときは、数式を編集中に対象セルをクリックすると、セル番地が自動で入力されます。
手入力による番地の打ち間違いを減らせるため、特に複数シートにまたがる表では有効です。
数式を修正した後にEnterキーを押し、警告が消えたかを確認してください。
警告が残るなら、同じブック内の別セルにも循環参照が存在する可能性があります。
【操作のポイント】数式を消す前に内容を控え、参照範囲と計算結果のセルを分離します。
循環参照の原因と計算への影響
続いては、循環参照が発生する仕組みと、ブックの計算に及ぼす影響を確認していきます。
| セル | 入力内容 | 参照先 |
|---|---|---|
| B2 | =C2+100 | C2 |
| C2 | =B2*2 | B2 |
B2とC2が互いを参照すると、直接自分を参照していなくても循環参照になります。
直接参照による循環参照
直接参照は、数式を入力したセルが、その数式の参照先に含まれる状態です。
典型例は、D5に=D5+1、またはD5=SUM(D1:D5)のような式を入力するケースです。
入力範囲をドラッグした際に終点まで選択してしまうと、合計セルを含めるミスが起こりやすくなります。
集計式は明細の最終行までを指定し、集計結果の行を含めないようにしてください。
なお、オートSUMで提案される範囲も常に正しいとは限りません。
空白行や隣接する数値の配置によっては、意図しない範囲が選ばれることがあります。
複数セルを経由する循環参照
間接的な循環参照は、複数のセルが鎖のようにつながり、最終的に元のセルへ戻る状態です。
たとえばB2がC2を参照し、C2がD2を参照し、D2がB2を参照すると、3つのセルで循環します。
数式を一つだけ見ても原因が分からないため、参照元と参照先を順番に追う必要があります。
別シートへの参照、名前の定義、IF関数の分岐が混ざると、関係はさらに見つけにくくなります。
複雑なブックでは、計算用シートと入力用シートを分け、役割ごとのセルを明確にしておくと予防につながります。
警告を放置した場合の注意点
循環参照の警告を放置すると、表示される数値が期待した結果ではないまま保存されることがあります。
計算モード、反復計算の設定、前回保存時の値によって見かけ上は数値が表示されることもあります。
しかし、その数値が正しい保証はありません。
請求額、在庫数、予算、売上集計のように判断へ直結する表では、循環参照の放置は避けるべきです。
ファイルを他の人へ渡す場合も、開いた環境によって警告が再表示され、計算結果の信頼性が疑われます。
【操作のポイント】直接参照だけでなく、複数セルを経由して元のセルへ戻る関係も確認します。
循環参照の場所を探す手順
続いては、警告が出るのに原因のセルが見つからない場合の探し方を確認していきます。
| 操作場所 | 選択項目 | 確認内容 |
|---|---|---|
| 数式タブ | エラーチェック | 循環参照の一覧 |
循環参照は数式タブのエラーチェックから確認でき、一覧にセル番地が表示されます。
数式タブのエラーチェックを使う方法
リボンの数式タブを開き、エラーチェックの横にある下向きの矢印を選択します。
メニュー内に循環参照が表示されていれば、その右側または下位メニューに対象のセル番地が出ます。
セル番地を選択すると、該当するワークシートへ移動して対象セルがアクティブになります。
複数の循環参照があるときは、表示されたセルを一つ修正した後に、もう一度メニューを開いて次のセルを確認しましょう。
一つの修正で関連する循環が解消する場合もあるため、一覧にあったセルをすべて個別に削除する必要はありません。
最初の原因になっている数式を正しく修正することが重要です。
トレース矢印で参照関係をたどる方法
対象セルを選択した状態で、数式タブにある参照元のトレース、または参照先のトレースを使う方法も便利です。
参照元のトレースでは、現在のセルの計算に使われているセルを矢印で確認できます。
参照先のトレースでは、現在のセルがどの数式から使われているかを確認できます。
矢印をたどって元のセルに戻る経路があれば、それが循環参照のつながりです。
別シートのセルが関係している場合は、点線の矢印やシート移動の記号が表示されることがあります。
トレース矢印が多くなったら、矢印の削除を使って表示を整理すると見やすくなります。
見つからないときの確認箇所
エラーチェックに循環参照が出ないのに警告だけ残る場合は、別シートを確認してください。
非表示のシート、グループ化されたシート、名前の定義に含まれる参照式が原因となることもあります。
数式タブの名前の管理を開き、参照範囲に不自然なセル参照がないかを見るのも有効です。
また、すでに反復計算が有効であると、警告が目立たず、古い数式が残っていることがあります。
循環参照が見つからないときは、現在のシートだけで判断せず、全シート、名前の管理、外部参照の順で確認します。
【操作のポイント】エラーチェックの循環参照一覧からセルへ移動し、トレース矢印で参照の輪を確認します。
反復計算の許可と設定方法
続いては、意図的な循環参照を計算したい場合に使う反復計算の設定を確認していきます。
| 項目 | 設定例 | 意味 |
|---|---|---|
| 最大反復回数 | 100 | 計算を繰り返す上限 |
| 変化の最大値 | 0.001 | 計算を止める差の基準 |
反復計算は、数値が一定の範囲に落ち着くまで計算を繰り返すための機能です。
反復計算が必要になる計算例
反復計算は、利息が残高に応じて変わり、その利息自体も残高へ影響するような計算で使われます。
手数料込みの目標金額、税引後の利益率、借入残高に連動する利息などが代表例です。
たとえば、残高から利息を計算し、利息を加えた後の残高を再計算するモデルでは、単純な一方向の数式では表現しにくい場合があります。
反復計算は入力ミスを隠す設定ではなく、循環する計算モデルを収束させるための設定です。
目的が明確でないまま有効にすると、誤った数式でも警告が出にくくなるため注意してください。
Excelのオプションで許可する手順
Excelのファイルタブからオプションを開き、左側のメニューで数式を選択します。
計算方法の設定にある反復計算を行うのチェックボックスをオンにすると、循環参照を反復計算できます。
チェックを入れた後は、最大反復回数と変化の最大値を計算モデルに合わせて設定します。
最大反復回数は試行回数の上限であり、数値を大きくすると計算時間が長くなる可能性があります。
変化の最大値は、前回の計算結果との差がどの程度まで小さくなれば終了と判断するかの基準です。
設定後に確認したい計算結果
反復計算を許可した後は、表示された値だけで正しいと判断せず、計算結果の妥当性を確認します。
入力値を少し変えたときに結果が不自然に大きく変わらないか、期待する方向へ動くかを試してください。
計算が収束しない場合、最大反復回数まで繰り返しても数値が安定しないことがあります。
反復計算の結果は、設定した回数と誤差の基準に影響されるため、重要な帳票では計算根拠を残しておくと安心です。
反復計算を使う数式では、初期値、最大反復回数、変化の最大値、想定する結果の範囲をセットで管理します。
【操作のポイント】反復計算は必要なモデルだけで有効にし、設定後は数値が収束しているかを確認します。
削除できない循環参照の対処
続いては、数式を直したつもりでも警告が消えない、または削除できない場合の対処を確認していきます。
| 症状 | 主な確認先 | 対処 |
|---|---|---|
| 警告が消えない | 別シート | エラーチェックを再実行 |
| セルを変更できない | シート保護 | 保護状態を確認 |
削除できない場合は、循環参照そのものではなく、編集を制限する設定が原因のこともあります。
シート保護と共有設定の確認
セルを選択できても数式を変更できない場合は、シート保護が有効になっている可能性があります。
校閲タブからシート保護の状態を確認し、編集権限がある場合だけ保護の解除を検討してください。
パスワードが設定されているシートは、作成者や管理者へ確認する必要があります。
共有されたファイルでは、他の利用者が編集していることや、閲覧専用で開かれていることもあります。
権限がない状態で保護を回避しようとせず、正規の管理者へ依頼することが安全です。
別シートと外部参照の確認
循環参照の対象が別シートにあると、現在の画面だけでは原因を見落としがちです。
数式タブのエラーチェックを開き直し、一覧に表示されるセル番地をすべて確認しましょう。
数式内に角かっこを含む外部ブック参照がある場合は、参照先ファイルの更新状況も確認します。
外部参照が切れた後に手作業で数式を修正した結果、意図しない参照の輪ができることもあります。
修正後に警告が残るときは、同じシートだけでなく、別シート、非表示シート、外部参照を確認します。
計算モードと保存データの確認
計算方法が手動になっていると、数式を直しても画面上の値がすぐ更新されない場合があります。
数式タブの計算方法の設定を確認し、必要に応じて自動へ変更するか、再計算を実行してください。
また、古い計算結果が保存されたブックを開くと、見た目の数値と現在の数式が一致していないことがあります。
重要なファイルでは、別名でコピーを作成してから修正し、保存後に開き直して警告と数値を再確認するとよいでしょう。
警告が消えたことと、計算結果が正しいことは別の確認項目です。
【操作のポイント】保護、別シート、外部参照、計算モードを順番に確認し、修正後は再計算します。
まとめ エクセルの循環参照の修正と解除方法
エクセルの循環参照は、数式が自分自身、または複数セルを経由して自分自身へ戻ることで発生します。
最初に数式タブのエラーチェックから対象セルを探し、数式バーとトレース矢印で参照関係を確認しましょう。
多くの場合は、SUM関数の範囲から合計セルを外す、または自分自身のセル参照を正しい元データへ置き換えることで解消できます。
警告が消えない場合は、別シート、非表示シート、名前の管理、外部参照、シート保護を確認してください。
反復計算は、意図的に循環する計算モデルでだけ許可し、最大反復回数と変化の最大値を管理することが重要です。
循環参照への対処は、場所を特定する、参照の輪を断つ、必要なら反復計算を設定するという流れです。
数式の役割を入力、計算、集計に分けて設計すると、循環参照の予防にもつながります。
