엑셀 IF, RANK 함수 활용하여 성과 순위별 인센티브 계산하기

 

 

 

 

안녕하세요. 지난 글에서 엑셀을 통해 아웃바운드 영업사원별 매출액 실적 합산 금액을 계산하는 SUMIF 함수 활용방법에 대해 알려드렸죠?^^ 그것에 이어서 마무리 못한 부분인, 성과 순위별 인센티브를 계산하기 위해 IF와 RANK 함수를 조합하여 활용하는 방법을 알려드리겠습니다.

 

 

※ SUMIF 함수 활용하여 매출액, 실적 등 원하는 항목 더하는 방법이 궁금하시면 아래 링크 참조!

2020/04/09 - [자기개발] - 엑셀 SUMIF 함수 - 매출액, 실적 등 원하는 항목만 더하고 싶을 때

엑셀 SUMIF 함수 - 매출액, 실적 등 원하는 항목만 더하고 싶을 때

 

 

 

먼저, '고정 인센티브' 열을 채워보겠습니다. 이 부분은 간단하죠. 매출액에 인센티브율 3%를 곱해주면 됩니다.

 

 

 

 

그리고, 아래 열에 셀을 복사해주면 아래와 같이 인센티브 계산이 완료됩니다. 쉽죠?^^

 

 

 

 

---

IF 와 RANK 함수 조합하여 성과 순위별 인센티브 계산하는 법

 

 

 

자! 지금부터는 본 글의 주제라고 할 수 있는 내용을 차근차근 진행해보겠습니다. 

4월 매출액 실적 순위별로, 1등은 20,000원, 2등은 10,000원, 3등은 5,000원, 4등은 2,000원의 인센티브를 지급하기로 했다고 가정하고 진행하겠습니다.

 

 

한비광의 '매출 순위별 인센티브' 셀(J2)을 선택한 상태에서 함수마법사를 실행합니다.(이전 글에서 배워서 아시죠? 모르시면, 서두에 남겨드린 링크로 가셔서 보고 오시면 됩니다) 순위를 구하는 RANK 함수를 선택해주세요.

 

 

 

RANK 함수의 인수는 3가지로 구성되어 있습니다. 아래사진을 확인해 주세요.

1,2,3,4,5 라는 숫자 중에 1이 몇 번째로 큰 숫자인지 궁금한 상황을 가정할 때, 여기서 '1'이 Number 에 해당하고, '1,2,3,4,5'가 Ref에 해당합니다. 그리고 Order는 순위를 내림차순으로 계산할지, 오름차순으로 계산할지 정해주는 인수입니다. 내림차순으로 한다면, '1'이 제일 큰 숫자로 인식되고 오름차순으로 한다면 '5'가 제일 큰 숫자로 인식됩니다.

 

 

 

 

한비광의 4월 매출액에 해당하는 셀을 기준점으로 두고 함수를 입력하는 것이기 때문에, 이에 해당하는 H2셀을 Number 인수에 입력합니다. (H2셀을 클릭해도 되고, 직접 'H2'라고 문자를 입력해도 됩니다)

 

 

 

 

Ref 인수에는 비교해야 할 숫자를 범위로 지정해줘야 합니다. 4월 매출액 중에 특정 영업사원의 매출액의 순위가 몇인지 비교하는 상황이므로, '4월 매출액' 열을 드래그하여 범위로 지정합니다.(직접 'H2:H5' 라고 입력해도 됩니다)

 

 

 

 

여기서!! 이전 글에서 언급했듯이, '범위'를 지정할 때에는 '고정값'으로 설정해줘야 합니다. F4키를 눌러주면 아래 사진처럼 행렬에 $문자가 표시됩니다. 모든 행과 열을 고정해준다는 의미입니다.

 

 

 

 

 

Order 인수에는 '내림차순'을 의미하는 숫자 0을 입력해 줍니다. 여기까지 모든 인수를 입력하면, 현재 선택되어 있는 셀에 해당하는 결과값이 '1'이라고 나옵니다. (한비광의 4월 매출액 715,900원의 실적이 영업사원 중 1등이라는 의미)

 

 

 

 

 

확인 버튼을 누르면 선택되어 있는 H2셀에 결과값이 표시되어 있습니다. 

 

 

 

 

 

아래 열로 셀을 복사해보겠습니다. 각 매출액의 순위가 매겨집니다.

 

 

 

 

커서를 H2 셀에 놓은 상태에서, 상단 수식창에 표기된 함수 수식을 '잘라내기'(Ctrl+X) 해준 후, 함수마법사를 통해 IF 함수를 열어주세요.

 

 

 

IF 함수의 인수도 3가지로 구성되어 있습니다.

- Logical_test 에 바로 전 잘라내기 했던 함수 수식을 '붙여넣기'(Ctrl+V) 해준 후 =1 을 더하여 수식을 완성해주세요. 'H2셀이 비교범위 중 순위가 1이라면' 이라는 조건을 의미합니다.

- Value_if_true 에는 위의 조건에 충족된다면 적용하고자 하는 결과값을 입력해 줍니다. 실적 1등에게는 20,000원을 지급하기로 하였으니, 20,000을 입력합니다.

- Value_if_false 에는 위의 조건이 충족되지 않을 경우 적용하고자 하는 결과값을 입력해 줍니다.

 

 

 

 

 

조건에 따르는 결과값의 경우의 수가 두 가지만 있다면, Value_if_false 인수에 조건 불충족 시 적용할 결과값을 입력하고 마치면 됩니다. 그러나 지금은 1등, 2등, 3등, 4등에게 인센티브를 지급해야 하는 4가지 경우의 수가 있어서 이렇게 마무리할 수가 없답니다. 

 

이 경우엔 어떻게 해야 할까요? 중첩 함수를 사용할 수 있습니다. 실적 1위라면 20,000원을 주지만 1위가 아닌 경우 중에 2등, 3등, 4등한 경우와 같은 조건을 다시 주어야 합니다. 그러므로 조건 불충족한 결과값 인수에 새로운 조건절을 입력하기 위해 Value_if_false에 커서를 놓고 중첩함수 기능 버튼을 눌러줍니다 (아래 빨강박스 체크한 버튼입니다)

 

 

 

 

 

그러면, 새로운 IF함수 인수 창이 뜹니다. 두 번째 조건에 해당하는 내용을 아래와 같이 입력해 줍니다. 그리고 조건 불충족 결과값 인수에 커서를 놓고 이번에도 중첩 함수를 한 번 더 사용합니다.

 

 

 

 

앞서 입력했던 순서대로, 실적 순위가 3등일 경우를 의미하는 조건절을 입력하고, 그에 해당하는 결과값 5000을 두 번째 인수에 입력합니다. 그리고 마지막 경우의 수인 실적 순위 4등일 경우 하나만 남았기 때문에 마지막 인수(조건 불충족)에 2000을 입력하고 확인 버튼을 누릅니다.

 

 

 

 

자! 드디어 최종 결과값이 도출되었습니다. 한비광의 매출액 실적이 가장 높기 때문에 1등 성과급 20,000원이 표시됩니다.

 

 

 

 

H2셀을 아래 열에도 복사해주면, 다음과 같이 결과값이 표시되지요. 담당자 별 실적 순위별 인센티브 성과금이 정확하게 도출된 것을 확인할 수 있습니다. (4명이기 때문에 육안으로도 쉽게 순위를 매길 수 있지만, 20명, 50명 처럼 많은 인원이라면 직접 체크하기는 어려울 것입니다)

 

 

 

 

이로써 이전 글에서 알려드린 엑셀 SUMIF 함수 활용법에 이어 IF, RANK 함수를 활용한 성과 순위별 인센티브 계산방법까지 전해드렸습니다. 실무에서 유용하게 활용해 보시죠!^^