엑셀 데이터 조회함수

엑셀 조회함수 

 

• 데이터 범위에서 값 찾기 - VLOOKUP 및 HLOOKUP
• 테이블 또는 범위 내에서 값 또는 값에 대한 참조 가져오기 - INDEX
• 셀 범위에서 지정된 항목의 상대 위치 가져오기 - MATCH

 

VLOOKUP 함수 사용

VLOOKUP 함수의 구문은 다음과 같습니다.

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

 

 

lookup_value − 찾고자하는 값입니다. Lookup_value 값 또는 셀에 대한 참조일 수 있습니다. Lookup_value table_array에서 지정한 셀 범위의 첫 번째 열에 있어야 합니다.

table_array - VLOOKUP이 lookup_value 및 반환 값을 검색하는 셀의 범위입니다. table_array 다음을 포함해야 합니다.

• 첫 번째 열의 lookup_value
• 찾으려는 반환 값

- lookup_value 포함하는 첫 번째 열은 오름차순으로 정렬 할 수 있습니다. 그러나 결과는 이 열의 순서를 기반으로 합니다.

 

 

col_index_num - 반환 값을 포함하는 table_array의 열 번호입니다. 숫자는 table-array의 가장 왼쪽 열에 대해 1로 시작합니다

range_lookup - VLOOKUP이 정확한 일치 또는 대략적인 일치를 찾을 수 있는지 여부를 지정하는 선택적 논리 값입니다. range_lookup 될 수 있습니다

생략된 경우 TRUE로 간주되고 VLOOKUP은 대략적인 일치 항목을 찾으려고 합니다.

TRUE인 경우 VLOOKUP은 대략적인 일치 항목을 찾으려고 합니다. 즉, 정확히 일치하는 항목을 찾을 수 없으면 lookup_value보다 작은 다음으로 큰 값이 반환됩니다

 

FALSE(이 경우 VLOOKUP은 정확히 일치하는 항목을 찾으려고 합니다.)

1인 경우 TRUE로 간주되고 VLOOKUP은 대략적인 일치 항목을 찾으려고 합니다.

0인 경우 FALSE로 간주되고 VLOOKUP은 정확히 일치하는 항목을 찾으려고 합니다.

 

- range_lookup 생략되거나 TRUE 또는 1 인 경우, VLOOKUP은 table_array의 첫 번째 열이 오름차순으로 정렬 될 때만 올바르게 작동합니다. 그렇지 않으면 잘못된 값이 발생할 수 있습니다. 이 경우 range_lookup에 FALSE를 사용합니다.

 

range_lookup TRUE와 함께 VLOOKUP 함수 사용

학생 점수 목록을 고려하십시오. VLOOKUP을 사용하여 점수 간격 및 합격 범주가 포함된 배열에서 해당 성적을 얻을 수 있습니다.

 

table_array −

 

성적을 산출하는 기준이 되는 첫 번째 열 표시는 오름차순으로 정렬됩니다. 따라서 인수 range_lookup TRUE를 사용하면 필요한 대략적인 일치를 얻을 수 있습니다.

이 배열의 이름을 Grades로 지정합니다.

 

셀 범위를 기억할 필요가 없도록 이러한 방식으로 배열의 이름을 지정하는 것이 좋습니다. 지금, 당신은 다음과 같이 당신이 가지고있는 점수의 목록에 대한 등급을 조회 할 준비가되었습니다 -

 

 

col_index_num - table_array의 반환 값의 열이 2임을 나타냅니다.

range_lookup은 TRUE입니다.

table_array 성적의 조회 값을 포함하는 첫 번째 열은 오름차순입니다. 따라서 결과가 정확합니다.

근사 일치에 대한 반환 값도 얻을 수 있습니다. 즉 VLOOKUP은 다음과 같이 계산합니다 -

 

 

 

다음과 같은 결과를 얻을 것이다 -

 

range_lookup FALSE와 함께 VLOOKUP 함수 사용

각 제품의 제품 ID 및 가격이 포함된 제품 목록을 고려합니다. 제품 ID와 가격은 새 제품이 출시될 때마다 목록 끝에 추가됩니다. 즉, 제품 ID가 오름차순일 필요는 없습니다. 제품 목록은 아래와 같을 수 있습니다 -

 

table_array −

 

이 배열의 이름을 ProductInfo로 지정합니다.

제품 ID가 첫 번째 열에 있으므로 VLOOKUP 함수를 사용하여 제품 ID가 지정된 제품의 가격을 얻을 수 있습니다. 가격은 3열에 있으므로 num col_index_ 3이어야 합니다.

 

range_lookup와 함께 VLOOKUP 함수를 TRUE로 사용

range_lookup와 함께 VLOOKUP 함수를 FALSE로 사용

 

 

ProductInfo 배열에서 171.65입니다. 

 

range_lookup FALSE일 때의 올바른 결과와

range_lookup TRUE일 때 잘못된 결과입니다.

ProductInfo 배열의 첫 번째 열이 오름차순으로 정렬되지 않기 때문입니다. 따라서 데이터가 정렬되지 않을 때마다 FALSE를 사용해야 합니다.

 

HLOOKUP 함수 사용

데이터가 열이 아닌 행에 있는 경우 HLOOKUP 함수를 사용할 수 있습니다.

 

제품 정보를 예로 들어 보겠습니다. 배열이 다음과 같이 보인다고 가정 -

 

이 배열의 이름을 ProductRange로 지정합니다. HLOOKUP 함수를 사용하여 제품 ID가 지정된 제품의 가격을 찾을 수 있습니다.

HLOOKUP 함수의 구문은 다음과 같습니다.

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

 

 

lookup_value - 테이블의 첫 번째 행에서 찾을 수있는 값입니다

table_array - 데이터를 조회하는 정보의 테이블입니다

row_index_num - 일치하는 값이 반환됩니다 table_array 행 번호입니다

range_lookup - HLOOKUP이 정확한 일치 또는 대략적인 일치를 찾을 수 있도록 여부를 지정하는 논리적 값입니다

range_lookup 될 수 있습니다

 

생략된 경우 TRUE로 간주되고 HLOOKUP은 대략적인 일치 항목을 찾으려고 합니다.

TRUE인 경우 HLOOKUP은 대략적인 일치 항목을 찾으려고 합니다. 즉, 정확히 일치하는 항목을 찾을 수 없으면 lookup_value보다 작은 다음으로 큰 값이 반환됩니다

 

FALSE(이 경우 HLOOKUP은 정확히 일치하는 항목을 찾으려고 합니다.)

1 - 이 경우 TRUE로 간주되고 HLOOKUP은 대략적인 일치 항목을 찾으려고 합니다.

0인 경우 FALSE로 간주되고 HLOOKUP은 정확한 일치 항목을 찾으려고 합니다.

 

- range_lookup 생략 또는 TRUE 또는 1 인 경우, HLOOKUP은 table_array의 첫 번째 열이 오름차순으로 정렬 될 때만 올바르게 작동합니다. 그렇지 않으면 잘못된 값이 발생할 수 있습니다. 이 경우 range_lookup에 FALSE를 사용합니다.

 

range_lookup FALSE와 함께 HLOOKUP 함수 사용

제품 ID가 첫 번째 행에 있으므로 HLOOKUP 함수를 사용하여 제품 ID가 지정된 제품의 가격을 얻을 수 있습니다. 가격은 3행에 있으므로 num row_index_ 3이어야 합니다.

 

range_lookup 있는 HLOOKUP 함수를 TRUE로 사용합니다.

range_lookup와 함께 HLOOKUP 함수를 FALSE로 사용합니다.

ProductRange 배열의 정답은 171.65입니다. 결과를 확인할 수 있습니다.

 

VLOOKUP의 경우와 마찬가지로

range_lookup FALSE일 때의 올바른 결과와

range_lookup TRUE일 때 잘못된 결과입니다.

이는 ProductRange 배열의 첫 번째 행이 오름차순으로 정렬되지 않기 때문입니다. 따라서 데이터가 정렬되지 않을 때마다 FALSE를 사용해야 합니다.

 

range_lookup TRUE와 함께 HLOOKUP 함수 사용

VLOOKUP에 사용된 학생 점수의 예를 살펴보겠습니다. 아래 주어진 표에 표시된 열이 아닌 행에 데이터를 가정 -

table_array −

 

이 배열의 이름을 GradesRange로 지정합니다.

성적을 산출하는 기준이 되는 첫 번째 행 표시는 오름차순으로 정렬됩니다. 따라서 range_lookup 인수에 대해 TRUE와 함께 HLOOKUP을 사용하면 대략적인 일치로 등급을 얻을 수 있으며 이것이 필요한 것입니다.

 

 

row_index_num - table_array의 반환 값의 열이 2임을 나타냅니다.

range_lookup은 TRUE입니다.

table_array 성적의 조회 값을 포함하는 첫 번째 열은 오름차순입니다. 따라서 결과가 정확합니다.

근사 일치에 대한 반환 값도 얻을 수 있습니다. 즉 HLOOKUP은 다음과 같이 계산합니다 -

표시패스 카테고리

 

다음과 같은 결과를 얻을 것이다 -

 

INDEX 함수 사용

데이터 배열이 있는 경우 배열에서 해당 값의 행 번호와 열 번호를 지정하여 배열의 값을 검색할 수 있습니다.

나열된 영업 사원별로 각 북부, 남부, 동부 및 서부 지역의 매출을 찾을 수 있는 다음 판매 데이터를 고려하십시오.

 

 

배열의 이름을 SalesData로 지정합니다.

INDEX 함수를 사용하여, 당신은 찾을 수 있습니다 -

특정 지역의 영업 사원의 판매.

모든 영업 사원에 의한 지역의 총 판매액입니다.

모든 지역의 영업 사원별 총 판매액입니다.

 

 

다음과 같은 결과를 얻을 것이다 -

 

 

영업 사원의 행 번호와 지역의 열 번호를 모른다고 가정해 보겠습니다. 그런 다음 index 함수로 값을 검색하기 전에 먼저 행 번호와 열 번호를 찾아야 합니다.

다음 섹션에서 설명하는 대로 MATCH 함수를 사용하여 수행할 수 있습니다.

 

MATCH 기능 사용

범위 내 항목의 위치가 필요한 경우 MATCH 함수를 사용할 수 있습니다. 당신은 다음과 같이 MATCH 및 INDEX 함수를 결합 할 수 있습니다 -

 

다음과 같은 결과를 얻을 것이다 -