Üldine valem
=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))
Kokkuvõte
Pakutavate kriteeriumide põhjal vahemikus kõige sagedamini esineva teksti leidmiseks võite kasutada massiivivalemit, mis põhineb mitmel Exceli funktsioonil INDEX, MATCH, MODE ja IF. Näidatud näites on valem G5-s järgmine:
=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))
kus "tarnija" on nimega vahemik C5: C15 ja "klient" on nimega vahemik B5: B15.
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.
Selgitus
Seestpoolt töötades kasutame funktsiooni MATCH tekstivahemiku sobitamiseks iseendaga, andes MATCH-ile otsingu väärtuse ja otsingu massiivi jaoks sama vahemiku, vastetüübi korral on null:
MATCH(supplier,supplier,0)
Kuna otsingu väärtus on 10 väärtusega massiiv, tagastab MATCH 10 tulemuse massiivi:
(1;1;3;3;5;1;7;3;1;5;5)
Selle massiivi iga üksus tähistab esimest positsiooni, kus tarnija nimi andmetes kuvatakse. See massiiv sisestatakse funktsiooni IF, mida kasutatakse ainult kliendi A tulemuste filtreerimiseks:
IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))
IF tagastab filtreeritud massiivi funktsioonile MODE:
(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)
Pange tähele, et massiivi jäävad ainult kliendiga A seotud positsioonid. MODE ignoreerib FALSE väärtusi ja tagastab rea numbri funktsioonile INDEX kõige sagedamini esineva numbri:
=INDEX(supplier,1)
Lõpuks tagastab massiivina nimega vahemik "tarnija" INDEX kliendi A kõige sagedamini esineva tarnija "Pruun".
