지금까지 급여 계산을 하기 위한 준비는 다 되었다.
이제 이 준비된 내용을 가지고 실지로 급여 계산을 하고
그 결과를 보여 줄 급여 대장과 개인에게 나누어 줄 급여 명세를 만들기만 하면 된다.
일단 위와 같은 형태의 급여 대장 포맷을 만들어 보자.
만들기 귀찮으면 아래 파일을 받아 급여대장준비 시트를 보면 된다.
PAYROLL.xlsx
제일 상단에 제목은 근태기록부에서 입력한 A1 셀의 "년월"로 이름정의된 날짜를 가지고 만들었다.
=YEAR(년월) & "년 " & MONTH(년월) & "월 급여대장"
즉 "년월"로 이름정의 된 급여 기준 일자에서 YEAR() 함수를 이용해 년도만 가져오고
MONTH() 함수를 이용해 월만 가져와 그 사이에 "년" "월 급여대장" 의 글자를 & 연산자로 합쳤다.
이렇게 근태 기록부의 A1(년월)을 수정하면 자동으로 제목이 바뀌도록 하였다.
다음 성명 열의 첫행은 앞의 근태기록에서 했던 것과 동일하게
=직원목록!A3
즉 직원목록 시트의 A3 셀의 값을 가져오게 했다.
다음 부서도 근태기록에서 했던 것과 같이 VLOOKUP 과 MATCH 함수를 써서
=VLOOKUP($A3,직원명부,MATCH(B$2,명부제목,0),0)
이렇게 하였다.
다시 설명하면 MATCH(B$2, 명부제목,0) 은 직원목록시트에 "명부제목" 이라고 이름 정의된
컬럼헤딩 영역 (직원명부!A2:H2 ) 에서 B2 즉 "부서" 라는 내용이 몇번 째 열에 있는지 찾고
VLOOKUP($A3,직원명부,MATCH 로 찾은 열번호,0)
로 "직원명부"로 이름정의 된 직원목록 시트의 A3 부터 H10 영역에서
헤딩컬럼이 A3 인 즉 첫열의 값이 "홍길동" 인 행을 찾고 위에서 MATCH() 함수로 찾은
"부서"에 해당하는 컬럼(열)의 값을 가져오게 했다.
이렇게 하면 직원 명부의 부서가 변경될 경우 변경된 내용이 그대로 급여대장에 적용되게 된다.
다음의 직책도 역시 마찬가지로
=VLOOKUP($A3,직원명부,MATCH(C$2,명부제목,0),0)
이렇게 B$2 만 C$2 로 변경해서 C2 인 직책의 열번호를 알아내 A3 "홍길동"의 열에서
"직책"열의 내용을 가져왔다.
그냥 오른쪽 부서의 내용을 끌고 와도 된다. $A3 에 A 가 고정되어 있기 때문에 그대록
적용시킬 수 있다.
다음 지급합계는 E3 기본급 부터 J3 보건수당 까지의 단순합으로
=SUM(E3:J3)
로 ∑ 자동합계를 이용해서 더해도록 한다.
역시 공제합계도 L3 부터 M3 까지의 단순 합이다.
실지급액은 지급합계에서 공제합계를 빼면 되므로
= K3 - N3
만 해주면 된다.
다음 입금계좌는 직원목록의 은행명과 계좌번호를 연결한 형태로
=VLOOKUP($A3,직원명부,MATCH("은행",명부제목,0),0) & " " &
VLOOKUP($A3,직원명부,MATCH("계좌번호",명부제목,0),0)
이렇게 직원명부에서 "은행"의 열에 있는 내용과 "계좌번호"의 열에 있는 내용의 데이터를
& 연결자를 이용해 공백하나를 가운데 두고 연결했다.
이렇게 한 다음 A3 부터 P3 까지 선택해서 끝을 잡고 주욱 아래로 내리면
직원명부에 등재된 직원 모두와 각각의 부서 직책 입금계좌가 아래에 주욱 나타나게 된다.
이렇게 나타난 직원목록 제일 하단에는 각 항목별 합계를 산출하는 행을 만들고
각 열의 단순합을 계산하도록 ∑ 를 사용해서 E 열 부터 O 열 까지 적용 시키면 된다.
이렇게 해놓고 나중에 직원이 늘어나게 되면
직원명부 하단에 새로 들어온 직원을 추가한 후
급여 대장과 근태기록부의 하단 합계 행위에 추가된 직원 수 만큼 행을 삽입한 후
데이터가 있는 행을 선택해서 아래로 주욱 복사 시키면 추가된 직원들이 나타나게 된다.
만약 직원이 퇴사하게 되면
직원명부에서 해당 직원을 삭제 시키고
급여 대장과 근태기록부에 #REF! 로 오류가 난 행을 삭제 시켜 버리면 된다.
덧글
2012/07/06 16:25 # 삭제 답글 
비공개 덧글입니다.일단 YEAR(년월) 이라는 식은 '년월'이라고 이름 정의된 데이터 에서 년도만 가져오라는 명령입니다.
'년월' 이라고 이름 정의된 데이터는 샘플의 근태기록부 라는 시트의 A1 영역입니다.
형태는 날자형이고 값은 2012-03-01 로 되어 있으며 표현은 2012년 3월 이렇게 표현되어 있습니다.
즉 YEAR(년월) 이라는 표현은 YEAR(근태기록부!A1) 이라는 말과 동일하며
이는 YEAR(2012-03-01) 값이기 때문에 2012 라는 숫자만 가져오게 됩니다.
또한 MONTH(년월) 도 MONTH(근태기록부!A1) 이므로 2012-03-01 에서 월의 값인 03을 가져오게 되는 것입니다.
이렇게 하는 이유는 매월 급여계산의 기준년월인 근태기록부 시트의 A1 의 값 (년월로 이름정의된)만 변형시킴으로
종속되는 모든 시트 (급여대장, 급여명세서 등등)에 계산 기준 년월이 적용되도록 하기 위합입니다.