VLOOKUP()함수: 구간별로 금액을 다르게 적용하는 사례

아래는 20회 드라마 광고 단가표입니다.(가상으로 만들었습니다.) 15초 기준으로 1번 나갈 때마다 광고주가 돈을 냅니다.

 

조건이 재미있습니다. 광고를 10번 내겠다고 하면, 5번까지는 1,300만원을 내야 하고, 6회부터 10번까지 1,200만원을 적용합니다.

 

총 금액은  1억 2,500만원(1,300만원X5회(1-5회차)+ 1,200만원X5회(6-10회차))입니다. 

 

 

쉽게 사용하는 방법은  광고횟수 별 단가표를 만드는 것입니다. 

 

 

간단하고, 바로 쓸 수 있는 장점이 있는 반면에 단가가 바뀌거나(대박이 나서 금액이 올랐습니다.),  횟수를 조정할 수 있습니다.(시청률이 저조해서 5회 단위가 아니라 8회 단위로 금액을 바꿉니다.)  그때 그때마다 바꿔야 하는 불편함이 생깁니다. 

 

vlookup() 함수를 교묘하게(?) 사용하면 이것을 한번에 해결할 수 있습니다. 

 

먼저 계산순서를 따져봐야 합니다. 

광고횟수를 정하면, 5회가 넘는 지 알아봅니다. 5회가 넘으면 5회, 10회, 15회, 20회, 25회 등의 변경지점까지 총 광고금액을 참고합니다. 광고 14회라면 10회까지 총광고금액 1억 2,500만원에 4,400만원_(14회-10회)*1,100만원을 합하여 계산합니다. 

계산을 위해 필요한 자료는 "변경지점까지 총 광고금액", “광고횟수”, “단가”입니다. 계산식은 “변경지점까지 총 광고금액”+(광고횟수-변경횟수)*”단가”입니다. 

 

엑셀로 구현합니다. 

 

이름을 주었습니다. 

횟수: D10

광고단가:  A1:D7

변경지점_별_총광고금액: D11

광고_횟수: D12

해당_단가: D13

 

광고_횟수의 수식에서 1을 더했습니다. 원래는 14에서 10을 빼야 하는 데, 실제 계산은 10이 아니라 11을 뺍니다. 사람은 구간의 끝을 계산하는 것이 직관적이지만, 엑셀은 구간의 처음인 11에서 셈해야 합니다. 끝과 처음 차이로 엑셀 수식을 다룰 때 1을  더하거나 빼거나 곱하거나 나누기도 합니다.