실무자를 위한 엑셀로 급여대장을 만드는 방법으로 알아보는 기초적인 엑셀 사용법 ft vlookup, iferror, rounddown

세무사사무실의 본연의 업무는 아님에도 고유 업무처럼 느껴질 정도로 많이 접하게 되는 것은 노무업무인 사대보험 관련 업무입니다.

거기에 더하여 간혹 거래처의 경리에게 받아야 할 급여대장을 직접 작성해 주는 경우들도 생기기 마련입니다.

 

보험료는 고지된 대로 원천징수하고, 세금은 간이세액표에서 따오는 방식으로 제대로 원천징수를 하는 경우에는 입력이 번거로울 뿐, 회계프로그램에서 대강 정리가 가능하니 어차피 별 일은 아닌데... 제대로 원천징수 하지 않는 곳들이 문제입니다.

 

이런 경우에 엑셀을 사용하면 일을 오히려 편하게 할 수 있는 경우도 있는데... 급여대장을 만들어 보는 예시를 통해 엑셀의 활용법을 익혀 보겠습니다.

 

 

 

들어가며...

위의 사진은 제가 예전에 작업을 하였던 엑셀 파일입니다.

 

이 회사는 '추가 수당'이라는 개념이 있어, 추가 수당만을 대표자가 알려주면, 급여대장 및 급여명세서를 회사에 만들어 주는 것과 별개로... 공동사업자도 아닌 사무실만 공유하는 두 명의 거주자가 모지란 나머지 공동사업장으로 사업자 등록을 하였는데, 각각 관리하는 직원이 있고 매 세금을 낼 떼마다 해당 각각의 대표자가 납부해야 하는 세금을 알려줘야 하는 곳입니다.

 

 

먼저 해당 거래처에서 추가수당을 카톡등으로 알려주면 저는 위의 칸에 추가수당을 입력해 주게 됩니다.

 

 

그럼 vlookup 함수를 이용하여 추가수당의 값을 불러와 위와 같이 급여대장이 자동으로 작성이 됩니다.

 

 

마찬가지로 vlookup 함수를 이용하여 위에서 작성된 급여대장의 값을 불러와 자동으로 급여명세서도 만들어지게 되며, 이렇게 되면 추가 수당으로 인한 급여나 갑근세의 변동을 반영하여 급여대장과 급여명세서를 자동으로 생성해 주니 일일이 계산하여 세무사랑등의 회계프로그램에 숫자를 입력하는 것보다 빠르게 처리할 수 있어, 당장 급여를 지급해야 하여 마음이 급한 거래처에서는 만족도가 올라가고, 실무자 입장에서는 회계프로그램을 이용하는 것보다는 일이 줄게 됩니다.

 

 

당연히 두 명의 공동사업자도 아닌데 공동사업자로 사업자등록을 한 모지리들이 각각 부담해야 할 세금도 자동으로 계산이 되는 것으로 해당 엑셀 파일은 마무리됩니다.

 

 

 

vlookup 함수를 이용한 간이세액표에서 갑근세 불러오기

홈택스에서 근로소득 간이세액표를 보고 내려받는 방법 feat 급여대장 원천세 (tistory.com)

홈택스에서 근로소득 간이세액표를 보고 내려받는 방법 feat 급여대장 원천세

간이세액표에 대해서는 이전에 언급한 바가 있습니다.

 

 

 

최근에 간이세액표의 자녀 관련 부분이 개정되었는데, 어차피 함수를 설명하려는 것이니 설명의 편의를 위해 자녀 부분은 무시하고 설명을 작성해 보겠습니다.

 

 

간이세액표의 경우 부양가족의 수에 따라 다른 칸의 값을 적용하게 되어 있으므로, 위 갈무리와 같이 급여대장의 어딘가에는 '피부양자'의 수가 기록되어 있어야 합니다.

 

 

소득세를 불러오는 함수는 이 파일에서는 위와 같은데, 기본적으로는 vlookup 함수를 사용하고 있음을 알 수 있습니다.

 

 

=VLOOKUP((T3-U3)/1000,'간이세액 240301'!$A$7:$M$652,2+계산!F3)

수식을 간단하게 만들어보자면 위와 같이 됩니다.

 

 

=VLOOKUP((총지급액의 셀-비과세 합계의 셀)/1000,'간이세액표가 있는 시트의 간이세액표의 범위',2+'부양가족수 셀값')

 

문자로 보면 위와 같은 상태인데...

 

 

=VLOOKUP(조회 값, 조회 값이 포함된 범위, 반환 값이 포함된 범위의 열 번호, 대략적인 일치(TRUE) 또는 정확히 일치(FALSE))

vlookup 함수는 찾으려는 조건의 값의 가로에 있는 값들을 가져오는 함수로 작성 방식은 위와 같으며 위의 함수를 처음부터 작성해 보겠습니다.

 

 

 

=VLOOKUP((총지급액의 셀-비과세 합계의 셀)

식대나 차량유지비등 비과세 되는 금액은 소득세를 과세하지 않으므로, 먼저 총지급액에서 비과세 되는 금액을 빼줍니다.

 

 

이후 위의 과세되는 값을 간이세액표에서 찾아 숫자를 불러와야 하는데, 간이세액표의 급여의 값의 범위가 '천 원' 단위로 되어 있으니 위에서 계산한 과세되는 값의 금액을 아래와 같이 1000으로 나눠줍니다.

 

 

=VLOOKUP((총지급액의 셀-비과세 합계의 셀)/1000,

이제 찾아야 할 값은 나왔고, 이를 간이세액표에서 찾아주면 되니 해당 값과, 찾아서 반환할 값이 있는 범위를 지정해 줍니다.

 

 

=VLOOKUP((총지급액의 셀-비과세 합계의 셀)/1000,'간이세액표가 있는 시트의 간이세액표의 범위',

vlookup 함수는 찾으려는 참조값을 범위의 첫번째 열에서 찾게 됩니다.

(위 사진상 A열)

실무적으로는 값을 찾을 범위의 입력은 키보드가 아닌 마우스 드래그로 많이 하게 될 것입니다.

 

 

위와 같이 드래그를 해보면 시트가 동일 통합문서인지 아닌지에 따라 반환되는 값이 좀 다를 수 있는데, 위에서 반환된 값에는 $ 기호가 붙어 있지 않습니다.

 

이런 경우 수식을 작성해놓은 후 급여대장의 다른 사람에게도 같은 수식을 적용하려고 드래그하면, 참조하는 영역도 따라서 이동하게 됩니다.

 

 

함수의 계산식이 표시된 수식줄에서 참조영역 부분을 마우스를 드래그하여 선택하고, F4 키를 누르면 위와 같이 참조영역이 고정된 영역으로 바뀌게 됩니다.

 

 

=VLOOKUP((총지급액의 셀-비과세 합계의 셀)/1000,'간이세액표가 있는 시트의 간이세액표의 범위',2+'부양가족수 셀값')

참조영역에서 값을 반환하기 위해 적당한 인덱스 번호를 선택해 주면 되는데...

 

 

지금까지의 과정을 이해를 돕기 위해 그림으로 보자면, 과세되는 급여를 1,000으로 나눈 후 해당 값을 위의 주황색 범위에서 찾아, 첫 열이 찾는 값과 일치하는 노랑 값의 열에서 인덱스 번호의 값을 반환해 주게 됩니다.

 

이때 인덱스 번호가 1인 경우에는 참조 값과 같거나 유사한 찾은 값을, 2인 경우에는 한 칸 오른쪽의 값인 2,090을 불러오게 되므로, 부양가족이 본인 혼자인 경우 인덱스 값이 '3'여야 하며, 추가 부양가족이 있는 경우 그만큼 오른쪽으로 더 이동하여 값을 불러와야 하기에 위의 함수 작업 중 마지막에 붙인 인덱스 부분의 값이 '2+'부양가족수 셀값''으로 되어 있는 경우 부양가족이 1로 되어 있다면, 본인 외에 부양가족이 없는 것이 됩니다.

 

또한 브이룩업 함수의 경우 =VLOOKUP(A1, 조회 값이 포함된 범위, 반환 값이 포함된 범위의 열 번호, 대략적인 일치(TRUE) 또는 정확히 일치(FALSE)) 설명과 같이, 참조값과 찾을 값이 유사일치인지, 정확히 일치인지 선택할 수 있습니다.

 

참조값이 이름이나 상호같은 문자열인 경우에는 제가 작성한 서식 뒤에 ',false' 문구를 추가하는 것으로 정확히 일치 옵션을 선택하는 경우가 많습니다만... 지금과 같이 간이세액표에서처럼 모든 숫자가 나열된 것이 아닌 범위의 일부 값만 나열되어 있는 경우에는 값이 정확히 일치하지 않으면, 결과값이 없다고 반환되기 때문에... 이런 경우에는 함수 뒤를 비워두거나, ',true' 문자열을 추가해 주면 대략적으로 일치하는 값을 반환하게 됩니다.

 

이렇게 '유사 일치'로 검색할 때 주의할 점이라는 것은 참조값을 찾는 범위의 첫 열의 숫자들이 아래로 내려갈수록 숫자가 커지는 오름차순으로 정렬되어 있어야 한다는 점인데, 국세청에서 제공하는 간이세액표는 항상 오름차순으로 되어 있어 따로 신경을 쓸 부분은 아닙니다.

 

 

이렇게 급여대장 작성 시 간이세액표에서 갑근세를 불러오는 법을 알아보았으며, 개정된 간이세액표 역시 피부양자에 따라 금액을 더하고 빼기만 하면 되므로 위의 수식을 조금만 더 다듬으면 됩니다.

 

 

요즘은 xlookup이라는 함수를 사용하기도 하고, 세로값에서 값을 찾는 같은 구조의 hlookup 함수도 있으나, 엑셀 버전에 따라 xlookup 함수를 사용할 수 없는 경우도 있고 하니 여전히 경리 업무 중에서 가장 많이 사용되는 함수는 상기의 vlookup 함수일 것입니다.

 

 

 

ifError 함수를 이용한 오류값 지우기

=IFERROR(VLOOKUP((T3-U3)/1000,'간이세액 240301'!$A$7:$M$652,2+계산!F3),0)

본문의 시작 부분에서 스크린숏에 보이는 최초의 수식은 바로 위의 결과와 다르게 앞뒤로 무엇인가가 더 붙어 있습니다.

 

 

=IFERROR(갑근세를 불러오는 서식,0)

요약하면 위와 같은데... 엑셀에서 함수를 추가하고 싶은 경우 작성된 서식의 맨 앞에 있는 '=' 문자열을 빼고, 해당 수식의 가장 왼쪽에 '=새로운 함수('를 입력해 주면 됩니다.

 

 

여기서는 iferror 함수가 추가된 것인데... 이프에러 함수의 작성 방법은 아래와 같습니다.

IFERROR(값, 값이 오류일 때 사용할 값)

 

 

위의 함수가 추가된 이유는 간이세액표 때문인데... 간이세액표에서는 숫자가 들어가는 최소값이 위 사진처럼 770인지라, 과세되는 급여가 77만 원 이하인 경우에는 내야 할 갑근세가 없다고 0 값을 반환하는 것이 아닌, 참조값을 찾을 수 없다고 에러값을 반환하게 됩니다.

 

이렇게 오류 값을 반환하게 될 때, 반환할 오류값에 '0'을 입력해 두면 과세되는 급여가 77만 원 이상인 경우에는 간이세액표에서 값을 불러오고, 77만 원 미만인 경우에는 0원이라는 세금을 반환하게 됩니다.

 

 

좀 더 정교하게 간다면, 월급여가 위와 같이 큰 금액일 때도 고려해야 하나, 세무사사무실에 기장을 맞기는 거래처 수준에서 저 정도 급여가 나오는 경우의 회사라면, 급여대장을 경리가 만들어 넘겨주는 경우인지라 언급하는 것이 크게 의미 없어 넘어가겠습니다.

 

 

 

rounddown 함수를 이용한 원 단위 버리기

국세에 이어 지방세를 급여대장에 반영해주어야 하는데, 지방소득세의 경우 국세의 1/10이니 계산식에 별것은 없으나, 원 단위 절사로 인해 새로운 함수를 하나 써야 합니다.

 

 

ROUNDDOWN(숫자, 절사할 숫자의 값)

round 관련 함수는 위와 같은데... 원단위를 절사 하는 경우에는 '절사 할 숫자의 값'에 '-1'을 입력한다는 점만 외우면 되겠습니다.

 

 

=ROUNDDOWN(Z3/10,-1)

본문의 스크린숏의 수식은 위와 같은데 'z3' 셀에 있는 갑근세의 값을 10으로 나눠준 후, 원단위를 절사하는 서식이라는 것은 이제 충분히 이해하시리라 생각됩니다.

 

 

ROUNDDOWN 함수 - Microsoft 지원

ROUNDDOWN 함수 - Microsoft 지원

라운드다운 함수의 더 자세한 사용법은 위의 글을 참고하시기 바라며, 유사한 함수로 올림을 해버리는 roundup, 반올림을 하는 round 함수도 존재합니다.

 

 

 

사회보험통합징수포털에서 사대보험료의 개인별 산출내역 조회 (tistory.com)

사회보험통합징수포털에서 사대보험료의 개인별 산출내역 조회

사대보험료의 경우 원래는 위의 이전 글에서와 같이 고지된 내역을 다달이 조회하고, 급여대장에 반영하는 것이 맞으나 실무적으로는 연말정산 시 보험료 폭탄을 방지하거나, 중도퇴사가 잦아 중도정산이 번거로워 다달이 과세되는 급여에 보험요율을 곱하여 공제하는 경우가 회사들이 있는데... 이런 상황을 지원하는 회계프로그램은 없습니다.

 

이런 경우 세무사랑이나 스마트에이 등의 회계프로그램에서 처리할 수 없어 일일이 과세되는 금액에 보험요율을 곱하는 것이 번거로우니 엑셀을 활용하는 것이 편하겠습니다.

 

또한 이런 식으로 계산을 하는 회사의 경우 다달이 급여에 변동이 있는 경우가 대부분이기 때문에 역시나 또 엑셀을 활용하는 것이 더 빠르고 효율적입니다.

 

무엇보다 계산기를 이용한 계산은 사람이 하는 계산이라 실수가 발생할 수 있는데, 엑셀로 한번 양식을 만들어 두면 여기서는 계산이 틀릴 일이 없기 때문에 실수를 줄일 수 있게 되기도 합니다.

 

 

다시 하던 얘기로 돌아가 사대보험을 요율대로 떼는 경우에는, 요율에 과세되는 금액을 곱하면 되는 간단한 계산식이므로 따로 수식을 언급할 생각은 없으나, 역시나 원 단위 절사가 필요하므로 여기서도 rounddown 함수는 사용됩니다.

 

 

 

=RIGHT(CELL("filename",B2),LEN(CELL("filename",B2))-FIND("]",CELL("filename",B2)))

급여명세서를 작성해 주는 경우에도 위에서 언급한 함수들을 이용하여 앞으로는 자동으로 급여명세서가 작성될 것입니다.

문제는 직원이 여럿이고, 그 여럿의 급여명세서를 작성하여 발송해야 하는 경우입니다.

 

여기까지는 어떻게 급여명세서 시트를 하나 만들었다 치고, 위에서 작성한 급여대장의 값을 vlookup 함수를 이용하여 불러와 자동으로 내용을 채우게 했더라도, 이 시트를 개인별로 번거롭게 만들어야 하는 일이 남아있습니다.

 

 

=RIGHT(CELL("filename",B2),LEN(CELL("filename",B2))-FIND("]",CELL("filename",B2)))

 

이럴 때 사용할 것이 위의 함수인데... 이건 따로 설명할 것도 없고... 그냥 위의 코드를 복사 붙여 넣기 하여 사용하시면 됩니다.

 

 

셀에 현재 Excel 파일 이름, 경로 또는 워크시트 삽입 - Microsoft 지원

셀에 현재 Excel 파일 이름, 경로 또는 워크시트 삽입 - Microsoft 지원

자세한 내용은 위에서 확인할 수 있습니다.

 

 

위의 값을 아무 셀에나 입력하면 해당 셀의 반환값은 시트의 이름이 되니, 위의 반환된 값을 참조값으로 하여 vlookup 함수를 사용하여 급여대장의 값을 불러오면 됩니다.

 

이후에는 완성된 시트를 복사하여 시트의 이름만 다른 사원의 이름으로 변경해 주면, 앞으로 인원변동시마다 급여명세서를 새로 작성할 필요 없이 바로바로 만들 수 있습니다.

 

 

이후 인쇄 화면에서 인쇄 범위를 '전체 통합 문서 인쇄'로 설정하면 모든 급여명세서 및 급여대장이 동시에 인쇄가 가능하며, 이때 프린터를 'pdf' 저장이 가능한 가상 프린터로 설정하면 급여명세서를 일괄적으로 출력하여 파일로 거래처에 보내줄 수 있게 됩니다.

 

엑셀 파일은 최초 한 번만 구축해 두면 되므로 손이 많이 가는 급여대장이라 하루 종일 작업하고도 틀린 부분에 대해 사과해야 하는 경우가 종종 발생되는 거래처의 경우 사과할 일도 줄고... 남들이 하루를 쏟아부을 일을 엑셀로 구축해 둔 시스템 덕분에 이후에는 5분 컷이 가능해집니다.

 

거래처에서의 만족도도 올라가게 되는데 이전의 담당자가 급여대장을 작성해 주는데 하루가 넘게 걸렸다면 이제는 5분 컷으로 돌아오니 당연히 만족도가 올라갈 수밖에 없습니다.

 

 

세무회계실무자를 위한 업무에 도움이 되는 윈도우즈 프로그램들 (tistory.com)

세무회계실무자를 위한 업무에 도움이 되는 윈도우즈 프로그램들

위의 npdf 프로그램에 대해서는 이전에 글을 안내한 바 있으니 필요하신 경우 위의 글을 참고하시기 바랍니다.

 

 

 

마지막으로... 스마트에이나 세무사랑 모두 급여대장의 업로드가 가능하기 때문에...

이렇게 작업 후 업로드 양식도 자동으로 작성되게 파일을 손봐두면, 앞으로는 진상 거래처가 급여대장 작성에 5분 컷에, 원천세 신고도 5분 컷이 되는 꽤나 편한 거래처가 될 수도 있습니다.

 

 

 

본문의 예제로 사용된 파일의 회사는 위의 같이 급여명세서의 특이사항에 휴일근로수당 계산식이나 사업주가 부당해야 하는 보험료 등도 자동으로 계산되어 표기해 주는데, 이는 본문의 함수를 충분히 익히면 여러분들도 하실 수 있는 아주 간단한 작업입니다.

 

 

 

 

이번에는 다른 회사의 예시인데... 이곳은 휴일근무수당이 중요한 거래처입니다.

위 사진상의 지정된 셀에 휴일근무시간만 적어주면, 휴일근무수당은 자동으로 계산되며, 그에 맞춰 보험료나 갑근세도 자동으로 계산이 됩니다.

 

 

물론 동시에 급여대장도 자동으로 작성이 되어 거래처에 발송할 수 있으며,

 

 

급여대장의 오른편엔 회사의 대표자가 요구하는 휴일근무수당의 산출식도 자동으로 불러옵니다.

 

 

마지막으로 이 회사는 급여대장의 차인지급액을 은행 대량이체 양식으로 작성해 주기까지 해야 하는 번거로운 회사인데... 역시나 알아서 숫자를 불러오게 됩니다.

 

 

 

이런 작업을 회계프로그램을 이용하여 한다면, 다달이 변동되는 급여에 보험요율을 일일이 계산기로 곱하여 숫자를 입력하고, 숫자가 맞는지 다시 검증하고, 그렇게 만든 급여대장에 일일이 휴일근무수당의 계산식을 타자를 쳐주고, 거래처에 자료를 보내준 후 어딘가의 숫자가 맞지 않는다고 욕을 먹은 후, 몇 번 자료가 오가고 나서야 급여대장이 확정되고, 그 이후 다시 급여대장을 열어 이체 양식에 숫자를 입력해 주었는데, 오타가 나서 이체 양식의 차인지급액과 급여대장의 차인지급액이 일치하지 않아 거래처에서 또 연락을 받게 되는 경우가 될 겁니다.

 

 

 

그러므로 제가 항상 직원들에게 강조하는... 어떻게 하면 일을 편하고 빠르게 할 수 있을까를 항상 고민하는 것이 좋습니다.

 

비록 회계프로그램을 메인으로 사용하더라도 엑셀의 사용법을 조금만 익혀두면 다른 직원들보다 빠르게 장부를 작성하고 남는 시간에 휴식을 하거나 신고서 검토를 한번 더 할 수 있게 되며, 이는 본인의 업무능력 향상이 되어 회사에서 인정받고, 거래처의 만족도를 높일 수도 있으며, 실수를 줄일 수 있는 방법입니다.

 

 

 

팡숀 많이 사용하시기 바랍니다.