이번에는 급여대장에서 계산된 결과를 가지고
개인별로 나누어줄 급여명세서를 작성하고 출력하는 것을 해보자.
일단 급여명세서를 만들기 전에 급여 대장에 몇가지 이름정의를 해 놓도록 한다.
급여대장 데이터 전체 A2 부터 P11 까지를 "급여대장" 이라고 이름정의 하고
급여대장의 이름열인 A3 부터 A10 까지를 "급여목록" 이라고 이름정의 한다
열제목 행인 A2 부터 P2 까지를 "급여제목" 이라고 이름정의 해 놓는다.
일단 모양은 위와 같이 만든다.
상단의 제목은 급여대장에서와 같이
=YEAR(년월) & "년 " & MONTH(년월) & "월 급여명세서"
수식으로 급여 지급 기준 일자 "년월" 을 이용해서 만든다.
다음에 2번째 열은 아주 가늘게 되어 있는데
이 열의 D2에는 숨겨진 비밀이 있다.
일단 엑셀강좌 24에서 배운 콤보상자 (Drop Down Selector) 를 D2 위치에 그려 넣고
오른쪽 버튼을 눌러 컨트롤 서식을 연다.
컨트롤 서식의 컨트롤 탭에서
입력범위는 급여목록이라고 입력한다 이렇게 하면
급여대장에서 이름정의한 "급여목록" 즉 직원이름 들이 목록에 나타나게 된다.
다음 셀연결은 D2 를 지정한다.
즉 콤보박스에서 선택되어진 값을 저장할 공간이 어디인가를 지정해 주는 것이다.
이 값은 직원이름으로 나오지 않고 콤보리스트의 위에서 부터 숫자로 1 2 3 4 의 값이 반환 된다.
우리는 이 숫자를 이용해서 아래의 모든 작업을 할 것이지만
명세서를 출력할 때는 불필요 하다.
화면에 있는 콤보박스 자체도 출력할 때는 나오지 말아야 한다.
그래서 컨트롤 서식의 속성창에 개체인쇄의 체크를 해제하여 인쇄되지 않도록 하고
D2 셀의 문자색은 백색으로 바꾸어 보이지 않고 출력되지 않도록 해 준다.
즉 D2 에는 보이지는 않지만 콤보박스에서 뭔가를 선택할 때 마다 어떤 숫자가 들어가게 되는데
그 숫자는 급여대장의 "급여목록"의 행 번호가 들어가게 된다.
즉 홍길동을 선택하면 1이 들어가고 장보고를 선택하면 2가 들어간다.
자 B3 값은 선택한 직원의 이름이 들어가야 하는데
=INDEX(급여대장,D2+1,MATCH(A3,급여제목,0))
로 INDEX() 함수와 MATCH() 함수를 사용해서 찾아냈다.
즉 "급여대장" 영역 (급여대장의 A2 부터 P11까지) 에서 D2+1 행의MATCH() 로 찾은
열의 데이터를 가져온다는 뜻이다.
D2 에는 콤보박스에서 선택한 직원의 "급여목록" 영역의 행 숫자가 들어가 있는데
이 "급여목록" 의 영역은 A3 부터 시작하고 "급여대장" 영역은 A2 부터 시작되므로
"급여대장" 영역에서 찾아오려면 해당 행번호 + 1 을 해 주어야 한다.
만약 "급여대장" 영역도 A3 부터 시작된다면 그냥 D2 를 해주면 된다.
열번호는MATCH(A3, 급여제목,0) 을 해서 찾았는데
헤딩로우영역인 "급여제목" 영역에서 A3 즉 "성명" 이라는 컬럼의 번호를 찾도록 하였다.
부서 과장 호복 역시 성명과 똑 같은 방법으로 찾았는데
MATCH() 함수에서 찾을 데이터의 위치만 변경 시키면 된다.
호봉란에는 호봉 숫자에 & 로 "호봉" 이란 문자를 연결 시켰다.
다음 기본급 부터 지급내역도 INDEX 를 사용했는데
기본급을 보면
=INDEX(급여대장,$D$2+1,MATCH(A7,급여제목,0))
이 처럼 찾을 행번호가 있는 셀이 절대 번지로 지정되어 있다 ($D$2)
즉 이 셀을 밑의 다른 행으로 끓어 내려 동일하게 적용 시킬 것이지만
셀의 행이나 열이 바뀌더라도 가져올 행번호가 있는 D2 의 번지는 고정시킨 다는 것이다.
이렇게 하고 제일 아래 실지급액 까지 쭈욱 이 공식을 적용 시키면 된다.
동일한 공식을 복사해서 D6 인 결근공제에 적용시키고 지각공제 공제합계 에도 적용
시키면 된다.
입금계좌는 찾을 타이틀이 없기 때문에
="입금계좌 : " & INDEX(급여대장,D2+1,MATCH("입금계좌",급여제목,0))
이렇게 그냥 "입금계좌"라는 단어를 "급여제목" 에서 찾아 "입금계좌 : " 라는 글자와 연결해서 입력하였다.
위의 콤보박스의 직원을 바꾸어 가며 급여명세서가 잘 만들어 졌는지 확인해 보자.
급여관리 파일 -> PAYROLL.xlsx
덧글