Công thức Excel: LỌC trên n giá trị hàng đầu
Công thức Excel: LỌC trên n giá trị hàng đầu

Công thức chung
=FILTER(data,range>=LARGE(range,n))
Tóm lược

Để lọc một tập hợp dữ liệu để hiển thị n giá trị hàng đầu, bạn có thể sử dụng Chức năng FILTER cùng với Hàm LARGE. Trong ví dụ được hiển thị, công thức trong F5 là:

=FILTER(data,score>=LARGE(score,3))

Ở đâu dữ liệu (B5: D16) 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 một bài kiểm tra logic được xây dựng bằng hàm LARGE.

Các Hàm LARGE là một cách đơn giản để nhận giá trị lớn thứ n trong một phạm vi. Chỉ cần cung cấp một phạm vi cho đối số đầu tiên (mảng), và một giá trị cho n làm đối số thứ hai (k):

 =LARGE(range,1) // 1st largest
 =LARGE(range,2) // 2nd largest
 =LARGE(range,3) // 3rd largest

Trong ví dụ này, logic cho FILTER được xây dựng bằng đoạn mã này:

score>=LARGE(score,3)

trả về TRUE khi điểm lớn hơn hoặc bằng điểm cao thứ 3. Vì có 12 ô trong phạm vi D5: D16, biểu thức này trả về một mảng gồm 12 giá trị TRUE và FALSE trong một mảng như thế này:

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

Mảng này được trả về trực tiếp cho hàm FILTER dưới dạng bao gồm đối số, nơi nó được sử dụng để lọc dữ liệu. Chỉ những hàng có kết quả là TRUE mới được đưa vào đầu ra cuối cùng. Kết quả từ công thức trong F5 bao gồm 3 điểm cao nhất trong dữ liệu, 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(score,3)),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