循環参照は非常に扱いにくい場合があるため、それらを見つける方法を知ることが重要です。 Excelには、循環参照を検出し、計算が無限ループになるのを防ぐことができるメカニズムが組み込まれています。この機能を有効にして使用する方法を知っておく必要があります。
この記事では、Excelで循環参照を見つける方法を紹介します。さらに、そもそも正確に循環参照とは何か、なぜそれらを避ける必要があるのかについて説明します。
MicrosoftExcelで循環参照を見つける方法
Excelブックに数式を入力しようとして、ある種の問題が発生した場合、循環参照を処理している可能性があります。これは、数式が独自の値を使用して計算を行おうとしているときに発生します。この時点で、Excelから警告メッセージが送信されます。
「数式が直接または間接的に自身のセルを参照する循環参照が1つ以上あります。これにより、計算が正しく行われない可能性があります。」
無限ループは永遠に続くか、正しい答えに達する前に終了する可能性があるため、Excelの循環参照には近づかないようにすることをお勧めします。それだけでなく、循環参照は、ワークブックの計算プロセス全体を大幅に遅くする可能性もあります。ただし、ほとんどの場合、循環参照の最大の問題はそれらを識別することです。
循環参照には、意図的でない、意図的な、非表示の3種類があります。循環参照の大部分は、Excelの使用に熟練した人が意図的な循環参照を作成する必要があるため、意図的ではありません。最後に、循環参照を非表示にしました。偶発的な循環参照は簡単に見つけることができますが、Excelは非表示の循環参照を常に検出できるとは限らないため、他の方法を使用する必要があります。
警告メッセージが表示されたら、「OK」または「ヘルプ」ボタンをクリックできます。後者は、ワークブックのどこにあるかを指摘せずに、循環参照に関する詳細情報を提供するだけです。一方、[OK]を選択した場合、またはメッセージをオフにするだけの場合は、最後に計算された値または最後のセルにゼロが表示されます。この通知が常に表示されるとは限らないことに注意することも重要です。たとえば、意図的または意図せずに循環参照を作成し続けると、Excelはこの問題についての通知を停止します。
ごくまれに、自己計算メカニズムが動作する前に、1つの循環参照を含む数式が完成することがあります。その場合、最後に成功した値のみが結果として表示されます。言い換えると、循環参照によってシステムが応答しなくなる可能性があります。そのため、それを特定することが最も重要なステップです。
Excelで循環参照エラーを修正するには、最初にそれを見つける必要があります。以下の手順に従って、その方法を確認してください。
- Excelが表示する警告メッセージをオフにします。
- トップメニューの「式」タブに移動します。
- 「エラーチェック」タブに移動してクリックします。
- ドロップダウンメニューの「循環参照」をクリックします。これは、循環参照が明らかになる場所です。
- ポップアップリストの値をクリックすると、その循環参照に直接移動します。
循環参照を含むセルをクリックすると、シート下部のアドレスバーにも表示されます。
循環参照についてさらにヘルプが必要な場合は、前例のトレースと依存関係のトレースの2つのツールが役立ちます。最初のツールであるトレースの前例は、クリックしたセルに影響を与えるセルを示す青い線をExcelブックに表示します。一方、トレース依存関係は逆のことをします。線をトレースして、クリックしたセルの影響を受けるセルを示します。これらの2つの機能は、Excelが検出できない循環参照を見つけるのに役立ちます。これらのトレーサーは循環参照がどこにあるかを正確に示しているわけではなく、より速く見つけるのに役立つ手がかりにすぎないことに注意してください。
トレースの前例とトレースの依存関係を有効にする場合は、次の手順に従います。
- スプレッドシートの上部にある[数式]タブに移動します。
- 「式の監査」カテゴリに進みます。
- 「TracePrecedents」または「TraceDependents」のいずれかを選択します。
一度に選択できるのは1つだけです。これを行うより簡単な方法は、次のショートカットを使用することです。トレースの前例の場合は「Alt + T U T」、トレースの依存関係の場合は「Alt + T UD」。
一部のExcelユーザーは、反復計算を行うために意図的に循環参照を作成します。ただし、一般的に、循環参照をシートに組み込むことはお勧めできません。
追加のFAQ
循環参照を削除する方法
Excelブックですべての問題を引き起こしている循環参照を最終的に見つけたら、それを削除する必要があります。自動的に修正する方法はありませんが、数式のどの部分を削除または置換する必要があるかを判断できます。セルをクリックすると問題が解決し、アドレスバーに「循環参照」タグが表示されていないことがわかります。
循環参照は、反復計算機能が有効になっている場合にのみExcelシートに作成できます。この機能はデフォルトで無効になっているため、通常、追い詰められるものはありません。ただし、反復計算機能が何らかの理由で有効になっているかどうかを確認する場合は、次のようにする必要があります。
1.画面の左上隅にある[ファイル]タブに移動します。
2.メニューの下部にある「オプション」セクションに進みます。
3.ポップアップウィンドウの左側のサイドバーで[式]を選択します。
4. [計算オプション]で、[反復計算を有効にする]ボックスに移動して、チェックされているかどうかを確認します。
5.チェックを外して、反復を無効にします。
6.「OK」ボタンをクリックします。
この方法は、Excel 2010、Excel 2013、Excel 2016、およびExcel2019に適用できます。Excel2007を使用している場合は、[Office]ボタンをクリックして[Excelオプション]に移動すると、反復計算機能が表示されます。 「反復領域」セクションは「式」タブにあります。 Excel 2003以前を使用している場合は、「メニュー」に移動してから「ツール」タブに移動する必要があります。 「計算タブ」は「オプション」セクションにあります。
Excelブックですべての循環参照を見つけます
ほとんどの場合、循環参照は偶然に作成されますが、かなり問題になる可能性があります。数式全体を台無しにするだけでなく、計算プロセス全体を遅くする可能性もあります。そのため、それらを見つけてできるだけ早く交換することが重要です。幸い、Excelは何かが検出された瞬間に通知します。トレースの前例とトレースの依存関係を使用して、循環参照と他のセルとの関係を見つけることもできます。
これまでにExcelシートで循環参照を見つけようとしたことがありますか？どうやって分かったの？以下のコメントセクションでお知らせください。
