本文へスキップ

エクセルの循環参照が見つからない|警告が消えないときの探し方

ノートPCで表計算ソフトの表とグラフを見ながらキーボードを打つ手元

数式を入れてEnterを押した瞬間に「循環参照」の警告が出て、[OK]で閉じても、画面の左下に「循環参照」の文字が残ったまま。どのセルのせいなのか、見当もつかないことはありませんか?

循環参照は、数式が自分自身の計算結果を材料に使ってしまっている状態です。場所は[数式]タブの[エラー チェック]の▼から[循環参照]を開くとセル番地で分かり、左下に番地が出ていないときは、ほかのシートに切り替えて同じ一覧を見ます。

1つ直しても警告が残るなら、別のセルを経由した輪になっているサイン。[数式]タブの[参照元のトレース]で矢印を出して参照をたどると、その輪が見えてきますよ。

循環参照のセルは[エラー チェック]の▼から探す

まずはExcelに場所を教えてもらいましょう。

警告を閉じたあと、画面の左下(ステータスバー)を見てください。「循環参照」の文字の横にセル番地が出ていれば、そこが問題のセルの1つです。

左下の「循環参照」の表示ごとに、次に何をするかを示した流れ図
左下の表示ごとに、次に見る場所をまとめた図です。

番地が出ていない、または複数ありそうなときは、リボンから一覧を開きます。

  1. [数式]タブを開く
  2. [ワークシート分析]グループの[エラー チェック]の右にある▼をクリックする
  3. [循環参照]にマウスポインターを合わせる
  4. 右に出たセル番地をクリックする

クリックすると、そのセルへカーソルが移動します。あとは数式バーで式を読み、自分自身を参照していないかを見ていきます。

私は警告が出たら、リボンより先に左下のステータスバーを見ます。番地が出ていれば、そのまま名前ボックスに打ち込んで飛べるので、▼を開くより一手少なく済むんです。

参考:Microsoft サポート(循環参照を削除または許可する)

ステータスバーに「循環参照」とだけ出て番地がないとき

左下に「循環参照」の文字はあるのに、セル番地が付いていない。これは、ループがいま開いているシートではなく、別のシートにあるときの表示です。

ステータスバーが番地を出すのは、開いているシートの分だけ。ですから、シートを切り替えながら確かめます。

  1. 画面下のシート見出しを、左から順に1枚ずつクリックする
  2. シートを切り替えるたびに、左下に番地が出ていないかを見る
  3. 番地が出たシートで、[数式]→[エラー チェック]の▼→[循環参照]を開いて一覧を見る

全部のシートを回っても番地が出ないなら、非表示のシートを疑いましょう。シート見出しを右クリックして[再表示]を選ぶと、隠れているシートの一覧が出てきます。

シートをまたぐ循環参照は、例えばこんな形です。シート「集計」のB2が「明細」の合計を参照していて、その「明細」のどこかで「集計」のB2を使っている。どちらのシートの式も、単独で見ると正しそうに見えるのがやっかいなところですね。

参考:Excelの循環参照を解除する手順とセルが特定できない時の確認方法

見つけたセルの数式は、この3つの形を疑う

番地が分かったら、そのセルの式を読みます。循環参照になる式は、だいたい次のどれかです。

合計の範囲に、合計のセル自身が入っている

一番多いのがこれ。A10に合計を出したいのに、式が =SUM(A1:A10) になっていて、範囲の終わりがA10自身を含んでいる形です。

直し方は、範囲を1つ手前で止めるだけ。=SUM(A1:A9) にすれば警告は消えます。

私も、表の途中に行を足したあと、SUMの範囲をドラッグで広げ直していて、1行下まで引っぱってしまったことがあります。合計のセルに0が出て、一瞬、表ごと壊したのかと青くなりました。範囲の青い枠をよく見たら、合計のセルまで囲っていたんです。

2つのセルがお互いを参照している

A1に =B1+1、B1に =A1+1 のように、2つのセルが相手の結果を待っている形です。どちらも相手が決まらないと計算できません。

実務で起きやすいのは、税抜きと税込みの欄。税込みのC2に =B2*1.1 と入れたあと、税抜きのB2にも =C2/1.1 と逆算の式を入れてしまうと、この形になります。

どちらか一方は、手で入れる数字(値)にしましょう。入力する側と計算する側を決めれば、輪はほどけます。

別のセルや別のシートを回って戻ってくる

A1→B5→D2→A1のように、3つ以上のセルを経由して自分に戻ってくる形もあります。一覧に出たセルの式だけを見ても、どこが悪いのか分からないのはこのパターン。次の節の矢印でたどるのが近道です。

1つ直したのにまだ警告が出るときは、矢印で参照をたどる

一覧のセルを直したのに、左下の「循環参照」が消えない。それは、輪がまだどこかでつながっているサインです。

こういうときは、[参照元のトレース]で、そのセルが何を材料にしているかを矢印で表示させます。Excelの版によっては[参照元の追跡]という名前になっています。

  1. [循環参照]の一覧に出たセルを選ぶ
  2. [数式]タブの[参照元のトレース]をクリックする
  3. 青い矢印の根元にあるセルを選び、もう一度[参照元のトレース]をクリックする
  4. 矢印が最初のセルに戻ってくるまで、3を繰り返す

矢印が一周したら、その輪の中のどこか1か所を直せば止まります。どこを切るかは、表の中で「本来は入力する欄」がどれかで決めましょう。

参照元が別のシートにあると、矢印は点線になり、先に小さな表のマークが付きます。点線の矢印をダブルクリックすると[ジャンプ]の画面が開くので、移動先を選んで[OK]を押せば、そのシートのセルへ飛べます。

もう1つ、気をつけたいのが警告の出方です。Microsoftの説明では、警告のメッセージが出るのはその作業中に初めて循環参照ができたときで、2つ目以降は同じ警告が出ないことがあります。警告が出なくなったことは、直った証拠になりません。終わりの目安は、左下の「循環参照」の文字が消えたかどうかです。

0が表示されているセルが怪しいのに一覧に出てこないときは、そのセルを選んで数式バーの式をクリックし、Enterを押してみてください。計算がやり直されて、循環参照なら一覧に上がってきます。

[反復計算を行う]にチェックを入れて警告を黙らせない

検索すると、[ファイル]→[オプション]→[数式]にある[反復計算を行う]にチェックを入れる方法も出てきます。たしかに、これで警告は出なくなりますね。

ただ、これは循環参照を「直す」設定ではありません。輪になった式を、決めた回数だけぐるぐる計算させる設定です。既定では[最大反復回数]が100回、[変化の最大値]が0.001で、そこで計算を止めます。

つまり、合計の範囲に自分が入ったままの式も、エラーにならずに何かしらの数字を出してしまうんです。間違った合計が、警告なしで表に残る…これは避けたいところ。Microsoftも、ほとんどのワークシートではこの設定をオフのままにするよう勧めています。

人から受け取ったブックで警告が一度も出ないのに数字が合わないときは、このチェックが入っていないかを見てみましょう。チェックを外してから怪しいセルでEnterを押し直せば、隠れていた循環参照が警告として出てくるはずです。

反復計算を使うのは、ローンの残高のように、わざと前の結果を使って計算を繰り返す表を作るときだけで十分だと思います。

まとめ

循環参照の警告は[OK]で閉じても直っていませんし、同じ作業中にできた2つ目以降の循環参照では、警告が出ないこともあります。

直すのは一覧に出たセルの数式で、合計の範囲から合計のセル自身を外すか、参照し合う2つの式の片方を手入力の値に変えます。

左下に番地が出ないのは、ループが別のシートにあるとき。シートを1枚ずつ切り替えれば、番地の出るシートが見つかります。1つ直しても残るなら、一覧のセルで[参照元のトレース]を押し、矢印の根元(材料になっている側)のセルで押し直すのを繰り返すと、輪の途中で経由しているセルが分かります。

[反復計算を行う]は警告を消すだけの設定なので、意図して計算を繰り返す表でなければ、チェックは外したままにしておくといいと思います。

どのシートを開いても左下に「循環参照」の文字が出なくなれば、そのブックの循環参照は全部直っています。

この記事をシェア

コメントを書く

投稿は管理人が確認したあとで表示されます。