Trong quá trình giới hạn số hàng, tôi thường sử dụng công thức Query trong Google Spreadsheets. Liệu chúng ta có thể sử dụng Query để trích xuất n hàng đầu từ mỗi nhóm trong Sheets không?
Câu trả lời là có! Chúng ta có thể giới hạn số hàng trong mỗi nhóm bằng Query trong Google Sheets.
Trước khi đi vào chi tiết, dưới đây là một bài hướng dẫn tương tự mà có thể hữu ích cho một số bạn: Cách tìm giá trị lớn nhất N trong mỗi nhóm trong Google Sheets.
Tôi sử dụng dữ liệu sau đây để giải thích cho bạn cách trích xuất n hàng đầu từ mỗi nhóm trong Google Sheets.
Trong dữ liệu minh hoạ, cột A chứa tên nhóm và cột B chứa các giá trị tương ứng.
Hãy nhớ rằng dữ liệu đã được sắp xếp theo cột A để sắp xếp các nhóm theo thứ tự.
Nếu dữ liệu của bạn chưa được sắp xếp (không có nhóm) thì có một công thức khác. Tôi đã bao gồm nó ở phần cuối của bài hướng dẫn này. Tuy nhiên, tôi khuyên bạn nên tìm hiểu cách giải quyết với dữ liệu đã được sắp xếp trước.
Cách trích xuất n hàng đầu từ mỗi nhóm
Dữ liệu đã được sắp xếp theo cột A:
Tôi có một danh sách chứa tên các loại hoa như Orchids, Tulips và Marigold trong cột A và số lượng cành hoa trong cột B.
Tôi muốn lọc ra n hàng đầu từ mỗi nhóm hoa. Với sự trợ giúp của mệnh đề giới hạn của Query, chúng ta có thể giới hạn số hàng, nhưng không phải từng nhóm.
Trong giải pháp này, tôi sử dụng mệnh đề Where của Query và một công thức đếm chạy.
Dưới đây là công thức trích xuất 2 hàng đầu từ mỗi nhóm hoa. Tôi đã sử dụng công thức này trong ô D2.
=Query({A2:B,ARRAYFORMULA(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)))},"Select Col1,Col2 where Col3<=2")Giải thích công thức trích xuất n hàng đầu trong mỗi nhóm:
Phần này giải thích công thức trên.
Thực chất, chúng ta có hai cột dữ liệu “vật lý”. Nhưng trong Query, có một cột thứ ba được tạo ra bởi công thức đếm chạy.
Công thức đếm chạy tạo ra các số tuần tự 1, 2, 3… bắt đầu lại từ 1 trong mỗi nhóm.
Nếu bạn nhập công thức đếm chạy trong một ô trống (ví dụ: ô C2) để kiểm tra, kết quả sẽ như sau:
Bạn muốn biết thêm về công thức đếm chạy ở trên?
Hãy kiểm tra: Công thức đếm chạy trong Google Sheets – Ví dụ cụ thể
Tôi đã thiết lập cột thứ ba này “ảo” trong Query. Trong mệnh đề Where của Query, tôi đã sử dụng toán tử so sánh “<=” để lọc dữ liệu, tức là Col3 <= 2. Điều đó có nghĩa là lấy hai hàng đầu tiên trong mỗi nhóm.
Hãy thay đổi số 2 này thành số hàng mà bạn muốn trích xuất từ mỗi nhóm.
Để trích xuất n hàng đầu từ mỗi nhóm, bạn có thể làm theo phương pháp trên trong Google Sheets.
Trích xuất n hàng đầu từ mỗi nhóm (chưa được sắp xếp)
Bạn có thể dễ dàng điều chỉnh công thức trên và sử dụng nó trong một nhóm dữ liệu chưa được sắp xếp như sau:
Dưới đây là công thức mới để trích xuất n hàng đầu từ mỗi nhóm chưa được sắp xếp trong Google Sheets.
=Query({sort(A2:B,1,0),ARRAYFORMULA(COUNTIFS(sort(A2:A,1,0),sort(A2:A,1,0),ROW(A2:A),"<="&ROW(A2:A)))},"Select Col1,Col2 where Col3<=2")Ở đây, tôi đã sử dụng công thức SORT với tất cả các phạm vi dữ liệu được sử dụng trong công thức, ngoại trừ trong hàm ROW.
Tôi đã sắp xếp dữ liệu đó “ảo” bằng cách sử dụng công thức SORT. Đây là thay đổi duy nhất trong công thức.
Đó là cách trích xuất n hàng đầu từ mỗi nhóm trong Google Sheets khi dữ liệu chưa được sắp xếp.
Chúc bạn thành công!
