2 차원 조회를 위해 Excel의 인덱스 매치

이 튜토리얼은 Excel에서 2 차원 조회를 수행하기 위해 몇 가지 다른 공식을 보여줍니다. 대안을 살펴보고 좋아하는 것을 선택하십시오 :)

Excel 스프레드 시트에서 무언가를 검색 할 때 대부분의 경우 열에서 수직 또는 수평으로 볼 수 있습니다. 그러나 때로는 행과 열을 모두 볼 필요가 있습니다. 다시 말해, 특정 행과 열의 교차점에서 값을 찾는 것을 목표로합니다. 이것을 매트릭스 조회 (일명 2 차원 또는 2 방향 조회 )라고 하며이 자습서는 4 가지 방법으로 수행하는 방법을 보여줍니다.

• 인덱스 매치 매치 행과 행과 열에서 조회
• Vlookup을 사용한 양방향 조회
• Xlookup을 사용한 2 방향 조회
• Sumproduct를 사용한 2 차원 조회
• 명명 된 범위가있는 매트릭스 조회 (명시 적 교차)

Excel Index Match Formula

Excel에서 양방향 조회를하는 가장 인기있는 방법은 인덱스 매치 매치를 사용하는 것입니다. 이것은 행과 열 숫자를 모두 얻기 위해 하나 더 일치 함수를 추가하는 클래식 인덱스 일치 공식의 변형입니다.

index ( data_array , match ( vlookup_value , lookup_column_range , 0), match ( hlookup 값 , lookup_row_range , 0)).

예를 들어, 아래 표에서 주어진 연도에 특정 동물의 집단을 끌어내는 공식을 만들어 봅시다. 우선, 우리는 모든 주장을 정의합니다.

• data_array -b2 : e4 (데이터 셀, 행 및 열 헤더가 포함되지 않음)
• vlookup_value -h1 (대상 동물)
• Lookup_Column_Range -A2 : A4 (행 헤더 : 동물 이름) -A3 : A4
• hlookup_value -h2 (대상 연도)
• lookup_row_range -b1 : e1 (열 헤더 : 년)

모든 인수를 정리하면 양방향 조회를 위해이 공식을 얻게됩니다.

=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))

두 가지 이상의 기준으로 양방향 조회를 해야하는 경우이 기사를 살펴보십시오. 행과 열의 여러 기준과 인덱스 일치.

이 공식의 작동 방식

언뜻보기에는 약간 복잡해 보일 수 있지만, 공식의 논리는 실제로 간단하고 이해하기 쉽습니다. 인덱스 함수는 행 및 열 숫자를 기반으로 데이터 배열에서 값을 검색하고 두 개의 일치 함수가 해당 숫자를 제공합니다.

INDEX(B2:E4, row_num, column_num)

여기서, 우리는 lookup_array 에서 lookup_value 의 상대적 위치를 반환 할 수있는 일치 능력 (lookup_value, lookup_array, [match_type])를 활용합니다.

따라서 행 번호를 얻기 위해 행 헤더 (A2 : A4)를 가로 질러 관심있는 동물 (H1)을 검색합니다.

MATCH(H1, A2:A4, 0)

열 번호를 얻으려면 열 헤더 (B1 : E1)에서 대상 연도 (H2)를 검색합니다.

MATCH(H2, B1:E1, 0)

두 경우 모두 세 번째 인수를 0으로 설정하여 정확한 일치를 찾습니다.

이 예에서, 첫 번째 매치는 Vlookup 값 (북극곰)이 A2 : A2 : A4의 2 ^ND 셀 인 A3에서 발견되므로 2를 반환합니다. HLOOKUP 값 (2000)은 D1에서 발견되기 때문에 두 번째 일치는 3을 반환합니다. 이는 B1 : E1의 3 ^rd 셀입니다.

위의 점을 감안할 때, 공식은 다음으로 줄어 듭니다.

INDEX(B2:E4, 2, 3)

셀 D3의 값인 데이터 배열 B2 : E4에서 2 ^행 및 ³ 열의 교차점에서 값을 반환합니다.

2 방향 조회를위한 Vlookup 및 매치 공식

Excel에서 2 차원 조회를 수행하는 또 다른 방법은 Vlookup과 매치 함수의 조합을 사용하는 것입니다.

vlookup ( vlookup_value , table_array , match ( hlookup_value , lookup_row_range , 0), false)

샘플 테이블의 경우 공식은 다음과 같은 모양을 취합니다.

=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)

어디:

• table_array -a2 : e4 (행 헤더를 포함한 데이터 셀)
• vlookup_value -h1 (대상 동물)
• hlookup_value -h2 (대상 연도)
• lookup_row_range -a1 : e1 (열 헤더 : 년)

이 공식의 작동 방식

공식의 핵심은 정확한 일치 (False로 설정된 마지막 인수)에 대해 구성된 VLOOKUP 함수이며 테이블 배열의 첫 번째 열에서 조회 값 (h1)을 검색하고 같은 행의 다른 열에서 값을 반환합니다. 값을 반환 할 열을 결정하려면 정확히 일치하도록 구성된 일치 함수를 사용합니다 (마지막 인수는 0으로 설정 됨).

MATCH(H2, A1:E1, 0)

일치하는 것은 열 헤더 (A1 : E1)를 가로 지르는 H2의 값을 검색하고 발견 된 셀의 상대 위치를 반환합니다. 우리의 경우, Target Year (2010)는 E1에서 발견되는데, 이는 조회 배열에서 5 ^번째 입니다. 따라서 숫자 5는 vlookup의 col_index_num 인수로갑니다.

VLOOKUP(H1, A2:E4, 5, FALSE)

Vlookup은 거기에서 그것을 가져 와서 A2에서 조회 값과 정확히 일치하는 것을 발견하고 같은 행의 5 ^번째 열에서 셀 E2 인 값을 반환합니다.

중요한 메모! 공식이 올바르게 작동하려면 Vlookup의 Table_Array (A2 : E4) 및 Match (A1 : E1)의 Xookup_Array (A1 : E1)는 동일한 수의 열이 있어야하며, 그렇지 않으면 Col_Index_num 에 일치하는 숫자는 잘못되며 ( Table_Array 에서 열의 위치에 해당하지 않음).

xlookup 함수는 행과 열에서 볼 수 있습니다

최근 Microsoft는 Excel에서 VLOOKUP, HLOOKUP 및 Index Match와 같은 모든 기존 조회 기능을 대체하기위한 한 가지 기능을 도입했습니다. 무엇보다도 Xlookup은 특정 행과 열의 교차점을 볼 수 있습니다.

xlookup ( vlookup_value , vlookup_column_range , xlookup ( hlookup_value , hlookup_row_range , data_array )))

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

=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))

메모. Xlookup 함수는 Microsoft 365, Excel 2021 및 웹 용 Excel에서만 사용할 수 있습니다.

이 공식의 작동 방식

공식은 Xlookup의 능력을 사용하여 전체 행 또는 열을 반환합니다. 내부 기능은 헤더 행에서 대상 연도를 검색하고 그 해의 모든 값을 반환합니다 (이 예에서는 1980 년). 이러한 값은 외부 xlookup의 return_array 인수로 이동합니다.

XLOOKUP(H1, A2:A4, {22000;25000;700}))

OUTER XLOOKUP 함수는 열 헤더를 가로 질러 대상 동물을 검색하고 return_Array에서 동일한 위치에서 값을 반환합니다.

양방향 조회를위한 SumProduct 공식

SumProduct 기능은 Excel의 스위스 나이프와 같습니다. 특히 여러 기준을 평가할 때 지정된 목적을 넘어서 많은 일을 할 수 있습니다.

행과 열에서 두 가지 기준을 찾으려면이 일반적인 공식을 사용하십시오.

sumProduct ( vlookup_column_range = vlookup_value ) * ( hlookup_row_range = hlookup_value ), data_array )

데이터 세트에서 2 방향 조회를 수행하려면 공식은 다음과 같습니다.

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)

아래 구문도 작동합니다.

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)

이 공식의 작동 방식

공식의 핵심에서, 우리는 두 개의 조회 값을 행과 열 헤더에 비교합니다 (A2 : A4의 모든 동물 이름과 B1 : E1의 모든 연도에 대한 H2의 모든 동물 이름에 대한 H1의 대상 동물) :

(A2:A4=H1) * (B1:E1=H2)

이로 인해 True와 False 값이 2 배 배열됩니다. 여기서 True가 일치합니다.

{FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}

곱셈 작업은 True and False 값을 1과 0으로 강요하고 4 개의 열 및 3 행으로 구성된 2 차원 배열을 생성합니다 (행은 세미 콜론과 각 컬럼에 의해 쉼표로 분리됩니다).

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

SumProduct 기능은 위의 배열의 요소에 B2 : E4의 항목에 동일한 위치에 곱합니다.

{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}

곱하기가 0을 곱하기 때문에 첫 번째 배열에서 1에 해당하는 항목 만 살아남을 수 있습니다.

SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})

마지막으로 SumProduct는 결과 배열의 요소를 추가하고 2000의 값을 반환합니다.

메모. 테이블에 동일한 이름을 가진 행 또는//열 헤더가 있으면 최종 배열에는 0 이외의 숫자가 하나 이상 포함되며 모든 숫자가 추가됩니다. 결과적으로 두 기준을 모두 충족하는 합계의 합이됩니다. SumProduct 공식을 Index Match Match 및 Vlookup과 다르게 만드는 이유는 첫 번째 발견 된 경기를 반환합니다.

명명 된 범위가있는 매트릭스 조회 (명시 적 교차)

Excel에서 매트릭스 조회를 수행하는 더 놀랍도록 간단한 방법은 명명 된 범위를 사용하는 것입니다. 방법은 다음과 같습니다.

1 부 : 이름 열과 행 이름

테이블의 각 행과 각 열을 지정하는 가장 빠른 방법은 다음과 같습니다.

1. 전체 테이블 (A1 : E4의 경우)을 선택하십시오.
2. 정의 된 이름 그룹의 공식 탭에서 선택에서 생성을 클릭하거나 Ctrl Shift F3 바로 가기를 누릅니다.
3. 선택 대화 상자에서 이름 작성 에서 상단 행 과 왼쪽 열을 선택하고 확인을 클릭하십시오.

   

이것은 행과 열 헤더를 기반으로 이름을 자동으로 생성합니다. 그러나 몇 가지 경고가 있습니다.

• 열 및/또는 행 헤더가 숫자 또는 Excel 이름으로 허용되지 않는 특정 문자를 포함하는 경우 해당 열 및 행의 이름이 생성되지 않습니다. 생성 된 이름 목록을 보려면 Name Manager ( Ctrl F3 )를여십시오. 일부 이름이 누락되면 Excel의 범위를 지정하는 방법에 설명 된대로 수동으로 정의하십시오.
• 행 또는 열 헤더에 공백이 포함 된 경우 공백은 밑줄로 교체됩니다 (예 : Polar_bear ).

샘플 테이블의 경우 Excel은 자동으로 행 이름 만 생성했습니다. 열 헤더의 숫자이므로 열 이름을 수동으로 만들어야합니다. 이를 극복하기 위해 _1990 과 같은 밑줄로 숫자를 서문 할 수 있습니다.

결과적으로, 우리는 다음과 같은 이름의 범위를 가지고 있습니다.

2 부 : 매트릭스 조회 공식을 만듭니다

주어진 행과 열의 교차점에서 값을 가져 오려면 빈 셀에 다음 일반 공식 중 하나를 입력하십시오.

= row_name column_name

또는 그 반대도 :

= column_name row_name

예를 들어, 1990 년에 푸른 고래의 인구를 얻으려면 공식은 다음과 같이 간단합니다.

=Blue_whale _1990

누군가가 더 자세한 지침이 필요한 경우 다음 단계가 프로세스를 안내합니다.

1. 결과가 나타나는 셀에서 평등 부호 (=)를 입력하십시오.
2. 대상 행의 이름을 입력 하기 시작합니다. 두 문자를 입력하면 Excel에 입력과 일치하는 모든 기존 이름이 표시됩니다. 원하는 이름을 두 번 클릭하여 공식에 입력하십시오.

   

3. 행 이름 이후,이 경우 교차로 연산자 로 작동하는 A 형 공간 .
4. 대상 열 이름 ( _1990 의 경우)을 입력하십시오.

   

5. 행과 열 이름이 모두 입력 되 자마자 Excel은 테이블의 해당 행과 열을 강조 표시하고 Enter를 눌러 공식을 작성합니다.

   

매트릭스 조회가 완료되고 아래 스크린 샷은 결과를 보여줍니다.

그것이 엑셀의 행과 열을 찾는 방법입니다. 읽어 주셔서 감사합니다. 다음 주에 우리 블로그에서 뵙기를 바랍니다!

사용 가능한 다운로드

2 차원 조회 샘플 통합 문서

위 내용은 2 차원 조회를 위해 Excel의 인덱스 매치의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!