공식 예제와 함께 Excel xmatch 함수

이 자습서는 새로운 Excel Xmatch 함수를 소개하고 몇 가지 일반적인 작업을 해결하는 것보다 더 나은 방법을 보여줍니다.

Excel 365에서, Xmatch 함수를 추가하여 매치 함수를 대체했습니다. 그러나 기존 공식을 업그레이드하기 전에 새로운 공식의 모든 장점과 기존 기능과 어떻게 다른지 이해하는 것이 좋습니다.

요약하면, Xmatch 함수는 일치와 동일하지만 더 유연하고 강력합니다. 수직 및 수평 배열에서 찾아, 최초 또는 최후의 첫 번째 검색, 정확한, 근사 및 부분 일치를 찾고 더 빠른 바이너리 검색 알고리즘을 사용할 수 있습니다.

• Excel의 xmatch 함수
• 기본 Excel XMatch 공식
• Excel에서 xmatch를 사용하는 방법 - 공식 예제
  • 정확히 일치 대 근사치 일치
  • 와일드 카드와 부분 일치
  • xmatch 리버스 검색
  • 일치하기 위해 두 열을 비교하십시오
  • 색인 XMATCH 공식
  • 색인 XMATCH XMATCH 공식
  • 사례에 민감한 Xmatch 공식
• xmatch vs. Excel에서 일치합니다

Excel xmatch 함수

Excel의 Xmatch 함수는 배열 또는 다양한 셀의 값의 상대 위치를 반환합니다.

다음 구문이 있습니다.

xmatch (lookup_value, lookup_array, [match_mode], [search_mode])

어디:

Lookup_value (필수) - 찾을 값.

Lookup_Array (필수) - 검색 할 셀의 배열 또는 범위.

match_mode (선택 사항) - 사용할 일치 유형을 지정합니다.

• 0 또는 생략 (기본값) - 정확한 일치
• -1- 정확한 일치 또는 다음으로 가장 작은 값
• 1- 정확한 일치 또는 다음 가장 큰 값
• 2- 와일드 카드 매치 (*,?)

search_mode (선택 사항) - 검색 방향 및 알고리즘을 지정합니다.

• 1 또는 생략 (기본값) - 처음부터 마지막으로 검색합니다.
• -1- 마지막부터 첫 번째로 역순으로 검색하십시오.
• 2- 이진 검색 오름차순. Lookup_Array가 오름차순으로 정렬해야합니다.
• -2- 이진 검색 하강. Xookup_array가 내림차순으로 정렬해야합니다.

이진 검색은 정렬 된 배열에서 효율적으로 작동하는 더 빠른 알고리즘입니다. 자세한 내용은 검색 모드를 참조하십시오.

어떤 Excel 버전에 xmatch가 있습니까?

Xmatch 함수는 Microsoft 365 및 Excel 2021 용 Excel에서만 사용할 수 있습니다. Excel 2019에서 Excel 2016 및 이전 버전에서는이 기능이 지원되지 않습니다.

Excel의 기본 xmatch 공식

함수가 무엇인지에 대한 일반적인 아이디어를 얻으려면 xmatch 공식을 가장 간단한 형식으로 만들어 처음 두 개의 필요한 인수 만 정의하고 선택적 인수를 기본값에 남겨 두겠습니다.

가정하면, 당신은 그들의 크기 (c2 : c6)에 의해 순위가 매겨진 바다 목록을 가지고 있으며 특정 바다의 순위를 찾고자합니다. 이를 수행하려면 바다의 이름 인 인디언을 조회 값으로 사용하고 전체 이름 목록을 조회 배열로 사용하십시오.

=XMATCH("Indian", C2:C6)

공식을보다 유연하게 만들려면 일부 셀에 관심있는 바다를 입력하십시오.

=XMATCH(F1, C2:C6)

결과적으로 수직 배열 에서 Xmatch 공식을 찾을 수 있습니다. 출력은 어레이에서 조회 값의 상대적 위치이며, 우리의 경우 바다의 순위에 해당합니다.

비슷한 공식은 수평 배열 에도 완벽하게 작동합니다. Lookup_Array 참조를 조정하는 것만으로는 다음과 같습니다.

=XMATCH(B5, B1:F1)

Excel xmatch 함수 - 기억해야 할 것

워크 시트에서 Xmatch를 효과적으로 사용하고 예상치 못한 결과를 방지하려면 다음과 같은 간단한 사실을 기억하십시오.

• 조회 배열에서 조회 값의 두 개 이상의 발생이있는 경우 Search_Mode 인수가 1으로 설정되거나 생략되면 첫 번째 일치 위치가 반환됩니다. Search_Mode가 -1로 설정된 경우 기능은 역 순서로 검색 하고이 예제에서 설명한대로 마지막 일치 의 위치를 ​​반환합니다.
• 조회 값을 찾을 수없는 경우 #N/A 오류가 발생합니다.
• Xmatch 함수는 본질적으로 대소 문자를 사용하지 않으며 문자 케이스를 구별 할 수 없습니다. 소문자와 대문자를 구별하려면이 케이스에 민감한 Xmatch 공식을 사용하십시오.

Excel에서 xmatch를 사용하는 방법 - 공식 예제

다음 예제는 Xmatch 기능과 실제 용도에 대한 이해를 높이는 데 도움이됩니다.

정확히 일치 대 근사치 일치

Xmatch의 일치 동작은 선택적 Match_Mode 인수에 의해 제어됩니다.

• 0 또는 생략 (기본값) - 공식은 정확한 일치 만 검색합니다. 정확한 일치를 찾을 수없는 경우 #N/A 오류가 반환됩니다.
• -1- 공식은 정확한 일치를 먼저 검색 한 다음 다음 작은 항목을 검색합니다.
• 1- 공식은 정확한 일치를 먼저 검색 한 다음 다음 큰 항목을 검색합니다.

그리고 이제 다른 일치 모드가 공식의 결과에 어떤 영향을 미치는지 살펴 보겠습니다. 80,000,000km ² 의 특정 지역이 모든 바다 사이에 위치하는 위치를 알고 싶다고 가정 해 봅시다.

정확히 일치합니다

match_mode 에 0을 사용하는 경우 공식이 조회 값과 정확히 동일한 값을 찾을 수 없으므로 #N/A 오류가 발생합니다.

=XMATCH(80000000, C2:C6, 0)

다음으로 가장 작은 항목

match_mode 에 -1을 사용하는 경우, 조회 값보다 가장 가까운 일치는 70,560,000이며 조회 배열의 3 ^RD 항목이기 때문에 공식은 3을 반환합니다.

=XMATCH(80000000, C2:C6, -1)

다음으로 가장 큰 항목

match_mode 에 1을 사용하는 경우, 조회 값보다 가장 큰 매치는 85,133,000이므로 조회 배열의 2 ^ND 항목입니다.

=XMATCH(80000000, C2:C6, -1)

아래 이미지는 모든 결과를 보여줍니다.

Excel의 부분 텍스트를 와일드 카드와 일치시키는 방법

Xmatch 함수에는 와일드 카드에 대한 특수 일치 모드가 있습니다. match_mode 인수는 2로 설정됩니다.

와일드 카드 일치 모드에서 Xmatch 공식은 다음 과일드 카드 문자를 수용합니다.

• 질문 마크 (?)는 단일 문자와 일치합니다.
• 모든 문자 시퀀스와 일치하는 별표 (*).

와일드 카드는 숫자가 아닌 텍스트로만 작동한다는 점을 명심하십시오.

예를 들어, "South"로 시작하는 첫 번째 항목의 위치를 ​​찾으려면 공식은 다음과 같습니다.

=XMATCH("south*", B2:B6, 2)

또는 일부 셀에 와일드 카드 표현식을 입력 할 수 있습니다 (F1). Lookup_value 인수에 대한 셀 기준을 제공 할 수 있습니다.

=XMATCH(F1, B2:B6, 2)

대부분의 Excel 함수를 사용하면 Tilde (~)를 사용하여 별표 (~*) 또는 물음표 (~?)를 와일드 카드가 아닌 문자 문자로 취급합니다. Xmatch를 사용하면 Tilde가 필요하지 않습니다. 와일드 카드 매치 모드를 정의하지 않으면 Xmatch가 그렇게 가정 할 것입니까? 그리고 *는 일반 문자입니다.

예를 들어, 아래 공식은 별표 문자에 대해 정확히 A2 : A7 범위를 검색합니다.

=XMATCH("*", A2:A7)

xmatch 반전 검색을 위해 마지막 경기를 찾으십시오

조회 배열에 몇 가지 조회 값이 발생하는 경우 때로는 마지막 발생 의 위치를 ​​가져와야 할 수도 있습니다.

검색 방향은 Search_Mode 라는 Xmatch의 4 ^번째 인수로 제어됩니다. 역순으로 검색하려면 수직 배열에서 바닥에서 상단으로, 수평 배열에서 오른쪽에서 왼쪽으로 검색하려면 Search_Mode는 -1로 설정해야합니다.

이 예에서는 특정 조회 값에 대한 마지막 레코드의 위치를 ​​반환합니다 (아래 스크린 샷 참조). 이를 위해 다음과 같이 인수를 설정하십시오.

• Lookup_value- H1의 대상 영업 사원
• C2 : C10의 Lookup_Array- 영업 사원 이름
• match_mode 가 0이거나 생략 (정확히 일치)
• Search_Mode 는 -1 (마지막으로 최초)입니다.

네 가지 인수를 정리하면이 공식을 얻습니다.

=XMATCH(H1, C2:C10, 0, -1)

Laura의 마지막 판매 수를 반환합니다.

일치하기 위해 Excel의 두 열을 비교하는 방법

일치에 대한 두 개의 목록을 비교하려면 if 및 isna와 함께 Xmatch 함수를 사용할 수 있습니다.

if (isna (xmatch ( target_list , search_list , 0)), "No Match", "Match")

예를 들어, A2 : A10의 목록 1에 대해 B2 : B10의 목록 2를 비교하려면 공식은 다음과 같은 형태를 취합니다.

=IF(ISNA(XMATCH(B2:B10, A2:A9)), "", "Match in List 1")

이 예에서는 일치 만 식별하므로 IF 함수의 value_if_true 인수는 빈 문자열 ( "")입니다.

가장 상단 셀에 위의 공식을 입력하고 (이 경우 C2) Enter 를 누르면 다른 셀에 "유출"됩니다 (유출 범위라고 함).

이 공식의 작동 방식

공식의 핵심에서 Xmatch 함수는 목록 1 내에서 목록 2에서 값을 검색합니다. 값이 발견되면 상대 위치가 반환됩니다. 우리의 경우 Xmatch의 결과는 다음과 같은 배열입니다.

{#N/A;#N/A;2;#N/A;4;#N/A;#N/A;8;#N/A}

이 배열은 #n/a 오류를 확인하기 위해 ISNA 기능에 "공급"됩니다. 각 #N/A 오류에 대해 ISNA는 TRUE를 반환합니다. 다른 값에 대해 - 거짓. 결과적으로 True가 일치하지 않는 다음과 같은 논리적 값 배열을 생성하고 False는 일치합니다.

{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}

위의 배열은 IF 함수의 논리 테스트로 이동합니다. 마지막 두 인수를 구성한 방법에 따라 공식은 해당 텍스트를 출력합니다. 우리의 경우, 비 일치 ( value_if_true )의 빈 문자열 ( "")이고 " value_if_false )의 경우"목록 1의 일치 "입니다.

메모. 이 공식은 동적 배열을 지원하는 Excel 365 및 Excel 2021에서만 작동합니다. Excel 2019, Excel 2016 또는 이전 버전을 사용하는 경우 다른 솔루션을 확인하십시오 : Excel에서 두 열을 비교하는 방법.

Excel의 색인 xmatch

xmatch는 인덱스 함수와 함께 인덱스 일치 공식과 마찬가지로 조회 값과 관련된 다른 열에서 값을 검색하기 위해 인덱스 함수와 함께 사용될 수 있습니다. 일반적인 접근 방식은 다음과 같습니다.

index ( return_array , xmatch ( lookup_value , lookup_array )

논리는 매우 간단하고 따르기 쉽습니다.

Xmatch 함수는 조회 배열에서 조회 값의 상대 위치를 계산하고 인덱스의 Row_num 인수로 전달합니다. 행 번호를 기준으로 인덱스 함수는 지정한 모든 열에서 값을 반환합니다.

예를 들어, E1의 바다 영역을 찾으려면이 공식을 사용할 수 있습니다.

=INDEX(B2:B6, XMATCH(E1, A2:A6))

INDEX XMATCH XMATCH를 사용하여 2 차례 조회를 수행하십시오

열과 행을 동시에 찾으려면 두 가지 Xmatch 함수와 함께 인덱스를 사용하십시오. 첫 번째 XMatch는 행 번호를 얻고 두 번째 XMatch는 열 번호를 검색합니다.

인덱스 ( data , xmatch ( lookup_value , vertical _ lookup_array ), xmatch ( 조회 값 , 수평 _ xovup_array ))

공식은 기본값이 정확히 일치하기 때문에 match_mode 인수를 생략 할 수 있다는 점을 제외하고 인덱스 매치 일치와 유사합니다.

예를 들어, 특정 달 (G2)에 주어진 품목 (G1)의 판매 번호를 검색하기 위해 공식은 다음과 같습니다.

=INDEX(B2:D8, XMATCH(G1, A2:A8), XMATCH(G2, B1:D1))

여기서 B2 : D8은 행 및 열 헤더를 제외한 데이터 셀이며 A2 : A8은 항목 목록이고 B1 : D1은 월 이름입니다.

사례에 민감한 Xmatch 공식

이미 언급했듯이 Excel Xmatch 함수는 설계에 의해 대소 문자를 사용하지 않습니다. 텍스트 케이스를 구별하려면 정확한 기능과 함께 xmatch를 사용하십시오.

매치 ( true, exact

마지막으로 역 순서 로 검색하려면 :

매치 ( true, exact )

다음 예제는이 일반적인 공식을 보여줍니다. B2 : B11에 사례에 민감한 제품 ID 목록이 있다고 가정합니다. E1에서 항목의 상대 위치를 찾고 있습니다. E2의 대소 문자에 민감한 공식은 다음과 같이 간단합니다.

=XMATCH(TRUE, EXACT(B2:B11, E1))

이 공식의 작동 방식 :

정확한 기능은 조회 배열의 각 항목과 조회 값을 비교합니다. 문자 케이스를 포함하여 비교 값이 정확히 같으면 함수는 true, 그렇지 않으면 false를 반환합니다. 이 논리적 값 배열 (True가 정확히 일치하는 곳)은 Xmatch의 Lookup_Array 인수로 이동합니다. 조회 값이 사실이므로 Xmatch 함수는 Search_Mode 인수를 구성한 방법에 따라 첫 번째 발견 된 정확한 일치 또는 마지막 정확한 일치의 위치를 ​​반환합니다.

xmatch vs. Excel에서 일치합니다

Xmatch는 일치를위한보다 강력하고 다재다능한 대체품으로 설계 되었으므로이 두 기능은 공통점이 많습니다. 그러나 필수 차이가 있습니다.

다른 기본 동작

일치 함수는 정확히 일치 또는 다음으로 가장 작은 항목으로 기본적으로 기본적으로 ( match_type 가 1으로 설정되거나 생략 됨).

xmatch 함수는 기본적으로 정확하게 일치합니다 ( match_mode 는 0으로 설정되거나 생략 됨).

대략적인 일치에 대한 다른 동작

match_mode / match_type 인수가 1으로 설정된 경우 :

• 정확한 일치 또는 다음으로 가장 작은 검색을 일치시킵니다. 조회 배열은 오름차순 순서로 정렬해야합니다.
• Xmatch는 정확한 일치 또는 다음으로 가장 큰 검색을 검색합니다. 정렬이 필요하지 않습니다.

match_mode / match_type 인수가 -1로 설정된 경우 :

• 정확한 일치 또는 다음 가장 큰 검색을 일치시킵니다. 조회 배열을 내림차순으로 정렬해야합니다.
• xmatch는 정확한 일치 또는 다음으로 가장 작은 검색을 검색합니다. 정렬이 필요하지 않습니다.

와일드 카드 검색

xmatch와 부분 일치를 찾으려면 match_mode 인수를 2로 설정해야합니다.

일치 함수에는 특수 와일드 카드 매치 모드 옵션이 없습니다. 대부분의 경우 정확히 일치 ( match_type Set Set 0으로 설정)로 구성되며 와일드 카드 검색에도 적합합니다.

검색 모드

새로운 XLookup 함수와 마찬가지로 Xmatch는 검색 방향을 정의 할 수있는 특별한 Search_Mode 인수를 가지고 있습니다.

• 1 또는 생략 (기본값)-가장 먼저 검색.
• -1- 마지막으로 리버스 검색.

그리고 정렬 된 데이터 에서 매우 빠르고 효율적인 이진 검색 알고리즘을 선택하십시오.

• 2- 데이터 정렬 된 오름차순에 대한 이진 검색.
• -2- 데이터 정렬 된 내림차순에 대한 이진 검색.

Half-InterVal 검색 또는 로그 검색 이라고도하는 이진 검색은 배열의 중간 요소와 비교하여 배열 내에서 조회 값의 위치를 ​​찾는 특수 알고리즘입니다. 이진 검색은 일반 검색보다 훨씬 빠르지 만 정렬 된 목록에서만 올바르게 작동합니다. 분류되지 않은 데이터에서는 첫눈에 꽤 정상적으로 보일 수있는 잘못된 결과를 반환 할 수 있습니다.

일치의 구문은 검색 모드 인수를 전혀 제공하지 않습니다.

Xmatch는 배열을 기본적으로 처리합니다

전임자와 달리 Xmatch 함수는 동적 엑셀을 위해 설계되었으며 Ctrl Shift Enter를 누르지 않아도 배열을 기본적으로 처리합니다. 이로 인해 특히 몇 가지 다른 기능을 함께 사용할 때 공식을 훨씬 쉽게 빌드하고 편집 할 수 있습니다. 다음 솔루션을 비교하십시오.

• 사례에 민감한 공식 : xmatch | 성냥
• 일치에 대한 두 열이나 목록을 비교하십시오. Xmatch | 성냥

xmatch 및 일치 가용성

Xmatch는 새로운 기능이며 Microsoft 365 및 Excel 2021 용 Excel에서만 사용할 수 있습니다.

매치 함수는 Excel 2007을 통해 Excel 365의 임의의 모든 버전에서 사용할 수 있습니다.

이것이 xmatch 함수를 Excel에서 사용하는 방법입니다. 읽어 주셔서 감사합니다. 다음 주에 우리 블로그에서 뵙기를 바랍니다!

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

Excel xmatch 공식 예제 (.xlsx 파일)

위 내용은 공식 예제와 함께 Excel xmatch 함수의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!