Formula umum
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Ringkasan
Untuk mendapatkan nama dari nilai terbesar ke-n, Anda dapat menggunakan INDEX dan MATCH dengan fungsi LARGE. Dalam contoh yang ditampilkan, rumus di sel H5 adalah:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
di mana nama (B5: B16), dan skor (D5: D16) diberi nama rentang.
Penjelasan
Singkatnya, rumus ini menggunakan fungsi LARGE untuk mencari nilai terbesar ke-n dalam satu set data. Setelah kami memiliki nilai itu, kami memasukkannya ke dalam rumus INDEX dan MATCH standar untuk mengambil nama terkait. Dengan kata lain, kami menggunakan nilai terbesar ke-n seperti "kunci" untuk mengambil informasi terkait.
Fungsi LARGE adalah cara langsung untuk mendapatkan nilai terbesar ke-n dalam suatu rentang. Cukup berikan rentang untuk argumen pertama (larik), dan nilai untuk n sebagai argumen kedua (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Bekerja dari dalam ke luar, langkah pertama adalah mendapatkan nilai terbesar "pertama" dalam data dengan fungsi LARGE:
LARGE(score,F5) // returns 93
Dalam hal ini, nilai di F5 adalah 1, jadi kami meminta skor terbesar ke-1 (yaitu skor teratas), yaitu 93. Sekarang kita dapat menyederhanakan rumusnya menjadi:
=INDEX(name,MATCH(93,score,0))
Di dalam fungsi INDEX, fungsi MATCH diatur untuk menemukan posisi 93 dalam rentang skor bernama (D5: D16):
MATCH(93,score,0) // returns 3
Karena 93 muncul di baris ke-3, MATCH mengembalikan 3 langsung ke INDEX sebagai nomor baris, dengan nama sebagai array:
=INDEX(name,3) // Hannah
Terakhir, fungsi INDEX mengembalikan nama di baris ke-3, "Hannah".
Perhatikan bahwa kita mengambil nilai n dari kisaran F5: F7, untuk mendapatkan skor tertinggi ke-1, ke-2, dan ke-3 saat rumus disalin.
Ambil grup
Rumus dasar yang sama akan berfungsi untuk mengambil informasi terkait. Untuk mendapatkan grup untuk nilai terbesar, Anda cukup mengubah array yang disediakan ke INDEX dengan grup rentang bernama :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Dengan nilai 1 di F5, LARGE akan mendapatkan skor tertinggi, dan rumus akan mengembalikan "A".
Catatan: dengan Excel 365, Anda bisa menggunakan fungsi FILTER untuk membuat daftar hasil atas atau bawah secara dinamis.
Dengan XLOOKUP
Fungsi XLOOKUP juga dapat digunakan untuk mengembalikan nama dari nilai terbesar ke-n seperti ini:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE mengembalikan nilai terbesar, 93, langsung ke XLOOKUP sebagai nilai pencarian:
=XLOOKUP(93,score,name) // Hannah
Dengan skor rentang bernama (D5: D16) sebagai larik pencarian, dan nama (B5: B16) sebagai larik kembali, XLOOKUP mengembalikan "Hannah" seperti sebelumnya.
Menangani ikatan
Nilai duplikat dalam data numerik akan membuat "dasi". Jika terjadi seri pada nilai yang diberi peringkat, misalnya, jika nilai terbesar pertama dan kedua sama, LARGE akan mengembalikan nilai yang sama untuk masing-masing. Ketika nilai ini diteruskan ke fungsi MATCH, MATCH akan mengembalikan posisi pertandingan pertama, sehingga Anda akan melihat nama (depan) yang sama dikembalikan.
Jika ada kemungkinan ikatan, Anda mungkin ingin menerapkan semacam strategi pemutusan hubungan. Salah satu pendekatannya adalah dengan membuat kolom penolong baru dengan nilai yang telah disesuaikan untuk memutuskan hubungan. Kemudian gunakan nilai kolom pembantu untuk menentukan peringkat dan mengambil informasi. Ini membuat logika yang digunakan untuk memutuskan hubungan menjadi jelas dan eksplisit.
Pendekatan lain adalah memutuskan hubungan berdasarkan posisi saja (yaitu, seri pertama "menang"). Berikut adalah rumus yang menggunakan pendekatan itu:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Di sini, kami menggunakan MATCH untuk menemukan angka 1, dan kami membuat array pencarian menggunakan logika boolean yang (1) membandingkan semua skor dengan nilai yang dikembalikan oleh LARGE:
score=LARGE(score,F5)
dan (2) menggunakan rentang yang meluas, periksa apakah namanya sudah ada dalam daftar peringkat:
COUNTIF(H$4:H4,name)=0
Ketika sebuah nama sudah ada dalam daftar, itu "dibatalkan" oleh logika, dan nilai berikutnya (duplikat) cocok. Perhatikan rentang perluasan dimulai pada baris sebelumnya, untuk menghindari referensi melingkar.
Pendekatan ini berfungsi dalam contoh ini karena tidak ada nama duplikat di kolom nama. Namun, jika nama duplikat muncul dalam nilai yang diberi peringkat, pendekatannya perlu disesuaikan. Solusi termudah adalah memastikan bahwa nama itu unik.
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.
