엑셀 ABS 함수 편차표: 빈칸·기준·가중치 오류까지 검산하기
차이가 큰 제품을 찾을 때 합계 하나만 보면 방향이 다른 오차가 서로 상쇄됩니다. 이 실습에서는 같은 단위의 가상 목표·실제값을 제품 다섯 개에 넣고, 방향 합계 400과 절대편차 합계 800을 따로 읽습니다. 원본 숫자를 바꾸기 전에 빈칸과 검토 기준부터 확인하는 편차표입니다.
도해는 방향 편차와 절대 편차의 차이를 설명합니다. 실습 파일의 셀 위치와 다섯 제품 입력은 아래 표를 기준으로 확인하세요.
제품 다섯 개에서 상쇄되는 차이를 찾아보기
첨부의 편차표 시트에서 A4:L4는 제목 행, 5~9행은 입력과 계산입니다. B·C열은 같은 단위의 가상 측정값이며 H열은 별도로 가정한 거래건수입니다. 합성 수치이며 제품 가격이나 실제 실적 자료가 아닙니다.
| 행·제품 | 목표 B | 실제 C | 방향 C−B | 절대편차 | 거래건수 H |
|---|---|---|---|---|---|
| 5·A | 1000 | 1200 | 200 | 200 | 10 |
| 6·B | 2000 | 1800 | -200 | 200 | 20 |
| 7·C | 1500 | 1600 | 100 | 100 | 5 |
| 8·D | 800 | 800 | 0 | 0 | 8 |
| 9·E | 0 | 300 | 300 | 300 | 3 |
-
B5:C9의 입력을 표와 대조하고 I5:I9가 모두
정상인지 확인합니다. I5의=IF(COUNT(B5:C5)=2,"정상","입력오류")는 숫자 두 개를 요구합니다. 빈 목표를 0으로 간주하지 않습니다. -
D5의
=IF(I5="정상",C5-B5,"")와 E5의=IF(ISNUMBER(D5),ABS(D5),"")를 아래로 확인합니다. D열은 증가·감소 방향, E열은 크기만 남깁니다. -
B12:B14를 비교합니다. 방향 합계는
200-200+100+0+300=400, 절대 합계는200+200+100+0+300=800, 방향 합계에 ABS를 한 값은 다시 400입니다. 따라서ABS(SUM(편차))와SUM(절대편차)는 다른 질문에 답합니다.
Microsoft의 ABS 함수 설명은 절댓값을 부호 없는 크기로 설명합니다. 원본 부호까지 필요하다면 ABS 결과로 D열을 덮어쓰면 안 됩니다.
목표가 0이면 비율 대신 사유를 남기기
F5의 수식은 입력이 정상이고 목표가 0이 아닐 때만 비율을 계산합니다.
=IF(I5="정상",IF(B5=0,"",ABS(D5/B5)),"")가상 입력 A·B·C·D의 편차율은 각각 20.0%, 10.0%, 약 6.7%, 0.0%입니다. E 제품은 목표가 0이므로 F9가 빈 문자열이고 L9는 목표 0: 비율 제외입니다. 이 가상 예제의 빈 표시를 0%로 바꾸면 차이가 없었다는 뜻으로 오독할 수 있습니다. F열의 빈 표시와 L열의 사유를 함께 읽으세요.
편차율을 읽을 때는 목표가 0인 행과 목표가 비어 있는 행을 따로 확인해야 합니다. 빈 목표·목표 0·실적 0의 나눗셈 비교에서 원시 수식과 IF 분기를 나란히 보고, 실적 0의 계산값이 보존되는지 사본으로 검산하세요.
150 경계와 빈 검토 기준을 구분하기
J5=150은 이 실습에서 정한 검토 기준입니다. 업무 표준이나 보편적인 허용 오차가 아닙니다. J8은 이 값이 0 이상의 숫자인지 검사하고, G열은 정상 입력에 대해서만 절대편차가 기준보다 큰지 비교합니다. 따라서 150은 정상, 151은 검토입니다.
경계 시트 E2:E4는 149·150·151에 대해 정상 / 정상 / 검토를 보여줍니다. 기본 다섯 제품에서는 A·B·E 세 개가 검토 대상이어서 B16=3입니다. 업무상 “이상”이 필요하면 비교 기호뿐 아니라 150 경계의 기대값도 함께 바꿔야 합니다.
기준 확인과 검토 건수를 연결하는 수식
첨부 편차표를 직접 재현할 때 J5에 숫자 150을 입력한 뒤, J8에는 다음 식을 넣습니다. 기준 0은 허용하지만 빈칸·문자·음수는 기준오류입니다.
=IFERROR(IF(AND(ISNUMBER(J5),J5>=0),"정상","기준오류"),"기준오류")편차표!G5: 입력과 기준이 정상일 때만 판정
=IF(I5<>"정상","입력오류",IF($J$8<>"정상","기준오류",IF(E5>$J$5,"검토","정상")))G5를 G9까지 아래로 복사합니다. I열의 입력 상태와 E열 절대편차는 같은 행을 따라가고, J8·J5의 기준 셀은 고정됩니다. 기본 G5:G9는 검토·검토·정상·정상·검토입니다.
편차표!B16: 다섯 입력과 기준이 모두 정상일 때 검토 수 집계
=IF(AND(COUNTIF(I5:I9,"정상")=5,J8="정상"),COUNTIF(G5:G9,"검토"),"판정보류")B16=3을 확인한 다음 J5만 비우면 J8=기준오류, G5:G9=기준오류, B16=판정보류여야 합니다. 기준만 바꾼 것이므로 방향 합계와 절대편차 합계는 그대로입니다. 기준을 150으로 복구한 뒤 J5=200도 비교해 보세요. 편차가 정확히 200인 A·B는 검토 대상에서 빠지고, 300인 E만 남아 B16=1입니다. 이 차이는 >와 >=의 선택에서 생깁니다. 마지막에는 150으로 되돌립니다.
가중 평균에 거래건수를 다시 곱해도 되는 경우
H열의 10·20·5·8·3은 합성 빈도입니다. 각 E값이 같은 단위의 거래당 대표 절대편차라는 가정 아래에서만 가중 평균을 읽습니다. 이미 제품별 총편차를 넣었다면 건수를 다시 곱하면 안 됩니다.
이 가정에서 분자는 200×10+200×20+100×5+0×8+300×3=7400, 분모는 46입니다. B18=7400, B17=46, B15는 7400/46이며 한 자리 표시값은 160.9입니다. SUMPRODUCT 공식 문서가 설명하는 대응 배열의 곱 합을 E5:E9·H5:H9에 적용했습니다. 단순 평균 160과 같은 값이 아닙니다.
가중치 검사부터 7400÷46까지 직접 계산하기
H5:H9에는 거래건수 10·20·5·8·3을 숫자로 입력합니다. 이 파일의 가중치는 건수이므로 음이 아닌 정수만 허용합니다. 확률이나 비율을 가중치로 쓰는 다른 분석에는 이 정수 제한을 그대로 가져가지 않습니다.
K5: 가중치 상태, K9까지 아래로 복사
=IFERROR(IF(AND(ISNUMBER(H5),H5>=0,H5=INT(H5)),"정상","가중치오류"),"가중치오류")문자형 10, 실제 빈칸, 음수 -1, 소수 1.5는 가중치오류입니다. 숫자 0은 정상이며 해당 행이 가중 분자와 분모에 0만 기여합니다. IFERROR는 INT에 전달할 수 없는 문자나 원본 오류를 계산값 0으로 바꾸는 대신 가중치오류로 남깁니다.
B17: 분모인 전체 거래건수
=IF(COUNTIF(K5:K9,"정상")=5,SUM(H5:H9),"가중집계보류")B18: 같은 행의 절대편차×거래건수 합
=IF(AND(COUNTIF(I5:I9,"정상")=5,COUNTIF(K5:K9,"정상")=5),SUMPRODUCT(E5:E9,H5:H9),"가중집계보류")B15: 유효한 양수 분모일 때 가중 평균
=IF(AND(ISNUMBER(B17),ISNUMBER(B18)),IF(B17>0,B18/B17,"가중집계보류"),"가중집계보류")기본값은 B17=46, B18=7400, B15≈160.8696입니다. B15에 소수 한 자리 표시 형식을 적용하면 160.9로 보이지만 수식 값을 반올림한 상수로 바꾸지는 않습니다. SUMPRODUCT는 같은 크기의 두 범위를 같은 위치끼리 곱하므로 E5:E9와 H6:H10처럼 한 행 밀린 범위를 연결하지 않습니다. Microsoft SUMPRODUCT 설명
먼저 H5만 -1로 바꾸면 K5=가중치오류이고 B17·B18·B15는 가중집계보류입니다. 10으로 복원한 뒤 H5:H9를 모두 숫자 0으로 바꾸면 K열은 모두 정상, B17=B18=0이지만 B15는 가중집계보류입니다. 0으로 나눈 평균을 0으로 표시하지 않는 것이 확인점입니다. 원래 10·20·5·8·3을 복원하면 7400÷46으로 돌아옵니다. 다섯 행이라는 조건은 이 고정 예제의 입력 계약이므로 행을 늘릴 때 검사 범위와 개수도 함께 바꿔야 합니다.
본문의 단일 셀 수식은 Enter로 입력하며 동적 배열이 필요하지 않습니다. ABS와 SUMPRODUCT는 Microsoft 365 및 Excel 2016·2019·2021·2024에 제공됩니다. 이 실습의 가중 평균이 의미 있으려면 E열이 같은 단위의 거래당 대표 편차라는 가정이 먼저 맞아야 합니다.
실패 입력을 넣었다가 복구하는 검산
사본에서 B5만 지우면 I5는 입력오류, D5는 빈 표시, B12는 입력보류, B15는 가중집계보류여야 합니다. 1000을 다시 입력해 합계 400과 가중 평균 약 160.9로 복구되는지 확인하세요.
J5만 지우면 J8은 기준오류, B16은 판정보류가 됩니다. 방향 합계 400은 그대로 남습니다. 기준을 150으로 복구하면 검토 수가 다시 3입니다. 모든 H값을 0으로 바꾼 반례에서는 분자·분모가 모두 0이고 B15는 가중집계보류여야 합니다. 원래 10·20·5·8·3을 되돌리면 계산이 재개됩니다. 음수나 소수 건수도 K열에서 가중치오류로 남깁니다.
이 편차표가 판단하지 않는 범위
이 파일은 오차 크기·입력 상태·가상 기준을 구분합니다. 표본의 대표성, 측정 장비의 정확도, 원인 분석, 허용 기준의 타당성은 계산만으로 결정하지 않습니다. 원본 측정값·단위·기간을 보존한 사본에서 비교하고, 새 제품을 추가할 때는 집계 범위와 정상 입력 개수 조건도 함께 확장해야 합니다.
ABS는 숫자의 부호 없는 절댓값을 반환합니다.
SUMPRODUCT는 대응하는 범위나 배열의 곱을 합합니다.
출처 원문 수집일: S1 2026-09-24; S2 2026-09-24.
작성자 소개
