모든 클래식 검색 및 유형 대체 기능 VPR (조회), GPR (검색), 더 많이 노출 (성냥) 그리고 그들과 같은 사람들은 한 가지 중요한 기능을 가지고 있습니다. 즉, 처음부터 끝까지, 즉 소스 데이터에서 왼쪽에서 오른쪽 또는 위에서 아래로 검색합니다. 일치하는 첫 번째 일치 항목이 발견되자마자 검색이 중지되고 필요한 요소의 첫 번째 항목만 발견됩니다.
첫 번째가 아니라 마지막 항목을 찾아야 하는 경우 어떻게 해야 합니까? 예를 들어 고객의 마지막 거래, 마지막 지불, 가장 최근 주문 등?
방법 1: 배열 수식으로 마지막 행 찾기
원래 테이블에 행의 일련 번호나 날짜(주문, 지불 ...)가 있는 열이 없으면 실제로 주어진 조건을 만족하는 마지막 행을 찾는 것이 우리의 임무입니다. 이것은 다음 배열 수식으로 수행할 수 있습니다.
이리:
- 함수 IF (만약) 열의 모든 셀을 하나씩 확인 빠른 필요한 이름이 포함된 경우 줄 번호를 표시합니다. 시트의 줄 번호는 함수에 의해 우리에게 주어집니다. LINE (열), 그러나 테이블에 행 번호가 필요하므로 테이블에 헤더가 있으므로 추가로 1을 빼야 합니다.
- 그런 다음 기능 MAX (최대) 구성된 행 번호 세트에서 최대값, 즉 클라이언트의 가장 최근 행 번호를 선택합니다.
- 함수 INDEX (인덱스) 다른 필수 테이블 열(주문 코드).
이 모든 것은 다음과 같이 입력해야 합니다. 배열 수식, 즉 :
- 최신 업데이트가 설치되고 동적 배열이 지원되는 Office 365에서 엔터 버튼.
- 다른 모든 버전에서는 수식을 입력한 후 바로 가기 키를 눌러야 합니다. Ctrl 키+변화+엔터 버튼, 수식 입력줄에 중괄호가 자동으로 추가됩니다.
방법 2: 새로운 LOOKUP 함수로 역방향 조회
나는 이미 새로운 기능에 대한 비디오와 함께 긴 기사를 썼습니다. 관람 (X룩업), 이전 VLOOKUP을 대체하기 위해 최신 버전의 Office에 등장했습니다. (조회). BROWSE의 도움으로 우리의 작업은 매우 기본적으로 해결됩니다. 이 함수의 경우(VLOOKUP과 달리) 검색 방향을 명시적으로 설정할 수 있습니다. 하향식 또는 상향식 – 마지막 인수(-1)가 이에 대한 책임이 있습니다.
방법 3. 최신 날짜의 문자열 검색
소스 데이터에 유사한 역할을 하는 일련 번호 또는 날짜가 있는 열이 있으면 작업이 수정됩니다. 일치하는 마지막(최하위) 행이 아니라 최신( 최대) 날짜.
기존 함수를 사용하여 이 작업을 수행하는 방법에 대해 이미 자세히 논의했으며 이제 새로운 동적 배열 함수의 기능을 사용해 보겠습니다. 더 큰 아름다움과 편리함을 위해 키보드 단축키를 사용하여 원래 테이블을 "스마트" 테이블로 변환합니다. Ctrl 키+T 또는 명령 홈 – 테이블 형식 (홈 — 표 형식).
그들의 도움으로 이 "킬러 커플"은 우리의 문제를 매우 우아하게 해결합니다.
이리:
- 기능 우선 FILTER (필터) 열에 있는 테이블에서 해당 행만 선택합니다. 빠른 – 우리에게 필요한 이름.
- 그런 다음 기능 학년 (종류) 선택한 행을 날짜별로 내림차순으로 정렬하고 가장 최근 거래가 맨 위에 오도록 합니다.
- 함수 INDEX (인덱스) 첫 번째 행을 추출합니다. 즉, 필요한 마지막 거래를 반환합니다.
- 마지막으로 외부 FILTER 함수는 결과에서 추가 첫 번째 및 세 번째 열을 제거합니다(주문 코드 и 빠른 ) 날짜와 금액만 남습니다. 이를 위해 상수 배열이 사용됩니다. {0;1;0;1}, 표시할 열(1) 또는 표시하지 않을 열(0)을 정의합니다.
방법 4: 파워 쿼리에서 마지막 일치 찾기
완벽을 기하기 위해 파워 쿼리 추가 기능을 사용하여 역 검색 문제에 대한 솔루션을 살펴보겠습니다. 그녀의 도움으로 모든 것이 매우 빠르고 아름답게 해결됩니다.
1. 키보드 단축키를 사용하여 원래 테이블을 "스마트" 테이블로 변환해 보겠습니다. Ctrl 키+T 또는 명령 홈 – 테이블 형식 (홈 — 표 형식).
2. 버튼을 사용하여 파워 쿼리에 로드 테이블/레인지에서 탭 Data (데이터 — 테이블/범위에서).
3. 헤더에 있는 필터의 드롭다운 목록을 통해 테이블을 날짜의 내림차순으로 정렬하여 가장 최근의 트랜잭션이 맨 위에 오도록 합니다.
4... 탭에서 변환 팀을 선택 그룹화 기준 (변환 — 그룹화 기준) 고객별로 그룹화를 설정하고 집계 기능으로 옵션 선택 모든 라인 (모든 행). 새 열의 이름은 원하는 대로 지정할 수 있습니다. 예를 들어 세부 정보.
그룹화 후, 우리는 고객의 고유 이름 목록과 열을 얻습니다. 세부 정보 – 각각의 모든 트랜잭션이 포함된 테이블. 첫 번째 행은 최신 트랜잭션이 될 것이며 이것이 필요한 것입니다.
5. 버튼으로 새 계산 열 추가 맞춤 항목 탭 열 추가 (열 추가 — 사용자 정의 열 추가)다음 공식을 입력하십시오.
여기에 세부 정보 – 이것은 고객이 테이블을 가져오는 열입니다. 0{} 추출하려는 행의 번호입니다(파워 쿼리의 행 번호 매기기는 XNUMX부터 시작). 레코드가 있는 열을 얻습니다(기록), 여기서 각 항목은 각 테이블의 첫 번째 행입니다.
열 머리글에 이중 화살표가 있는 버튼을 사용하여 모든 레코드의 내용을 확장해야 합니다. 마지막 거래 원하는 열 선택:
... 그런 다음 더 이상 필요하지 않은 열을 삭제합니다. 세부 정보 제목을 마우스 오른쪽 버튼으로 클릭하여 - 열 제거 (열 제거).
를 통해 시트에 결과를 업로드한 후 홈 — 닫기 및 로드 — 닫기 및 로드 (홈 — 닫기 및 로드 — 닫기 및 로드…) 우리는 원하는 대로 최근 거래 목록이 있는 멋진 테이블을 얻을 것입니다.
소스 데이터를 변경할 때 결과를 마우스 오른쪽 버튼으로 클릭하여 업데이트하는 것을 잊지 말아야 합니다. – 명령 업데이트 및 저장 (새로 고치다) 또는 키보드 단축키 Ctrl 키+다른+F5.
- LOOKUP 함수는 VLOOKUP의 자손입니다.
- 새로운 동적 배열 함수 SORT, FILTER 및 UNIC를 사용하는 방법
- LOOKUP 함수를 사용하여 행이나 열에서 비어 있지 않은 마지막 셀 찾기