무엇을 다루나요 - 현장 데이터에는 잘못된 날짜, 음수 수량, 존재하지 않는 코드가 섞이기 쉽습니다. 이 글에서는 날짜 형식과 범위, 수량의 유효 구간, 코드 존재 여부를 각각 검사하고, 세 검사를 묶어 최종 판정을 만드는 방법을 안내드립니다. 스크린샷 없이 수식과 CSV만으로 바로 재현하실 수 있도록 샘플 파일과 필드 설명을 함께 제공합니다.
샘플 데이터와 결과 받기
- 원본 입력 샘플: 09_data_validation_sample.csv
- 검증 결과 포함본: 09_data_validation_with_flags.csv
- 코드표 예시 제품 목록: 02_products.csv
샘플 데이터 설명
원본 09_data_validation_sample.csv
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
date |
입력 날짜 | 2025-07-33 |
잘못된 날짜 사례 포함 |
sku |
제품 코드 | P999 |
코드표에 없는 값 포함 |
qty |
수량 | -1 |
음수나 공란 사례 포함 |
결과 포함본 09_data_validation_with_flags.csv
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
date |
입력 날짜 | 2025-07-02 |
원본 유지 |
sku |
제품 코드 | P002 |
원본 유지 |
qty |
수량 | 3 |
원본 유지 |
is_valid_date |
날짜 유효성 | TRUE |
형식과 하한일자 충족 여부 |
qty_valid |
수량 유효성 | TRUE |
0 이상 1000 이하 |
sku_exists |
코드 존재 여부 | TRUE |
제품 코드표 기준 |
final_status |
최종 판정 | OK |
세 플래그 모두 TRUE면 OK 그 외 REVIEW |
검증 정책의 핵심을 먼저 정합니다
- 날짜는 YYYY-MM-DD 형식만 허용하며 2025-01-01 이후만 유효로 간주합니다.
- 수량은 0 이상 1000 이하인 숫자만 허용합니다. 공란은 무효입니다.
- 코드는 코드표에 존재해야 합니다. 존재하지 않으면 누락으로 간주합니다.
- 최종 판정은 세 검사가 모두 통과하면 OK 그 외는 REVIEW로 정합니다.
Excel 공식을 그대로 사용해 보세요
아래 예시는 표 형태로 작업한다는 가정입니다. 열 이름은 대괄호 표기입니다.
-- 1. 날짜 유효성 is_valid_date
-- 엄격한 형식 검사와 하한일자 동시 적용
=LET(
s, [@date],
oklen, LEN(s)=10,
hy1, MID(s,5,1)="-",
hy2, MID(s,8,1)="-",
y, --MID(s,1,4),
m, --MID(s,6,2),
d, --MID(s,9,2),
try, IFERROR(DATE(y,m,d), ""),
strict, IF(try="",
FALSE,
TEXT(try,"yyyy-mm-dd")=s
),
AND(oklen, hy1, hy2, strict, try>=DATE(2025,1,1))
)
-- 2. 수량 유효성 qty_valid
=AND(ISNUMBER([@qty]), [@qty]>=0, [@qty]<=1000)
-- 3. 코드 존재 여부 sku_exists (products라는 표가 있다고 가정)
=COUNTIF(products[sku], [@sku])>0
-- 또는 =ISNUMBER(XMATCH([@sku], products[sku]))
-- 4. 최종 판정 final_status
=IF(AND([@is_valid_date], [@qty_valid], [@sku_exists]), "OK", "REVIEW")
Google Sheets에서는 이렇게 바꿔 쓰시면 됩니다
-- A열 date, B열 sku, C열 qty 라고 가정
-- 날짜 유효성 엄격 검사
=LET(
s, A2,
oklen, LEN(s)=10,
hy1, MID(s,5,1)="-",
hy2, MID(s,8,1)="-",
y, VALUE(MID(s,1,4)),
m, VALUE(MID(s,6,2)),
d, VALUE(MID(s,9,2)),
try, IFERROR(DATE(y,m,d), ""),
strict, IF(try="", FALSE, TEXT(try,"yyyy-mm-dd")=s),
AND(oklen, hy1, hy2, strict, try>=DATE(2025,1,1))
)
-- 수량 유효성
=AND(ISNUMBER(C2), C2>=0, C2<=1000)
-- 코드 존재 여부 products 시트의 A열에 sku 목록이 있다고 가정
=COUNTIF(products!A:A, B2)>0
-- 최종 판정
=IF(AND(D2, E2, F2), "OK", "REVIEW") -- D E F는 각각의 플래그 열
자주 나오는 오류 패턴과 처리 요령
- 날짜가 텍스트로 입력되어 비교가 실패합니다. 위의 엄격 검사 공식을 사용해 텍스트를 정확한 날짜로 해석한 뒤 형식 일치까지 확인해 주십시오.
- 수량 셀에 공백과 문자 섞임이 있습니다. TRIM과 VALUE를 결합하여 입력 단계에서 숫자 변환을 먼저 적용해 주십시오.
- 코드표가 수시로 바뀝니다. products 범위를 표로 관리하면 열 추가나 행 추가에도 참조가 안정적으로 유지됩니다.
- 재계산으로 판정이 흔들립니다. 배포 전에는 플래그와 최종 판정을 값으로 확정하는 절차를 두시는 것이 안전합니다.
빠르게 점검하는 스니펫
-- 세 검사가 모두 TRUE인 행 수와 OK 건수는 같아야 합니다
=SUMPRODUCT( (is_valid_date=TRUE) * (qty_valid=TRUE) * (sku_exists=TRUE) ) = COUNTIF(final_status, "OK")
-- 날짜 형식이 올바른 행 비율
=COUNTIF(is_valid_date, TRUE) / COUNTA(is_valid_date)
-- 코드 누락 비율
=COUNTIF(sku_exists, FALSE) / COUNTA(sku_exists)
-- 수량 음수 건수
=COUNTIF(qty, "<0")
운영 팁 한 가지
검증 기준은 데이터의 맥락에 따라 달라질 수 있습니다. 예를 들어 날짜 하한을 프로젝트 시작일로 맞추거나, 수량 상한을 품목별 허용치로 분기하는 식입니다. 기준을 시트 상단에 변수 테이블로 분리해 두시면 변경 시 공식을 고치지 않고도 즉시 반영하실 수 있습니다.
맺음말
입력 검증은 리포트 품질을 지키는 가장 경제적인 장치입니다. 제공된 샘플 CSV와 공식을 그대로 사용하시면 날짜, 수량, 코드 검사를 자동화하고 OK와 REVIEW로 정리하는 흐름을 바로 구축하실 수 있습니다.