마지막으로 세금계산서 만들기.
일단 세금계산서 양식 만들기 겁나 복잡하다.
나도 이것 만들다가 머리에 쥐날 것 같아서 인터넷에서 구한 폼을 가지고
조금 변형했다.
일단 세금계산서는 상하 공급자 보관용과 공급받는자 보관용으로 나뉘어 진다.
옛날에는 4장이었다.
지금은 전자세금계산서까지 나와 종이로 안 찍어도 된다.
공급자 보관용은 적색양식이고 공급받는자는 청색양식이다.
이 것만 제외하고는 모양과 내용은 동일하다.
품목은 견적서나 거래명세서처럼 줄줄이 다 나열 안시키고 대표로 하나만 기재해도 된다.
해당 세금계산서의 증빙서류가 거래명세표가 되기 때문이다.
따라서 본 시트의 세금계산서의 품목은 견적서장의 내용으로 입력한 "업무용 컴퓨터" 로 하고
거래명세표의 매출금액 총액을 공급가액으로 그리고 세액 합계를 세액으로 명시했다.
일단 공급자의 사업 등록내역 (등록번호, 상호, 성명 등등)은 거래명세표의 것을 그대로 가져왔다.
다음 공급받는자의 상호는 견적서상의 상호 셀의 데이터 (B4)를 가져오고
다른 사업 등록 내역은 VLOOKUP() 함수를 사용해서 "거래처목록" 으로 이름 정의된 영역에서
해당 열번호를 MATCH() 함수로 얻은 열번호의 데이터를 가져왔다.
물론 견적서의 A3 에 담겨있는 거래처 목록의 행번호를 가지고 INDEX() 함수를 사용해서
가져올 수도 있다.
하지만 INDEX() 는 지금까지 많이 했기 때문에 혹시 잊어버릴까봐 VLOOKUP() 함수를 사용했다.
이렇게 하면 샘플시트처럼 견적서에서 콤보박스로 업체를 찾지 않고 그냥 수기로 입력했을 경우
정확히 입력만 한다면 해당 업체의 정보를 불러올 수 있게 된다.
공급받는자의 등록번호인 U3 셀을 보자
=VLOOKUP(U4,거래처목록,MATCH("사업자번호",거래처헤드,0 ),0)
일단 VLOOKUP() 함수를 다시 보면
VLOOKUP(찾을데이터, 찾을영역, 찾을열번호, 옵션) 이다.
즉 찾을영역에서 찾을데이터를 컬럼헤딩으로하는 행을 찾고 해당 행의 찾을열번호의 데이터를 가져오게 된다.
따라서 위의 식은 "거래처목록"으로 이름정의된 영역에서 U4 즉 "한솔 전지" 가 컬럼헤딩인 행의 위치를
위에서 아래로 찾은 다음 해당 행에서 MATCH() 함수로 찾아낸 "사업자번호" 열의 번호를 이용해
해당 업체의 사업자 번호를 가져오는 것이다.
"거래처헤드"는 거래처 목록에서 로우헤딩행(열제목행) 이기 때문에 열제목에서 "사업자번호" 의 행번호를
MATCH() 함수를 통해 얻어낼 수 있다.
역시 마찬가지로 VLOOKUP() 함수와 MATCH() 함수를 이용해서
해당 업체의 "대표자", "주소", "업태", "종목"을 불러와 각 셀에 표시했다.
다음 상단의 일련번호와 작성일자는 임의로 입력하도록 되어 있고 이 작성일자의 년,월이
하단 품목란의 월 일 부분에 같이 들어가게 된다.
다음 공급가액 과 세액을 한 글자씩 떼어서 칸안에 삽입시키는 부분인데
여기서 우리가 여태 다루지 못한 ISERROR() 함수가 나타난다.
상당히 논리적 분석이 필요로 한다.
=IF(ISERROR(
MID(거래명세표!$I$24,LEN(거래명세표!$I$24)-(14-COLUMN()),1) ),"",
MID(거래명세표!$I$24,LEN(거래명세표!$I$24)-(14-COLUMN()),1)
)
ISERROR(함수)는 함수의 실행에 오류가 발생했을 경우에는 TRUE 를 반환하고 성공하면 FALSE 를 반환한다.
즉 위의 식에서는 MID() 함수를 실행해서 오류가 있을 경우에는 해당 셀에 "" 즉 공백문자를 입력하고
MID() 함수가 성공했을 경우에는 그 뒤의 MID() 함수의 결과값을 셀에 넣으라는 뜻이다.
간추리면IF(ISERROR(MID(....)) ,"",MID(.....)) 가된다.
다시 해석을 해보면 "만약 MID(...) 함수의 실행에 오류가 있으면 "" 이고 아니면 MID(....) 이다" 가 된다.
그럼 오류가 나는지 안나는지 파악하는 파란색 MID(....)를 보자.
MID() 함수는 문자열에서 원하는 위치의 문자를 짤라 반환하는 함수로
함수 형식은MID(대상문자열,짤라낼시작위치,짤라낼크기) 이다.
즉 대상문자열에서 짤라낼시작위치부터 짤라낼크기 만큼의 문자를 짤라서 반환하는 함수이다.
MID("123456",3,2) 하게되면 문자령 "123456" 의 3번째 문자부터 2개의 문자를 짤라낸 "34" 를 반환한다.
그런데 만약 MID("123456", 7, 2)이렇게 하면 문자열의 길이는 6인데 7번째 부터 2개를 잘라내라고 하니
이런 경우에 오류가 발생하게 된다.
따라서 ISERROR(MID("123456",7,2))같은 경우 오류가 발생하므로TRUE 가 되며
ISERROR(MID("123456", 3, 2)) 같은 경우에는 오류가 발생하지 않으므로FALSE 가 된다.
MID() 함수의 내용을 보자.
MID(거래명세표!$I$24,LEN(거래명세표!$I$24)-(14-COLUMN()),1)
잘라낼 대상 문자열은 거래명세표의 I24 즉 금액의 합계금액을 대상으로 한다.
자를 위치는 LEN(거래명세표!$I$24)-(14-COLUMN())이다.
자를 크기는 1 이다.
여기서 자를 위치를 구하는 부분이 복잡한 부분이다.
자 세금계산서 공급가액 부분을 보면
합계금액인 1453634 를 한 글자씩 잘라서 E 열 부터 N 열 까지 10개의 칸에 넣어야 한다.
일단 일단위를 자르기 위해서는 위 합계금액 1453634 의 어느 위치에서 한 글자를 잘라야 할까?
7번째 위치에서 1자 를 짤라야 한다.
다음 십단위를 자르기 위해서는6번째 위치에서1자 를 자르고
다음 백단위를 자르기 위해서는 5번째 위치에서 1자 를 자르고 이렇게 십억단위까지 잘라내야 한다.
자 처음 일단위 를 자르기 위한7번째 위치는 어떻게 나온 것인가?
만약 합계금액이 12345678 이라면 일단위를 자르기 위한 첫 위치는 문자열의8번째 위치가 된다.
만약 합계금액이 123456789 이라면 일단위를 자르기 위한 첫 위치는 9번째 위치가 된다.
즉 첫 일단위를 자를 위치는 해당 문자열의 크기가 되는 것이므로 문자열의 크기를 알기위해
LEN(문자열) 함수가 동원되는 것이다.
다음 이 자를 위치가 하나씩 왼쪽으로 이동할 때마다 위치도 하나씩 줄어들게 된다.
즉 일단위 다음 십단위를 자를 경우에는 일단위위치 - 1 이 되고 백단위는일단위 위치 - 2
이렇게 컬럼위치가 하나씩 줄어들면서(왼쪽으로가면서) 자를 위치도 하나씩 줄어들게 되고
이렇게 위치를 줄여나가기 위해서 기준위치 - 현재 컬럼위치인 COLUMN() 함수가 동원된 것이다.
기준위치는 시트상의 공급가액 중 일단위가 있는 로우 N 이므로 14번째 컬럼이 된다.
즉 일단위를 자를 위치는 문자열길이(7) - 기준위치 (14) -일단위위치(14) =7 이 자를 위치가 되고
십단위를 자를 위치는 문자열길이(7) - 기준위치 (14) -십단위위치(13) = 6 이 되며
백단위를 자를 위치는 문자열길이(7)- 기준위치 (14)-백단위위치(12) = 5 가 된다.
이런 방법으로 합계금액을 오른쪽부터 하나씩 잘라내서 각 칸에 한자씩 표시 시키게 했다.
자 그런데 금액이 백만단위이므로 백만단위 이상은 자를 위치가 0 이하가 된다.
이경우 ISERROR(MID(...)) 가 TRUE 가 되므로 천만단위 부터는 "" 즉 공백문자가 들어가게 된다.
백만단위 까지는 잘 자를 수 있으므로 ISERROR(MID(..)) 가FALSE 가 되므로
뒤의 MID(....) 함수로 잘라낸 한 글자가 셀에 표시가 되게 되는 것이다.
앞의MID(...) 나 뒤의 MID(...) 나 내용은 동일하다.
뒤의 세액 부분도 동일하다 다만 잘라낼 대상이 거래명세서의 금액의 합계가 아닌 세액합계라는 점과
기준위치가 23번 (세액의 일단위 위치) 이라는 것만 다를 뿐이다.
다음 공란은 공급가액 칸에서 공백으로 된 부분의 갯수를 세는 것이므로
=COUNTBLANK(E9:N9)함수를 이용해서 간단히 공백의 갯수를 계산할 수 있다.
다음 품목란의 월일은 위의 작성일자의 월일을 그대로 적용하면 되며
금액과 세액도 거래명세서의 금액과 세액을 그대로 불러오면 된다.
하단의 합계금액은 금액 + 세액하면 되고
금액의 영수 청구 체크 부분도 견적서의 세액포함 별도의 옵션단추 사용과 동일한 내용으로
다만 계산서 상에 이금액을 영수함으로 할 것인가 청구함으로 할 것인가만 표시하기 위해서
사용하는 것이다.
하단의 공급받는자 보관용 계산서는 그냥 위의 각 셀과 동일하게 연결만 시켜주면 된다.
이상으로 실무 : 견적서 거래명세표, 세금계산서 만들기 강좌를 끝냄.
영업관리 샘플. : Sales.xlsx
덧글
스포츠배배팅사이트. www.pnc-no1.com 가입 코드 tatoo
스포츠경기 최소 한 경기부터 배팅참여 가능.
누적회원 1만 4천명 돌파 기념 신규회원 무료 4천원 제공중.
24시간 운영. 24시간 친절 상담원 대기.
스포츠배팅사이트,온라인스포츠배팅,인터넷스포츠배팅
사설토토,온라인토토,사설토토배팅,온라인토토사이트
해외스포츠배팅,국내스포츠배팅사이트,야구배팅,축구배팅
부탁드립니다. 메일주소는 bedals@naver.com 입니다.