Trên bài viết này, chúng ta sẽ tìm hiểu cách sử dụng chức năng XLOOKUP để lấy dữ liệu hiển thị (được lọc) trong Google Sheets. Để làm điều này, chúng ta sẽ sử dụng hàm LHF Lambda kết hợp với SUBTOTAL.
Các Cách Lọc Dữ Liệu Trên Google Sheets
Trước tiên, chúng ta hãy xem xét hai, ba cách lọc dữ liệu không cần thiết hoặc không sử dụng trong ô trên Google Sheets. Bạn có quen thuộc với cách bật/tắt chế độ lọc dữ liệu thông qua các tùy chọn “Data > Create a filter” hoặc “Data > Filter view > Create a new filter view”. Dưới đây là cách sử dụng hai, ba cách để lọc dữ liệu trên Google Sheets:
- Chọn “View > Group > Group row” để nhóm các hàng lại với nhau.
- Nhấp chuột phải vào hàng hoặc hàng được chọn và ẩn chúng đi.
Chúng ta đã biết hai, ba cách này. Tuy nhiên, còn rất nhiều cách khác nữa và sau đây là một số ví dụ.
XLOOKUP và Dữ Liệu Hiển Thị
XLOOKUP có nhiều tùy chọn để tìm kiếm trong một phạm vi. Tuy nhiên, không có tùy chọn nào để loại bỏ các hàng bị ẩn.
Để sử dụng XLOOKUP với dữ liệu hiển thị, chúng ta cần sử dụng một hàm khác, đó là SUBTOTAL.
Cách Sử Dụng XLOOKUP với Dữ Liệu Hiển Thị Trên Google Sheets
XLOOKUP cho phép tùy chỉnh nhiều tùy chọn tìm kiếm theo cặp khóa/giá trị và chế độ tìm kiếm. Tôi sẽ không đi vào chi tiết vì đã có hướng dẫn chi tiết về cách sử dụng XLOOKUP.
Ở đây, chúng ta sẽ sử dụng một công thức cơ bản để tìm hiểu cách sử dụng XLOOKUP với dữ liệu hiển thị trên Google Sheets. Nếu bạn có thêm câu hỏi, hãy để lại bình luận ở dưới.
Dữ Liệu Mẫu (Chưa Được Lọc)
Chúng ta có mã của một số mặt hàng trong cột A và số lượng tương ứng trong cột B. Hiện tại, tôi chưa lọc dữ liệu.
Để lấy số lượng của mặt hàng đầu tiên kết thúc bằng 1003, chúng ta có thể sử dụng XLOOKUP với ký tự đại diện cho một phần của từ khóa như sau:
=XLOOKUP("*1003",A2:A,B2:B,"Not Available!",2,1)
Công Thức
Cú Pháp: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
- search_key:
*1003 - lookup_range:
A2:A - result_range:
B2:B - missing_value:
Not Available! - match_mode:
2(tìm kiếm với ký tự đại diện) - search_mode:
1(tìm kiếm từ giá trị đầu tiên đến giá trị cuối cùng)
Bây giờ chúng ta sẽ xem cách XLOOKUP hoạt động với dữ liệu hiển thị hoặc đã được lọc trên Google Sheets.
Dữ Liệu Mẫu (Được Lọc) và XLOOKUP với Dữ Liệu Hiển Thị
Trước tiên, tôi sẽ lọc dữ liệu A1:B bằng cách chọn “Data > Create a filter”. Sau đó, tôi sẽ lọc cột A theo công thức tùy chỉnh sau:
=not(regexmatch(A2,"A"))
Như bạn có thể thấy, công thức XLOOKUP trong ô D1 không loại bỏ các hàng bị ẩn. Nó vẫn trả về giá trị trong hàng bị ẩn.
Để tìm hiểu cách sử dụng XLOOKUP với dữ liệu hiển thị, hãy thực hiện như sau:
Chúng ta có thể sử dụng công thức BYROW sau đây để tìm kiếm dữ liệu đã được lọc trên Google Sheets:
=BYROW(A2:A,LAMBDA(range,SUBTOTAL(103,range)))Lưu ý: Hãy xem hướng dẫn về hàm BYROW để hiểu rõ về công thức này.
Công thức trên sẽ trả về 1 hoặc 0 trong cột A2:A, tùy thuộc vào trạng thái của các hàng (ẩn hoặc không ẩn).
Bằng cách thêm 1 vào search_key và công thức trên vào lookup_range (phần cuối của cả hai công thức), chúng ta có thể loại bỏ các hàng được ẩn trong XLOOKUP.
Đôi khi, số 1 có thể đã là một phần của search_key của bạn. Để tránh vấn đề đó, hãy thay số 1 bằng “^1” và làm thay đổi tương tự trong lookup_range.
Vì chúng ta kết hợp hai mảng (thêm một kết quả mảng BYROW vào lookup_range), chúng ta phải sử dụng ArrayFormula cùng với XLOOKUP.
Đây là công thức để lấy dữ liệu hiển thị (được lọc) bằng XLOOKUP trên Google Sheets:
=ArrayFormula(xlookup("*1003^1",A2:A&"^"&BYROW(A2:A,LAMBDA(range,SUBTOTAL(103,range))),B2:B,"Not Available",2,1))Đó là tất cả. Cảm ơn bạn đã đọc bài viết này. Chúc bạn có những trải nghiệm thú vị!
