Formula umum
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Ringkasan
Untuk memfilter data untuk memperlihatkan nilai n teratas yang memenuhi kriteria tertentu, Anda dapat menggunakan fungsi FILTER bersama dengan fungsi LARGE dan IF. Dalam contoh yang ditunjukkan, rumus di F5 adalah:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
di mana data (B5: D16), grup (C5: C16) dan skor (D5: D16) diberi nama rentang.
Penjelasan
Rumus ini menggunakan fungsi FILTER untuk mengambil data berdasarkan tes logika yang dibuat dengan fungsi LARGE dan IF. Hasilnya adalah 3 skor teratas di grup B.
Fungsi FILTER menerapkan kriteria dengan argumen penyertaan. Dalam contoh ini, kriteria dibangun dengan logika boolean seperti ini:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Sisi kiri ekspresi menargetkan skor yang lebih besar dari atau sama dengan skor tertinggi ke-3 di grup B:
score>=LARGE(IF(group="b",score),3)
Fungsi IF digunakan untuk memastikan LARGE hanya bekerja dengan skor grup B. Karena kami memiliki total 12 skor, IF mengembalikan array dengan 12 hasil seperti ini:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Perhatikan bahwa satu-satunya skor yang bertahan dalam operasi ini berasal dari Grup B. Semua skor lainnya SALAH. Array ini dikembalikan langsung ke LARGE sebagai argumen array:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE mengabaikan nilai FALSE dan mengembalikan skor tertinggi ke-3, 83.
Sekarang kita dapat menyederhanakan rumusnya menjadi:
=FILTER(data,(score>=83)*(group="b"))
yang memutuskan untuk:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Akhirnya, FILTER mengembalikan rekaman untuk Mason, Annie, dan Cassidy, yang masuk ke kisaran F5: H7.
Urutkan hasil berdasarkan skor
Secara default, FILTER akan mengembalikan rekaman yang cocok dalam urutan yang sama dengan yang muncul di data sumber. Untuk mengurutkan hasil dalam urutan menurun menurut skor, Anda dapat menumpuk rumus FILTER asli di dalam fungsi SORT seperti ini:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Di sini, FILTER mengembalikan hasil secara langsung ke fungsi SORT sebagai argumen array. Indeks urutan disetel ke 3 (skor) dan urutan urutan disetel ke -1, untuk urutan menurun.
