[엑셀] 달성률 계산하는 방법

ㅇ회사의 각종 실적에 대해서 목표 대비 실적의 달성률을 구해야 하는 경우가 있습니다. 저의 경우는 퀀트투자 데이터를 정리할 때 엑셀로 달성률을 구하기도 하는데요. 일반적인 경우 달성률을 구하는 방법은 매우 간단합니다.

 

=실적/목표량

 

달성률이라는 것은 어떠한 목표량이 있을 때 구할 수 있는 수치입니다. 목표량 대비 실적이 얼마인지 나타내는 것이기 때문에 실적을 목표량으로 나눠서 구할 수 있습니다.

 

실적이 있는 B열의 값을 목표량이 있는 A열의 값으로 나누면 위와 같이 값이 구해집니다. 보통은 달성률을 백분율로 나타내기 때문에 C열의 값을 백분율로 바꿔보겠습니다.

 

달성률을 나타낼 셀을 선택한 다음 '홈' 메뉴에 표시 형식 부분에 있는 '%'모양 아이콘을 클릭합니다.

 

달성률이 백분율 스타일로 바뀌게 됩니다.

 

 

이렇게 백분율 스타일로 바꾼 달성률을 자릿수를 늘리거나 줄일 수 있습니다. '자릿수 늘림' 아이콘을 2번 눌러보겠습니다.

 

소수점 이하 둘째 자리까지 나타납니다.

 

이렇게 달성률을 구하는 것은 간단합니다. 그런데 꼭 이렇게 단순한 케이스만 존재하는 것은 아닙니다. 실적이 꼭 양수인 것도 아니고 목표량 자체가 음수인 경우도 있습니다. 목표량과 실적이 음수인 경우는 상황별로 나눠서 말씀드리겠습니다.

 

목표량은 양수, 실적은 음수인 경우

이 경우는 실적은 음수이지만 목표량과 실적 모두가 양수인 경우와 동일하게 달성률을 구하면 됩니다. 

 

목표량이 양수이고 실적은 음수이므로 달성률 또한 마이너스 수치로 나타납니다. 즉 목표량이 양수이면 실적이 양수인지 음수인지 상관없이 '=실적/목표량'으로 달성률을 구할 수 있습니다.

 

목표량이 음수인 경우

목표량이 음수인 경우가 문제입니다. 다음의 예를 한 번 보겠습니다.

 

6행을 보면 목표량은 -10,000이고 실적은 5,240입니다. 목표량은 음수였지만 실적은 양수로 기대한 목표량을 초과 달성한 것으로 볼 수 있습니다. 그런데 목표량이 양수일 때 사용한 수식을 그대로 적용하면 달성률은 -52.40%가 나옵니다. 예상을 상회하는 실적은 올렸음에도 달성률은 음수로 나타나는 것입니다.

 

7행을 보면 목표량은 -20,000이고 실적은 -35,335입니다. 목표량보다 더 낮은 실적을 기록했음에도 달성률은 100%를 초과한 것으로 나타납니다. 

 

이렇게 6행과 7행에서 구한 달성률은 상식적으로 생각해도 올바른 값이라고 할 수 없습니다. 이렇게 목표량이 음수인 경우에는 단순이 실적을 목표량으로 나눠서 달성률을 구해서는 안 됩니다.

 

그렇다면 결국 수식을 기존과 달리 적용해야 하는데 달성률을 어떻게 구할 수 있을지 한 번 생각해 보겠습니다. 달성률이 100%라는 것은 목표량과 실적이 동일한 때입니다. 위의 6행처럼 목표량이 -10,000이면 실적이 -10,000인 경우 달성률이 100%가 나와야겠죠. 그러면 실적이 -10,000이 기준점이 된다고 생각하면 -20,000이면 0%, 0이면 200%가 되겠구나 생각해 볼 수 있습니다.

 

이런 생각을 가지고 달성률을 구하는 수식을 만들면 아래와 같습니다.

 

=(실적-목표량)/ABS(목표량)+1

 

ABS 함수는 절대값을 구하는 함수로 이에 관해서는 아래의 포스팅을 참고하시면 되겠습니다.

[엑셀] ABS 함수로 절대값 구하는 방법

 

이제 위의 수식대로 달성률을 구해보겠습니다.

 

목표량이 음수일 때 달성률을 구하는 수식의 의미가 바로 이해가 되지 않을 수 있습니다. 먼저 목표량과 실적이 동일할 때 달성률을 100%가 된다는 생각을 가지고 수식의 의미를 생각해 보시면 됩니다.

 

목표량이 음수인 경우, 목표량과 실적이 동일하면 100%, 실적이 0이면 200%, 실적이 '목표량*2'이면 달성률은 0%가 됩니다. 

 

그리고 '=(실적-목표량)/ABS(목표량)+1'의 수식은 목표량이 음수일 때만 적용할 수 있는 것은 아닙니다. 목표량이 양수일 때도 적용할 수 있는 수식이기 때문에 달성률을 구할 때는 항상  '=(실적-목표량)/ABS(목표량)+1'을 사용하시면 되겠습니다.