logo

TechCodeview

Excel의 INDEX 및 MATCH 함수

INDEX-MATCH는 VLOOKUP 기능의 한계를 해결하고 사용하기 쉬워지면서 Excel에서 더욱 널리 사용되는 도구가 되었습니다. Excel의 INDEX-MATCH 함수는 VLOOKUP 함수에 비해 여러 가지 장점이 있습니다.

  1. INDEX 및 MATCH는 Vlookup보다 더 유연하고 빠릅니다.
  2. 가로 조회, 세로 조회, 양방향 조회, 왼쪽 조회, 대소문자 구분 조회, 심지어 다중 기준 조회까지 실행 가능합니다.
  3. 정렬된 데이터에서 INDEX-MATCH는 VLOOKUP보다 30% 빠릅니다. 이는 더 큰 데이터 세트에서는 30% 더 빠른 것이 더 합리적이라는 것을 의미합니다.

각 INDEX와 MATCH의 세부 개념부터 살펴보겠습니다.

인덱스 기능

Excel의 INDEX 함수는 매우 강력함과 동시에 범위 내 특정 위치의 값을 검색하는 유연한 도구입니다. 즉, 행 및 열 오프셋으로 지정된 셀의 내용을 반환합니다.



통사론: 

=INDEX(reference, [row], [column])>

매개변수:

    reference: 오프셋할 셀의 배열입니다. 단일 범위일 수도 있고 데이터 테이블의 전체 데이터세트일 수도 있습니다. row [선택사항]: 오프셋 행 수입니다. 이는 테이블 참조 범위를 A1:A5로 선택하면 추출하려는 셀/내용이 수직 거리만큼 떨어져 있음을 의미합니다. 여기서 A1 행의 경우 1이 되고, A2 행의 경우 = 2가 됩니다. 행 = 4를 주면 A4가 추출됩니다. 행은 선택 사항이므로 행 번호를 지정하지 않으면 참조 범위의 전체 행을 추출합니다. 이 경우에는 A1부터 A5까지입니다. 열 [선택 사항]: 오프셋 열 수입니다. 이는 테이블 참조 범위를 A1:B5로 선택하면 추출하려는 셀/내용이 수평 거리만큼 떨어져 있음을 의미합니다. 여기서 A1 행은 1이고 열은 1이고, B1 행의 경우 행은 1이지만 열은 2입니다. 마찬가지로 A2 행 = 2 열 = 1, B2 행 = 2 열 = 2 등입니다. 행 = 5와 열 2를 제공하면 B5가 추출됩니다. 열은 선택 사항이므로 행 번호를 지정하지 않으면 그런 다음 참조 범위의 전체 열을 추출합니다. 예를 들어 행 = 2이고 열을 공백으로 지정하면 (A2:B2)가 추출됩니다. 행과 열을 모두 지정하지 않으면 (A1:B5)인 전체 참조 테이블이 추출됩니다.

참조 테이블: 다음 표는 INDEX 함수의 모든 예제에 대한 참조 표로 사용됩니다. 첫 번째 셀은 B3(FOOD)에 있고 마지막 대각선 셀은 F10(180)에 있습니다.

참고표

예: 다음은 Index 함수의 몇 가지 예입니다.

사례 1: 행과 열이 언급되지 않았습니다.

입력 명령: =인덱스(B3:C10)

사례 1

사례 2: 행만 언급됩니다.

입력 명령: =INDEX(B3:C10,2)

사례 2

사례 3: 행과 열이 모두 언급됩니다.

입력 명령: =INDEX(B3:D10,4,2)

사례 3

사례 4: 열만 언급됩니다.

입력 명령: =INDEX(B3 : D10 , , 2)

사례 4

INDEX 함수 문제: INDEX 함수의 문제점은 찾고 있는 데이터에 대해 행과 열을 지정해야 한다는 것입니다. 10,000개의 행과 열로 구성된 기계 학습 데이터 세트를 다루고 있다고 가정하면 우리가 찾고 있는 데이터를 검색하고 추출하는 것이 매우 어려울 것입니다. 여기에는 일부 조건에 따라 행과 열을 식별하는 일치 함수의 개념이 있습니다.

일치 기능

범위에서 항목/값의 위치를 ​​검색합니다. 실제 데이터가 아닌 위치 정보만 반환하는 VLOOKUP 또는 HLOOKUP의 덜 정제된 버전입니다. MATCH는 대소문자를 구분하지 않으며 범위가 수평인지 수직인지 상관하지 않습니다.

통사론: 

=MATCH(search_key, range, [search_type])>

매개변수:

    search_key: 검색할 값입니다. 예를 들어 42, Cats 또는 I24입니다. range: 검색할 1차원 배열입니다. 단일 행 또는 단일 열일 수 있습니다. 예->A1:A10 , A2:D2 등. search_type [선택 사항]: 검색 방법입니다. = 1(기본값)은 범위를 오름차순으로 정렬할 때 search_key보다 작거나 같은 가장 큰 값을 찾습니다.
    • = 0은 범위가 정렬되지 않은 경우 정확한 값을 찾습니다.
    • = -1은 범위를 내림차순으로 정렬할 때 search_key보다 크거나 같은 가장 작은 값을 찾습니다.

match 함수를 이용하여 행 번호나 열 번호를 찾을 수 있고, index 함수 내에서 사용할 수 있기 때문에 항목에 대한 세부정보가 있는 경우 match 함수를 이용하여 해당 항목의 행/열을 찾아 해당 항목에 대한 모든 정보를 추출할 수 있습니다. 그런 다음 이를 인덱스 함수에 중첩합니다.

참조 테이블: 다음 표는 MATCH 함수의 모든 예에 대한 참조 표로 사용됩니다. 첫 번째 셀은 B3(FOOD)에 있고 마지막 대각선 셀은 F10(180)에 있습니다.

참조 테이블 MATCH 함수

예: 다음은 MATCH 함수의 몇 가지 예입니다.

사례 1: 검색 유형 0, 완전 일치를 의미합니다.

입력 명령: =MATCH(남인도,C3:C10,0)

사례 1 일치

사례 2: 검색 유형 1(기본값).

입력 명령: =MATCH(남인도,C3:C10)

사례 2 일치

리눅스의 cp 명령


사례 3: 검색 유형 -1.

입력 명령: =MATCH(남인도,C3:C10,-1)

사례 3 일치

함께 인덱스 매치

이전 예제에서는 INDEX 함수에 행과 열의 정적 값을 제공했습니다. 행과 열 위치에 대한 사전 지식이 없다고 가정하면 MATCH 함수를 사용하여 행과 열 위치를 제공할 수 있습니다. 이는 가치를 검색하고 추출하는 동적 방법입니다.

통사론: 

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

참조 테이블: 다음 참조 테이블이 사용됩니다. 첫 번째 셀은 B3(FOOD)에 있고 마지막 대각선 셀은 F10(180)에 있습니다.

참조 테이블 INDEX-MATCH

예: 작업이 Masala Dosa의 비용을 찾는 것이라고 가정해 보겠습니다. 3열은 품목 단가를 나타내는 것으로 알려져 있으나 Masala Dosa의 행 위치는 알 수 없습니다. 문제는 두 단계로 나눌 수 있습니다.

1 단계: 다음 공식을 사용하여 Masala Dosa의 위치를 ​​찾으세요.

 =MATCH('Masala Dosa',B3:B10,0)>

여기서 B3:B10은 음식 열을 나타내고 0은 완전 일치를 의미합니다. Masala Dosa의 행 번호를 반환합니다.

2 단계: Masala Dosa의 비용을 찾아보세요. INDEX 함수를 사용하여 Masala Dosa의 비용을 알아보세요. Masala Dosa의 정확한 위치가 필요한 위치에 INDEX 함수 내부에 위의 MATCH 함수 쿼리를 대입하면 비용의 열 번호는 이미 알려진 3개입니다.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

함께 인덱스 매치

INDEX-MATCH를 함께 사용한 양방향 조회

이전 예에서는 Cost 속성의 열 위치가 하드코드되었습니다. 그래서 그것은 완전히 역동적이지 않았습니다.

사례 1: Cost의 열 번호에 대한 지식도 없다고 가정하면 다음 공식을 사용하여 얻을 수 있습니다.

 =MATCH('Cost',B3:F3,0)>

여기서 B3:F3은 헤더 열을 나타냅니다.

사례 2: 행과 열 값이 MATCH 함수를 통해 제공되는 경우(정적 값 제공 없이) 이를 양방향 조회라고 합니다. 다음 공식을 사용하여 얻을 수 있습니다.

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

양방향 조회

왼쪽 조회

VLOOKUP 함수에 비해 INDEX 및 MATCH의 주요 장점 중 하나는 왼쪽 조회를 수행하는 기능입니다. 이는 오른쪽에 있는 임의의 속성을 사용하여 항목의 행 위치를 추출할 수 있고 왼쪽에 있는 다른 속성의 값을 추출할 수 있음을 의미합니다.

예를 들어 가격이 140 Rs인 식품을 구매한다고 가정해 보겠습니다. 간접적으로 우리는 Biryani를 사라고 말하고 있습니다. 이 예에서는 Rs 140/-의 비용이 알려져 있으므로 음식을 추출해야 합니다. Cost 열은 Food 열의 오른쪽에 배치되기 때문입니다. VLOOKUP을 적용하면 비용 열의 왼쪽을 검색할 수 없습니다. 그렇기 때문에 VLOOKUP을 사용하면 음식 이름을 얻을 수 없습니다.

이러한 단점을 극복하기 위해 INDEX-MATCH 함수 왼쪽 조회를 사용할 수 있습니다.
1 단계: 먼저 다음 공식을 사용하여 Cost 140 Rs의 행 위치를 추출합니다.

 =MATCH(140, D3:D10,0)>

여기서 D3: D10은 Cost 140 Rs 행 번호에 대한 검색이 수행되는 Cost 열을 나타냅니다.

2 단계: 행 번호를 얻은 후 다음 단계는 INDEX 함수를 사용하여 다음 공식을 사용하여 음식 이름을 추출하는 것입니다.

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

여기서 B3:B10은 식품 열을 나타내고 140은 식품 항목의 비용을 나타냅니다.

왼쪽 조회

대소문자 구분 조회

MATCH 함수 자체는 대소문자를 구분하지 않습니다. 이는 음식 이름이 DHOKLA이고 MATCH 함수가 다음 검색어와 함께 사용되는 경우를 의미합니다.

  1. 도클라
  2. 도클라
  3. 도클라

모두 DHOKLA의 행 위치를 반환합니다. 그러나 EXACT 함수를 INDEX 및 MATCH와 함께 사용하여 대문자와 소문자를 고려하는 조회를 수행할 수 있습니다.

정확한 기능: Excel EXACT 함수는 대문자와 소문자를 고려하여 두 개의 텍스트 문자열을 비교하고, 같으면 TRUE를 반환하고, 같지 않으면 FALSE를 반환합니다. EXACT는 대소문자를 구분합니다.

예:

    EXACT(DHOKLA,DHOKLA): True를 반환합니다. EXACT(DHOKLA,Dhokla): False를 반환합니다. EXACT(DHOKLA,dhokla): False를 반환합니다. EXACT(DHOKLA,DhOkLA): False를 반환합니다.

예: 작업이 음식 Dhokla 유형을 대소문자를 구분하여 검색하는 것이라고 가정해 보겠습니다. 이는 다음 공식을 사용하여 수행할 수 있습니다.

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

여기서 EXACT 함수는 B3:B10 열의 값이 동일한 대소문자를 가진 Dhokla와 일치하면 True를 반환하고, 그렇지 않으면 False를 반환합니다. 이제 MATCH 함수가 B3:B10 열에 적용되고 정확한 값이 TRUE인 행을 검색합니다. 그 후 INDEX 함수는 MATCH 함수에서 반환된 행에서 C3:C10 열(식품 유형 열)의 값을 검색합니다.

대소문자 구분 조회

다중 기준 조회

Excel에서 가장 까다로운 문제 중 하나는 여러 기준에 따른 조회입니다. 즉, 동시에 둘 이상의 열을 일치시키는 조회입니다. 아래 예에서는 INDEX 및 MATCH 함수와 부울 논리를 사용하여 3개 열을 일치시킵니다.

  1. 음식.
  2. 비용.
  3. 수량.

총 비용을 추출합니다.

예: 작업이 파스타의 총 비용을 계산하는 것이라고 가정해 보겠습니다.

    음식: 파스타. 가격: 60. 수량: 1.

따라서 이 예에는 일치를 수행하기 위한 세 가지 기준이 있습니다. 다음은 여러 기준에 따른 검색 단계입니다.

1 단계: 다음 수식을 사용하여 음식 열(B3:B10)을 파스타와 먼저 일치시킵니다.

 'PASTA' = B3:B10>

그러면 B3:B10(음식 열) 값이 부울로 변환됩니다. 음식이 파스타라면 그것은 사실이고 그렇지 않으면 거짓입니다.

2 단계: 그런 다음 다음과 같은 방식으로 비용 기준을 일치시킵니다.

 60 = D3:D10>

그러면 D3:D10(비용 열) 값이 부울로 대체됩니다. Cost=60이면 True이고 그렇지 않으면 False입니다.

3단계: 다음 단계는 다음과 같은 방식으로 수량 = 1인 세 번째 기준을 일치시키는 것입니다.

 1 = E3:E10>

이는 E3:E10 열(수량 열)을 True로 대체합니다. 여기서 수량 = 1이면 False가 됩니다.

4단계: 첫 번째, 두 번째, 세 번째 기준의 결과를 곱합니다. 이는 모든 조건의 교차점이며 Boolean True/False를 1/0으로 변환합니다.

5단계: 이제 결과는 0과 1이 있는 열이 됩니다. 여기서 MATCH 함수를 사용하여 1을 포함하는 열의 행 번호를 찾습니다. 열의 값이 1이면 세 가지 기준을 모두 충족한다는 의미이기 때문입니다.

6단계: 행 번호를 얻은 후 INDEX 함수를 사용하여 해당 행의 총 비용을 가져옵니다.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

여기서 F3:F10은 총 비용 열을 나타냅니다.