Excelでセルに何らかの数式を設定していると、「循環参照」というメッセージが表示されることがある。この「循環参照」とは何なのか、対策と解消方法を解説する。
この記事は会員限定です。会員登録(無料)すると全てご覧いただけます。
対象:Office 2013/2016/2019/365
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は循環参照を使う繰り返し計算が行えるようになる。この設定を行うと、循環参照が発生したことを示すメッセージは表示されなくなるので注意してほしい。
一部の関数に関しては、自分を含むセル参照を引数にしても、循環参照にならないものがある。循環参照は、式の結果である「値」を参照しているときのみ発生する。例えば、セルの数式を参照する「FORMULATEXT」関数だ。
この関数は、数式そのものをテキストとして返すものであるため、循環参照にならない。具体的には、下画面のように2つのセルがあり、セル「B2」に「FORMULATEXT」関数(数式を文字列として返す関数)を使い、セル「B3」の数式を文字列として、その文字列の長さを「LEN」関数で求めている。セル「B3」は、「SUM」関数でセル「B2」の値を合計している。
このようにセル「B2」は、セル「B3」を参照しているが、「FORMULATEXT」関数を使っているため、循環参照にならない。しかし、セル「B2」を書き換えて「FORMULATEXT」関数を外すと循環参照が発生する。こうした循環参照にならない関数には、「ISFOMULA」関数(セルが数式かどうか)、「ROW」関数(セルの行番号を返す)、「COLUMN」関数(セルの列番号を返す)などがある。
ブック内で循環参照が発生すると、デフォルトでExcelウィンドウ下部にあるステータスバーに循環参照が発生していることを示すメッセージが表示されるようになっている。
Copyright© Digital Advantage Corp. All Rights Reserved.