【エクセル】循環参照の検索方法と修正方法

Excelファイルを開き、画像のような長いダイアログボックスを思いついたことがありますか?

「1 つ以上の循環参照が発生しています。循環参照は、数式が直接または間接的にそれ自身のセルを参照する条件です。これにより、計算が正しく行われなくなる可能性があります。循環参照を削除または変更するか、数式を別のセルに移動します。と書かれています。

メッセージは、「循環参照が発生しています。修正してください」です。

正しい計算をするためにも、ダイアログが出たらすぐに対処しましょう。

循環参照とは何か、原因細胞を見つける方法、画像で修正する方法を説明します。
何が起こっているのかがわかったら、修正するのは難しくないので、挑戦しましょう。

循環参照とは

ユーザーが自身のセルを参照している状態、または数式で競合が発生している状態アール。

「1 つ以上の循環参照が発生しています。選択[OK]「循環参照とは…」ダイアログボックスでは、循環参照が発生している場所に移動したり、修正したりすることはありません。
あなたは自分でそれを見つけて修正する必要があります。

このダイアログボックスはファイルを再度開くまで表示されないため、修正がある場合は、ファイルを開くたびにダイアログボックスが表示されます。
慌てないでください、それらを一つずつ修正しましょう。

循環参照の検索方法

[数式]タブをクリックします。

の▼をクリック[Error checking]カーソルを[Circular reference]循環参照の原因となっているセル(画像の場合はD6)が表示されるので、クリックします。

セルが選択されたので、数式バーをチェックして修正します。

注意

▼の[Error Check]カーソルを[Circular Reference]循環参照の原因となっているセル(画像の場合)が表示されますが、複数の循環参照があっても1つのセルしか表示されません!
グレーになるまで数回繰り返します(クリックすることはできません)。

循環参照の原因とその修正方法

循環参照には、主に 2 つの原因があります。
自己参照または相互参照の状態。

• 自己参照:答えを式に組み込んだ状態、
• 相互参照:お互いが答えである状態。

数式での自己参照

たとえば、上の表では、次のようになります。
数量(列B)×単価(列C)=金額(列D)ですが、ブラシボックスの金額は表示されません。
セル内の数式を見ると、数量 (B6) ×量 (D6) です。これはあなたに答え(金額)を与えません。
ダブルクリックするとセルに色がついているので、見やすいです。

D6 でセルを選択し、=B6*C6 に修正します。

「=B6*C6」に変更すると、数量×単価が表示されました。

関数式での自己参照

合計金額(D7)は出ていません。
セルを選択すると、そのセルは “=SUM(D2:D7)" となり、回答 (合計) が数式に組み込まれます。

D7 でセルを選択し、=SUM(D2:D6)) に修正します。
このような入力ミスは、ドラッグ操作でセルを指定する場合に発生しやすい。

数式内の相互参照

A1 のセルは “=B1" で、B1 のセルは “=A1" です。
お互いがお互いを参照しているので、答えはありません。

数式の 1 つを削除し、表示する数値を入力します。
画像は、数式 B1 が削除され、300 が入力された状態です。

関数式での相互参照

A4 のセルは “=IF(B4="","入力なし","入力あり","入力あり") と入力されます。
「B4セルが空白の場合は「入力なし」を表示し、空白でない場合は「入力あり」と表示する機能が含まれています。
ただし、セル B4 では、"=IF(A4<>"","入力あり","入力なし")" です。
「A4セルが空白でない場合は「入力あり」と表示し、空白の場合は「入力なし」と表示する」という機能が含まれています。

式は異なりますが、空白であるかどうかにかかわらず、互いに参照し続けます。

A4 関数または B4 関数のいずれかを削除すると、解決されます。
私は画像のためにB4に300を入力したので、A4は今入力を持っています。

数式の表示方法

[[Formula]タブをクリックし、[Display Formula]セルに入力された数式を表示できます。

次の図は、表示された数式を示しています。

より多くの循環参照の原因を見つけやすくする方法

循環参照の原因となっているセルを見つけやすくするには、循環参照の原因となっているセルにカーソルを置き、[Reference Source Trace]に[Formula]タブをクリックすると、計算式を見やすくするために矢印が追加されます。

最後です
循環参照を見つけて修正する方法を説明する

Excelファイルを開くときに、長いダイアログボックスが表示されたら驚かれますが、循環参照について知っていれば、それは難しくありません。
循環参照は、関数の引数をドラッグしたり、タイプミスをすることで発生する可能性が高くなります。
間違いは誰にでも起こり得ます。
落ち着いて、原因を見つけ、それに対処してください。