Heading

Excelの数式を操作していると、次のような警告メッセージが表示されることがあります。

このプロンプトは、ワークシート内に循環参照があり、これが原因で数式による計算が正しく行われない可能性があることを知らせます。

このチュートリアルでは、循環参照について知っておくべきことや、Excel で循環参照を見つけて削除する方法をすべて説明します。

では、さっそく始めてみましょう！

What is Circular Reference in Excel?

簡単に言うと、循環参照とは、あるセルに数式を入力すると、その数式自体が計算のために（入力された）セルを使用することになります。

簡単な例を挙げて説明しましょう。

セル A1:A5 にデータセットがあり、セル A6 で次の数式を使用したとします。

これは、セル A1:A6 の値を合計し、その結果をセル A6 に入れる必要があるためです。

これにより、Excel がセル A6 に新しい値を追加し続け、それが変更され続けるというループが発生します (そのため、循環参照のループが発生します)。

循環参照の警告メッセージは、ワークシートに循環参照が存在することを親切に教えてくれますが、どこで発生しているのか、どのセル参照が原因なのかは教えてくれません。

以下に、Excelで循環参照を見つけるための手順を示します。

1. 循環参照があるワークシートをアクティブにする
2. [数式]タブをクリックする
3. [数式編集]グループで[エラー チェック]ドロップダウン アイコンをクリックする (右にある小さな下向きの矢印)
4. [循環参照]オプションにカーソルを合わせる。 ワークシート内の循環参照を持つセルが表示されます
5. 表示されたセルのアドレスをクリックすると、ワークシート内のそのセルに移動します

問題を解決したら、再び上記の同じ手順を実行すると、循環参照を持つセルの参照がさらに表示されます。 何もない場合は、セル参照は表示されません。

循環参照を見つけるためのもう 1 つの迅速かつ簡単な方法は、ステータス バーを見ることです。

円形参照を見つけるもうひとつの簡単な方法は、ステータス バーを見ることです。ステータス バーの左側に、セルのアドレスと一緒に円形参照というテキストが表示されます。

循環参照を扱う際には、いくつか知っておくべきことがあります。

1. 反復計算が有効な場合 (このチュートリアルで後述)、ステータス バーには円形参照のセル アドレスは表示されません
2. 円形参照がアクティブなシートにない場合 (同じワークブックの他のシートにある場合)、円形参照が表示されるだけで、セル アドレスは表示されません
3. 円形参照の警告プロンプトが一度表示され、それを削除した場合、次回からはプロンプトは表示されません
4. 円形参照の警告プロンプトが一度表示され、それを削除した場合、次回からはプロンプトは表示されません。
5. 循環参照があるワークブックを開くと、ワークブックが開いた時点でプロンプトが表示されます。

Excel で循環参照を削除するには？

シートに循環参照があることを確認したら、これらを削除しましょう (理由があって存在させたい場合を除く)。

残念ながら、削除キーを押すほど簡単ではありません。

セルの参照が誤って問題を引き起こしているだけの場合は、参照を調整することで修正できます。

しかし、それほど単純ではない場合もあります。

循環参照は、複数のセルがさまざまなレベルで相互に影響しあって引き起こされることもあるのです。

例を挙げましょう。

• セル A6 の計算式は =SUM(A1:A5)+C6
• セル C1 の計算式は =A6*0.1
• セル C6 の数式は =A6+C1

上記の例では、セル C6 の結果は、セル A6 と C1 の値に依存しており、さらにセル C6 にも依存しています (そのため、循環参照エラーが発生しています)

繰り返しになりますが、ここではデモ用に非常に簡単な例を選んでいます。

このような場合、循環参照の原因となっているセルを特定して処理する方法があります。

それは、[先例のトレース] オプションを使用することです。

以下は、循環参照の原因となっているセルに供給されているセルを見つけるために、先例のトレースを使用する手順です。

1. 循環参照を持つセルを選択する
2. [数式]タブをクリックする
3. [先例のトレース]をクリックする

上記の手順では、どのセルが選択したセルの数式に入力されているかを示す青い矢印が表示されます。

複雑な財務モデルを扱っている場合、これらの先例が複数のレベルに及ぶ可能性があります。

この方法は、すべての数式が同じワークシートのセルを参照している場合に有効です。

Excel で繰り返し計算を有効または無効にする方法

セル内に循環参照があると、まず以下のような警告プロンプトが表示され、このダイアログ ボックスを閉じると、セル内の結果として 0 が返されます。

しかし、場合によっては、循環参照を実際にアクティブにして、何度か繰り返し実行したいこともあるでしょう。

このような場合、無限ループではなく、ループを何回実行するかを決めることができます。

これは、Excelの反復計算と呼ばれています。

以下に、Excelで反復計算を有効にして設定する手順を示します。 これにより、[Excel オプション] ダイアログ ボックスが表示されます

左側のペインで [数式] を選択します

[計算オプション] セクションで、[反復計算を有効にする] にチェックを入れます。 ここでは、最大反復回数と最大変化値を指定することができます

以上で完了です。

反復計算の2つのオプションについても簡単に説明します。 これは、最終的な結果を得るまでにExcelが計算する最大の回数です。 つまり、これを 100 と指定した場合、Excel は最終結果を得るまでに 100 回ループを実行します。 これは、反復の間に達成されなかった場合、計算が停止される最大の変化です。 デフォルトでは0.001となっています。

反復処理の回数が多いほど、Excelの処理にかかる時間とリソースが増えることを覚えておいてください。

意図的に循環参照を使用する

ほとんどの場合、ワークシートに循環参照があるとエラーになります。

しかし、目的の結果を得るために循環参照が必要な特定のケースがあるかもしれません。

そのような特定のケースの 1 つは、Excel のセル内のタイム スタンプを取得することについて既に書いたことがあります。

たとえば、A列のセルに入力が行われるたびに、B列にタイムスタンプが表示されるような数式を作成したいとします（以下のように）：

一方、以下の数式を使用して、タイムスタンプを簡単に挿入することができます。

=IF(A2<>"",IF(B2<>"",B2,NOW()),"")

上記の数式の問題点は、ワークシートに変更が加えられたり、ワークシートが再度開かれたりすると、すぐにすべてのタイムスタンプが更新されてしまうことです (NOWの数式は揮発性であるため)

この問題を回避するには、循環参照の方法を使用します。

他にも、循環参照を使用できることが望ましいケースがいくつかあります (1 つの例をここで見つけることができます)。

このチュートリアルがお役に立てれば幸いです！

その他のExcelチュートリアル:

お役に立てるかもしれません。