Formula generik
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Ringkasan
Untuk mendapatkan nama nilai terbesar ke-9 dengan kriteria, anda boleh menggunakan INDEX dan MATCH, fungsi LARGE, dan filter yang dibuat dengan fungsi IF. Dalam contoh yang ditunjukkan, formula dalam sel G5, disalin ke bawah, adalah:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
di mana nama (B5: B16), kumpulan (C5: C16), dan skor (D5: D16) dinamakan julat. Rumus mengembalikan nama yang dikaitkan dengan nilai tertinggi 1, 2 dan 3 dalam Kumpulan A.
Catatan: Ini formula larik yang mesti dimasukkan dengan kawalan + shift + enter, kecuali di Excel 365.
Penjelasan
Fungsi LARGE adalah cara mudah untuk mendapatkan nilai terbesar ke-9 dalam julat:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Dalam contoh ini, kita boleh menggunakan fungsi BESAR untuk mendapatkan skor tertinggi, kemudian menggunakan skor seperti "kunci" untuk mengambil nama yang berkaitan dengan INDEX dan MATCH. Perhatikan bahawa kita mengambil nilai untuk n dari julat F5: F7, untuk mendapatkan skor tertinggi 1, 2, dan 3.
Kelainan dalam kes ini ialah kita perlu membezakan antara skor dalam kumpulan A dan kumpulan B. Dengan kata lain, kita perlu menerapkan kriteria. Kami melakukan ini dengan fungsi IF, yang digunakan untuk "menyaring" nilai sebelum dinilai dengan BESAR. Sebagai contoh umum, untuk mendapatkan nilai terbesar (iaitu nilai 1) dalam range2 di mana range 1 = "A", anda boleh menggunakan formula seperti ini:
LARGE(IF(range="A",range2),1)
Catatan: menggunakan JIKA dengan cara ini menjadikan ini formula larik.
Berusaha dari dalam ke luar, langkah pertama adalah mendapatkan nilai terbesar "1" dalam data yang berkaitan dengan Kumpulan A dengan fungsi BESAR:
LARGE(IF(group="A",score),F5)
Dalam kes ini, nilai di F5 adalah 1, jadi kami meminta skor teratas dalam Kumpulan A. Ketika fungsi IF dinilai, ia menguji setiap nilai dalam kumpulan julat yang dinamakan . Skor julat dinamakan disediakan untuk value_if_true. Ini menghasilkan array baru, yang dikembalikan terus ke fungsi BESAR:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Perhatikan satu-satunya skor yang bertahan dalam saringan adalah dari Kumpulan A. BESAR kemudian mengembalikan skor tertinggi yang tinggal, 93, terus ke fungsi MATCH sebagai nilai carian. Kita sekarang boleh mempermudah formula untuk:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Sekarang kita dapat melihat bahawa fungsi MATCH dikonfigurasikan menggunakan array yang ditapis yang sama seperti yang kita lihat di atas. Fungsi IF sekali lagi menyaring nilai yang tidak diingini, dan bahagian MATCH dari formula memutuskan untuk:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Oleh kerana 93 muncul di kedudukan ke-3, MATCH mengembalikan 3 secara langsung ke fungsi INDEX:
=INDEX(name,3) // Hannah
Akhirnya, fungsi INDEX mengembalikan nama pada baris ke-3, "Hannah".
Dengan XLOOKUP
Fungsi XLOOKUP juga dapat digunakan untuk menyelesaikan masalah ini, dengan menggunakan pendekatan yang sama seperti yang dijelaskan di atas:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Seperti di atas, LARGE dikonfigurasi untuk bekerja dengan array yang disaring oleh IF, dan mengembalikan hasil 93 ke XLOOKUP sebagai nilai pencarian:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Susunan carian juga dibuat dengan menggunakan IF sebagai penapis skor dari Kumpulan A. Dengan susunan pulangan disediakan sebagai nama (B5: B16). XLOOKUP mengembalikan "Hannah" sebagai hasil akhir.
Catatan
- Untuk mendapatkan nama nilai n dengan kriteria, (iaitu hadkan hasil kepada kumpulan A atau B) anda perlu memperluas formula untuk menggunakan logik tambahan.
- Dalam Excel 365, fungsi FILTER adalah cara yang lebih baik untuk menyenaraikan hasil atas atau bawah secara dinamik. Pendekatan ini secara automatik akan mengatasi hubungan.
