엑셀 함수를 사용해서 수식을 작성한 문서는 원하는 계산 결과값을 데이터값을 입력함에따라 자동으로 표기된다는점에서 유용하게 사용할 수 있습니다.
그러나 시간계산의 경우 단순 숫자 계산이 아니고 수식으로 표현해야 할 경우의수가 복잡하고 환경에 따라 다양하게 표현해야 하기등의 조건들 때문에 수식을 작성하면서 난관에 부딧히는 경우가 많습니다.
이번 글에서는 시간 계산과 관련하여 근로 시간과 급여 계산 자동 서식을 작성해 보도록 하겠습니다.
| 급여, 근무시간 계산 서식을 작성하기 위해 다음의 글을 참조할 수 있습니다. |
본문에서 작성된 수식을 참조하여 사용자의 환경에 맞도록 수정 또는 원본 수식을 활용하여 업무 효율을 상승 시켜보시길 바랍니다.
※ 샘플 서식은 본문 하단에서 다운로드 할 수 있습니다.
근로 시간 유형.
급여를 계산하기 위한 서식을 작성하기 위해서는 근로 시간 유형을 분류할 필요가 있습니다.
월급제, 연봉제와 같은 급여계산에서는 근로 시간을 별도로 계산할 필요가 없을것이고, 환경에 따라 추가 근로 수당, 연장 근로 수당만을 별도로 계산하여 급여를 산정하게 되므로 큰 어려움 없이 엑셀 서식을 작성 할 수 있을것입니다.
근로 시간으로 급여를 계산하고 산정하는 근로 유형으로 시급제, 현장직, 생산직, 아르바이트등과 같은 형태의 급여를 계산하고자 하는 경우에는 기본근로시간, 추가/연장 근로시간, 심야 근로시간과 같은 형태로 근로 시간을 분류하여 급여를 계산하게 됩니다.
- 기본 근로 시간 – 일반적으로 8시간의 근로 시간.
- 추가/연장 근로 시간 – 기본 근로 시간 외 근로 시간.
- 심야 근로 시간 – 일반적으로 22시~익일 06시까지의 근로시간.
근로 시간 유형별 급여 계산.
일반적으로 근로 시간 유형별로 급여 계산식은 다음과 같습니다.
- 기본 근로 시간 – *1
- 추가/연장 근로 시간 – *1.5
- 심야 근로 시간 – *2 또는 *0.5
심야 근로 시간의 경우 기본 시급 *2배로 적용하기 때문에 사용자의 계산 방식에 따라 추가/연장 근로 시간을 심야 근로시간까지 포함하여 *1.5배로 계산 하는 경우 심야 근로 시간은 *0.5배로 계산할 수 있고, 심야 근로시간을 제외한 추가/연장 근로 시간만을 *1.5배로 계산 하는 경우에는 심야 근로시간 *2배로 계산 할 수 있습니다.
Ex) 추가/연장 근로시간 : 5시간, 심야 근로 시간 : 3시간, 추가/연장 + 심야 근로 시간 = 8시간일 경우 급여 계산식은 다음과 같이 계산 할 수 있습니다.
계산식1. (추가/연장근로시간 *1.5) + (심야근로시간 *2)
계산식2. ((추가/연장근로시간 + 심야근로시간)*1.5)) + (심야근로시간 *0.5)
사용자의 편의에 따라 계산식1,2를 선택 적용하여 계산하되 그 값은 동일하게 나오게 됩니다.
급여 계산시 주휴 수당 및 공제 유형, 적용 요율.
이번글에서는 급여 계산시 공제 되는 항목과 주휴 수당을 간략히 살펴 보기로 하겠습니다.
월급제, 연봉제와 같이 월 급여단위 경우에는 월 급여에 주휴수당이 포함되어 있기 때문에 크게 신경쓸 부분은 아닙니다.
그러나 시급제, 현장직, 생산직, 아르바이트등의 시급을 적용하는 형태의 근로자의 급여에는 주휴 수당을 계산하여 급여를 계산하고 있습니다.
주휴수당이란?
1주일동안 소정의 근로일수+근로시간을 근무한 경우 유급휴일을 제공하는것입니다.
일반적으로 주휴수당은 1주일 만근시 1일 급여를 추가 지급하는것으로 알고 있는 경우가 많습니다.
그러나 주휴수당의 지급 조건은 근로기준법 제55조에 따라 지급하게 됩니다.
4대보험과 소득세.
일반적으로 급여에서 공제되는 항목으로는 4대보험과 소득세로 분류할 수 있고, 적용 요율에 따라 공제 금액을 산정하여 급여에서 공제됩니다.
2023년 현재 적용 요율은 다음과 같습니다.
| 항목 | 세부 항목 | 적용 요율 | |
| 근로자 | 사용자 | ||
| 국민연금 | – | 4.5% | 4.5% |
| 건강보험 | – | 3.545% | 3.545% |
| 장기요양보험
(건강보험료 * 요율) |
6.405% | 6.405% | |
| 고용보험 | 실업급여 | 0.9% | 0.9% |
| 산재보험 | – | – | 업종별 요율 적용 |
| 소득세 | – | 간이세액표 구간에 따라80, 100, 120% 적용. | – |
| 주민세 | 소득세 *10% | – | |
※ 고용보험 적용 요율은 실업급여, 150인 미만, 150인 이상 우선지원 대상 기업, 150인 ~ 1,000명 미만, 1,000명 이상 기업 및 지방자치단체와 같이 기업 형태에 따라 적용 요율을 달리 하고 있으며 해당 샘플 서식에서는 일반적인 실업급여 적용 요율을 적용하도록 합니다.
※ 국민연금은 18세 이상 60세 미만 근로자에게만 공제 되는 항목으로 최저 급여액 37만원, 최고 급여액 590만원으로 급여액이 37만원 이하라면 37만원으로 계산하고, 급여액이 590만원 이상일 경우 590만원으로 요율을 적용하여 계산 하도록 합니다.
※ 산재 보험은 사용자(사업주) 100% 부담하는 항목으로 실질적으로 근로자의 급여에서 제공되는 항목은 아닙니다.
또한 사업장의 업종에 따라 적용 요율을 달리 하고 있기 때문에 사업장에 해당 하는 업종별 적용 요율을 적용 하도록 합니다.
※ 소득세의 경우 4대보험 항목으로 분류 되는 항목은 아니지만 급여 발생시 고정적으로 공제 되는 항목으로 근로자 100% 공제 항목으로 총급여액을 기준으로 간이세액표 구간에 따라 80, 100, 120%로 사용자와 협의하여 정할 수 있습니다. 다만 일반적으로 소득세 요율까지 지정하는 근로자가 많지는 않기 때문에 간이세액표 구간 금액 100%를 적용합니다.
급여 계산 샘플 서식 및 주요 수식.
급여 계산 샘플 서식은 2가지를 작성해 봤습니다.
급여 계산 샘플 서식1.
급여 계산 샘플 서식1에서는 2021년 당시 작성하여 네이버, 티스토리등의 블로그를 통해서 배포한 무료 파일로 계산 수식 및 적용 요율은 2021년 요율이 적용 되어 있기 때문에 최저시급, 소득세, 4대보험과 관련한 수식 일부를 수정하여 사용할 수 있습니다.
해당 서식은 급여를 관리해야 하는 인원에 따라 시트를 복사하여 사용해야 하는 이유로 급여 계산 인원이 적을 경우에 사용하면 좋을것 같습니다.
급여 계산 샘플 서식2.
급여 계산 샘플 서식2에서는 급여계산서식1과 달리 조금 더 많은 인원의 급여를 계산하고자 하는 경우 근로자들의 출,퇴근시간을 입력할 수 있는 시트와 근로자 각 개인별로 급여 계산시 적용되어야할 기본정보와 적용 되는 요율을 작성하는 시트, 그리고 급여명세서 출력이 필요한 근로자 이름을과 년,월의 정보를 입력했을 경우 계산근거와 관련한 급여 내용들이 결과값으로 입력되는 급여명세서 시트로 구분해서 서식을 작성해 보았습니다.
급여 계산 샘플 서식2에서 적용된 요율은 2023년 초기 요율을 적용하였기 때문에 2023년2월부터 적용되는 소득세의 계산 수식은 일부 수정하여 사용할 수 있습니다.
필자 역시 현재 진행형 직장인이지만 누군가에게 보여주기 위한 문서 서식을 작성하는 업무 위주가 아니므로 서식이 이쁘거나 하지는 않을 수 있고, 방문자들의 질문 내용을 토대로 작성한 가이드 형식의 샘플 서식으로 사용자의 환경에 따라 서식을 수정하여 사용할 수 있습니다.
서식의 수정을 하면서 분문의 수식을 참조한다면 서식의 일부를 수정하더라도 참조한는셀만 잘 기억해 둔다면 변경된 서식에서 무리 없이 계산 수식을 적용할 수 있을것입니다.
급여 계산 주요 수식.
해당 서식에서 적용된 급여 계산을 위한 주요 계산 수식을 확인해 볼텐데, 계산 수식을 필요한 항목에는 크게 달라지는 부분이 없기 때문에 급여 계산 샘플 서식2를 기준으로 작성된 수식들을 확인해 보도록 하겠습니다.
“기본정보” 시트 주요 계산 수식.
기본정보 시트에서는 4대 보험 근로자+사용자 적용 합산 요율, 국민연금 근로소득기준액 최저~최대 범위 금액, 고정적으로 적용되는 특근 근로 시간, 적용되는 공휴일, 연차별 적용 연차 발생 개수, 근로자 활용 정보등을 직접 작성해 줄 수 있습니다.
기본정보 시트에서 작성된 주요 계산 수식은 다음과 같습니다.
=IF(Q3=””,””,INDEX($H$2:$H$52,MATCH(DATEDIF(Q3,TODAY(),”Y”)&”년”,$F$2:$F$52,0)))
=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”))
{=MIN(IF(출퇴근입력!$G$3:$G$5000=기본정보!$K3,출퇴근입력!$Z$3:$Z$5000))} → 해당 수식은 배열수식으로 수식을 작성 하고 Ctrl+Enter키를 치면 배열수식으로 작성 할 수 있습니다. |
“출퇴근입력” 시트 주요 계산 수식.
출퇴근입력 시트에서는 근로자 각 개인의 출퇴근시간 입력과 출근날짜, 연차 사용등을 입력함으로써 근무시간, 급여산정근무시간, 주차별 총 근로시간, 사용 가능한 연차등을 결과값으로 반환하는 수식을 작성하여 일자별, 근로자 개인별 근태를 간소화 하여 확인이 가능하도록 합니다.
출퇴근입력 시트에서 작성된 주요 계산 수식은 다음과 같습니다.
A열 – =IF(E3=””,””,YEAR(E3)) B열 – =IF(E3=””,””,MONTH(E3)) C열 – =IF(E3=””,””,DAY(E3))
=IF(IFERROR(WEEKNUM(E3)-WEEKNUM(E3-DAY(E3)+1)+1,””)=0,””,IFERROR(WEEKNUM(E3)-WEEKNUM(E3-DAY(E3)+1)+1,””))
Q열 (기본근로시간 8시간) =IFERROR(IF( R열 (연장 근무 시간) =IF(IF(OR(K3=””,L3=””),””, S열 (심야 근무 시간) =IFERROR(IF(OR(K3=””,L3=””),””,
U열 (기본 근무 시간) =IF(COUNTIFS(기본정보!$D$28:$D$57,출퇴근입력!$E3)=1,($Q3*2), V열 (연장 근무 시간) =IFERROR(IF(COUNTIFS(기본정보!$D$28:$D$57,출퇴근입력!$E3)=1,($R3*2), W열 (심야 근무 시간) =IFERROR(SUM(S3*2),””)
=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))
=IF(AND(K3=””,L3=””),””, |
급여명세서 시트 주요 계산 수식.
급여명세서 시트에서는 기본정보, 출퇴근입력 시트에서 계산된 결과값을 해당 항목에 맞도록 결과값을 불러오는등의 수식이 주를 이루고 있고, 직접 작성해줘야 하는 데이터로는 년, 월, 이름, 기타급여항목과 같은 데이터들을 추가적으로 입력할 수 있습니다.
=IFERROR(VLOOKUP(급여명세서!$B4,기본정보!$K$3:$S$1000,2,FALSE),””)
=IFERROR(VLOOKUP($B$4,기본정보!$K$3:$O$1000,5,FALSE),0)
B8 (기본 근무 시간 합계) =SUMIFS(출퇴근입력!$Q$3:$Q$10000,출퇴근입력!$G$3:$G$10000,$B$4,출퇴근입력!$A$3:$A$10000,$A$3,출퇴근입력!$B$3:$B$10000,$C$3) B9 (연장 근무 시간 합계) =SUMIFS(출퇴근입력!$R$3:$R$10000,출퇴근입력!$G$3:$G$10000,$B$4,출퇴근입력!$A$3:$A$10000,$A$3,출퇴근입력!$B$3:$B$10000,$C$3) B10 (심야 근무 시간 합계) =SUMIFS(출퇴근입력!$S$3:$S$10000,출퇴근입력!$G$3:$G$10000,$B$4,출퇴근입력!$A$3:$A$10000,$A$3,출퇴근입력!$B$3:$B$10000,$C$3) C8, 9, 10 – 근무 유형별 급여 산정 근로 시간 합계. C8 (기본 근무 시간 합계) =SUMIFS(출퇴근입력!$U$3:$U$5000,출퇴근입력!$A$3:$A$5000,급여명세서!$A$3,출퇴근입력!$B$3:$B$5000,급여명세서!$C$3,출퇴근입력!$G$3:$G$5000,급여명세서!$B$4) C9 (연장 근무 시간 합계) =SUMIFS(출퇴근입력!$V$3:$V$5000,출퇴근입력!$A$3:$A$5000,급여명세서!$A$3,출퇴근입력!$B$3:$B$5000,급여명세서!$C$3,출퇴근입력!$G$3:$G$5000,급여명세서!$B$4) C10 (심야 근무 시간 합계) =SUMIFS(출퇴근입력!$W$3:$W$5000,출퇴근입력!$A$3:$A$5000,급여명세서!$A$3,출퇴근입력!$B$3:$B$5000,급여명세서!$C$3,출퇴근입력!$G$3:$G$5000,급여명세서!$B$4)
=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))
=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))
=IF(C15<15,0,IF(AND(C15>=15,C15<=40),((C15/40)*8)*$F$4,IF(C15>40,8*$F$4)))
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, F24 (건강보험) =ROUNDDOWN((E11+D20)*(기본정보!C4/2),-1) F25 (장기요양보험) =ROUNDDOWN(SUM(F24*기본정보!C5),-1) F26 (고용보험) =ROUNDDOWN(SUM(E11+D20)*SUM(기본정보!C6/2),-1) F27 (소득세) =IFERROR(IF(C38<=10000000,VLOOKUP(C38/1000,근로소득간이세액표!$A$6:$M$652, ROUND(IF(C38>10000000,VLOOKUP(10000,근로소득간이세액표!$A$6:$M$652,MATCH(IF(LOOKUP(1,1/(기본정보!K3:K1000=$B$4),기본정보!$P$3:$P$1000)=””,1, IF(AND(C38>1000000,C38<=14000000),(((C38-10000000)*0.98)*0.35)+25000, F28 (주민세) =ROUNDDOWN(SUM(F27*0.1),-1) |
글을 마치며…[행아아빠 생각]
급여 계산을 하기 위한 수식을 작성하면서 수식 풀이까지 글을 쓸 경우 페이지가 굉장히 길어지게 되어 별도의 수식 해석을 작성하지는 않았습니다.
해당 수식들은 본문 상단에 링크된 글의 본문 내용으로 대체하여 수식이 어떻게 작성되었는지 그 수식 풀이를 해볼 수 있을것입니다.
급여 계산을 하기 위한 수식은 사용자의 환경에 따라 정말 다양한 형태로 나타나기 때문에 수식 자체를 일반화 하기란 쉽지 않은것 같습니다.
앞으로도 급여 계산과 관련하여 수식 문의를 남겨 주시면 해당 내용들을 토대로 더 일반화 할 수 있는 서식을 작성해보도록 하겠습니다.
뿐만 아니라 엑셀 수식과 관련되어 여러분들의 댓글 항상 기다리고 있습니다.
