Santrauka
Norėdami išskleisti kelias atitiktis į atskirus langelius, atskiruose stulpeliuose galite naudoti masyvo formulę, pagrįstą INDEX ir SMALL. Parodytame pavyzdyje formulė F5 yra:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($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 C5: C11, o „grupės“ - B5: B11. Šie pavadinimai taip pat apibrėžti aukščiau esančiame ekrano kadre.
Šios formulės esmė yra tokia: mes naudojame funkciją SMALL, kad generuotume eilutės numerį, atitinkantį „n-ąjį atitikimą“. 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=$E5,ROW(names)-MIN(ROW(names))+1)
Šis fragmentas patikrina įvardytų diapazonų „grupes“ pagal vertę E5. Jei bus rasta, jis grąžins eilutės numerį iš santykinių eilučių skaičių masyvo, sukurto naudojant:
ROW(names)-MIN(ROW(names))+1
Galutinis rezultatas yra masyvas, kuriame yra skaičiai, kur yra atitiktis, ir FALSE, jei ne:
(1; NETIESA; NETIESA; NETIESA; NETIESA; 6; NETIESA)
Šis masyvas eina į MAŽĄ. „SMALL“ (n-oji) k vertė gaunama iš besiplečiančio diapazono:
COLUMNS($E$5:E5)
Nukopijavus per rezultatų lentelę, 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 COLUMNS pateikia k reikšmę, kurios nėra, SMALL išmeta #NUM klaidą. Tai atsitinka po to, kai įvyko visos rungtynės. Norėdami užkirsti kelią klaidai, formulę įtraukiame į IFERROR funkciją, kad užfiksuotume klaidas ir grąžintume tuščią eilutę ("").
