엑셀 데이터 오류 자동 탐지! 파이썬으로 유효성 검사 자동화하기 (완성 코드 공개)
"담당자가 빈 칸으로 제출한 데이터가 있는데 나중에야 발견했다", "금액 열에 음수가 들어가 있어서 합계가 틀렸다", "날짜 형식이 제각각이라 정렬이 안 된다"…
엑셀 데이터를 눈으로 직접 검토하면 시간도 오래 걸리고 실수도 납니다. 특히 데이터가 수백 행 이상이라면 사람이 모든 오류를 찾아내기는 사실상 불가능합니다. 파이썬을 한 번만 세팅해두면 필수 항목 누락, 중복 데이터, 숫자 범위 초과, 날짜 형식 오류, 허용되지 않는 값까지 자동으로 탐지하고 오류 보고서를 만들 수 있습니다.
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가지 오류 유형 자동 탐지, 오류목록·원본데이터 포함 보고서 엑셀 자동 생성
이 코드를 응용하면 이메일 형식 검사, 전화번호 패턴 검사, 두 열 간의 논리 관계 검사 등 더 복잡한 규칙도 추가할 수 있습니다.
