【Excel】ISFORMULAで数式の有無を判定し、計算列の上書きを見つける

同じ100と表示されていても、セルの中身は計算式かもしれませんし、直接入力された数値かもしれません。計算列がいつの間にか値に置き換わっていないか確認したいときは、ISFORMULA関数で「数式が入っているか」を調べられます。

返るのはTRUEまたはFALSEです。TRUEは数式があるという意味で、計算結果が正しいことまでは示しません。エラーになる式でもTRUEです。この区別を押さえたうえで、確認用の列を作る方法と、数式がなくなったセルを条件付き書式で目立たせる方法を紹介します。

1. セル参照を渡して数式の有無を調べる

A2を調べるなら、空いているB2へ次の式を入力します。A2自身へ入れるのではなく、判定結果を置く別セルを用意してください。A2の内容が数式ならTRUE、直接入力した値や文字列ならFALSEになります。

=ISFORMULA(A2)

試すときはA2に=50+50、A3に数値の100を入力します。どちらも画面上は100ですが、B2へ入れた式をB3にコピーすると、B2はTRUE、B3はFALSEです。下へコピーすることで、参照もA2からA3へ移ります。

式の文字列を直接渡して評価する関数ではありません。例えば文字として保存した「=50+50」が計算できるかを解析するのではなく、指定セルが数式を持つかを確認します。セル参照ではない値を引数に指定してエラーになった場合は、調べたいセルの番地を渡しているか見直します。

2. 空欄やエラーの見た目に惑わされない

A4に=""を入力すると、見た目は空欄でも数式があるため=ISFORMULA(A4)はTRUEです。A5に=1/0を入れた場合も、結果は#DIV/0!ですが判定はTRUEになります。何も入力していないA6はFALSEです。

A7へ先頭にアポストロフィを付けて'=50+50と入力すると、数式に見える文字列として保存され、ISFORMULAの結果はFALSEです。画面にイコールが見えるかどうかでは判定できません。先頭記号を取り除く操作は内容を計算式へ変える可能性があるので、単なる見た目の整理として行わないでください。

このように、数式の有無、空白かどうか、エラーかどうかは別の問いです。エラーを調べるならISERROR、完全な空セルを調べるならISBLANKなど、目的に合う判定を使います。ISFORMULAがTRUEだから計算済みで問題なし、と扱うと、参照間違いやエラーを見落とします。

判定列をフィルターでTRUEだけ、FALSEだけに絞れば、数式と固定値が混在する範囲を調べやすくなります。ただし、固定値が正しい列もあります。入力欄まで一律に数式へ戻そうとせず、どの列が計算列のはずなのかを先に決めます。

3. 数式があるセルを条件付き書式で色分けする

例えばD2:D20が計算列なら、その範囲をD2を起点に選択します。［ホーム］→［条件付き書式］→［新しいルール］を開き、［数式を使用して、書式設定するセルを決定］を選びます。次の式を入力して塗りつぶし色を設定します。

=ISFORMULA(D2)

［ルールの管理］で適用先が=$D$2:$D$20になっていることを確認します。ルールのD2は相対参照なので、D3ではD3、D4ではD4の数式を調べます。ここを$D$2にすると、全セルがD2だけを基準に色付けされてしまいます。

判定が一行ずれる場合は、ルールの基準セルと適用先の先頭行を合わせます。既存の条件付き書式がある場合は、ルールの優先順位や［条件を満たす場合は停止］の設定も確認します。色が見えないことだけで、ISFORMULAがFALSEと返したとは判断できません。

取り消すときは［ルールの管理］から今回追加したルールだけを削除します。シート全体のルールを一括で消すと、もともとあった警告色や集計用の表示まで失います。作成前の状態を残し、変更対象を限定してください。

4. 計算列の値への置き換えと削除を見つける

上書きの点検では「数式がない」を条件にするほうが役立ちます。A列に商品名がある行ではD列に計算式が必要、という表を考えます。D2:D20に対し次のルールを設定すると、商品名があるのにD列が数式でないセルを目立たせられます。

=AND($A2<>"",NOT(ISFORMULA($D2)))

数値100へ置き換えたセルだけでなく、Deleteで消した空セルも対象になります。逆に、A列が空で使っていない行は対象外です。A列に商品名、B列に単価、C列に数量、D列に金額を置いた例では、D2が=B2*C2なら対象外、D3が直接入力の100なら対象、D4が空欄でもA4に商品名があれば対象です。

行全体を色付けしたい場合は、同じ式の適用先を=$A$2:$D$20にします。列の$は判定するA列とD列を固定し、行番号は各行へ動かします。適用先だけを広げて$を外すと、横のセルでは別の列を調べることになるので注意してください。

条件に「D列が空欄ではない」を加えると、誤って数式を消したセルが見つからなくなります。使っている行かどうかは商品名などの必須項目で判断し、計算列の空欄も検出する設計にします。A列自体を消した行まではこの条件では検出できないため、必須項目のチェックは別に必要です。

5. 色が付いたセルを確認してから直す

色が付いたら、まずその行が本当に数式を必要とする行か確認します。例外的な調整額を手入力する設計なら、FALSEでも誤りとは限りません。一方、通常の計算行なら、数式バーで中身を見て、隣の行や変更前のファイルと比較します。

単に上の式をコピーすればよいとも限りません。小計行、税率が異なる行、別の参照先を使う行が混じる場合があります。B列の単価とC列の数量から金額を求める行だと確認できたら、正しい式を入れ直し、参照する行番号も見ます。復元した後は元の数値との違いを調べてください。

直前に値貼り付けや削除をしたなら、まず［元に戻す］が使えるか確認します。保存して開き直した後など、戻せない場合はバックアップや利用環境のバージョン履歴から比較します。ISFORMULAは以前の式を保存する機能でも、変更者や変更時刻を記録する機能でもありません。

また、間違った式が残っているセルはTRUEのままです。例えばD2が本来=B2*C2なのに=B3*C3となっていても、数式なしの警告は出ません。重要な表では式の有無に加え、参照先、合計、代表行の計算結果も点検します。

6. 検出と上書き防止を組み合わせる

条件付き書式は気付きやすくするための表示で、入力を禁止する機能ではありません。上書きを減らすなら、入力してよいセルと計算セルを分け、必要に応じて計算セルをロックしてシートを保護します。保護は誤編集を抑えるために使い、機密情報を守る強いセキュリティ機能と同一視しないでください。

運用前には作業用のコピーで三つを試します。計算セルを100へ置き換える、計算セルを削除する、正しい数式を戻す、という操作です。前の二つで警告色が付き、数式を戻すと色が消えることを確認します。使っていない行に不要な警告が出ないかも見ます。

試験後は変更した値と式を元へ戻し、判定用の列や条件付き書式を保存します。行を追加したときはルールの適用先に新しい行が入っているか確認します。数式があるか、式が正しいか、上書きを防げるかを分けて管理すると、ISFORMULAを過信せず、計算表の日常点検に生かせます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】