今回は、Excelの数式監査を使い、エラーを追跡して原因を見つける方法を紹介します。
数式監査が役立つ場面
Excelで集計表や管理表を作っていると、数式の結果が合わない、エラー値が出る、参照先が分からないといった問題が起こることがあります。数式が短ければ目で確認できますが、複数のセルやシートを参照している場合は原因を探すのに時間がかかります。
数式監査の機能を使うと、参照元や参照先を確認したり、エラーの原因を追ったりできます。複雑な表を直すときは、いきなり数式を書き換えるのではなく、どのセルが原因かをたどることが大切です。
エラー値の種類を確認する
Excelのエラーには種類があります。表示されているエラー値を見れば、原因を絞り込める場合があります。
- #DIV/0!: 0または空白で割っている可能性
- #N/A: 検索値が見つからない可能性
- #VALUE!: 文字列と数値の扱いが合っていない可能性
- #REF!: 参照先セルが削除された可能性
- #NAME?: 関数名や名前定義が認識されていない可能性
エラー値を見たら、まず式全体を直そうとせず、原因になりやすい部分を確認します。検索関数なら検索値と検索範囲、割り算なら分母、参照エラーなら削除された行や列を見ます。
エラーチェックを使う
Excelにはエラーを確認する機能があります。エラーが出ているセルを選択すると、原因の候補や関連する操作が表示される場合があります。候補をそのまま採用する前に、表の目的と合っているか確認します。
エラーを非表示にするだけでは、根本原因が残ることがあります。IFERROR関数で見た目を整える場合も、まず元のエラーがなぜ出ているのかを確認してから使うと安全です。
参照元をたどる
数式の結果がおかしいときは、参照元のトレースを使います。選択した数式セルがどのセルを使っているかを矢印で確認できます。
材料セルを確認する
参照元をたどると、数式が使っている値を確認できます。計算式が正しくても、元データに空白、文字列、古い値、範囲外のデータが入っていると結果は合いません。
確認するときは、次の順番で見ると整理しやすくなります。
- 数式セルを選ぶ
- 参照元のトレースを表示する
- 矢印の先のセルを確認する
- 値と表示形式を見る
- 必要に応じてさらに参照元をたどる
別シートを参照している場合は、参照先のシート名も確認します。コピーした表では、古いシートを参照したままになっていることがあります。
参照先を確認する
あるセルを修正する前には、そのセルがどこで使われているかを確認します。参照先のトレースを使うと、選択したセルを利用している数式を探せます。
単価、税率、基準値、日付など、複数の数式に影響するセルを直す場合は、参照先の確認が役立ちます。変更後にどの計算へ影響するかを事前に把握できます。
参照先がないはずのセルに矢印が出る場合は、過去に作った数式が残っている可能性があります。逆に、使われているはずのセルに参照先がない場合は、数式が固定値に置き換わっているかもしれません。
数式の検証で途中結果を見る
長い数式では、どの部分で想定と違う結果になっているか分かりにくいことがあります。数式の検証を使うと、式の中の計算を段階的に確認できます。
IF関数、XLOOKUP、INDEXとMATCH、日付計算、文字列操作などでは、参照セルが正しくても条件判定が合っていない場合があります。数式の検証で途中結果を見ると、どの条件で分岐しているか確認しやすくなります。
検証中に分からない部分があれば、式の一部を別セルに取り出して確認する方法もあります。確認用セルを使う場合は、作業後に削除し、最終版に余分な計算が残らないようにします。
エラー追跡の注意点
数式監査は便利ですが、すべての参照を完全に分かりやすく表示できるわけではありません。INDIRECT関数のように文字列で参照を作る式、外部ブック参照、名前定義を使った式では、追加の確認が必要です。
名前定義を使っている場合は、名前の管理で参照範囲を確認します。外部ブックを参照している場合は、リンク先ファイルが存在するか、古いファイルを見ていないかを確認します。
エラーを見つけたら、すぐに削除するのではなく、なぜそのエラーが出たかをメモしておくとよいです。同じ表を繰り返し使う場合、原因が分かっていれば次回の修正が早くなります。
エラーを再発させない工夫
エラーを直した後は、同じ原因が起きにくいように表を整えます。参照範囲がずれていたならテーブル機能を使う、検索値の表記ゆれが原因なら入力規則を使う、空白が原因なら必須入力欄を分かりやすくするなど、原因に合わせて対策を考えます。
数式が複雑すぎる場合は、途中計算を分ける方法もあります。1つのセルに長い式を入れるより、確認用の列で段階的に計算したほうが、後から原因を追いやすい場合があります。提出用の見た目を優先する場合は、補助列を非表示にするなどの整理もできます。
修正後の確認
エラーを修正したら、該当セルだけでなく、関連する合計欄やグラフも確認します。1つの参照先を直したことで、別の数式の結果が変わることがあります。参照先のトレースを使い、影響範囲を見てから完了にすると安心です。
引き継ぎ用のメモ
複雑な表では、エラーが起きやすい箇所や更新時の注意点をメモしておくと役立ちます。どのシートを更新するか、どの列を触らないか、エラーが出た場合にどこを見るかを残せば、次の担当者も対応しやすくなります。
確認順を固定する
エラー調査では、確認順を固定すると迷いにくくなります。まずエラー値の種類を見て、次に参照元をたどり、元データの値と表示形式を確認します。その後、参照先を見て修正の影響を把握します。
複数のエラーが出ている場合は、先頭のセルだけでなく、共通する原因がないかを確認します。同じ列でまとめてエラーが出ているなら、参照範囲やコピーした数式が原因かもしれません。1セルずつ直す前に、表全体の傾向を見ると効率よく進められます。
まとめ
Excelの数式監査は、エラーの原因を追跡するために役立ちます。エラー値の種類を確認し、参照元と参照先をたどり、必要に応じて数式の検証で途中結果を見ます。
数式を直すときは、見えているエラーだけを消すのではなく、元データ、参照範囲、条件式を確認することが大切です。原因セルを特定してから修正することで、Excel表の信頼性を保ちやすくなります。