Kopsavilkums
Lai izvilktu vairākas atbilstības atsevišķām šūnām, atsevišķās kolonnās varat izmantot masīva formulu, kuras pamatā ir INDEX un SMALL. Parādītajā piemērā formula F5 ir:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),""))
Šī ir masīva formula, un tā jāievada ar 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.
Paskaidrojums
Piezīme: šajā formulā tiek izmantoti divi nosauktie diapazoni: "nosaukumi" attiecas uz C5: C11 un "grupas" attiecas uz B5: B11. Šie vārdi ir noteikti arī iepriekš redzamajā ekrānuzņēmumā.
Šīs formulas būtība ir šāda: mēs izmantojam funkciju MAZA, lai ģenerētu rindas numuru, kas atbilst "n -tajam mačam". Kad mums ir rindas numurs, mēs vienkārši nododam to funkcijai INDEX funkcija, kas atgriež vērtību šajā rindā.
Triks ir tāds, ka SMALL strādā ar masīvu, kuru IF šajā bitā ir dinamiski izveidojis:
IF(groups=$E5,ROW(names)-MIN(ROW(names))+1)
Šis fragments pārbauda nosaukto diapazonu "grupas" vērtībai E5. Ja tiek atrasts, tas atgriež rindas numuru no relatīvo rindu numuru masīva, kas izveidots ar:
ROW(names)-MIN(ROW(names))+1
Gala rezultāts ir masīvs, kurā ir skaitļi, ja ir atbilstība, un FALSE, ja ne:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Šis masīvs nonāk MAZĀ. K vērtība SMALL (n-tajai) nāk no paplašināšanās diapazona:
COLUMNS($E$5:E5)
Kopējot rezultātu tabulā, diapazons paplašinās, izraisot k (n-to) pieaugumu. Funkcija SMALL atgriež katru atbilstošo rindas numuru, kas tiek piegādāts funkcijai INDEX kā rindas_numurs, ar masīvu nosaukto diapazonu "nosaukumi".
Rīcības kļūdas
Kad COLUMNS atgriež vērtību k, kuras nav, SMALL izmet kļūdu #NUM. Tas notiek pēc visu spēļu norises. Lai nomāktu kļūdu, mēs iesaiņojam formulu IFERROR funkcijā, lai noķertu kļūdas un atgrieztu tukšu virkni ("").
