Ištraukite kelias atitiktis į atskiras eilutes

Norėdami išgauti kelias atitiktis atskiroms ląstelėms, atskirose eilutėse, galite naudoti masyvo formulę, pagrįstą INDEX ir SMALL. Pateiktame pavyzdyje E5 formulė yra tokia:

 
{= IFERROR ( INDEX (names, SMALL ( IF (groups=E, ROW (names)- MIN ( ROW (names))+1), ROWS ($E:E5))),'')}

Tai masyvo formulė, kurią reikia įvesti naudojant „Control“ + „Shift“ + „Enter“.

Įvedę formulę pirmame langelyje, vilkite žemyn ir skersai, kad užpildytumėte kitas ląsteles.

„Excel“ sąlyginio formatavimo formulė „if“

Pastaba: šioje formulėje naudojami du pavadinti diapazonai: „vardai“ reiškia C4: C11, o „grupės“ - B4: B11. Šie pavadinimai taip pat apibrėžti aukščiau esančioje ekrano kopijoje.

Šios formulės esmė yra tokia: mes naudojame funkciją SMALL, kad gautume eilutės numerį, kuris atitinka „n“ rungtynę. Gavę eilutės numerį, mes tiesiog perkeliame jį į funkcijos INDEX funkciją, kuri grąžina tos eilutės reikšmę.

Apgaulė yra ta, kad „SMALL“ dirba su masyvu, kurį dinamiškai sukuria IF šiame bite:

kaip skaityti jautrumo ataskaitą

 
 IF (groups=E, ROW (names)- MIN ( ROW (names))+1)

Šis fragmentas patikrina pavadintų diapazonų „grupių“ vertę E4. Jei jis randamas, jis grąžina „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, kai ne. Masyvas atrodo maždaug taip:

{1FALSEFALSEFALSEFALSE6FALSE}

kaip naudotis „Excel“ duomenų lentele

Šis masyvas pereina į SMALL. SMALL (n -oji) k reikšmė gaunama iš an plečiantis asortimentas :

 
 ROWS ($E:E5)

Nukopijuojant rezultatų lentelę diapazonas plečiasi, todėl k (n) padidėja. Funkcija SMALL grąžina kiekvieną atitinkantį eilutės numerį, kuris pateikiamas funkcijai INDEX kaip eilutės numeris, o įvardytas diapazonas „vardai“ yra masyvas.

Tvarkymo klaidos

Kai ROWS grąžina k reikšmę, kurios nėra, SMALL pateikia #NUM klaidą. Tai atsitinka po visų rungtynių. Norėdami užkirsti kelią klaidai, mes naudojame IFERROR klaidai surasti ir grąžinti tuščia eilutė ('').