Công thức Excel: LỌC trên n giá trị hàng đầu với tiêu chí
Công thức Excel: LỌC trên n giá trị hàng đầu với tiêu chí

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 tiêu chí cụ thể, bạn có thể sử dụng Chức năng FILTER cùng với LỚNNẾU chức năng. 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"))

Ở đâu dữ liệu (B5: D16), nhóm (C5: C16) và ghi bàn (D5: D16) là 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 với LỚNNẾU chức năng. Kết quả là 3 điểm cao nhất bảng B.

Hàm FILTER áp dụng các tiêu chí với bao gồm tranh luận. Trong ví dụ này, các tiêu chí được xây dựng với logic boolean như thế này:

(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. Bởi vì chúng ta có tổng cộng 12 điểm, IF trả về một mảng với 12 kết quả như thế này:

{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 mảng tranh luận:

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, tràn vào 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ả trong thứ tự giảm dần theo điểm số, bạn có thể làm tổ công thức FILTER ban đầu bên trong Hàm SORT như thế này:

=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 mảng tranh luận. 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 giảm dần đặt hàng.

https://exceljet.net/formula/filter-on-top-n-values-with-criteria