AIが提案したExcel数式を空白・0・文字列・日付でテストする方法

011行で正しくても数式全体が正しいとは限らない理由

AIチャットアシスタントが提案したExcel数式は、通常のデータでは正しく見えても、セルが空白、分母が0、数値の代わりに文字列が入っている、日付がExcelのシリアル値として保存されている、といった場合に異なる動作をすることがあります。こうした問題は#DIV/0!や#VALUE!のような目立つエラーだけではありません。見た目にはもっともらしい数値が返っても、業務上は誤った結果である場合があります。

以下は合成した例です。A列に旧値、B列に新値を入れ、C列で(新値 - 旧値) / 旧値として変化率を計算するとします。業務ルールは、空白なら空白のまま、分母が0または数値以外ならCHECK、日付は通常の測定値として計算しない、というものです。

02意図的に例外値を入れた小さなシートを作る

実際のブックへ数式を適用する前に、小さなテストシートを作ります。通常値とよくある失敗ケースを一緒に確認できるよう、次の行を用意します。

最初の2行は通常動作の基準です。(120 - 100) / 100 = 0.20なので20%、(50 - 50) / 50 = 0なので0%です。残りの行は通常データというより、数式の失敗条件を確認するためのテストです。

03AIに数式を依頼した後、その前提を確認する

AIチャットアシスタントには、A2からB2への変化率を計算し、エラーなら空白にする、といった形で依頼できます。

この数式は100から120、50から50では正しく動きます。しかしIFERRORは異なる問題をすべて同じ空白に隠します。分母が0、文字列入力、その他の数式エラーが同じ結果になるため、確認すべき問題を区別できません。さらに重要なのは、Excelの日付は内部的にシリアル番号として保存されることです。A2とB2が実際のExcel日付なら、減算や除算が実行され、明確なエラーではなく数値の変化率が返る場合があります。

つまり、計算に成功したことだけでは、入力値が意図したデータ型であることを証明できません。

04数式を明示的にして全行をテストする

ここで定義したルールに合わせるなら、もう少し明示的な数式にできます。

この数式は空白、数値以外の入力、分母が0の場合を分けて扱います。そのため2行目から7行目までは意図したルールどおりになります。C列の数値を20%のように表示したい場合はPercentage形式を設定します。

ただし8行目では重要な限界が分かります。Excelの日付は内部では数値なので、実際の日付セルにもISNUMBERはTRUEを返します。そのため、この防御的な数式でも日付を数値として計算できます。一般的な数式だけで、ある数値シリアルが意図した測定値なのか、誤って入った日付なのかを数値だけから確実に判断することはできません。

05Pythonで意図したルールを照合する

2つ目の確認として、合成ケースをrow,old_value,new_value列を持つformula_cases.csvとして保存します。2026-10-01と2026-10-08はそのまま残します。次の標準ライブラリだけを使うスクリプトは、意図したルールを独立して適用します。空白はblank、ISO形式の日付はCHECK、数値以外はCHECK、旧値が0ならCHECK、通常の数値なら変化率を計算します。

from pathlib import Path
from decimal import Decimal, InvalidOperation
from datetime import date
import csv
import sys

INPUT_PATH = Path("formula_cases.csv")
OUTPUT_DIR = Path("outputs")
REPORT_PATH = OUTPUT_DIR / "formula_check_report.txt"

if not INPUT_PATH.is_file():
    sys.exit("Missing input file: formula_cases.csv")

if OUTPUT_DIR.exists():
    sys.exit("Stop: outputs folder already exists. Remove or rename it manually first.")


def is_iso_date(value):
    try:
        date.fromisoformat(value)
        return True
    except ValueError:
        return False


def check_change(old_text, new_text):
    old_text = old_text.strip()
    new_text = new_text.strip()

    if old_text == "" or new_text == "":
        return "blank"

    if is_iso_date(old_text) or is_iso_date(new_text):
        return "CHECK"

    try:
        old = Decimal(old_text)
        new = Decimal(new_text)
    except InvalidOperation:
        return "CHECK"

    if old == 0:
        return "CHECK"

    change = (new - old) / old * Decimal("100")
    return f"{change:.2f}%"


results = []

with INPUT_PATH.open("r", encoding="utf-8", newline="") as file:
    reader = csv.DictReader(file)
    required = {"row", "old_value", "new_value"}

    if reader.fieldnames is None or not required.issubset(reader.fieldnames):
        sys.exit("CSV must contain: row, old_value, new_value")

    for record in reader:
        result = check_change(record["old_value"], record["new_value"])
        results.append(
            f"Row {record['row']}: "
            f"old={record['old_value']!r}, "
            f"new={record['new_value']!r} -> {result}"
        )

OUTPUT_DIR.mkdir()
REPORT_PATH.write_text("\n".join(results) + "\n", encoding="utf-8")
print(f"Created: {REPORT_PATH}")

7つの合成行で期待するPythonの結果は、順に20.00%、0.00%、blank、CHECK、CHECK、CHECK、CHECKです。Excelでエラー表示が出るかどうかだけではなく、各行の結果を比較してください。

06AIが提案した数式を確認するときのよくあるミス

• 通常の1行だけを試し、そのまま数千行へ数式をコピーします。
• 欠損データと不正データを区別せず、IFERRORですべての失敗を隠します。
• 分母が0の場合に何を返すかという業務ルールを定義しません。
• 数値のように見えるセルや数値形式のセルが、必ず意図した数値データだと考えます。
• Excelの日付が内部では数値として保存され、計算に使われることを見落とします。
• 表示されたパーセントだけを見て、内部値が0.20なのか20なのか、別の尺度なのかを確認しません。
• 期待結果を先に定義せず、画面上のエラーが消えるまで数式だけを修正します。

Pythonを使う利点は、同じ業務ルールを別の方法で独立して実装できることです。Excel数式をそのまま文字単位で再現するだけでは意味がありません。両方が同じ誤った前提を持っていれば、照合しても同じミスを繰り返します。

07最終的な数式テスト用チェックリスト

1. 数式を採用する前に、意図する計算ルールを文章で書きます。
2. 通常値、空白、0、文字列、日付のテスト行を作ります。
3. 単純な期待値は手計算します。
4. エラーを空白にするのか、CHECKにするのか、処理を止めるのかを決めます。
5. 日付などExcel特有のデータ型がどう表現されるか確認します。
6. 結果が重要なら、独立した計算方法でルールを照合します。
7. その後で数式を全データへ適用します。

重要なのは、数式の検証が構文だけの問題ではなく、データ型と業務ルールの問題でもあることです。AIが作った数式は実装案として扱い、例外値テストで本当に必要な動作と一致しているか確認してください。

実行・検証の記録

2026-09-21 · hand-checked example · Python 3.12

• (120 - 100) / 100 = 0.20、つまり20%であることを手計算で確認しました。
• (50 - 50) / 50 = 0、つまり0%であることを手計算で確認しました。
• 合成ルールでは空白入力がblankになることを確認しました。
• 旧値が0の場合は分母に使えないためCHECKになることを確認しました。
• pendingとn/aは数値以外の入力なのでCHECKになることを確認しました。
• Python側のルールがISO日付2026-10-01と2026-10-08を明示的に検出し、その行をCHECKにする構成であることを確認しました。
• スクリプトがformula_cases.csvを読み、outputs/formula_check_report.txtだけを書き出し、outputsフォルダがすでに存在する場合は停止する構成であることを確認しました。

サイト全体の執筆・検証方針

参考資料

説明と例は独自に作成しました。関連する動作や概念は、以下の公式資料で確認できます。

• Python csv ドキュメント ↗ (New tab)
• Python decimal ドキュメント ↗ (New tab)
• Python datetime ドキュメント ↗ (New tab)