誤ったデータを回避するために Excel で循環参照を見つける方法

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

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

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

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

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

Excel の循環参照とは何ですか?

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

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

セルB1から10を掛けて、その結果をセルB1自体に入れようとしています。

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

Excelの循環参照とは

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

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

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

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

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

Excel で循環参照エラーが表示されるのはいつですか?

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

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

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

Excel で循環参照エラーが表示される場合

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

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

Excel の循環参照の種類

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

循環参照の使用例

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

Excel での意図しない循環参照

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

=SUM(A2:A10)

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

間接循環参照の例

• 間接循環参照も一般的であり、たとえばセルA1などの任意のセルで新しい数式を作成すると発生し、この新しい数式は、セルA1を参照として既に使用しているセルB1などの別のセルで別の関数を使用します。.

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

Excelで循環参照を見つける方法：ステータスバーから

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

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

Excel ステータス バーに循環参照を表示する

• 循環参照が含まれていないワークシートを使用している場合にのみ、循環参照が表示されますが、ブックにはこれらの循環式がいくつか含まれています。

Excel で循環参照を見つける方法: エラー チェックの使用

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

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

Excel で循環参照を見つける方法のエラー チェックを使用する

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

Excel で循環参照を見つける方法: フォーミュラ トレースから

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

• 参照されているセルまたは数式を含むセルを選択します。
• Excel のリボン メニューの[式]タブに移動し、 [参照元の追跡]をクリックします。
• Excel は、現在のセルの数式の結果に影響を与えるセルを矢印で強調表示します。

Excel で循環参照を検索する方法にフォーミュラ トレースを使用する

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

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

Excel で循環参照を見つける方法: Excel オプションの使用

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

Excelで反復計算を無効にする方法

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

Excel で循環参照を解決する方法

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

Excel で循環参照を解決する方法

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

Googleスプレッドシートで循環参照を見つける方法（ボーナスメソッド）

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

Google スプレッドシート 循環参照を見つける方法

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

Excel 循環参照: 結びの言葉

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

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