[엑셀] 급여관리 - 주휴수당 자동으로 계산하는 수식 작성 방법!!

안녕하세요.

행아아빠입니다.

 

이번 내용은 엑셀로 급여관리 하는 방법중 주휴수당에 관한 내용 입니다.

사실 각 근로자마다의 출,퇴근시간과 비근로시간은 직접 입력을 해 주어야 하기 때문에 100% 자동 계산은 아닙니다만,

급여를 계산해야 할 인원이 많으면 많을 수록 정말 단시간내에 급여를 계산하고 실수를 줄일 수 있기 때문에

엑셀 급여관리 양식을 만들어 두시면 편리하게 이용하실 수 있을 것 같습니다.

 

주휴수당을 자동으로 계산하기 위한 수식을 작성하기 위해서는 Sumifs함수와 Left함수정도를 알고 계신다면,

간단하게 수식을 작성하여 적용하실 수 있을 것 같습니다.

---

■ 주휴수당 자동계산을 위한 수식 작성 방법.

▲ 위 그림과 같이 미리 2019-11월의 2주차에 해당하는 일주일치의 출/퇴근 시간과 그에 해당하는 근로시간등의

    데이터를 미리 입력해 놓았습니다.

 

    이 데이터를 기초로 해서 아래 빨간네모박스 안에 들어가야 할 해당주차의 근무시간과 주휴수당 금액을 자동 계산되도록

    수식을 작성하도록 하겠습니다.

 

    시급은 2019년 최저시급인 8,350원 이라는 가정하에 계산되도록 합니다.

    (2020년 최저시급은 8,590원 입니다.)

 

    우선 주휴수당을 계산하는 방법은

    (1주간 총근로시간 / 40시간) * 근로자시급 * 8시간 = 주휴수당을 계산해 낼 수 있습니다.

 

    주휴수당을 계산하는 공식을 입력된 데이터에 대입시켜보면,

    총근로시간의 합계는 61시간,

    61시간을 40시간으로 나눈값은 1.525

    1.525 * 8,350원 * 8시간 = 101,870원이 되겠습니다.

 

    굉장히 단순하죠??!!

    하지만, 이 단순한 계산과정이 계산해야 할 인원이 많아지다보면 실수가 발생하게 되기 때문에

    수식으로 자동 계산하게 하는것이 시간과 실수를 줄일 수 있을 것 같습니다.

    각 주차별로 총근로시간의 합계를 구하고 그 값을 40시간으로 나누어준뒤 시급과 8시간을 곱하면 끝!!

 

    수식을 정리해보면

    해당주차의 근무시간의 합계를 구하는 수식은

    =Sumifs($H$3:$H$11,$B$3:$B$11,LEFT(B16,1))

   

    H열과 B열에는 $기호를 붙여준 것은 수식을 복사하더라도 H열과 B열은 항상 참조하는 영역이 바뀌지 않도록 하기 위함 입니다.

    H열은 총근로시간을 참조하는 영역이 되겠고,

    B열은 몇 주차인지를 알 수 있는 열의 영역이 되겠습니다. 

 

    Left(B16,1)은 B16셀에 입력된 "2주차" 중에서 첫째자리만 출력해서 숫자 "2"를 지칭하는 것이 되겠습니다.

    다시 말해서 2주차에 해당하는 총근로시간의 합계를 계산해서 가져오라는 의미 입니다.

▲ 위 수식을 넣어준 결과 2주차에 해당하는 근로시간은 61시간이 수식에 의하여 자동으로 계산되어 입력 됩니다.

    그럼 이제 1주일간 61시간을 근무했을 경우 주휴수당의 금액을 계산하는 수식까지 입력을 해보면,

    아래와 같이 작성 할 수 있습니다.

    ((SUMIFS($H$3:$H$11,$B$3:$B$11,LEFT(B16,1)))/40)*$G$13*8

    앞서 작성한 근무시간 수식에 주휴수당을 계산하는 공식 그대로를 작성해 주었다고 보시면 되겠습니다.

   

    다만, 시급은 8,530을 입력하는 것 대신 G13셀에 시급을 입력해 놓았기 때문에 해당 수식을 어디에 복사하더라도

    참조하는 G13셀의 주소가 변하지 않도록 $기호를 넣어 주었습니다.

 

    시급은 근로자마다 그리고 매년 최저시급이 변하기 때문에 수식을 넣어주는 것보다 셀에 입력을 해 놓고 변동이 있을때마다

    바꾸어 주는것이 편리하게 사용할 수 있겠습니다.

▲ 해당 수식을 입력한 값으로 101,870원 이라는 금액이 계산되어 입력되는 것을 위 그림에서 확인 할 수 있습니다.

    나머지 1주차, 3주차, 4주차, 5주차, 6주차는 수식을 복사하거나 드래그 하셔서 복사하시면

    해당하는 값들이 알아서 계산되어 입력 되겠습니다.

 

    이제 입력하신 출/퇴근시간과 비근로시간만으로 기본, 야근, 특근 근로시간과 주휴수당이 자동으로 계산 되기 때문에

    시급에 따라 나오는 금액 + 주휴수당을 해 주시면 세전급여인 총급여액이 산출 되어 나오겠네요!!

    (기본, 야근, 특근 근로시간 자동계산은 제 블로그내 "[엑셀] 급여관리" 의 제목으로 작성된 글들을 참조해 주시면 됩니다.)

---

■ 4대보험 계산.

이렇게 계산되어 나온 총급여액에서

국민연금은 총급여액이 입력된 셀 * 4.5% (0.045)

건강보험은 총급여액이 입력된 셀 * 3.35% (0.0335)

장기요양보험은 건강보험이 입력된 셀 * 10.25% (0.1025)

고용보험은 총급여액이 입력된 셀 * 0.8% (0.008) 을 해 주시면 되겠습니다.

 

이렇게 각 셀에 입력해 주시면 4대보험까지 자동으로 계산되어 입력되는 파일 하나를 완성하게 될 수 있습니다.

 

추가로!! 4대보험 계산하는 수식에

RoundDown(국민연금등의 4대보험 계산 수식, -1) 이렇게 해 주시면 원단위는 절사하고 값을 계산하게 됩니다.

---

이상으로 오늘 내용은 마무리하고,

오늘 내용도 많은 분들께 도움이 될 수 있도록 전달 되었으면 좋겠습니다.

기분 좋은 하루 보내시길 바라겠습니다.^^!