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을 더하거나 빼거나 곱하거나 나누기도 합니다.
'엑셀 함수' 카테고리의 다른 글
| vlookup()함수로 2015년 소득세율 계산하기 (0) | 2016.05.08 |
|---|---|
| vlookup 함수가 어려운 까닭은? (0) | 2016.04.25 |
vlookup()함수로 2015년 소득세율 계산하기
아래 문제는 "Microsoft Excel 2013: Data Analysis and Business Modeling”에서 발췌한 것입니다.
이 책은 2007인가 하는 버전부터 구해서 읽고, 감탄했던 책입니다. 설명은 간결하고, 문제는 현실적입니다.
짧은 영어로 제대로 읽었는 지는 자신없지만. 문제들은 실제 사용자가 저자에게 물어보았던 것이라고 합니다. 아, 문제는 다소 고쳤습니다.
<문제>
대한민국의 2015년 과세표준에 따른 소득세율입니다. 과세표준 금액을 입력했을 때, 소득세를 계산하시오.
<계산 순서>
위의 표를 엑셀이 이해할 수 있게 변환하는 것이 핵심입니다. 변환을 하려면 계산식을 잘 짜야 합니다. 계산식은 하나만 있지 않습니다. 이를테면 vlookup()함수가 아니라 IF()함수를 사용해도 결과는 똑같이 나옵니다. IF()함수는 좀 길게 작성해야 하고, 괄호를 5개 이상은 쳐야 합니다.
과세표준의 "1,200만원 이하”는 0원에서 시작한다고 가정합니다. 참고로 과세표준은 연봉이 아닙니다.
연말정산이 다달이 미리 떼고, 한 번에 돌려 받는 구조입니다. 다달이 미리 뗀 세금(이를 갑종근로소득세, 갑근세라고 합니다)과 연봉에서 공제 항목을 다 뗀 금액(이것이 과세표준)에서 나온 산출 세액의 차액 만큼을 돌려줍니다. 이를테면 내가 1년 동안 낸 미리 뗀 세금이 200만원이고, 연말정산에서 계산한 세액이 70만원이라면, 실제로 돌려받는 금액은 130만원입니다. 거꾸로 미리 뗀 세금이 70만원이고, 연말정산에서 계산한 세액이 200만원이면 130만원을 국가에 납부해야 합니다.
기본세율은 속산표를 이용하여 수식을 짭니다. +와 - 로 된 계산식은 엑셀에서 셀을 분리하여 작성합니다.
vlookup( )함수에서 맨마지막 인수를 생략하면, 반드시 오름차순으로 정렬해야 한다는 점을 꼭 기억해야 합니다.
'엑셀 함수' 카테고리의 다른 글
| VLOOKUP()함수: 구간별로 금액을 다르게 적용하는 사례 (0) | 2016.05.09 |
|---|---|
| vlookup 함수가 어려운 까닭은? (0) | 2016.04.25 |
vlookup 함수가 어려운 까닭은?
엑셀을 처음 대한 것이 윈도우 3.0인가 3.1인가 했던 무렵이었습니다. 당시 엑셀 버전은 기억이 가물거리는 데 4.0이었습니다. 한글판이었는데, QUERY인가 라는 프로그램이 영어로 나와서 어마 무시워라 하고 황급히 창을 닫았던 일이 떠오릅니다.
엑셀을 계산기의 확장판으로 사용했던 주변 여건에, 배우는 데 용감하지 않았던 게으름이 한몫 했습니다. 이때부터 IF 함수를 몸에 배는 데 5년 정도 걸린 것 같았습니다. vlookup함수는 IF함수가 익숙해진 후 3년 정도 더 걸렸던 듯 싶습니다. 지금은 vlookup 함수를 많이 씁니다.
vlooup함수를 쓰기 어려운 이유는 두 가지입니다. 하나는 함수 자체에 넣어야 할 항목이 4가지인데, 이게 직관적이지 않습니다. 아마도 세 번쨰, 네 번째 들어가는 함수 항목(인수)가 생경합니다. 숫자를 적어야 하고, TRUE 또는 FALSE를 적어야 합니다. 게다가 일치하는 것을 찾으라고 하면 FALSE를 선택해야 합니다.
두 번쨰는 자료 정리를 제대로 못한 것입니다. vlookup 함수를 잘 쓰려면 데이터를 잘 정돈해야 합니다. 내용 앞 뒤에 있는 여백이 있으면 제대로 못찾습니다. 숫자와 숫자 형태의 문자를 하나로 일치해야 합니다. 셀 병합을 없애야 하는 등 사전 작업이 필요합니다.
'엑셀 함수' 카테고리의 다른 글
| VLOOKUP()함수: 구간별로 금액을 다르게 적용하는 사례 (0) | 2016.05.09 |
|---|---|
| vlookup()함수로 2015년 소득세율 계산하기 (0) | 2016.05.08 |
