Excelで依存関係をトレースする2つの簡単な方法

Excelでデータを扱う際、依存関係のトレース(Trace Dependents)の使い方を知っておくことは非常に重要です。あるセルの値が他のどのセルに依存しているのかを把握できれば、ワークブック全体の構造理解やトラブルシューティングが格段にしやすくなります。この記事では、Excelで依存関係をトレースする2つの簡単で実用的な方法を解説します。

練習用ワークブックをダウンロードして、実際に手を動かしながら試してみてください。

Excelで依存関係をトレースする2つの方法

ここでは、「ABC商事の上半期売上」というサンプルデータセットを使用します。データは3列構成で、列B・C・Dにはそれぞれ月・売上高・3か月平均売上高が入力され、データ行は7行です。この記事では、標準機能の数式の検証(Formula Auditing)を使う方法と、VBAコードを実行する方法の2通りを紹介します。

方法1: 数式の検証機能を使って依存関係をトレースする

まずは、Excelに標準搭載されている数式の検証機能を使う最も基本的な方法から見ていきましょう。リボンから数クリックだけで操作できる手軽な方法です。以下の手順に従ってください。

手順:

• まず、依存関係を確認したいセルを選択します。ここでは例としてC5を選択します。

• 次に、リボンの「数式」タブを選択します。「数式の検証」グループが見つかります。

• 「数式の検証」をクリックすると「依存先のトレース」オプションが表示されるので、それをクリックします。

• すると、下の画像のようにセルC5の依存関係が矢印で表示されます。この図から、D5とC11がC5に依存していることが読み取れます。

• 同様の手順をC6、C7、C8、C9、C10に対しても実行すると、以下の図のような結果になります。

• 矢印を消したい場合は、リボンの「数式」タブ→「数式の検証」→「矢印の削除」を選択します。

• さらに、別シートとの連携ケースも確認してみましょう。「Formula Auditing (Sales)」シートのセルC5と、「Formula Auditing (Revenue)」という別シートのセルB5の間に参照リンクを作成します。

• 再びセルC5に対して同じ手順で依存関係をトレースすると、新たな黒い斜め矢印が現れます。これは、別シートのセルがこのセルに依存していることを示しており、この例ではそのセルがB5です。

• 同様にC6〜C10についても手順を繰り返すと、以下の図のような結果になります。

• 最後に、前述した手順に従って矢印を削除しておきましょう。

方法2: VBAコードを実行して依存関係をトレースする

続いて、VBAマクロを使った方法を紹介します。短いコードを実行するだけで依存関係を一括表示できるため、大量のデータを扱う場合に特に便利です。

手順:

• この方法でも、前の方法と同じデータセットを使用します。

• Alt + F11キーを押して、VBE(Microsoft Visual Basic for Applications)のウィンドウを開きます。

• メニューの「挿入」から「標準モジュール」を選択してクリックします。

• 下図のようなコード入力画面が表示されます。

• 次のコードをコピーして貼り付けます。

Sub TraceDependents()
Dim xM As Range
Dim xN As Range
Dim xT As String
On Error Resume Next
xT = ActiveWindow.RangeSelection.Address
Set xM = Application.InputBox("Please select the data range:", "Find Trace Dependents", xT, , , , , 8)
Set xM = Application.Union(xM, ActiveSheet.UsedRange)
If xM Is Nothing Then Exit Sub
For Each xN In xM
xN.ShowDependents
Next
End Sub

• F5キーを押してコードを実行します。
• 下図のようなダイアログボックスが表示されます。

• データ範囲としてC5〜D11を選択します。

• その後、下図のようなウィンドウが表示されたら、OKをクリックします。

• これで、下図のように結果が表示されます。矢印を消す場合は、方法1で説明した手順に従ってください。

覚えておくと便利なショートカットキー

参照関係のトレースには、いくつか便利なショートカットキーがあります。主なものは以下の通りです。

• Ctrl + [ – アクティブセルの直接の参照元(依存元)セルを選択します。
• Ctrl + Shift + [ – 直接・間接のすべての参照元セルを選択します。
• Ctrl + ] – アクティブセルの直接の依存先セルを選択します。
• Ctrl + Shift + ] – 直接・間接のすべての依存先セルを選択します。

まとめ

この記事では、Excelで依存関係をトレースするための2つの方法(数式の検証機能とVBAコード)を紹介しました。どちらもすぐに試せるので、複雑なワークブックの分析やメンテナンスにぜひ役立ててください。ご不明な点があれば、コメント欄からお気軽にお問い合わせください。