Excel Index는 여러 기준 - 공식 예제와 일치합니다

자습서는 인덱스 및 매치를 사용하여 Excel에서 여러 기준으로 조회하는 방법 및 몇 가지 다른 방법을 보여줍니다.

Microsoft Excel은 수직 및 수평 조회를위한 특수 기능을 제공하지만 전문가 사용자는 일반적으로이를 인덱스 매치로 대체하여 여러면에서 Vlookup 및 Hlookup보다 우수합니다. 무엇보다도 열과 행에서 두 개 이상의 기준을 찾을 수 있습니다. 이 튜토리얼은 구문 및 내부 역학에 대해 자세히 설명하여 특정 요구에 대한 공식을 쉽게 조정할 수 있습니다. 예제를보다 쉽게 ​​따라갈 수 있도록 샘플 통합 문서를 다운로드 할 수 있습니다.

• 여러 기준과 인덱스 일치
• 두 가지 이상의 기준과 비 어절 인덱스 일치
• 행 및 열의 여러 기준 (매트릭스 조회)의 인덱스 일치

Excel Index는 여러 기준과 일치합니다

대형 데이터베이스로 작업 할 때 때때로 무언가를 찾아야하지만 검색을위한 고유 식별자가 없을 때 상황에 처할 수 있습니다. 이 경우 여러 조건이있는 조회가 유일한 솔루션입니다.

별도의 열에서 여러 기준을 기반으로 값을 찾으려면이 일반적인 공식을 사용하십시오.

{= index ( return_Range , match (1, ( Criteria1 = Range1 )) * ( Criteria2 = Range2 ) * (…), 0)}}

어디:

• return_range는 값을 반환 할 범위입니다.
• Criteria1 , criteria2 ,… 충족 될 조건입니다.
• Range1 , Range2 ,…는 해당 기준을 테스트 해야하는 범위입니다.

중요한 메모! 이것은 배열 공식이며 Ctrl Shift Enter 로 완료해야합니다. 이것은 당신의 공식을 {curly brackets}로 둘러싸고, 이는 Excel의 배열 공식의 시각적 부호입니다. 버팀대를 수동으로 입력하지 마십시오. 작동하지 않습니다!

이 공식은 단일 기준에 따라 일치를 반환하는 상징적 인덱스 매치의 고급 버전입니다. 여러 기준을 평가하기 위해 배열 공식에서 및 연산자 역할을하는 곱셈 작업을 사용합니다. 아래에는 실제 예제와 논리에 대한 자세한 설명이 있습니다.

팁. Excel 365 및 2021에서 여러 기준으로 Xlookup 공식을 사용할 수 있습니다.

몇 가지 기준과 인덱스 일치 - 공식 예제

이 예를 들어, 우리는 자체 행에 각 별도의 기준 조합 (우리의 경우 영역-분기)이있는 소위 "flat-file"형식의 테이블을 사용합니다. 우리의 목표는 특정 지역과 월의 특정 품목에 대한 판매 수치를 검색하는 것입니다.

다음 셀의 소스 데이터 및 기준으로 :

• return_range (판매) -D2 : D13
• Criteria1 (대상 영역) -G1
• Criteria2 (대상 월) -G2
• Criteria3 (대상 항목) -G3
• 범위 1 (영역) -A2 : A13
• 범위 2 (달) -B2 : B13
• Range3 (항목) -C2 : C13

공식은 다음과 같은 모양을 취합니다.

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

G4에 공식을 입력하고 Ctrl Shift Enter를 눌러 완료하면 다음과 같은 결과를 얻을 수 있습니다.

이 공식의 작동 방식

가장 까다로운 부분은 매치 함수이므로 먼저 알아 봅시다.

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

기억하시면 Match (Lookup_Value, Lookup_Array, [match_type])는 조회 배열에서 조회 값을 검색하고 배열에서 해당 값의 상대 위치를 반환합니다.

우리의 공식에서 논쟁은 다음과 같습니다.

• Lookup_Value : 1
• Lookup_Array : (g1 = a2 : a13) * (g2 = b2 : b13) * (g3 = c2 : c13)
• match_type : 0

1 ^ST 인수는 CHEFEL이 명확합니다. 함수는 숫자 1을 검색합니다. 3 ^RD 인수는 0으로 설정된 3 rd 인수는 "정확한 일치"를 의미합니다. 즉, 공식은 조회 값과 정확히 같은 첫 번째 발견 된 값을 반환합니다.

문제는 - 왜 "1"을 검색합니까? 답을 얻으려면 각 기준을 해당 범위와 비교하는 조회 배열을 자세히 살펴 보겠습니다. 모든 영역에 대한 G1의 목표 영역 (A2 : A13), 모든 달 (B2 : B13)에 대한 G2의 목표 달 (C2 : C13)에 대한 G3의 대상 항목에 대한 G2의 목표 달. 중간 결과는 True의 3 배 어레이와 False가 테스트 된 조건을 충족하는 값을 나타냅니다. 이를 시각화하려면 공식에서 개별 표현식을 선택하고 F9 키를 눌러 각 표현식이 무엇을 평가하는지 확인할 수 있습니다.

곱셈 작업은 True and False 값을 각각 1과 0으로 변환합니다.

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

그리고 0을 곱하면 항상 0을 제공하기 때문에 결과 배열은 모든 기준을 충족하는 행에 1 만 있습니다.

{0;0;1;0;0;0;0;0;0;0;0;0}

위의 배열은 일치의 Lookup_Array 인수로 이동합니다. Xookup_value 1의 경우 함수는 모든 기준이 true 인 행의 상대 위치를 반환합니다 (우리의 경우 3 행). 배열에 몇 개의 1이있는 경우 첫 번째 위치의 위치가 반환됩니다.

매치로 반환 된 숫자는 인덱스의 Row_num 인수 (Array, Row_num, [collect_num]) 함수로 직접 이동합니다.

=INDEX(D2:D13, 3)

그리고 D2 : D13 배열의 3 ^rd 값 인 115 달러의 결과를 얻습니다.

여러 기준을 갖는 비 배열 인덱스 일치 공식

이전 예제에서 논의 된 배열 공식은 숙련 된 사용자에게 좋습니다. 그러나 다른 사람을위한 공식을 구축하고 누군가가 배열 기능을 알지 못한다면 우연히 그것을 깨뜨릴 수 있습니다. 예를 들어, 사용자는 공식을 클릭하여 검사 한 다음 Ctrl Shift Enter 대신 Enter 를 누릅니다. 그러한 경우, 배열을 피하고 더 방탄 인 일반 공식을 사용하는 것이 현명합니다.

index ( return_range , match (1, index (( Criteria1 = range1 )) * ( Criteria2 = Range2 ) * (..), 0, 1), 0)).

샘플 데이터 세트의 경우 공식은 다음과 같습니다.

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

이 공식의 작동 방식

인덱스 함수가 ​​기본적으로 배열을 처리 할 수 ​​있으므로 두 개 이상의 True/False 배열을 곱하여 생성되는 1과 0의 배열을 처리하기 위해 다른 인덱스를 추가합니다. 두 번째 인덱스는 단일 값이 아닌 전체 열 배열을 반환하기 위해 0 row_num 인수로 구성됩니다. 어쨌든 1 열 배열이므로 column_num 에 안전하게 1을 공급할 수 있습니다.

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

이 배열은 일치 함수로 전달됩니다.

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

일치는 모든 기준이 true (보다 정확하게, 지정된 배열에서 해당 행의 상대적 위치)를 찾는 행 번호를 찾고 첫 번째 색인의 row_num 인수로 그 숫자를 전달합니다.

=INDEX(D2:D13, 3)

행과 열의 여러 기준과 인덱스 일치

이 예제는 행과 열에서 둘 이상의 기준을 테스트하여 조회를 수행하는 방법을 보여줍니다. 실제로, 그것은 하나 이상의 헤더 행이있는 소위 "매트릭스 조회"또는 "양방향 조회"의 더 복잡한 경우입니다.

행과 열의 여러 기준을 가진 일반 인덱스 일치 공식은 다음과 같습니다.

{= index ( table_array , match ( vlookup_value , lookup_column , 0), match ( hlookup_value1 & hlookup_value2 , lookup_row1 & lookup_row2 , 0))}

어디:

TABLE_ARRAY- 검색 할지도 또는 영역 (예 : 열 및 행 헤더를 제외한 모든 데이터 값).

Vlookup_value- 열에서 수직으로 찾고있는 값.

Lookup_Column- 검색 할 열 범위, 일반적으로 행 헤더입니다.

hlookup_value1, hlookup_value2, … - 당신이 찾고있는 값은 행으로 수평으로.

Lookup_row1, Lookup_row2, … - 행은 검색하는 범위, 일반적으로 열 헤더입니다.

중요한 메모! 공식이 올바르게 작동하려면 Ctrl Shift Enter가 있는 배열 공식 으로 입력해야합니다.

특정 행과 열의 교차점에서 값을 검색하는 클래식 양방향 조회 공식의 변형입니다. 차이점은 여러 열 헤더를 평가하기 위해 여러 HLOOKUP 값과 범위를 연결한다는 것입니다. 논리를 더 잘 이해하려면 다음 예를 고려하십시오.

여러 기준 - 공식 예제가있는 매트릭스 조회

아래 샘플 표에서 행 헤더 (항목) 및 2 개의 열 헤더 (지역 및 공급 업체)를 기반으로 값을 검색합니다. 공식을보다 쉽게 ​​구축 할 수 있도록 먼저 모든 기준과 범위를 정의해 봅시다.

• table_array -b3 : e4
• vlookup_value (대상 항목) -H1
• Lookup_Column (행 헤더 : 항목) -A3 : A4
• hlookup_value1 (대상 영역) -H2
• hlookup_value2 (대상 공급 업체) -H3
• Lookup_row1 (열 헤더 1 : 영역) -B1 : E1
• Lookup_row2 (열 헤더 2 : 공급 업체) -B2 : E2

그리고 이제 위에서 설명한 일반적인 공식에 인수를 제공하면이 결과를 얻을 수 있습니다.

=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

Ctrl Shift Enter 바로 가기를 눌러 공식을 완료해야하며 여러 기준으로 매트릭스 조회가 성공적으로 수행됩니다.

이 공식의 작동 방식

수직 및 수평으로 검색 할 때 인덱스 (Array, Row_num, Column_Num) 기능의 행 번호와 열 번호를 모두 제공해야합니다.

ROW_NUM은 A3 : A5의 행 헤더와 H1의 대상 항목 (사과)을 비교하는 매치 (H1, A3 : A5, 0)에 의해 전달됩니다. "사과"는 지정된 범위의 첫 번째 항목이므로 1의 결과를 제공합니다.

Collect_num은 2 개의 조회 값과 2 개의 조회 배열을 연결하여 해결됩니다 : 매치 (H2 & H3, B1 : E1 & B2 : E2, 0))

성공의 핵심 요소는 조회 값이 열 헤더와 정확히 일치하고 동일한 순서로 연결되어야한다는 것입니다. 이를 시각화하려면 경기 공식에서 처음 두 인수를 선택하고 F9 를 누르면 각 인수가 평가하는 내용을 볼 수 있습니다.

MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)

"Northvendor 2"는 배열의 두 번째 요소이므로 함수는 2를 반환합니다.

이 시점에서, 우리의 긴 2 차원 인덱스 매치 공식은이 간단한 것으로 바뀝니다.

=INDEX(B3:E5, 1, 2)

그리고 셀 C3의 값 인 범위 B3 : E5의 첫 번째 행과 2 열의 교차점에서 값을 반환합니다.

이것이 Excel에서 여러 기준을 찾는 방법입니다. 읽어 주셔서 감사합니다. 다음 주에 우리 블로그에서 뵙기를 바랍니다!

다운로드를위한 연습 통합 문서

Excel Index는 여러 기준 (.xlsx 파일) 일치합니다.

위 내용은 Excel Index는 여러 기준 - 공식 예제와 일치합니다의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!