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 hàm 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))
trong đó dữ liệu (B5: D16) 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.
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à đố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ư sau:
(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 đối số include, 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 kết quả 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ả 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(score,3)),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.
