Резюме
Щоб виділити кілька збігів в окремі комірки, в окремих рядках, ви можете використовувати формулу масиву на основі 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)
Цей масив переходить у SMALL. Значення k для SMALL (n-го) походить із розширеного діапазону:
ROWS($E$5:E5)
При копіюванні таблиці результатів діапазон розширюється, приводячи до збільшення k (n-го). Функція SMALL повертає кожен відповідний номер рядка, який надається функції INDEX як номер_ рядка, а масив має іменований діапазон "names".
Помилки обробки
Коли ROWS повертає значення k, яке не існує, SMALL видає помилку #NUM. Це відбувається після того, як відбулися всі збіги. Щоб придушити помилку, ми використовуємо IFERROR для виявлення помилки та повернення порожнього рядка ("").
