Splošna formula
=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))
Povzetek
Če želite poiskati najpogosteje pojavljeno besedilo v obsegu na podlagi podanih meril, lahko uporabite matrično formulo, ki temelji na več Excelovih funkcijah INDEX, MATCH, MODE in IF. V prikazanem primeru je formula v G5:
=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))
kjer je "dobavitelj" imenovani obseg C5: C15, "odjemalec" pa imenovani obseg B5: B15.
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Pojasnilo
Če delamo od znotraj navzven, s funkcijo MATCH primerjamo obseg besedila s samim seboj, tako da MATCH damo enak obseg za iskalno vrednost in iskalno polje, z ničlo za vrsto ujemanja:
MATCH(supplier,supplier,0)
Ker je iskalna vrednost matrika z 10 vrednostmi, vrne MATCH polje 10 rezultatov:
(1;1;3;3;5;1;7;3;1;5;5)
Vsak element v tej matriki predstavlja prvi položaj, na katerem se v podatkih pojavi ime dobavitelja. Ta matrika se poda v funkcijo IF, ki se uporablja za filtriranje rezultatov samo za odjemalca A:
IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))
IF vrne filtrirano matriko v funkcijo MODE:
(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)
Upoštevajte, da v polju ostanejo samo položaji, povezani s stranko A. MODE prezre vrednosti FALSE in vrne najpogostejše število funkciji INDEX kot številko vrstice:
=INDEX(supplier,1)
Nazadnje, z imenovanim obsegom "dobavitelj" kot matriko, INDEX vrne "Brown", najpogostejši dobavitelj za odjemalca A.
