AI가 제안한 Excel 수식을 빈칸, 0, 텍스트, 날짜로 테스트하는 방법

01한 행에서 맞는 수식도 전체 데이터에서는 틀릴 수 있는 이유

AI 채팅 도우미가 제안한 Excel 수식은 일반적인 데이터에서는 정상적으로 작동해 보여도, 셀이 비어 있거나 분모가 0이거나 숫자가 들어가야 할 곳에 텍스트가 있거나 날짜가 Excel 일련번호로 저장된 경우에는 전혀 다른 결과를 낼 수 있습니다. 이런 경우는 #DIV/0!이나 #VALUE!처럼 눈에 띄는 오류로만 나타나는 것이 아닙니다. 겉보기에는 그럴듯한 숫자가 나오지만 논리적으로는 잘못된 경우도 있습니다.

아래 예시는 합성 예제입니다. A열에는 이전 값, B열에는 새 값이 있고, C열에서는 (새 값 - 이전 값) / 이전 값으로 변화율을 계산한다고 가정합니다. 업무 규칙은 빈칸이면 빈칸을 유지하고, 분모가 0이거나 숫자가 아닌 값은 CHECK로 표시하며, 날짜는 일반 측정값처럼 계산하지 않는 것입니다.

02의도적으로 엣지케이스를 넣은 작은 시트 만들기

실제 통합문서에 수식을 적용하기 전에 작은 테스트 시트를 만듭니다. 정상 값과 자주 발생하는 예외를 한 번에 볼 수 있도록 다음과 같은 행을 준비합니다.

처음 두 행은 정상 동작을 확인하기 위한 기준입니다. (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으로 의도한 규칙을 교차검증하기

두 번째 검증으로 합성 테스트 데이터를 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가 제안한 수식을 검토할 때 자주 하는 실수

• 정상적인 한 행만 테스트한 뒤 수천 행에 바로 채워 넣습니다.
• 누락 데이터와 잘못된 데이터를 구분하지 않고 IFERROR로 모든 실패를 숨깁니다.
• 분모가 0인 경우 어떤 결과를 낼지 업무 규칙을 별도로 정의하지 않습니다.
• 숫자처럼 보이거나 숫자 형식으로 표시된 셀이 항상 의도한 숫자 데이터라고 가정합니다.
• Excel 날짜가 내부적으로 숫자로 저장되어 산술 연산에 참여할 수 있다는 점을 놓칩니다.
• 표시된 백분율만 비교하고 실제 내부 값이 0.20인지 20인지 다른 배율인지 확인하지 않습니다.
• 기대 결과를 먼저 정의하지 않고 화면의 오류가 사라질 때까지 수식만 계속 수정합니다.

Python은 같은 업무 규칙을 독립적으로 구현할 수 있다는 점에서 유용합니다. Excel 수식을 문자 그대로 그대로 복제해서는 안 됩니다. 두 구현이 같은 잘못된 가정을 공유하면 교차검증도 동일한 오류를 반복할 수 있습니다.

07최종 수식 테스트 체크리스트 사용하기

1. 수식을 받아들이기 전에 의도한 계산 규칙을 자연어로 먼저 적습니다.
2. 정상값, 빈칸, 0, 텍스트, 날짜 테스트 행을 만듭니다.
3. 단순한 기대 결과는 직접 손으로 계산합니다.
4. 오류를 무조건 숨길지, 빈칸으로 둘지, CHECK로 표시할지 구분합니다.
5. 날짜와 기타 Excel 특수 데이터 유형이 어떻게 표현되는지 확인합니다.
6. 결과가 중요하다면 독립적인 계산으로 규칙을 교차검증합니다.
7. 그다음에 전체 데이터셋에 수식을 적용합니다.

핵심은 수식 검증이 단순한 문법 문제가 아니라 데이터 유형과 업무 규칙의 문제라는 점입니다. AI가 만든 수식은 초안 구현으로 보고, 엣지케이스 테스트를 통해 실제로 필요한 동작과 일치하는지 확인해야 합니다.

실행·검증 기록

ChatGPT Python tool · Python 3.x · standard library pathlib, decimal, datetime, csv, sys

• 첫 번째 실행은 성공적으로 완료되어 outputs/formula_check_report.txt를 생성했다.
• 생성된 결과는 정확히 다음과 같았다: Row 2 = 20.00%, Row 3 = 0.00%, Row 4 = blank, Rows 5-8 = CHECK.
• 결과는 문서에 제시된 순서인 20.00%, 0.00%, blank, CHECK, CHECK, CHECK, CHECK와 일치했다.
• ISO 날짜 행 2026-10-01부터 2026-10-08까지는 올바르게 감지되어 CHECK로 표시되었다.
• 원본 formula_cases.csv는 실행 후에도 바이트 단위로 완전히 동일했다.
• 두 번째 실행은 outputs가 이미 존재하여 명시된 메시지와 함께 중단되었고, 이전 보고서는 덮어쓰지 않았다.
• 스크립트는 Python 표준 라이브러리 모듈만 사용하며 제공된 합성 CSV에 대해 명시된 업무 규칙과 일치하게 동작했다.

참고 출처

설명과 예제는 직접 작성했습니다. 관련 동작과 개념은 아래 공식 자료에서 확인할 수 있습니다.

• Python csv 문서 ↗ (New tab)
• Python decimal 문서 ↗ (New tab)
• Python datetime 문서 ↗ (New tab)