Excelでシートの不具合をなんとかする3つの方法(エラーと循環参照)

Excelでは計算式を作成中に想定していない値が出てしまい、計算式を見直すのですが今一つ分かりにくい場合があります。また、合計した範囲が合っているか、本当に確認したいときもあるでしょう。

そんな時に使えるExcelシートの不具合を見つける方法を解説します。

Excel計算式で循環参照を見つける方法

Excel計算式で循環参照を見つける場合の操作方法は「数式」タブの中の「ワークシート分析」グループの中の「エラーチェック」ボタンの下向き三角をクリックし、「循環参照」にマウスカーソルを合わせると、シート内の循環参照になっているセルが表示されるので、それをクリックすると循環参照が発生しているセルにジャンプできます。

循環参照とは

さて、循環参照とはなんでしょうか。
一番簡単な循環参照はセルA1に「=A1」という計算式が入っているケースです。Excelの計算式は自動的に変化のあったセルの値を参照し計算をやりなおす「再計算」の機能があります。このケースではセルA1の値を見て、その結果をA1に表示し、セルA1の値に変化があったのでセルA1の計算をやり直す、ということをします。「計算が循環してしまう参照を持った式」という意味で循環参照と呼びます。

循環参照が見つからない

もうちょっと複雑な循環参照だと見つかりにくいですね。
セルA1に「=B1」、セルB1に「=A1」のように入っていると、2つのセルの間で循環参照をしてしまいます。また、これが別シートを参照している式だと、もっと見つけにくくなります。

セルに表示される緑の三角を消す

セルに表示される緑の三角を消す場合の操作方法はそのセルやセル範囲をクリックして表示される、警告の三角形をクリックし、「エラーを無視する」をクリックすると消えます。

範囲で選択する場合はエラーがないセルからドラッグで範囲選択すると警告の三角形が表示されないので、エラーのあるセルからドラッグし範囲選択するようにしましょう。

Excelのセルになぜ緑の三角が表示されるのか

Excelのセルに表示される緑の三角形は、その計算式がエラーになっていたりエラーかもしれないと判断されたりしたときに表示されています。「エラーインジケータ」と呼ばれます。エラーであれば表示してもいいのですが、かもしれないでも表示されるので邪魔になるときがあります。ただ、本当のエラーである場合もあるので一概に無視していいのものではありません。緑の三角が表示されたら、その原因によって修正するのか無視するのかを決めます。

Excelのセルの緑の三角の意味は?

緑の三角が表示されるエラーの種類はたくさんありますが、そのうち、Excelが「これはエラーの可能性がある」と確定ではないのですが警告をしてくれるエラーには次のようなものがあります。
矛盾した数式 周辺の数式の入力されている法則と違う。上のセルの計算式まではA列を参照してきたのにB列を参照しているなど。
数値が文字列として保存されています 数値に見える値が文字列として入力されている。「’」を入力した後に入力した文字は文字列になるという場合など。

これらは自分でわざとそうしている場合は無視してかまわないでしょう。それ以外にセルに「#N/A」など計算エラーが出た時にも表示されますが、これは把握していないで表示されたのであれば修正の必要があります。

計算式のエラーの原因を突き止める

計算式のエラーの原因を突き止めるために計算式で参照しているセルを明確にする一番簡単な方法は、セルをダブルクリックし数式を表示させれば、どのセルを参照しているか色分けで明確に表示されます。

計算式が参照しているセルを明確にする

計算式が参照しているセルを明確にする方法は、数式の入っているセルを選択し、「数式」タブの中の「ワークシート分析」グループの「参照元のトレース」をクリックします。

すると、参照元を表すセルと矢印が青い線で表示されます。これで計算式の修正がやりやすくなります。

この青い線は、「数式」タブの中の「ワークシート分析」グループの「トレース矢印」の削除で消すことができます。また「参照先のトレース」では、そのセルがどのセルに影響しているかも調べることができます。

まとめ

Excelでよくある困ったことを大きく「循環参照」「エラーインジケータ」「参照元」の3つの考え方で解説しました。

なにより、自分が想定していないことが目の前で起きたら慌ててしまうと思いますが、想定していないことが起きたのは、間違った操作をしたということですので、その原因をしっかり突き止めることが重要です。

詳しくないから原因なんて、と思うかもしれませんが、Excelを使うということは、不具合が起きた時に原因を探り、納得できる答えを見つけて、修正するということができることですので、この記事を参考にスキルアップを目指しましょう。

また、思いがけずエラーインジケータが表示されたときは、それはわざとやった操作で起きているのかどうかもしっかり判断できる力も重要です。

コメント

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