결근 공제는 결근횟수가 2회 이상인 경우
월차 하루를 뺀 횟수 만큼의 일급을 계산해서 공제하게 된다.
식을 보면
=IF(VLOOKUP($A3,근태기록,MATCH("결근",근태제목,0),0)>1,
(VLOOKUP($A3,근태기록,MATCH("결근",근태제목,0),0)-1)*E3/30*1.5,0)
간단히 보면
=IF( 결근횟수> 1, (결근횟수-1) * 기본급 / 30 * 1.5 , 0)
이다.
즉 결근횟수 가 > 1 이면 결근횟수 -1 에 기본급 / 30 * 1.5를 아니면 0을 계산하라는 뜻이다.
결근횟수는 근태기록부의 해당 사람의 행의 "결근" 열에 위치해 있으므로
VLOOKUP($A3,근태기록,MATCH("결근",근태제목,0),0)
즉 VLOOKUP() 함수로 해당 사람의 행을 찾아 MATCH() 함수로 구한 열의 값을 가져오면 된다.
지각 공제는
=VLOOKUP($A3,근태기록,MATCH("지각",근태제목,0),0)*E3/240*1.5
의 수식으로
VLOOKUP() 으로 근태기록에서 해당 사람의 행을 찾고MATCH() 로 "지각" 열의 번호를 찾아
지각 시간을 구한 후 기본급(E3) 의 240분의 금액을 곱하고 1.5배를 하면 된다.
자 여기까지 하면 급여 대장은 모두 끝났고
지급합계 공제합계 실지급액에 SUM 함수가 있다면 자동으로 계산이 될 것이다.
첫 행에 입력한 공식들을 마지막 직원까지 끌어 내려 적용시키면 급여대장은 완성 된다.
앞으로는 급여 지급 기준이 바뀌지 않는 한 급여대장의 공식을 손댈일은 거의 없게 된다.
기본급여의 호봉값, 각종 수당의 기준이 달라져도 급여기준만 수정하면 되고
입사 퇴사, 직원의 승진, 부서이동 등의 경우에는 직원목록을 수정하고
입사시는 근태기록부와 급여대장에 늘어난 사원 숫자만큼 행을 추가해서 위의 라인을 복사하고
퇴사시는 사라진 직원의 행을 삭제하기만 하면 된다.
그 외에는 매월 근태기록부만 잘 기록하면 된다.
급여관리 파일 -> PAYROLL.xlsx
덧글