Formula generik
(=INDEX(rng1,MATCH(MAX(LEN(rng1)*(rng2=criteria)),LEN(rng1)*(rng2=criteria),0)))
Ringkasan
Untuk mencari rentetan terpanjang dalam julat dengan kriteria, anda boleh menggunakan formula larik berdasarkan INDEX, MATCH, LEN dan MAX. Dalam contoh yang ditunjukkan, formula dalam F6 adalah:
(=INDEX(names,MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)))
Di mana "nama" adalah julat bernama C5: C14, dan "kelas" adalah julat bernama B5: B14.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Inti formula ini adalah fungsi MATCH, yang menentukan kedudukan rentetan terpanjang menggunakan kriteria yang disediakan:
MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)
Catatan MATCH disiapkan untuk melakukan padanan tepat dengan memberikan sifar untuk jenis pertandingan. Untuk nilai carian, kami mempunyai:
LEN(names)*(class=F5)
Fungsi LEN mengembalikan pelbagai hasil (panjang), satu untuk setiap nama dalam senarai di mana class = "A" dari sel F5:
(5;6;8;6;6;0;0;0;0;0)
Ini menyaring semua Kelas B dengan berkesan, dan fungsi MAX kemudian mengembalikan nilai terbesar, 8.
Untuk membina array carian, kami menggunakan pendekatan yang sama:
LEN(names)*(class=F5)
Dan dapatkan hasil yang sama:
(5;6;8;6;6;0;0;0;0;0)
Selepas LEN dan MAX dijalankan, kami mempunyai formula MATCH dengan nilai-nilai ini:
MATCH(8,(5;6;8;6;6;0;0;0;0;0),0))
MATCH kemudian mengembalikan kedudukan 8 dalam senarai, 3, yang masuk ke INDEX seperti ini:
=INDEX(names,3)
Akhirnya, INDEX memberikan nilai pada kedudukan ke-3 nama , "Jonathan".
