Santrauka
Norėdami išskleisti kelias atitiktis į atskirus langelius, atskirose eilutėse, galite naudoti masyvo formulę, pagrįstą INDEX ir SMALL. Parodytame pavyzdyje formulė E5 yra:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
Tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“.
Įvedę formulę pirmoje langelyje, vilkite ją žemyn ir skersai, kad užpildytumėte kitas langelius.
Paaiškinimas
Pastaba: šioje formulėje naudojami du pavadinti diapazonai: „pavadinimai“ nurodo C4: C11, o „grupės“ - B4: B11. Šie pavadinimai taip pat apibrėžti aukščiau esančiame ekrano kadre.
Šios formulės esmė yra tokia: mes naudojame funkciją MAŽA, kad gautume eilutės numerį, atitinkantį „n-tas rungtynes“. Turėdami eilutės numerį, mes tiesiog perduodame jį į funkciją INDEX, kuri grąžina tos eilutės vertę.
Apgaulė yra ta, kad SMALL dirba su masyvu, kurį IF dinamiškai sukonstravo šiame bitelyje:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
Šis fragmentas išbando įvardytų diapazonų „grupių“ vertę E4. Jei rastas, jis pateikia „normalizuotą“ eilutės numerį iš eilutės numerių masyvo, sukurto naudojant šią formulės dalį:
ROW(names)-MIN(ROW(names))+1
Rezultatas yra masyvas, kuriame yra eilučių numeriai, kur yra atitiktis, ir FALSE, jei ne. Masyvas atrodo maždaug taip:
(1; NETIESA; NETIESA; NETIESA; NETIESA; 6; NETIESA)
Šis masyvas eina į MAŽĄ. „SMALL“ (n-oji) k vertė gaunama iš besiplečiančio diapazono:
ROWS($E$5:E5)
Nukopijavus rezultatų lentelėje, diapazonas išsiplečia, todėl k (n-tasis) padidėja. SMALL funkcija grąžina kiekvieną atitinkantį eilutės numerį, kuris tiekiamas funkcijai INDEX kaip eilės_numeris, o masyvas yra pavadintas diapazonas „vardai“.
Tvarkymo klaidos
Kai ROWS grąžina k, kurio nėra, reikšmę SMALL išmeta #NUM klaidą. Tai atsitinka po to, kai įvyko visos rungtynės. Norėdami užkirsti kelią klaidai, mes naudojame IFERROR klaidai užfiksuoti ir grąžinti tuščią eilutę ("").
