안녕하세요. 행아아빠입니다.
대부분의 업무를 엑셀로 하시는분들이 굉장히 많으실텐데, 오늘은 엑셀로 근로자의 연차를 관리 할 수 있는 연차관리대장 자동 계산 서식을 만들어 보려고 합니다.
연차관리대장 서식은 다양한 형태로 사용자의 환경이나 데이터관리 방식에 따라 다양한 형태로 작성할 수 있는데, 여러분들이 만들고자 하는 연차관리대장의 서식과 틀리더라도 근속연수를 계산하는 수식과 같이 기간을 계산하고, 발생하는 연차를 계산하는 수식을 참고 해 보시길 바라겠습니다.
■ 행아아빠의 Youtube 채널.
● 주요 컨텐츠 주제 - 내일은로또왕 / 내일은엑셀왕
● Youtube 채널 주소 - https://www.youtube.com/@economic-freedom
■ 행아아빠 후원 안내.
● Youtube 채널 맴버십 후원 - https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA/join
● Youtube 댓글 후원 - Youtube 영상 댓글에서 "Super Thanks" 구매 후 댓글 작성.
● 내일은엑셀왕 대표 Youtube 영상 - https://youtu.be/oYVZHgv_sKM (2023년 급여 자동 계산 서식)
1. 연차 관리 대장 데이터 서식을 어떻게 작성할 것인가.
앞서말씀 드렸듯이 사용자의 환경에 따라서 연차 관리 대장에 작성할 데이터의 형식은 다양한 형태로 작성할 수 있습니다.
그렇다면 어떻게 데이터를 입력하고 입력된 데이터로 근로자마다 발생하는 연차를 계산하고, 사용한 연차를 차감하고, 또 1년이상, 1년미민 근로자마다 각각 적용되는 연차 발생 계산 조건과 1년이상 근속기간의 근로자들이 몇년마다 연차가 몇개씩 추가되는지와 같은 조건에 따라 연차 관리 대장 서식 데이터를 사용자의 환경에 맞게 서식을 작성하다 보니 정해진것은 없고, 굉장히 다양한 형태의 서식으로 작성할 수 있습니다.
따라서 여러분들이 연차 관리 대장 자동 서식을 작성한다고 했을때 데이터를 어떻게 입력하는것이 편하고 어떻게 작성했을때 근로자마다의 연차를 계산하고, 연차 관리대장으로 계산하고자 하는 방식이 어떤것인지를 먼저 생각해 보고 데이터를 정리하는것이 좋습니다.
엑셀 수식을 자유자재로 활용하고 작성할 수 있다면 서식을 작성하는 형태에 상관없이 자신이 계산하고자 하는 데이터들을 참조해서 원하는 결과값을 계산하도록 수식을 작성할 수 있겠지만, 보통은 주로 사용하는 함수가 있고, 사용하기 편한 함수, 함수와 수식을 자유자재로 활용할 수 없고 제한적으로 활용할 수 있다면 최대한 자신이 수식으로 계산하기 쉽도록 데이터를 정리할 수 있는 서식으로 만드는것이 추후에 수식을 수정하더라도 어렵지 않게 계산 수식들을 수정하고 사용하기 쉽도록 해야합니다.
1-1. 연차 관리 대장 샘플 예제 서식 작성.
연차 관리 대장 샘플 서식을 작성하는데 사용자마다의 주어진 환경에 따라 다양한 형태로 서식을 작성할 수 있기 때문에 오늘 작성하는 예제 서식 역시 여러분들이 연차관리대장 서식을 만드는데 참고하셔서 여러분들이 사용하기 편하고 필요한 데이터들을 계산하는 서식과 수식을 작성하는데 활용해 보시길 바라겠습니다.
연차관리대장서식을 작성하기 위해서 가장 기본적으로 생각해 볼 수 있는것은 입사일 기준으로 근속연수마다 발생하는 연차 개수가 있고, 근로자가 연차를 사용하면 근로자의 근속연수에서 발생한 연차에서 사용연차를 차감하고, 현재 근로자마다 남아 있는 연차 개수는 몇개인지, 또 근로자가 남아 있는 연차를 소진하지 못했을 경우 연차 수당으로 지급해야할 예상 비용을 얼마인지를 생각해 볼 수 있습니다.
이러한 기본 조건들만 기준으로 연차 관리 대장 샘플 예제 서식을 작성해 보도록 하겠습니다.
A열에는 근속연수, B열에는 근속연수마다 발생하는 연차 개수를 입력해주어 근속 연수가 1년이상일 경우 근속연수마다 발생하는 연차 개수를 작성해서 참조할 수 있는 데이터들을 입력해 주도록 합니다.
근속연수가 1년 미만의 경우에는 근속 개월수마다 연차 1개를 부여할 수 있는 데이터 표로 D열에는 근속 개월수, E열에는 근속 개월수마다 발생하는 연차 개수를 입력해서 참조할 수 있는 데이터표를 작성해 주도록 합니다.
G열부터 I열에는 근로자 명부를 작성해놓는다고 생각하고 G열에는 순번, H열에는 근로자 이름, I열에는 근로자의 입사일을 작성하고, J,K열에는 근속년수와 근속개월수, L열에는 근속연수와 근속개월수에 따라 발생하는 총 연차 개수, M열에는 총 연차 발생개수 - 사용한 연차 개수로 연차 잔여 개수, N열에는 근로자의 일평균 통상 임금, O열에는 연차 잔여 개수에 따라서 연차수당으로 지급해야할 예상 비용을 작성하는데 G열부터 I열까지는 직접 입력하고 J열부터 O열까지는 수식으로 결과값이 자동으로 계산되도록 합니다.
J열부터 O열까지의 수식을 작성하기 전에 근로자가 실제로 연차를 사용할 경우 연차 사용 관리 대장 데이터를 입력하는 서식을 먼저 작성한 후에 수식으로 결과값이 입력되어야할 순서로 수식을 입력해보도록 하겠습니다.
1-2. 연차사용관리대장 서식.
1-1번에서는 연차관리대장 서식으로 계산하고자 하는 데이터들의 기본 정보들을 직접 입력해 주는 데이터들을 "참조"시트에 작성을했다면 이번에는 "연차관리대장"시트에서 실제로 근로자가 연차를 사용할 경우 사용일자와 연차 차감일수등을 계산 할 수 있는 연차 사용 데이터 이력을 남겨놓는 시트를 작성해야겠습니다.
연차관리대장 시트에서는 A열에는 순번, B열에는 이름, C열에서는 연차 사용 시작일자, D열에는 연차 사용 종료일자, E열에는 연차, 반차, 조퇴 3종류로 분류해서 근로자가 실제 연차를 사용하는 데이터를 직접 입력해 주도록 합니다.
E열에 연차, 반차, 조퇴 3종류로 분류해서 작성 하는 이유로는 연차는 연차 사용일수만큼 차감을 하고 반차와 조퇴의 경우에는 연차 사용일수를 1개를 차감하는것이 아니고 0.5개를 차감하기 위한것으로 실제로 여러분들의 회사에서 0.5개와 같은 연차 사용은 인정해주지 않는다면 수식을 약간 수정해야 합니다.
수식을 작성하는열은 F열부터 I열까지인데, F열은 연차 사용 시작일자와 연차 사용 종료일자까지의 기간으로 연차 사용 개수를 계산해주는 수식을 작성하고, G열에는 현재 근로자의 근속기간에서 발생하는 총 연차 개수, H열에는 해당 근로자가 지금까지 사용한 연차 누적 사용 개수, I열에는 총 연차 개수 - 누적 사용 연차 개수를 차감해주어 연차 잔여 개수를 확인할 수 있는 수식을 작성해 주도록 합니다.
수식으로 결과값이 계산되는 셀의 수식을 다음과 같이 작성할 수 있습니다.
| 1. F열 - 연차 사용 기간. =IF(OR($E2="반차",$E2="조퇴"),0.5,DATEDIF(C2,D2,"d")+1) 2. G열 - 근로자의 근속기간에서 발생하는 총 연차 개수. =VLOOKUP($B2,참조!$H$4:$L$100,5,0) 3. H열 - 근로자가 현재까지 사용한 연차 누적 사용 개수. =SUMIFS($F$2:$F$999,$B$2:$B$999,$B2) 4. I열 - 해당 근로자가 사용할 수 있는 연차 개수. (총 발생 연차 - 연차 누적 사용 개수) =SUM(G2-H2) |
F열에서 만약 반차, 조퇴를 연차로 차감하는 개념이 0.5단위가 아니고 1일 단위라면 =DATEDIF(C5,D5,"d")+1만으로 수식을 계산하여 반차, 조퇴등을 사용하더라도 1개의 연차를 사용한 개수로 계산할 수 있습니다.
연차관리대장을 비롯해서 엑셀로 업무 데이터를 참조해서 어떠한 결과값을 계산하는 수식을 작성하고자 한다면 최대한 여러분이 활용할 수 있는 함수와 수식 범위를 고려해서 데이터를 작성하는것이 수식을 잘 활용할 수 있고 원하는 결과값 데이터들을 추출할 수 있습니다.
오늘은 엑셀로 연차관리대장 자동 서식을 만들기 위한 수식을 작성해 보았습니다.
여러분들의 실제 업무에서 활용해 보실 수 있기를 바라겠습니다
※ 연차관리대장 샘플 서식 다운로드.
Blog : https://hang-a-fafa.tistory.com
Youtube : https://www.youtube.com/@economic-freedom
Naver 까페 : https://cafe.naver.com/hatime
'엑셀' 카테고리의 다른 글
| 엑셀 자주 사용하는 5~9개 함수. (0) | 2023.04.19 |
|---|---|
| 엑셀 차량 배차 관리 자동 서식. (0) | 2023.04.07 |
| 엑셀 날짜와 시간으로 입력된 데이터를 날짜와 시간으로 분리 시키는 수식 작성 방법. (0) | 2023.03.02 |
| 엑셀 날짜와 시간대별 예약 관리 현황 서식과 수식 작성 방법. (1) | 2023.03.02 |
| 엑셀 급여 계산 사용자 조건에 맞는 급여 계산 서식 작성 방법. (0) | 2023.02.27 |
댓글