Bendroji formulė
(=INDEX(rng1,MATCH(MAX(LEN(rng1)*(rng2=criteria)),LEN(rng1)*(rng2=criteria),0)))
Santrauka
Norėdami rasti ilgiausią eilutę diapazone su kriterijais, galite naudoti masyvo formulę, pagrįstą INDEX, MATCH, LEN ir MAX. Parodytame pavyzdyje formulė F6 yra:
(=INDEX(names,MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)))
Kur „vardai“ yra pavadintas diapazonas C5: C14, o „klasė“ - pavadintas diapazonas B5: B14.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Šios formulės esmė yra funkcija MATCH, kuri nustato ilgiausios eilutės poziciją pagal pateiktus kriterijus:
MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)
Pastaba „MATCH“ yra nustatytas tiksliam atitikimui atlikti pateikiant nulį atitikties tipui. Norėdami sužinoti paieškos vertę, turime:
LEN(names)*(class=F5)
Funkcija LEN pateikia rezultatų (ilgių) masyvą, po vieną kiekvienam sąrašo vardui, kur klasė „A“ iš langelio F5:
(5;6;8;6;6;0;0;0;0;0)
Tai efektyviai išfiltruoja visą B klasę, o funkcija MAX grąžina didžiausią vertę 8.
Norėdami sukurti paieškos masyvą, mes naudojame tą patį metodą:
LEN(names)*(class=F5)
Ir gaukite tą patį rezultatą:
(5;6;8;6;6;0;0;0;0;0)
Paleidus LEN ir MAX, mes turime atitikties formulę su šiomis vertėmis:
MATCH(8,(5;6;8;6;6;0;0;0;0;0),0))
Tada MATCH grąžina 8 poziciją sąraše 3, kuri tiekiama į INDEX:
=INDEX(names,3)
Galiausiai INDEX pareigingai grąžina vertę 3-oje vardų pozicijoje , kuri yra „Jonathanas“.
