関数・数式の動作を確認する方法

第19回と第20回の連載では、少しだけ処理が複雑な数式・関数について解説してきた。特に「参照元のセルにも数式が入力されている・・・」といった処理を行うときは、各セルでの処理を順番に検証しながら動作を確認していく必要がある。このような場合に活用できるのが「参照元のトレース」や「数式の検証」といった機能だ。

入力した数式・関数をそのまま表示するには？

他のセルで計算した結果を使って「さらに別の計算」を実行できることもExcelの特長の一つといえる。簡単な例としては、金額のセル範囲を合計して「小計」を求め、その数値に0.1を掛け算して「消費税」を求める、さらに「小計」と「消費税」を足し算して「税込の合計金額」を求める、などの処理が挙げられるだろう。

このようにセル参照が何層にも重なってくると、状況によっては頭が混乱してしまったり、途中でミスを犯してしまったりするケースがある。そこで、数式や関数の動作を順を追って確認する方法を学んでおくとよい。

Excelには「参照元のトレース」や「数式の検証」など、数式・関数の動作を確認するためのツールが用意されている。今回は、これらの使い方を紹介していこう。

数式の表示、参照元のトレース、数式の検証について

ここでは、第19回の連載で作成した計算ツールを例に詳しい操作手順を解説していこう。この計算ツールは、「駅名」と「XX時からXX時まで」の条件を指定すると、その条件を満たすデータ（利用者数）の合計と平均を自動計算してくれるものだ。

第19回の連載で作成した計算ツール

まだ第19回の連載を読んでいない方は、先に第19回を一読してから本稿に戻っていただけると内容を理解しやすくなるとだろう。

まずは「数式の表示」の使い方から紹介していこう。通常、それぞれのセルには「数式や関数の計算結果」が表示されている。この状態のままでは「各セルでどんな処理をしているのか？」を確認しにくい場合もあるだろう。

このような場合は「数式」タブにある「数式の表示」をクリックしてONにすると、各セルに入力した数式・関数を、そのまま表示することが可能となる。

数式の表示（1）

数式の表示（2）

「数式・関数の記述にミスがないか？」などを確認するときに活用できるので、いちど試してみるとよいだろう。なお、もういちど「数式の表示」をクリックしてOFFにすると、通常の「計算結果の表示」に戻すことができる。

参照しているセルを視覚的に表示する「参照元のトレース」

各セルに入力した「数式・関数の記述」を確認したいときは、そのセルをダブルクリックするのも効果的だ。すると以下の図に示したような画面表示になり、「数式・関数の記述」だけでなく「参照元のセル」も色で確認できるようになる。

ダブルクリックを使った参照元の確認

なお、この方法で数式・関数の記述を確認した後は、そのまま「Enter」キーを押して元の画面に戻すのが基本だ。というのも、「Enter」キーを押さずに適当なセルをクリックしてしまうと、「そのセルのセル参照」が数式に追加されてしまうからだ。つまり、数式の記述が勝手に書き換えられてしまうことになる。念のため、注意しておこう。

ダブルクリックによる確認は手軽な反面、十分に機能してくれないケースもある。というのも、色で確認できるのは「直接の参照元」だけで「参照元の参照元」までは確認できないからだ。

たとえば、以下の図に示した例の場合、関数SUMが「どのセル範囲を合計しているのか？」までを確認することはできない。

参照元を確認しづらい場合

このような場合は「参照元のトレース」を使うと、より詳細に「参照元」を可視化できる。具体的には、数式（関数）を入力したセルを選択し、「参照元のトレース」をクリックすればよい。すると、以下の図のように「参照元のセル」が青色のトレース矢印で表示される。

「参照元のトレース」を表示した例

数式（関数）が直接参照しているK5やJ11、J12といったセルはもちろん、その結果として生成されるセル範囲（関数SUMが実際に合計するセル範囲）も確認することが可能となる。

なお、表示された「トレース矢印」を削除して元の画面に戻したいときは、「トレース矢印の削除」をクリックすればよい。

トレース矢印の削除

「数式の検証」を使ったステップ形式の動作確認

「参照元のセル」に入力されている数式・関数も含めて、順を追って処理手順を確認したいときは「数式の検証」を活用するとよい。

今度は、第20回の連載で紹介した「交差演算子を使ったデータのピックアップ」を例に、使い方を紹介していこう。こちらも、第20回の連載を一読してから本稿を読み進めていただけると幸いだ。

それでは、具体的な操作手順を解説していこう。数式（関数）を入力したセルを選択し、「数式の検証」をクリックする。

「数式の検証」の開始

すると、以下の図のようなダイアログが表示され、セルに入力されている数式（関数）が表示される。今回の例では、数式中にある「C4」の部分に下線が描画されている。ここが最初に検証される部分となる。「検証」ボタンをクリックすると・・・、

セルに入力されている数式の表示

C4セルのデータ（"可住地面積"）が数式に代入され、下線が「関数INDIRECT」の部分に移動する。さらに「検証」ボタンをクリックすると・・・、

「参照元のデータ」が代入された数式

関数INDIRECTが実行され、その結果として「$D$7:$D$53」というセル範囲が表示される。ちなみに、この例では「D7:D53」のセル範囲に「可住地面積」という名前を定義している。よって、関数INDIRECTの結果は「$D$7:$D$53」（D7:D53）となる。

「関数INDIRECTの結果」が代入された数式

続いては、「ステップイン」の使い方を紹介していこう。現時点では「G4」の部分に下線が描画されている（上図）。この状態で「ステップイン」ボタンをクリックすると、ダイアログの表示が下図のように変化し、G4セルに入力されている数式（関数）を確認できる。また、下線の位置は、関数MATCH内の「B4」に移動する。

「ステップイン」で参照元セルの記述を確認（1）

さらに「ステップイン」ボタンをクリックすると、B4セルに入力されているデータ（"宮城県"）をダイアログ内で確認できる。

「ステップイン」で参照元セルの記述を確認（2）

これを参照先の数式に反映させるには「ステップアウト」をクリックすればよい。すると、「B4」の部分が"宮城県"という記述に置き換わり、下線が「関数MATCH」の部分に移動する。この状態のまま「検証」ボタンをクリックして、関数MATCHを実行することも可能だ。

「ステップアウト」で参照元のデータを代入

今回の例では、"宮城県"のデータは「B7:B53」のセル範囲の上から4番目にある。よって、関数MATCHの結果は「4」となる。これを代入した形に数式が置き換わる。さらに「検証」を続けても構わないし、計算結果を「ステップアウト」して参照先の数式に反映してもよい。

「関数MATCHの結果」が代入された数式

「ステップアウト」をクリックした場合は「"C"&4+6」の数式が実行され、その結果である"C10"が参照先の数式に代入される。

「ステップアウト」で結果を代入

このようにステップ形式で各部の処理内容を確認していける機能が「数式の検証」となる。念のため、各ボタンの役割を“おさらい”しておこう。いずれも「下線が表示されている部分」が動作確認の対象となる。

◆「検証」ボタン
　セル参照、演算、関数などを実行する。

◆「ステップイン」ボタン
　参照元のセルに入力されている数式を表示する。

◆「ステップアウト」ボタン
　計算結果を参照先に代入して、参照元セルの表示を閉じる。

今回、例として示した数式の検証を最終段階まで進めていくと、以下の図のように「2つのセル範囲」を半角スペース（交差演算子）で区切った記述になる。

検証を最終段階まで進めた様子

絶対参照を示す「$」（ドル）を含めた表示になるため少し見にくいが、要は「D7:D53」と「C10:H10」のセル範囲を「半角スペース」で区切っている、という記述になる。この状態で「検証」ボタンをクリックすると、交差演算子（半角スペース）が実行され、最終的な結果である「315,489」という数値が表示される。