■ 목차
1. 함수 설명
2. Xmatch 함수의 구문 / 사용방법
3. Xmatch 함수 사용해 보기(1) - 일치값 찾기 (Xmatch, Index 함수)
4. Xmatch 함수 사용해 보기(2) - 근삿값 찾기 (Xmatch, Index 함수)
5. Xmatch 함수 응용 - 다중 조건 찾기
** 핵심 요약
- Xmatch 함수는 배열 또는 셀 범위에서 해당 항목을 찾아 상대적 위치를 반환하는 "동적배열 함수"입니다.
- 기존 Match 함수에서 기능이 확장된 상위 호환 함수로 최신 엑셀 프로그램(엑셀 2021, MS 365)에서 사용이 가능한 함수입니다.
- 찾는 항목이 몇 번째 있다는 위치를 정수로 반환하는 함수라서 보통 특정 위치의 값을 찾아오는 Index 함수와 같이 사용됩니다.
- 기존의 Match 함수에서 향상된 기능과 다양한 예제를 통해서 사용방법을 알아보도록 하겠습니다.
- "상위 호환"이란 의미는 Match기능을 완벽하게 대체를 하고 추가로 향상된 기능을 제공하기 때문에
- 기존의 Match 함수를 계속사용하는 것은 가능하지만 엑셀에서는 최신 함수인 Xmatch 함수 사용을 추천한다는 의미입니다.
- 또한 Xmatch 함수는 "동적배열 함수"이기 때문에 여러 개( = 배열)의 데이터를 한 번에 찾는 것이 가능합니다.
1. 함수 설명
- Xmatch 함수는 찾고자 하는 값을 지정된 범위에서 다양한 방법(일치, 작은 값, 큰 값, 위에서 부터 찾기, 아래서 부터 찾기)으로 검색하여 해당하는 값의 상대적 위치를 정수로 반환하는 함수입니다.
- Match 함수와 사용법이 유사하지만 좀 더 강력한 기능으로 업그레이드된 찾기 함수라고 이해하시면 됩니다.
- Match 함수나 Xmatch 함수의 기능으로 우리가 알아야 하는 부분은 이 두 함수는 모두 찾기 기능으로 해당 값의 "위치"를 찾아 준다는 것입니다.
- 찾는 값이 있는 경우 지정된 범위에서 몇 번째 있다는 정수값이 반환되고 보통은 위치에 대한 정수값 만으로는 우리가 원하는 최종 결괏값을 얻을 수 없기 때문에
- 거의 대부분 해당 위치와 관련된 특정값을 가져오는 기능인 Index 함수를 같이 사용을 합니다.
- 그리고 "동적배열 함수"이기 때문에 한 번에 여러 값들을 찾는 것이 가능하고
- 수식 입력도 기존 배열수식 입력을 위한 "Ctrl + Shift + Enter"로 마무리할 필요 없이
- 일반 함수의 수식입력과 동일하게 "Enter"로 수식입력을 마무리하시면 됩니다.
- 기존의 Match 함수와의 차이를 간단하게 구분해 보면 아래와 같으며 예제를 통해서 알아보도록 하겠습니다.
- ① 근삿값 찾기에서 정렬이 불필요해짐 - Very Good!
- ② 역방향 찾기 지원
- ③ 이진검색 지원
2. Xmatch 함수의 구문 / 사용방법
- 아래 구문은 엑셀 도움말의 설명 내용입니다. 우리는 엑셀 함수의 구문을 다 외울 수도 없고 그럴 필요도 없습니다.
- 다만 엑셀 도움말과 함수사용 시 표시되는 풍선도움말의 용어들에 익숙해질 필요가 있기 때문에 기회가 될 때마다 편하게 봐주시기 바랍니다.
- XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
- lookup_value 보기 값
- lookup_array 검색할 배열 또는 범위
- [match_mode] 일치 유형을 지정합니다.
- [search_mode] 검색 유형을 지정합니다.
- 첫 번째 인수는 lookup_value입니다. 찾고자 하는(lookup)는 값(value)으로 텍스트, 숫자 모두 가능합니다.
- 텍스트를 수식에 직접 입력하는 경우 반드시 큰 따옴표( " " )를 사용하여 텍스트임을 명시적으로 표시 해줘야 합니다.
- 두 번째 인수 lookup_array는 찾고자 하는(lookup)는 범위(array)로 검색할 범위에 해당하는 셀 범위 주소를 마우스 선택으로 지정해 주시면 됩니다.
- 행방향, 열 방향 모두 설정이 가능하지만 어떤 경우든 한 줄( = 한 개의 행 혹은 한 개의 열)만 선택해주셔야 합니다.
- 세 번째 인수 match_mode는 일치 옵션으로 기본값은 숫자 0으로 "정확히 일치"하는 값을 찾는 것입니다.
- 여기서부터 기존의 Match 함수와 차이가 좀 나는데 기존의 Match 함수의 기본값은 숫자 1로 초과하지 않는 가장 큰 값이었습니다.
- 그리고 옵션으로 숫자 2가 새로 생겼으며 이 경우 "와일드카드 일치"로 와일드카드( "?", "*")를 사용하여 찾기가 가능합니다.
- 기존 Match 함수의 경우 옵션이 "일치값 찾기"이고 찾는 값이 "텍스트"인 경우 그냥 와일드카드를 사용할 수 있었습니다.
- 숫자 0 : 정확히 일치 (기본값)
- 숫자 -1 : 정확히 일치하거나 작은 값 = Max( ≤ 찾는 값) → 같거나 "작은" 쪽에서 찾기 (음의 숫자 1) = "작은 쪽"에서 가장 큰 값 찾기
- 숫자 1 : 정확히 일치하거나 큰 값 = Min( ≥찾는 값) → 같거나 "큰" 쪽에서 찾기 (양의 숫자 1) = "큰 쪽"에서 가장 작은 값 찾기
- 숫자 2 : 와일드카드(*,?, ~)를 적용하여 값을 찾는 옵션입니다.
- 와일드카드는 "서울*"과 같이 "서울"로 시작하는 모든 문자를 찾는다는지 하는 방식의 확장된 검색방식으로
- 여러 문자를 대체할 수 있는 "*" (별표 = Asterisk, 애스터리스크)와 한 문자를 대체할 수 있는 "?" (물음표)
- 그리고 "~*", "~?" ( 물결 기호 = Tilde, 틸드)와 같이 사용해서 와일드카드 문자 자체를 찾고자 하는 경우처럼 사용이 가능합니다.
- 네 번째 인수는 search_mod로 검색 유형을 지정합니다. 검색 방향과 이진 검색을 지정할 수 있습니다.
- 숫자 1 : 첫 번째에서 마지막까지 순서(방향)인 "정방향"으로 검색하고 생략 시 기본값으로 사용됩니다.
- 숫자 -1 : 역 방향 검색으로 끝에서부터 검색을 하게 됩니다. 찾고자 하는 값이 여러 개 일 때 마지막 값을 찾을 수 있습니다.
- 숫자 -2, 2 : 이진 검색 적용 옵션입니다. 굉장히 많은 데이터에서 빠르게 검색이 가능한 검색 알고리즘으로 반드시 정렬이 되어 있어야 합니다.
- 즉, 굉장히 많은(?) 데이터가 아니면 그렇게 실감할 수 없는 옵션으로 일반적인 데이터의 검색에서는 크게 활용되지 않을 듯합니다.
- 기존의 Match 함수는 근삿값 찾기를 사용하기 위해서는 먼저 데이터가 정렬이 되어 있어야 하는데 반해서
- Xmatch 함수는 이진 검색을 사용하는 경우가 아니면 정렬을 할 필요가 없습니다.
- "이진 검색" 이란 데이터를 순서대로 모두 비교하는 것이 아니라 정렬이 된 데이터에서 임의 중간위치의 값을 선택해서 찾고자 하는 값과 비교하고
- 찾는 값이 아닌 경우에는 정렬된 데이터에서 한쪽 절반의 데이터는 찾는 데이터가 없는 것을 확신할 수 있기 때문에 버리고
- (정렬되어 있고 비교하는 숫자보다 찾는 값이 더 큰지, 작은 지를 알 수 있기 때문에 한쪽 절반의 데이터는 찾기 대상에서 버릴 수 있습니다.)
- 나머지 절반의 데이터에서 다시 중간 위치의 값을 선택해서 비교하면서 찾는 값에 접근하는 방식으로 검색하는 방법입니다.
- 우리는 정렬하는 것도 귀찮고 엄청나게 많은 데이터도 아니기 때문에 그렇게 사용할 일은 없을 듯합니다.
3. Xmatch 함수 사용해 보기(1) - 일치값 찾기 (Xmatch, Index 함수)
- 가장 기본적인 형태인 Xmatch 함수의 일치값 찾기입니다.
- 다시 한번 얘기드리지만 Xmatch 함수는 찾고자 하는 값의 "위치"를 찾아 주는 함수입니다.
- 그래서 해당하는 위치를 기반으로 원하는 값을 반환하기 위해서는 반드시 Index 함수를 함께 사용해줘야 합니다.
- ① Xmatch 함수로 일치하는 학생의 위치를 찾은 후 ② Index 함수로 점수를 가져오는 방식의 예제를 살펴보도록 하겠습니다.
- 1) 함수 입력 : =XMATCH(
- 수식 입력을 위한 등호 ( " = " )와 함수 Xmatch를 입력하고 인수 입력을 위해서 괄호 열기를 키보드로 입력합니다.
- 2) 첫 번째 인수 lookup_value 입력하기 : =XMATCH(E3,
- 첫 번째 인수는 lookup_value로 찾고자(lookup)하는 값(value)에 해당하는 "최민서"가 입력된 E3 셀을 마우스로 선택 혹은 키보드로 이동하여 입력하여 줍니다.
- 그리고 두 번째 인수 입력을 위해서 인수 구분자 쉼표( " , ")를 키보드로 입력합니다.
- 3) 두 번째 인수 lookup_array 입력하기 : =XMATCH(E3, B3:B7,
- 두 번째 인수는 lookup_array로 찾을(lookup) 범위(array)로 이름값이 입력되어 있는 원본 데이터의 B3:B7 범위를 마우스 드래그로 선택하여 셀 주소를 입력합니다.
- 그리고 다음 인수 입력을 위해서 인수 구분자 쉼표( " , ")를 키보드로 입력합니다.
- 4) 세 번째 인수 match_mode 입력하기 : =XMATCH(E3, B3:B7, 0,
- 세 번째 인수는 match_mode로 일치 옵션으로 정확하게 일치값을 찾기 위해서 숫자 0을 입력합니다.
- 정확하게 일치하는 값 찾기는 기본 옵션값으로 생략하는 것도 가능합니다.
- 그리고 다음 인수 입력을 위해서 인수 구분자 쉼표( " , ")를 키보드로 입력합니다.
- 5) 네 번째 인수 search_mode 입력하기 : =XMATCH(E3, B3:B7, 0, 1)
- 네 번째 인수는 search_mod로 검색 방향과 이진 검색을 지정할 수 있습니다.
- 검색 방향의 옵션이 필요한 경우는 중복의 값이 있는 경우 첫 번째 값을 찾을 것인지 아니면 마지막 값을 찾을 것인지를 설정가능한데
- 현재의 경우 일치되는 값이 하나뿐이기 때문에 크게 의미가 없는 옵션입니다.
- 우리는 정방향으로 숫자 1을 입력하고 정방향(순방향)의 경우 기본 옵션값이기 때문에 생략하는 것도 가능합니다.
- 그리고 마지막으로 괄호 닫기와 "Enter"키를 눌러 수식입력을 마무리합니다.
- 세 번째 (match_mode)와 네 번째(search_mode) 인수의 경우 기본값인 "정확하게 일치"와 "정방향"에 해당함으로 생략이 가능하고
- 모두 생략하는 경우 아래와 같이 간단한 수식으로 Xmatch 함수를 동일하게 사용할 수 있습니다.
- 수식 입력(기본 옵션 생략) : =XMATCH(E3, B3:B7)
- 6) Index 함수로 성적 찾아오기
- 여기까지는 Xmatch 함수로 원하는 값의 위치를 찾는 과정이었습니다.
- 그리고 실제로 우리가 알고 싶은 "최민서"학생의 "성적"까지 찾기 위해서는 Xmatch 함수의 결괏값을 이용하여
- Index 함수를 사용해줘야 합니다. Index 함수는 지정한 위치의 값을 반환하는 함수로
- Xmatch 함수의 "최민서"학생의 위치인 숫자 3에 해당하는 3번째 위치의 성적을 가져오는 방식으로 사용을 하게 됩니다.
- 아래 수식을 참조하시기 바랍니다.
- 입력 수식 : =INDEX(C3:C7, F3)
4. Xmatch 함수 사용해 보기(2) - 근삿값 찾기 (Xmatch, Index 함수)
- 다음은 근삿값 찾기로 각각의 성적에 해당하는 등급을 자동으로 부여하는 방법을 알아보도록 하겠습니다.
- 여기서 기존의 Match 함수와 다르게 개선된 중요한 기능상의 차이 있는데
- 기존의 Match 함수에서 근삿값 찾기에서는 선행 조건이 작은 값을 찾거나 큰 값을 찾는 경우 반드시 정렬이 되어 있어야 했지만
- Xmatch 함수의 경우 이러한 사전 정렬이 더 이상 필요하지 않게 되었습니다.^^
- 1) Xmatch, match 함수 - 근삿값 위치 찾기
- 아래와 같이 등급기준이 정렬이 되어있지 않은 상태에서 Match 함수와 Xmatch 함수의 차이를 확인해 보시기 바랍니다.
- 이 경우 match_mode 인수의 경우 숫자 1을 입력하여 "정확하게 일치하거나 큰 값"을 찾을 수 있게 설정합니다.
- Match 함수, Xmatch 함수에 사용하는 인수는 이 경우 모두 동일합니다.
- lookup_array 인수는 수식 복사를 위해서 F4 키를 사용하여 절대참조 형식 ($H$3:$H$6)의 셀 주소로 입력해 줍니다.
- 사용 수식 (Match 함수) : =MATCH(C3,$H$3:$H$6,1)
- 사용 수식 (Xmatch 함수) : =XMATCH(C3,$H$3:$H$6,1)
- 2) Xmatch 함수 - 근삿값 위치에 해당하는 등급 찾기 (Index 함수)
- Xmatch 함수로 찾은 위치에 대한 정보를 이용하여 Index 함수로 해당하는 위치의 등급을 가져오는 방식입니다.
- Index 함수에서 등급범위는 수식 복사를 위해서 "F4"를 사용해서 "절대 참조"형식 ($I$3:$I$6)의 셀 주소로 입력해 줍니다.
- 적용 수식 : =INDEX($I$3:$I$6, E3)
5. Xmatch 함수 응용 - 다중 조건 찾기
- 2개 이상의 다중 조건을 만족하는 값을 찾아오는 예제입니다.
- 물론 Xmatch 함수는 위치만을 찾을 수 있고 위치에 해당하는 다른 값을 가져오기 위해서는 Index 함수를 함께 사용해줘야 합니다.
- 여러 조건을 만족하는 값을 가져오기 위해서는 먼저 "배열 수식"의 이해가 필요합니다.
- 배열 수식에 대한 지식이나 경험이 없으신 분들은 그냥 참조해 주시기 바랍니다.
- 그리고 최신 엑셀 (엑셀 2021, MS 365)에서는 배열 수식입력에 더 이상 "Ctrl + Shift + Enter"를 입력해서 수식을 마무리할 필요가 없어졌습니다.
- 일반 수식과 동일하게 "Enter"키로 배열 수식입력이 가능하게 되었으며 "동적 배열"이 자동으로 적용되게 되었습니다.
- 배열과 동적 배열에 대해서는 따로 공부를 해주시기 바라며 오늘은 이를 이용한 다중 조건 찾기 예제를 보여드리도록 하겠습니다.
- 다중 조건의 경우 A도 만족하고 B도 만족하는 And 조건을 말하며 이 경우 (조건 1) * (조건 2)와 같이 곱하기를 해주게 됩니다.
- 아래 예제는 "소속 = 1팀"이고 (And) "성명 = 강수빈"인 경우의 "소재지"룰 찾아오는 예제입니다.
- Xmatch 함수 적용 수식 : =XMATCH(1,((B3:B7=B10)*(C3:C7=C10)),0)
- 1) Xmatch 함수 - 배열 수식으로 다중 조건 찾기
- 이 경우 ① 찾고자 하는 값은 "숫자 1 ( = True = 참)"이 됩니다. 2가지 조건을 모두 만족하는 경우 참(True)이 되고 엑셀의 겨우 True = 숫자 1로 취급을 하기 때문입니다.
- ② 찾는 범위는 "(소속 = "2팀") And (성명 = "강수빈")의 조건 만족여부의 배열이 되고
- (소속 = "2팀") = {False; Fasle; True; True; False} = {0 ; 0 ; 1 ; 1 ; 0}
- (이름 = "강수빈") = {True ; False ; True ; Fasle ; Fasle} = { 1 ; 0 ; 1 ; 0 ; 0 }
- (소속 = "2팀") * (이름="강수빈") = {0 ; 0 ; 1 ; 1 ; 0} * { 1 ; 0 ; 1 ; 0 ; 0 }
- { 0 *1 ; 0 * 0 ; 1 * 1 ; 1 * 0, 0 * 0} = {0 ; 0 ; 1 ; 0 ; 0 }
- Xmatch(1, {0 ; 0 ; 1; 0 ; 0}, 1) = 3과 같이 2가지 모두 만족하는 값이 3번째 있다는 결괏값을 가져올 수 있게 됩니다.
- 배열 수식에 경험이 없으신 경우 조금 어려울 수 도 있는 게 그냥 이런 게 있다 정도를 보시고 넘어가셔도 됩니다.
- 단 최신 엑셀이 아닌 경우에는 배열 수식 입력 후 마지막에 반드시 "Ctrl + Shift + Enter"로 수식 입력을 마무리해 주셔야 합니다.
- 2) Xmatch 함수 - Index 함수로 소재지 찾기
- Index 함수로 Xmatch 함수에서 찾은 위치 정보( 숫자 3)를 이용해서 소재지 값을 가져오는 수식입니다.
- 적용 수식 : =INDEX(D3:D7, C12)
- 3) Index / Xmatch 함수 - 한 번에 소재지 찾기
- Index, Xmatch 함수를 한 번에 사용해서 다중 조건의 소재지를 찾는 수식입니다.
- 단순히 함수 안에 함수를 사용하는 방식으로 2개의 수식을 하나로 만든 것에 불가하지만 어떻게 구성되는지 잘 살펴보시기 바랍니다.
- 적용 수식 : =INDEX(D3:D7, XMATCH(1,((B3:B7=B10)*(C3:C7=C10)),0))
** 핵심 요약 : Xmatch 함수 사용법 - 특정값의 위치 찾기, Index Xmatch 함수, 다중 조건 찾기
1. 함수 설명
- Xmatch 함수는 배열 또는 셀 범위에서 항목을 찾아 상대적 위치를 반환하는 "동적배열 함수"이다.
- 찾고자 하는 값을 지정된 범위에서 다양한 방법(일치, 작은 값, 큰 값, 위에서 부터 찾기, 아래서 부터 찾기)으로 검색하여 해당하는 값의 상대적 위치를 정수로 반환하는 함수이다.
- 기존 Match 함수에서 기능이 확장된 상위 호환 함수로 최신 엑셀 프로그램(엑셀 2021, MS365)에서 사용이 가능한 함수이다.
- 거의 대부분 우리가 원하는 최종값을 가져오기 위해 해당 위치와 관련된 특정값을 가져오는 기능인 Index 함수를 같이 사용한다.
- 기존의 Match 함수와의 차이는 아래와 같다.
- ① 근삿값 찾기에서 정렬이 불필요해짐
- ② 역방향 찾기 지원
- ③ 이진검색 지원
2. Xmatch 함수의 구문 / 사용방법
- XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
- lookup_value 보기 값
- lookup_array 검색할 배열 또는 범위
- [match_mode] 일치 유형을 지정합니다.
- [search_mode] 검색 유형을 지정합니다.
- 첫 번째 인수 lookup_value는 찾고자 하는(lookup)는 값(value)으로 텍스트, 숫자 모두 가능하다.
- 두 번째 인수 lookup_array는 찾고자 하는(lookup)는 범위(array)로 검색할 범위에 해당하는 셀 범위 주소를 마우스 선택으로 지정해 준다.
- 세 번째 인수 match_mode는 일치 옵션으로 기본값은 숫자 0으로 "정확히 일치"하는 값을 찾는 것이다.
- 네 번째 인수 search_mode는 검색 유형 지정으로 검색 방향과 이진 검색을 지정할 수 있다.
3. Xmatch 함수 사용해 보기(1) - 일치값 찾기 (Xmatch, Index 함수)
- 가장 기본적인 형태인 Xmatch 함수의 일치값을 찾아보자, Xmatch 함수는 찾고자 하는 값의 "위치"를 찾아 주는 함수이다.
- ① Xmatch 함수로 일치하는 학생의 위치를 찾은 후 ② Index 함수로 점수를 가져오는 방식의 예제이다.
- 1) 함수 입력 : =XMATCH(
- 2) 첫 번째 인수 lookup_value 입력하기 : =XMATCH(E3,
- 3) 두 번째 인수 lookup_array 입력하기 : =XMATCH(E3, B3:B7,
- 4) 세 번째 인수 match_mode 입력하기 : =XMATCH(E3, B3:B7, 0,
- 5) 네 번째 인수 search_mode 입력하기 : =XMATCH(E3, B3:B7, 0, 1)
- 수식 입력(기본 옵션 생략 시) : =XMATCH(E3, B3:B7)
- 6) Index 함수로 성적 찾아오기 : =INDEX(C3:C7, F3)
4. Xmatch 함수 사용해 보기(2) - 근삿값 찾기 (Xmatch, Index 함수)
- 1) Xmatch, match 함수 - 근삿값 위치 찾기
- 사용 수식 (Match 함수) : =MATCH(C3,$H$3:$H$6,1)
- 사용 수식 (Xmatch 함수) : =XMATCH(C3,$H$3:$H$6,1)
- Match 함수의 경우 기준값이 정렬이 되어 있지 않으면 근삿값 찾기에서 오류가 발생된다.
- Xmatch 함수는 기준값 정렬 없이 사용 가능하다.
- 2) Index 함수 - 근삿값 위치에 해당하는 등급 찾기
5. Xmatch 함수 응용 - 다중 조건 찾기
- 여러 조건을 만족하는 값을 가져오기 위해서는 먼저 "배열 수식"의 이해가 필요하다.
- 다중 조건의 경우 A도 만족하고 B도 만족하는 And 조건을 말하며 이 경우 (조건 1) * (조건 2)와 같이 곱하기를 해주면 된다.
- 예제는 "소속 = 1팀"이고 (And) "성명 = 강수빈"인 경우의 "소재지"룰 찾아오는 예제이다.
- Xmatch 함수 적용 수식 : =XMATCH(1,((B3:B7=B10)*(C3:C7=C10)),0)
- (소속 = "2팀") * (이름="강수빈") = {0 ; 0 ; 1 ; 1 ; 0} * { 1 ; 0 ; 1 ; 0 ; 0 } = {0 ; 0 ; 1 ; 0 ; 0 }
- 2) Xmatch 함수 - Index 함수로 소재지 찾기
- 적용 수식 : =INDEX(D3:D7, C12)
- 3) Index / Xmatch 함수 - 수식을 한 번에 적용 소재지 찾기
- 적용 수식 : =INDEX(D3:D7, XMATCH(1,((B3:B7=B10)*(C3:C7=C10)),0))
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글