【Excel】循環参照の探し方と直し方 | 合計範囲と参照のつながりを確認

「1つ以上の循環参照が発生しています」と表示されたら、数式が自分自身を直接、または別のセルを経由して参照しています。よくある例は、合計を入れるセルまでSUMの範囲に含めてしまうことです。警告を閉じても原因は残るため、対象のセルと数式を探して修正します。

表示された0や以前の計算結果だけから、原因や正しい答えを判断することはできません。Windowsのデスクトップ版Excelを中心に、エラーチェック、参照のトレース、修正後の確認までを順番に説明します。

1. 自分のセルを合計に含めていないか調べる

B2、B3、B4に100、200、300があり、B5を合計欄にするなら、B5の式は=SUM(B2:B4)です。誤って=SUM(B2:B5)とすると、計算中のB5自身を合計へ含めるため循環参照になります。この場合は終点をB4に直すことで、合計600を求められます。

【要点】循環参照は、答えを求めるためにその答えが必要になっている状態です。Excelの内部で必ず同じ処理エラーが起こり、常に0が返るといった説明で捉える必要はありません。数式の位置と、その式が読むセルの関係を見ます。

合計欄に式を入れる直前に、数値範囲を末尾まで選び過ぎていないかを確かめます。列全体を集計する=SUM(B:B)をB列内へ入れることも、自分を含む原因になります。合計を別の列に置く、または明細だけの範囲を指定するなど、表の配置から見直してください。

2. エラーチェックの一覧から対象セルへ移動する

警告を閉じてしまった場合も、数式タブから原因を探せます。まず変更前のファイルのコピーを残し、どこを直したか分かる状態にします。集計結果だけを値で上書きすると原因を隠すことになるため、式を確認してから修正します。

1. 「数式」タブで「エラーチェック」の矢印を開きます。
2. 「循環参照」を開き、表示されたセル番地を選びます。
3. 移動先の数式バーを読み、自分の番地が直接含まれていないか確認します。
4. 合計範囲や参照先を修正してEnterで確定します。
5. 再び「循環参照」の項目を確認し、残っている対象を順に調べます。

最初に出た番地だけがすべての原因とは限りません。一つ直すと別の循環が分かる場合もあるため、修正後の再確認が必要です。メニューが使えないときは、セル編集中ならEnterまたはEscで編集を終え、ワークシート上のセルを選び直します。Web版で分析コマンドが足りない場合は、デスクトップ版で開いて確認します。

3. ステータスバーは場所を探す手掛かりにする

画面下部のステータスバーに「循環参照」とセル番地が表示される場合があります。番地が分かれば、名前ボックスへ入力するかCtrl + Gのジャンプ機能で移動し、数式を確認できます。表示文字をクリックすれば必ず原因へ移動できる、と考える必要はありません。

循環参照が別のシートにあると、番地を伴わず「循環参照」とだけ表示される場合があります。現在のシートに見当たらないから解消済みとは判断せず、エラーチェックの一覧やシートを切り替えて確認します。複数ブックを開いているときは、警告が出たファイルも取り違えないようにします。

警告ダイアログは、循環参照を作るたびに必ず繰り返し表示されるわけではありません。警告が出なかったことより、残っている数式とステータスの確認を優先してください。反復計算を使うブックでは表示の前提も異なるため、後述の設定と作成者の意図を確かめます。

4. 別のセルを経由する循環は矢印で追う

A2に=B2+1、B2に=A2+1があると、A2には自分の番地が書かれていなくても循環します。A2はB2の答えを必要とし、B2もA2の答えを必要とするためです。セル単体の数式だけでなく、参照がどこへ戻るかを確認します。

「数式」の「参照元のトレース」は、選んだ式に値を渡すセルを調べるのに使います。「参照先のトレース」は、そのセルを利用する式を調べる機能です。矢印と数式バーを照合し、どこで元の計算へ戻るかを追います。別シートなど画面上だけで追いにくい場合は、番地とシート名を控えて移動します。

修正では、入力値と計算結果を分けます。例のB2が本来手入力欄なら、B2を正しい入力値へ戻すことが候補です。どちらの式が正しいかはExcelが決めるものではないため、計算の目的を確認せず、片方を適当な値に置き換えないでください。

5. 反復計算を警告消しのためにオンにしない

反復計算は、循環する式を一定の条件で繰り返し計算する設定です。意図したモデルで使う場合がありますが、誤って自分を含めた合計式を正す機能ではありません。オンにして警告が出なくなっても、計算の目的が正しくなるわけではありません。

Windowsでは「ファイル」→「オプション」→「数式」に「反復計算を行う」があります。既存ブックで有効なら、まず設定値と作成目的を確認します。最大反復回数や変化量は終了条件に関係し、設定を変えると結果も変わり得ます。収束せず値が増え続ける式や、値が往復する式もあります。

通常の集計表で誤設定が疑われる場合は、コピー上で設定を見直して循環する式を修正します。作成者が意図して使っているブックを、説明なしにオフへ変えるのも適切ではありません。反復を必要とする計算なら、期待する結果と許容差を作成者に確認し、一般的な合計表の修正とは分けて扱います。

6. 修正後は警告の消失と計算結果の両方を確かめる

循環参照の表示がなくなったら、修正したセルの答えを別の方法で照合します。100、200、300の例なら600であることを確認します。実際の表では、一つの部署や一日の明細など小さな範囲を選ぶと検算しやすくなります。

次に、その結果を利用する総合計やグラフも確認します。循環参照があれば無関係なセルの計算まで必ずすべて止まる、という説明は正確ではありません。ただし、影響を受ける計算結果を信用してよいかは別なので、参照先のトレースなどで利用箇所を調べます。

警告は消えたのに結果が変わらない場合は、「計算方法の設定」が手動になっていないかも確認してください。元の式が分からなくなったら、直前の修正を取り消すか保存しておいたコピーと比較します。原因を隠すためにIFERRORで0にしたり、全数式を値へ置き換えたりせず、関係を修正した上で保存します。

7. まとめ:入力と集計が一方向につながる表にする

循環参照を見つける基本は、エラーチェックで対象へ移動し、直接自分を読んでいるか、別の式を経由して戻ってくるかを調べることです。一つ直すたびに再確認し、最後に小さな明細の合計と最終結果を照合します。

再発しやすい表では、入力欄と結果欄を見出しや位置で分け、明細の中に合計セルを混ぜない配置にします。行を追加した後には、合計範囲の最後が明細なのか合計なのかを確かめてください。式を短く分けて補助セルに置く方法も、参照のつながりを読みやすくします。

Microsoftの循環参照の削除・許可に関する説明には、警告やステータスバーの動作も掲載されています。警告を出なくすることを目的にせず、計算のつながりと期待する答えが一致する状態へ戻しましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】