Spread.NET 13 Winforms는 XLOOKUP과 XMATCH라는 중요한 새 Excel 계산 함수를 지원합니다. 이 새로운 기능은 최근 Excel에 추가된 기능입니다.

참고 : 첫 번째 Spread.NET 13 서비스 릴리스(버전 13.45.20191.1)는 현재 제공되고 있으며 글의 콘텐츠는 최신 상태로 정확하게 작성해야 합니다. 새로운 릴리스는 XLOOKUP 함수를 향상시켜 새로운 인자 if_not_found 를 지원합니다. Spread.NET 13 WinForms를 이미 설치한 고객은 업데이트를 하기 전에 제어판 – 프로그램 및 기능을 사용하여 초기 v13.45.20191.0 릴리스를 제거해야 합니다!

XLOOKUP

XLOOKUP 함수는 지정된  match_mode와 search_mode을 사용하여 lookup_array에서 특정 lookup_value 조회한 다음,  return_array의 해당 셀에서 값을 반환합니다.

XLookUp 구문

XLOOKUP 함수는 기존 LOOKUP , VLOOKUP과 HLOOKUP의 함수를 대체할 수 있는 새로운  검색 기능이며 이 새로운 함수가 더 좋은 이유는 아래와 같습니다.
 

1. XLOOKUP은 lookup_array의 방향에 따라 세로 또는 가로 검색을 수행할 수 있습니다 (또는 중첩된 경우 둘 다).
2. XLOOKUP은 데이터가 정렬되지 않은 경우에도 (HLOOKUP/VLOOKUP과 달리) 정확한 결과로 정확하지 않은 조회를 수행할 수 있습니다 .
3. XLOOKUP은 lookup_array 및 return_array를 포함한 전체 범위를 참조할 필요가 없으며  두 특정 범위만 필요하므로 XLOOKUP은 필요한 재계산 측면에서 더 효율적일 수 있습니다.
4. XLOOKUP 인자는 인덱스 대신에 범위 참조를 사용하므로 lookup_array 및 return_array 를 이동하는 열 또는 행을 삽입하거나 제거될 때 자동으로 조정됩니다 .
5. XLOOKUP은 if_not_found 인자를 지정할 때, #N/A 대신에 0 또는 다른 유용한 값을 반환할 수 있습니다 .
6. XLOOKUP은 0- 모두 일치하는 모든 항목을 배열로 반환하는 새로운 search_mode 를 지원하기 위해 Spread.NET 13 WinForms에서 향상되었습니다. 동적 배열이 활성화될 때, 인접 셀로 넘어갈 수 있습니다.

실제로 XLOOKUP 함수를 설명하기 위해 XLookupData1 이라는 샘플 테이블이 있습니다.

예제 1은 가장 일반적인 사용 케이스인 정확히 일치하는 수직 조회를 보여줍니다.

XLOOKUP 함수는 lookup_array(XLookupData1[Number])안에  lookup_value(7)를 검색하고 아래 식을 사용하여 return_array (XLookupData1[English]) 로부터  해당 값을 반환합니다.:

=XLOOKUP(H20,XLookupData1[Number],XLookupData1[English]) 

다른 수식은 비슷하게 작동하여 프랑스어와 스페인어 열에서 각각 값을 반환합니다.

=XLOOKUP(H20,XLookupData1[Number],XLookupData1[French])

=XLOOKUP(H20,XLookupData1[Number],XLookupData1[Spanish])

VLOOKUP과 달리 XLOOKUP의 기본 동작은 해당 인수가 지정되지 않은 경우 match_mode 0-정확히 일치와 search_mode 1 -처음부터 끝까지를 사용하여 검색을 수행합니다.

예제 2는 XLOOKUP이 lookup_array의 왼쪽 또는 위에 있는 값을 검색하여 반환하는 방법을 보여줍니다. VLOOKUP 및 HLOOKUP은 다음을 수행할 수 없습니다.

예제 2는 XLOOKUP을 사용하여 셀 C33에서 지정된 단어에 대한 스페인어 열을 검색하고 Number , English 및 French 열에서 일치하는 해당 값을 반환합니다.

=XLOOKUP(C33,XLookupData1[Spanish],XLookupData1[Number])

=XLOOKUP(C33,XLookupData1[Spanish],XLookupData1[English])

=XLOOKUP(C33,XLookupData1[Spanish],XLookupData1[French])

다음 예제는 XLookupData1 테이블의 조옮김을 사용하여 가로 조회를 수행합니다.

예제 3은 XLOOKUP이 lookup_array 수평 범위를 참조하여 수평 검색을 수행하는 방법을 보여줍니다.:

예제 3은 XLOOKUP을 사용하여 C46의 지정된 값에 대한 Number 행에서 검색하고 English, French과 Spanish 행에서 해당 값을 반환합니다 .

=XLOOKUP(C46,C40:L40,C41:L41) =XLOOKUP(C46,C40:L40,C42:L42) =XLOOKUP(C46,C40:L40,C43:L43) 

테이블이 전치되었기 때문에 검색이 가로로 수행된다는 점을 제외하고는 예제 1과 동일합니다.

예제 4는 XLOOKUP이 어떻게 수평 조회를 수행하고 lookup_array 범위를 초과하는 행에서 항목을 어떻게 반환하는지에 대한 방법을 보여줍니다.

예제 4는 XLOOKUP을 사용하여 I46에서 지정된 값에 대한 Spanish행을 검색하고 Number , English 및 French 행에서 해당 값을 리턴합니다.

=XLOOKUP(I46, C43:L43,C40:L40) =XLOOKUP(I46, C43:L43,C41:L41) =XLOOKUP(I46, C43:L43,C42:L42) 

테이블이 전치되어 검색이 가로로 수행된다는 점을 제외하고는 예제 2와 동일합니다.

다음 예제는 커미션 비율을 나열하는 일반적인 테이블인  XLookupData3을 사용합니다.

예제 5는 XLOOKUP이 검색을 수행하고 정확히 일치하거나 작은 값을 어떻게 반환하는지 그에 대한 방법을 보여줍니다.

예제 5는 match_mode -1 - (정확히 일치 또는 보다 작은 값)을 사용하여 F58에 특정 값에 대한 Sales 열을 검색하기 위해 XLOOKUP을 사용합니다. :

=XLOOKUP(F58,XLookupData3[Sales],XLookupData3[Commission],,-1) 

다음 수식은 lookup_array에서 오름차순으로 이진 검색할 지정된 '2'라는 search_mode 인자의 추가를 제외하고 동일합니다. 이 경우는 테이블이 정렬될 때 보다 더 빠릅니다.

=XLOOKUP(F58,XLookupData3[Sales],XLookupData3[Commission],,-1,2)

레거시 LOOKUP 함수는 정렬된 테이블에서 비율을 찾는 데도 사용할 수 있습니다. 테이블이 첫 번째 열의 lookup_array와 마지막 열의 return_array로 구성되므로 수식은 실제로는 훨씬 간단합니다.:

=LOOKUP(F58,XLookupData3)

레거시 LOOKUP 함수는 XLOOKUP과 같이 lookup_array 및 return_array를 별도로 지정할 수 있습니다.

=LOOKUP(F58,XLookupData3[Sales],XLookupData3[Commission])

VLOOKUP 함수도 수행할 수 있습니다 :

=VLOOKUP(F58,XLookupData3,2)

그러나 XLookupData3 테이블이 역순으로 정렬되며 다음과 같이 됩니다.:

그런 다음 XLOOKUP을 사용한 첫 번째 수식을 제외하고 다른 모든 수식은 중단되거나 잘못된 결과를 반환합니다.

다음 예제는 표 XLookupData4를 사용하여 특정 크기의 상자에 들어갈 수 있는 다양한 최대 단위 수를 나열합니다.

예제 6은 XLOOKUP이 어떻게 검색을 수행하고 정확히 일치하는 값이나 더 큰 값을 반환할 수 있는지 보여줍니다.

예제 6은 XLOOKUP와 match_mode 1-정확히 일치 또는 더 큰 값을 사용하여 Units 열에서 F70의 값을 검색하여 적절한 상자 크기를 찾습니다.

=XLOOKUP(F70,XLookupData4[Units],XLookupData4[Box Size],,1) 

다음 예제는 표 XLookupData5를 사용합니다. 테이블에는 장기의 첫 수와 이동을 나열하고 있습니다.

예제 7은 XLOOKUP이 match_mode 2-와일드 카드 일치를 사용하여 검색을 수행하는 방법을 보여줍니다.

예제 7은 XLOOKUP을 사용하여 G77의 Name 열에서 값을 검색하고 match_mode 2-와일드 카드를 사용하여 ECO 및 Move를 찾습니다. 

=XLOOKUP(""&G77&"",XLookupData5[Name],XLookupData5[ECO],,2) 

=XLOOKUP(""&G77&"",XLookupData5[Name],XLookupData5[Move(s)],,2)

와일드 카드 일치는 '?' 문자를 나타내고 '*'는 문자 시퀀스를 나타냅니다. match_mode 2-와일드 카드 일치 가 지정된 경우에만 적용됩니다.

다음 예제는 테이블 XLookupData6을 사용합니다. 이 표에는 시작 날짜, 이름 및 부서가 나열되며 여기에는 일부 중복 항목이 포함되어 있습니다.

표의 중복 항목은 조직 개편과 다른 부서로 전환한 사람을 나타냅니다.

예제 8은 XLOOKUP이 검색을 수행하고 마지막으로 일치하는 항목을 반환하는 방법을 보여줍니다.

예제 8은 XLOOKUP 을 사용하여 search_mode -1-끝에서 처음으로를 사용하여 G87에서 Name 열의 값을 검색합니다.

=XLOOKUP(G87,XLookupData6[Name],XLookupData6[Dept],,,-1) 

마지막 항목을 반환하는 것은 테이블에 있는 항목의 현재 순서에 따라 다릅니다.

XLOOKUP은 lookup_value 인자에 대해 셀 범위 또는 배열 참조를 참조할 수 있으며 동적 배열이 활성화된 경우 결과를 볼 수 있습니다.

위의 예에서 셀 F91에는 결과를 F91:F98에 보여주는 동적 배열 수식이 포함되어 있습니다.

=SORT(UNIQUE(XLookupData6[Names]))

셀 G91의 수식은 lookup_value F91#과 함께 XLOOKUP을 사용하고 결과를 G91 : G98에 보여줍니다.

=XLOOKUP(F91#,XLookupData6[Name],XLookupData6[Dept],,,-1) 

레거시 LOOKUP 함수는 다음을 수행할 수도 있습니다.

그러나 LOOKUP 함수는 올바른 결과를 반환하려면 다음과 같은 복잡한 배열 수식이 필요합니다. 다음은 P90셀의 수식입니다.

=LOOKUP(2,1/(XLookupData6[Name]=O90),XLookupData6[Dept]) 

이 수식은 원하는 결과를 얻기 위한 까다로운 방법입니다. 먼저, 지정된 lookup_value(2)를 의도적으로 선택하여 일치하지 않은 항목을 찾을 수 있게 합니다. 그런 다음 배열 수식 부분인 (XLookupData6 [Name] = O90)을 True(1) 및 False(0) 값의 배열로 지정할 두 번째 인자 1/(XLookupData6 [Name] = O90을 지정합니다. XLookupData6[Name]의 특정하지 않은 셀이 O90의 지정된 이름과 동일한지 여부를 지정합니다. 그런 다음 각 요소가 1로 분할되어 새로운 값 배열이 생성됩니다. 이전에 False (0) 값을 가지고 있던 배열에 O90와 #DIV/0 오류 값과 동일한 각 이름에 대해서 True(1) 포함되어 있습니다. 

LOOKUP 함수는 해당 배열 요소(설계 기준)에서 lookup_value(2)를 찾지 못하며 배열의 마지막 비오류값을 반환합니다.

LOOKUP을 사용하는 수식은 더 복잡할 뿐만 아니라 LOOKUP이 동적 배열 또는 spilling behavior을 지원하지 않으므로 범위 또는 동적 배열 인자(예 : 대신 O90 # 사용)를 사용하여 작동할 수도 없습니다  대신 셀 P91:P97의 수식을 P90에 복사해야 합니다.

예제 9는 XLOOKUP이 search_mode 0-all을 사용하여 검색을 수행하고 동적 배열에서 일치하는 모든 요소를 ​ 반환하는 방법을 보여줍니다.

예제 9는 XLOOKUP이 search_mode 0-all을 사용하여 C102의 값에 대한 Name열을 검색합니다.

=XLOOKUP(C102,XLookupData6[Name],XLookupData6[Start],,,0)

=XLOOKUP(C102,XLookupData6[Name],XLookupData6[Dept],,,0)

B104의 첫 번째 수식은 Fred의 시작 날짜를 찾고 C104의 두 번째 수식은 연관된 Dept를 찾습니다.

참고 : search_mode 0-모두 Spread.NET에서만 지원되며 Excel에서 지원되지 않습니다 (#VALUE! 오류를 반환함).

XMATCH

XMatch 구문

XMATCH 함수는 match_mode 및 search_mode을 사용하여 지정된 lookup_array에서 특정 lookup_value에 대한 조회를 수행한 다음 lookup_array에서 발견된 항목의 인덱스를 반환합니다.

XMATCH 함수는 기존 MATCH 함수를 대체할 수 있는 새로운 조회 함수입니다. 다음과 같은이유들은 새 함수가 더 좋은 지를 설명해줍니다.

1. XMATCH는 lookup_array의 방향에 따라 세로 또는 가로 조회(또는 둘 다)를 수행할 수 있습니다.
2. XMATCH는 데이터가 정렬되지 않은 경우에도(MATCH와 달리) 정확한 결과를 사용하여 비정확한 검색을 수행할 수 있습니다.
3. XMATCH는 Spread.NET 13 WinForms에서 새로운 search_mode 0-All을 지원하도록 향상되었습니다. 배열에서 일치하는 모든 항목을 반환하며, 동적 배열이 활성화되면 인접한 셀로 넘길 수 있습니다.

XMATCH 함수의 실제 작동을 설명하기 위해 XMatchData1이라는 샘플 테이블이 있습니다.

예제 1 - 가장 일반적인 사용 사례인 정확히 일치하는 값을 찾는 수직 조회를 보여줍니다.

H20의 XMATCH 함수는 lookup_array(XMatchData1 [Number]) 내부에서 lookup_value (7)을 검색하고 다음 공식을 사용하여 lookup_array의 인덱스를 반환합니다.

=XMATCH(H20,XMatchData1[Number]) 

H23의 MATCH 함수는 동일한 기능을 수행합니다.

=MATCH(H19,XMatchData1[Number]) 

다음 예제는 XMatchData1 테이블 의이 조옮김을 사용하여 가로 조회를 보여줍니다.

예제 2는 XMATCH가 lookup_array의 가로 범위를 참조하여 가로 조회를 수행하는 방법을 보여줍니다.

예제 2는 XMATCH를 사용하여 C38에서 지정된 값에 대한 Number 행을 검색하고 인덱스를 반환합니다.

= XMATCH (C38, C32 : L32)

C42의 MATCH 함수는 동일한 기능을 수행합니다.

=MATCH(C38,C32:L32)

다음 예는 표 XMatchData3을 사용하여 일반적인 수수료 요금표를 보여줍니다.

예제 3은 XMATCH가 어떻게 검색을 수행하고 정확히 일치하거나 작은 값을 반환하는지 보여줍니다.

예제 3에서는 F49의 XMATCH를 사용하여 match_mode -1-정확히 일치 또는 작은 값을 사용하여 F48에서 지정된 값을 Sales 열에서 검색합니다.

=XMATCH(F48,XMatchData3[Sales],-1) 

F50의 다음 수식은 오름차순인 lookup_array에서 이진 검색을 지정하는 search_mode 인자를 추가 한 것을 제외하고는 동일합니다. 이 경우에는 테이블을 정렬할 때 속도가 더 빠릅니다.

=XMATCH(F48,XMatchData3[Sales],-1,2) 

레거시 MATCH 함수는 정렬된 테이블에서 비율을 찾는 데도 사용할 수 있습니다. 테이블은 첫 번째 열의 lookup_array와 마지막 열의 return_array로 구성되므로 수식이 더 간단합니다.:

=MATCH(F48,XMatchData3[Sales]) 

그러나 XMatchData3 테이블이 역순으로 정렬된 경우 아래와 같습니다. :

그런 다음 XMATCH를 사용한 첫 번째 수식을 제외하고 다른 모든 수식은 중단되고 잘못된 결과를 반환합니다 .

다음 예는 표 XMatchData4를 사용하여 특정 크기의 상자에 맞는 다양한 최대 단위 수를 나열합니다.

예제 4는 XMATCH가 어떻게 검색을 수행하고 정확히 일치하거나 큰 값을 반환할 수 있는지 보여줍니다.

예제 4는 XMATCH가 match_mode 1-정확히 일치 또는 큰 값을 사용하여 F59에서 Units 열의 값을 검색합니다.

=XMATCH(F59,XMatchData4[Units],1) 

다음 예는 테이블 XMatchData5를 사용합니다. 이 테이블에는 장기의 첫 수와 이동이 나열되어 있습니다.

예제 5는 XMATCH가 match_mode 2-와일드 카드 일치를 사용하여 검색을 수행하는 방법을 보여줍니다.

예제 5는 XMATCH가 match_mode 2 - 와일드 카드 일치를 사용하여  Name 열에서 G66의 값을 검색합니다. :

=XMATCH(""&G66&"",XMatchData5[Name],2) 

와일드카드 일치는 '?' 문자를 나타내고 '*' 를 사용하여 문자 시퀀스를 나타냅니다. match_mode 2-와일드 카드 일치가 지정된 경우에만 적용됩니다.

다음 예제는 테이블 XMatchData6을 사용합니다. 이 표에는 시작 날짜, 이름 및 부서가 나열되며 여기에는 일부 중복 항목이 포함됩니다.

표의 중복 항목은 조직 개편과 다른 부서로 전환한 사람을 나타냅니다.

예제 6은 XMATCH가 검색을 수행하고 마지막으로 일치하는 항목을 반환하는 방법을 보여줍니다.

예제 6에서는 XMATCH를 사용하여 search_mode -1-끝에서 첫 번째를 사용하여 G76 값에 대한 Name 열을 검색합니다.

=XMATCH(G76,XMatchData6[Name],,-1) 

마지막 항목을 반환하는 방법은 표에 있는 항목의 현재 순서에 따라 다릅니다.

XMATCH는 lookup_value 인자에 대한 셀 범위 또는 배열 참조를 나타내며 동적 배열이 활성화된 경우 결과를 펼쳐서 보여줍니다.

위의 예제에서 셀 F80에는 동적 배열 수식이 포함되어 있으며 F80:F87에 결과가 보여집니다.

=SORT(UNIQUE(XMatchData6[Names])) 

셀 G80의 수식은 lookup_value F90#과 함께 XMATCH를 사용하고 결과를 G80 : G87에 보여줍니다.

=XLOOKUP(F80#,XMatchData6[Name],,-1) 

예제 7은 XMATCH가 search_mode 0-모두를 사용하여 검색을 수행하고 모든 일치하는 요소를 동적 배열로 반환하는 방법을 보여줍니다.

예제 7은 XMATCH를 사용하여 search_mode 0-모두를 사용하여 Name열에서 C91 값을 검색합니다.

=XMATCH(C91,XMatchData6[Name],,0) 

이 search_mode 0-모두는 Spread.NET에서만 지원되며 Excel에서 지원되지 않습니다 ( #VALUE! 오류를 반환 함 ).