上のような画像が出てきて、なんだかよくわからないけど、怖いからとりあえず”OK “を押しました、、なんて声をよく聞きます。
上のウィンドウが警告している循環参照とは一体どのような状態なのか。それを知ることで、ウィンドウが出てきても慌てずに対処することができます。
循環参照の定義
循環参照の定義は、ウィンドウ上に書いてあります。
“循環参照とは、数式が直接的または間接的に自身のセルを参照している状態を指します。これにより、計算が正しく行われない可能性があります。”
つまり、計算がループしてしまっていて、計算できない状態のことです。以下で具体的に例を挙げて、どのような状態のことを指すのか説明していきます。
循環参照の例
今回は以下の商品リストを例に説明します。
りんご・バナナ・チョコという商品に対して、原価・売価・利益・利益率のデータが投入されるリストです。E列とF列には以下のように数式が投入されています。
この時、バナナのデータを投入している時に、売価を”200″と投入します。続いて、原価の欄には本来絶対値を投入する想定になっていますが、ここで”売価と利益率のデータがあるのだから、原価=売価x(1-利益率)だ”ということでC5セルに”=D5(1-F5)”と投入したとします。
すると、この段階で上の循環参照を知らせるウィンドウが出てきて、OKを押すと以下のように循環参照を知らせる青い線が表示されます。
なぜ循環参照が起きてしまうのか、わかりますでしょうか？
E5セル(バナナの利益)では、”=D5-C5″、つまりバナナの原価と売価が入力されて初めて利益が計算されるようになっています。またF5セル(バナナの利益率)では、”=E5/D5″、つまりバナナの売価と利益が入力されて初めて利益率が計算されるようになっています。それに対して上ではC5セル(バナナの原価セル)に”=D5(1-F5)”と投入しました。これは、バナナの売価と利益率が入力されて初めてバナナの原価が計算されることになっています。
ここで、計算がループしてしまうのです。F5セル(バナナの利益率)を計算するにはE5セル(バナナの利益)のデータが必要。そのE5セル(バナナの利益)を計算するにはC5セル(バナナの原価セル)のデータが必要。そして、そのC5セル(バナナの原価セル)を計算するには、F5セル(バナナの利益率)のデータが必要です。
言い換えると、原価が分からないと利益が分からない、利益が分からないと利益率が分からない、利益率が分からないと原価が分からない、という構造になってしまっているのです。
このように計算がループしてしまっている状態を、”循環参照”と呼ぶのです。
循環参照の解決方法
さてこの循環参照ですが、解決方法はいたってシンプルです。それは、”循環参照をなくす”ことです。青い線は放っておいても、計算式が修正されれば勝手に消えます。上記の例でいえば、原価欄に利益率を参照する計算式ではなく、絶対値(例えば160など)を投入すれば、青い線は消えてくれます。これで一安心ですね。
コメント