사용자 정의 엑셀 XLOOKUP 기능

Posted on: 2020年11月12日 2020年11月12日
Tags: lookup-formulas

XLOOKUP 기능은 Office 365의 내부 프로그램 전용입니다.

LOOKUP 함수에는 VLOOKUP 및 HLOOKUP 함수의 많은 약점을 극복 한 많은 기능이 있지만 슬프게도 지금은 사용할 수 없습니다. 하지만 걱정하지 마세요. 곧 출시 될 XLOOKUP 함수 인 MS Excel과 똑같이 작동하는 XLOOKUP 함수를 만들 수 있습니다. 기능을 하나씩 추가하겠습니다.

XLOOKUP 함수의 VBA 코드 아래 UDF 조회 함수는 많은 문제를 해결합니다. 복사하거나 아래 파일 아래에 xl 추가 기능을 다운로드하십시오.

Function XLOOKUP(lk As Variant, lCol As Range, rCol As Range)

XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0))

End Function

설명 :

위의 코드는 VBA에서 사용되는 기본 INDEX-MATCH입니다. 이것은 새로운 사용자가 직면하는 많은 것들을 단순화합니다. INDEX-MATCH 함수의 복잡성을 해결하고 세 개의 인수 만 사용하는 경우. 엑셀 파일로 복사하거나 아래의 .xlam 파일을 다운로드하여 추가 기능 엑셀로 설치할 수 있습니다. 추가 기능을 만들고 사용하는 방법을 모르는 경우`link : / excel-macros-and-vba-add-in-in-vb [여기를 클릭하면 도움이 될 것입니다].

XLOOKUP Add-In]

엑셀 워크 시트에서 어떻게 작동하는지 봅시다.

XLOOKUP 구문

=XLOOKUP(lookup_value, lookup_array, result_array)

lookup_value : 이것은

에서 검색하려는 값입니다. === lookup_array.

lookup_array : lookup_value를 검색하려는 1 차원 범위입니다. result_array : 또한 1 차원 범위입니다. 이것은 값을 검색하려는 범위입니다.

이 XLOOKUP 함수가 작동하는지 살펴 보겠습니다.

XLOOKUP 예 :

여기에 Excel의 데이터 테이블이 있습니다. 이 데이터 테이블을 사용하여 몇 가지 기능을 살펴 보겠습니다.

기능 1. 조회 값의 왼쪽과 오른쪽에있는 정확한 조회. Excel VLOOKUP 함수는 조회 값의 왼쪽에서 값을 검색 할 수 없다는 것을 알고 있습니다. 이를 위해서는 복잡한 INDEX-MATCH 조합을 사용해야합니다. 하지만 더 이상은 아닙니다.

일부 롤 번호 표에서 사용 가능한 모든 정보를 검색해야한다고 가정합니다. 이 경우 롤 번호 열의 왼쪽에있는 영역도 검색해야합니다.

다음 공식을 작성하십시오. I2 :

=XLOOKUP(H2,$B$2:$B$14,$A$2:$A$14)

롤 번호 112에 대한 결과 North를 얻습니다. 아래 셀의 수식을 복사하거나 아래로 드래그하여 각 영역을 채 웁니다.

어떻게 작동합니까?

메커니즘은 간단합니다. 이 함수는 lookup_array에서 lookup_value를 조회하고 정확히 일치하는 첫 번째 인덱스를 반환합니다. 그런 다음 해당 인덱스를 사용하여 result_array에서 값을 검색합니다. 이 함수는 명명 된 범위에서 완벽하게 작동합니다.

마찬가지로이 수식을 사용하여 각 열에서 값을 검색합니다.

기능 2. 조회 값의 위와 아래에 ExactHorizontalLookup. XLOOKUP은 정확한 HLOOKUP 함수로도 작동합니다. HLOOKUP 함수에는 VLOOKUP과 동일한 제한이 있습니다. 조회 값 위에서 값을 가져올 수 없습니다. 그러나 XLOOKUP은 HLOOKUP으로 작동 할뿐만 아니라 이러한 약점도 극복합니다. 방법을 봅시다.

두 레코드를 비교하려는 경우 가정적으로. 이미 가지고있는 조회 레코드입니다. 비교하려는 레코드가 lookup_range 위에 있습니다. 이 경우이 공식을 사용하십시오.

=XLOOKUP(H7,$A$9:$E$9,$A$2:$E$2)

수식을 아래로 드래그하면 행을 비교하는 전체 레코드가 있습니다.

기능 3. 열 번호 및 기본 정확히 일치가 필요하지 않습니다.

VLOOKUP 함수를 사용할 때 값을 가져 오려는 열 번호를 알려야합니다. 이를 위해서는 열을 세거나 몇 가지 트릭을 사용하고 다른 기능의 도움을 받아야합니다. 이 UDF XLOOKUP을 사용하면 그렇게 할 필요가 없습니다.

한 열에서 일부 값을 가져 오거나 열에 값이 있는지 확인하기 위해 VLOOKUP을 사용하는 경우 이것이 저에게 가장 적합한 솔루션입니다.

기능 4. INDEX-MATCH, VLOOKUP, HLOOKUP 함수 대체

간단한 작업의 경우 XLOOKUP 기능이 위에서 언급 한 기능을 대체합니다.

XLOOKUP의 제한 사항 :

또 다른 제한은 테이블에서 여러 개의 임의의 열이나 행을 찾아야하는 경우이 수식을 반복해서 작성해야하므로이 함수는 쓸모가 없다는 것입니다. 이것은`link : / excel-range-name-all-about-excel-named-ranges-excel-range-name [named ranges]`를 사용하여 극복 할 수 있습니다.

지금은 근사 기능을 추가하지 않았으므로 당연히 근사 일치를 얻을 수 없습니다. 너무 빨리 추가하겠습니다.

XLOOKUP 함수가 조회 값을 찾지 못하면 # N / A가 아닌 #VALUE 오류를 반환합니다 .

예, XLOOKUP을 사용하여 Excel 테이블에서 값을 검색, 검색 및 유효성 검사하는 방법입니다. 이 사용자 정의 함수를 사용하여 조회 값의 왼쪽 또는 위쪽에서 번거롭지 않은 조회를 수행 할 수 있습니다. 이 기능 또는 EXCEL 2010/2013/2016/2019/365 또는 VBA 관련 쿼리와 관련된 의심이나 특정 요구 사항이 여전히 있으면 아래 의견 섹션에서 질문하십시오. 당신은 반드시 답장을받을 것입니다.