엑셀 실무 109강] VLOOKUP 함수를 활용한 편리한 견적서 만들기

전 강좌에서 만든 견적서를 조금 더 편리하게 만들어 사용해 보겠습니다.
아래 그림을 보세요.
견적서 양식이 있는 시트와 단가표 시트가 있습니다.
견적서를 작성할 때 목록에서 품명을 선택하면 단가표에서 자동으로 규격과 단가를 찾아서 견적서에 입력되도록 수식을 작성하려고 합니다.

 

 

 

연습 파일 다운로드

견적서.xlsx

0.01MB

 

이름 정의하기

 

다른 시트에 있는 셀이나 범위를 수식이나 또는 유효성 검사 원본으로 사용할 때 미리 범위를 이름으로 정의해두고 사용하면 편리합니다.

1. 단가표 시트 [B3:D25] 셀 범위를 선택하고 이름 상자에 단가표라고 입력하고 <Enter>를 누릅니다.

2. 같은 방법으로 [B3:B25] 셀 범위를 선택하고 품명이라고 이름 상자에 입력하고 <Enter>를 누릅니다.

 

[이름 상자 ▼]를 누르면 방금 이름으로 정의한 단가표와 품명을 확인할 수 있습니다.

 

 

데이터 유효성 검사

 

이름을 정의했다면 본격적으로 편리한 견적서를 만들어 볼까요?
품명 범위를 유효성 검사 목록을 설정하여 직접 품명을 입력하는 것이 아니라 목록에서 선택할 수 있도록 설정하겠습니다.

1. [B12:B21] 셀 범위를 선택하고 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사 ▼]를 선택합니다.

 

 

2. [데이터 유효성] 대화상자가 열리면 [설정] 탭에서 [제한 대상] → [목록]을 선택합니다.

 

 

3. [원본] 입력창에 커서를 두고 =품명 이라고 입력하고 [확인] 버튼을 클릭합니다.
품명은 단가표 시트에서 품명 범위[B3:B25]를 이름으로 정의한 이름입니다.

 

 

견적서에서 품명 셀 [B12] 선택합니다.
목록 버튼이 생겼죠? 목록 버튼을 눌러보세요.
품명이 아래로 쭉 보여줍니다.

 

 

VLOOKUP 함수를 사용하여 규격과 단가 찾아오기

 

이제 품명을 목록에서 선택하면 규격과 단가가 자동으로 입력되도록 VLOOKUP 함수를 사용하여 수식을 작성해보겠습니다.
먼저 규격 수식부터 작성해보죠.

1. VLOOKUP()을 입력하고 함수 삽입 버튼[fx]를 클릭합니다.

2. VLOOKUP 함수 삽입 대화상자가 열리면 첫 번째 인수 Lookup_value를 커서를 두고 [B12] 셀을 선택합니다.

3. Table_array 인수 입력창에 단가표 범위[B3:D25]를 이름으로 정의해둔 단가표라고 입력하고

4. Col_index_num 인수 입력창에 2를 입력합니다. 단가표 범위에서 규격은 두 번째 열에 있습니다.

5. Range_lookup 인수 입력창에 0을 입력합니다. (정확하게 일치하는 값을 찾을 경우 Range_lookup 인수는 false 즉, 0을 대신 사용합니다. Lookup_value 인수가 문자인 경우 Range_lookup 인수는 false가 됩니다.)

:: VLOOKUP 함수 기본 익히기 강좌를 참고하세요. http://naver.me/xHC5ptQJ

 

Table_array 인수로 지정된 범위는 첫 번째 열이 Col_index_num 1, 그 다음번째 열이 2, 3 순서로 정해집니다.

 

PC 1의 규격이 제대로 구해졌나요?
[D12] 셀에 작성한 수식을 [D21] 셀 까지 채우기(복사)해 봅시다.
헉~
수식을 복사한 셀에 #N/A 오류가 표시되네요?

VLOOKUP 함수는 Lookup_value 인수의 값이 없으면 #N/A 오류를 표시합니다.
규격 범위에 수식을 미리 넣어두고 품명을 선택하면 규격이 자동으로 입력되고
품명이 입력되지 않아도 규격 범위에 오류가 나타나지 않게 하려면 IFERROR 함수를 사용하면 됩니다.
IFERROR 함수는 #N/A와 같이 엑셀에서 표시되는 오류를 다른 값으로 대체해서 표시할 수 있도록 하는 함수입니다.

 

 

IFERROR 함수를 사용하여 오류 나타나지 않도록 설정하기

 

다시 규격을 구해보겠습니다.

1. [D12] 셀을 선택하고 =IFERROR()를 입력하고 함수 삽입 버튼[fx]을 클릭합니다.

2. Value 인수 입력창에 VLOOKUP() 이라고 입력하고 수식 입력줄에서 VLOOKUP 함수를 선택합니다.

 

함수 삽입 대화상자가 IFERROR에서 VLOOKUP으로 바뀝니다.

3. Lookup_value를 커서를 두고 [B12] 셀을 선택합니다.

4. Table_array 인수 입력창에 단가표 범위[B3:D25]를 이름으로 정의해둔 단가표라고 입력하고

5. Col_index_num 인수 입력창에 2를 입력합니다.

6. Range_lookup 인수 입력창에 0을 입력합니다.

7. 다시 수식 입력줄에서 IFERROR 함수를 선택합니다.

 

 

함수 삽입 대화상자가 IFERROR 함수를 바뀝니다. (함수를 중첩할 때 함수 삽입 대화 상자 이동은 지금과 같이 하면 됩니다.)
8. Value_if_error 인수에 ""을 입력하고 [확인] 버튼을 클릭합니다.
""는 공백을 의미합니다. 에러 대신 공백을 표시하라는 겁니다.

 

 

같은 방법으로 IFERROR 함수를 사용하여 오류 없이 단가를 구해봅시다.

 

 

단가를 구하는 수식에서 신경 써야 할 부분은 IFERROR 함수의 Value_if_error 인수입니다.
단가는 수량과 곱해서 공급가액을 구할 참조셀입니다.
이때 에러 대신 "" 으로 결과를 구하면 공급가액에서 #VALUE 오류가 생깁니다.
""(공백)은 문자이기 때문에 계산이 안되는 데이터입니다.
""(공백) 대신 Value_if_error 인수는 0을 입력합니다.

 

 

단가, 공급가액, 세액에 표시된 -(하이픈)은 0이 회계 표시 형식으로 적용된 경우입니다.
-(하이픈) 대신 공백으로 표시해서 견적서 양식을 깔끔하게 나타내고 싶다면 범위를 선택하고 단축키 <Ctrl + 1>을 눌러 [셀 서식] 대화상자를 엽니다.
[표시 형식] → [사용자 지정] 범주를 선택하고 [형식] 입력창에 #,### 으로 입력합니다.
#은 0을 공백으로 표시합니다.

 

 

자~ 이제 완성했습니다.
품명을 선택해보세요.
규격과 단가가 자동으로 입력됩니다.
수량을 입력해보세요.
공급가액과 세액을 구하는 수식을 미리 작성해 두었기 때문에 자동으로 구해집니다.

 

 

영상 강좌도 준비했습니다.
위 설명으로 부족하다면 영상강좌 참고하세요~