Nội dung
Tất cả các chức năng thay thế loại và tìm kiếm cổ điển VPR (Vlookup), GPR (HLOOKUP), THÊM ĐƯỢC TIẾP XÚC (CUỘC THI ĐẤU) và những người như họ có một tính năng quan trọng - họ tìm kiếm từ đầu đến cuối, tức là từ trái sang phải hoặc từ trên xuống dưới trong dữ liệu nguồn. Ngay khi tìm thấy kết quả phù hợp đầu tiên, quá trình tìm kiếm sẽ dừng lại và chỉ tìm thấy lần xuất hiện đầu tiên của phần tử chúng ta cần.
Phải làm gì nếu chúng ta cần tìm không phải lần xuất hiện đầu tiên mà là lần xuất hiện cuối cùng? Ví dụ: giao dịch cuối cùng cho khách hàng, lần thanh toán cuối cùng, đơn đặt hàng gần đây nhất, v.v.?
Phương pháp 1: Tìm hàng cuối cùng bằng công thức mảng
Nếu bảng ban đầu không có cột có ngày hoặc số thứ tự của một hàng (đơn hàng, thanh toán…), thì thực tế, nhiệm vụ của chúng ta là tìm hàng cuối cùng thỏa mãn điều kiện đã cho. Điều này có thể được thực hiện với công thức mảng sau:
Đây:
- Chức năng IF (NẾU) kiểm tra tất cả các ô trong từng cột một Khách hàng và hiển thị số dòng nếu nó chứa tên chúng ta cần. Số dòng trên trang tính được cung cấp cho chúng ta bởi hàm ĐƯỜNG DÂY (HÀNG NGANG), nhưng vì chúng ta cần số hàng trong bảng, nên chúng ta cũng phải trừ đi 1, vì chúng ta có tiêu đề trong bảng.
- Sau đó, hàm MAX (TỐI ĐA) chọn giá trị lớn nhất từ tập hợp số hàng đã hình thành, tức là số dòng gần đây nhất của khách hàng.
- Chức năng INDEX (MỤC LỤC) trả về nội dung của ô với số cuối cùng được tìm thấy từ bất kỳ cột bảng bắt buộc nào khác (Mã đặt hàng).
Tất cả điều này phải được nhập dưới dạng công thức mảng, I E:
- Trong Office 365 với các bản cập nhật mới nhất được cài đặt và hỗ trợ cho mảng động, bạn có thể chỉ cần nhấn đăng ký hạng mục thi.
- Trong tất cả các phiên bản khác, sau khi nhập công thức, bạn sẽ phải nhấn phím tắt Ctrl+sự thay đổi+đăng ký hạng mục thi, sẽ tự động thêm dấu ngoặc nhọn vào nó trong thanh công thức.
Phương pháp 2: Tra cứu ngược với hàm LOOKUP mới
Tôi đã viết một bài báo dài với video về một tính năng mới XEM (XTRA CỨU), xuất hiện trong các phiên bản Office mới nhất để thay thế hàm VLOOKUP cũ (Vlookup). Với sự trợ giúp của BROWSE, nhiệm vụ của chúng tôi đã được giải quyết một cách khá yếu tố, bởi vì. đối với hàm này (không giống như hàm VLOOKUP), bạn có thể đặt hướng tìm kiếm một cách rõ ràng: từ trên xuống hoặc từ dưới lên - đối số cuối cùng của nó (-1) chịu trách nhiệm cho việc này:
Phương pháp 3. Tìm kiếm một chuỗi có ngày mới nhất
Nếu trong dữ liệu nguồn, chúng ta có một cột có số sê-ri hoặc ngày đóng vai trò tương tự, thì tác vụ được sửa đổi - chúng ta cần tìm không phải dòng cuối cùng (thấp nhất) có kết quả khớp mà là dòng mới nhất ( tối đa) ngày.
Tôi đã thảo luận chi tiết về cách thực hiện việc này bằng cách sử dụng các hàm cổ điển, và bây giờ chúng ta hãy thử sử dụng sức mạnh của các hàm mảng động mới. Để tiện lợi và đẹp hơn, chúng tôi cũng chuyển đổi bảng gốc thành bảng “thông minh” bằng cách sử dụng phím tắt Ctrl+T hoặc lệnh Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng).
Với sự giúp đỡ của họ, "cặp đôi sát thủ" này giải quyết vấn đề của chúng ta một cách rất duyên dáng:
Đây:
- Chức năng đầu tiên LỌC (LỌC) chỉ chọn những hàng đó từ bảng của chúng tôi, nơi trong cột Khách hàng - tên chúng tôi cần.
- Sau đó, hàm GRADE (LOẠI) sắp xếp các hàng đã chọn theo ngày theo thứ tự giảm dần, với giao dịch gần đây nhất ở trên cùng.
- Chức năng INDEX (MỤC LỤC) trích xuất hàng đầu tiên, tức là trả về giao dịch cuối cùng mà chúng ta cần.
- Và, cuối cùng, hàm FILTER bên ngoài loại bỏ các cột thứ 1 và 3 thừa khỏi kết quả (Mã đặt hàng и Khách hàng) và chỉ để lại ngày và số tiền. Đối với điều này, một mảng các hằng số được sử dụng. {0; 1; 0; 1}, xác định cột nào chúng ta muốn (1) hoặc không muốn (0) hiển thị.
Phương pháp 4: Tìm kết quả phù hợp cuối cùng trong Power Query
Vâng, vì mục đích đầy đủ, chúng ta hãy xem xét giải pháp cho vấn đề tìm kiếm ngược của chúng tôi bằng cách sử dụng bổ trợ Power Query. Với sự giúp đỡ của cô ấy, mọi thứ được giải quyết rất nhanh chóng và đẹp đẽ.
1. Hãy chuyển đổi bảng gốc của chúng ta thành một bảng “thông minh” bằng cách sử dụng phím tắt Ctrl+T hoặc lệnh Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng).
2. Tải nó vào Power Query bằng nút Từ Bảng / Phạm vi chuyển hướng Ngày (Dữ liệu - Từ Bảng / Phạm vi).
3. Chúng tôi sắp xếp (thông qua danh sách thả xuống của bộ lọc trong tiêu đề) bảng của chúng tôi theo thứ tự ngày tháng giảm dần, để các giao dịch gần đây nhất ở trên cùng.
4… Trong tab Chuyển đổi chọn một đội Nhóm theo (Chuyển đổi - Nhóm Theo) và đặt nhóm theo khách hàng và như một chức năng tổng hợp, hãy chọn tùy chọn Tất cả các dòng (Tất cả các hàng). Bạn có thể đặt tên cho cột mới bất kỳ thứ gì bạn thích - ví dụ: Chi tiết.
Sau khi nhóm, chúng tôi sẽ nhận được danh sách tên duy nhất của các khách hàng của chúng tôi và trong cột Chi tiết - các bảng với tất cả các giao dịch của từng người trong số họ, trong đó dòng đầu tiên sẽ là giao dịch mới nhất, đó là những gì chúng ta cần:
5. Thêm một cột mới được tính toán bằng nút Cột tùy chỉnh chuyển hướng Thêm cột (Thêm cột - Thêm cột tùy chỉnh)và nhập công thức sau:
Đây Chi tiết - đây là cột mà từ đó chúng tôi lấy bảng của khách hàng và 0 {} là số hàng mà chúng ta muốn trích xuất (đánh số hàng trong Power Query bắt đầu từ XNUMX). Chúng tôi nhận được một cột với các bản ghi (Kỷ lục), trong đó mỗi mục nhập là hàng đầu tiên từ mỗi bảng:
Nó vẫn để mở rộng nội dung của tất cả các bản ghi bằng nút có mũi tên kép trong tiêu đề cột Thỏa thuận cuối cùng chọn các cột mong muốn:
… Và sau đó xóa cột không còn cần thiết nữa Chi tiết bằng cách nhấp chuột phải vào tiêu đề của nó - Loại bỏ các cột (Loại bỏ các cột).
Sau khi tải kết quả lên trang tính qua Trang chủ - Đóng và tải - Đóng và tải vào (Trang chủ - Đóng & Tải - Đóng & Tải vào…) chúng tôi sẽ nhận được một bảng đẹp như vậy với danh sách các giao dịch gần đây, như chúng tôi muốn:
Khi bạn thay đổi dữ liệu nguồn, bạn không được quên cập nhật kết quả bằng cách nhấp chuột phải vào chúng - lệnh Cập nhật & Lưu (Làm mới) hoặc phím tắt Ctrl+Khác+F5.
- Hàm LOOKUP là hậu duệ của hàm VLOOKUP
- Cách sử dụng các hàm mảng động mới SORT, FILTER và UNIC
- Tìm ô không trống cuối cùng trong một hàng hoặc cột bằng hàm LOOKUP