01 / 03
Excel의 VLOOKUP을 사용하여 데이터와 대략 일치하는 항목 찾기
VLOOKUP 함수 작동 방법
수직 조회 를 나타내는 Excel의 VLOOKUP 함수를 사용하여 데이터 또는 데이터베이스 테이블에있는 특정 정보를 조회 할 수 있습니다.
VLOOKUP은 일반적으로 단일 출력 필드를 출력으로 반환합니다. 그것이 어떻게 하는가 :
- 원하는 데이터를 찾을 데이터 테이블의 행 또는 레코드 를 VLOOKUP에 알려주는 이름 또는 lookup_value 를 제공합니다
- 원하는 데이터의 열 번호 ( col_index_num) 를 제공합니다.
- 이 함수는 데이터 테이블의 첫 번째 열에서 lookup_value를 찾습니다 .
- 그런 다음 VLOOKUP은 제공된 열 번호를 사용하여 동일한 레코드의 다른 필드에서 찾는 정보를 찾아서 반환합니다.
먼저 데이터 정렬하기
항상 필요한 것은 아니지만 일반적으로 VLOOKUP이 정렬 키 범위의 첫 번째 열을 사용하여 오름차순으로 검색하는 데이터 범위 를 먼저 정렬 하는 것이 가장 좋습니다.
데이터가 정렬되지 않으면 VLOOKUP이 잘못된 결과를 반환 할 수 있습니다.
VLOOKUP 함수의 구문 및 인수
함수의 구문 은 함수의 레이아웃을 참조하며 함수의 이름, 대괄호 및 인수를 포함 합니다.
VLOOKUP 함수의 구문은 다음과 같습니다.
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
조회 _value - (필수) 검색 할 값 - 위의 이미지에서 판매 된 수량 등
table_array - (필수) VLOOKUP이 사용자가 검색 한 정보를 찾기 위해 검색하는 데이터 테이블입니다.
- table_array에는 적어도 두 개의 데이터 열이 포함되어야합니다.
- 첫 번째 열은 일반적으로 lookup_value
col_index_num - 발견 할 값의 열 번호입니다 (필수).
- 번호 매기기는 search_key 열에서 열 1로 시작됩니다.
- col_index_num 이 table_array 인수에서 선택된 열의 수보다 큰 수로 설정된 경우 #REF! 함수에 의해 오류가 반환됩니다.
range_lookup - (선택 사항) 범위 가 오름차순으로 정렬되는지 여부를 나타냅니다.
- 첫 번째 열의 데이터가 정렬 키로 사용됩니다.
- 부울 값 - TRUE 또는 FALSE 만 허용되는 값입니다.
- 생략하면 값은 기본적으로 TRUE로 설정됩니다.
- TRUE 또는 생략하고 범위의 첫 번째 열을 오름차순으로 정렬하지 않으면 잘못된 결과가 발생할 수 있습니다.
- TRUE로 설정하거나 생략하고 _value 조회에 대한 정확히 일치하는 항목이 없으면 크기 또는 값이 가장 작은 가장 가까운 일치 항목이 search_key 로 사용됩니다
- FALSE로 설정된 경우 VLOOKUP은 _value 조회 와 정확히 일치하는 것을 허용합니다. 일치하는 값이 여러 개인 경우 첫 번째 일치하는 값이 반환됩니다.
- FALSE로 설정되고 search_key 와 일치하는 값이없는 경우 함수에서 # N / A 오류가 반환됩니다.
예 : 구입 수량에 대한 할인율 찾기
위 그림의 예는 VLOOKUP 함수를 사용하여 구입 한 항목의 수량에 따라 할인율을 찾습니다.
이 예에서는 19 개 품목 구매에 대한 할인이 2 %임을 보여줍니다. 이는 수량 열에 값의 범위가 포함되어 있기 때문입니다. 따라서 VLOOKUP은 정확한 일치 항목을 찾을 수 없습니다. 대신 올바른 할인율을 반환하려면 대략적인 일치를 찾아야합니다.
대략적인 일치 항목을 찾으려면 다음을 수행하십시오.
- table_array 의 데이터를 오름차순으로 정렬합니다.
- range_lookup 인수를 TRUE로 설정하십시오.
이 예에서 VLOOKUP 함수가 포함 된 다음 수식을 사용하여 구입 한 제품 수량에 대한 할인을 찾습니다.
= VLOOKUP (C2, $ C $ 5 : $ D $ 8,2, TRUE)
이 수식을 워크 시트 셀에 입력 할 수 있지만 아래에 나와있는 단계에서 사용되는 다른 옵션은 함수의 대화 상자를 사용하여 인수를 입력하는 것입니다.
- 대화 상자를 사용하면 함수의 인수를 올바르게 입력하는 것이 더 쉽습니다.
VLOOKUP 대화 상자 열기
위 이미지에 표시된 VLOOKUP 함수를 셀 B2에 입력하는 단계는 다음과 같습니다.
- B2 셀을 클릭하여 VLOOKUP 함수의 결과가 표시되는 활성 셀 - 위치로 만듭니다.
- 수식 탭을 클릭하십시오.
- 리본 에서 찾아보기 및 참조 를 선택하여 기능 드롭 다운 목록을 엽니 다.
- 목록에서 VLOOKUP 을 클릭하여 함수의 대화 상자를 불러옵니다.
02 of 03
Excel의 VLOOKUP 함수의 인수 입력
셀 참조 가리키기
VLOOKUP 함수의 인수는 위의 그림과 같이 대화 상자의 별도 행에 입력됩니다.
인수로 사용할 셀 참조를 올바른 행에 입력하거나 아래 단계에서와 같이 마우스 포인터로 원하는 셀 범위를 강조 표시하는 포인팅을 사용하여 대화 상자에 입력 할 수 있습니다 .
포인팅을 사용하면 다음과 같은 이점이 있습니다.
- 타이핑보다 빠릅니다.
- 올바른 셀 참조를 입력하는 실수가 거의 없습니다.
인수로 상대 및 절대 셀 참조 사용
VLOOKUP 사본을 여러 개 사용하여 동일한 데이터 테이블에서 다른 정보를 반환하는 경우는 드뭅니다. 이를 쉽게 수행하기 위해 종종 VLOOKUP을 한 셀에서 다른 셀로 복사 할 수 있습니다. 함수가 다른 셀에 복사되면 함수의 새 위치에서 결과 셀 참조가 올바른지 확인해야합니다.
위의 이미지에서 달러 기호 ( $ )는 table_array 인수에 대한 셀 참조를 둘러싸고있어 절대 셀 참조 임을 나타내므로 함수가 다른 셀로 복사되면 변경되지 않습니다. 이것은 VLOOKUP의 여러 복사본이 모두 정보 소스와 동일한 데이터 테이블을 참조하기 때문에 바람직합니다.
반면에 lookup_value에 사용 된 셀 참조는 달러 기호로 묶여 있지 않으므로 상대 셀 참조가됩니다. 상대 셀 참조는 참조 할 데이터의 위치를 기준으로 새로운 위치를 반영하도록 복사 될 때 변경됩니다.
함수 인수 입력하기
- VLOOKUP 대화 상자에서 Lookup _value 행을 클릭하십시오.
- 워크 시트에서 C2 셀을 클릭하여이 셀 참조를 search_key 인수로 입력하십시오.
- 대화 상자의 Table_array 행을 클릭하십시오.
- 워크 시트의 C5에서 D8 셀을 강조 표시하여이 범위 를 Table_array 인수로 입력하십시오. 표 표제는 포함되지 않습니다.
- 키보드의 F4 키를 눌러 범위를 절대 셀 참조로 변경하십시오.
- 대화 상자의 Col_index_num 행을 클릭하십시오.
- 할인율이 Table_array 인수의 2 열에 있으므로 Col_index_num 인수 로이 행에 2를 입력하십시오.
- 대화 상자의 Range_lookup 행을 클릭하십시오.
- Range_lookup 인수로 True 를 입력하십시오.
- 대화 상자를 닫고 워크 시트로 돌아가려면 키보드의 Enter 키를 누릅니다.
- 워크 시트의 D2 셀에 응답 2 % (구매 한 수량의 할인율)가 표시되어야합니다.
- D2 셀을 클릭하면 전체 함수 = VLOOKUP (C2, $ C $ 5 : $ D $ 8,2, TRUE)이 워크 시트 위의 수식 입력 줄에 나타납니다
VLOOKUP이 결과로 2 %를 반환 한 이유
- 이 예에서 Quantity 열은 search_key 값 19와 정확히 일치하지 않습니다.
- is_sorted 인수가 TRUE로 설정되어 있으므로 VLOOKUP은 search_key 값과 대략 일치하는 항목을 찾습니다.
- 19의 search_key 값보다 여전히 작은 가장 가까운 값은 11입니다.
- 따라서 VLOOKUP은 11이 들어있는 행에서 할인율을 찾아 결과적으로 2 %의 할인율을 반환합니다.
03 of 03
Excel VLOOKUP가 작동하지 않음 : # N / A 및 #REF 오류
VLOOKUP 오류 메시지
다음 오류 메시지는 VLOOKUP과 관련되어 있습니다.
A # N / A ( "값을 사용할 수 없음") 오류가 표시되는 경우 :
- _value 조회 가 range 인수의 첫 번째 열에 없습니다.
- Table_array 인수가 정확하지 않습니다. 예를 들어 인수에는 범위의 왼쪽에 빈 열이 포함될 수 있습니다.
- Range_lookup 인수가 FALSE로 설정되어 있으며 range 의 첫 번째 열에서 search_key 인수와 정확히 일치하는 항목을 찾을 수 없습니다.
- Range_lookup 인수가 TRUE로 설정되고 범위 의 첫 번째 열에있는 모든 값이 search_key 보다 큽니다.
#REF! ( "범위를 벗어난 참조") 오류가 표시되는 경우 :
- Col_index_num 인수가 Table_array 의 열 수보다 큽니다.