배경 - 값이 몇 개만 튀어도 합계와 평균이 크게 흔들립니다. 단순히 평균에서 많이 벗어났다는 이유만으로 제외하면 정당성이 약하고, 반대로 느슨하게 두면 리포트 품질이 떨어집니다. 이 글에서는 IQR 경계와 표준편차 기준을 동시에 적용하는 간단한 이중 판정 방식을 소개해 드립니다. 스크린샷 없이 수식과 CSV만으로 바로 따라 하실 수 있도록 샘플 파일과 필드 해설을 함께 제공합니다.
실습 파일 내려받기
- 원본 값 목록: 07_values.csv
- 판정 결과 포함본: 07_values_with_outliers.csv
샘플 데이터 요약
원본 07_values.csv
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
value |
측정값 또는 관측값 | 10 |
숫자형, 결측 없음 가정 |
결과 포함본 07_values_with_outliers.csv
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
value |
원본 값 | 200 |
숫자형 |
lower_bound |
IQR 하한 | 6.5 |
Q1 - 1.5×IQR |
upper_bound |
IQR 상한 | 19.5 |
Q3 + 1.5×IQR |
iqr_flag |
IQR 기준 이상치 여부 | 1 |
0은 정상 1은 이상 |
mean |
표본 평균 | 23.0 |
AVERAGE |
std |
표준편차 | 47.0 |
STDEV.S |
std_flag |
표준편차 기준 이상치 여부 | 1 |
|값−평균| ≥ 2×표준편차 |
outlier_flag |
최종 이상치 여부 | 1 |
iqr_flag와 std_flag 중 큰 값 |
두 기준을 왜 같이 쓰나요
IQR 경계는 중간값 주변 분포에 민감하고 꼬리가 긴 분포에도 비교적 안정적으로 동작합니다. 반면 표준편차 기준은 전체 평균과 분산을 사용하므로 대칭적인 분포에서 강력합니다. 두 기준을 결합하면 한쪽 기준의 약점을 다른 쪽이 보완하여 오탐과 누락을 함께 줄일 수 있습니다.
작업 순서 요약
- Q1과 Q3를 계산하고 IQR을 구합니다. IQR은 Q3 − Q1 입니다.
- 하한과 상한을 만들고 범위를 벗어나면 iqr_flag를 1로 표시합니다.
- 평균과 표준편차를 구하고 2시그마 기준으로 std_flag를 1로 표시합니다.
- 두 플래그의 최대값을 outlier_flag로 지정합니다.
복사용 수식 모음 (Excel 기준)
-- 값 범위가 Table1[value]라고 가정합니다.
Q1 =QUARTILE.INC(Table1[value],1)
Q3 =QUARTILE.INC(Table1[value],3)
IQR =Q3 - Q1
하한 =Q1 - 1.5*IQR
상한 =Q3 + 1.5*IQR
평균 =AVERAGE(Table1[value])
표준편차 =STDEV.S(Table1[value])
-- 행 단위 플래그
IQR_플래그 =--OR([@value] < 하한, [@value] > 상한)
STD_플래그 =--(ABS([@value]-평균) >= 2*표준편차)
최종_플래그 =MAX(IQR_플래그, STD_플래그)
Google Sheets 기준 사용 방법
Q1 =QUARTILE(INDEX(A2:A,1):INDEX(A:A,COUNTA(A:A)),1)
Q3 =QUARTILE(INDEX(A2:A,1):INDEX(A:A,COUNTA(A:A)),3)
IQR =Q3 - Q1
하한 =Q1 - 1.5*IQR
상한 =Q3 + 1.5*IQR
평균 =AVERAGE(A2:A)
표준편차 =STDEV.S(A2:A)
IQR_플래그 =IF(OR(A2 < 하한, A2 > 상한),1,0)
STD_플래그 =IF(ABS(A2-평균) >= 2*표준편차,1,0)
최종_플래그 =MAX(IQR_플래그, STD_플래그)
경계값을 정하는 감각을 기르기
- 데이터가 매우 작거나 한쪽으로 치우친 경우에는 1.5 대신 2.0 IQR 배수를 고려해 보십시오. 반대로 품질 요구가 매우 엄격하면 1.0 IQR로 좁히는 방법도 있습니다.
- 표준편차 기준 2시그마는 일반적인 초벌 선별용으로 적당합니다. 매우 엄격한 검수에는 2.5 또는 3시그마를 권장합니다.
- 최종 의사결정은 outlier_flag 뒤에 사용 맥락이 붙어야 합니다. 예를 들어 측정 오류 추정, 재작업 필요, 그대로 유지 등으로 후속 조치 분류를 합의해 두면 좋습니다.
현장에서 자주 생기는 문제와 해결책
- 텍스트 숫자 혼재 문제. 숫자로 보이는 텍스트가 섞이면 통계가 틀립니다. VALUE 또는 데이터 변환을 먼저 적용해 주세요.
- 결측 처리. 중간에 빈 셀이 있으면 사분위수 계산이 불안정해질 수 있습니다. 빈값은 제외하고 계산 범위를 명확히 지정해 주세요.
- 표본과 모집단의 혼동. STDEV.S와 STDEV.P를 혼용하면 경계가 달라집니다. 보통은 STDEV.S를 사용합니다.
- 재계산으로 경계가 바뀌는 문제. 운영 리포트는 값 확정 후 배포해 주세요.
리포트 문장 자동 생성 예시
한 줄 요약은 읽는 시간을 아껴 줍니다.
= "이상치 " & TEXT(SUM(outlier_flag),"0") & "건, IQR 경계 " &
TEXT(lower_bound,"0.00") & " ~ " & TEXT(upper_bound,"0.00") &
", 평균 " & TEXT(mean,"0.0") & ", 표준편차 " & TEXT(std,"0.0")퀵 검증 체크리스트
-- 경계 논리 확인
=MIN(value) >= lower_bound 또는 =MAX(value) <= upper_bound 를 만족하지 않아야 정상입니다.
경계 바깥 값이 최소 하나라도 존재해야 iqr_flag가 1이 됩니다.
-- 플래그 합 검증
=SUM(outlier_flag) >= SUM(iqr_flag) 그리고 =SUM(outlier_flag) >= SUM(std_flag)
-- 기준 변경 실험
IQR 배수를 1.5에서 2.0으로 바꿔 플래그 변화를 기록하고, 운영 규칙을 문서화합니다.
정리하며
IQR 경계와 표준편차 기준을 함께 쓰면 데이터의 꼬리와 중심성을 동시에 고려할 수 있습니다. 위에 제공한 두 개의 CSV와 수식 세트를 그대로 적용하시면 테스트부터 보고서 반영까지 한 번에 이어지며, 기준값만 조정하여 귀하의 도메인에 맞는 판정 정책으로 빠르게 전환하실 수 있습니다.