엑셀 견적서 양식 만들기: 품목 단가 VLOOKUP 자동 채우기, 공급가액·부가세 ROUND 계산

 

핵심 요약
엑셀 견적서는 품목 목록을 따로 만들어 VLOOKUP으로 단가를 자동으로 불러오고, 수량과 단가를 SUMPRODUCT나 SUM으로 더해 공급가액을 낸 뒤 ROUND로 부가세를 원 단위까지 맞추면 손계산 없이 금액이 맞아떨어집니다. 처음부터 그리기 번거로우면 마이크로소프트 Create의 무료 템플릿을 받아 고쳐 써도 됩니다.

 
거래처에서 견적서를 보내 달라는 요청을 받으면 품목과 수량, 단가를 적고 공급가액과 부가세를 계산해 한 장으로 정리해야 합니다. 품목이 몇 개 안 되면 계산기로 두드려도 되지만, 품목이 늘고 단가가 자주 바뀌면 숫자가 어긋나기 쉽습니다.
 
엑셀로 견적서 양식을 한 번 만들어 두면 품목명만 고르면 단가가 VLOOKUP으로 들어오고, 수량과 단가를 SUMPRODUCT로 한 번에 더해 공급가액을 내고, ROUND로 부가세를 원 단위까지 맞출 수 있습니다. 양식을 처음부터 그리기 번거로우면 마이크로소프트 Create의 무료 템플릿을 받아 고쳐 쓰는 방법과, ERP 견적 기능과의 차이도 함께 정리합니다.
 

 

목차
① 품목을 고르면 단가가 자동으로 (VLOOKUP)
② 수량 × 단가로 금액과 합계 (SUMPRODUCT, SUM)
③ 공급가액·부가세·합계 정리 (ROUND)
④ 템플릿 활용과 ERP 견적의 차이
⑤ 엑셀 견적서 만들 때 오류·상황별 해결

 

1. 품목을 고르면 단가가 자동으로 들어오게 (VLOOKUP)

견적서마다 단가를 외워서 치지 않으려면 품목명과 단가를 적어 둔 '품목 목록'을 따로 만들고, 견적서에서는 품목명만 입력해 단가를 끌어오게 합니다. 이때 쓰는 함수가 VLOOKUP입니다. 구문은 다음과 같습니다.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

 
lookup_value는 찾을 값(품목명), table_array는 찾아볼 범위(품목 목록), col_index_num은 가져올 열 번호, 마지막 range_lookup은 0(FALSE)이면 정확히 일치입니다. 예를 들어 '품목'이라는 시트에 아래처럼 목록을 두었다고 하겠습니다.
 

A열 (품목명)	B열 (단가)
A4용지(1박스)	25,000
볼펜(12개입)	9,000
토너	78,000

 
견적서의 품목명이 B5 칸에 있고 단가를 D5 칸에 넣는다면 수식은 다음과 같습니다.

=VLOOKUP(B5, 품목!$A$2:$B$20, 2, 0)

 
table_array의 맨 왼쪽 열(A열)에 찾는 품목명이 있어야 하고, 2는 단가가 들어 있는 두 번째 열, 0은 정확히 일치입니다. 찾는 품목명이 목록 맨 왼쪽 열에 없으면 #N/A 오류가 나므로, 품목명을 목록과 똑같이 맞추고 범위에는 $를 붙여 고정합니다.
 

2. 수량 × 단가로 금액과 합계 내기 (SUMPRODUCT, SUM)

단가가 들어왔으면 수량을 곱해 줄별 금액을 내고, 그 금액을 모두 더해 공급가액을 구합니다. 수량이 C열, 단가가 D열, 금액이 E열이라면 수식은 다음과 같습니다.

=C5*D5
=SUM(E5:E14)
=SUMPRODUCT(C5:C14, D5:D14)

 
E5에 =C5*D5를 넣어 줄별 금액을 내고 SUM으로 더해도 되고, 금액 열을 따로 두지 않고 SUMPRODUCT로 수량과 단가를 한 번에 곱해 더해도 결과는 같습니다. SUMPRODUCT는 같은 행끼리 곱한 뒤 전부 합하는 함수입니다.
 

 

품목명	수량	단가	금액(=수량×단가)
A4용지(1박스)	2	25,000	50,000
볼펜(12개입)	3	9,000	27,000
토너	1	78,000	78,000
공급가액 합계			155,000

 
위 예시에서 SUM(E5:E14)과 SUMPRODUCT(C5:C14, D5:D14)는 모두 155,000이 됩니다. 다만 SUMPRODUCT는 곱할 두 범위의 크기가 같아야 하며, 수량 범위와 단가 범위의 행 수가 다르면 #VALUE! 오류가 납니다.
 

3. 공급가액·부가세·합계를 원 단위로 정리하기 (ROUND)

공급가액이 나왔으면 부가가치세 10%를 더해 합계금액을 냅니다. 10%를 곱하면 원 미만 소수가 생길 수 있어 ROUND로 정리합니다. 구문은 =ROUND(number, num_digits)이고, num_digits가 0이면 가장 가까운 정수로 반올림합니다.

=ROUND(E15*0.1, 0)
=E15+E16

 
E15가 공급가액, E16이 부가세, E17이 합계금액일 때의 수식입니다. 공급가액 155,000원을 기준으로 계산하면 다음과 같습니다.
 

항목	수식	결과
공급가액	=SUMPRODUCT(C5:C14, D5:D14)	155,000
부가세(10%)	=ROUND(155000*0.1, 0)	15,500
합계금액	=155000+15500	170,500

 

 
num_digits가 0이면 원 단위 정수로 반올림되고, 양수면 소수점 아래 자리까지, 음수면 소수점 왼쪽(십·백 단위)으로 반올림됩니다. 부가세에서 원 미만을 반올림하지 않고 버리는 관행이라면 ROUND 대신 =ROUNDDOWN(공급가액*0.1, 0)을 씁니다.
 

4. 처음부터 그리기 번거로우면 템플릿, ERP 견적과의 차이

표 테두리와 로고 자리까지 직접 그리기 번거로우면 마이크로소프트 Create의 무료 템플릿을 받아 고쳐 쓰면 됩니다. 송장(Invoice) 템플릿에는 합계와 세금을 자동으로 계산하는 수식이 들어 있어, 품목과 금액만 바꾸면 견적서로 쓸 수 있습니다. 순서는 다음과 같습니다.

1. create.microsoft.com의 템플릿에서 엑셀 송장(Invoice) 템플릿을 고릅니다.
2. 회사명·고객명·품목·수량·단가 등 필요한 항목을 입력합니다.
3. 템플릿에 내장된 수식이 합계와 세금을 자동으로 계산합니다.
4. PDF로 내보내 거래처에 보내거나 온라인 링크로 공유합니다.

 
마이크로소프트 Create의 기본 템플릿은 영문 송장 위주라, 항목 이름을 견적서에 맞게 '공급가액', '부가세'로 바꿔 고쳐 쓰면 됩니다. 한편 재고 차감, 발주와 세금계산서 발행, 거래처별 단가 관리까지 묶어야 한다면 엑셀보다 ERP의 견적 기능이 맞습니다. 품목이 적고 단건·비정기로 보내는 견적이면 엑셀 양식이 더 빠릅니다.
 

5. 엑셀 견적서 만들 때 오류·상황별 해결 11가지

 

1. 품목을 골랐는데 단가가 #N/A로 뜰 때

찾는 품목명이 품목 목록 맨 왼쪽 열에 없거나, 띄어쓰기·철자가 조금만 달라도 #N/A가 납니다. 품목명을 목록과 똑같이 맞추고 네 번째 인수를 0으로 두어 정확히 일치를 쓰며, 빈 줄까지 조회해 오류가 보이는 게 싫으면 =IFERROR(VLOOKUP(...), "")로 감싸 빈칸으로 표시합니다.
 

2. 단가는 목록에 있는데 엉뚱한 값이 들어올 때

네 번째 인수(range_lookup)를 생략하면 근사값(TRUE)으로 찾아 비슷한 다른 품목의 단가가 들어올 수 있습니다. 수식 끝에 0(FALSE)을 넣어 정확히 일치로 바꿉니다.
 

3. 품목 줄을 늘렸더니 아래쪽이 조회가 안 될 때

table_array를 $A$2:$B$20처럼 절대참조로 고정하지 않으면 수식을 아래로 채울 때 범위가 같이 밀려 내려갑니다. 범위에 $를 붙여 고정하고, 품목이 20개를 넘으면 범위도 넉넉히 늘립니다.
 

4. 합계가 0으로 나오거나 더해지지 않을 때

단가나 금액 열이 숫자가 아니라 텍스트로 들어가면 SUM·SUMPRODUCT가 0을 냅니다. 셀 서식을 숫자로 바꾸고, 셀 왼쪽 위에 초록 삼각형(텍스트로 저장된 숫자) 표시가 있으면 숫자로 변환합니다.
 

5. SUMPRODUCT가 #VALUE!로 나올 때

SUMPRODUCT는 곱할 범위들의 크기가 같아야 합니다. =SUMPRODUCT(C5:C14, D5:D15)처럼 행 수가 다르면 #VALUE! 오류가 납니다. 두 범위를 같은 행(C5:C14, D5:D14)으로 맞춥니다.
 

6. 부가세에 소수점 원이 붙을 때

공급가액이 딱 떨어지지 않으면 10%를 곱한 부가세에 원 미만 소수가 생깁니다. =ROUND(공급가액*0.1, 0)으로 원 단위 반올림하고, 원 미만을 버리는 관행이면 =ROUNDDOWN(공급가액*0.1, 0)을 씁니다.
 

7. 면세라 부가세를 빼야 할 때

세율을 수식에 직접 쓰지 말고 셀 하나(예: G1)에 세율을 넣어 =ROUND(공급가액*G1, 0)으로 참조하면, 면세 건은 G1만 0%로 바꾸면 됩니다. 과세·면세 견적을 같은 양식으로 돌려쓸 수 있습니다.
 

8. 다른 시트의 품목 목록을 가져올 때

table_array 앞에 시트 이름과 느낌표를 붙입니다(품목!$A$2:$B$20). 시트 이름에 띄어쓰기가 있으면 '품목 목록'!$A$2:$B$20처럼 작은따옴표로 감쌉니다.
 

9. 금액을 천 단위 콤마로 보이게 하고 싶을 때

수식이 아니라 표시 형식 문제입니다. 금액 범위를 선택하고 쉼표 스타일(1,000 단위 구분 기호)을 적용하면 155000이 155,000으로 보입니다. 셀 값 자체는 그대로라 계산에는 영향을 주지 않습니다.
 

10. 견적서를 PDF로 보낼 때 표가 잘려 나올 때

인쇄할 범위를 선택해 인쇄 영역을 설정하고, 페이지 레이아웃에서 한 페이지에 맞추기로 조정한 뒤 '다른 이름으로 저장'에서 PDF로 내보냅니다.
 

11. 엑셀 견적과 ERP 견적 중 무엇을 쓸지 고민될 때

품목이 적고 단건·비정기로 보내는 견적이면 엑셀 양식이 빠릅니다. 재고 차감, 발주와 세금계산서 발행, 거래처별 단가 관리까지 묶어야 하면 ERP의 견적 기능이 맞습니다.
 
 
품목이 자주 바뀌고 종류가 많으면 품목 목록을 따로 두고 VLOOKUP으로 단가를 끌어오는 방식이, 가끔 보내는 단건 견적이면 마이크로소프트 Create 템플릿을 고쳐 쓰는 방식이 편합니다. 어느 쪽이든 공급가액과 부가세는 SUMPRODUCT·ROUND로 계산하고, 수식을 채운 뒤에는 결과를 한 번 검산해 금액이 맞는지 확인하는 것이 안전합니다.
 

참고 자료

• VLOOKUP function - Microsoft Support
• ROUND function - Microsoft Support
• SUMPRODUCT function - Microsoft Support
• Free invoice templates in Excel - Microsoft Create

 
※ 더 보기
엑셀 VLOOKUP 오류 해결 완벽 가이드: #N/A, #VALUE! 원인과 IFERROR 함수 활용법
엑셀 곱하기 수식 함수 SUMPRODUCT, PRODUCT, MMULT