Обобщение
За да извлечете множество съвпадения в отделни клетки, в отделни редове, можете да използвате формула на масив, базирана на INDEX и SMALL. В показания пример формулата в E5 е:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
Това е формула на масив и трябва да се въведе с Control + Shift + Enter.
След като въведете формулата в първата клетка, плъзнете я надолу и назад, за да попълните останалите клетки.
Обяснение
Забележка: тази формула използва два именувани диапазона: "имена" се отнася до C4: C11, а "групи" се отнася до B4: B11. Тези имена са дефинирани и на снимката на екрана по-горе.
Същността на тази формула е следната: ние използваме функцията SMALL, за да получим номер на ред, който съответства на „n-то съвпадение“. След като получим номера на реда, ние просто го предаваме във функцията INDEX функция, която връща стойността в този ред.
Номерът е, че SMALL работи с масив, който е динамично конструиран от IF в този бит:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
Този фрагмент тества посочения диапазон "групи" за стойността в E4. Ако бъде намерен, той връща "нормализиран" номер на ред от масив от номера на редове, създаден с тази част от формулата:
ROW(names)-MIN(ROW(names))+1
Резултатът е масив, който съдържа номера на редове, където има съвпадение, и FALSE, където не. Масивът изглежда по следния начин:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Този масив преминава в МАЛКИ. Стойността k за SMALL (n-та) идва от разширяващ се диапазон:
ROWS($E$5:E5)
Когато се копира надолу в таблицата с резултати, диапазонът се разширява, което води до увеличаване на k (nth). Функцията SMALL връща всеки съвпадащ номер на ред, който се предоставя на функцията INDEX като row_num, като наименованието на имената на диапазона е като масив.
Обработка на грешки
Когато ROWS връща стойност за k, която не съществува, SMALL извежда грешка #NUM. Това се случва, след като са настъпили всички мачове. За да потиснем грешката, използваме IFERROR, за да хванем грешката и да върнем празен низ ("").
