연차 수당은 근로자의 근속 기간에 따라 각 개인별로 발생하는 개수와 수당 금액이 달라지는 경우 별도의 “연차 관리 대장”과 같은 서식으로 관리 하는 경우를 종종 볼 수 있습니다.
이번 글에서는 근속 기간에 따라 달라지는 연차 수당을 계산하는 수식을 예제 서식에 맞춰 작성해 보도록 하겠습니다.
예제 서식에서 작성된 수식을 확인해 보고 사용자의 환경에 맞도록 서식을 만들고 서식에 맞춰 수식을 작성하여 업무 효율을 증대시켜보시길 바랍니다.
예제.
준비한 예제 서식에서는 A열 4행까지 근속 기간을 0~5년차 단위로 구분하고 B열 5행까지는 각각의 연차에 해당하는 지급 금액을 작성했습니다.
A,B열 4행에 작성한 각 연차별 지금 급액은 A열 7행부터 E열 11행까지 각각의 근로자의 입사일로부터 연차 수당을 지급하는 금액을 계산 하는 기준일로부터 근속 기간을 연차로 계산하여 작성하고 근속 기간에 해당하는 금액을 참조하여 결과값으로 가져올 수 있도록 합니다.
우리가 수식으로 결과값을 자동으로 가져오는 셀은 C7셀부터 E11셀까지로 근속 기간을 계산 하는 수식과 금액을 가져오는 수식은 이전에 작성했던 글을 참고할 수 있습니다.
근속기간에 따라 정해진 금액 계산 수식.
예제 서식에서 결과값을 자동으로 계산 하는 각각의 수식을 작성해 보도록 합시다.
연차 수당 지급 기준일.
연사 수당 지급 기준일은 연차 수당을 계산하기 위한 기준일 매번 확인하는 기준일이 별도로 있다면 수식을 작성하지 않고 기준일자를 직접 작성해 주어야 합니다.
다만 연차 수당을 계산하는 시점이 매번 현재 시점이라면 =Today()로 수식을 작성해서 현재 일자를 결과값으로 반환하면 날짜를 매번 입력하는 번거로움을 줄일 수 있습니다.
근속 기간 계산 수식.
근속 기간 계산 수식은 입사일과 연차 수당 지급 기준일로부터의 경과 기간을 계산하는것으로 이전에 작성했던 글을 참고하여 수식을 작성 할 수 있습니다.
| =DATEDIF(B7,C7,”Y”) |
근속 기간에 따라 차등 지급되는 연차 수당 지급 금액 계산 수식.
근속 기간에 따라 지급해야할 연차 수당 지급 금액은 A2:B4셀 범위를 참조해서 D열의 근속기간에 해당하는 금액을 결과값으로 가져올 수 있습니다.
이렇게 참조범위에서 조건을 만족하는 결과값을 반환 하는 경우 Vlookup함수 수식으로도 작성할 수 있지만 필자의 경우에는 Vlookup함수 보다는 수식이 폭넓은 범위에서 계산할 수 있는 index, match함수 수식을 선호하기 때문에 index, match함수 수식으로 결과값을 반환 할 수 있도록 작성해 보겠습니다.
| =INDEX($B$2:$B$4,MATCH($D7,$A$2:$A$4,1)) |
결과값을 가져올 참조 범위는 B2:B4, 찾을 조건은 D열, D열의 조건을 참조 범위는 A2:A4, 일치하는 유형은 1로 해서 index, match함수 수식을 작성해 주면 Vlookup함수 수식의 결과값과 동일한 결과값을 얻을 수 있습니다.
vlookup함수 수식으로도 결과값을 가져오는 수식을 작성해 보면 =VLOOKUP($D7,$A$2:$B$4,2,TRUE)과 같이 작성할 수 있고 index, match함수 수식 결과와 동일한 값을 반환합니다.
다만 vlookup함수 수식에서는 다중조건을 사용할 수 없다는 한계가 있을뿐 아니라 행과 열에서 한번에 조건을 만족하는 결과값을 가져올 수 없기 때문에 서식이 변하게 되면 결과값을 찾아야 할 열 번호를 수정해줘야 하는 번거로움이 생기게 됩니다.
글을 마치며…
근속 기간에 따라 정해져 있는 연차 수당 지급 금액을 근속년수에 따라 자동으로 계산되는 서식과 수식을 작성해 봤습니다.
특히 근속 기간에 따라서 정해져 있는 연차 수당 지급 금액을 결과값으로 가져오는 수식을 작성하기 위해서는 우리가 많이 알고 있는 vlookup함수로도 작성할 수 있지만 vlookup함수의 가장 큰 한계점으로 다중조건을 계산할 수 없다는것입니다.
따라서 서식이 수정되었을때 재대로된 결과값을 가져올 수 없고 수식을 새로 수정해주는 번거로움이 발생할 수 있기 때문에 본문에서도 언급했듯이 필자의 경우에는 index, match함수 수식을 선호하고 있습니다.
index, match함수 수식은 vlookup, hlookup, xlookup등의 함수 수식으로 작성되는 다양한 형태로 활용할 수 있기 때문에 계산할 수 있는 확장 범위가 넓고 다중조건뿐만 아니라 가로 세로의 조건을 모두 참조할 수 있다는 장점이 있습니다.
결국 수식을 작성해서 결과값을 반환하기 위한 정답은 정해져 있지 않고 사용자가 사용하기 쉽고 빠르게 작성할 수 있는 함수로 수식을 작성하는것이 가장 좋은 수식이라고 할 수 있을것같습니다.
