Excel [지식인 해결 시리즈] 001. lookup 함수를 이용한 양도세 구간 구하기

전체 목차

 

001. lookup 함수를 이용한 양도세 구간 구하기

002. 공백 셀 제거 후 정렬

003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)

004. 목록상자, 토글버튼 연동

005. 다른 시트에 있는 특정 데이터를 복사해오기

006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)

 

포스트 목차.

 

01. 문제

02. 문제해결 방법

03. 예제 및 설명

 

01. 문제

사진으로 올리셔서 잘 안보이기는 하지만 질문을 해석하자면, 양도차익에 따라 결정되는 양도세율을 결정하는 문제로 보입니다.

 

셀의 특정 부분에 양도차익을 입력하면, 양도세율 및 보정값에 따라 세율을 계산해주면 되는것 같습니다.

 

[여기서 잠깐] 양도차익의 결정은 본 포스트에서 다루지는 않겠습니다. 질문자가 첨부한 그림을 보면 알 수 있듯이 보유기간, 필요경비, 양도금액, 취득금액, 기본공제 등을 따져봐야됩니다. 양도세는 양도차익이 1200만원 이하인 경우 양도차익의 6%를 내야합니다. 그리고 이것은 구간별로 적용되는데요, 양도차익이 2400만원이라면 1200만원에 대해서는 6%를 내야하고, 나머지 1200만원에 대해서는 15%를 적용해서 내야됩니다.  이런걸 나눠서 계산하기 귀찮은데 그냥 2400만원의 15%를 계산한 후에 옆에 있는 보정값을 빼주면 같은 계산이 나오게 됩니다.

 

02. 문제해결 방법

 

결국 우리가 해야되는 것은 양도차익을 입력했을 때, 그게 어느 구간에 걸리는지를 구하는 겁니다. 당연히 여러가지 방법이 있을 수 있을거에요. 중복 IF문을 써서 계산할 수도 있을거고요. 본 포스트에서는 lookup 함수를 이용해서 해결해보겠습니다.

 

lookup (찾는값, 찾는값이 있는 범위, 찾는 값이 있을 때 가져올 값이 있는 범위)

 

위의 그림은 lookup 함수의 예제입니다.

F20셀에 있는 값을 기준으로 요율을 찾아오는 작업입니다.

찾는 값은 F20에 있는 4이고, 찾는 값이 있는 범위는 A21:A28까지입니다. 찾는 값이 있을 때 가져올 값이 있는 범위는 B21:B28까지입니다.

 

lookup 함수 사용 시 주의할 점 찾는 값이 있는 범위에 있는 값들은 정렬이 되어 있어야 한다. 찾는 값이 있는 범위, 찾는 값이 있을 때 가져올 값이 있는 범위의 크기가 동일해야 한다.

 

남은 것은 양도차익을 입력했을 때 적정한 인덱스를 만드는 과정입니다.

 

F열에 B열에 있는 값과 H3에 있는 양도차익을 비교하여, 양도차익이 큰 경우 1을 반환하고, 양도차이익 크지 않은 경우 0을 반환하는 로직을 작성합니다.

 

양도차익이 12,000,000 이하라면 모든 F열의 값이 0일 것입니다.

양도차익이 12,000,000 초과하면 F2의 값이 1이되고, 단계별로 1씩 증가할 것입니다. 

이러한 값을 이용하여, F10 열에 F2:F8까지의 합에 +1한 값을 작성해두면, 이 값이 구간을 지칭하는 인덱스 값이 됩니다.

 

예를 들어, 양도차익이 46,000,000원이면 F2만 1이 되고, F3~F8까지는 0이되어 F10은 2가됩니다. 인덱스 2에 해당하는 요율은 0.15입니다.

 

03. 예제 및 설명

 

요율을 구하기 위한 인덱스를 구했으니, 위에서 다룬 lookup  함수를 이용하여, 요율, 보정값(그냥 조정계수라고 썼습니다.)을 가져옵니다.

 

요율 가져오기

 

보정값 가져오기

세금 계산하기