엑셀[Excel] 목표대비 달성률, 전년대비 성장률(증감률) 구하기

회사 생활에서 엑셀에 관련된 부분을 작업을 했을 때 처음 요청 받은 부분이 달성률, 성장률을 구하는 시트를 만들어 내라는 지시였습니다. 그래서 이번 포스팅은 달성률, 성장률에 대해서 이야기하면서 엑셀(Excel)에서 이 부분을 구하는 공식을 만들어 보고자 합니다. 우선 다음 양식을 토대로, 예시 및 공식을 만들어 진행을 해보고자 합니다. 

 

예시로 사용할 데이터

 

달성률 

흔히들 달성률을 목표대비 성취한 부분을 비율로 표시한 것이라고 합니다. 그래서 가장 흔히드 사용하는 공식이 다음과 같습니다. 

 

실적 / 목표 

 

그럼 위의 공식을 토대로 엑셀에 다음같이 작성을 해보도록 하겠습니다. 우선 올해 실적인 E행을 D열로 나누는 방식으로 진행하고자 합니다. 그럼 우선 첫번째 열에 E5를 D5로 나누겠습니다. 그리고 이 부분을 빈열로 복사하여 나머지 열들을 위의 공식이 적용되게 합니다. 

달성률 공식 입력 "실적/목표"

처음에는 공식을 바탕으로 입력을 하니 다음과 같이실수 기반으로 명시가 되었습니다. 1이 100%입니다. 물론 이렇게 생각하여 보는 것도 나쁘지 않지만, 이를 좀 더 잘 보기 위해서는 다음 보이는 상단에 [홈]탭에 - [%]을 클릭해주시길 바랍니다. 

달성률을 실수 기반으로 표시, 이를 %로 변환

비율(%)로 표시를 했더니 C사업부가 이상하게 표시가 되었습니다. 분명 목표 대비 엄청나게 성장하였지만 성장률은 엄청나게 떨어진 것 같이 표시가 됩니다. 

기존 공식에 오류

바로 실적을 목표로 나누다 보니, 기존의 공식에 음수가 들어가 있는 부분때문에 위와 같이 부정확한 결과가 나왔습니다. 

 

음수가 포함된 값을 토대로 한 달성률 계산 공식

그럼 위의 부분을 개정하기 위해서 일정 부분 수정이 불가피합니다. 그럼 다음 결과를 토대로 수정하겠습니다. 

 

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

 

위와 같은 공식을 사용하셔야 합니다. 그럼 엑셀에 이를 토대로 입력해보도록 하겠습니다. 

음수가 포함된 값을 토대로 한 달성률 계산 공식

이를 토대로 일단 음수 부분이 있는 부분만 =(E7-D7)/ABS(D7)+1를 입력해주시면 됩니다. 그럼 다음과 같은 공식이 적용이 됩니다. 

정상적인 달성률 계산 결과

물론 양수가 포함되어 있는 공식에도 동일하게 잘 적용이 됩니다. 

 

전년대비 성장률(증감률)

위에서는 달성률에 대해서 살펴봤습니다. 그럼 이번에는 성장률(증감률)에 대해서 살펴보도록 하겠습니다. 물론 저는 제목에는 전년대비라고 했지만, 이 부분은 적용하시기에 따라 전분기 대비등으로 사용하실 수 있습니다. 

 

일단 성장률(증감률)이란? 특정 기준 대비 성장/감소한 정도를 비율로 나타낸 것입니다. 이를 나타내는 공식은 다음과 같습니다. 

 

(이번값 - 이전값 )/ 이전 값

 

그럼 이전 예시에 더불어 계속 진행을 해보도록 하겠습니다. 보이는 바와 같이 2019년 매출(E행)-2018년 매출(C행)/2018년 매출(C행)을 토대로 성장률을 계산할 수있게 엑셀 셀에 입력해보도록하겠습니다. 

성장률(증감률) 공식입력

역시 위에서의 결과와 동일하게 실수를 바탕으로 이 부분이 표시가 됩니다. 이런 부분을 비율로 나타내기 위해서 상단에 보이는 바와 같이 [홈]탭에 - [%]을 클릭해주시길 바랍니다. 

성장률 연산 결과 

그럼 다음 보이는 바와 같이 비율 형태로 나옵니다. 하지만 앞서 달성률에서와 같이 여기에서도 또 문제가 있습니다. 물론 900%성장 했지만, 음수가 표시가 됩니다. 

비율로 결과 표시 

음수가 포함된 값을 토대로 한 성장률(증감룰) 계산 공식

그럼 이 부분을 수정해보도록 하겠습니다. 음수가 식에 포함될 경우는 다음과 같은 공식을 입력하셔야 합니다. 

 

(이번값 - 이전값 )/ABS(이전 값)

 

그럼 한번 다음과 같이 공식을 입력해보도록 하겠습니다. 

음수가 포함된 값을 토대로 한 성장률(증감룰) 계산 공식 입력

위의 공식을 토대로 살펴보니, 정상적으로 결과가 나타난 것을 알 수 있습니다. 

성장률(증감률) 계산 결과 

이번 포스팅에서는 달성률과 성장률(증감률)에 구하는 엑셀 공식 및 작성 방법에 대해서 살펴봤습니다. 이 부분이 실제 회사에서 매우 많이 사용하는 부분입니다. 필요 하신 분들은 다음 파일을 다운로드 받아 활용하시길 바랍니다. 

달성률.xlsx

0.02MB

혹 궁금하신점이나 문의 사항이 있으시면 언제든지 댓글 및 방명록에 글 남겨 주시길 바랍니다.