엑셀 견적서 만드는법: 단가표와 연결하면 숫자를 다시 안 칩니다

견적서를 보내고 나면 그 파일이 어디로 갔는지 모릅니다.

지난달 그 거래처에 얼마를 불렀는지 확인하려고 메일함을 뒤집니다. 파일명이 견적서_최종_수정2라서 어느 것이 나간 판인지도 헷갈립니다.

엑셀 견적서 만드는법을 검색하면 양식이 먼저 나오는데, 견적서를 만드는 일과 나간 견적을 남기는 일은 같이 가야 합니다. 양식만 예쁘게 만들면 두 번째가 빠집니다.

엑셀 견적서 만드는법은 품목 목록부터입니다

품목명을 손으로 치면 단가가 안 붙습니다. 데이터 유효성으로 목록을 겁니다.

품목 셀을 선택하고 데이터 · 데이터 유효성 · 목록을 고른 뒤 원본에 품목 마스터 범위를 넣습니다. 품목이 늘어나도 따라오게 하려면 마스터를 표(Ctrl+T)로 만들고 이름을 참조합니다.

목록에서 고른 뒤에는 나머지가 자동으로 들어옵니다.

=IFERROR(VLOOKUP($B8,품목마스터!A:C,2,FALSE),"")

규격과 단위가 이렇게 붙고, 단가는 단가표에서 가져옵니다.

단가는 견적일 기준으로 가져옵니다

단가를 견적서에 직접 치면 단가가 개정될 때마다 양식을 고치게 됩니다.

=MAXIFS(단가!C:C,단가!A:A,$B8,단가!B:B,"<="&$C$3)

C3이 견적일입니다. 견적일을 바꾸면 그 시점 단가로 전부 다시 계산됩니다. 단가표를 이력으로 쌓는 방법은 엑셀 단가표 만드는법에 적어 뒀습니다.

거래처별 단가가 있으면 거래처 코드까지 조건에 넣습니다. 견적서 상단에서 거래처를 고르면 그 거래처 단가로 표가 채워집니다.

견적번호는 손으로 매기지 않습니다

번호를 손으로 매기면 중복이 납니다. 견적 이력 시트의 마지막 번호에 1을 더하는 방식으로 둡니다.

="Q"&TEXT(TODAY(),"yymm")&"-"&TEXT(COUNTIF(이력!$A:$A,"Q"&TEXT(TODAY(),"yymm")&"*")+1,"000")

Q2609-001 같은 번호가 나옵니다. 월이 바뀌면 001부터 다시 시작합니다.

여기서 주의할 것이 있습니다. TODAY()는 파일을 열 때마다 바뀝니다. 번호를 확정할 때는 값으로 붙여넣어 고정합니다. 이 한 단계를 빼먹으면 지난 견적의 번호가 오늘 날짜로 바뀝니다.

나간 견적을 한 줄씩 쌓습니다

엑셀 견적서 만드는법에서 대부분 빠지는 부분이 이것입니다.

견적이력 시트에 견적번호 · 견적일 · 거래처코드 · 금액 · 상태 다섯 열을 둡니다. 견적서를 보낼 때마다 한 줄을 추가합니다.

상태 열에는 발송 · 수주 · 실주 셋 중 하나를 적습니다. 이 한 열이 있으면 두 숫자가 나옵니다.

=COUNTIFS(상태범위,"수주") / COUNTA(견적번호범위)

수주 건수와 수주율입니다. 견적을 몇 건 냈고 몇 건이 돌아왔는지 아는 것과 모르는 것의 차이가 큽니다. 실주 사유를 한 열 더 두면 가격 때문인지 납기 때문인지도 쌓입니다.

인쇄가 깨지지 않게 두 가지를 고정합니다

견적서는 결국 PDF로 나갑니다. 화면에서 멀쩡하다가 인쇄에서 두 장으로 갈라지는 경우가 흔합니다.

① 페이지 레이아웃에서 인쇄 영역을 지정합니다. 품목이 열 줄이든 스무 줄이든 한 장에 들어가게 배율을 한 페이지 너비에 맞춤으로 둡니다

② 빈 품목 줄이 인쇄되지 않게 조건부 서식으로 글자색을 흰색으로 바꾸거나, 인쇄 직전에 빈 줄을 숨깁니다

엑셀 견적서 양식을 인터넷에서 받아 쓰면 이 두 가지가 대개 안 맞춰져 있습니다. 열 너비를 손대는 순간 배율이 틀어집니다.

엑셀 자동화는 이력 시트에서 시작합니다

견적서 한 장을 자동으로 채우는 것보다, 나간 견적이 이력에 자동으로 쌓이게 만드는 편이 값어치가 큽니다.

엑셀 자동화라고 부를 만한 부분은 이것 하나입니다. 견적서 시트에서 버튼 하나로 이력에 줄을 추가하는 매크로가 열 줄이면 됩니다. 매크로가 부담스러우면 견적서를 시트 복사로 만들고 이력 시트에 손으로 한 줄 적는 규칙만 지켜도 됩니다. 자동이냐 손이냐보다 이력이 쌓이느냐가 먼저입니다.

여기서 나온 수주 건수는 엑셀 매출 관리의 매출 숫자와 이어집니다.

자주 받는 질문

Q. 견적서마다 파일을 따로 만드는 것이 나쁜가요

A. 파일이 늘어나는 것 자체는 괜찮습니다. 문제는 어느 파일이 나간 판인지 모른다는 것입니다. 이력 시트에 견적번호와 파일 경로를 한 줄로 남겨 두면 파일이 몇백 개여도 찾습니다.

Q. 거래처가 양식을 자기 서식으로 달라고 합니다

A. 계산 시트와 출력 시트를 나눕니다. 계산은 한 곳에서 하고 출력 시트를 거래처별로 몇 개 만들어 참조만 걸어 둡니다. 서식이 늘어도 숫자는 한 군데서만 바뀝니다.

Q. 부가세와 할인은 어디에 넣나요

A. 합계 아래에 공급가액 · 부가세 · 합계금액 세 줄로 둡니다. 할인은 품목 줄에 섞지 않고 조정 열이나 별도 줄로 뺍니다. 품목 단가를 깎아 적으면 나중에 실제 판매단가를 못 셉니다.

Q. 엑셀 견적서 양식을 매크로 없이 만들 수 있나요

A. 됩니다. 드롭다운과 조회 수식만으로 입력은 다 자동이 됩니다. 매크로가 필요한 부분은 이력에 줄을 자동으로 쌓는 것 하나뿐이고, 그건 손으로 적어도 됩니다.

견적 건수와 수주 건수가 같이 남는 달부터 가격을 다시 봅니다. 그 두 숫자부터 쌓아 두려 합니다.