Formula generik
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Ringkasan
Untuk menyaring data untuk menunjukkan nilai n teratas yang memenuhi kriteria tertentu, anda boleh menggunakan fungsi FILTER bersama dengan fungsi LARGE dan IF. Dalam contoh yang ditunjukkan, formula dalam F5 adalah:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
di mana data (B5: D16), kumpulan (C5: C16) dan skor (D5: D16) dinamakan julat.
Penjelasan
Formula ini menggunakan fungsi FILTER untuk mengambil data berdasarkan ujian logik yang dibina dengan fungsi LARGE dan IF. Hasilnya adalah 3 markah teratas dalam kumpulan B.
Fungsi FILTER menerapkan kriteria dengan argumen include. Dalam contoh ini, kriteria dibina dengan logik boolean seperti ini:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Bahagian kiri ungkapan menargetkan skor lebih besar daripada atau sama dengan skor tertinggi ke-3 dalam kumpulan B:
score>=LARGE(IF(group="b",score),3)
Fungsi IF digunakan untuk memastikan LARGE hanya berfungsi dengan skor kumpulan B. Oleh kerana kita mempunyai 12 markah keseluruhan, JIKA mengembalikan array dengan 12 hasil seperti ini:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Perhatikan satu-satunya skor yang bertahan dari operasi adalah dari Kumpulan B. Semua skor lain adalah PALSU. Susunan ini dikembalikan terus ke BESAR sebagai argumen array:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE mengabaikan nilai PALSU dan mengembalikan skor tertinggi ke-3, 83
Kita sekarang boleh mempermudah formula untuk:
=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 rekod untuk Mason, Annie, dan Cassidy, yang masuk ke julat F5: H7.
Isih hasil mengikut skor
Secara lalai, FILTER akan mengembalikan rekod yang sepadan dengan urutan yang sama seperti yang terdapat dalam data sumber. Untuk menyusun hasil mengikut urutan menurun mengikut skor, anda boleh memasukkan formula FILTER yang 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 terus ke fungsi SORT sebagai argumen array. Indeks urutan ditetapkan ke 3 (skor) dan urutan urutan ditetapkan ke -1, untuk urutan menurun.
