엑셀 XMATCH 마스터하기: 고급 조회의 궁극적인 가이드

데이터 분석 중에는 종종 목록이나 셀 범위에서 항목의 상대적 위치를 찾아야 할 때가 있습니다. 수년 동안 이는 복잡한 Excel 함수 조합에 의존하는 것을 의미했습니다.

Excel에서 패턴 매칭 작업을 수행하는 가장 효과적인 기본 방법은 XMATCH 함수입니다. 간단하면서도 강력한 XMATCH는 정밀하고 유연한 매칭을 가능하게 하는 여러 기능을 갖추고 있습니다. 하지만 공식을 완전히 우회할 수 있다면 어떨까요?

이 튜토리얼에서는 Excel XMATCH 함수의 기본 사항을 다루고, 구문을 분석하며, 실용적인 예제를 제공할 것입니다. 또한 Excelmatic을 소개합니다. 이는 간단한 언어 프롬프트로 동일한 작업을 수행하여 더 빠르고 직관적인 대안을 제공하는 AI 기반 도구입니다. 마지막으로 XMATCH와 MATCH를 비교하고 두 방법에 대한 모범 사례를 다룰 것입니다.

XMATCH란 무엇인가요?

XMATCH는 Excel에서 매칭 작업을 위한 유연하고 강력한 함수입니다. 수직 및 수평 검색부터 정확한, 근사치, 심지어 부분(와일드카드) 일치까지 모든 종류의 조회를 수행할 수 있게 해줍니다.

Excel을 한동안 사용해 왔다면 MATCH 함수에 익숙할 것입니다. XMATCH가 도입되기 전에는 MATCH가 이러한 작업을 위한 핵심 함수였습니다. 그러나 MATCH에는 한계가 있습니다. XMATCH는 오랫동안 기다려온 업그레이드로, 모든 방향으로의 검색과 여러 일치 유형을 제공하여 더 넓은 범위의 사용 사례에 적합합니다.

XMATCH는 상당한 발전이지만, 데이터 조회의 다음 진화는 인공 지능을 포함합니다. Excelmatic과 같은 도구는 함수 구문을 암기할 필요를 없애고, 단순히 목표를 진술하기만 하면 됩니다.

XMATCH는 Microsoft 365용 Excel과 Excel 2021에서만 사용 가능합니다. 이전 버전 사용자의 경우 MATCH가 유일한 내장 옵션입니다.

이제 XMATCH 구문과 매개변수를 살펴보겠습니다.

XMATCH 구문 및 매개변수

다음은 XMATCH 함수의 기본 구문입니다:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

이 함수에는 두 개의 필수 인수(lookup_value 및 lookup_array)와 더 정밀하고 유연한 작업을 위한 두 개의 선택적 인수(match_mode 및 search_mode)가 있습니다.

• Lookup_value: 찾고자 하는 값입니다.
• Lookup_array: 검색하려는 배열 또는 목록입니다.
• Match_mode: 일치 유형을 지정합니다.
  • 0: 정확한 일치 (기본값).
  • -1: 정확한 일치 또는 다음으로 작은 항목.
  • 1: 정확한 일치 또는 다음으로 큰 항목.
  • 2: * 및 ?와 같은 문자를 사용하는 와일드카드 일치.
• Search_mode: 검색 방향과 알고리즘을 지정합니다.
  • 1: 처음부터 마지막까지 검색 (기본값).
  • -1: 마지막부터 처음까지 검색 (역방향 검색).
  • 2: 오름차순으로 정렬된 데이터에 대한 이진 검색.
  • -2: 내림차순으로 정렬된 데이터에 대한 이진 검색.

강력하지만, 이러한 매개변수와 해당 숫자를 기억하는 것은 번거로울 수 있습니다. 이것이 AI 접근 방식이 명확한 이점을 제공하는 부분입니다.

일치 모드: 정밀성과 유연성

XMATCH의 선택적 match_mode 매개변수를 사용하면 정밀하고 근사치 매칭을 모두 수행할 수 있습니다. 어떻게 작동하는지 살펴보고 더 현대적인 방법과 비교해 보겠습니다.

정확한 일치

정확한 일치(match_mode = 0)는 기본값입니다. Excel은 조회 값에 대한 정확한 일치를 찾으며, 그렇지 않으면 #N/A 오류를 반환합니다. 정확성이 핵심일 때 필수적입니다.

예를 들어, DataCamp Course Catalog의 기술 목록에서 "Excel"의 정확한 위치를 찾으려면 =XMATCH(D3, A3:A14, 0) 공식을 사용합니다. Excel은 올바르게 위치 6을 반환합니다.

Excelmatic 방법: 그냥 물어보세요

Excelmatic을 사용하면 공식이 필요 없습니다. 파일을 업로드하고 평범한 언어로 질문하기만 하면 됩니다:

기술 목록에서 'Excel'의 위치는 무엇인가요?

Excelmatic은 요청을 이해하고 즉시 답변을 제공하여 공식을 작성하고 디버깅하는 데 드는 시간과 노력을 절약해 줍니다.

근사치 일치

때로는 유연한 일치가 필요합니다. XMATCH를 사용하면 다음으로 작은 값(match_mode = -1) 또는 다음으로 큰 값(match_mode = 1)을 찾을 수 있습니다.

아래 예에서는 15에 가장 가까운 강좌 수를 찾습니다.

• match_mode = -1을 사용하면 공식은 위치 6(12개 강좌, 가장 가까운 작은 숫자)을 반환합니다.
• match_mode = 1을 사용하면 위치 5(16개 강좌, 가장 가까운 큰 숫자)를 반환합니다.

Excelmatic 방법: 간단하고 직접적

어떤 숫자(-1 또는 1)가 무엇을 하는지 기억하는 대신, Excelmatic에 직접 물어볼 수 있습니다:

15에 가장 가까운 강좌 수의 위치를 찾되, 초과하지 않는 것으로.

또는:

15이거나 그 다음으로 높은 강좌 수의 위치를 찾으세요.

Excelmatic은 의도를 해석하고 함수 문서를 참조할 필요 없이 올바른 결과를 제공합니다.

와일드카드 일치

XMATCH의 훌륭한 기능은 match_mode가 2로 설정되었을 때 와일드카드를 사용할 수 있다는 것입니다. 이는 텍스트 데이터에 대해 작동합니다.

• ?: 임의의 단일 문자와 일치합니다.
• *: 임의의 문자 시퀀스와 일치합니다.

아래 예에서는 =XMATCH("E*", A3:A14, 2) 공식을 사용하여 "E"로 시작하는 첫 번째 기술을 찾습니다.

Excelmatic 방법: 자연어 패턴

Excelmatic을 사용하면 패턴을 자연스럽게 설명할 수 있습니다:

'E'로 시작하는 첫 번째 기술의 위치는 무엇인가요?

또는 더 복잡한 패턴의 경우:

세 글자인 첫 번째 기술의 위치를 찾으세요.

이는 와일드카드와 특정 일치 모드로 공식을 구성하는 것보다 훨씬 더 직관적입니다.

XMATCH 검색 모드: 방향과 효율성

XMATCH는 또한 검색 방향을 제어할 수 있게 해주며, 이는 시계열에서 가장 최근 항목을 찾는 것과 같은 작업에 유용합니다.

역방향 검색

역방향 검색(마지막에서 처음으로)을 수행하려면 search_mode 매개변수를 -1로 설정하세요. 아래 예에서는 =XMATCH("Peter", B3:B14, 0, -1) 공식으로 사용자 "Peter"가 마지막으로 완료한 강좌를 찾습니다.

Excelmatic 방법: 명확성이 핵심입니다

-1이 역방향 검색을 의미한다는 것을 외울 필요가 없습니다. Excelmatic을 사용하면 의도가 명확합니다:

Peter가 마지막으로 완료한 강좌의 위치를 찾으세요.

Excelmatic은 간단한 언어 명령을 기반으로 첫 번째, 마지막 또는 N번째 발생을 찾는 논리를 처리합니다.

이진 검색

매우 크고 정렬된 데이터 세트의 경우, XMATCH는 엄청난 속도를 위해 이진 검색 알고리즘(search_mode = 2는 오름차순, -2는 내림차순)을 사용할 수 있습니다. 그러나 이는 데이터가 미리 정렬되어 있어야 합니다. Excelmatic은 알고리즘을 지정하거나 데이터를 미리 정렬할 필요 없이 검색 프로세스를 자동으로 최적화합니다.

XMATCH의 실용적인 응용 및 예제

XMATCH는 강력하지만 다른 함수와 결합하면 복잡한 중첩 공식으로 이어질 수 있습니다. AI 접근 방식이 이러한 고급 작업을 어떻게 단순화하는지 살펴보겠습니다.

INDEX/XMATCH를 사용한 2차원 조회

일반적인 작업은 특정 행과 열의 교차점에서 값을 조회하는 것입니다. 기존 Excel을 사용하면 INDEX와 두 개의 XMATCH 함수를 결합해야 합니다.

아래 예에서 =INDEX(C3:F14, XMATCH(I2, A3:A14, 0), XMATCH(I3, C2:F2, 0)) 공식은 "Q2"에서 "Excel" 강좌의 등록 사용자 수를 찾습니다.

Excelmatic 방법: 좌표가 아닌 답을 얻으세요

Excelmatic을 사용하면 행과 열 번호를 찾는 중간 단계를 건너뛰고 최종 답변을 직접 요청합니다:

Q2에서 Excel 강좌의 등록 사용자 수는 얼마였나요?

Excelmatic은 데이터 간의 관계를 이해하고, 2차원 조회를 수행하며, 필요한 값을 제공합니다. 길을 묻는 것과 즉시 목적지로 순간이동하는 것의 차이입니다.

다중 열 조회

여러 기준을 충족하는 행을 찾아야 한다면 어떨까요? XMATCH를 사용하면 =XMATCH(1, (C3:C14<1000)*(D3:D14<1000)*(E3:E14<1000)*(F3:F14<1000), 0)과 같은 부울 논리를 사용하는 복잡한 배열 공식이 포함됩니다. 이 공식은 작성하기 어렵고 디버깅하기는 더 어렵습니다.

Excelmatic 방법: 원하는 것을 설명하세요

부울 배열은 잊어버리세요. Excelmatic에 조건을 설명하기만 하면 됩니다:

모든 분기에서 등록 사용자가 1000명 미만인 첫 번째 기술을 찾으세요.

이는 더 간단할 뿐만 아니라 가독성이 높고 오류 가능성이 적습니다.

동적 배열 필터링

XMATCH, ISNUMBER, FILTER를 결합하여 동적 필터를 만들 수 있습니다. 이 프로세스에는 유지할 행을 결정하기 위한 도우미 열 또는 복잡한 중첩 공식을 만드는 것이 포함됩니다.

Excelmatic 방법: 필터링이 쉬워집니다

필터링은 Excelmatic의 핵심 강점입니다. 기준을 명시하기만 하면 됩니다:

Sam과 Peter가 완료한 강좌만 표시하도록 테이블을 필터링하세요.

Excelmatic은 도우미 열이나 다단계 공식 구성 없이 즉시 필터링된 테이블을 생성합니다.

XMATCH 대 MATCH: 주요 차이점

XMATCH는 MATCH를 대체하도록 설계되었습니다. 이전 함수에 익숙한 분들을 위한 빠른 비교입니다:

일반적인 함정 및 해결책

XMATCH 또는 Excelmatic을 사용하든, 다음은 몇 가지 팁입니다:

• 데이터 유형 확인: XMATCH는 데이터 유형(예: 텍스트 대 숫자)에 민감합니다. 데이터가 깨끗한지 확인하세요. Excelmatic은 종종 더 관대하지만 좋은 데이터 위생은 항상 최선입니다.
• #N/A 오류 처리: 일치하는 항목이 없으면 XMATCH는 #N/A 오류를 반환합니다. 사용자 정의 메시지를 표시하기 위해 IFNA 또는 IFERROR 함수로 감쌀 수 있습니다. Excelmatic을 사용하면 "일치하는 항목을 찾을 수 없습니다."와 같은 명확하고 사람이 읽을 수 있는 응답을 받습니다.
• 일치 모드 활용: 공식을 고수한다면 XMATCH의 전체 성능을 활용하기 위해 다양한 match_mode 및 search_mode 옵션을 이해하세요. Excelmatic을 사용한다면 질문을 최대한 명확하게 표현하는 데 집중하세요.

성능 고려 사항

거대하고 정렬된 데이터 세트에 대한 원시 계산 속도 측면에서, 잘 만들어진 XMATCH 이진 검색은 엄청나게 빠릅니다. 그러나 실제 비즈니스 시나리오의 대부분에서 병목 현상은 함수의 실행 속도가 아니라 사람이 복잡한 공식을 작성, 테스트 및 디버깅하는 데 걸리는 시간입니다.

이것이 Excelmatic이 엄청난 성능 향상을 제공하는 부분입니다. 복잡한 공식을 구축하는 대신 간단한 언어 프롬프트를 사용하여 절약된 시간은 특히 다단계 작업의 경우 몇 분, 심지어 몇 시간으로 측정될 수 있습니다.

결론

XMATCH는 진지한 Excel 사용자에게 강력하고 필수적인 함수입니다. 레거시 MATCH 함수에 비해 상당한 개선을 제공하며 다양한 조회 시나리오를 처리할 수 있습니다.

그러나 데이터 분석의 세계는 진화하고 있습니다. AI 기반 도구인 Excelmatic은 다음 도약을 나타냅니다. 자연어를 사용하여 질문할 수 있게 함으로써 함수와 공식의 가파른 학습 곡선을 제거하고, 오류를 줄이며, 워크플로를 극적으로 가속화합니다.

완벽한 공식을 만드는 것을 좋아하는 사람들에게 XMATCH는 숙달해야 할