Formula generik
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Ringkasan
Untuk mendapatkan nama nilai terbesar ke-9, anda boleh menggunakan INDEX dan MATCH dengan fungsi LARGE. Dalam contoh yang ditunjukkan, formula dalam sel H5 adalah:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
di mana nama (B5: B16), dan skor (D5: D16) dinamakan julat.
Penjelasan
Ringkasnya, formula ini menggunakan fungsi BESAR untuk mencari nilai terbesar ke-9 dalam satu set data. Sebaik sahaja kita mempunyai nilai itu, kita memasukkannya ke formula INDEX dan MATCH standard untuk mendapatkan semula nama yang berkaitan. Dengan kata lain, kami menggunakan nilai terbesar ke-9 seperti "kunci" untuk mendapatkan maklumat yang berkaitan.
Fungsi LARGE adalah cara mudah untuk mendapatkan nilai terbesar ke-9 dalam julat. Cukup berikan julat untuk argumen pertama (array), 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 "1" dalam data dengan fungsi BESAR:
LARGE(score,F5) // returns 93
Dalam kes ini, nilai dalam F5 adalah 1, jadi kami meminta skor terbesar 1 (iaitu skor teratas), iaitu 93. Sekarang kita dapat mempermudah formula untuk:
=INDEX(name,MATCH(93,score,0))
Di dalam fungsi INDEX, fungsi MATCH disiapkan untuk mencari kedudukan 93 dalam skor julat yang dinamakan (D5: D16):
MATCH(93,score,0) // returns 3
Oleh kerana 93 muncul di baris ke-3, MATCH mengembalikan 3 terus ke INDEX sebagai nombor baris, dengan nama sebagai tatasusunan:
=INDEX(name,3) // Hannah
Akhirnya, fungsi INDEX mengembalikan nama pada baris ke-3, "Hannah".
Perhatikan kita mengambil nilai untuk n dari julat F5: F7, untuk mendapatkan skor tertinggi 1, 2, dan ke-3 ketika formula disalin ke bawah.
Ambil kumpulan
Formula asas yang sama akan berfungsi untuk mendapatkan maklumat yang berkaitan. Untuk mendapatkan kumpulan dengan nilai terbesar, anda boleh menukar susunan yang dibekalkan ke INDEX dengan kumpulan julat bernama :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Dengan nilai 1 di F5, LARGE akan mendapat skor tertinggi, dan formula akan mengembalikan "A".
Catatan: dengan Excel 365, anda boleh menggunakan fungsi FILTER untuk menyenaraikan hasil atas atau bawah secara dinamik.
Dengan XLOOKUP
Fungsi XLOOKUP juga boleh digunakan untuk mengembalikan nama nilai terbesar ke-9 seperti ini:
=XLOOKUP(LARGE(score,F5),score,name)
BESAR mengembalikan nilai terbesar, 93, terus ke XLOOKUP sebagai nilai carian:
=XLOOKUP(93,score,name) // Hannah
Dengan julat skor dinamakan (D5: D16) sebagai tatasusunan pencarian, dan nama (B5: B16) sebagai susunan pulangan, XLOOKUP mengembalikan "Hannah" seperti sebelumnya.
Mengendalikan hubungan
Nilai pendua dalam data berangka akan membuat "dasi". Sekiranya hubungan berlaku pada nilai yang diperingkat, misalnya, jika nilai pertama dan kedua terbesar sama, BESAR akan mengembalikan nilai yang sama untuk masing-masing. Apabila nilai ini diteruskan ke fungsi MATCH, MATCH akan mengembalikan kedudukan pertandingan pertama, jadi anda akan melihat nama (pertama) yang sama dikembalikan.
Sekiranya ada kemungkinan hubungan, anda mungkin ingin menerapkan semacam strategi pemutusan hubungan. Salah satu pendekatan adalah dengan membuat kolom penolong nilai yang telah disesuaikan untuk memutuskan hubungan. Kemudian gunakan nilai lajur penolong untuk memberi peringkat dan mendapatkan maklumat. Ini menjadikan logik yang digunakan untuk memutuskan hubungan jelas dan jelas.
Pendekatan lain adalah memutuskan hubungan berdasarkan kedudukan sahaja (iaitu tali pertama "menang"). Berikut adalah formula yang menggunakan pendekatan tersebut:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter, kecuali di Excel 365.
Di sini, kita menggunakan MATCH untuk mencari nombor 1, dan kita membina array carian menggunakan logik boolean yang (1) membandingkan semua skor dengan nilai yang dikembalikan oleh BESAR:
score=LARGE(score,F5)
dan (2) menggunakan semakan jangkauan yang meluas jika nama itu sudah ada dalam senarai peringkat:
COUNTIF(H$4:H4,name)=0
Apabila nama sudah ada dalam senarai, ia "dibatalkan" oleh logiknya, dan nilai (pendua) seterusnya dipadankan. Perhatikan rentang pengembangan bermula pada baris sebelumnya, untuk menghindari rujukan bulat.
Pendekatan ini berfungsi dalam contoh ini kerana tidak ada nama pendua di lajur nama. Walau bagaimanapun, jika nama pendua berlaku dalam nilai peringkat, pendekatan perlu disesuaikan. Penyelesaian yang paling mudah adalah memastikan bahawa nama itu unik.
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.
