품명과 규격을 입력하면 자동으로 단가가 구해지도록 견적서 만들기(VLOOKUP 이용)

안녕하세요.

 

견적서 양식을 만들 때 품명과 규격 같은 일부 필드를 입력하면 자동으로 나머지가 구해지도록 수식을 작성해 두면 사용하기 편리합니다.

이렇게 사용하도록 만들어진 함수가 VLOOKUP 함수입니다.

그런데 어떤 경우에는 VLOOKUP 함수를 쓸 수 없는 경우가 있습니다.

VLOOKUP 함수의 한계 때문인데 찾는 값이 한 개의 필드에 있는 것이 아니라 여러 개 필드를 조합해서 찾아야 하는 경우입니다.

 

실습 파일 다운로드

실습파일.xlsx

0.01MB

 

완성 파일 다운로드

완성파일.xlsx

0.02MB

 

견적서 시트에는 양식이 작성되어 있습니다.

 

단가표 시트에는 품명, 규격, 단가가 입력되어 있습니다.

 

단가표를 보면 품명이나 규격만으로 상품을 구분할 수 없고, 품명과 규격을 합쳐 하나의 상품을 구분할 수 있도록 만들어져 있습니다.

 

이 경우에는 VLOOKUP 함수를 쓸 수 없습니다.

대신 쓸 수 있는 함수는 INDEX 함수와 MATCH 함수를 결합해서 쓰는 것인데, 익숙한 VLOOKUP 함수에 비해 어렵게 느껴집니다.

그래서 함수는 꼭 익숙한 VLOOKUP 함수를 쓰기로 결정했습니다.

대신에 단가표를 약간 수정해서 VLOOKUP 함수를 쓸 수 있도록 만듭니다.

 

단가표 시트에서 D열을 선택하고 마우스 오른쪽 클릭 [삽입]을 선택해 새 열을 삽입합니다.

 

필드명은 뭐라도 상관없어 '찾기'라고 적고, 아래 수식을 작성합니다.

=B3 & C3

 

아래로 채우기합니다.

 

VLOOKUP 함수에서 찾는 값으로 사용할 필드를 만들었습니다.

 

견적서 시트 [G12] 셀에 수식을 작성합니다.

 

검색할_값 인수에 B12 & D12 를 입력해서 품명과 규격을 합칩니다.

=VLOOKUP(B12 & D12,단가표!$D$3:$E$25,2,0)

 

단가가 구해졌습니다.

 

이 수식을 아래로 채우기 하면 품명과 규격이 입력되지 않은 행에서는 오류 메시지가 표시됩니다.

 

오류 메시지가 표시되지 않도록 IFERROR 함수를 씁니다.

=IFERROR(VLOOKUP(B12 & D12,단가표!$D$3:$E$25,2,0),"")

 

공급가액을 구하는 수식도 작성합니다.

=IFERROR(G12/1.1*E12,"")

 

세액을 구하는 수식도 작성합니다.

=IFERROR(G12*E12-H12,"")

 

 

작성한 수식을 아래로 채우기합니다.

 

견적서를 완성했습니다.

 

새 열을 하나 추가한 것으로 익숙한 VLOOKUP 함수를 써서 완성할 수 있었습니다.

 

필요하신 분께 도움이 되길 바랍니다.