4つの方法でExcelで循環参照を見つける方法

Excel シートに循環参照エラーが表示され、ワー​​クシートが壊れているのではないかと思いましたか? 私を信じて！ この Excel エラーに直面したのはあなたが初めてではありません。 毎日、何百、何千もの Excel ユーザーが循環参照エラーに遭遇しています。 心配無用！ Excel で循環参照を見つけて解決する方法は次のとおりです。

熱心な Excel ユーザーは、多くのセルに同じデータを入力することはめったにありません。 代わりに、ソースからデータ セットをインポートし、セル参照を使用して数式を作成します。 したがって、動的スプレッドシートを作成します。

誰かがセル参照のセル値を変更すると、ワークシートは自動的に更新されます。 これはデータ分析の自動化と呼ばれ、データ アナリストはセル参照を操作するのが大好きです。

ただし、現在計算を実行しているセルアドレスを意図的または誤って入力すると、問題が発生します。 または、ワークシート全体で多くの数式を作成している場合、数式を含むセルが別の数式に間接的に表示されます。

このような状況や他の多くの同様の状況では、Excel で循環参照エラーが発生します。 循環参照とは何か、その理由、回避する理由、そして最も重要なこととして、これらの異常を永久に取り除くために Excel で循環参照を見つける方法を学びましょう。

Excel では、ワークシートの 1 つまたは複数の関数が関数が作成されたセル アドレスを参照していることがわかると、循環参照エラーが発生します。

混乱するかもしれませんが、Excel アプリにとってどれほど深刻な問題になるかを考えてみてください。 要するに、セル アドレスを作成している式にセル アドレスを入れたくないということです。 視覚的にわかりやすくするために、次の例を見てください。

増やそうとしている 10 セルから B1 結果をセルに入れます B1 自体。

さて、上記のシナリオで、 OK、Excel はエラーを閉じ、計算結果としてゼロを表示します。 これは単純な例です。

ここで、簿記ワークシート、財務計画ワークシート、従業員の給与計算、ベンダー支払いワークシートなどに何百もの数式があると想像してください。

循環参照エラーが原因で Excel によって計算された誤ったデータを信頼するようになり、ビジネスや専門的な意思決定に影響を与えることになります。

さらに、循環参照は、Excel アプリ内の計算速度を大幅に低下させる可能性があります。 深刻なケースでは、すぐに対処しないと、非常にエラーが原因で PC のパフォーマンスが極端に低下することもあります。

ただし、データ モデルが長い関数を継続するために循環参照からの中間入力を必要とする高度な Excel 数式で作業している場合は、それを使用する必要があるかもしれません。 実際、Excel では、必要に応じてこの計算方法を使用できます。

たとえば、風車などのエネルギー生産ユニットでは、入力エネルギーの一部からリサイクルされた出力エネルギーを計算する必要があります。 あるいは、化学プラントでは、エンジニアは出力ステムを同じまたは別のボイラーまたはプロセス容器の入力として使用します。 このような高レベルの工学計算では、反復計算のために循環参照が必要です。

明らかに、循環参照ポップアップはエラー メッセージではありません。 これは、1 つまたは複数の数式で循環参照が発生し、計算アルゴリズムとパフォーマンスに影響を与える可能性があることを Excel が通知する方法です。

Excel 数式の循環参照による損害を回避するには、それらがいつ表示され、いつ表示されないかを知る必要があります。 以下のインスタンスを見つけます。

• 開いているワークシートまたはブックで、数式を作成し、循環参照を初めて入力します。
• あなたまたは誰かが Excel ファイルからすべての循環参照を削除して保存しました。 ここで、あなたまたは別のユーザーが同じファイルに循環参照を入力します。
• 開いているすべてのブックを閉じました。 ここで、別のブックを開き、循環参照を含む数式を入力します。

• 以前に 1 つまたは多数の循環参照が含まれていたブックを開いたところです。 警告が表示されます。
• Excel で循環参照の警告メッセージが表示された後、クリックすると OK、循環参照式をさらに作成し続けても、同じセッションで同じポップアップが表示されなくなります。

したがって、反復計算を探していない場合は、上記のインスタンスを慎重に探して循環参照の警告をキャッチし、それらを解決する必要があります。

Excel で循環参照を見つける方法を学びたい場合は、それらの種類を理解する必要があります。 Excel で循環参照を見つけて排除することは、自動化されたタスクではないためです。 このエラーの一般的なタイプを以下に示します。

• 意図的な循環参照は、反復計算の目的で意図的に使用すると発生します。 たとえば、誰かが IF、OR、SUMIF などの揮発性および論理関数を使用して参照セルに値を入力したときに、別のセルにタイム スタンプを入力する必要があります。

• Excel の数式をすばやく処理しようとして、数式を含むセルを誤って参照すると、意図しない循環参照が発生します。 たとえば、適用する必要があります 和 細胞からの機能 A1 に A9 結果をセルにフェッチします A10. しかし、誤ってセル A10 に次のような数式を入力してしまいます。

=SUM(A2:A10)

• 非表示の循環参照とは、数式を作成するとき、またはワークシートを初めて開いたときに、警告メッセージが表示されないものです。 これは、誰かが Excel ファイルで反復計算を許可したために発生します。

• 間接循環参照も一般的であり、任意のセル (セルなど) で新しい数式を作成するときに発生します。 A1、そしてこの新しい数式は、セルのように別のセルで別の関数を使用します B1、すでにセルを使用しています A1 参考までに。

Excel での循環参照の基本を理解したので、以下の Excel で循環参照を見つける方法を見てみましょう。

反復計算機能が無効になっている場合、循環参照に関する次の通知が Excel ステータス バーに表示されます。

• 循環参照: C3 (またはその他のセル参照) ワークブック内の循環式を含むワークシートを使用している場合。

• あなただけが見るでしょう 循環参照 何も含まれていないワークシートを使用している場合、ブックにはこれらの循環式がいくつかあります。

これは、ワークブックまたはワークシートで循環参照を確認するための最良の方法です。 知っておくべき手順は次のとおりです。

• 循環式が含まれている疑いのあるワークシートを開きます。
• クリック 数式 Excel リボンのメニュー。

• の中に フォーミュラ監査 コマンド グループで、 エラーチェックドロップダウンリスト.
• カーソルを 循環参照 すべてのワークシートで使用可能なすべてのインスタンスを表示するオプション。
• 各インスタンスを選択すると、Excel がそれらのセルを自動的に特定します。

循環参照を含む多数の数式を検出するのに十分な効率のエラー チェック方法が見つからない場合は、次の手順を試して、数式トレース方法を使用できます。

• 参照されているセルまたは数式を含むセルを選択します。
• に行く 数式 Excel リボン メニューのタブをクリックし、 前例をトレース.
• Excel は、現在のセルの数式の結果に影響を与えるセルを矢印で強調表示します。

• クリックすることもできます 依存関係のトレース 選択したセル参照の値によって影響を受けるセルを検出します。
• 上記のコマンドをクリックしても Excel にエラーが表示されない場合は、ワークシートに循環参照はありません。
• すべてのワークシートで手順を繰り返して、ワークブック全体を確認します。

この方法は、反復計算機能が Excel ワークシートでアクティブになっている場合に特に重要です。

• クリック ファイル タブをクリックして開く Excel オプション 表示されるサイドバーから。
• 上で Excel オプション ダイアログ ボックスで、 数式 サイドバーからメニューを選択し、チェックボックスのチェックを外します 反復計算を有効にする.

• クリック OK 変更を保存します。
• これで、ワークブックまたはワークシートに循環参照の警告が自動的に表示されます。
• すぐに表示されない場合は、ワークブックを保存して閉じ、再度開いて警告を確認します。
• 警告が表示されたら、 Excel ステータス バー 循環参照にあるセルを知る。

Excel で 1 つまたは多数の循環参照エラーを修正する確実な方法は、それを含む各数式を手動で編集することです。 このようにして、Excel での偶発的な循環参照と間接的な循環参照の両方を解決できます。

もう 1 つの簡単な方法は、現在のセルから数式を削除し、同じワークシート内の別の場所に配置することです。

Excel と同様に、Google スプレッドシートでは、反復計算機能を有効にしていない場合、1 つ以上の数式で循環参照を使用できません。 したがって、Google スプレッドシートはすぐに #REF! を表示します。 循環式を含むセルのエラー。

したがって、#REF! を含むすべてのセルを簡単に選択できます。 Google スプレッドシートの循環参照を見つけて解決するための警告。

これで、Excel で循環参照を見つける方法のすべての秘密がわかりました。 好きな方法を使用して、すべての循環参照を捨てて、Excel レポートや数学ダッシュボードからの信頼できないデータに混乱しないようにします。

次は、 任意の年に最適な Excel カレンダー テンプレート そしてその 最高の Excel 予算テンプレート.