Excelで循環参照を許可する方法｜反復計算の設定と2つの実用的な活用例

「循環参照（Circular Reference）」とは、Excelの数式が自分自身のセルを直接または間接的に参照してしまう状態を指します。Microsoftの公式定義では、たとえばB2とC2の2つのセルの値を合計したいのに、その結果を入力元のセルであるB2自体に表示しようとすると、循環参照が発生します。この状態で初めてEnterキーを押すと、「数式に1つ以上の循環参照が含まれています」という警告メッセージが表示されます。

循環参照は、数式の結果を「反復計算」によって求めます。そのため、最大反復回数の設定値が大きすぎると、計算が無限ループのように繰り返され、意図しない結果になることがあります。

本記事では、循環参照の仕組み、注意点、そして実際の活用方法について詳しく解説します。

循環参照が有効か無効かを確認する方法

まず、自分のワークシートで循環参照が有効になっているのか、それとも無効なのかを確認する方法を見ていきましょう。

最も分かりやすい確認方法は、自分でセル参照を入力してみることです。循環参照が無効な場合、Excelは初回入力時に警告を表示します。逆に、警告が表示されず数式が正しい結果を返す場合は、循環参照（反復計算）が有効になっていると判断できます。

また、数式にセル参照を入力したのに、理由もなく結果が0になってしまう場合は、ワークシート下部のステータスバーにある「準備完了」の右側を確認してください。「循環参照： セル番地」と表示されていれば、そのワークシートでは循環参照が無効になっています。

さらに、数式タブ ＞ エラーチェック（ワークシート分析グループ内）＞ 循環参照を選択すると、循環参照エラーが発生しているセルの場所を特定できます。

ファイルリボンから循環参照を許可する手順

循環参照を許可するということは、数式の参照エラーを意図的に無視できるようにすることです。これにより、数式は参照エラーを乗り越えて正しい計算結果を返せるようになります。

以下の手順に従って設定を行ってください。

➤ 任意のワークシートでファイルタブを開きます。

➤ ファイルメニューからオプションを選択します（Excelのオプションウィンドウが開きます）＞ 左側の一覧から数式を選択 ＞ 反復計算を使用するにチェックを入れます（必要に応じて最大反復回数と最大変化値も入力します）。

最後にOKをクリックします。

以上の手順を実行することで、既存の循環参照を含む数式でも計算結果を返せるようになります。

最大反復回数に関する注意点

循環参照が発生すると、Excelは初回のみ警告を表示します（下のスクリーンショット参照）。

しかし、それ以降は警告が表示されなくなり、ユーザーは何が問題なのか分からないまま、どのような数式を入力しても結果が0になってしまう事態に悩まされることになります。

この計算トラブルを解決するには、反復計算を使用するオプションを確認する必要があります。ファイルタブを開いてください。

ファイルタブをクリック後、オプションを選択（Excelのオプションウィンドウが開きます）＞ 数式を選択 ＞ 反復計算を使用するにチェックを入れます。そしてOKをクリックします。

これで数式内で循環参照を使えるようになります。ただし、最大反復回数に1を入力した場合、数式（=B2+C2）は20を返します（B2が空でC2の値が20なので、これは正しい結果です）。

ところが、下のスクリーンショットのように最大反復回数に5を入力するとどうなるでしょうか。

同じ計算が5回繰り返されるため、数式は100を返してしまいます。これはExcelにおける最も厄介な誤計算の一つです。

Excelで循環参照を活用する2つの実例

循環参照は、特定の場面では非常に便利な機能です。値をより効果的に表現するために、循環参照が必要になるケースがあります。ここでは、最もよく使われる2つの活用例を紹介します。

例1： 循環参照を使って静的なタイムスタンプを挿入する

たとえば、商品ごとの注文日と配送状況のデータがあり、商品が配送された時点でタイムスタンプを自動的に記録したいとします。配送状況は「Yes」または「No」で管理します。

つまり、配送状況が「Yes」であれば、その時点の日付と時刻をタイムスタンプとして表示させたいわけです。

➤ タイムスタンプを表示したい空白セル（ここではE5）に、次の数式を貼り付けます。

=IF(D5="Yes",IF(E5="",NOW(),E5),"")

この数式は、まずD列の値が「Yes」と一致するかどうかの論理テストを行います。TRUEの場合は現在時刻を表示し、FALSEの場合はセルを空白のままにします。E5が空欄ならNOW()の結果を記録し、一度記録された後はE5自身の値を参照し続けることで、時刻が更新されない「静的なタイムスタンプ」を実現しています。

➤ Enterキーを押したら、フィルハンドルをドラッグして、配送済みの商品すべてにタイムスタンプを挿入します。

循環参照は自己参照を伴うため、数式がうまく動作しないように見えることがあります。その場合は、配送状況や条件を再入力してから数式を適用してください。また、日付・時刻形式で表示するために、セルの書式設定を変更する必要がある場合もあります。

例2： 循環参照を使った反復計算

反復計算が必要な場面もあります。反復計算を使うと、全体の計算過程を経由せずに、目的の反復値を直接求めることができます。

たとえば、初期資金$1500を投資し、資産から毎月の支払額を受け取るケースを考えてみましょう。1か月目の支払額は次の数式で計算できます。

=$C$4*(1+$C$5)

続けて、前月の支払額を参照しながら翌月以降の支払額を順番に計算していきます（下図参照）。

➤ しかし、循環参照を利用すれば、n回目の支払額を、反復回数を指定するだけで一気に求められます。正しい結果を得るために、最大反復回数に5、最大変化値に0.001（1000分の1）を入力します。

最大反復回数を5にしているのは、6回目の支払額を計算するためです。また、変化量の許容範囲を1000分の1に設定しています。

➤ 循環参照を許可した状態で、セルF4に=F6と入力します。これにより、セルF6（=F4*(1+F5)の数式が入力されている）の5回反復後の計算値を取得でき、循環参照が成立します。

セルF6の数式は、わずか2ステップで6回目の支払額を算出し、手間を大幅に削減できます。

まとめ

本記事では、Excelで最も混乱を招きやすい現象の一つである循環参照について解説しました。最大反復回数オプションが引き起こす問題点にも触れ、循環参照の一般的な活用法と、適切な計算に役立てるテクニックをご紹介しました。この記事が循環参照に対する理解を深め、より効果的な活用につながれば幸いです。ご質問や追加情報があれば、ぜひコメントでお知らせください。

関連記事

• Excelで循環参照を削除する方法（2つの方法）
• Excelで一覧に表示されない循環参照を修正する方法（4つの簡単な方法）