Obecný vzorec
(=INDEX(rng1,MATCH(MAX(LEN(rng1)*(rng2=criteria)),LEN(rng1)*(rng2=criteria),0)))
souhrn
Chcete-li najít nejdelší řetězec v rozsahu s kritérii, můžete použít maticový vzorec založený na INDEX, MATCH, LEN a MAX. V zobrazeném příkladu je vzorec v F6:
(=INDEX(names,MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)))
Kde „names“ je pojmenovaný rozsah C5: C14 a „class“ je pojmenovaný rozsah B5: B14.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Jádrem tohoto vzorce je funkce MATCH, která pomocí zadaných kritérií vyhledá pozici nejdelšího řetězce:
MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)
Poznámka MATCH je nastaven na provádění přesné shody zadáním nuly pro typ shody. Pro vyhledávací hodnotu máme:
LEN(names)*(class=F5)
Funkce LEN vrací pole výsledků (délek), jeden pro každý název v seznamu, kde class = "A" z buňky F5:
(5;6;8;6;6;0;0;0;0;0)
Tím se efektivně odfiltruje celá třída B a funkce MAX poté vrátí největší hodnotu 8.
K vytvoření vyhledávacího pole používáme stejný přístup:
LEN(names)*(class=F5)
A získáte stejný výsledek:
(5;6;8;6;6;0;0;0;0;0)
Po spuštění LEN a MAX máme MATCH vzorec s těmito hodnotami:
MATCH(8,(5;6;8;6;6;0;0;0;0;0),0))
MATCH pak vrátí pozici 8 v seznamu, 3, který se přivádí do INDEXu takto:
=INDEX(names,3)
Nakonec INDEX poslušně vrátí hodnotu na 3. pozici jmen , což je „Jonathan“.
