사업계획 시즌 !! 엑셀 달성률 계산법 — 목표가 음수·0이거나 비용 목표일 때 실적÷목표가 틀리는 이유

엑셀 달성률은 보통 =실적/목표로 구하지만, 목표가 음수(적자 목표)이거나 낮을수록 좋은 지표(비용·불량률)라면 이 수식이 정반대 결과를 냅니다. 적자 목표 −100에 실적 −50이면 적자를 절반으로 줄였는데 달성률이 50%로 나옵니다. 이럴 때는 =1+(실적-목표)/ABS(목표)를 쓰면 150%로 바르게 나옵니다. 다섯 가지 경우를 계산한 실습 파일로 정리합니다.

다섯 가지 경우 — 단순 나누기 vs 올바른 수식

항목	목표	실적	실적÷목표	올바른 달성률	판정
매출	1,000	1,120	112.0%	112.0%	같음
영업이익	200	170	85.0%	85.0%	같음
적자 사업부 손익	−100	−50	50.0%	150.0%	단순 나누기 틀림
판관비 (낮을수록 좋음)	300	270	90.0%	110.0%	단순 나누기 틀림
신규사업 매출	0	30	＃DIV/0!	목표 0 — 증감 +30	달성률 정의 불가

목표가 양수이고 높을수록 좋은 지표라면 단순 나누기도 맞습니다. 매출·영업이익처럼 대부분의 경우가 여기에 해당해서, 문제가 있다는 걸 모르고 쓰다가 연말 실적 보고 때 사고가 납니다.

엑셀 달성률 오류 해결

왜 음수 목표에서 틀릴까

적자 사업부의 올해 목표가 "적자 100으로 줄이기"였고, 실제로 적자 50을 냈습니다.

단순 나누기:  -50 / -100 = 0.5  →  50% (목표의 절반만 했다?)

음수끼리 나누면 양수가 되고, 절댓값이 작을수록(적자가 줄수록) 결과도 작아집니다. 좋아질수록 달성률이 떨어지는 거꾸로 된 지표가 됩니다. 실적이 −150(적자 확대)이면 단순 나누기는 150%가 나와서, 더 나빠졌는데 초과 달성처럼 보입니다.

올바른 수식 — "목표 대비 얼마나 더 했나"로 바꾸기

높을수록 좋은 지표:  =1+(실적-목표)/ABS(목표)
낮을수록 좋은 지표:  =1+(목표-실적)/ABS(목표)

원리는 단순합니다. 목표를 100%로 놓고, 목표와의 차이를 목표의 크기(절댓값)로 나눠 더하거나 빼는 것입니다.

경우	계산	결과
적자 −100 목표, 실적 −50	1 + (−50 − (−100)) / 100	1 + 50/100 = 150%
적자 −100 목표, 실적 −150	1 + (−150 − (−100)) / 100	1 − 50/100 = 50%
판관비 300 목표, 실적 270	1 + (300 − 270) / 300	1 + 30/300 = 110%
매출 1,000 목표, 실적 1,120	1 + (1,120 − 1,000) / 1,000	112% (단순 나누기와 같음)

ABS가 핵심입니다. 분모를 절댓값으로 바꿔서 음수 목표에서도 방향이 뒤집히지 않게 합니다. 목표가 양수면 ABS를 씌워도 값이 같으니, 이 수식 하나로 모든 경우를 처리할 수 있습니다.

비용·불량률처럼 낮을수록 좋은 지표

판관비 목표 300, 실적 270이면 30을 아꼈으니 초과 달성입니다. 그런데 단순 나누기는 90%라 미달처럼 보입니다.

이런 지표는 실무에서 흔합니다.

• 판관비, 원가, 재료비
• 불량률, 클레임 건수, 재작업 시간
• 리드타임, 납기 지연 일수

목표÷실적(300/270=111.1%)으로 뒤집어 쓰는 회사도 있는데, 이 방식은 실적이 0에 가까워지면 달성률이 수천 %로 튀는 문제가 있습니다. 1+(목표-실적)/ABS(목표)가 더 안정적입니다.

첨부 파일은 유형 열에 "높을수록 좋음 / 낮을수록 좋음"을 적으면 수식이 알아서 바뀌게 만들어 두었습니다.

=IF(B5=0, "목표 0 — 증감 "&TEXT(C5-B5,"+#,##0;-#,##0"),
   IF(F5="낮을수록 좋음", 1+(B5-C5)/ABS(B5), 1+(C5-B5)/ABS(B5)))

목표가 0일 때 — 달성률을 쓰지 마세요

신규 사업처럼 목표가 0이면 어떤 수식으로도 달성률이 정의되지 않습니다. 0으로는 나눌 수 없습니다. IFERROR로 0%나 100%를 넣는 것도 틀린 정보입니다.

"목표 0 → 실적 30 (증감 +30)"처럼 증감액으로 표기하세요. 첨부 파일은 목표가 0이면 자동으로 이렇게 표시합니다.

달성률 표 만들 때 실무 팁

팁	이유
달성률 옆에 증감액 열도 같이 두기	150%가 "+50"인지 "+5,000"인지 규모가 보임
100% 이상은 파랑, 90% 미만은 빨강 조건부 서식	보고 받는 사람이 3초 안에 판단
표시 형식은 소수 첫째 자리 % (112.0%)	99.6%가 100%로 반올림돼 보이는 착시 방지
음수 목표 행에는 주석 달기	"적자 축소 목표"임을 명시해 오해 방지

성장률과 달성률은 다른 지표입니다. 성장률은 작년 대비, 달성률은 목표 대비입니다. 연평균 성장률(CAGR)은 이 블로그의 CAGR 글, 전년 대비 신장률은 매출 신장률·성장률 계산법에 정리해 두었습니다.

엑셀 달성률 계산 실습

마무리

• 목표가 양수이고 높을수록 좋은 지표라면 실적/목표로 충분합니다.
• 음수 목표·비용 지표까지 다룬다면 1+(실적-목표)/ABS(목표) 하나로 통일하세요. 낮을수록 좋은 지표는 실적과 목표 순서만 바꿉니다.
• 목표가 0이면 달성률 대신 증감액으로 표기합니다.

첨부 실습 파일의 결과가 112% / 85% / 150% / 110% / "목표 0 — 증감 +30"으로 나오는지 확인해 보세요. 유형 열만 바꾸면 회사 KPI 표에 바로 쓸 수 있습니다.

관련 글

• 매출 신장률·성장률 계산법 – 엑셀 자동계산 서식
• 의류 재고회전율 계산법 – 엑셀 자동계산 서식
• 엑셀 피벗테이블 새로고침 안 될 때 — 원인 5가지와 해결법

엑셀 피벗테이블 새로고침 안 될 때 — 원인 5가지와 해결법

 

엑셀_달성률_실습.xlsx

0.01MB