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