☰

*마지막* 일치 항목을 반환하도록 VLOOKUP을 얻는 방법은 무엇입니까?

tag-icon • tag-icon microsoft-excel libreoffice-calc openoffice-calc vlookup lookup
*마지막* 일치 항목을 반환하도록 VLOOKUP을 얻는 방법은 무엇입니까?

저는 VLOOKUP 작업에 익숙했지만 이번에는 도전이 생겼습니다.나는 처음으로 일치하는 값을 원하지 않지만마지막.어떻게? (저는 LibreOffice Calc로 작업하고 있지만 MS Excel 솔루션도 마찬가지로 유용할 것입니다.)

그 이유는 수천 개의 행이 있는 두 개의 텍스트 열이 있기 때문입니다. 하나는 거래 수취인 목록(Amazon, Ebay, 고용주, ​​식료품점 등)이고 다른 하나는 지출 범주(임금, 세금, 가구, 임대료 등). 일부 거래에는 매번 동일한 지출 범주가 없으며 가장 최근에 사용한 거래를 가져오고 싶습니다. 목록은 두 열(실제로는 날짜 기준)로 정렬되지 않으며 정렬 순서를 변경하고 싶지 않습니다.

내가 가지고 있는 것(오류 처리 제외)은 일반적인 "첫 번째 일치" 공식입니다.

=VLOOKUP( 
[payee field] , [payee+category range] , [index of category column] , 
0 )

나는 본 적이솔루션이렇게 하는데 #DIV/0!오류가 발생합니다.

=LOOKUP(2 , 1/( [payee range] = [search value] ) , [category range] )

솔루션은 반드시 VLOOKUP일 필요는 없지만 모든 수식이 될 수 있습니다. 수취인/범주 열을 바꿀 수도 있습니다. 정렬 열을 변경하지 마세요.

다음을 선택하는 솔루션에 대한 보너스 포인트가장 빈번한마지막보다는 가치!

답변1

배열 수식을 사용하여 마지막으로 일치하는 레코드에서 데이터를 가져올 수 있습니다.

=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))

Ctrl+ Shift+ 를 사용하여 수식을 입력합니다 Enter.

이는 a의 INDEX/ 구성 과 유사하게 작동 하지만 대신에 조건문을 사용합니다 .MATCHVLOOKUPMAXMATCH

ROW(...)이는 테이블이 행 1에서 시작한다고 가정합니다. 데이터가 다른 행에서 시작하는 경우 맨 위 행과 1 사이의 차이를 빼서 부분을 조정해야 합니다 .

답변2

(정렬된 데이터에 대해서는 별도의 질문으로 답변하지 않습니다.)

데이터의 경우~이었다정렬된 경우 Excel에서 공식적으로 "대략적인 일치 항목 검색"으로 설명되는 인수 (또는 기본값이므로 생략) VLOOKUP와 함께 사용할 수 있습니다 .range_lookupTRUE

즉, 정렬된 데이터의 경우:

  • 마지막 인수를 설정하여 FALSE다음을 반환합니다.첫 번째가치, 그리고
  • 마지막 인수를 설정하여 TRUE다음을 반환합니다.마지막값.

이는 대부분 문서화되지 않고 모호하지만 VisiCalc(1979)로 거슬러 올라가며 현재는 적어도 Microsoft Excel, LibreOffice Calc 및 Google Sheets에 적용됩니다. 이는 궁극적으로 네 번째 매개변수가 없을 때 LOOKUPVisiCalc(그리고 이후 VLOOKUP및 ) 의 초기 구현 때문입니다 . HLOOKUP값은 다음에 의해 발견됩니다.이진 검색, 포괄적인 왼쪽 경계와 배타적인 오른쪽 경계(일반적이고 우아한 구현)를 사용하면 이 동작이 발생합니다.

기술적으로 이것은 후보 간격으로 검색을 시작한다는 것을 의미합니다. 여기서 는 배열의 길이이고 루프 불변 조건은 다음과 같습니다 [0, n)( 왼쪽 경계는 <= 대상이고 오른쪽 경계는 끝 이후에 시작됩니다). > 대상을 확인하려면 이전에 끝점에서 값을 확인하거나 이후에 결과를 확인하고 이 불변성을 유지하는 쪽을 연속적으로 이등분하고 선택합니다. 한 쪽을 제외하면 1개의 항이 있는 간격에 도달할 때 까지 그런 다음 알고리즘은 를 반환합니다 . 이는 정확히 일치할 필요는 없습니다(!). 아래에서 가장 가까운 일치일 뿐입니다. 중복된 일치 항목이 있는 경우 결과가 반환됩니다.nA[imin] <= key && key < A[imax][k, k+1)k마지막다음 값이 필요하므로 일치합니다.보다 큰키(또는 배열의 끝)보다. 중복되는 경우 필요합니다.일부행동이며 이는 합리적이고 구현하기 쉽습니다.

이 동작은 이전 Microsoft 기술 자료 문서(강조 추가): "XL: 배열에서 첫 번째 또는 마지막 일치 항목을 반환하는 방법"(Q214069):

LOOKUP() 함수를 사용하면 정렬된 데이터 배열 내에서 값을 검색하고 다른 배열 내 해당 위치에 포함된 해당 값을 반환할 수 있습니다. 배열 내에서 조회 값이 반복되는 경우마지막으로 일치하는 항목을 반환합니다.. 이 동작은 VLOOKUP(), HLOOKUP() 및 LOOKUP() 함수에 적용됩니다.

일부 스프레드시트에 대한 공식 문서는 다음과 같습니다. 둘 다 "마지막 일치" 동작이 명시되어 있지 않지만 Google 스프레드시트 문서에는 암시되어 있습니다.

  • 마이크로 소프트 엑셀

    진실테이블의 첫 번째 열이 숫자 또는 알파벳순으로 정렬되어 있다고 가정하고 다음을 검색합니다.가장 가까운 값.

  • Google 스프레드시트:

    is_sorted이거나 TRUE생략된 경우가장 가까운 일치(작거나 같음검색 키에)가 반환됩니다.

답변3

검색 배열의 값이 순차적인 경우(즉, 최신 날짜와 같이 가장 큰 값을 찾는 경우) INDIRECT 함수를 사용할 필요조차 없습니다. 다음의 간단한 코드를 사용해 보세요.

=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)

다시 CTRL + SHIFT + ENTER를 사용하여 수식을 입력하세요.

답변4

나는 가장 빈번한 값으로 이동했습니다. libreOffice에서는 작동할지 확실하지 않지만 Excel에서는 작동하는 것 같습니다.

=INDEX($B$2:$B$9,MATCH(MAX(--($A$2:$A$9=D2)*COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2 :$A$9,D2)),--($A$2:$A$9=D2)*COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2 ),0))

A열은 수취인, B열은 카테고리, D2는 필터링 기준으로 삼을 수취인입니다. 위 함수에 추가 줄바꿈을 추가하는 이유가 무엇인지 잘 모르겠습니다.

마지막 셀을 찾는 기능은 다음과 같습니다.

=INDIRECT("B" & MAX(--($A$2:$A$9=D2)*ROW($A$2:$A$9)))

간접을 사용하면 반환하려는 열을 지정하고 행을 직접 찾을 수 있습니다. 따라서 헤더 행 수를 뺄 필요가 없습니다.

이 두 기능은 모두 다음을 사용하여 입력해야 합니다.Ctrl+시프트+엔터

관련 정보