Formula generik
=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))
Ringkasan
Untuk mencari teks yang paling kerap berlaku dalam julat, berdasarkan kriteria yang anda berikan, anda boleh menggunakan formula array berdasarkan beberapa fungsi Excel INDEX, MATCH, MODE, dan IF. Dalam contoh yang ditunjukkan, formula dalam G5 adalah:
=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))
di mana "pembekal" adalah julat bernama C5: C15, dan "pelanggan" adalah julat bernama B5: B15.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Bekerja dari dalam ke luar, kami menggunakan fungsi MATCH untuk mencocokkan julat teks dengan dirinya sendiri, dengan memberikan julat yang sama untuk nilai pencarian dan tatasusunan, dengan sifar untuk jenis padanan:
MATCH(supplier,supplier,0)
Oleh kerana nilai pencarian adalah array dengan 10 nilai, MATCH mengembalikan array 10 hasil:
(1;1;3;3;5;1;7;3;1;5;5)
Setiap item dalam array ini mewakili kedudukan pertama di mana nama pembekal muncul dalam data. Array ini dimasukkan ke dalam fungsi IF, yang digunakan untuk menyaring hasil untuk Pelanggan A sahaja:
IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))
JIKA mengembalikan array yang ditapis ke fungsi MODE:
(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)
Perhatikan hanya kedudukan yang berkaitan dengan Pelanggan A yang kekal dalam array. MODE mengabaikan nilai FALSE dan mengembalikan nombor yang paling kerap berlaku ke fungsi INDEX sebagai nombor baris:
=INDEX(supplier,1)
Akhirnya, dengan julat "pembekal" yang dinamakan sebagai array, INDEX mengembalikan "Brown", pembekal yang paling kerap berlaku untuk Pelanggan A.
