Vispārēja formula
(=INDEX(rng1,MATCH(MAX(LEN(rng1)*(rng2=criteria)),LEN(rng1)*(rng2=criteria),0)))
Kopsavilkums
Lai atrastu garāko virkni diapazonā ar kritērijiem, varat izmantot masīva formulu, kuras pamatā ir INDEX, MATCH, LEN un MAX. Parādītajā piemērā formula F6 ir:
(=INDEX(names,MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)))
Kur "nosaukumi" ir nosauktais diapazons C5: C14 un "klase" ir nosauktais diapazons B5: B14.
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + shift + enter.
Paskaidrojums
Šīs formulas kodols ir funkcija MATCH, kas atrod garākās virknes pozīciju, izmantojot komplektācijā iekļautos kritērijus:
MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)
Piezīme MATCH ir iestatīts, lai veiktu precīzu atbilstību, norādot nulli atbilstības veidam. Lai iegūtu uzmeklēšanas vērtību, mums ir:
LEN(names)*(class=F5)
Funkcija LEN atgriež rezultātu masīvu (garumu), katram katram nosaukumam sarakstā, kur class = "A" no šūnas F5:
(5;6;8;6;6;0;0;0;0;0)
Tas efektīvi filtrē visu B klasi, un funkcija MAX pēc tam atgriež lielāko vērtību 8.
Lai izveidotu uzmeklēšanas masīvu, mēs izmantojam to pašu pieeju:
LEN(names)*(class=F5)
Un iegūstiet to pašu rezultātu:
(5;6;8;6;6;0;0;0;0;0)
Pēc LEN un MAX palaišanas mums ir MATCH formula ar šīm vērtībām:
MATCH(8,(5;6;8;6;6;0;0;0;0;0),0))
Tad MATCH atgriež 8 pozīciju sarakstā 3, kas tiek ievadīts INDEX šādi:
=INDEX(names,3)
Visbeidzot, INDEX apzinīgi atgriež vērtību vārdu 3. pozīcijā , kas ir "Jonathan".
