【Excelトラブル対策】うっかり発生する「循環参照」とは？　その対策と解消方法：Tech TIPS

Excelでセルに何らかの数式を設定していると、「循環参照」というメッセージが表示されることがある。この「循環参照」とは何なのか、対策と解消方法を解説する。

連載目次

意外と簡単に起きる「循環参照」
多くの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)」のような自分自身を参照するような数式があれば、循環参照が発生する。

「循環参照」とは
数式で自分が書き込まれているセルを参照すると「循環参照」となる。画面では、SUM関数で自身を含むセルが参照されている。循環参照が発生すると、このようにメッセージが表示される。

　循環参照が起こったセルの再計算は停止し、セルの値は「0（ゼロ）」になることがほとんどだ。多くの場合、これで数式に問題があることに気が付く。しかし循環参照は、他のセルからの参照で発生することもあり、必ずしも結果が「0」になるとは限らず、循環参照が発生する前の値が残ることがある。このため、一見、ちゃんと計算が行われているように見えることがある。

「反復計算を行う」を有効にすると循環参照のメッセージが表示されない

　意図的に循環参照を使って繰り返し計算を行うことも可能だ。［ファイル］タブの［オプション］を選択して、［Excelのオプション］ダイアログを表示し、左ペインで［数式］を選び、右ペインの「計算方法の設定」欄にある「反復計算を行う」を「オン」にして、「最大反復回数」を設定することで、Excelは循環参照を使う繰り返し計算が行えるようになる。この設定を行うと、循環参照が発生したことを示すメッセージは表示されなくなるので注意してほしい。

反復計算を有効にする（1）
［ファイル］タブを開き、［オプション］を選択する。

▼

反復計算を有効にする（2）
［Excelのオプション］ダイアログの左ペインで［数式］を選択し、右ペインの「反復計算を行う」にチェックを入れて、「最大反復回数」で、計算の繰り返し回数を指定する。ここをチェックすると、循環参照のメッセージが表示されなくなるので注意してほしい。

一部の関数は自分を含むセルを参照しても循環参照にならない

　一部の関数に関しては、自分を含むセル参照を引数にしても、循環参照にならないものがある。循環参照は、式の結果である「値」を参照しているときのみ発生する。例えば、セルの数式を参照する「FORMULATEXT」関数だ。

　この関数は、数式そのものをテキストとして返すものであるため、循環参照にならない。具体的には、下画面のように2つのセルがあり、セル「B2」に「FORMULATEXT」関数（数式を文字列として返す関数）を使い、セル「B3」の数式を文字列として、その文字列の長さを「LEN」関数で求めている。セル「B3」は、「SUM」関数でセル「B2」の値を合計している。

循環参照にならない関数（1）
循環参照を行っても「循環参照」として認識されない関数もある。例えば、「FORMULATEXT」関数は、指定したセルの数式を文字列として返すものであるため、循環参照にならない。

　このようにセル「B2」は、セル「B3」を参照しているが、「FORMULATEXT」関数を使っているため、循環参照にならない。しかし、セル「B2」を書き換えて「FORMULATEXT」関数を外すと循環参照が発生する。こうした循環参照にならない関数には、「ISFOMULA」関数（セルが数式かどうか）、「ROW」関数（セルの行番号を返す）、「COLUMN」関数（セルの列番号を返す）などがある。

循環参照にならない関数（2）
「FORMULATEXT」関数を外してしまうと、循環参照になってしまう。

循環参照が発生した場合の表示

　ブック内で循環参照が発生すると、デフォルトでExcelウィンドウ下部にあるステータスバーに循環参照が発生していることを示すメッセージが表示されるようになっている。