Formula umum
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Ringkasan
Untuk mendapatkan nama nilai terbesar ke-n dengan kriteria, Anda dapat menggunakan INDEX dan MATCH, fungsi LARGE, dan filter yang dibuat dengan fungsi IF. Dalam contoh yang diperlihatkan, rumus di sel G5, disalin, adalah:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
di mana nama (B5: B16), grup (C5: C16), dan skor (D5: D16) diberi nama rentang. Rumus mengembalikan nama yang terkait dengan nilai tertinggi ke-1, ke-2, dan ke-3 di Grup A.
Catatan: Ini adalah rumus array yang harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Penjelasan
Fungsi LARGE adalah cara mudah untuk mendapatkan nilai terbesar ke-n dalam suatu rentang:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Dalam contoh ini, kita dapat menggunakan fungsi LARGE untuk mendapatkan skor tertinggi, kemudian menggunakan skor tersebut seperti "kunci" untuk mengambil nama terkait dengan INDEX dan MATCH. Perhatikan bahwa kita mengambil nilai n dari kisaran F5: F7, untuk mendapatkan skor tertinggi ke-1, ke-2, dan ke-3.
Namun twist dalam hal ini adalah kita perlu membedakan antara skor di grup A dan grup B. Dengan kata lain, kita perlu menerapkan kriteria. Kami melakukan ini dengan fungsi IF, yang digunakan untuk "memfilter" nilai sebelum dievaluasi dengan LARGE. Sebagai contoh umum, untuk mendapatkan nilai terbesar (yaitu nilai 1) dalam range2 di mana rentang 1 = "A", Anda dapat menggunakan rumus seperti ini:
LARGE(IF(range="A",range2),1)
Catatan: menggunakan IF cara ini menjadikan ini rumus array.
Bekerja dari dalam ke luar, langkah pertama adalah mendapatkan nilai terbesar "pertama" dalam data yang terkait dengan Grup A dengan fungsi LARGE:
LARGE(IF(group="A",score),F5)
Dalam hal ini, nilai di F5 adalah 1, jadi kami meminta skor tertinggi di Grup A. Ketika fungsi IF dievaluasi, ini menguji setiap nilai dalam grup rentang bernama . Skor rentang bernama disediakan untuk value_if_true. Ini menghasilkan larik baru, yang dikembalikan langsung ke fungsi LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Perhatikan bahwa hanya skor yang bertahan dari filter berasal dari Grup A. LARGE kemudian mengembalikan skor sisa tertinggi, 93, langsung ke fungsi MATCH sebagai nilai pencarian. Sekarang kita dapat menyederhanakan rumusnya menjadi:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Sekarang kita dapat melihat bahwa fungsi MATCH dikonfigurasi menggunakan array yang difilter yang sama seperti yang kita lihat di atas. Fungsi IF lagi memfilter nilai yang tidak diinginkan, dan bagian MATCH dari rumus memutuskan menjadi:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Karena 93 muncul di posisi ke-3, MATCH mengembalikan 3 secara langsung ke fungsi INDEX:
=INDEX(name,3) // Hannah
Terakhir, fungsi INDEX mengembalikan nama di baris ke-3, "Hannah".
Dengan XLOOKUP
Fungsi XLOOKUP juga dapat digunakan untuk menyelesaikan masalah ini, 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 difilter oleh IF, dan mengembalikan hasil 93 ke XLOOKUP sebagai nilai pencarian:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Array pencarian juga dibuat dengan menggunakan IF sebagai filter pada skor dari Grup A. Dengan array yang dikembalikan diberikan sebagai nama (B5: B16). XLOOKUP mengembalikan "Hannah" sebagai hasil akhirnya.
Catatan
- Untuk mendapatkan nama nilai n dengan kriteria, (yaitu membatasi hasil ke grup A atau B) Anda perlu memperluas rumus untuk menggunakan logika tambahan.
- Di Excel 365, fungsi FILTER adalah cara yang lebih baik untuk membuat daftar hasil atas atau bawah secara dinamis. Pendekatan ini secara otomatis akan menangani hubungan.
