【Excel】エクセルで「1つ以上の循環参照が発生しています」と表示される原因と確認方法
Excelで数式を入力した後に「1つ以上の循環参照が発生しています」と表示されると、どのセルが問題なのか分からず作業が止まりがちです。
循環参照とは、数式が自分自身のセルを直接または間接的に参照し、計算の終点がなくなっている状態を指します。
多くのケースでは、数式の参照範囲に数式を入力したセル自身が含まれていることが原因です。
この記事で分かるポイント
・循環参照が発生する代表的な原因
・エラーメッセージから問題のセルを探す方法
・数式の修正方法と反復計算を使う場合の注意点
売上集計、割合計算、在庫表、家計簿など、日常的な表でも循環参照は発生します。
表示の意味を理解し、参照元と参照先を順番にたどれば、落ち着いて修正できるでしょう。
エクセルの循環参照を確認する方法【数式タブからエラー箇所を探す】
それではまず、循環参照が発生しているセルを確認する方法について解説していきます。
| A | B | C |
|---|---|---|
| 1 | 項目 | 金額 |
| 2 | 売上 | 10000 |
| 3 | 経費 | 3000 |
| 4 | 利益 | =SUM(C2:C4) |
上の例ではC4に合計式を入れながら、合計範囲をC2からC4にしているため、C4が自分自身を参照しています。
循環参照の確認は、警告を閉じるだけでは終わらせず、数式タブのエラーチェックを使うことが重要です。
エラーチェックの循環参照メニュー
数式タブの数式の検証グループにあるエラーチェックを開き、循環参照に表示されるセル番地を確認しましょう。
表示されたセル番地をクリックすると、Excelが該当するセルへ移動します。
複数のシートに問題がある場合は、循環参照メニューに複数のセル番地が並ぶこともあります。
その場合は上から一つずつ選び、数式バーの内容と参照範囲を見比べてください。
【操作のポイント】エラーチェックの循環参照にセル番地が表示されない場合は、開いている別シートや別ブックも確認します。
ステータスバーと警告メッセージ
循環参照があるブックでは、Excel画面の左下にあるステータスバーへ循環参照の情報が表示される場合があります。
警告メッセージに出る「1つ以上」は、原因セルが一つとは限らないという意味です。
最初に見つけたセルだけを直しても、別の数式が残っていれば警告が続くかもしれません。
エラーメッセージは故障を示すものではなく、計算経路を確認してほしいというExcelからの通知です。
【操作のポイント】警告が出た直後に保存する前に、循環参照メニューで対象セルを確認しておくと修正漏れを防げます。
参照元のトレースによる数式の追跡
セル番地だけでは原因を判断できないときは、数式タブの参照元のトレースを使います。
対象セルを選択して参照元のトレースを実行すると、そのセルの計算に使われているセルへ矢印が表示されます。
続けて参照先のトレースも利用すると、選択中のセルがどの数式に影響するかを確認できます。
矢印を順番にたどると、最終的に元のセルへ戻ってしまう計算経路を見つけやすくなります。
【操作のポイント】トレースの矢印が多い表では、不要になったら矢印の削除を実行し、見やすい状態で次のセルを確認します。
循環参照が発生する主な原因【合計範囲とセル参照の重なり】
続いては、循環参照が発生する代表的な原因を確認していきます。
| セル | 入力内容 | 判定 |
|---|---|---|
| C5 | =SUM(C2:C4) | 正常 |
| C5 | =SUM(C2:C5) | 循環参照 |
数式の入力位置と参照範囲が重なることは、もっとも多い原因です。
合計セルがC5の場合
正しい数式は =SUM(C2:C4)
循環参照になる数式は =SUM(C2:C5)
SUM関数の範囲指定ミス
合計セルを表の末尾に置くとき、ドラッグ操作で範囲選択した結果、合計セル自身まで含めてしまうことがあります。
特に行や列を追加した直後は、数式の範囲が意図どおりに拡張されているか確認しましょう。
合計を入れるセルは、SUM関数で指定する開始セルと終了セルの外側に置くのが基本です。
【操作のポイント】数式を確定する前に、編集状態で色分けされる参照範囲を見て、数式セル自身が含まれていないか確認します。
割合計算での参照先の取り違え
構成比や達成率を計算するとき、分子と分母に同じセルを使うべき場面と、使ってはいけない場面があります。
たとえばC2に売上、C10に合計売上、D2に構成比を入力する場合は、D2へ =C2/$C$10 と入力します。
構成比の基本式
構成比 = 個別の金額 ÷ 合計金額
D2 = C2/$C$10
D2へ誤って =C2/D2 と入力すると、D2が自分自身を分母として参照するため循環参照になります。
割合計算では、分母に置く合計セルを絶対参照にしておくと、コピー時の参照ずれを防げます。
【操作のポイント】F4キーでドル記号を付け、コピーしても合計セルが移動しない数式に整えます。
複数セルをまたぐ間接的な参照
循環参照は、一つの数式だけで起きるとは限りません。
たとえばC2が =C3+100、C3が =C2-100 という数式になっていると、二つのセルが互いを参照します。
さらに別シートを経由するケースでは、見た目だけで関係を見抜きにくくなります。
数式をコピーした後に参照セルが変わっていないかを確認すると、間接的な循環参照を早期に見つけられます。
【操作のポイント】関係するセルが複数ある場合は、数式バーを見ながら参照元のトレースを使い、矢印の循環を確認します。
数式を修正して循環参照を解消する手順【参照範囲の見直し】
続いては、見つけた循環参照を修正する手順を確認していきます。
| A | B | C |
|---|---|---|
| 1 | 月 | 売上 |
| 2 | 4月 | 120000 |
| 3 | 5月 | 150000 |
| 4 | 合計 | =SUM(C2:C4) |
修正では、数式そのものを消す前に、どの計算結果が必要なのかを整理することが大切です。
合計、差額、割合などの目的が分かれば、参照すべきセルも自然に決まります。
SUM関数の終了セルの修正
合計セルC4であれば、数式バーの =SUM(C2:C4) を =SUM(C2:C3) に書き換えてEnterキーを押します。
これでC4は売上と経費だけを計算し、自分自身を参照しなくなります。
合計の式は、合計セルの一つ上までを終了位置にするという考え方で確認すると分かりやすいでしょう。
【操作のポイント】範囲をマウスで選び直すときは、数式を編集している途中でC4をクリックしないよう注意します。
コピーした数式の参照先の確認
数式を下方向や右方向へコピーすると、相対参照のセル番地はコピー先に合わせて変化します。
そのため、元の数式が正しくても、コピー後に合計セルや計算セルを参照してしまう場合があります。
コピー前の式がD2 = C2/$C$10 の場合、D3へコピーすると D3 = C3/$C$10 になります。
合計セルC10は絶対参照のため固定され、個別金額だけが行に合わせて変わります。
数式をコピーした後は、先頭セルだけでなく末尾セルの数式も確認する習慣が有効です。
【操作のポイント】Ctrlキーを押しながら数式を表示する切り替えを使うと、計算結果ではなく数式そのものを一覧で確認できます。
数式を分けて計算経路を整理
長い数式に多くの計算を詰め込むと、参照関係が複雑になり、誤ったセルを指定しやすくなります。
途中の計算結果を補助列へ分けると、どの値を使っているかが見えやすくなります。
たとえば値引き後金額、消費税、請求額を別々の列で計算すれば、最終金額が自分自身を参照する事故を避けやすくなります。
売上金額 C2
値引き額 D2
請求額 E2 = C2-D2
【操作のポイント】計算を分けると表の列数は増えますが、検証しやすくなり、後から修正する人にも親切です。
反復計算を使う場合の設定と注意点
続いては、意図して循環参照を利用する反復計算について確認していきます。
| 用途 | 判断 |
|---|---|
| 誤って合計セルを範囲に含めた | 数式を修正 |
| 利息や残高を繰り返し計算したい | 反復計算を検討 |
通常の集計表では、循環参照は修正すべきエラーです。
一方で、計算結果を再び計算条件に使う財務モデルなどでは、反復計算を意図的に有効にすることがあります。
反復計算は循環参照を自動的に正しくする機能ではなく、繰り返し計算を許可する設定です。
反復計算を有効にする場所
ファイルタブからオプションを開き、数式の項目で反復計算を有効にするチェックを確認します。
設定を有効にすると、最大反復回数と最大変更値を指定できます。
最大反復回数は計算を繰り返す上限であり、最大変更値は計算結果が十分に収束したと判断する差の大きさです。
【操作のポイント】設定を変更する前に、循環参照が業務上必要な計算か、単純な入力ミスかを必ず見分けます。
計算結果が安定しないケース
反復計算を有効にしても、数式の関係によっては結果が収束せず、値が大きく変動することがあります。
たとえば、前回の結果へ一定割合を加え続けるような式では、反復するほど値が増える可能性があります。
反復計算を使う場合は、想定される結果の範囲と計算ロジックを事前に確認することが不可欠です。
【操作のポイント】設定後に値が不自然なら、反復回数を増やす前に数式の意味と参照関係を見直します。
共有ファイルでの運用ルール
反復計算の設定はブックを使う人全員の結果確認に影響するため、共有ファイルでは特に注意が必要です。
設定を有効にした理由、対象となるセル、想定する計算結果をシート内に記録しておくとよいでしょう。
意図を知らない人が数式を編集すると、正しい循環参照と誤入力の区別がつきにくくなります。
【操作のポイント】反復計算を使うブックには、計算条件を説明する専用シートや注記を残しておくと管理しやすくなります。
循環参照を防ぐ数式作成のポイント
続いては、循環参照を予防する数式作成のポイントを確認していきます。
| 確認する場面 | 確認内容 |
|---|---|
| 数式入力時 | 数式セル自身を範囲に含めない |
| コピー後 | 相対参照と絶対参照の変化を見る |
| 行追加後 | 合計範囲の終点を見直す |
循環参照は、Excelの操作に慣れている人でも起こしやすい入力ミスです。
作表のルールを決めておくと、問題の発生率を下げられます。
合計行と入力行を分ける構成
データ入力行と合計行を明確に分けると、SUM関数の範囲を判断しやすくなります。
たとえば明細を2行目から20行目、合計を21行目と決めれば、合計式は =SUM(C2:C20) と一目で確認できます。
合計行に背景色や太い罫線を付けるだけでも、参照範囲の誤指定を防ぐ助けになります。
【操作のポイント】表の途中に小計を置く場合は、合計範囲が小計セルを二重に含まないようにします。
テーブル機能の集計行
Excelのテーブル機能を使うと、データ範囲の追加や削除に合わせて数式を管理しやすくなります。
テーブルデザインの集計行を利用すれば、通常のセル範囲を手で広げる作業を減らせます。
ただし、テーブル内の計算列でも参照先を誤れば問題は起こるため、数式の意味まで自動で保証されるわけではありません。
【操作のポイント】毎月行を追加する集計表では、テーブル化を検討すると範囲修正の手間を減らせます。
数式表示モードによる最終確認
数式タブの数式の表示を使うと、すべてのセルで計算結果の代わりに数式を確認できます。
値だけを見ていると気付きにくいセル参照も、数式一覧にすると不自然な参照が見つかりやすくなります。
完成前に数式表示モードで全体を見る作業は、循環参照だけでなく参照ずれの発見にも役立ちます。
【操作のポイント】確認後は数式の表示を元へ戻し、数値結果にエラー表示が残っていないかも確認します。
まとめ エクセルで「1つ以上の循環参照が発生しています」の原因と確認方法
Excelで「1つ以上の循環参照が発生しています」と表示される主な原因は、数式が自分自身のセルを直接または間接的に参照していることです。
最初は数式タブのエラーチェックから循環参照を選び、問題のセル番地を確認しましょう。
合計セルをSUM関数の範囲へ含めていないか確認することが、もっとも基本的で効果的な対処です。
割合計算では、分母に指定する合計セルの参照先を確認し、必要に応じて絶対参照を使います。
複数のセルをまたぐ場合は、参照元のトレースと参照先のトレースで数式の流れをたどると原因を見つけやすくなります。
反復計算は特殊な計算で必要になることがありますが、単純な入力ミスを隠してしまう場合もあるため、安易に有効化しないことが大切です。
数式を入力した後、コピーした後、行を追加した後に参照範囲を見直し、正確で管理しやすいExcelファイルを作成していきましょう。
