엑셀 데이터 오류 자동 탐지! 파이썬으로 유효성 검사 자동화하기

6월 21, 2026

엑셀 데이터 오류 자동 탐지! 파이썬으로 유효성 검사 자동화하기 (완성 코드 공개)

"담당자가 빈 칸으로 제출한 데이터가 있는데 나중에야 발견했다", "금액 열에 음수가 들어가 있어서 합계가 틀렸다", "날짜 형식이 제각각이라 정렬이 안 된다"…

엑셀 데이터를 눈으로 직접 검토하면 시간도 오래 걸리고 실수도 납니다. 특히 데이터가 수백 행 이상이라면 사람이 모든 오류를 찾아내기는 사실상 불가능합니다. 파이썬을 한 번만 세팅해두면 필수 항목 누락, 중복 데이터, 숫자 범위 초과, 날짜 형식 오류, 허용되지 않는 값까지 자동으로 탐지하고 오류 보고서를 만들 수 있습니다.

1단계: 준비물 설치

파이썬이 설치되어 있어야 합니다. 없다면 python.org에서 최신 버전을 받아 설치하세요. 설치 시 반드시 "Add Python to PATH"에 체크해야 합니다.

터미널(윈도우: CMD 또는 파워셸)을 열고 아래 명령어를 실행하세요:

pip install pandas openpyxl

pandas는 엑셀 데이터를 읽고 각 열별로 유효성 조건을 검사하는 작업을 처리합니다. openpyxl은 .xlsx 파일을 읽고 오류 보고서를 저장하는 데 사용합니다.

💡 이 코드로 탐지하는 오류 유형: 필수 열 빈 값(Null) 탐지, 중복 행 탐지, 숫자 범위 초과(최솟값·최댓값), 허용되지 않는 값(목록 외 값), 날짜 형식 오류, 문자열 길이 초과까지 6가지 유효성 검사를 한 번에 처리합니다.

2단계: 완성 코드

아래 코드를 그대로 복사해서 메모장에 붙여넣고, validate.py로 저장하세요. 저장 시 파일 형식은 "모든 파일", 인코딩은 UTF-8로 설정합니다.

import pandas as pd
from pathlib import Path
from datetime import datetime

# ① 설정
INPUT_PATH  = r"C:\Users\내이름\Desktop\거래처데이터.xlsx"      # ← 검사할 엑셀 경로
REPORT_PATH = r"C:\Users\내이름\Desktop\유효성검사_보고서.xlsx"  # ← 보고서 저장 경로

# ② 유효성 검사 규칙 정의
RULES = {
    # 필수 항목 (빈 값 허용 안 됨)
    "required": ["거래처명", "담당자", "매출금액", "날짜"],

    # 중복 검사 기준 열 (이 열들의 조합이 중복이면 오류)
    "unique": ["거래처명", "날짜"],

    # 숫자 범위 검사: {열이름: (최솟값, 최댓값)}
    "range": {
        "매출금액": (0, 100_000_000),   # 0원 이상 1억 이하
        "수량":     (1, 9999),
    },

    # 허용 값 목록 검사: {열이름: [허용값 리스트]}
    "allowed": {
        "납부상태": ["완납", "미납", "부분납"],
        "지역":    ["서울", "부산", "대전", "인천", "광주"],
    },

    # 날짜 형식 검사: {열이름: 형식}
    "date_format": {
        "날짜": "%Y-%m-%d",
    },

    # 문자열 최대 길이 검사: {열이름: 최대길이}
    "max_length": {
        "거래처명": 50,
        "담당자":  20,
    },
}

# ③ 데이터 읽기
df = pd.read_excel(INPUT_PATH, engine="openpyxl", dtype=str)
df = df.where(df.notna(), None)   # NaN → None 변환
print(f"  ✔ 데이터 로드 완료: {len(df)}행 × {len(df.columns)}열")

errors = []   # 오류 목록

# ④ 필수 항목 검사
for col in RULES.get("required", []):
    if col not in df.columns:
        continue
    null_rows = df[df[col].isna() | (df[col].astype(str).str.strip() == "")]
    for idx in null_rows.index:
        errors.append({
            "행번호": idx + 2,
            "열이름": col,
            "오류유형": "필수값 누락",
            "오류값": "(빈 값)",
            "검사규칙": "필수 입력 항목",
        })

# ⑤ 중복 검사
unique_cols = [c for c in RULES.get("unique", []) if c in df.columns]
if unique_cols:
    dupes = df[df.duplicated(subset=unique_cols, keep=False)]
    for idx in dupes.index:
        errors.append({
            "행번호": idx + 2,
            "열이름": " + ".join(unique_cols),
            "오류유형": "중복 데이터",
            "오류값": " / ".join(str(df.loc[idx, c]) for c in unique_cols),
            "검사규칙": f"{unique_cols} 조합 중복 불가",
        })

# ⑥ 숫자 범위 검사
for col, (min_val, max_val) in RULES.get("range", {}).items():
    if col not in df.columns:
        continue
    numeric = pd.to_numeric(df[col].astype(str).str.replace(",", ""), errors="coerce")
    out_of_range = df[numeric.notna() & ((numeric < min_val) | (numeric > max_val))]
    for idx in out_of_range.index:
        errors.append({
            "행번호": idx + 2,
            "열이름": col,
            "오류유형": "범위 초과",
            "오류값": df.loc[idx, col],
            "검사규칙": f"{min_val:,} ~ {max_val:,}",
        })

# ⑦ 허용 값 목록 검사
for col, allowed in RULES.get("allowed", {}).items():
    if col not in df.columns:
        continue
    invalid = df[~df[col].isin(allowed) & df[col].notna()]
    for idx in invalid.index:
        errors.append({
            "행번호": idx + 2,
            "열이름": col,
            "오류유형": "허용되지 않는 값",
            "오류값": df.loc[idx, col],
            "검사규칙": f"허용값: {allowed}",
        })

# ⑧ 날짜 형식 검사
for col, fmt in RULES.get("date_format", {}).items():
    if col not in df.columns:
        continue
    for idx, val in df[col].items():
        if val is None or str(val).strip() == "":
            continue
        try:
            datetime.strptime(str(val).strip(), fmt)
        except ValueError:
            errors.append({
                "행번호": idx + 2,
                "열이름": col,
                "오류유형": "날짜 형식 오류",
                "오류값": val,
                "검사규칙": f"형식: {fmt}",
            })

# ⑨ 문자열 길이 검사
for col, max_len in RULES.get("max_length", {}).items():
    if col not in df.columns:
        continue
    too_long = df[df[col].astype(str).str.len() > max_len]
    for idx in too_long.index:
        errors.append({
            "행번호": idx + 2,
            "열이름": col,
            "오류유형": "길이 초과",
            "오류값": df.loc[idx, col],
            "검사규칙": f"최대 {max_len}자",
        })

# ⑩ 결과 출력 및 보고서 저장
print(f"\n  검사 완료: 총 {len(errors)}개 오류 발견")

if errors:
    df_errors = pd.DataFrame(errors)
    with pd.ExcelWriter(REPORT_PATH, engine="openpyxl") as writer:
        df_errors.to_excel(writer, sheet_name="오류목록", index=False)
        df.to_excel(writer, sheet_name="원본데이터", index=False)
    print(f"  ✔ 오류 보고서 저장 → {REPORT_PATH}")

    print(f"\n  오류 유형별 집계:")
    for err_type, count in df_errors["오류유형"].value_counts().items():
        print(f"    {err_type}: {count}건")
else:
    print("  ✅ 오류 없음! 데이터가 모두 유효합니다.")

print(f"\n✅ 완료!")

3단계: 실행 방법

코드 ① 설정의 INPUT_PATH에 검사할 엑셀 파일 경로를 입력합니다.
코드 ② 유효성 검사 규칙의 각 항목을 본인 데이터에 맞게 수정합니다. 필요 없는 규칙은 해당 항목을 비워두거나 삭제하면 됩니다.
터미널에서 아래 명령어로 실행합니다:

python validate.py

정상 실행 시 터미널에 이렇게 출력됩니다:

  ✔ 데이터 로드 완료: 120행 × 6열

  검사 완료: 총 8개 오류 발견
  ✔ 오류 보고서 저장 → C:\Users\내이름\Desktop\유효성검사_보고서.xlsx

  오류 유형별 집계:
    필수값 누락: 3건
    허용되지 않는 값: 2건
    범위 초과: 2건
    날짜 형식 오류: 1건

✅ 완료!

보고서 엑셀을 열면 오류목록 시트에 행번호, 열이름, 오류유형, 오류값, 검사규칙이 정리되어 있어 담당자가 어느 셀을 수정해야 하는지 바로 확인할 수 있습니다.

4단계: 자주 발생하는 오류와 해결법

오류 1: 날짜 형식 검사에서 정상 날짜도 오류로 탐지됨

엑셀에서 날짜가 "2026-01-05" 형태가 아닌 "2026/01/05" 또는 "26-01-05"로 저장된 경우입니다. RULES["date_format"]의 형식을 실제 데이터 형식과 일치하도록 수정하세요. 예: "%Y/%m/%d"

오류 2: 숫자 열이 쉼표 포함 텍스트로 저장되어 범위 검사 오류

코드에 이미 str.replace(",", "")로 쉼표를 제거하는 처리가 포함되어 있습니다. 그래도 오류가 발생한다면 숫자 앞뒤에 공백이 있는지 확인하세요. .str.strip()을 추가하면 해결됩니다.

오류 3: 중복 검사에서 의도한 중복이 오류로 잡힘

RULES["unique"]의 열 조합을 조정하세요. 예를 들어 같은 거래처가 날짜가 다르면 정상인 경우, "날짜" 열을 unique 기준에 추가하면 날짜가 다른 동일 거래처는 중복으로 잡히지 않습니다.

응용: 오류가 있는 행을 엑셀에서 빨간색으로 강조하기

오류 보고서 외에 원본 엑셀에서 오류 행을 빨간색으로 강조 표시하고 싶다면 아래 코드를 보고서 저장 부분 앞에 추가하세요.

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

# 오류 행 번호 목록
error_rows = set(df_errors["행번호"].tolist())

wb = load_workbook(INPUT_PATH)
ws = wb.active
red_fill = PatternFill(fill_type="solid", fgColor="FFD7D7")

for row_num in error_rows:
    for cell in ws[row_num]:
        cell.fill = red_fill

highlight_path = Path(INPUT_PATH).parent / (Path(INPUT_PATH).stem + "_오류강조.xlsx")
wb.save(highlight_path)
print(f"  ✔ 오류 강조 파일 저장 → {highlight_path}")

💡 이전 게시글과 결합하면: 엑셀 자동 병합 편으로 여러 파일을 합친 뒤 이 코드로 유효성 검사를 실행하면 데이터 수집부터 품질 검증까지 완전 자동화할 수 있습니다. 텔레그램 자동 발송 편과 결합하면 오류 발견 시 즉시 알림을 받는 것도 가능합니다.

핵심 요약

준비물: Python + pandas + openpyxl 설치 (pip install pandas openpyxl)
설정: INPUT_PATH, RULES(유효성 검사 규칙) 수정
실행: 터미널에서 python validate.py 입력
결과: 6가지 오류 유형 자동 탐지, 오류목록·원본데이터 포함 보고서 엑셀 자동 생성

이 코드를 응용하면 이메일 형식 검사, 전화번호 패턴 검사, 두 열 간의 논리 관계 검사 등 더 복잡한 규칙도 추가할 수 있습니다.

IT 생활 꿀팁 - 스마트폰·PC 활용법