! 제품 버전을 정확하게 입력해 주세요.
제품 버전이 정확하게 기재되어 있지 않은 경우,
최신 버전을 기준으로 안내 드리므로
더욱 빠르고 명확한 안내를 위해
제품 버전을 정확하게 입력해 주세요!

.NET 응용 프로그램에 XLOOKUP 및 XMATCH 기능을 추가하는 방법 > 블로그 & Tips

본문 바로가기

Spread.NET

블로그 & Tips

.NET 응용 프로그램에 XLOOKUP 및 XMATCH 기능을 추가하는 방법

페이지 정보

작성자 GrapeCity 작성일 2021-07-14 10:58 조회 2,105회 댓글 0건

본문

첨부파일

Spread.NET 14 WinForms에는 두 가지 중요한 Excel 계산 기능인 XLOOKUP 및 XMATCH 이 지원됩니다. 최근 Excel에 이러한 새로운 기능이 추가되었습니다.


XLOOKUP


XLOOKUP 함수는 match_modesearch_mode를 사용하여 지정된 lookup_array에서 lookup_value에 대한 조회가 이루어진 후 지정된 return_array의 해당 셀에서 값을 반환합니다.


XLookUp 구문

XLookuptable


XLOOKUP 기능은 기존의 LOOKUP, VLOOKUP 및 HLOOKUP 기능을 대체 할 수있는 새로운 lookup 기능입니다.


새로운 기능은 다음과 같은 이점을 제공해줍니다:

  1. XLOOKUP은 lookup_array 방향에 따라 수직 또는 수평으로 조회(중첩된 경우 둘 다)를 수행할 수 있습니다.

  2. XLOOKUP은 (HLOOKUP/VLOOKUP과 달리) 데이터가 정렬되지 않은 경우에도, 정확한 결과를 통해 올바른 검색을 할 수 있습니다.

  3. XLOOKUP는 lookup_arrayreturn_array를포함하는 전체 범위를 참조할 필요가 없으며, 이러한 두 가지 특정 범위만 포함되므로 XLOOKUP은 필요한 재계산 측면에서 더 효율적일 수 있습니다.

  4. XLOOKUP 인수는 인덱스 대신 범위 참조를 사용했기 때문에, lookup_array 및 return_array를 이동하여 열이나 행을 삽입하거나 제거할 때 자동으로 조정됩니다.

  5. XLOOKUP은 if_not_found 인수를 지정할 때 #N/A 대신 0 또는 기타 유용한 값을 반환할 수 있습니다.

    1. XLOOKUP는 스프레드에서 기능이 향상되었습니다. NET 14 WinForms는 배열의 일치하는 항목을 모두 반환하는 search_mode 0을 새롭게 지원합니다. 동적 배열을 활성화되면 인접한 셀까지 영향을 줄 수 있습니다.



XLookupData1를 사용하여 XLOOKUP 함수를 설명하겠습니다.

New XLOOKUP and XMATCH Functions


예제 1은 흔히 사용하는 exact match를 사용한 vertical lookup 입니다

New XLOOKUP and XMATCH Functions


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-exact matchsearch_mode 1 - first to last을 사용하여 검색합니다.


예제 2에서는 XLOOKUP이 lookup_array 왼쪽 또는 위에 있는 값을 반환하는 조회(lookup)을 수행하는 방법을 볼 수 있습니다.


VLOOKUPHLOOKUP는 이 작업을 수행할 수 없습니다.

New XLOOKUP and XMATCH Functions


예제 2는 XLOOKUP를 사용하여 셀 C33에 지정된 단어에 대한 Spanish 열을 검색하고 Number, EnglishFrench 열에서 해당 값을 반환합니다. 

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

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

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


다음 예제에서는 XLookupData1 의 행렬을 바꾸어 horizontal lookup를 적용해봅시다:

New XLOOKUP and XMATCH Functions


예제 3에서는 XLOOKUP가 lookup_array 가 열을 참조하여 조회를 수행하는 방법을 보여줍니다.

New XLOOKUP and XMATCH Functions


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

=XLOOKUP (C46,C40:L40,C41:L41)

=XLOOKUP (C46,C40:L40,C42:L42)

=XLOOKUP (C46,C40:L40,C43:L43)


이는 테이블이 뒤바뀌어 검색이 수평(horizontally)으로 수행된다는 점을 제외하면 예제 1과 동일합니다.


예제 4에서는 XLOOKUP가 horizontal lookup를 수행하고 lookup_array 범위 외의 행에서 항목을 반환하는 방법을 보여줍니다.

New XLOOKUP and XMATCH Functions


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

=XLOOKUP (I46, C43:L43,C40:L40)

=XLOOKUP (I46, C43:L43,C41:L41)

=XLOOKUP (I46, C43:L43,C42:L42)


이는 테이블이 뒤바뀌어 검색이 수평(horizontally)으로 수행된다는 점을 제외하면 예제 2와 동일합니다.


다음 예제에서는 수수료(commission) 비율을 나열하는 일반적인 표, XLookupData3을 사용해봅시다.

New XLOOKUP and XMATCH Functions


예제 5에서는 XLOOKUP가 검색을 수행하고, 정확한 일치(exact match) 또는 다음으로 작은 값(next smaller value)을 반환하는 방법을 볼 수 있습니다.

New XLOOKUP and XMATCH Functions


예제 5는 XLOOKUP을 가지고 match_mode -1 - 정확한 일치 또는 다음 작은 값을 사용하여 Sales 열에서 F58에 지정된 값을 검색합니다.

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


다음 수식은 오름차순 lookup_array 에 2-binary search를 지정하는 search_mode 인수의 추가를 제외하고 동일합니다.


아래의 경우처럼 테이블이 정렬되 있다면 더 빨리 처리됩니다.

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


legacy LOOKUP 함수는 정렬된 테이블에서 비율을 계산하는데도 사용될 수 있습니다. 테이블의 첫 번째 열이 lookup_array, 마지막 열이 return_array로 구성되어 공식이 훨씬 간단합니다.

=LOOKUP (F58, XLookupData3)


legacy LOOKUP 기능은 XLOOKUP와 같이 lookup_arrayreturn_array 를 별도로 지원할 수도 있습니다.

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


VLOOKUP 함수로 다음과 같은 작업도 수행할 수 있습니다.

=VLOOKUP (F58, XLookupData3,2)


반면, XLookupData3 테이블이 역순으로 정렬되는 경우:

New XLOOKUP and XMATCH Functions


XLOOKUP을 사용한 첫 번째 수식을 제외하고 다른 모든 공식은 잘못된 결과를 반환합니다.

New XLOOKUP and XMATCH Functions


다음 예제에서는 다음 표 XLookupData4를 사용하여, 특정 포장 박스에 들어갈 수 있는 다양한 최대 유닛(unit)개수를 나열합니다.

11


예제 6에서 XLOOKUP가 어떻게 검색을 수행하고, 정확한 일치 또는 다음 더 큰 값을 반환하는지 볼 수 있습니다.

12


예제 6은 적절한 상자 크기를 찾기 위해, XLOOKUP을 통해 match_mode 1-정확히 일치하거나 그 다음 큰 것을 사용하여 Units에서 F70 값을 검색합니다.

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


다음은 XLookupData5를 사용한 예시입니다. 아래의 표에는 체스 오프닝과 이동이 드러나 있습니다.

13


예 7은 XLOOKUP의 match_mode 2 - 와일드카드 일치(wildcard match)를 사용하여 검색하는 방법을 보여줍니다 :

14


예시 7은 XLOOKUP의 match_mode 2 - wilcard match를 사용하여 Name 열의 G77 값을 검색해서 관련 Eco와 Move(s)를 찾습니다.

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

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


Wildcard match는 '?'를 사용하여 문자, '*'를 사용하여 연속문자를 표시하는데, 이는 match_mode 2 - wildcard match가 명시되 있을 때 적용됩니다.


다음 예제에서는 XLookupData6을 사용합니다. 아래의 표에는 몇 가지 중복된 항목이 있는 시작 날짜, 이름 및 부서가 나와있습니다.

15


테이블의 중복 된 항목은 조직 개편에서 다른 부서로 전환한 사람들을 의미합니다.


예제 8에서는 XLOOKUP을 사용해 검색을 수행하고, 마지막 일치 항목을 반환하는 것을 볼 수 있습니다.

16


예제 8에선 XLOOKUPsearch_mode-1 - last to first를 사용하여 Name 열의 G87 값을 검색합니다.

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


마지막 항목을 반환하는 방법은 테이블에 있는 항목의 순서에 따라 달라집니다.


XLOOKUP은 lookup_value 인수에 대해 셀 범위나 배열을 참조하고, 동적배열 설정 후 결과를 도출할 수 있습니다.

17


위의 예에서, 셀 F90에는 F90:F97에 결과를 표시하는 동적 배열이 공식이 있습니다.

=SORT(UNIQUE(XLookupData6[Names]))


G90에는XLOOKUP과 lookup_valueF90#을 사용하고 G90:G97에 결과를 표시합니다.

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


legacy LOOKUP 함수는 다음과 같은 작업도 수행할 수도 있습니다.

18


이 때, LOOKUP 함수는 올바른 결과를 반환하려면 복잡한 정렬 공식이 필요합니다. – 셀 P90의 수식은 아래와 같습니다.

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


복잡한 공식을 통해 원하는 결과를 얻을 수 있게 됩니다. 먼저 lookup_value는 의도적으로 (2)을 선택하여 일치가 발생하지 않도록 합니다. 그런 다음 두 번째 인수는 1/(XLookupData6[Name]=을 지정하는데, 이는 정렬 공식 부분(XLookupData6[Name]=O90)을 True(1)False(0) 값의 배열로 먼저 평가합니다. 이는 XLookupData6[Name]의 특정 셀이 O90의 지정된 이름과 동일한지 여부를 확인합니다. 그 후 각 요소가 1로 분할되어 새로운 값의 정렬이 만들어집니다. 이전 정렬에 False(0) 값이 포함되었던, O90 값 및 #DIV/0 오류 값과 동일한 각 이름에 True(1)가 포함되어 있습니다.


LOOKUP 함수는 이러한 배열 요소(의도적으로)에서 lookup_value(2)를 찾을 수 없으며 배열의 마지막 비(非)오류 값과 연결된 값을 산출합니다. 


LOOKUP을 사용하는 수식은 상대적으로 더 복잡하다는 단점을 가지고 있을 뿐만 아니라, LOOKUP 수식이 동적 배열이나 유출 동작을 지원하지 않기 때문에 범위 또는 동적 배열 인수(예: O90#대신 사용)를 사용하여 작업할 수 없습니다. 대신, 셀 P91:P97의 수식은 P90에서 아래로 복사되어야 합니다.


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

19


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

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

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


B103의 첫 번째 공식은 Fred와 관련한 시작 날짜를 발견하고, C103의 두 번째 공식은 관련 부서를 찾습니다.


search_mode 0 - all은 Spread.NET에서 고유하고 Excel에서 지원되지 않습니다 (#VALUE! 오류가 산출됩니다).



XMATCH


XMatch 구문


XMATCH 함수는 지정된 match_mode search_mode사용하여 지정된 lookup_array 지정된 lookup_value 조회를 수행한 다음 lookup_array 를 통해 찾은 항목의 인덱스를 산출합니다.

Xmatch


XMATCH 기능은 이전 MATCH 함수를 대체할 수 있는 새로운 조회 기능입니다. 새로운 기능의 개선점들은 다음과 같습니다.

  1. XMATCH는 lookup_array 방향에 따라 수직 또는 수평 조회(또는 중첩된 경우 모두)기능을 수행할 수 있습니다.

  2. XMATCH는 데이터가 정렬되어 있지 않은 경우라도(MATCH와 달리) 정확한 결과를 산출하는 non-exact lookup을 수행할 수 있습니다.

  3. XMATCH는 스프레드 새로운 search_mode 0 - All.을 지원하는 Spread .NET 14 WinForms의 측면에서도 개선되었습니다. 다이내믹 어레이가 활성화될 때 인접한 셀로 확장될 수 있는 배열과 일치하는 모든 항목을 산출합니다.


XMATCH 기능을 설명하기 위한 샘플 테이블로 XMatchData1를 볼 수 있습니다.

20


예제 1은 가장 흔하게 사용되는, exact match를 사용한 vertical lookup를 보여줍니다.

21


XMATCH 함수가 H20셀에 적용되어 lookup_array(XMatchData1[Number])내에서 lookup_value(7)을 검색하고 다음 공식을 사용하여 lookup_array 인덱스를 도출합니다.

=XMATCH(H20,XMatchData1[Number])


H23 셀에서의 MATCH 기능은 다음과 같습니다.

=MATCH(H19, XMatchData1[Number])


다음 예제에서는 XMatchData1 테이블의 행렬을 전환하여 수평조회(horizontal lookup) 사용하겠습니다.

22


예제 2에서는 XMATCH가 lookup_array의 수평 범위를 참조하여 수평 조회(horizontal lookup)를 수행하는 방법을 보여줍니다.

23


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

=XMATCH (C38, C32:L32)


C42 셀의 MATCH** 함수는 다음과 같습니다.

=MATCH(C38, C32:L32)


다음 예제에서는 수수료(Commission) 비율을 보여주는 일반적인 표, XMatchData3를 사용해봅시다.

24


예 3에서은 XMATCH가 검색을 수행하고 정확한 일치 또는 다음으로 작은 값을 반환하는 방법을 보여줍니다.

25


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

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


F50셀에서의 다음 수식은 2 - 오름차순 lookup_array에 대한 이진 검색 (binary search on ascending lookup_array) 를 지정하는 search_mode 의 인수를 추가하는 것을 제외하고는 동일합니다. 아래의 경우와 같이 테이블을 정렬할 때 더 빠릅니다.

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


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

=MATCH (F48, XMatchData3[Sales])


그러나 XMatchData3 테이블이 역순으로 정렬되는 경우:

26


그러면 XMATCH를 사용한 첫 번째 수식을 제외하고, 다른 모든 수식에서는 잘못된 결과를 반환합니다.

27


다음 예제에서는 다음 표 XMatchData4를 사용하여 특정 크기의 상자(Box Size)에 맞을 수 있는 다양한 최대 유닛개수를 알 수 있습니다.

28


예제 4는 XMATCH가 검색을 수행하고 정확한 일치 또는 다음으로 더 큰 값을 반환하는 방법을 보여 줍니다.

29


예 4는 XMATCH의 match_mode 1-정확히 일치하거나 다음으로 큰 값을 사용하여 F59에 Units 값을 검색합니다 .

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


다음 예제에서는 XMatchData5를 사용합니다. 이 표에는 체스 오프닝 및 움직임이 드러나 있습니다.

30


예제 5는 XMATCH가 match_mode 2 - wildcard match 사용하여 검색을 수행하는 방법을 보여줍니다.

31


예제 5는 XMATCH의 match_mode 2 - wildcard match를 사용하여 G66 셀의 Name 열을 검색합니다.

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


Wildcard match는 '?'를 사용하여 문자를, 그리고 '*'를 사용하여 연속문자를 표시하는데, 이는 match_mode 2 - wildcard match가 명시되 있을 때 적용됩니다.


다음 예제에서는 XMatchData6을 사용합니다. 이 표에는 시작 날짜, 이름 및 부서의 일부 항목이 중복되어 나와있습니다.

32


표의 중복 항목은 조직 개편에서 다른 부서로 전환한 사람들을 의미합니다.

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

33


예제 6은 XMATCH의 search_mode -1-last to first를 사용하여 G76의 값의 Name 열을 검색합니다.

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


마지막 항목을 반환하는 방법은 표에 있는 항목 순서에 따라 달라집니다.


XMATCH는 lookup_value 인수의 셀 범위 또는 spilled array를 참조할 수도 있고, dynamic array가 활성화된 경우 결과를 도출합니다.

34


위의 예에서, 셀 F80에는 dynamic array 공식이 포함되어 있어 F80:F87:로 결과를 드러냅니다.

=SORT(UNIQUE(XMatchData6[Names]))


G80에서 XMATCHlookup_value F80 #를 사용하여 G80:G87에 결과를 보이게 됩니다.

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


예제 7에서는 XMATCHsearch_mode 0 - all 를 사용하여 검색을 수행하고, dynamic array에서 일치하는 모든 요소를 반환하는 방법을 보여 주습니다.

35


예제 7은 XMATCHsearch_mode 0 - all을 사용하여 Name 열의 셀 C91 값을 검색합니다.

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


search_mode 0 - all 기능은 Spread.NET 고유하며, Excel에서 지원되지 않습니다. ( #VALUE! 오류가 발생합니다).


위의 설명에 대한 예제는 첨부 파일을 확인해주세요. 




지금 바로 Spread.NET를 다운로드하여 직접 테스트해보세요!

7edc1ee1cacfe0b6267e01e8a3ac91b6_1626227201_9312.png

  • 페이스북으로 공유
  • 트위터로  공유
  • 링크 복사
  • 카카오톡으로 보내기

댓글목록

등록된 댓글이 없습니다.

메시어스 홈페이지를 통해 제품에 대해서 더 자세히 알아 보세요!
홈페이지 바로가기

태그1

인기글

더보기
  • 인기 게시물이 없습니다.
메시어스 홈페이지를 통해 제품에 대해서 더 자세히 알아 보세요!
홈페이지 바로가기
이메일 : sales-kor@mescius.com | 전화 : 1670-0583 | 경기도 과천시 과천대로 7길 33, 디테크타워 B동 1107호 메시어스(주) 대표자 : 허경명 | 사업자등록번호 : 123-84-00981 | 통신판매업신고번호 : 2013-경기안양-00331 ⓒ 2024 MESCIUS inc. All rights reserved.