【Excelトラブル対策】うっかり発生する「循環参照」とは？ その対策と解消方法：Tech TIPS
Excelでセルに何らかの数式を設定していると、「循環参照」というメッセージが表示されることがある。この「循環参照」とは何なのか、対策と解消方法を解説する。
対象：Office 2013／2016／2019／365
意外と簡単に起きる「循環参照」
多くのExcel関係の文書や記事には、「循環参照エラー」と表記されることが多い。しかし、循環参照はエラーではなく、れっきとしたExcelの計算方法の1つだ。とはいえ、現在では使う意味が薄れてきており、間違いの元になりやすいため、循環参照が起こるとメッセージを表示するようになっている。
SUM関数の引数に自分自身のセルを含むと「循環参照」というメッセージが表示される。この「循環参照」は、「Microsoft Excel（エクセル）」で比較的よく見かけるメッセージの1つだ。簡単な式であっても、セル範囲指定を間違うなどすると、表示されてしまうからだ。自由にセル範囲を指定できるExcelの数式では、循環参照が発生することを防ぐことはできない。
実は、循環参照は、「エラー」ではない。もともと循環参照は、Excelで関数などから構成される数式では計算できない「繰り返し」計算を行うために利用されてきた方法だからだ。例えば、1から100までの和を求める場合、反復回数を100回と設定しておき、セル「A2」に「=A1+A2」という数式を入れ、セル「A1」に「=A1+1」という数式を入力すると、セル「A2」に計算結果の「5050」が表示される。
しかし、Excelの関数は初期の頃に比べると充実し、また、VBAを使って繰り返し計算を行う関数を定義することも可能になったため、現在では循環参照を使う意味は薄れている。
過去に作られたExcelのシートや数式の互換性のため、循環参照を完全に禁止することはできず、現状では、循環参照に対する設定（後述）と、発生時にメッセージを表示するという妥協点に落ち着いている。新規作成時には、メッセージで循環参照の対応を促し、過去に作成された循環参照による繰り返し計算は、設定で有効にしておくことができる。
このような経緯から、循環参照が発生しても、常にメッセージが表示されるとは限らない。シート内に既に循環参照があった場合、これをコピーしたときなど、循環参照が発生したことを示すメッセージが出ないことがある。今回は、こうした循環参照への対応方法を解説する。
そもそも循環参照とは？
循環参照は、簡単にいうと、数式で自分が書き込まれているセルを参照することで発生する。例えば、セル「B3」に「=SUM(B1:B3)」のような自分自身を参照するような数式があれば、循環参照が発生する。
循環参照が起こったセルの再計算は停止し、セルの値は「0（ゼロ）」になることがほとんどだ。多くの場合、これで数式に問題があることに気が付く。しかし循環参照は、他のセルからの参照で発生することもあり、必ずしも結果が「0」になるとは限らず、循環参照が発生する前の値が残ることがある。このため、一見、ちゃんと計算が行われているように見えることがある。
「反復計算を行う」を有効にすると循環参照のメッセージが表示されない
意図的に循環参照を使って繰り返し計算を行うことも可能だ。［ファイル］タブの［オプション］を選択して、［Excelのオプション］ダイアログを表示し、左ペインで［数式］を選び、右ペインの「計算方法の設定」欄にある「反復計算を行う」を「オン」にして、「最大反復回数」を設定することで、Excelは循環参照を使う繰り返し計算が行えるようになる。この設定を行うと、循環参照が発生したことを示すメッセージは表示されなくなるので注意してほしい。
反復計算を有効にする（2）
［Excelのオプション］ダイアログの左ペインで［数式］を選択し、右ペインの「反復計算を行う」にチェックを入れて、「最大反復回数」で、計算の繰り返し回数を指定する。ここをチェックすると、循環参照のメッセージが表示されなくなるので注意してほしい。
一部の関数は自分を含むセルを参照しても循環参照にならない
一部の関数に関しては、自分を含むセル参照を引数にしても、循環参照にならないものがある。循環参照は、式の結果である「値」を参照しているときのみ発生する。例えば、セルの数式を参照する「FORMULATEXT」関数だ。
この関数は、数式そのものをテキストとして返すものであるため、循環参照にならない。具体的には、下画面のように2つのセルがあり、セル「B2」に「FORMULATEXT」関数（数式を文字列として返す関数）を使い、セル「B3」の数式を文字列として、その文字列の長さを「LEN」関数で求めている。セル「B3」は、「SUM」関数でセル「B2」の値を合計している。
循環参照にならない関数（1）
循環参照を行っても「循環参照」として認識されない関数もある。例えば、「FORMULATEXT」関数は、指定したセルの数式を文字列として返すものであるため、循環参照にならない。
このようにセル「B2」は、セル「B3」を参照しているが、「FORMULATEXT」関数を使っているため、循環参照にならない。しかし、セル「B2」を書き換えて「FORMULATEXT」関数を外すと循環参照が発生する。こうした循環参照にならない関数には、「ISFOMULA」関数（セルが数式かどうか）、「ROW」関数（セルの行番号を返す）、「COLUMN」関数（セルの列番号を返す）などがある。
循環参照が発生した場合の表示
ブック内で循環参照が発生すると、デフォルトでExcelウィンドウ下部にあるステータスバーに循環参照が発生していることを示すメッセージが表示されるようになっている。
入力時に循環参照が発生したが、後で直すつもりでメッセージボックスを閉じても、ここに表示が出ているため循環参照が存在していることが常に把握できる。なお、この表示は、ステータスバーの設定では、「オフ」にすることはできない。ステータスバーに関してはTech TIPS「Excelのステータスバーで素早く合計などを確認する」を参照してほしい。
ステータスバーに「循環参照」と表示されているとき、その右側にセルアドレスが表示されることがある。これは、アクティブになっているExcelウィンドウで開いているシートに循環参照が起こっていることを示す。
また、開いている複数のブックのうち、現在アクティブなウィンドウに表示されていないブックやシートに循環参照が発生している場合は、セルアドレスが表示されない。
循環参照で間違ったセルアドレスが表示されることもある
このステータスバーの表示は、基本的には正しいのだが、他のシートで循環参照が発生しているとき、表示中のシートに前記の循環参照にならない関数などが使われていると、ここに間違ったセルアドレスが表示されることがある。この不具合は、かなり昔からあるもので、最新のMicrosoft 365版Excelでも発生する。
循環参照ではないのにステータスバーに「循環参照」と表示されるケース（1）
循環参照が含まれるブックで、「FORMULATEXT」関数のような循環参照にならない関数を別のシートで使うと、ステータスバーの循環参照のセルアドレスが間違って表示されることがある。
循環参照のセルに矢印が表示されるとき
Excelのバージョンによっては、循環参照が発生したとき、セルに矢印が表示されることがある。これは、「トレース矢印」と呼ばれ、数式の参照関係を示すものだ。Excelでは条件により、自動的に表示されることがあるが、必ずしも全ての循環参照に表示されるわけではない。
トレース矢印は、［数式］タブにある「ワークシート分析」グループの［トレース矢印の削除］コマンドで消すことが可能だ。なお、循環参照で自動的に表示されたトレース矢印は、循環参照が解消されると自動的に消える。
循環参照の発見方法
循環参照を解決する確実な方法は、［数式］タブにある「ワークシート分析」グループの［エラーチェック］−［循環参照］を使って、循環参照が発生しているセルを確認する方法だ。この方法では、確実に循環参照が発生しているセルを確実に選択できる。
ただし、複数箇所で循環参照が発生している場合、1カ所を解決した後、ここに次の循環参照の位置が表示される。つまり、エラーチェックで発生位置を特定し、数式などの書き換えを行い、ステータスバーから循環参照の表示が消えるまで作業を繰り返す必要がある。
循環参照が発生しているセルを確実に確認する
循環参照を確実に修正するには、［数式］タブにある［エラーチェック］を使う。ここにある［循環参照］には、循環参照が発生しているセルアドレスが表示され、クリックで該当のセルを確認、選択が行える。循環参照があるブックが開いていれば、他のブックウィンドウからでも利用できる。
ここには、同時に複数のセルが表示されることがある。これらは、1つの循環参照に関連する複数のセルを表す。ここに表示されているセルのうち1つの数式を見ても、循環参照しているように見えない場合、別のセルを調べてみる必要がある。
そのようなときには、数式の参照先や参照元のセルへ移動できるショートカットキーが役に立つ。現在のセルに入力されている数式が参照しているセルには、［Ctrl］＋［[］キーで、逆に現在のセルを参照している数式を持つセルには［Ctrl］＋［]］キーでそれぞれ移動できる。
循環参照の解消を繰り返し、ステータスバーの「循環参照」の表示が消えれば、開いているブック内には、循環参照がなくなった状態だ。このとき、前記の［エラーチェック］−［循環参照］はグレーアウトした状態に戻る。
循環参照は、かつて1つの計算方法であったため、エラーと表示されるものの、数式エラーなどと違い、セル表示が変わらないなど、存在が許容されている。また、入力時にはメッセージボックスによる警告だけなので、無意識に［OK］ボタンをクリックしてしまい、後から循環参照に気が付くこともある。こうした循環参照の解消には、［数式］タブにある［エラーチェック］が確実な方法だ。
Copyright© Digital Advantage Corp. All Rights Reserved.