Excelの受注案件表を修正したいけど構造が不明！ 数式の参照元・参照先を知る方法

数式を直す前に「参照元」と「参照先」を切り分ける

　ここでは、受注案件をまとめた表を例にします。下部には案件ごとの明細があります。上部には「消費税率」「大口割引率」「割引適用額」「送料無料ライン」という設定値と、「売上合計」「割引額合計」「消費税額合計」「送料無料件数」という集計項目を用意してあります。また、［商品マスタ］シートには商品コードと商品名、単価をまとめた一覧があります。

下部には案件ごとの明細があります（②）。上部には「消費税率」「大口割引率」「割引適用額」「送料無料ライン」という設定値（③）と、「売上合計」「割引額合計」「消費税額合計」「送料無料件数」という集計項目を用意してあります（④）。

［商品マスタ］シートには商品コードと商品名、単価をまとめてあります（⑤）

　それぞれの表の役割を考えると、1つのセルを変更することで、複数のセルに影響がある可能性がありますね。例えば、設定項目の「消費税率」を変更すれば、明細の消費税額や請求金額に影響します。「送料無料ライン」を変更すれば、送料の判定が変わります。

　ここで意識したいのが、「参照元」と「参照先」の違いです。［参照元のトレース］では、選択したセルの数式が“参照するセル”を調べられます。一方、［参照先のトレース］では、選択したセルを“参照しているセル”を調べられます。例えば、消費税率の値を使っている消費税額のセルなどが参照先になります。つまり、このセルを変更した時の影響範囲を確認したい場合に使います。

数式が参照するセルを調べる［参照元のトレース］

　「参照元」のわかりやすい例として、明細の「送料」列の数式が参照するセルを調べてみましょう。セルJ9に入力された送料の数式は「=IF(G9>=$B$6,0,800)」であり、セルG9とB6が参照元のセルとなります。

　さらに、小計（G9）の数式「=E9*F9」はセルE9（単価）とF9（数量）を参照しており、セルE9の単価を求める数式「=XLOOKUP(C9,商品マスタ!$A$2:$A$7,商品マスタ!$C$2:$C$7)」はセルC9（商品コード）と商品マスタシートを参照していることも調べられます。

「送料」列のセルを選択して（⑥）、［数式］タブ（⑦）にある［参照元のトレース］（⑧）をクリックします

セルJ9の数式の「参照元」となるセルから矢印が2本表示されます（⑨）。セルJ9の数式は「=IF(G9>=$B$6,0,800)」なので、セルG9（小計）とB6（送料無料ライン）を参照していることがわかります

もう一度［参照元のトレース］（⑩）をクリックすると、セルG9に入力されている小計の数式「=E9*F9」が参照する単価（E9）と数量（F9）からの矢印が表示されます（⑪）

もう一度［参照元のトレース］（⑫）をクリックします。セルE9に入力された単価の数式「=XLOOKUP(C9,商品マスタ!$A$2:$A$7,商品マスタ!$C$2:$C$7)」が参照する商品コード（C9）と商品マスタシートからの矢印が表示されます（⑬）。別のシートを参照している場合はアイコン（⑭）が表示されます

　何度か［参照元のトレース］をクリックすると、参照するセルからの矢印が次々と表示されます。これにより、どのセルを参照しているのかを視覚的に確認できます。参照するセルのないセルにたどり着くと、それ以上矢印は表示されなくなります。

　数式が正しいセルを参照しているか、コピーした数式の参照元がずれていないかといったことを確認できます。なお、［トレース矢印の削除］をクリックすると、すべての矢印が削除されます。

［トレース矢印の削除］（⑮）をクリックします

参照元の矢印が削除されました

値や数式の影響範囲を確認する［参照先のトレース］

　値や数式を変更した時の影響範囲（参照先）を確認してみましょう。例えば、セルB3の「消費税率」の値は、明細の消費税額を求める数式で使われています。値を参照する数式が入力されたセルに向かって矢印が表示され、消費税率を変更した時に、どのセルに影響があるのかを確認できます。

「消費税率」の入力されたセルB3（⑯）を選択して、［参照先のトレース］（⑰）をクリックすると、セルB3を参照する数式が入力されたセルに向かって矢印が表示されます

　消費税率といった値を参照する数式だけでなく、数式の結果を参照する数式も調べられます。どのセルに影響するのか、セルの変更前に参照先を確認しておくといいでしょう。もし、関係のないセルへ矢印が表示された場合は、数式の参照先を見直してください。

セルI9を選択して（⑱）、［参照先のトレース］（⑲）をクリックします

セルI9の数式「=ROUND((G9-H9)*$B$3,0)」の結果は、セルE5（消費税額合計）とセルK9（請求金額）から参照されていることがわかりました（⑳）

　また、不要に思える列やセルを削除する前にも、［参照先のトレース］は役立ちます。参照先が表示される場合、そのセルは別の数式で使われています。見た目だけで不要と判断して削除すると、後続の計算結果が崩れる可能性があるため注意が必要です。

　数式が参照しているセルを調べたい場合は［参照元のトレース］、値や数式を変更・削除する前に影響範囲を確認したい場合は［参照先のトレース］と考えると使い分けやすくなります。

　数式の意味を理解するだけでなく、表全体のつながりを確認してから修正することで、思わぬ計算ミスや影響範囲の見落としを防げます。数式が多い表を扱うときは、いきなり編集せずに、まずは矢印で関係性をたどってみましょう。