엑셀(Excel) 급여 계산 자동 서식과 수식 작성 그리고 2023년 4대보험 요율 최저시급 변경을 확인해 보자.

안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.

저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.

기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.

---

 

 

 

 

2023년 4대보험 요율과 급여 계산 자동 서식 수식 작성 방법 썸네일 이미지.

엑셀(excel) 작업으로 급여 계산을 할 경우 자동 계산 되는 수식이 굉장히 복잡해서 기존에 누군가 작성해 놓은 서식을 그대로 이용하거나 또는 기존의 서식에 불편함이 발생했는데도 불구하고 수식이 복잡하여 사용자의 환경에 맞게 수정하지 못하고 그대로 사용하는 경우가 발생할 수 있습니다.

 

혹은 사용자의 환경에 맞게 급여 계산 서식을 처음부터 만들려고 하면 어디서부터 어떻게 작성해야할지 막막하다가 포기 하게 되는 경우도 생기게 되죠.

 

이번 엑셀(excel) 기록에서는 2023년 급여 자동 계산 서식 샘플 예제 서식을 작성해 보고 입력된 데이터 값을 기초로 원하는 결과값이 입력 될 수 있는 수식을 작성해 보고자합니다.

 

1. 함께 확인해 볼 수 있는 글 목록.

오늘 기록할 글과 함께 급여 계산 수식과 관련된 다음의 글을 함께 확인해 보시면 "2023년 급여 자동 계산 샘플 예제 서식"을 사용자의 환경에 맞게 수정하거나 사용자가 원하는 형태의 급여 계산 서식을 작성하는데 참고해 보시길 바라겠습니다.

1. 엑셀(Excel) 근로 소득세를 자동 계산해주는 엑셀 수식을 작성해 보자.

2. 엑셀(Excel) 날짜를 입력하면 요일과 몇주차인지 한번에 알 수 있는 수식을 작성해 보자.

3. 엑셀(Excel)] - 엑셀(Excel) 입력한 날짜의 해당 월 시작일부터 종료일까지 자동으로 작성되는 수식을 작성해 보자.

4. 엑셀(Excel) 시간 계산 출퇴근시간, 근로시간, 근무시간, 급여시간 계산

5. 엑셀(Excel) 요일별, 근로 시간별로 적용되는 급여 계산 시간으로 환산 계산 수식을 작성해 보자.

6. 엑셀(Excel) 엑셀 급여 계산 주휴수당 계산 수식을 작성해 보자.

7. 엑셀(Excel) 조건 최소값,최대값 Max(if, Min(if 수식 사용 방법.

8. 엑셀(Excel) round함수 반올림 반내림, 원단위 절사 수식 작성을 해보자.

9. 엑셀(Excel) 다중 조건 합계 계산 수식 작성을 해보자.

10. 엑셀(Excel) 4대보험 계산 국민연금, 건강보험, 고용보험, 산재보험 계산 수식을 작성해 보자.

11. 엑셀(Excel) 비 근로 시간 계산 점심, 저녁 심야 시간 규칙적으로 발생하는 시간 계산 수식 작성을 해보자.

12. 엑셀(Excel) 연차 남은 개수 사용 개수 발생 개수 수식을 작성해 보자.

13. 엑셀(Excel) 조건부 서식 텍스트 변환 if수식 vlookup수식 색깔 음영 폰트 사이즈 굵기등의 수식 결과값 반영하는 기능과 수식을 사용해 보자.

14. 엑셀(Excel) 주민 등록 번호로 나이 계산 수식 작성을 해보자.

15. 엑셀(Excel) 급여대장 서식과 공제액 계산 수식을 작성해 보자.

 

 

급여 계산 서식과 수식을 작성하기 전에 2023년에 변경되어 적용되는 4대보험 요율을 현재 시점에서 먼저 확인하고 급여 계산 서식과 수식을 차례로 살펴 보기로 하겠습니다.

 

2. 2023년 4대 보험 적용 요율.

4대보험 적용 요율은 거의 매년 상승하고 있는 추세인것 같습니다.

2022년에 비해서 2023년에도 건강보험료, 장기요양보험료 요율이 일부 상승 하였고 국민연금과 고용보험은 동결이지만 요율 상승이 확정되고 적용 시점부터는 상승된 요율을 적용해 주어야합니다.

 

2022년과 2023년 4대 보험 적용 변경 요율을 비교해서 확인해 보도록 하겠습니다.

4대보험	2022년	2023년	비고
국민연금	9%	9%
건강보험	6.99%	7.09%	0.1% 상승
장기요양보험	12.27%	12.81%	0.54% 상승
고용보험	1.8%	1.8%

표에서 보는것과 같이 국민연금은 2022년도와 9%동일하여 사업주, 근로자 각각 4.5%를 적용 하고 건강보험은 2022년도에 비해서 0.1%상승하여 사업주, 근로자 각각 3.495%적용 요율이 2023년도에는 사업주와 근로자 각각 3.545% 요율로 변경, 장기요양보험은 2022년도에 비해서 0.54% 상승하여 사업주와 근로자 각각 부담액이 건강보험료의 6.405%요율이 적용되는것으로 볼 수 있습니다.

 

 

3. 급여 계산 샘플 예제 서식.

급여 계산 샘플 예제 서식은 총 4개의 시트로 나누어 작성하였고, 근로기준법의 일부 변경으로 인하여 2021년 11월부터 급여 지급 명세서 의무화가 되어 급여명세서 시트까지 포함하여 샘플 예제 서식을 작성해 보았습니다.

 

각 시트별로 작성되어 있는 내용을 간략하게 확인해 보도록 하겠습니다.

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

2023년 급여 계산 샘플 예제 서식 근로소득간이세액표 시트 이미지.

근로소득간이세액표 시트에서는 국세청에서 제공하는 근로소득간이세액표를 참조 하여 소득세를 계산 하기 위한 테이블을 입력되어 있는 표의 데이터로 작성되어 있습니다.

 

근로소득간이세액표는 직접 입력해주는것이 아니고 국세청에서 제공하는 근로소득간이세액표를 엑셀파일로 다운로드 한 후 소득세를 계산하기 위한 수식이 참조할 수 있도록 원본 데이터에서 몇가지 편집을 해 주었습니다.

 

자세한 내용은 다음의 글에서 추가로 확인해 볼 수 있습니다.

▶ 엑셀(Excel) 근로 소득세를 자동 계산해주는 엑셀 수식을 작성해 보자.

 

 

3-2. "기본정보" 시트.

2023년 급여 계산 샘플 예제 서식 기본정보 시트 이미지.

기본정보 시트에서는 4대보험 적용 요율과 국민연금 최저액과 최대액, 특근시간이 적용되는 시간, 해당연도 공휴일및 회사 창립일등, 재직연차마다 부여되는 연차 개수, 근로자 개인정보들을 입력해줌으로써 급여를 계산하기 위한 기본 데이터를 입력해 주는 시트로 볼 수 있습니다.

 

따라서 4대보험 요율이 변경되면 수식을 변경하지 않고, 기본정보 시트에서 변경된 요율을 입력해 주는것만으로도 변경된 요율이 적용할 수 있고, 공휴일과 회사 창립일등과 같이 휴무일을 지정하여 해당일자에 근로를 할 경우 특근 급여로 계산 할 수 있도록 할 수 있을뿐 아니라 연차 사용과 남은 연차 개수등의 정보를 모두 기본정보 시트에서 입력하고 수정하여 사용 할 수 있습니다.

 

왠만하면 초기 정보를 입력하고 매번 입력하는 시트는 아니고 근로자가 증원되거나 요율변경등이 있을 경우 입력해야 하는 데이터가 필요한 시트로 볼 수 있습니다.

 

 

3-3. "출퇴근입력" 시트.

2023년 급여 계산 샘플 예제 서식 출퇴근입력 시트 이미지.

출퇴근입력시트에는 실제로 매일같이 근로자의 출퇴근 시간을 입력해 주는 시트로 날짜, 이름, 출근시간과 퇴근시간, 비근로시간을 입력해 주게 되면 나머지 셀은 기본정보 시트에서 입력해둔 근로자의 정보 데이터를 수식에 의해서 자동으로 결과값이 입력되고, 출퇴근 시간에 대한 근로시간과 급여 계산 근로시간으로의 환산 시간이 계산되고 해당 근로자가 입력된 날짜에 해당하는 주차에 총 몇시간 근로 했는지 그리고 남아있는 연차는 몇개가 남아있는지까지 계산되는 수식을 입력하여 결과값을 확인할 수 있는 시트입니다.

 

소속부서, 직책, 직급은 수식을 작성하지는 않았는데 기본정보에서 좀 더 구체적으로 입력하게되면 해당 셀까지도 직접 입력하지 않고 수식을 작성하여 자동으로 결과값을 입력시키는 셀로 활용할 수 있습니다.

 

샘플 예제 서식에서는 기본정보에서 근로자의 세부적인 정보데이터까지 입력하지 않았기에 소속부서(H열), 직책(I열), 직급(J열)은 별도로 사용자가 필요로 하는분만 수식을 작성하여 활용할 수 있고, 필요하지 않다면 숨기기 기능으로 열을 숨겨서 사용하면 됩니다.

 

또한 결과값이 자동으로 채워지는 수식은 기본적으로 5,000행까지 수식이 입력되어 있기 때문에 5,000행을 넘어가는 데이터를 입력할 경우에는 수식을 윗행으로부터 복사하여 사용해야겠습니다.

 

① 조건부 서식 적용.

기본정보에서 작성한 2023년 공휴일 및 회사창립일고 같이 유급휴일에 해당하는 일자를 입력할 경우에는 행 전체에 회색 음영이 적용되도록 조건부 서식을 적용하고 토요일과 일요일인 경우에도 각각 다른 음영이 적용되도록하여 주중과 주말, 그리고 공휴일을 구분할 수 있도록 합니다.

 

조건부 서식은 수식으로 작성하여 적용하는데 조건부 서식의 적용 범위는 $A$3:$Z$5000로 동일하게 적용하였으니 만약 5,000행 이상의 데이터를 입력할 경우 조건부 서식의 범위를 변경할 필요가 있습니다.

 

작성한 수식은 다음과 같습니다.

1. 토요일 조건부 서식의 수식. =IF($F3="토",1) 2. 일요일 조건부 서식의 수식. =IF($F3="일",1) 3. 공휴일 조건부 서식의 수식. =IF(COUNTIFS(기본정보!$D$28:$D$57,$E3)>=1,1)

 

 

3-4. "급여명세서" 시트.

급여명세서 시트는 말 그대로 근로자의 급여명세서를 출력할 수 있는 서식을 작성한 서식으로 사용자가 직접 입력해줘야 하는 데이터는 많지는 않고 연도와 월, 이름을 입력해 주게 되면 기타 급여를 제외한 나머지 항목을 앞서 작성한 근로소득간이세액표, 기본정보, 출퇴근입력 시트에 입력된 데이터를 참조 하여 결과값을 계산하여 표기 하게 됩니다.

 

다만 기타 급여는 자동으로 결과값이 표기 되지 않는 항목으로 상여금, 연말정산 환급금등과 같이 매번 동일하게 지급되는 급여 항목이 아닌 항목들에 대해서 지급 명목과 지급 금액을 직접 입력해 줘야 하는 항목입니다.

 

샘플 예제 서식 급여 명세서 시트에 표기 되는 항목은 기본적으로 실제 근로시간과 급여 산출 근로시간에 따라 시급과 세전급여가 표기 되고, 주휴수당의 지급을 근로일수와 근로시간 주휴수당 금액을 표기 하여 지급되지 주휴수당의 지급 조건이 유효한지 알 수 있고, 급여 공제 내역으로 매번 공제내역이 발생하는 4대보험과 소득세, 주민세가 자동으로 표기 되도록 합니다.

 

만약, 사용자의 급여 계산 항목에서 급여 계산 항목에 매번 발생하는 항목이 있다면 사용자의 환경에 맞는 항목들을 추가 하여 수식을 작성하여 활용할 수 있습니다.

 

 

4. 급여 자동 계산 서식 수식 작성.

급여 자동 계산 샘플 서식에서 직접 입력하는 데이터를 기초로 자동으로 계산되어 결과값을 표기 하는 항목들과 각 항목들의 수식이 작성된것을 시트별로 확인해 보도록 하겠습니다.

 

다만, 근로소득간이세액표는 수식의 작성이 아닌 원본데이터를 일부 편집 하는것으로 앞서도 언급했듯이 근로 소득세를 계산 하는 수식을 작성하기 위해 이전에 작성한 글을 참고 해 보시길 바랍니다.

▶ 엑셀(Excel) 근로 소득세를 자동 계산해주는 엑셀 수식을 작성해 보자.

 

또한 기본적으로 급여 자동 계산 서식은 수식이 입력된 셀에 직접 입력 데이터의 입력 방지를 위해서 시트보호 설정을 해 두었습니다. 따라서 수식의 수정 또는 서식 변경을 하고자 하는 사용자는 시트보호 해제를 한 후에 수식 수정 또는 서식 변경을 해주어야 합니다.

 

 

4-1. 기본정보 시트 입력해야할 데이터와 계산 수식 작성.

기본정보 시트에서 우선 직접 입력해야할 데이터와 계산 수식을 작성하는 항목들을 하나씩 확인해 보면 다음과 같이 확인해 볼 수 있습니다.

 

① A1:D7 - 4대보험 요율.

A1:D7범위의 4대보험 요율은 각 보험명칭마다 공제 되는 요율을 입력해주도록 합니다. 이때 요율은 사업주와 근로자 적용분을 합한 100% 요율을 입력해 주도록 합니다.

앞서 확인해 봤듯이 2023년 국민연금은 9%, 건강보험은 7.09%, 장기요양보험은 12.81%, 고용보험은 1.8%입니다.

 

② A9:D11 - 국민연금 최저액과 최대액.

국민연금은 적용요율이 9%이지만 최저액과 최대액이 정해져 있기 때문에 총 급여액이 350,000만원 이하이면 350,000원 * 9%, 5,530,000원 이상이면 5,530,000원 * 9%이기 때문에 최저액은 350,000입력 하고 최대액은 5,530,000원을 입력해 주도록 하고 최저액과 최대액이 변경될때 여기서 변경된 금액을 수정해서 입력해 주도록 합니다.

 

③ A16:D24 - 연차, 조퇴등의 경우에 급여를 지급하기 위해 인정하는 최대 근로 시간 설정.

근로자가 연차를 사용할 경우 유급휴무이기 때문에 기본 근로시간 8시간에 대한 급여를 지급해야 합니다.

또한 사용자의 환경에 따라 조퇴, 무급, 결근에 대해 별도의 급여를 지급하는 인정 근로시간이 있다면 해당하는 인정 근로 시간을 입력하여 출퇴근입력시트에 해당 텍스트가 입력될때 적용될 수 있는 참조 영역이 됩니다.

 

④A26:D57 - 공휴일 및 회사창립일등 입력.

사용자의 급여 계산 환경에서 공휴일로 적용되는 날짜를 입력해 주게 되면 출퇴근입력 시트에서 여기에 입력된 공휴일로 지정한 날짜에 혹시라도 근무할 경우 특근 근로시간으로 급여 계산 시간을 반영하게 됩니다.

 

⑤ F1:H52 - 재직기간별 발생하는 연차 개수 입력.

보통 재직기간의 연차가 늘어날수록 연차 개수를 추가해 주는 경우가 있기 때문에 각 연차마다 발생하는 연차 개수를 입력해 주도록 합니다.

만약, 연차 계산이 필요하지 않을경우에는 공란으로 두어도 무방합니다.

 

⑥ J:H - 근로자 기본 정보 입력.

근로자의 기본정보를 입력하는것으로 J열에는 단순히 순번을 입력하거나 사번을 부여하는 근로자마다의 특정 코드를 입력하는등의 사용자의 환경에 따라 입력해 주고, K열은 이름, L열은 소속부서, M열은 직책, N열은 직급, O열은 시급, P열은 근로 소득세를 계산 하기 위한 부양가족 수, Q열은 근로자의 입사일, S열에는 근로자의 주민등록번호를 직접 입력해 주도록 합니다.

 

 

R열에는 근로자의 정보를 입력한 Q열의 근로자 입사일을 기준으로 재직기간을 계산하여 F:H열에 입력한 해당 재직기간에 따라 발생하는 연차 개수를 계산하여 결과값을 표기 하게 됩니다.

 

수식은 다음과 같이 작성합니다.

=IF(Q3="","",INDEX($H$2:$H$52,MATCH(DATEDIF(Q3,TODAY(),"Y")&"년",$F$2:$F$52,0)))

 

T열은 S열에 입력한 근로자의 주민등록 번호를 참조하여 근로자의 만나이를 계산하는 수식을 작성하는데 근로자의 만 나이 계산은 국민연금 공제 기준 조건에 일치하는지를 확인하기 위해서 작성하는 수식으로 다음과 같이 수식을 작성 할 수 있습니다.

=IF(S3="","", DATEDIF(IF(OR(MID(S3,8,1)="1",MID(S3,8,1)="2"), 19&TEXT(LEFT(S3,6),"00-00-00"),20&TEXT(LEFT(S3,6),"00-00-00")),TODAY(),"Y"))

주민등록번호로 나이를 계산하는 수식 작성 방법은 다음의 글을 참고 하여 좀 더 구체적으로 내용을 확인해 볼 수 있습니다.

▶ 엑셀(Excel) 주민 등록 번호로 나이 계산 수식 작성을 해보자.

 

U열은 근로자가 실제 해당 연도에 사용한 연차를 차감하고 사용가능한 연차 개수를결과값으로 표기 하는 수식을 다음과 같이 작성 합니다.

{=MIN(IF(출퇴근입력!$G$3:$G$5000=기본정보!$K3,출퇴근입력!$Z$3:$Z$5000))}

위 수식을 작성할때 주의할점은 배열수식이기 때문에 =MIN(IF(출퇴근입력!$G$3:$G$5000=기본정보!$K3,출퇴근입력!$Z$3:$Z$5000))수식을 작성한 후 수식의 완료를 ctrl+shift+enter로 완료를 해 주어 배열수식이 적용되는 "{}"기호가 수식의 앞뒤에 입력될 수 있도록 배열수식으로 작성을 해주어야 한다는것입니다.

 

조건을 충족하는 최대값과 최소값을 계산하는 수식은 다음의 글을 함께 참고해 보시길 바라겠습니다.

▶ 엑셀(Excel) 조건 최소값,최대값 Max(if, Min(if 수식 사용 방법.

 

근로자 기본정보를 참조 하는 시트에서는 기본적으로 1,000행까지만을 참조하고 있기 때문에 근로자 기본정보가 1,000행을 넘기는 경우까지 작성할 경우 근로자 기본정보를 참조 하는 범위를 1,000행 이상으로 수정해 주어야 합니다.

 

 

4-2. 출퇴근입력 시트 직접 입력 데이터와 수식 작성 데이터.

출퇴근입력 시트에서는 근로자의 출퇴근시간을 기록하여 기본적으로 근로 시간을 계산 하기 위한 시트 이지만 급여를 계산 하기 위한 대부분의 데이터를 수식에 의해서 결과값으로 입력하는 데이터의 기초를 만드는 시트라고 할 수 있습니다.

 

직접 입력하는 내용은 E열의 출근 날짜, G열의 근로자 이름, H열의 소속부서, I열의 직책, J열의 직급, K열의 출근시간, L열의 퇴근시간, M, N, O, P열의 비 근로시간을 직접 입력해 주어야 하는 데이터입니다.

 

다만, 샘플 예제 서식에서는 H~J열의 소속부서, 직책, 직급의 데이터를 참조하는 수식은 작성되어 있지 않기 때문에 공란으로 둘경우 해당열은 숨기기하여 서식을 좀 더 보기 좋게 관리 할 수 있습니다.

 

또한 해당 데이터를 활용할 경우에는 앞서 작성한 기본정보 시트의 근로자 기본정보에서 각각의 데이터를 결과값으로 입력할 수 있는 수식을 작성하여 직접 입력하지 않아도 데이터를 표기 할 수도 있겠습니다.

 

수식을 입력해서 데이터 값을 표기 하는 열을 하나씩 살펴 보도록 하겠습니다.

A열 ~ F열은 E열에 입력한 날짜를 기준으로 연도, 월, 일자, 해당일자의 주차를 각각 나누어 자동으로 표기 되는 수식으로 다음과 같이 수식을 해당열에 각각 작성할 수 있습니다.

1. A열 =IF(E3="","",YEAR(E3)) 2. B열 =IF(E3="","",MONTH(E3)) 3. C열 =IF(E3="","",DAY(E3)) 4. D열 =IF(IFERROR(WEEKNUM(E3)-WEEKNUM(E3-DAY(E3)+1)+1,"")=0,"", IFERROR(WEEKNUM(E3)-WEEKNUM(E3-DAY(E3)+1)+1,"")) 5. F열 =TEXT(E3,"AAA")

 

 

다음은 근로시간과 근로자의 남은 연차를 계산 하는 수식을 작성하는 열에 작성된 수식을 각각 다음과 같이 작성해 보도록 합니다.

1. Q열 - 실제 기본 근로 시간 =IFERROR(IF( (IF(COUNTIFS(기본정보!$B$17:$B$24,출퇴근입력!K3)>=1, VLOOKUP(출퇴근입력!K3,기본정보!$B$17:$D$24,2,FALSE), IF(OR(K3="",L3=""),"",IF(K3>L3,1+L3-K3, IF(K3<L3,L3-K3))*24))-SUM(M3:P3))>8,8, IF(COUNTIFS(기본정보!$B$17:$B$24,출퇴근입력!K3)>=1, VLOOKUP(출퇴근입력!K3,기본정보!$B$17:$D$24,2,FALSE),IF(OR(K3="",L3=""),"", IF(K3>L3,1+L3-K3,IF(K3<L3,L3-K3))*24))-SUM(M3:P3)),"") 2. R열 - 실제 연장 근로 시간 =IF(IF(OR(K3="",L3=""),"", IF(((IF(K3<L3,SUM(L3-K3)*24, IF(K3>L3,SUM(L3-K3+1)*24)))-SUM(M3:P3))<8,0, IF(K3<L3,((SUM(L3-K3)*24)-SUM(M3:P3))-Q3, IF(K3>L3,((SUM(L3-K3+1)*24)-SUM(M3:P3))-Q3)))-S3)<=0,0, IF(OR(K3="",L3=""),"", IF(((IF(K3<L3,SUM(L3-K3)*24, IF(K3>L3,SUM(L3-K3+1)*24)))-SUM(M3:P3))<8,0, IF(K3<L3,((SUM(L3-K3)*24)-SUM(M3:P3))-Q3, IF(K3>L3,((SUM(L3-K3+1)*24)-SUM(M3:P3))-Q3)))-S3)) 3. S열 - 실제 특근 근로 시간 =IFERROR(IF(OR(K3="",L3=""),"", IF(K3>L3,MIN(TIME(기본정보!$D$14,0,0),L3)+1, MAX(TIME(기본정보!$A$14,0,0),L3))-MAX(K3,TIME(기본정보!$A$14,0,0)))*24,"") 4. T열 - 실제 근로 시간 합계 =IF(SUM(Q3:S3)<=0,"",SUM(Q3:S3))

 

실제근로시간을 기준으로 급여를 계산 하기 위한 근로시간으로 환산하기 위한 근로시간의 수식은 각각의 해당 열에 다음과 같이 작성할 수 있습니다.

1. U열 - 급여 계산 산출 기본 근로시간 =IF(COUNTIFS(기본정보!$D$28:$D$57,출퇴근입력!$E3)=1,($Q3*2), IF(AND(TEXT($E3,"AAA")="토",SUMIFS($Q$3:$Q$5000,$D$3:$D$5000,$D3)<=40),$Q3*1, IF(AND(TEXT($E3,"AAA")="토",SUMIFS($Q$3:$Q$5000,$D$3:$D$5000,$D3)>40),($Q3*1.5), IF(AND(TEXT($E3,"AAA")="일",SUMIFS($Q$3:$Q$5000,$D$3:$D$5000,$D3)<=40),$Q3*1, IF(AND(TEXT($E3,"AAA")="일",SUMIFS($Q$3:$Q$5000,$D$3:$D$5000,$D3)>40),($Q3*2),$Q3 ))))) 2. V열 - 급여 계산 산출 연장 근로시간 =IFERROR(IF(COUNTIFS(기본정보!$D$28:$D$57,출퇴근입력!$E3)=1,($R3*2), IF(AND(TEXT($E3,"AAA")="일", SUMIFS($Q$3:$Q$5000,$D$3:$D$5000,$D3)>40),($R3*2),$R3*1.5)),"") 3. W열 - 급여 계산 산출 특근 근로시간 =IFERROR(SUM(S3*2),"") 4. X열 - 급여 계산 산출 근로시간의 합계 =SUM(U3:W3)

 

시급제, 현장직, 생산직, 아르바이트와 같이 월급제가 아닌 시급으로 급여를 계산 하는 경우에는 주휴수당을 별도로 계산하여 총 급여에 포함시켜야 하는데 주휴 수당을 지급하기 위한 일부 충족조건으로 해당 주차의 총 근로시간이기 때문에 근로자가 해당 주차에 근로한 근로시간을 계산해야 할 필요가 있습니다.

 

따라서 다음과 같이 해당 주차에 근무한 근로시간과 사용가능한 연차 개수를 계산하여 결과값으로 표기 하기 위해서 다음과 같이 수식을 각각의 해당열에 작성할 수 있습니다.

1. Y열 - 해당 주차에 해당 근로자의 근로시간 합계 =IF($E3="","", SUMIFS($T$3:$T$5000,$A$3:$A$5000,$A3,$B$3:$B$5000,$B3,$D$3:$D$5000,$D3,$G$3:$G$5000,$G3)) 2. Z열 - 해당 근로자가 사용하고 남아 있는 연차 개수. =IF(AND(K3="",L3=""),"", SUMIFS(기본정보!$R$3:$R$1000,기본정보!$K$3:$K$1000,출퇴근입력!G3) -COUNTIFS($K$3:$K$10000,"연차",$G$3:$G$10000,G3))

 

 

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

급여명세서 시트에서 직접 입력해야할 데이터는 위에서도 언급했듯이 연도와 월, 이름, 불규칙 지급 급여 항목을 직접 입력해 주도록 하고, 근로시간, 주휴수당, 4대보험과 소득세의 공제 금액을 계산하는 데이터는 다음과 같이 각각의 셀을 기준으로 수식을 작성 할 수 있습니다.

 

① 근로 시간 계산 수식.

1. D4셀 =IFERROR(VLOOKUP(급여명세서!$B4,기본정보!$K$3:$S$1000,2,FALSE),"") 2. F4셀 =IFERROR(VLOOKUP($B$4,기본정보!$K$3:$O$1000,5,FALSE),0) 3. B8셀 =SUMIFS(출퇴근입력!$Q$3:$Q$10000, 출퇴근입력!$G$3:$G$10000,$B$4,출퇴근입력!$A$3:$A$10000,$A$3,출퇴근입력!$B$3:$B$10000,$C$3) 4. B9셀 =SUMIFS(출퇴근입력!$R$3:$R$10000, 출퇴근입력!$G$3:$G$10000,$B$4,출퇴근입력!$A$3:$A$10000,$A$3,출퇴근입력!$B$3:$B$10000,$C$3) 5. B10셀 =SUMIFS(출퇴근입력!$S$3:$S$10000, 출퇴근입력!$G$3:$G$10000,$B$4,출퇴근입력!$A$3:$A$10000,$A$3,출퇴근입력!$B$3:$B$10000,$C$3) 6. C8:D8셀 =SUMIFS(출퇴근입력!$U$3:$U$5000, 출퇴근입력!$A$3:$A$5000,급여명세서!$A$3, 출퇴근입력!$B$3:$B$5000,급여명세서!$C$3,출퇴근입력!$G$3:$G$5000,급여명세서!$B$4) 7. C9:D9셀 =SUMIFS(출퇴근입력!$V$3:$V$5000, 출퇴근입력!$A$3:$A$5000,급여명세서!$A$3, 출퇴근입력!$B$3:$B$5000,급여명세서!$C$3,출퇴근입력!$G$3:$G$5000,급여명세서!$B$4) 8. C10:D10셀 =SUMIFS(출퇴근입력!$W$3:$W$5000, 출퇴근입력!$A$3:$A$5000,급여명세서!$A$3, 출퇴근입력!$B$3:$B$5000,급여명세서!$C$3,출퇴근입력!$G$3:$G$5000,급여명세서!$B$4)

 

 

② 주휴수당 계산 수식.

9. B15 ~ B19셀 =COUNTIFS(출퇴근입력!$G$3:$G$10000,$B$4, 출퇴근입력!$A$3:$A$10000,$A$3,출퇴근입력!$B$3:$B$10000,$C$3,출퇴근입력!$D$3:$D$10000,LEFT($A15,1)) 10. C15 ~ C19셀 =SUMIFS(출퇴근입력!$T$3:$T$10000, 출퇴근입력!$A$3:$A$10000,$A$3, 출퇴근입력!$B$3:$B$10000,$C$3,출퇴근입력!$G$3:$G$10000,$B$4,출퇴근입력!$D$3:$D$10000,LEFT($A15,1)) 11. D15 ~ D19셀 =IF(C15<15,0,IF(AND(C15>=15,C15<=40),((C15/40)*8)*$F$4,IF(C15>40,8*$F$4)))

 

③ 4대보험과 근로 소득세 공제 금액 계산 수식.

1. F23셀 - 국민연금 =IFERROR(ROUNDDOWN(IF(OR(LOOKUP(1,1/(기본정보!$K$3:$K$1000=$B$4),기본정보!$T$3:$T$1000)>=60,LOOKUP(1,1/(기본정보!$K$3:$K$1000=$B$4),기본정보!$T$3:$T$1000)<=17),0, IF($C$38=0,0, IF($C$38<=기본정보!$C$10,기본정보!$C$10*(기본정보!C3/2), IF($C$38>=기본정보!$C$11,기본정보!C11*(기본정보!C3/2),$C$38*(기본정보!C3/2) )))),-1),0) 2. F24셀 - 건강보험 =ROUNDDOWN((E11+D20)*(기본정보!C4/2),-1) 3. F25셀 - 장기요양보험 =ROUNDDOWN(SUM(F24*기본정보!C5),-1) 4. F26셀 - 고용보험 =ROUNDDOWN(SUM(E11+D20)*SUM(기본정보!C6/2),-1) 5. F27셀 - 근로소득세 =IFERROR(IF(C38<=10000000,VLOOKUP(C38/1000,근로소득간이세액표!$A$6:$M$652, MATCH(IF(LOOKUP(1,1/(기본정보!K3:K1000=$B$4),기본정보!$P$3:$P$1000)="",1, LOOKUP(1,1/(기본정보!K3:K1000=$B$4),기본정보!$P$3:$P$1000)),근로소득간이세액표!$A$5:$M$5,0),1), ROUND(IF(C38>10000000,VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(LOOKUP(1,1/(기본정보!K3:K1000=$B$4),기본정보!$P$3:$P$1000)="",1, LOOKUP(1,1/(기본정보!K3:K1000=$B$4),기본정보!$P$3:$P$1000)),근로소득간이세액표!$A$5:$M$5,0),1)+ IF(AND(C38>1000000,C38<=14000000),((C38-10000000)*0.98)*0.35, IF(AND(C38>14000000,C38<=28000000),(((C38-14000000)*0.98)*0.38)+1372000, IF(AND(C38>28000000,C38<=30000000),(((C38-28000000)*0.98)*0.4)+6585600, IF(AND(C38>30000000,C38<=45000000),((C38-30000000)*0.4)+7369600, IF(AND(C38>45000000,C38<=87000000),((C38-45000000)*0.42)+13369600, IF(C38>87000000,(((C38-87000000)*0.45)+31009600) ))))))),-1)),0) 6. F28셀 - 주민세 =ROUNDDOWN(SUM(F27*0.1),-1) 7. F38셀 - 총급여 =SUM(E11,D20,B36) 8. F40셀 - 실지급 급여 =SUM(C38-C39)

간단한 합계를 구하는 수식은 위에서 별도로 기록하지는 않았지만, 단순한 sum합계이기 때문에 어렵지 않게 작성할 수 있는 수식이기 때문에 제외하고 꼭 확인을 해야할 수식만을 위주로 작성해 보았습니다.

 

 

이번 급여 계산 자동 서식을 작성하면서 가장 염두해 뒀던것은 많은 인원을 관리할 경우 각 근로자별로 시트를 관리하기 어려운 단점을 보완하고자 했고, 4대보험 요율이 변경될때마다 수식자체에서 요율을 변경해야 하는 번거로움, 그리고 사용자의 환경에 따라서 특근 근로시간이 적용되는 시간이 다를수 있기 때문에 이것또한 계속해서 수식안에서 특근 근로 적용 시간을 수정하는대신 기본정보에서 수정하여 수식이 알아서 특근 적용 근로 시간을 계산 할 수 있도록 작성을 해 보았습니다.

 

해당 서식은 모든 급여 계산을 하는 사용자를 만족할수는 없을것입니다.

다만, 최대한 급여 계산과 관련하여 많은 질문들중에서 사용자의 환경에 맞게 수식을 수정하는 과정에서 수식이 길게 작성되고 복잡한 구조를 띄고 있기 때문에 수식의 수정시 괄호의 위치를 놓치게 되어 수식을 완료시킬수 없거나 오류값이 반영되는 부분들을 좀 더 쉽게 자동으로 데이터를 참조하여 결과값을 계산할 수 있도록 작성했다는점을 참고해 주시길 바랍니다.

 

또한 수식을 수정하거나 서식을 변경하는데 도움이 될 수 있는 참고할만한 글은 이 글을 시작하는 처음부분에서 링크를 달아두었으니 참고 하셔서 사용자의 환경에 맞는 서식을 작성하는데 도움이 될 수 있기를 바랍니다.

 

---

이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"2023년 급여 계산" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.

긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.

 

※ 2023년 급여 계산 샘플 예제 서식 다운로드.

 

 

글쓰는이작가 샘플 예제 서식 - 2023년 급여 계산..xlsx

0.95MB