概要
「どこがおかしいのか分からない…」
「セルには #DIV/0! としか出ていないけど、式の中のどこでミスってるの？」
エラーの“原因”を探すには、
セルの中に入っている「数式そのもの」を文字として見える形にすると楽になります。
そのとき使えるのが FORMULATEXT 関数です。
FORMULATEXT は、セルに入っている数式を そのまま文字列として取り出す関数 です。
これを使うと、
- エラーになっているセルの式を一覧表示
- コピペで貼った式が本当に同じか比較
- どのセルだけ参照先がズレているかチェック
といった“数式レベルのデバッグ”がやりやすくなります。
FORMULATEXT の基本
FORMULATEXT の書式と動き
書式はとてもシンプルです。
=FORMULATEXT(参照)
参照したセルに「数式」が入っている場合
→ その数式を文字列として返します。
参照したセルが「値のみ（数値・文字列など）」の場合
→ #N/A エラーを返します（数式がないから）。
例えば、A2 に次の式が入っているとします。
=IFERROR(C2/B2, 0)
このとき、別のセル（たとえば D2）に
=FORMULATEXT(A2)
と書くと、D2 には
=IFERROR(C2/B2, 0)
という「式そのものの文字列」が表示されます。
「セルの中身（計算結果）」ではなく
「セルの裏側（数式）」を一覧で見たいときに使う関数です。
エラーセルの式を一覧で確認する基本パターン
エラーになっているセルの式を横に表示する
例えば、B列にたくさんの計算式があり、
一部がエラーになっているとします。
C列に FORMULATEXT を入れて、式を表示してみます。
C2 に次の式を書きます。
=FORMULATEXT(B2)
これを下方向にコピーすると、
各行の B列の数式が C列にずらっと並びます。
こうすると、
- どの行の数式が他と違っているか
- エラー行と正常行で式がどう違うか
を“文字”として比較しやすくなります。
特に、VLOOKUP や INDEX/MATCH の参照範囲が微妙にズレている場合など、
目で見て「ここだけ $ の付け方がおかしい」といったことに気付きやすくなります。
FORMULATEXT で「式のコピペミス」を見抜く
左右の列で本当に同じ式が入っているかチェック
例えば、B列とC列に「同じ式を入れたつもり」だけど、
なぜか結果が違う、ということがあります。
そのとき、D列とE列に FORMULATEXT を使って、
D2: =FORMULATEXT(B2)
E2: =FORMULATEXT(C2)
と表示させてみると、
式が本当に同じかどうか、文字として比較できます。
見た目の結果だけを見ていても分からない違い（絶対参照・相対参照のズレなど）が
FORMULATEXT であぶり出されます。
FILTER や条件付き表示と組み合わせて「エラー式だけ抜き出す」
ISERROR と組み合わせるイメージ（動的配列対応版）
動的配列対応の Excel なら、
FILTER と組み合わせて「エラーセルの式だけ一覧化」もできます。
例えば、A2:A20 に数式が入っていて、
エラーになっているセルの数式だけ一覧にしたい場合。
B2 に次のような式を書くイメージです。
=FILTER(FORMULATEXT(A2:A20), ISERROR(A2:A20))
これで、
- A2:A20 の中でエラーになっているセルだけ
- そのセルの数式を一覧で表示
といったことができます。
※環境によっては配列数式の扱いが異なる場合がありますが、考え方としては
「FORMULATEXT(範囲)」で数式の一覧を作り、
「ISERROR(範囲)」でエラーセルを判定し、
FILTER でエラーの行だけ抽出
という流れです。
FORMULATEXT を使うときの注意点・コツ
1. 数式がないセルは #N/A になる
FORMULATEXT は「数式が入っているセル」専用です。
参照先が
- 手入力の値だけのセル
- 空白セル
の場合は #N/A になります。
したがって、「計算式だけが入っている列」や
「ここは全部数式にしている」という範囲に対して使うのが向いています。
2. 参照先が別ブックの場合
別ブック参照など、状況によっては FORMULATEXT が働かないケースもあります。
基本的には「同じブック内での数式確認」に使う、ぐらいの意識でいると扱いやすいです。
例題
問題1
A2 に次の数式が入っています。
=IFERROR(C2/B2, 0)
この数式そのものを文字列として B2 に表示したいとき、
B2 にはどんな式を書けばよいでしょうか。
=FORMULATEXT(A2)
問題2
B列（B2:B10）にさまざまな数式が入っています。
各セルにどんな数式が入っているかを C列（C2:C10）に一覧表示したいです。
C2 に書くべき式を書いてください。
=FORMULATEXT(B2)
この式を C10 までコピーします。
問題3
A2:A20 に数式が入っていて、一部がエラーになっています。
エラーになっているセルの数式だけを B列に一覧表示したいとします。
動的配列対応の Excel を想定し、B2 に書く FILTER × FORMULATEXT × ISERROR の式例を書いてください。
=FILTER(FORMULATEXT(A2:A20), ISERROR(A2:A20))
問題4
B列と C列に「同じ式を入れたつもり」ですが、結果が異なります。
B2 と C2 に入っている数式を D2 と E2 に文字として表示して比較したいとき、
それぞれのセルにはどんな式を書けばよいでしょうか。
D2: =FORMULATEXT(B2)
E2: =FORMULATEXT(C2)
問題5
A2:A10 のうち、数式が入っているセルについてだけ式を確認し、
値しか入っていないセルや空白セルでは空白を表示したいとします。
B2 に書く式の一例を、IF と FORMULATEXT を組み合わせて書いてください。
=IF(ISFORMULA(A2), FORMULATEXT(A2), "")
※ISFORMULA 関数は「そのセルに数式が入っているか」を判定します。
まとめ
FORMULATEXT は、
「セルの結果」ではなく「セルの数式そのもの」を文字列で見せるための関数です。
エラー位置を特定する、という意味では
- エラーになっているセルの数式を横や別シートに一覧表示する
- 正常行と比較して、参照範囲や固定の違いを見つける
- FILTER や ISERROR と組み合わせて、“エラー式だけ” 抜き出す
といった使い方が非常に効果的です。
数式のデバッグは、“結果”だけ見ていても限界があります。
一歩踏み込んで「式そのものを一覧で眺める」感覚を、
FORMULATEXT でぜひ体験してみてください。