[엑셀] 급여관리 - 근로시간별, 요일별에 따라 적용되는 급여적용 시간 자동계산하는 수식 작성 방법.

안녕하세요.

행아아빠입니다.

 

이번에 작성할 엑셀 내용으로는

엑셀 함수를 이용해서 급여관리 하는 방법중

근로시간별, 기본근로시간, 야간근로시간, 특근, 심야근로시간과

요일별로 급여 계산시 적용되어 산정하는 급여를 자동으로 계산되어 산출 할 수 있는

급여계산 적용 근로시간을 작성하는 수식 작성 방법입니다.

 

이번에 적용할 함수로는

IF함수, Sumifs함수, And함수등을 사용하게 될 것 같습니다.

---

 

급여계산 시간을 자동으로 입력되도록 수식 작성 하는 방법.

▲ 위 그림에서 보는 표에서

    직접 입력해 주셔야 할 내용은 아래 두가지만 입력해 주시면, 나머지는 미리 작성한 수식에 의해서 자동으로 입력 됩니다.

    1. 날짜의 시작일인 2019-11-01

    2. 출근시간과 퇴근시간 그리고 비근로시간인 점심시간과 저녁시간.

 

    그럼 입력된 데이터를 기준으로 빨간네모박스 안의 L열에 들어갈 급여계산 시간의 수식을 자동으로 계산하여

    입력될 수 있도록 작성해 보겠습니다.

 

    일요일 ~ 토요일까지를 일주일로 가정하고, 미리 데이터를 입력했기 때문에 2019-11-03일부터 2019-11-09일까지 작성했으니

    여러분들은 수식을 참고하실때 셀주소를 잘 확인해 주셔서 참조해 주시면 되겠습니다.

 

    수식을 작성하기 전에 급여를 계산할 때

    여러분들이 속한 회사에서 총근로시간중 급여산정근로시간으로 어떻게 환산해서 급여를 계산하는지 미리 조건들을 정리해 두시면

    어렵지 않게 작성하실 수 있으니 항상 직접 계산하던 것들을 수식으로 만들 경우에는 어떤 패턴과 어떤 기준으로 계산을 하는지

    경우의 수가 있는지 정리를 해 보시면 좋을 것 같습니다.

 

 

▲ 위 그림에서 보듯이 수식은

    =IF(AND(SUMIFS($H$3:$H$33,$B$3:$B$33,$B3)<40,$A3="토"),SUM(($I3*1)+($J3*1.5)+($K3*2)),

    IF(AND(SUMIFS($H$3:$H$33,$B$3:$B$33,$B3)>=40,$A3="토"),SUM(($I3*1.5)+($J3*2)+($K3*2)),

    IF(AND(SUMIFS($H$3:$H$33,$B$3:$B$33,$B3)<40,$A3="일"),SUM(($I3*1)+($J3*1.5)+($K3*2)),

    IF(AND(SUMIFS($H$3:$H$33,$B$3:$B$33,$B3)>=40,$A3="일"),SUM(($I3*2)+($J3*2)+($K3*2)),

    SUM(($I3*1)+($J3*1.5)+($K3*2))))))

    위에 수식으로 작성 할 수 있습니다.

    하지만 수식이 길어지다 보니... 복잡해 보입니다만, 사실은 간단한 수식이기 때문에

    수식을 단락별로 끊어서 확인해 보겠습니다.

 

    =IF(AND(SUMIFS($H$3:$H$33,$B$3:$B$33,$B3)<40,$A3="토"),SUM(($I3*1)+($J3*1.5)+($K3*2)),

    → B열에서 해당셀의 주차에 해당하는 총근로시간의 합계가 40시간보다 작고, A열이 토요일이라면,

        (기본근로시간 * 1배), (야근시간 * 1.5배), (특근시간 * 2배)의 값들을 모두 더해서 계산하고

        그것이 아니라면 다음의 수식을 계산.

 

    IF(AND(SUMIFS($H$3:$H$33,$B$3:$B$33,$B3)>=40,$A3="토"),SUM(($I3*1.5)+($J3*2)+($K3*2)),

    → B열에서 해당셀의 주차에 해당하는 총근로시간의 합계가 40시간보다 같거나 크고, A열이 토요일이라면,

        (기본근로시간 * 1.5배), (야근시간 * 2배), (특근시간 * 2배)의 값들을 모두 더해서 계산하고,

        그렇지 않다면 다음의 수식을 계산.

 

    IF(AND(SUMIFS($H$3:$H$33,$B$3:$B$33,$B3)<40,$A3="일"),SUM(($I3*1)+($J3*1.5)+($K3*2)),   

    → B열에서 해당셀의 주차에 해당하는 총근로시간의 합계가 40시간보다 작고, A열이 일요일 이라면,

        (기본근로시간 * 1배), (야근시간 * 1.5배), (특근시간 * 2배)의 값들을 모두 더해서 계산하고,

        그렇지 않다면 다음의 수식을 계산.

 

    IF(AND(SUMIFS($H$3:$H$33,$B$3:$B$33,$B3)>=40,$A3="일"),SUM(($I3*2)+($J3*2)+($K3*2)),   

    → B열에서 해당셀의 주차에 해당하는 총근로시간의 합계가 40시간보다 크거나 같고, A열이 일요일 이라면,

        (기본근로시간 * 2배), (야근시간 * 1.5배), (특근시간 * 2배)의 값들을 모두 더해서 계산하고,

        그렇지 않다면 다음의 수식을 계산.

 

    SUM(($I3*1)+($J3*1.5)+($K3*2))))))

    → (기본근로시간 * 1배), (야근시간 * 1.5배), (특근시간 * 2배)의 값들을 모두 더해서 계산.!!

 

단락별로 끊어서 수식을 확인해 보니 별것 없습니다...

 

 

 

 

일주일의 총근로시간이 40시간을 넘느냐 넘지 않느냐를 기준으로 둔 것은

주 5일제 근무라고 가정했을 경우 8시간 * 5일 = 40시간이 되기 때문입니다.

1주일 중 40시간을 초과해서 근무한 경우 토요일 근무인지 일요일 근무인지에 따라 기본근무시간도 2배를 해줄 것인지

야근시간을 1.5배를 해줄 것인지 2배를 해줄 것인지 결정할 수 있겠습니다.

 

급여를 계산하는 방법은 회사규정마다 조금씩 틀리기 때문에,

꼭!! 여러분들의 회사에서 또는 내가 급여를 계산하는 방법을 미리 생각해 보시면 좋을 것 같습니다.

 

저는 일반적으로 생각할 수 있는 기본8시간은 *1배, 야근시간은 *1.5배, 특근시간은 *2배로 생각을 하고 수식을 작성했고,

하루 총근로시간 *1배, 야근시간 *0.5배, 특근시간 *1배의 합계로도 계산 할 수 있겠습니다.

 

그림에서 보듯이 2019-11-03 일요일의 경우에는 총근로시간은 8시간이지만 급여를 계산하기 위한 시간은 16시간이 됩니다.

이유는 간단합니다.!!

2019-11-03일에 해당하는 2주차의 총근로시간이 40시간을 넘은 상황에서 일요일 근무를 했기 때문에

기본시간을 포함한 모든 근로시간이 *2배가 되는 것 입니다.

---

 

그럼 오늘 하루도 좋은 하루 되시길 바라겠습니다.^^!