恐怖!計算式の答えが必ず0に!(循環参照の見つけ方

先日ある計算式を作ったんですよ。

f:id:ExcelLover:20180804113113j:plain
(※実際のややこしい表の一部抜粋のため数式もややこしい)

そしたらですね、数式はあってるのに(あってないけど)

答えが0しか表示されないんですよ。

どこがおかしいんだろうと思って、「数式の検証」で調べてみると

途中までは、よしよしちゃんと計算できていると、思っていたのに f:id:ExcelLover:20180804113140j:plain

一番最後で答えがになる! f:id:ExcelLover:20180804113226j:plain

why?

 循環参照の見つけ方

タイトルで出オチしてるんですが、循環参照が発生しているために答えが0になっているんですね。

循環参照とは

数式が入力されているセル自体を計算しようとし、かつ反復計算と呼ばれる機能がオフになっているために発生します

今回の例では(最初の画像で)B1セルの数式がB1セル自体を参照しているために循環参照が発生していました。

そもそも循環参照が発生したときにはこのような忠告が表示されます。 f:id:ExcelLover:20180804113314j:plain

この忠告は

・循環参照が1つもないで新しく循環参照が発生したとき

・対象のブックを開いたとき

にのみ表示されるようです。

既に循環参照が発生しているときに更に循環参照が発生する数式を作成しても忠告は表示されませんでした。

発生時の忠告以外にも自分で循環参照を確認する方法もあります。

ステータスバー

循環参照が発生しているときはステータスバーに「循環参照」の表示と、発生しているセルが1つだけ表示されます。

セル番地の表示は1つだけですの表示されている箇所を直してまだ循環参照が残っている場合は、セル番地の表示が変わります。
f:id:ExcelLover:20180804113340j:plain

エラーチェック

数式タブの「エラーチェック」からも同じように確認することができます。

こちらも同じように複数の循環参照があっても1つだけ表示されますので、表示が無くなるまで修正していきましょう。
f:id:ExcelLover:20180804113357j:plain

まとめ

最初に循環参照が発生したときにメッセージは表示されたんですが、その後で席を外して戻ってきてから、その事を忘れていて、ハマっていました。

数式の検証で調べているときに数式自体をよく見ていればすぐに気づいたんでしょうけど、1番最後までは想定通りの計算をしてくれていたので見逃していました。