今回は、Excelの「数式の検証」機能を使って、複雑な数式の計算過程を1段階ずつ確認する方法を紹介します。関数をいくつも組み合わせた数式は、結果が想定と違っていても、どこで食い違ったのかを見つけにくいものです。数式の検証を使うと、Excelが数式をどの順番で計算し、途中でどんな値になっているのかを画面で追いかけられます。使い方の手順に加えて、確認するときのコツや注意点もまとめます。
この記事は、Windows版のExcel（Microsoft 365、Excel 2021など）のデスクトップアプリを前提にしています。ボタンの名前は、バージョンや更新時期によって[数式の検証]または[数式の評価]と表示される場合があります。Mac版やWeb版では、利用できる機能や操作が異なることがあります。
数式の検証でできること
数式の検証は、選んだセルの数式を計算される順番どおりに分解して表示する機能です。ダイアログボックスの中で、参照しているセルの値や、関数の途中結果が順番に置き換わっていく様子を確認できます。
たとえば、次のような数式があるとします。
=IF(AVERAGE(B2:B5)>=70,”合格”,”再確認”)
この数式を検証すると、まずAVERAGE関数の部分が平均値に置き換わり、次に「平均値>=70」の比較がTRUEまたはFALSEに置き換わり、最後にIF関数の結果が表示される、という流れで確認できます。結果だけを見ていると分かりにくい途中の判断が見えるため、数式の誤りを探す手がかりになります。
こんなときに役立つ
- IF関数を何重にも組み合わせた数式で、どの条件に当てはまったのかを確かめたいとき
- VLOOKUPやXLOOKUPなどの検索結果が想定と違い、検索値や範囲が正しいか確認したいとき
- 他の人が作った数式を引き継ぎ、どのように計算しているのかを理解したいとき
- エラー値が表示されていて、どの部分でエラーになったのかを特定したいとき
数式の検証の基本手順
- 確認したい数式が入っているセルを1つ選択します。数式の検証は、一度に1つのセルだけが対象です。
- [数式]タブの[ワークシート分析]グループにある[数式の検証]（または[数式の評価]）をクリックします。
- ダイアログボックスの[検証]欄に数式が表示され、次に計算される部分に下線が付きます。
- [検証]ボタンをクリックすると、下線の部分が計算結果に置き換わります。直前に計算された結果は斜体で表示されます。
- [検証]ボタンをくり返しクリックし、数式の最後まで計算を進めます。
- 最初から見直したい場合は[再び開始]、終える場合は[閉じる]をクリックします。
ダイアログボックスのボタン名も、バージョンによって表記が異なることがあります。手順の流れは同じなので、画面の表示に合わせて読み替えてください。
下線と斜体を目印に読む
数式の検証で大切なのは、下線が付いている部分＝次に計算される部分、斜体の部分＝直前に計算された結果という見方です。1回クリックするごとに、どの部分がどんな値になったかを確認しながら進めると、想定と違う値が出た段階に気づきやすくなります。
ステップインとステップアウトで参照先をたどる
数式の中で参照しているセルに、さらに別の数式が入っていることがあります。たとえば、合計を計算したセルを別のセルで参照しているような場合です。
このとき、下線が付いている参照先が数式のセルであれば、[ステップイン]ボタンが使えます。クリックすると、参照先のセルに入っている数式が別の枠に表示され、その数式の中身も確認できます。元の数式に戻るときは[ステップアウト]をクリックします。
ステップインが使えない場合
次のような場合は、[ステップイン]が使えません。
- 同じ参照が数式の中で2回目に出てきたとき
- 数式が別のブックのセルを参照しているとき
別のブックを参照している場合は、参照先のブックを開き、そちらのセルを直接選んで検証すると確認しやすくなります。
検証結果を読むときの注意点
数式の検証は便利ですが、表示のされ方に独特な点があります。あらかじめ知っておくと、表示を誤解せずに済みます。
IF関数やCHOOSE関数では評価されない部分がある
IF関数では、条件の結果に応じて「真の場合」か「偽の場合」のどちらか一方だけが使われます。使われない側の引数は計算されません。また、IF関数やCHOOSE関数を使う数式の一部が評価されず、#N/Aと表示されることがあります。これは数式の誤りとは限らないため、どちらの引数が使われているのかを確かめながら読み進めるのがコツです。
空白のセルは0として表示される
参照しているセルが空白の場合、検証の画面では0として表示されます。「文字が入っているはずのセルが0になっている」といった場合は、参照先が空白になっていないか、参照範囲がずれていないかを確認する手がかりになります。
再計算のたびに値が変わる関数
RAND、RANDBETWEEN、NOW、TODAY、OFFSET、INDIRECT、CELL、INFOなどの関数は、ワークシートが変更されるたびに再計算されます。そのため、検証画面の値とセルの表示が一致しないことがあります。これらの関数を含む数式では、表示の差を誤りと判断しないようにしましょう。
循環参照を含む数式
数式が自分自身のセルを間接的に参照している循環参照の状態では、検証が期待どおりに進まない場合があります。意図せず循環参照になっていないかも、あわせて確認しておくと安心です。
数式の誤りを見つけるためのコツ
期待する値を先にメモしておく
検証を始める前に、「平均はおよそこのくらい」「この条件はTRUEになるはず」と、途中の値の見込みを書き出しておくと、ずれた段階を見つけやすくなります。ただ画面を眺めるよりも、予想と実際の値を比べながら進めるのが効率的です。
数式バーで一部分だけを計算して確かめる
数式バーで数式の一部を選択してF9キーを押すと、選択した部分だけの計算結果を確認できます。確認が終わったら、必ずEscキーを押して元の数式に戻します。Enterキーを押すと、計算結果の値が数式に固定されてしまうため注意が必要です。特定の部分だけをすばやく見たいときは、数式の検証と使い分けると便利です。
トレース矢印と組み合わせる
[ワークシート分析]グループには、参照元や参照先のセルを矢印で示す機能もあります。数式がどのセルを使っているかを矢印で確認してから数式の検証で中身を追うと、表全体の流れと個々の計算の両方を把握しやすくなります。
長い数式は分けて考える
検証してもなお分かりにくいほど長い数式は、途中の計算を別のセルに分けて書くと、確認も修正もしやすくなります。Microsoft 365などLET関数が使える環境では、途中の計算に名前を付けて数式を整理する方法もあります。数式の検証で構造を理解したうえで、見直しやすい形に整えておくと、後から読む人の助けにもなります。
文字列と数値の違いに目を向ける
検証画面では、文字列は「”」で囲まれて表示されます。数値のつもりで入力した値が「”100″」のように表示されていたら、そのセルは文字列として扱われています。検索関数で一致しない、比較の結果が想定と違う、といった問題の原因になりやすいため、表示の形にも注目すると誤りを見つけやすくなります。
数式の検証を使った確認の流れ（例）
最後に、検索関数の結果が「#N/A」になってしまった場合を例に、確認の流れをまとめます。
- エラーが出ているセルを選択し、[数式の検証]を開きます。
- [検証]をクリックし、検索値の参照がどんな値に置き換わったかを確認します。
- 検索値が文字列か数値か、余分なスペースが含まれていないかを見ます。
- 検索範囲の参照が意図した範囲になっているかを確認します。
- 検索値が別の数式で作られている場合は、[ステップイン]でその数式の中身を確認します。
- 原因が分かったら[閉じる]をクリックし、元のデータや数式を修正します。
このように、計算を1段階ずつたどることで、「どこまでは正しく、どこから想定と違うのか」を切り分けられます。
まとめ
Excelの数式の検証を使うと、複雑な数式の計算過程を順番に確認できます。ポイントを振り返ります。
- [数式]タブの[ワークシート分析]グループにある[数式の検証]（または[数式の評価]）から使う
- 下線は次に計算される部分、斜体は直前の計算結果として読む
- 参照先に数式がある場合は[ステップイン]で中身をたどれる
- IF関数やCHOOSE関数の#N/A表示、空白セルの0表示、再計算される関数の値の違いを理解しておく
- 期待する値のメモ、F9キーでの部分計算、トレース矢印と組み合わせると原因を見つけやすい
数式の結果が合わないときに、いきなり数式を書き直すのではなく、まず計算の過程を確かめる習慣を付けておくと、修正の手間を減らせます。