エクセルで「1つ以上の循環参照が発生しています」と出たことがあるかもしれません。Excelでは循環参照が消えないとフリーズするため、循環参照の探し方や解消できる方法を知っておきたいですよね。循環参照を削除する方法は複数考えられ、エクセルの設定を変えて解除できるやり方などもあるのが特徴です。今回は、エクセルでの循環参照の探し方や消し方をご紹介します。
エクセルでの「1つ以上の循環参照が発生しています」は、反復計算によってエラーが出ている表示です。循環参照とは、あるセルで立式して他のセルを値を利用している場合に、使っているセルを全てたどるといずれかで自身のセルの値を利用している状態のことです。言葉では分かり辛いでしょうから、例を挙げてみましょう。数字が並んでいて、L6でSUM関数を使って合計値を求めています。しかし、数式は「=SUM(L2:L10)」で値がわからないL6の値を使っているため計算できません。
特に複数の数式を使っているときには循環参照になりやすいです。こちらには、H2=SUM(F2:F10)・F8=SUM(H2:H10)の2種類の式が入力されています。循環参照なのは、H2の値を代入するとF8=H2+SUM(H3:H10)=SUM(F2:F10)+SUM(H3:H10)で右辺にもF8が出てきてしまい、値を定義することができないためです。
このように、参照元をたどっていき、式を代入していくと自身のセルが出てくる場合、エクセルが反復計算のエラーが出ているとみなして警告を表示します。エクセルで循環参照を無視しようとすると、計算できずに0が表示されることもあるかもしれません。Excelの循環参照でフリーズするのを避けるにはどうすれば良いのかを次の項目で見ていきましょう。
エクセルで循環参照を解消するには、該当箇所を探す必要があります。場合によっては警告メッセージを消した後に、以下のように青の矢印が表示されることもありますが、必ずではないため、探し方を把握しておくとスムーズに解決できるでしょう。
Excelでの循環参照を探し方はまず、エクセル上部にあるタブから『数式』を選択します。「ワークシート分析」内の『エラーチェック』の右端▼をクリックしましょう。『循環参照(C)』と記された部分にマウスを合わせると、エラーが起こっているセルが(複数のシートを使っている場合にはシート名も)出てきます。循環参照が複数ある場合には全てが表示されるわけではないため、次の項目以降でご紹介する「循環参照の削除」あるいは「Excelに循環参照を許可する」操作と探す動きを繰り返しましょう。
エクセルで循環参照を削除する方法として最も簡単なのは、当該のセルを別のセルに移すことです。単に計算式が別の部分に移り、循環参照をエクセルから解除できます。循環参照を含むセルを選択して『Ctrl』+『X』を押し、計算式や関数が入っていない別のセルをクリックして 『Ctrl』+『V』で貼り付けましょう。
エクセルで循環参照に関する警告が出ているのは、言い換えると式の右辺にも左辺にも同じセルが出てきているからです。そこで、別のセルに移動させずに、関係式そのものを見直すことも有効でしょう。数式や関数をやめて、Excelでセル参照を直接入力にするだけでも循環参照の削除につながります。また、引数を変更した際に連動しないのが面倒な場合は、空白のセルに同じ値を入力し、そのセルを参照するのもおすすめです。
基本的に、循環参照が起こっている場合には計算が煩雑になるので、警告が出るように設定されています。しかし、条件を満たすまで計算を続けたい場合もあるかもしれません。その際には、エクセルの循環参照を解除して表示させないように設定を変えることも可能です。Excelで循環参照があったときに無効にする方法をみていきましょう。
エクセルにある反復計算の設定を変えてエラーが出ないようにするやり方です。『ファイル』を押して「名前を付けて保存」などが出てくる画面に入ります。左側の緑の部分の一番下にある『オプション』⇒「Excelのオプション」内の『数式』と進みましょう。「計算方法の設定」の中にある『反復計算を行う(I)』にチェックを入れます。これでExcelへ循環参照を許可することができました。回数を設定するのであれば『最大反復回数』の部分に回数を入力しましょう。結果は、収束・発散・2種類の値が入れ替わりで出てくる、のいずれかです。
今回は、エクセルで循環参照を探す方法や、循環参照の消し方などをご紹介しました。Excelで循環参照に関する警告が出ている場合には、数式をたどると自身のセルが引数になるのが原因です。探す・削除あるいは表示させない設定をすることを繰り返して、Excelの循環参照を解消していきましょう。最後までお読みいただきありがとうございました。
関連記事