KopsavilkumsLai atsevišķās šūnās iegūtu vairākas atbilstības atsevišķās rindās, varat izmantot masīva formulu, kuras pamatā ir INDEX un SMALL. Parādītajā piemērā E5 formula ir šāda:
{= IFERROR ( INDEX (names, SMALL ( IF (groups=E, ROW (names)- MIN ( ROW (names))+1), ROWS ($E:E5))),'')}
Šī ir masīva formula, un tā jāievada, izmantojot Control + Shift + Enter.
Pēc formulas ievadīšanas pirmajā šūnā velciet to uz leju un pāri, lai aizpildītu pārējās šūnas.
kā konvertēt datumusSkaidrojums
Piezīme: šī formula izmanto divus nosauktos diapazonus: “nosaukumi” attiecas uz C4: C11, bet “grupas” - uz B4: B11. Šie nosaukumi ir definēti arī iepriekš redzamajā ekrānuzņēmumā.
Šīs formulas būtība ir šāda: mēs izmantojam funkciju SMALL, lai iegūtu rindas numuru, kas atbilst 'n -tajai sakritībai'. Kad mums ir rindas numurs, mēs to vienkārši nododam funkcijas INDEX funkcijā, kas atgriež vērtību šajā rindā.
Triks ir tāds, ka SMALL strādā ar masīvu, ko IF dinamiski izveido šajā bitā:
kā pielietot šūnu stilus
IF (groups=E, ROW (names)- MIN ( ROW (names))+1)
Šis fragments pārbauda nosaukto diapazona “grupas” vērtību E4. Ja tas tiek atrasts, tas atgriež “normalizētu” rindas numuru no rindu numuru masīva, kas izveidots, izmantojot šo formulas daļu:
ROW (names)- MIN ( ROW (names))+1
Rezultāts ir masīvs, kas satur rindu numurus, kur ir atbilstība, un FALSE, ja nav. Masīvs izskatās apmēram šādi:
{1FALSEFALSEFALSEFALSE6FALSE}
kā atrast atkārtojumus Excel
Šis masīvs nonāk SMALL. SM vērtība SM (n.) Nāk no an diapazona paplašināšana :
ROWS ($E:E5)
Nokopējot rezultātu tabulu, diapazons paplašinās, izraisot k (n) palielināšanos. Funkcija SMALL atgriež katru atbilstošo rindas numuru, kas tiek piegādāts funkcijai INDEX kā rindas_numurs, ar nosaukto diapazonu 'nosaukumi' kā masīvu.
Apstrādes kļūdas
Kad ROWS atgriež vērtību k, kas neeksistē, SMALL izdara kļūdu #NUM. Tas notiek pēc tam, kad visas spēles ir notikušas. Lai izslēgtu kļūdu, mēs izmantojam IFERROR, lai konstatētu kļūdu un atgrieztos tukša virkne ('').
Autors Deivs Bruns
