Công thức chung
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Tóm lược
Để lọc dữ liệu nhằm hiển thị n giá trị hàng đầu đáp ứng các tiêu chí cụ thể, bạn có thể sử dụng hàm FILTER cùng với các hàm LARGE và IF. Trong ví dụ được hiển thị, công thức trong F5 là:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
trong đó dữ liệu (B5: D16), nhóm (C5: C16) và điểm (D5: D16) là các phạm vi được đặt tên.
Giải trình
Công thức này sử dụng hàm FILTER để truy xuất dữ liệu dựa trên kiểm tra logic được xây dựng bằng hàm LARGE và IF. Kết quả là 3 điểm đứng đầu bảng B.
Hàm FILTER áp dụng tiêu chí với đối số bao gồm. Trong ví dụ này, các tiêu chí được xây dựng bằng logic boolean như sau:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Phía bên trái của biểu thức có điểm số lớn hơn hoặc bằng điểm cao thứ 3 trong nhóm B:
score>=LARGE(IF(group="b",score),3)
Hàm IF được sử dụng để đảm bảo LARGE chỉ hoạt động với điểm nhóm B. Vì chúng ta có tổng cộng 12 điểm, IF trả về một mảng có 12 kết quả như sau:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Lưu ý rằng điểm số duy nhất tồn tại trong hoạt động là từ Nhóm B. Tất cả các điểm số khác là FALSE. Mảng này được trả về trực tiếp cho LARGE dưới dạng đối số mảng:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE bỏ qua các giá trị FALSE và trả về điểm cao thứ 3, 83.
Bây giờ chúng ta có thể đơn giản hóa công thức thành:
=FILTER(data,(score>=83)*(group="b"))
giải quyết thành:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Cuối cùng, FILTER trả về các bản ghi cho Mason, Annie và Cassidy, các bản ghi này nằm trong phạm vi F5: H7.
Sắp xếp kết quả theo điểm số
Theo mặc định, FILTER sẽ trả về các bản ghi phù hợp theo cùng thứ tự mà chúng xuất hiện trong dữ liệu nguồn. Để sắp xếp kết quả theo thứ tự giảm dần theo điểm số, bạn có thể lồng công thức FILTER ban đầu bên trong hàm SORT như sau:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Ở đây, FILTER trả về kết quả trực tiếp cho hàm SORT dưới dạng đối số mảng. Chỉ mục sắp xếp được đặt thành 3 (điểm) và thứ tự sắp xếp được đặt thành -1, cho thứ tự giảm dần.
