[엑셀강좌:49] 실무 : 급여관리 (연장수당, 근속수당 계산)

 이번에는 변동급여에서 연장수당과 근속수당을 산출해 보도록 하겠다.

 연장수당은 근태기록부에서 입력된 연장근로 시간을
 급여기준의 "연장근로수당" 이라고 이름 정의된 영역에서
 시간당 적용할 단가를 산출 한 후 시간 * 단가로 계산하여야 한다.

 급여 기준의 연장근로 수당 규칙을 보면
 연장근무 시간이 몇 시간 부터 몇 시각 까지는 얼마이다 라고 되어 있다.
 이 범위는 가로로 되어있기 때문에 가로로 찾아야 한다.

 따라서 이때는HLOOKUP() 함수를 사용하여야 한다.

 식을보면
=HLOOKUP(VLOOKUP($A3,근태기록,MATCH("연장",근태제목,0),0),연장근로수당,3) *
 VLOOKUP($A3,근태기록,MATCH("연장",근태제목,0),0)
 
아주 복잡시럽다.
단순하게 보면HLOOKUP() 을 이용해 연장근로수당 영역에서 찾은 단가와
VLOOKUP() 을 이용해 근태기록에서 찾은 근무시간을 곱한 것이다.

자 단가를 찾기위한 HLOOKUP() 함수를 보자.
단가를 찾기 위해서는 일단 연장근무 시간을 알아야 하는데
그 연장근무시간은 근태기록의 "연장" 이라는 열에 있다.
해당 사람의 연장근무 시간을 알기 위해서
VLOOKUP($A3,근태기록,MATCH("연장",근태제목,0),0) 를 동원했다.
"근태기록" 영역에서 A3(이름) 를 헤딩으로 하는 행을 찾고 (VLOOKUP)
"근태제목" 영역에서 "연장" 의 값이 있는 열을 찾아 (MATCH)
해당 행의 해당열의 값을 가져오게 된다.

이렇게 하면 연장근무한 시간을 가져오는 데 이 시간은 나중에
단가 * 시간에서 또 구해서 계산해야 한다.
따라서 아랫줄의 VLOOKUP($A3,근태기록,MATCH("연장",근태제목,0),0) 도
똑 같은 수식이다.

자 이제 연장근무 시간을 찾았으니
"연장근로수당" 영역에서 해당 시간에 적용되는 시간당 단가를 구해야 한다.
시간별 구분은 옆으로 되어 있으니
HLOOKUP() 을 이용해서 좌에서 우로 시간이 포함된 열을 찾고
다음에 단가가 기록된 행의 데이터를 가져오면 된다.

이때 HLOOKUP(찾을내용, 찾을범위, 행번호, 논리) 에서
논리는 공백 혹은 1로 두어 비슷한 것을 찾도록 해야 한다.
만약 이것을 0으로 두게 되면 정확히 일치하는 것을 찾으라는 것으로
0, 10, 20, 30 이 아닌 다른 숫자는 오류를 발생시키게 된다.
비슷하다는 것은 숫자가 근사치가 아닌 모양이 비슷한 것이라고 생각하면 된다.
즉 0 - 9 까지 가장 비슷한 것은 0 이고
10 - 19 까지 가장 비슷한 것은 10 이고
20 - 29 까지 가장 비숫한 것은 20  이렇게 찾아 준다.

"연장근로수당"에서 해당 시간과 비숫한 열을 찾으면
해당 열의 3번째 데이터인 시간당 금액을 가져오게 된다.

이렇게 가져온 시간당 금액에 위에서 설명한
 VLOOKUP($A3,근태기록,MATCH("연장",근태제목,0),0)
의 수식으로 찾은 근무시간을 곱하면
연장근로 수당이 산출할 수 있다.

다음 근속수당은 근속개월수 에 기본급의 100분의 1을 곱한 금액을 산출하는 것이다.
근속개월수는 입사일로 부터 급여 기준일 까지의 월수이므로
DATEDIF() 함수를 사용해서 구할 수 있다.

식을 보면
=DATEDIF(VLOOKUP($A3,직원명부,MATCH("입사일자",명부제목,0),0),년월,"M") * (E3/100)

인데 단순하게 보면  = 근속월수 * 기본급(E3) / 100  이다.
근속월수를 계산하기 위해서는 =DATEIF(입사일자,기준일자,"M") 함수를 사용해서
입사일자와 기준일자의 기간을 개월수로 산출했다.
입사일자는 "직원명부" 에서 해당 사람의 행을 찾아 (VLOOKUP())
"입사일자"의 열(MATCH())의 값을 가져오면 된다.

기준일자는 근태기록부의 A1 셀의 값인 "년월" 로 이름정의된 날짜이다.
이렇게 구해진 근속개월에 기본급이 E3 의 100분의 1을 곱하면 근속수당이 계산된다.

   급여관리 파일 -> PAYROLL.xlsx

덧글

[두바이]

과,중,한, ,스,트,레,스,와,
우,울,증,.,,기,분,전,환,에, ,쎅,스,는, ,최고,죠,~,
,2,0,대, ,초,반, ,중,반, ,언，,니,，,들, ,2,4,시, 완,벽,준,비,~,!,!
,서,비,스,-,S,M노,예,/,컨,셥,놀,이,/,입사,/,질,사,,,/,안,,.,마,등,등,

장,소,만, ,알,려,주,시,면, ,직,접, ,찾,아가,는, ,서,,비,스,~,
,착한 ,가,,,격,에 ,지,방,까,지, ,고,고,.,.,일,일,,대,,기 !~

,자,세,한, ,문,의는, ,톡,",d,.,u,,,b.,,a,,,,i,.,,8,8,8

[,,1,9,금,], ,d,u,b,a,,i,3,4,.n,e,,,t

,상,담,환,영, ,친,절,히, ,답,해드,려,요,!,
답,변,이, ,늦,을, ,수,있,는,점, ,양,해, ,부,탁,드,립,,니,다

<img alt="dubai34.jpg" src="http://www.medwide.net/files/attach/images/2858877/360/337/009/765ff3af4def6f0aae7642644fa24e4f.jpg">