数式に「_xlfn」が表示されるのはなぜ？
excel-meister
エクセルマイスター
【対処方法】Excelの循環参照とは？警告の原因と見つけ方・解除方法
excel-meister
Excelで数式を入力したら、次のような警告が表示されたことはないでしょうか。
1 つ以上の循環参照が発生しています。循環参照とは、数式が直接的または間接的に自身のセルを参照している状態を指します。これにより、計算が正しく行われない可能性があります。
循環参照を削除または変更するか、数式を別のセルに移動してください。
[OK] を押すと警告は消えますが、数式の結果が「0」になったり、ファイルを開くたびに同じ警告が出たりします。この記事では、循環参照とは何か、どのセルが原因かを見つける方法、よくあるパターンごとの直し方を紹介します。
Contents
循環参照とは
循環参照とは、数式が自分自身のセルを、直接または別のセルを通して参照している状態のことです。
たとえば、B5セルに =SUM(B2:B5) と入力すると、B5の値を計算するためにB5自身の値が必要になります。計算がぐるぐる回って終わらないため、Excelは計算を止めて警告を出します。
自分自身を直接参照していなくても、次のように別のセルを経由して元に戻ってくる場合も循環参照です。
- A1セル:
=B1 - B1セル:
=C1 - C1セル:
=A1
A1→B1→C1→A1と参照がひと回りして戻ってくるので、どのセルも計算できません。それぞれのセルの数式だけを見るとおかしくないので、気づきにくいのが特徴です。
循環参照になっているセルの見つけ方
見つけ方1：ステータスバーを見る
画面左下のステータスバーに、循環参照になっているセルの番地が表示されます。
- 「循環参照: A1」と表示されている場合: A1セルが原因の一つです
- 「循環参照」とだけ表示されている場合: 開いている別のシートに循環参照があります
見つけ方2：［エラーチェック］の［循環参照］を使う
循環参照が複数ある場合は、メニューから一覧を確認できます。
- ［数式］タブを開く
- ［ワークシート分析］グループの［エラーチェック］の右にある「▼」を押す
- ［循環参照］にマウスを合わせる
- 「A1」「B1」「C1」のように循環参照にかかわるセルが一覧で表示されるので、番地をクリックしてそのセルに移動する
一覧のセルの数式を順に確認し、直したら一覧が出なくなるまで繰り返します。
見つけ方3：［参照元のトレース］で矢印を出す
別のセルを経由した循環参照は、どこで回っているかが分かりにくいものです。そのときは矢印で参照関係を表示します。
- 見つけ方1・2で分かったセルを選ぶ
- ［数式］タブの［参照元のトレース］を押す
- 矢印をたどって、元のセルに戻ってくる経路を確認する
確認が終わったら、［トレース矢印の削除］で矢印を消せます。
よくある原因と直し方
原因1：合計の範囲に合計のセル自身が入っている
いちばん多いのがこのパターンです。
- B5セル:
=SUM(B2:B5)→ B5自身が範囲に入っている
直し方: 範囲を合計のセルの手前までにします。
- B5セル:
=SUM(B2:B4)
行を挿入したときや、オートSUMで範囲がずれたときにも起こります。合計の数式を入れ直したら、範囲が正しいか確認してください。
原因2：列全体を参照している
- B10セル:
=SUM(B:B)→ B列全体にはB10自身も含まれる
直し方: 列全体ではなく、データがある範囲だけを指定するか、合計を別の列に置きます。
- B10セル:
=SUM(B2:B9) - または、D1セルなど別の列に
=SUM(B:B)を置く
原因3：別のセルを経由して元のセルに戻っている
請求書などで、消費税を「合計」から計算してしまう例です。
- B4セル（小計）:
=SUM(B2:B3) - B5セル（消費税）:
=B6*0.1→ 合計を参照している - B6セル（合計）:
=B4+B5→ 消費税を参照している
B5はB6を、B6はB5を参照しているので、どちらも計算できません。
直し方: 消費税は小計から計算します。
- B5セル（消費税）:
=B4*0.1
このように別のセルを経由している場合は、見つけ方3の矢印で経路をたどり、どちらか一方の参照をやめるように数式を組み直します。
原因4：数式をコピーしたら参照がずれた
数式をコピーしたり移動したりすると、参照先のセル番地も一緒にずれます。その結果、自分自身や、自分を参照しているセルを指してしまうことがあります。
直し方: 見つけたセルの数式を確認し、正しいセルを指すように直します。動かしたくない参照には $B$1 のように「$」を付けておくと、ずれを防げます。
残高や累計のように「1つ上の行の値＋今回の金額」で計算する表では、C3なら =C2+B3 のように、1つ上の行を参照しているかも確認してください。
直したのに警告が消えないときの対処
別のシートに循環参照がある
Sheet1のセルがSheet2を参照し、Sheet2のセルがSheet1を参照している、というように、シートをまたいで循環することもあります。ステータスバーに番地が出ない場合は、別のシートに原因があります。各シートに切り替えて、見つけ方2の［循環参照］を確認してください。
非表示のシートに循環参照がある
非表示のシートにある循環参照は、そのままでは見つけにくくなります。シート見出しを右クリックして［再表示］を選び、非表示のシートを表示してから、見つけ方2で探してください。
名前の定義の中に循環参照がある
［数式］→［名前の管理］で定義した名前の中に、その名前を使っているセル自身を参照するものがあると、セルの数式を見ても原因が見つかりません。［名前の管理］を開き、［参照範囲］に自分のセルが含まれていないか確認します。［名前の管理］に表示されない「非表示の名前」が原因のこともあります。
計算方法が「手動」になっている
計算方法が手動だと、数式を直しても再計算されず、表示が残ることがあります。［数式］→［計算方法の設定］で［自動］を選ぶか、［F9］キーを押して再計算してください。
警告を出さないようにする方法（反復計算）
循環参照をわざと使う計算（利息の計算など）では、「反復計算」を有効にすると警告が出なくなります。
- ［ファイル］→［オプション］を開く
- ［数式］を選ぶ
- ［計算方法の設定］の［反復計算を行う］にチェックを入れる
- 必要に応じて［最大反復回数］（初期値 100）と［変化の最大値］（初期値 0.001）を変える
- ［OK］を押す
注意: 反復計算を有効にすると、うっかり作ってしまった循環参照も警告されなくなり、間違った結果に気づけなくなります。意図して使う場合以外は、チェックを外したままにして、数式の方を直すことをおすすめします。
また、この設定はExcel全体に効きます。反復計算を有効にして保存されたブックを開くと、ほかのブックでも警告が出なくなることがあります。
よくある質問
Q. 警告を [OK] で閉じたままにしても問題ありませんか？
A. 循環参照のセルは正しく計算されていません。合計や集計の結果が間違っている可能性があるので、早めに直してください。
Q. [OK] を押したら結果が「0」になりました。
A. 循環参照のセルは計算できないため、「0」や最後に計算された値が表示されます。見つけ方1〜3で原因のセルを探し、数式を直してください。
Q. ファイルを開くたびに警告が出ます。
A. ブックのどこかに循環参照が残っています。見つけ方2の［循環参照］で一覧を確認してください。ほかの人から受け取ったファイルの場合は、作った人に確認するのも一つの方法です。
Q. 循環参照があるはずなのに警告が出ません。
A. ［反復計算を行う］が有効になっている可能性があります。［ファイル］→［オプション］→［数式］で確認してください。また、同じExcelで2回目以降に作った循環参照では、警告が出ないことがあります。
まとめ
- 循環参照は、数式が自分自身のセルを直接または間接的に参照している状態
- 原因のセルは、ステータスバーか［数式］→［エラーチェック］→［循環参照］で見つかる
- 多いのは「合計の範囲に合計のセル自身が入っている」パターン
- 直しても警告が消えないときは、別のシート・非表示のシート・名前の定義・計算方法を確認する
- 反復計算で警告は消せるが、間違いに気づけなくなるので基本は数式を直す
