Excel で循環参照を許可する方法 (2 つの適切な用途)

「Excel で循環参照を許可」は、Excel でセルを自己参照する簡単な方法です。 Microsoft の定義によると、循環参照 これは、Excel の数式が直接的または間接的に独自のセルを参照している場合に発生します。たとえば、2 つのエントリ (つまり、B2) を追加する場合 とC2 ) ただし、エントリの 1 つに結果を表示します (つまり、B2) )、次に循環参照を作成します。 ENTER を押した後 Excel は初めて、式のみに 1 つ以上の循環参照があるという警告を表示します。

循環参照は、式の結果を繰り返し計算します。その結果、最大反復時間を設定しすぎると、ループが作成される可能性があります。

この記事では、循環参照について説明します 、その問題、およびその使用法。

Excel ワークブックをダウンロード

循環参照が有効になっているかどうかを確認する方法

前のセクションから、循環参照とは何か、そしてその対処方法を理解しました。最も関連性の高い質問は、Excel ワークシートの循環参照が有効か無効かを確認する方法です。

最も自然な方法は、自分で参照を入力すると、次の画像に示すように、Excel が最初に警告を表示することです。 Excel に警告が表示された場合、ワークシートで循環参照が無効になっていることはほぼ確実です。それ以外の場合、数式は最大反復回数 1 に対して正しい結果を返します .

数式にセル参照を入力すると、Excel がゼロ (0) を返します。 ) 明白な理由なし。 Ready の右側のテキストを確認します ワークシートの下部にあるステータス。 Circular Reference:cell と表示されている場合 、ワークシートの循環参照オプションが無効になっています。

数式に移動できます タブ> [エラー チェック] を選択します (フォーミュラ監査で セクション)> [循環参照] を選択します (エラー チェックから オプション）。 循環参照のエラーが発生した場所を示します。

続きを読む: Excel で循環参照を見つける方法 (2 つの簡単なトリック)

Excel で循環参照を許可する ファイル リボンの使用

循環参照を許可することは、数式の参照エラーを無視することの一種です。それでも、循環参照を許可することで、式は参照エラーを克服し、正しい結果値を返すことができます。

循環参照を許可するには、以下のシーケンスに従います。

➤ ファイルに移動 任意のワークシートのリボン。

➤ オプションを選択 ファイルから メニュー オプション (Excel のオプション ウィンドウが表示されます)> [式] を選択します (ウィンドウの右側)> [反復計算を有効にする] にチェックを入れます オプション (必要な Maximum iterations を入力します) 数と最大変更 ).

[OK] をクリックします .

これらのシーケンスを実行すると、数式が既存の循環参照で結果の値を返すことができるようになります。

最大反復の問題

Excel で循環参照が発生すると、下のスクリーンショットに示すように、Excel は初めて警告を返します。

ただし、後で Excel に警告が表示されず、結果の値がゼロ (0) を返すため、ユーザーは何が問題なのか疑問に思って計算に行き詰まります。 ) 式の種類に関係なく。

この計算の問題を解決するには、反復計算を有効にするをチェックする必要があります オプション。 ファイル に移動 次の図に示すように、タブ オプション。

ファイルをクリックした後 リボン、オプションを選択 (Excel のオプション ウィンドウが表示されます)> [式] を選択します (Excel のオプション の左側のセクションにあります) ウィンドウ)> [反復計算を有効にする] にチェックを入れます . [OK] をクリックします .

これで、数式で循環参照を使用できるようになりました。 1 と入力すると (入力したとおり) Maximum iterations 数式をボックス化します (つまり、=B2+C2 ) 20 を返します (これは B2 として正しいです 空で C2 20 の値を持っています ) 下の画像に示すように。

ただし、5 と入力すると 最大反復回数 次のスクリーンショットに表示されているように、オプション ボックス。

数式は 100 を返します 同じ計算を繰り返すため 5 回。これは、Excel で最も気がかりな誤算です。

Excel での循環参照の使用

循環参照 特定の分野で非常に役立ちます。 循環参照が必要なインシデントもあります 価値をより効果的に表現するために。この記事では、最もよく使用される 2 つの 循環参照 について説明します インスタンス。

例 1:Excel での循環参照による静的タイムスタンプの挿入を許可する

いくつかの商品があるとしましょう 注文日付き および配信ステータス . タイムスタンプを挿入したい 製品のセルに 配信されます。 配送状況を表示します はい または いいえ

そのため、配信ステータス はい タイムスタンプ 当時の時間を表示します と日付 .

➤ 次の数式を空白のセルに貼り付けます (例:E5) ) タイムスタンプを表示する場所。

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

式は logical_test を実行します Yes を列 D のエントリと一致させます .次に、TRUE を返すセルに現在の時刻を表示します それ以外の場合は、セルを空白のままにしてください。

➤ ENTER を押します フィル ハンドルをドラッグします タイムスタンプを挿入します

循環参照以来 セル参照の問題が発生するため、数式が正しく機能しない場合があります。その場合、配送ステータスを書き換えます または条件は、任意の式を適用します。また、日時形式を取得するには、セル形式を変更する必要がある場合があります。

例 2:反復計算のために Excel で循環参照を許可する

場合によっては、反復計算を実行する必要があります。反復計算では、計算全体を実行せずに直接反復値を取得します。

あるシナリオで、最初の現金額 (つまり、$1500) を投資するとします。 ) であり、資産から毎月の分割払いが必要です。 1 ^st の分割払い金額を計算できます 以下の式を使用して月。

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

次に、次の図に示すように、前回の分割払いの金額を参照して、次の分割払いの金額を計算します。

➤ ただし、循環参照を使用すると、n を取得できます。 ^番目 Maximum iterations として分割払い回数を入力するだけで、分割払い金額 番号。正しい結果を得るには、5 と入力します 最大反復回数と 1 として ^番目 1000の 下の画像に示すように、許容される変更。

最大反復回数 5 を入力します 6 を計算する必要があるため ^番目 取り付け。そして、変更可能な量が 1 変化することを許可します 1000の

➤ 循環参照を許可した後 、タイプ =F6 セル内 F4 5 後に計算値を取得する セル F6 の繰り返し (循環参照を作成しています ) =F4*(1+F5) を適用

F6 の数式 セルは 6 を計算します ^番目 2 回目の分割払いで、手間がかかりません。

結論

この記事では、Excel で最も厄介な問題の 1 つである Circular Reference について説明します。 .また、Maximum iterations によって作成された問題を処理します オプション。 循環参照の一般的な使用法を示します 適切な計算で使用するためのトリック。この記事で Circular Reference に関する概念が明確になることを願っています より効果的に対処するのに役立ちます。さらにクエリや追加するものがあればコメントしてください。次回の記事でお会いしましょう。

関連記事

• Excel で循環参照を削除する方法 (2 つの方法)
• Excel で一覧表示できない循環参照を修正する (4 つの簡単な方法)