Hàm CHOOSEROWS trong Google Sheets giúp tạo ra một mảng mới bằng cách chọn các số dòng được chỉ định từ phạm vi hiện có. Bạn có thể nhập số dòng dương hoặc âm.
Khi cung cấp số dòng âm, đếm sẽ bắt đầu từ dòng cuối cùng trong phạm vi (từ dưới lên), trong khi nó bắt đầu từ trên xuống khi sử dụng số dòng dương.
Hàm này mở ra nhiều khả năng để thao tác dữ liệu trong Google Sheets.
Ví dụ, hàm CHOOSEROWS có thể được sử dụng kết hợp với XMATCH và TOCOL để giải quyết một hạn chế của hàm XLOOKUP.
Hạn chế đó là gì?
Trong các tình huống liên quan đến nhiều khóa tìm kiếm, XLOOKUP sẽ truy xuất một giá trị từ một cột duy nhất, ngay cả khi kết quả chứa nhiều hơn một cột.
Chúng ta sẽ thảo luận vấn đề này chi tiết hơn ở dưới đây.
Hàm CHOOSEROWS – Cú pháp và đối số
Cú pháp:
CHOOSEROWS(dãy, số_dòng1, [số_dòng2, …])
Các đối số trong hàm CHOOSEROWS trong Google Sheets:
- dãy: Mảng nguồn hoặc dãy.
- số_dòng1: Số dòng (trong mảng) của dòng đầu tiên cần trả về.
- số_dòng2, …: Các số dòng bổ sung (nếu có) của các dòng cần trả về.
Các số dòng có thể là số nguyên dương hoặc số nguyên âm. Nếu số dòng không phải là số nguyên, hàm sẽ làm tròn xuống.
Sử dụng cơ bản
Giả sử chúng ta có một bảng chứa tên, điểm và xếp hạng của các học sinh, được sắp xếp theo xếp hạng, với những người đứng đầu xếp hạng đầu tiên.
Để lấy thông tin của người đứng hạng 5, loại bỏ hàng tiêu đề, chúng ta có thể sử dụng hàm CHOOSEROWS trong Google Sheets.
=CHOOSEROWS(B3:D12, 5)
Hàm này được giới thiệu vào năm 2023, và chúng ta đã có các giải pháp thay thế trong quá khứ. Đây là các công thức đó:
- Hàm Index.
=INDEX(B3:D12, 5, 0)
- Hàm Query.
=QUERY(B3:D12, “SELECT * LIMIT 1 OFFSET 4”, 0)
- Hàm Offset.
=OFFSET(B3, 4, 0, 1, 3)
Bây giờ, hãy giả sử chúng ta muốn lấy thông tin của người đứng hạng 5, 6 và 7. Để đạt được điều này với các công thức trên, chúng ta có thể sử dụng một hằng mảng như {5, 6, 7} hoặc SEQUENCE(3, 1, 5) trong hàm CHOOSEROWS trong Google Sheets.
Ví dụ:
=CHOOSEROWS(B3:D12, {5, 6, 7})
=CHOOSEROWS(B3:D12, SEQUENCE(3, 1, 5))
Tuy nhiên, hàm INDEX không phù hợp cho mục đích này. Tuy nhiên, QUERY và OFFSET có thể xử lý yêu cầu này một cách hiệu quả.
=QUERY(B3:D12, “SELECT * LIMIT 3 OFFSET 4”, 0)
=OFFSET(B3, 4, 0, 3, 3)
Hàm CHOOSEROWS để đảo ngược bảng trong Google Sheets
Trong bài hướng dẫn trước đó, chúng ta đã tìm hiểu cách đảo ngược bảng từ phải sang trái bằng cách sử dụng hàm CHOOSECOLS trong Google Sheets.
Tương tự, chúng ta có thể đạt được cùng một kết quả của việc đảo ngược bảng, lần này từ dưới lên, bằng cách sử dụng hàm CHOOSEROWS. Dưới đây là cách bạn có thể thực hiện:
Giả sử A2:D7 là phạm vi cần đảo từ dưới lên, bạn có thể sử dụng công thức sau:
=CHOOSEROWS(A2:D7, SEQUENCE(ROWS(A2:A7), 1, -1, -1))
Hàm SEQUENCE(ROWS(A2:A7), 1, -1, -1) tạo ra một loạt số âm từ -1 đến -7. Kết quả là công thức trả về các hàng từ dưới lên.
Công thức thay thế:
=SORT(A2:D7, SEQUENCE(ROWS(A2:A7)), 0)
Công thức thay thế này đạt được cùng một hiệu ứng đảo ngược bằng cách sử dụng hàm SORT phối hợp với SEQUENCE để sắp xếp các hàng theo thứ tự giảm dần.
Bạn cũng có thể quan tâm: Cách đảo ngược một cột trong Google Sheets – Cột hữu hạn và vô hạn.
Hạn chế mảng hai chiều của hàm XLOOKUP và công thức thay thế
Tìm kiếm thông tin cần thiết trong một tập dữ liệu lớn một cách thủ công có thể tốn thời gian. Ứng dụng bảng tính cung cấp các hàm tìm kiếm tích hợp để tối ưu quy trình này, và XLOOKUP là một trong những lựa chọn phổ biến.
Với XLOOKUP, chúng ta có thể tìm kiếm một khóa trong một cột cụ thể của một bảng và lấy thông tin cần thiết từ tất cả hoặc các cột cụ thể. Tuy nhiên, khi cố gắng tìm kiếm nhiều khóa, XLOOKUP không đáp ứng vì nó chỉ trả về thông tin từ một cột duy nhất.
Hiện tại, hàm XLOOKUP không hỗ trợ trả về kết quả dạng mảng hai chiều. Để vượt qua hạn chế này, chúng ta có thể sử dụng kết hợp của XMATCH, TOCOL và CHOOSEROWS.
Dưới đây là một ví dụ minh họa cho phương pháp thay thế này.
Hàm CHOOSEROWS trong việc giải quyết vấn đề mảng hai chiều của XLOOKUP trong Google Sheets
Vấn đề:
Tìm kiếm “fruits” và “pulses” trong cột đầu tiên của bảng và trả về kết quả từ cả hàng (ngoại trừ cột đầu tiên), tìm kiếm từ mục nhập cuối cùng đến mục nhập đầu tiên.
=ArrayFormula(XLOOKUP(B13:B14, B2:B7, C2:E7, “”, 0, -1))
Công thức này CHỈ trả về “strawberry” và “split peas” từ cột đầu tiên của kết quả.
Trong công thức này:
- search_key: B13:B14 (Hai khóa tìm kiếm)
- lookup_range: B2:B7 (Phạm vi tìm kiếm)
- result_range: C2:E7 (Mảng hai chiều)
- missing_value: “” (Giá trị trả về khi không có kết quả)
- match_mode: 0 (Trùng khớp chính xác của các khóa tìm kiếm)
- search_mode: -1 (Tìm kiếm từ mục nhập cuối cùng đến mục nhập đầu tiên)
Giải pháp:
Hãy sử dụng hàm CHOOSEROWS với TOCOL và XMATCH để giải quyết vấn đề trên.
=ArrayFormula(CHOOSEROWS(B2:E7, TOCOL(XMATCH(B13:B14, B2:B7, 0, -1), 3)))
Đây là cú pháp của XMATCH:
XMATCH(search_key, lookup_range, [match_mode], [search_mode])
Hàm XMATCH trả về số dòng cho các khóa tìm kiếm phù hợp và trả về #N/A cho các khóa không phù hợp.
Vai trò của TOCOL ở đây là loại bỏ các lỗi #N/A. Tất nhiên, chúng ta có thể sử dụng IFNA cho việc này. Tuy nhiên, nó để lại một ô trống có thể gây sự cố CHOOSEROWS.
Chúng tôi đã sử dụng các số chỉ mục phù hợp đó trong hàm CHOOSEROWS để trả về các hàng liên quan.
