스프레드시트 연차관리대장 자동 계산 서식 만들기

스프레드시트 연차관리대장 자동 계산 서식 만들기

기록처리된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다. 엑셀excel 작업으로 급여 계산을 할 경우 자동 계산 되는 수식이 굉장히 복잡해서 기존에 누군가 작성해 놓은 서식을 그대로 이용하거나 아니면 기존의 서식에 불편함이 발생했는데도 불구하고 수식이 복잡하여 사용자의 환경에 맞게 수정하지 못하고 그대로 활용하는 경우가 발생할 수 있습니다. 혹은 사용자의 환경에 맞게 급여 계산 서식을 처음부터 만들려고 하면 어디서부터 어떠한 방법으로 작성해야할지 막막하다가 포기 하게 되는 경우도 생기게 되죠. 이번 엑셀excel 기록에서는 2023년 급여 자동 계산 서식 샘플 예제 서식을 작성해 보고 입력된 데이터 값을 기초로 요구하는 결과값이 입력 될 수 있는 수식을 작성해 보고자합니다.

동일한 의미를 가지고 있는 필드

JIRA 티켓에 이렇게 많은 데이터가 포함되어 있었나? 하며 어떤 필드가 있는지 확인을 해보았습니다. 동일한 의미를 가지고 있는 필드가 2개 이상 존재하는 경우가 있었어요. JIRA 는 여러 명의 감시하는 사람을 표현하기 위해 복수 개의 필드를 만들어 스프레드시트 형태로 제공하는 듯 했다. 확인해보니 추출된 티켓 중 감시하는 사람이 5명이나 되는 티켓이 존재했다.

스프린트 기본 통계

2022년도에 우리 편은 약 30개 정도 스프린트를 수행했고 약 1,700여개의 티켓을 등록했다. 완료 유무와는 무관. 우리 편은 1 스프린트를 2주 단위로 설정하고 있기에 1년 52주에 데이터가 얼추 맞다는 것을 확인할 수 있었어요. 스프린트 2주 기간동안 평균적으로 약 60개의 티켓이 등록되었고 최대 100개, 최소 20개가 등록되는 스프린트도 있었어요. 특이 패턴으로는 티켓이 많이 등록되는 스프린트가 있다면 그 뒤로 23주 정도는 티켓이 점점 적게 등록된다는 점이었다.

이를 통해 우리 편은 큰 프로젝트를 시작하기에 앞서 플래닝된 모든 티켓을 등록해두고 작업하는 경향이 있다는 것을 데이터를 통해 알 수 있었어요.

3 근로소득간이세액표 시트.

근로소득간이세액표 시트에서는 국세청에서 제공하는 근로소득간이세액표를 참조 하여 소득세를 계산 하기 위한 테이블을 입력되어 있는 표의 데이터로 작성되어 있습니다. 근로소득간이세액표는 직접 입력해혜택이 아니고 국세청에서 제공하는 근로소득간이세액표를 엑셀파일로 다운로드 한 후 소득세를 계산하기 위한 수식이 참조할 수 있도록 원본 데이터에서 몇가지 편집을 해 주었습니다. 분명한 내용은 다음의 글에서 추가로 확인해 볼 수 있습니다.

4 기본정보 시트 주요 계산 수식 작성.

기본정보 시트에서 수식이 작성되는 항목은 R열의 근속연수에 따라 깨어나는 해당연도 연차 개수, T열의 주민등록번호를 참조 하여 해당 근로자의 만 나이 계산, U열에 해당 근로자가 해당 연도에 사용하고 남아 있는 연차 개수를 계산 하기 위한 수식을 다음과 같이 작성 할 수 있습니다. 위 수식을 작성하는데 주의해야 할점으로는 U열에 해당 근로자가 사용하고 남아 있는 연차 개수를 계산 하기 위한 수식을 배열수식으로 작성해야 해야하는 것으로 MINIF출퇴근입력G3G5000기본정보K3,출퇴근입력Z3Z5000수식을 작성한 후 수식의 완료는 ctrlshiftenter로 수식을 완성해야 배열 수식을 적용하여 계산 할 수 있습니다.

만약 배열수식을 적용하지 않고 일반 수식으로 작성하여 완료할 경우에는 요구하는 결과값을 재대로 표기 할 수 없겠습니다.

4 급여명세서 시트 직접 입력 데이터와 수식 작성 데이터.

급여명세서 시트에서 직접 입력해야할 데이터는 위에서도 언급했듯이 연도와 월, 이름, 불윤리 지급 급여 항목을 직접 입력해 주도록 하고, 근로시간, 주휴수당, 4대보험과 소득세의 공제 금액을 계산하는 데이터는 다음과 같이 각각의 셀을 기준으로 수식을 작성 할 수 있습니다. 근로 시간 계산 수식. 주휴수당 계산 수식. 4대보험과 근로 소득세 공제 금액 계산 수식. 단일한 합계를 구출하는 수식은 위에서 별도로 기록하지는 않았지만, 심플한 sum합계이기 때문에 어렵지 않게 작성할 수 있는 수식이기 때문에 빼고 꼭 확인을 해야할 수식만을 위주로 작성해 보았습니다.

이번 급여 계산 자동 서식을 작성하면서 가장 염두해 뒀던것은 많은 인원을 관리할 경우 각 근로자별로 시트를 관리하기 어려운 단점을 보완하고자 했고, 4대보험 요율이 변경될때마다.

자주 묻는 질문