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

 

 

 

연습 파일 

견적서.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 함수의 Vlaue_if_error 인수입니다. 
단가는 수량과 곱해서 공급가액을 구할 참조셀입니다. 
이때 에러 대신 "" 으로 결과를 구하면 공급가액에서 #VALUE 오류가 생깁니다. 
""(공백)은 문자기 때문에 계산이 안되는 데이터죠.
""(공백) 대신 Value_if_error 인수는 0을 입력합니다. 

 

 

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

 

 

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

 

 

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

 

 

 

  1. 최부림 2019.05.10 11:50

    이름상자의 이름을 지울경우 어떻게 해야 합니까?

    • [수식] → [이름 관리자]를 선택하면 이름 관리자 대화상자가 실행됩니다.
      삭제할 이름을 선택하고 [삭제] 버튼 누르면 됩니다.

  2. 이영진 2019.06.08 06:01

    안녕하세요

    질문이 있습니다.

    엑에 이런 함수가 있나요?



    예)

    일번시트

    물품명 / 규격 / 단가 /

    라면1 / 사리 / 900/

    .

    .

    .

    ---------------------

    이번시트에서

    물품명 /규격/ 단가 /

    라면1/ x / x /



    물품명을 입력 하면 규격과 단가 입력이 한번에 할수 있는 함수를 찾고 있습니다.

    ahazero@naver.com 으로 답변 부탁합니다.

+ Recent posts