スポンサーリンク

【Excel】エクセルで循環参照に関する警告とは?エラーを探す/検索方法や削除の流れ


Excelで計算処理などをしている際に、「1つ以上の循環参照が発生しています」との警告が出て驚いたことがあるかもしれません。どのような問題なのかを知って、対処法を把握して解消したいですよね。今回は、エクセルで循環参照に関する警告に関して、エラーを探す方法や削除の流れをご紹介します。



◎Excelでの循環参照とは?エクセルのエラー例「1つ以上の循環参照が発生しています」


循環参照とは、計算式で関数などの参照先がある場合に、自身のセルが含まれていることに起こるエラーを指します。参照先とは、Excelにおいて、あるセル(薄い四角で囲まれている区画それぞれのこと)が、ほかのセルに入力された文字式や数字を受けて表示を変える仕組みです。エクセルの計算では、仮に元々の部分の内容が変わっても自動的に反映する機能があります。数多くの情報を処理する際に、計算結果などは触らずに勝手に変更できる点で便利かもしれません。一方で、何回も使ううちに定義されるはずのセルが説明文にも出てくることがあります。その際には、



●Excel(エクセル)に「1つ以上の循環参照が発生しています」と表示される


ことが、エラーの事例としては多いようです。たとえば、F2~F6にそれぞれ1.2.3.4.5を入力し、H2=SUM(F2:F6)=F2+F3+F4+F5+F6と関数を入力しています。H2には見たところ15が表示されていますが、実際は、F2~F6の合計値が指定されているのです。このとき、F6に改めてF6=SUM(H2:H6)=H2+H3+H4+H5+H6とすると、以下のように循環参照に関する警告が出てきました。先程H2=F2+F3+F4+F5+F6としており、F6に代入すると右辺にも(F2+F3+F4+F5+F6)+H3+H4+H5+H6と未確定であるはずのF6が出てくるためです。このように、何を入力するべきかを指令している際に確定していないそのもののセルを含めることで循環参照が起こることがあります。



○今回の記事での「参照」の意味合い


先ほど、「参照先とは、Excelにおいて、あるセル(薄い四角で囲まれている区画それぞれのこと)が、ほかのセルに入力された文字式や数字を受けて表示を変える仕組みです。」と記載しました。Excelでは、参照先と参照元が逆の認識をしていることもあるようですが、当方の記載では、定義される側を『参照元』、定義に出てくるセル内容を『参照先』としています。



◎エクセルで循環参照に関する警告が出る理由は、関数が多い?



Excelで循環参照に関する理由としては、当然ながら参照が原因ですが、特に関数の利用が多いと言われています。複雑な計算処理や引用ができる点で、手動で行う操作が減るメリットがありますが、その分構造が見えにくいためです。計算式も、単に1回限りの足し算などであればこのような心配はないでしょう。しかし、合計したものをさらに別のセルで使って計算する場合などは、参照が複数回行われることもあります。また、関数を使った処理では、多くの範囲を参照データとして使う事例も多いです(全項目の平均など)。こういったことから、反復計算や関数を使ってExcelのデータを作成する場合には、循環参照に関する警告が出やすくなります。

TechAcademy 無料体験



◎エクセルで循環参照を探す検索・表示方法は?まずはエラーチェックから


エクセルでの循環参照を解消するためには、どの部分に問題があるのかを特定する必要があります。しかし、最近のExcelは、循環参照があることを示唆する表示があった際にボタンをクリックすると、どの部分にあるのかがわからなくなってしまうのです。そこで、エクセルでの循環参照を探す方法を押さえておきましょう。


まずは、エクセル上部にあるタブから『数式』を選択します。中央付近にある「ワークシート分析」の中にある『エラーチェック』の右端▼をクリックしましょう。ここの『循環参照(C)』と記された部分にマウスを合わせると、エラーが起こっているセルが(複数のシートを使っている場合にはシート名も)出てきます。右側に出てきたセルをクリックすると、該当部分がエクセルにも表示されるので、あとは修正すれば完了です。



◎エクセルで循環参照の削除によってエラーを解消or許可する方法、反復計算とは?


循環参照の場所さえわかれば、あとは解消するだけですが、関係式を削除しない方法でもできます。解消する手段をいくつかご紹介しましょう。



●循環参照の参照元を計算式がない別のエクセルシートorセルに移動して表示する


一番簡単なのは、循環参照の参照元(計算結果が表示されるセル)を別のセルに移動させることです。切り取りして貼り付けであれば、単に計算式が別の部分に移り、循環参照を解除できます。循環参照を含むセルを選択して『Ctrl』+『X』で同時押しし、計算式や関数が入っていない別のセルをクリックして 『Ctrl』+『V』で貼り付けましょう。



●Excelで循環参照になっている関数や式を削除する


別のセルに移動させずに、関係式そのものを見直すことも有効です。自身が含まれる結果になっている式のうち一つを変更して、代入を繰り返しても自身のセルが入らないようにします。数値の変更に連動しないのは面倒かもしれませんので、空白のセルに同じ値を入力して、そのセルを参照先として置き換えるのもおすすめです。



●循環参照を許可するエクセルの機能とは:反復計算の設定をオンにする【応用】


基本的に、循環参照が起こっている場合には計算が煩雑になるので、警告が出るように設定されています。しかし、関数が条件を満たすまで計算を続けたい場合もあるかもしれません。その場合には、反復計算の設定をオンにして循環参照を許可するのがおすすめです。『ファイル』を押して「名前を付けて保存」などが出てくる画面に入ります。左側の緑の部分の一番下にある『オプション』⇒「Excelのオプション」内の『数式』と進みましょう。「計算方法の設定」の中にある『反復計算を行う(I)』にチェックを入れます。回数を設定するのであれば『最大反復回数』の部分に回数を入力しましょう。結果は、収束・発散・2種類の値が入れ替わりで出てくる、のいずれかです。

キャリアアップをするならGraspy



◎まとめ


今回は、エクセルで循環参照に関する警告に関して、エラーを探す方法や削除の流れをご紹介しました。循環参照は、他のセルの結果を受ける設定をする際に、自身のセルも参照している場合に起こるエラーのことです。エラーチェックから場所を特定し、関係式を削除するなどして解消しましょう。最後までお読みいただきありがとうございました。

コメント

タイトルとURLをコピーしました