Excel에서 VLOOKUP을 사용하는 방법

VLOOKUP은 Excel의 가장 유용한 기능 중 하나이며 가장 잘 이해되지 않는 기능이기도합니다. 이 기사에서는 실제 예제를 통해 VLOOKUP을 설명합니다. 가상 회사에 사용할 수있는 송장 템플릿 을 만들 것 입니다.

VLOOKUP은 Excel 함수 입니다. 이 기사에서는 독자가 이미 Excel 기능을 이해하고 있고 SUM, AVERAGE 및 TODAY와 같은 기본 기능을 사용할 수 있다고 가정합니다. 가장 일반적으로 사용되는 VLOOKUP은 데이터베이스 함수입니다. 즉, 데이터베이스 테이블 또는 간단히 Excel 워크 시트의 항목 목록 과 함께 작동합니다 . 무슨 일이야? 음, 어떤 물건의 종류. 직원, 제품, 고객 또는 CD 컬렉션의 CD 또는 밤하늘의 별 목록이 포함 된 워크 시트가있을 수 있습니다. 정말 중요하지 않습니다.

다음은 목록 또는 데이터베이스의 예입니다. 이 경우 가상 회사에서 판매하는 제품 목록입니다.

일반적으로 이와 같은 목록에는 목록의 각 항목에 대해 일종의 고유 식별자가 있습니다. 이 경우 고유 식별자는 "항목 코드"열에 있습니다. 참고 : VLOOKUP 함수가 데이터베이스 / 목록과 함께 작동하려면 해당 목록에 고유 식별자 (또는 "키"또는 "ID") 가 포함 된 열이 있어야하며 해당 열은 테이블의 첫 번째 열이어야합니다 . 위의 샘플 데이터베이스는이 기준을 충족합니다.

VLOOKUP 사용의 가장 어려운 부분은 정확히 무엇을위한 것인지 이해하는 것입니다. 그래서 우리가 먼저 그것을 명확히 할 수 있는지 봅시다 :

VLOOKUP은 제공된 고유 식별자 인스턴스를 기반으로 데이터베이스 / 목록에서 정보를 검색합니다.

위의 예에서 VLOOKUP 함수를 항목 코드와 함께 다른 스프레드 시트에 삽입하면 원본에 설명 된대로 해당 항목의 설명, 가격 또는 가용성 ( "재고"수량)이 반환됩니다. 명부. 다음 중 어떤 정보가 다시 전달됩니까? 음, 공식을 만들 때 이것을 결정해야합니다.

데이터베이스의 정보 하나만 필요한 경우 VLOOKUP 함수가 포함 된 수식을 구성하는 데 많은 어려움이 있습니다. 일반적으로 템플릿과 같은 재사용 가능한 스프레드 시트에서 이러한 종류의 기능을 사용합니다. 누군가 유효한 품목 코드를 입력 할 때마다 시스템은 해당 품목에 대해 필요한 모든 정보를 검색합니다.

이에 대한 예를 만들어 보겠습니다 . 가상의 회사에서 계속 재사용 할 수 있는 송장 템플릿 입니다.

먼저 Excel을 시작하고 빈 송장을 만듭니다.

이것이 작동하는 방법입니다. 송장 템플릿을 사용하는 사람은 "A"열에 일련의 품목 코드를 입력하고 시스템은 제품 데이터베이스에서 각 품목의 설명과 가격을 검색합니다. 해당 정보는 각 항목의 라인 합계를 계산하는 데 사용됩니다 (유효한 수량을 입력했다고 가정).

이 예제를 간단하게 유지하기 위해 동일한 통합 문서의 별도 시트에서 제품 데이터베이스를 찾습니다.

실제로는 제품 데이터베이스가 별도의 통합 문서에있을 가능성이 더 높습니다. 데이터베이스가 동일한 시트, 다른 시트 또는 완전히 다른 통합 문서에 있는지 여부는 실제로 중요하지 않은 VLOOKUP 함수와 거의 차이가 없습니다.

그래서 우리는 다음과 같은 제품 데이터베이스를 만들었습니다.

작성하려는 VLOOKUP 수식을 테스트하기 위해 먼저 빈 송장의 A11 셀에 유효한 항목 코드를 입력합니다.

다음으로, VLOOKUP에 의해 데이터베이스에서 검색된 정보를 저장할 셀로 활성 셀을 이동합니다. 흥미롭게도 이것은 대부분의 사람들이 잘못하는 단계입니다. 추가 설명 : A11 셀의 항목 코드에 해당하는 설명을 검색하는 VLOOKUP 수식을 만들려고합니다. 이 설명을 얻을 때 어디에 넣을까요? 물론 B11 셀에서. 이것이 바로 B11 셀에 VLOOKUP 수식을 작성하는 곳입니다. 지금 셀 B11을 선택하십시오.

VLOOKUP을 선택하고 수식을 완성하는 데 도움을받을 수 있도록 Excel에서 제공해야하는 사용 가능한 모든 함수 목록을 찾아야합니다. 먼저 수식 탭을 클릭 한 다음 함수 삽입 을 클릭 하면 찾을 수 있습니다 .

Excel에서 사용할 수있는 기능을 선택할 수있는 상자가 나타납니다.

찾고있는 것을 찾으려면 "lookup"과 같은 검색어를 입력 할 수 있습니다 (관심있는 기능 이 조회 기능 이기 때문입니다 ). 시스템은 Excel의 모든 조회 관련 함수 목록을 반환합니다.  VLOOKUP 은 목록에서 두 번째입니다. 그것을 선택하고 확인을 클릭 합니다.

함수 인수는 모두 우리를 묻는 나타납니다 상자 인수 (또는 매개 변수 VLOOKUP 함수를 완료하기 위해 필요). 이 상자를 다음과 같은 질문을하는 기능으로 생각할 수 있습니다.

  1. 데이터베이스에서 어떤 고유 식별자를 찾고 있습니까?
  2. 데이터베이스는 어디에 있습니까?
  3. 고유 식별자와 관련된 데이터베이스에서 어떤 정보를 검색하고 싶습니까?

처음 세 개의 인수는 굵게 표시되어 필수 인수 임을 나타냅니다 (VLOOKUP 함수는 인수가 없으면 불완전하며 유효한 값을 반환하지 않습니다). 네 번째 인수는 굵게 표시되지 않으므로 선택 사항입니다.

우리는 위에서 아래로 순서대로 인수를 완료 할 것입니다.

완료해야하는 첫 번째 인수는 Lookup_value 인수입니다. 함수 는 설명을 반환해야하는 고유 식별자 ( 이 경우 항목 코드) 를 찾을 위치를 알려야합니다 . 앞서 입력 한 품목 코드 (A11)를 선택해야합니다.

첫 번째 인수 오른쪽에있는 선택기 아이콘을 클릭합니다.

그런 다음 품목 코드 (A11)가 포함 된 셀을 한 번 클릭하고 Enter 키를 누릅니다 .

"A11"값이 첫 번째 인수에 삽입됩니다.

이제 Table_array 인수에 대한 값을 입력해야합니다 . 즉, 데이터베이스 / 목록을 찾을 위치를 VLOOKUP에 알려야합니다. 두 번째 인수 옆에있는 선택기 아이콘을 클릭합니다.

이제 데이터베이스 / 목록을 찾고 헤더 행을 제외한 전체 목록을 선택합니다. 이 예에서 데이터베이스는 별도의 워크 시트에 있으므로 먼저 해당 워크 시트 탭을 클릭합니다.

다음으로 헤더 행을 제외하고 전체 데이터베이스를 선택합니다.

… Enter 키를 누릅니다 . 데이터베이스를 나타내는 셀 범위 (이 경우 " '제품 데이터베이스'! A2 : D7")는 두 번째 인수에 자동으로 입력됩니다.

이제 세 번째 인수 인 Col_index_num 을 입력해야합니다 . 이 인수를 사용하여 데이터베이스의 어떤 정보가 A11의 항목 코드와 연결되는지 VLOOKUP에 지정합니다. 이 특정 예에서 항목의 설명이 반환 되기를 원합니다 . 데이터베이스 워크 시트를 보면 "Description"열이 데이터베이스 의 두 번째 열 임을 알 수 있습니다. 즉, Col_index_num 상자 에 "2"값을 입력해야합니다 .

"설명"열이 해당 워크 시트 의 B 열에 있기 때문에 여기에 "2"를 입력하지 않는다는 점에 유의해야합니다 . 데이터베이스 가 워크 시트의 K 열에서 시작된 경우 "Description"열이 "Table_array"를 지정할 때 선택한 셀 집합의 두 번째 열이기 때문에이 필드에 "2"를 입력합니다.

마지막으로 최종 VLOOKUP 인수 인 Range_lookup에 값을 입력할지 여부를 결정해야합니다 . 이 인수에는 true 또는 false 값 이 필요하거나 비워 두어야합니다. 데이터베이스와 함께 VLOOKUP을 사용할 때 (정말 90 %)이 인수에 넣을 항목을 결정하는 방법은 다음과 같이 생각할 수 있습니다.

데이터베이스의 첫 번째 열 (고유 식별자가 포함 된 열)이 알파벳순 / 숫자 순으로 오름차순으로 정렬 된 경우이 인수 에 true 값을 입력 하거나 비워 둘 수 있습니다.

데이터베이스의 첫 번째 열이 정렬 되지 않았 거나 내림차순으로 정렬 된 경우이 인수 에 false 값을 입력 해야 합니다.

데이터베이스의 첫 번째 열이 정렬 되지 않았으므로 다음 인수에 false 를 입력합니다 .

그게 다야! 필요한 값을 반환하기 위해 VLOOKUP에 필요한 모든 정보를 입력했습니다. 확인 버튼을 클릭하고 항목 코드 "R99245"에 해당하는 설명이 셀 B11에 올바르게 입력되었는지 확인합니다.

우리를 위해 만들어진 공식은 다음과 같습니다.

A11 셀에 다른 항목 코드를 입력 하면 VLOOKUP 함수의 기능이 표시되기 시작합니다. 설명 셀이 새 항목 코드와 일치하도록 변경됩니다.

비슷한 단계를 수행하여 항목의 가격 을 셀 E11로 반환 할 수 있습니다. 새 수식은 E11 셀에 만들어야합니다. 결과는 다음과 같습니다.

… 그리고 공식은 다음과 같습니다.

두 수식의 유일한 차이점은 세 번째 인수 ( Col_index_num )가 "2"에서 "3"으로 변경 되었다는 것입니다 (데이터베이스의 세 번째 열에서 데이터를 검색하기를 원하기 때문).

이러한 항목 중 2 개를 구입하기로 결정한 경우 D11 셀에 "2"를 입력합니다. 그런 다음 F11 셀에 간단한 수식을 입력하여 라인 합계를 얻습니다.

= D11 * E1

… 이것은 이렇게 생겼습니다…

송장 템플릿 완료

지금까지 VLOOKUP에 대해 많은 것을 배웠습니다. 사실, 우리는이 기사에서 배울 모든 것을 배웠습니다. VLOOKUP은 데이터베이스 외에 다른 상황에서도 사용할 수 있다는 점에 유의해야합니다. 이것은 덜 일반적이며 향후 How-To Geek 기사에서 다룰 수 있습니다.

인보이스 템플릿이 아직 완성되지 않았습니다. 이를 완료하기 위해 다음을 수행합니다.

  1. A11 셀에서 샘플 항목 코드를 제거하고 D11 셀에서 "2"를 제거합니다. 이렇게하면 새로 생성 된 VLOOKUP 수식에 오류 메시지가 표시됩니다.



    Excel의 IF ()ISBLANK () 함수 를 적절히 사용하여이 문제를 해결할 수 있습니다 . 공식을 다음과 같이 변경합니다…      = VLOOKUP (A11, 'Product Database'! A2 : D7,2, FALSE) …이… = IF (ISBLANK (A11),””, VLOOKUP (A11, 'Product Database'! A2 : D7,2, FALSE))


  2. B11, E11 및 F11 셀의 수식을 송장의 나머지 항목 행에 복사합니다. 이렇게하면 결과 수식이 더 이상 데이터베이스 테이블을 올바르게 참조하지 않습니다. 데이터베이스에 대한 셀 참조 를 절대 셀 참조 로 변경하여이 문제를 해결할 수 있습니다. 또는 - 그리고 더 나은 - 우리가 만들 수있는 범위 이름을 (예 : "제품")을 전체 제품 데이터베이스, 셀 참조 대신이 범위 이름을 사용합니다. 공식은 다음과 같이 변경됩니다…      = IF (ISBLANK (A11),””, VLOOKUP (A11, 'Product Database'! A2 : D7,2, FALSE)) … 이것으로…       = IF (ISBLANK (A11),”” , VLOOKUP (A11, 제품, 2, FALSE)) ... 그리고 다음 공식을 나머지 송장 항목 행에 복사합니다.
  3. 우리는 아마 "잠금"세포 우리의 공식을 (또는 오히려 포함 할 것이라고 잠금을 해제 다른 세포), 다음 사람이 송장에 기입 할 때 우리의주의 깊게 구축 수식 실수로 덮어 쓰기되지 않도록하기 위해, 워크 시트를 보호합니다.
  4. 회사의 모든 사람이 재사용 할 수 있도록 파일을 템플릿 으로 저장합니다.

정말 영리 하다고 느끼면 다른 워크 시트에 모든 고객의 데이터베이스를 만든 다음 셀 F5에 입력 한 고객 ID를 사용하여 셀 B6, B7 및 B8에 고객의 이름과 주소를 자동으로 채 웁니다.

VLOOKUP으로 연습하거나 결과 청구서 템플릿을보고 싶다면 여기에서 다운로드 할 수 있습니다.