わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】循環参照のエラーは直し方より、探し方を理解したほうがよい


定期的に避難訓練をするのは災害が発生したときに適切な行動がとれるようにするためですが、Excelのエラーのなかにも、事前に訓練しておけば冷静に対処できるものがあります。

f:id:waenavi:20181013174254j:plain

特に、「循環参照(じゅんかんさんしょう)」のエラーは、普段から注意すれば防げるものであり、発生してもたいていの場合はすぐに直すことができます。しっかりと理論的に原因を知ったうえで、対処できるように日頃から訓練をしておくことが大事です。

循環とは何か、参照とは何か、循環参照のエラーが発生したことを想定して訓練してみましょう。 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画のリンクを載せています。ぜひご覧ください。

1.参照とは何か

問題

セルB1に、セルA1を参照する式を入力しなさい。

解説

セル番地を計算式に利用することを「参照 reference 」といいます(参考:【Excel数式】セルの「参照」が分からなければ相対参照は理解できない)。

f:id:waenavi:20181013072934j:plain

 

イコールを入力した直後に、セルA1をクリックします。これでA1を利用したことになります。

f:id:waenavi:20181013073338j:plain

f:id:waenavi:20181013073343j:plain

 

確定するとゼロになります。これはA1に何も入力していないからです。

f:id:waenavi:20181013073347j:plain

 

セルA1に5を入力すると、連動してB1も5になります。

f:id:waenavi:20181013073351j:plain

f:id:waenavi:20181013073356j:plain

 

このように参照するときには必ず、他のセルを参照します。参照することによって、A1に入力した値が、そのままB1にも表示されて連動します。

f:id:waenavi:20181013073400j:plain

 

文字列もそのまま表示されます。

f:id:waenavi:20181013073404j:plain

 

2.自己参照はダメ!

問題

次の表で、A~C列の合計を求めたが、さらに2倍しようとして合計を足したらエラーになった。その理由を述べたうえで、数式を修正しなさい。

f:id:waenavi:20181013081429j:plain

 

解説

SUM関数を用いて合計を求めます。

f:id:waenavi:20181013081908j:plain

 

さらに2倍するのに、いま求めた合計を足せばいいので、D2を足します。

f:id:waenavi:20181013081911j:plain

 

エラーになります。

f:id:waenavi:20181013081913j:plain

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

 

数式の先頭のイコールは「等しい」という意味ではありません。

f:id:waenavi:20181013081916j:plain

 

イコールの左側に「セルD2」があるものと考えます。セルD2に代入するという意味があります。

f:id:waenavi:20181013082039j:plain

(※注:厳密に言えば先頭のイコールは演算子ではありませんが、計算結果をセルD2に代入して上書きするという意味をもつ記号です)

セルD2の値は計算が終わらなければ確定しません。

f:id:waenavi:20181013083056j:plain

 

「Excelの数式は過去に表示されていた値を取り消したうえで計算をやり直す」という大原則があります。これは極めて重要な原則です!

仮に、数式を入力する前に「271」という合計値が表示されていたとしても、それを取り消して計算をやり直します。

過去の値を取り消さないと、とんでもないことになります。セルD2に表示されていた値によって計算結果が変わってしまいます。例えば、セルD2に100000000などと適当な値を入力してから数式を入力すると、異なる計算結果になるということです。また、自動再計算をするたびにコロコロと計算結果が変わってしまいます(SUMの分だけ増え続ける)。

したがって、セルD2は新たな計算結果を待ち受けている状態になります。今から計算しようとしているセルを足そうとしても、足せるわけがありません。エラーになるのは当然のことです。

f:id:waenavi:20181013083059j:plain

 

このように、自分自身を参照することを循環参照(自己参照)といい、Excelではエラーとなります。「循環」というのは、自分自身の数式で自分自身を参照しているために、いつまでたっても計算が終わらない、または計算結果が変わり続けて最終的な答えが確定しないという状態です。

f:id:waenavi:20181013083101j:plain

 

循環参照のエラーにもこのように書いてあります。

1つ以上の循環参照が発生しています。循環参照とは、数式が直接的または間接的に自身のセルを参照している状態を指します。

f:id:waenavi:20181013174254j:plain

 

重要なのでもう一度繰り返します。Excelの数式は、過去に表示されていた値にかかわらず、それを取り消して計算をします。自分自身のセルを参照しても絶対に答えは出ません。また、自分自身を数式の一部に用いてはいけません。(ちなみに、プログラミングの場合は過去の値を保持するので自己参照をしてもOKです)

f:id:waenavi:20181013083105j:plain

 

SUM関数の引数をD2にするのもエラーです。

f:id:waenavi:20181013083110j:plain

 

D2の値は代入されるまで確定しないので、D2の値がわからない段階で、引数として使うことはできません。

f:id:waenavi:20181013083113j:plain

 

これも循環参照です。

f:id:waenavi:20181013083115j:plain

 

循環参照のエラーが起きると、計算が止まり0と表示されます。

f:id:waenavi:20181013083118j:plain

 

また、Excelの画面の左下に循環参照のセルが表示されます。

f:id:waenavi:20181013083120j:plain

 

さらに、数式タブのエラーチェックにも、循環参照のセルが表示されます。

f:id:waenavi:20181013083123j:plain

 

循環参照のエラーが出たときは、いったん消してやり直します。

f:id:waenavi:20181013083130j:plain

 

この場合はSUM関数を2倍すればよいです。

f:id:waenavi:20181013083128j:plain

 

3.相互参照

問題

次の表で、セルD3にD11を参照する数式を入力したらエラーになった。その理由を述べたうえで、数式を修正しなさい。

f:id:waenavi:20181013081441j:plain

 

解説

D3は合計金額なので、D11を参照するのは正しいです。

f:id:waenavi:20181013105138j:plain

 

循環参照のエラーになります。

f:id:waenavi:20181013105140j:plain

 

エラーチェックには、D3とD11の式に問題があると表示されています。

f:id:waenavi:20181013105142j:plain

 

D11の式は合計ですが、引数のセル範囲が間違っています。

f:id:waenavi:20181013105149j:plain

 

D3の数式はD11を参照しています。D11の計算結果が決まらなければ、D3にそれを表示することもできません。

f:id:waenavi:20181013105157j:plain

 

D11の合計の計算式は、引数(かっこ)の中にD3が含まれていますので、D3の値が決まらなければD11に合計を表示することができません。

f:id:waenavi:20181013105153j:plain

 

しかし、そのD3はD11を参照していますから、結局、自分自身を参照しているのと同じです。

f:id:waenavi:20181013105159j:plain

 

このように、2つのセルが参照しあっている状態も、循環参照のエラーです。

f:id:waenavi:20181013105336j:plain

 

合計の引数を直します。

f:id:waenavi:20181013105443j:plain

f:id:waenavi:20181013105446j:plain

 

4.エラーを放置してはいけない

問題

次の表で、B8:E8を選択します。

f:id:waenavi:20181013105746j:plain

 

ドラッグして1つ上のセルに移動すると、エラーは表示されますが、上に移動することはできました。

f:id:waenavi:20181013105751j:plain

 

B8:E8をB7:E7に移動する操作の問題点を述べなさい。

f:id:waenavi:20181013105755j:plain

 

解説

セルE8はE3:E7の合計ですが、セルの移動をしても引数は変わりません。

f:id:waenavi:20181013165556j:plain

 

したがって、循環参照になります。

f:id:waenavi:20181013165600j:plain

 

エラーチェックで循環参照にE7と表示されます。

f:id:waenavi:20181013165604j:plain

 

かならず引数を直します。

f:id:waenavi:20181013165609j:plain

 

循環参照のエラーが出たときは絶対に放置してはいけません。必ずエラーチェックで循環参照のセルを確認します。

f:id:waenavi:20181013182911j:plain

 

*補足*

循環参照を含むExcelファイルを開いたときにも、次のような循環参照のエラーが表示されます。放置せず、必ず修正します。

ご注意ください。ブックに循環参照が見つかったため、数式を正しく計算できない可能性があります。
参考:循環参照とは、計算結果を表示するセルに自分自身を入れてしまった場合や、その値に依存するセルを入れてしまった数式をいいます。

 

5.オートフィルによる循環参照

問題

合計を求めます。

f:id:waenavi:20181013171326j:plain

 

オートフィルをすると循環参照のエラーが表示されました。エラーの理由を述べなさい。

f:id:waenavi:20181013171328j:plain

 

解説

合計の数式自体は間違っていません。また、オートフィルをすることも間違っていません。しかし循環参照のエラーが出ます。

f:id:waenavi:20181013171328j:plain

 

このように「正しい操作をしている」と思っていても循環参照が発生することはあります。

f:id:waenavi:20181013165615j:plain

 

循環参照のエラーが出たときは、エラーチェックで循環参照のセルを確認します。
D7とB7です。D7の数式自体間違っていません。

f:id:waenavi:20181013165620j:plain

 

セルB7にはD7を参照する数式が入力されています。D7の引数にB7が含まれるため、循環参照です。

f:id:waenavi:20181013165624j:plain

 

B7の参照を消します。

f:id:waenavi:20181013165629j:plain

 


解説は以上です。

*補足意見1*
Excelで反復計算を許すように設定することは可能(Excelのオプション)ですが、収束値を求めるなどの特殊な用途でない限り、循環参照を放置してはいけません。

*補足意見2*
「循環とは相互に参照しあっていること」と記述しているサイトを見かけますが間違いです。循環とは計算の途中に自分自身が登場する(間接的に自分に返ってくる)ことをいい、「自分とは何か」という問いを解決するのに、「自分とは何か」という同じ計算を繰り返さなければいけないという状態を指します。


6.動画版はこちら(無料)

この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 11-11 のYoutube動画を書き起こして、加筆修正したものです。

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ