임금대장에서 작성한 내용을 가져와 자동으로 급여명세서 출력용 시트 만들기

엑셀을 이용한 여러 가지 업무 처리 중 한 시트에 기본 데이터를 입력해 두면 이 데이터를 이용해 다른 시트에서 입력된 데이터를 가공해서 문서를 만드는 작업이 있습니다.

 

임금대장 시트에 필요한 내용을 입력하고, 명세서 시트에서 이름을 선택하면 임금대장 시트에 입력된 내용을 찾아 자동으로 가져와서 명세서 시트를 완성하도록 만들어 보겠습니다.

 

실습 파일 임금대장 시트에 이름은 모두 입력해 두었지만 나머지 값은 예시로 한 건만 등록해 두었습니다.

실제 작업에서는 모두 입력해야 합니다.

그리고 여기서는 이름이 중복된 경우는 없다고 전제해서 이름을 기준으로 작성하겠습니다.

입력된 내용 중 동명이인이 있는 경우에는 사번이나 주민등록번호처럼 다른 직원과 구분되는 값을 기준으로 처리해야 합니다.

 

실습 파일 다운로드

급여명세서 출력_실습.xlsx

0.02MB

 

완성 파일 다운로드

급여명세서 출력_완성.xlsx

0.02MB

급여명세서 항목 나타내기

명세서 시트에 이미 서식과 내용이 작성되어 있습니다.

현재 등록된 내용은 임금대장 시트와 연결되어 있지 않고, 일일이 수작업으로 데이터를 불러와 급여명세서를 출력할 수 있도록 틀을 만들어 둔 것입니다.

급여명세서는 두 장을 만드는데, 왼쪽 사무실 보관용 부분을 완성하면 오른쪽 직원 보관용 부분은 셀을 연결해 나타내도록 합니다.

 

명세서 시트 [AA2] 셀에 이름을 입력하면 자동으로 찾아지도록 만들려고 하기 때문에 [AA2] 셀에 임금대장 시트에 있는 이름이 모두 나타나서 선택할 수 있도록 [데이터 유효성 검사] 기능을 설정합니다.

 

[AA2] 셀을 선택한 상태에서 [데이터]-[데이터 도구]-[데이터 유효성 검사]를 선택합니다.

[설정] 탭에서 [제한 대상] 항목에서 '목록'을 선택하고 '원본' 항목에 커서를 두고 임금대장 시트 이름 필드 내용 부분을 범위선택합니다.

이제 [AA2] 셀에 직접 임금대장 시트에 등록되어 있는 이름을 입력하거나 셀에 커서를 두면 나타나는 목록에서 이름을 선택해 입력할 수도 있습니다.

이번에는 사전 준비 작업으로 임금대장 시트의 내용을 VLOOKUP 함수로 불러오려 하기 때문에 이름 정의 기능을 사용해서 수식을 알아보기 쉽게 만드려고 합니다.

임금대장 시트 [C5:V16] 셀 범위를 선택합니다.

그런 다음 수식입력줄 앞 이름 상자에 '명단'을 입력한 뒤 <Enter>를 눌러 이름을 정의 합니다.

[수식]-[정의된 이름]-[이름 관리자]를 선택하면 정의된 이름을 확인할 수 있습니다.

제대로 범위 선택하고 이름을 입력했다면 따로 확인할 필요는 없습니다.

이제 수식을 작성할 준비가 되었으니 명세서 시트로 갑니다.

 

명세서 시트 [C5] 셀 부서를 나타내는 셀은 임금대장 시트에 있는 값을 불러 오기엔 문제가 있습니다.

기준이 되는 이름 항목 왼쪽에 부서가 있기 때문에 VLOOKUP 함수를 이용할 수 없고, INDEX 함수와 MATCH 함수를 결합해 작성해야 합니다.

좀 더 간단한 방법이 있습니다.

부서는 현재 입력된 데이터 중 두 사람만 제외하고 모두 같은 부서입니다. 그리고 직원 명단이 자주 바뀌는 것도 아닙니다. 이런 경우 IF 함수로 간단하게 작성하겠습니다.

 

[C5] 셀에 커서를 두고 바로 입력합니다.

=IF(E5="하하","사회복지사",IF(E5="김태호","센터장","요양보호사"))

간단한 대신에 앞으로 두 사람이 바뀌게 되면 IF 함수의 조건을 수정해야 합니다.

 

이제 본격적으로 임금대장 시트에서 값을 가져오도록 수식 작성 합니다.

[G12] 셀을 선택 합니다.

=vlookup( 까지 입력한 뒤 [E5] 셀을 선택하고 키보드 <F4>를 눌러 절대참조합니다.

원래는 사용자가 선택하는 [AA2] 셀을 참조해야 하지만 [E5] 셀에 [AA2] 셀을 연결해 두었기 때문에 어느 셀을 선택하든 결과는 같기 때문에 선택하기 쉬운 가까운 셀을 선택했습니다.

이어서 계속 수식을 작성합니다.

 

=vlookup($E$5,명단,2,0) 을 입력하고 <Enter>를 누릅니다.

=VLOOKUP($E$5,명단,2,0)

다른 셀들도 같은 수식이고, 단지 3번째 인수만 바꾸면 되기 때문에 새로 수식을 작성하는 것보다 수식을 복사해 붙여넣은 다음 수정하면 빠르게 작성할 수 있습니다.

 

이때 주의할 점이 있는데, 복사해 붙여넣을 [I12] 셀을 보면 [I12:K12] 셀 범위가 병합되어 있습니다.

이 경우 [G12] 셀을 선택해 바로 복사하고 [I12] 셀에서 붙여넣기 하면 병합되어 있던 셀이 분리되어 [I12], [J12], [K12] 셀 각각에 수식이 나타납니다.

서식이 같이 복사되기 때문인데, 붙여넣을 때 수식만 복사하도록 선택하면 해결됩니다.

※ 주의 '수식만 복사'한 경우에 병합된 셀 중 표시되지 않는 셀에도 붙여넣기가 됩니다. 총 급여액을 구하는 수식에 표시되지 않는 셀에 붙여넣기 된 숫자 때문에 잘못된 값이 나타나게 됩니다. 해결책은 병합을 해제하고 수식을 붙여넣은 다음 다시 병합을 하는 방법입니다.

 

[G12] 셀을 선택해 마우스 오른쪽 클릭하고 복사를 선택합니다.

[I12:I16] 셀 범위를 선택한 뒤 마우스 오른쪽 클릭해 붙여넣기 항목 중 '수식'을 선택합니다.

서식은 그대로 유지되면서 수식만 그대로 복사되었습니다.

[I12] 셀은 시간*시간당임금으로 구할 수도 있지만 다른 셀과 같이 임금대장 시트에서 가져오도록 하겠습니다.

[I12] 셀을 더블 클릭하거나 <F2>를 눌러 셀 수정 상태로 전환하고 VLOOKUP 함수 3번째 인수를 4로 고칩니다.

임금대장 시트에서 기본급은 명단 이름 범위에서 4번째 열에 있기 때문입니다.

 

나머지 가산금, 시간외 수당, 장기근속장려금, 상여금 항목도 5, 8, 7, 6으로 각각 고칩니다.

[G17] 셀 총 급여액은 SUM 함수로 구했습니다.

 

아래 [E18], [E19], [E20], [E21], [I18], [I19], [I21] 셀에도 수식을 복사해 붙여넣기 옵션 중 수식을 선택해 붙여넣습니다.

 

※ 주의 '수식만 복사'한 경우에 병합된 셀 중 표시되지 않는 셀에도 붙여넣기가 됩니다. 총 급여액을 구하는 수식에 표시되지 않는 셀에 붙여넣기 된 숫자 때문에 잘못된 값이 나타나게 됩니다. 해결책은 병합을 해제하고 수식을 붙여넣은 다음 다시 병합을 하는 방법입니다.

 

고용보험, 건강보험, 노인장기요양보험, 국민연금, 갑근세, 주민세, 연말정산공제 항목도 3번째 인수를 14, 12, 13, 11, 16, 17, 15로 수정합니다.

[G22] 셀 공제계 항목은 SUM 함수로 구합니다.

=SUM(E18:F21,I18:K19,I21)

[G23] 셀 차감지급액은 수식을 작성합니다.

=G17-G22

[G24] 셀에 직접 입력된 이름도 셀 연결합니다.

=E5

날짜 자동으로 나타내기

날짜를 나타내는 셀이 [B2], [C9] 셀 두 군데입니다.

특히 [C9] 셀은 수작업으로 나타낼 때 어렵지는 않지만 실수하지 않도록 신경써야 하는 귀찮은 작업입니다.

[Z5] 셀과 [AB5] 셀에 연도와 월을 입력하면 자동으로 [B2] 셀과 [C9] 셀에 나타나도록 수식을 작성하겠습니다.

[B2] 셀은 날짜만 나타나는 것이 아니라 글자도 같이 나타나야 합니다.

문자열 연결 연산자 &(앰퍼샌드)를 이용해도 되지만 복잡한 문자열을 만들 때는 CONCATENATE 함수를 이용하는게 이해하기 좋습니다.

[B2] 셀에 커서를 두고 =conc 까지 입력한 뒤 나타나는 목록에서 CONCATENATE를 더블 클릭합니다.

단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

Text1 인수에 [Z5] 셀을 선택해 나타냅니다.

Text2 인수에 '년'과 빈 칸을 한 칸 넣습니다.

뒤에 빈 칸을 넣지 않으면 Text3 인수에 나타낼 월과 붙어서 나타나게 됩니다.

Text3 인수에 [AB5] 셀을 선택해 나타냅니다.

Text4 인수에 '월 급여명세서'를 입력합니다.

=CONCATENATE(Z5,"년 ",AB5,"월 급여명세서")

[C9] 셀에 나타낼 내용을 수식으로 작성할 때 앞 부분은 월 첫날이 항상 1일이기 때문에 쉽게 입력할 수 있지만 뒷 부분의 월 마지막 날짜를 넣는 부분은 조금 복잡한 몇가지 방법 중 하나를 써야 합니다.

첫번째 방법은 IF 함수를 이용해 월말이 30일인 달과 31일인 달을 구분하고, 2월의 경우 연도를 감안해서 28일인지 29일인지 구분해 입력할 수 있습니다.

두번째 방법은 DATE 함수와 DAY 함수를 이용해 다음 달 첫 날의 하루 전 날로 계산해 입력할 수 있습니다.

여기서는 DATE 함수와 DAY 함수를 이용하겠습니다.

그리고 3월을 03월로 월을 두자리에 맞춰 나타내기 위해 TEXT 함수를 이용하는 방법도 있지만, 여기서는 RIGHT 함수를 이용하겠습니다.

자주 쓰는 방식으로 "0"과 월을 문자열 연결 연산자로 결합해 오른쪽 기준으로 두 글자 가져오는 방식입니다.

 

[C9] 셀을 선택하고 =conc 까지 입력한 뒤 목록에서 CONCATENATE를 더블 클릭해 나타내고 단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

Text1 인수에 '[ 급여산정기간 '을 입력합니다.

Text2 인수에 [Z5] 셀을 선택해 나타냅니다.

Text3 인수에 '.'을 입력합니다.

Text4 인수에 right("0" & AB5, 2)를 입력합니다.

이 때 AB5는 직접 적는 것이 아니라 [AB5] 셀을 선택해 나타냅니다.

Text5 인수에 '.01 ~ '을 입력합니다.

띄어쓰기를 잘 보고 빈 칸이 입력되었는지 아닌지 함수 인수 대화상자 아래 수식 결과 항목을 확인하며 작성합니다.

오른쪽 이동막대를 이용해 아래로 내리고 Text6 인수에 [Z5] 셀을 선택해 나타냅니다.

Text7 인수에 '.'을 입력합니다.

Text8 인수에 right("0" & AB5, 2)를 입력합니다.

Text9 인수에 '.'을 입력합니다.

Text10 인수에 day(date(Z5, AB5 + 1, 1)-1)을 입력합니다.

DATE 함수 부분만 보면 date(2021, 3+1,1)이 됩니다.

DAY 함수 부분을 보면 2021-4-1에서 1을 뺀 값을 나타내게 되는 것으로, 다음 달 첫 날의 전날이 나타납니다.

 

마지막으로 Text11 인수에 ' ]'를 입력합니다.

완성된 수식입니다.

=CONCATENATE("[ 급여산정기간 ",Z5,".",RIGHT("0" & AB5, 2),".01 ~ ",Z5,".",RIGHT("0" & AB5, 2),".",DAY(DATE(Z5,AB5+1,1)-1)," ]")

완성된 모습입니다.

[H5] 셀 급여지급일도 나타나게 할 수 있는데, 항상 20일이 아닐 수 있어 직접 입력하도록 두었습니다.

 

 

이렇게 임금대장 시트에 내용을 입력하면 명세서 시트에 나타나도록 수식을 작성했습니다.

수작업으로 처리하는 부분이 있고, 중복 입력해야 하는 부분, 억지스러운 기능을 사용하는 부분이 있는데, 엑셀 프로그램의 한계입니다.

시중에 나와 있는 업무 프로그램처럼 매끄럽게 동작하도록 만들려면 액세스 같은 데이터베이스 프로그램을 사용해야 합니다.

물론 사용법은 따로 배워야 합니다.

 

필요하신 분들께 도움이 되기를 바랍니다.