Загальна формула
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Резюме
Щоб витягти всі збіги на основі часткового збігу, ви можете використовувати формулу масиву на основі функцій INDEX та AGGREGATE, за підтримки ISNUMBER та SEARCH. У наведеному прикладі формула в G5 має вигляд:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
з такими іменованими діапазонами: "пошук" = D5, "ct" = D8, "дані" = B5: B55.
Примітка: це формула масиву, але вона не вимагає керування + shift + enter, оскільки AGGREGATE може обробляти масиви безпосередньо.
Пояснення
Ядром цієї формули є функція INDEX, за допомогою якої AGGREGATE використовується для визначення "n-го збігу" для кожного рядка в області вилучення:
INDEX(data,nth_match_formula)
Майже вся робота полягає у з'ясуванні та звітуванні про те, які рядки в "даних" відповідають пошуковому рядку, та звітуванні позиції для кожного відповідного значення INDEX. Це робиться за допомогою функції AGGREGATE, налаштованої так:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Перший аргумент, 15, говорить AGGREGATE поводитися як МАЛИЙ і повертати n-е найменше значення. Другий аргумент, 6, - це варіант ігнорування помилок. Третій аргумент - це вираз, який генерує масив відповідних результатів (описаних нижче). Четвертий аргумент, F5, діє як "k" у МАЛОМУ, щоб вказати значення "n-го".
AGGREGATE оперує масивами, і вираз нижче будує масив для третього аргументу всередині AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Тут функція ROW використовується для створення масиву відносних номерів рядків, а ISNUMBER та SEARCH використовуються разом для узгодження рядка пошуку зі значеннями в даних, що генерує масив значень TRUE та FALSE.
Розумний біт - розділити номери рядків на результати пошуку. В математичній операції, як це, TRUE поводиться як 1, а FALSE - як нуль. Результатом є те, що номери рядків, пов'язані з позитивним збігом, діляться на 1 і переживають операцію, тоді як номери рядків, пов'язані з невідповідними значеннями, руйнуються і стають помилками # DIV / 0. Оскільки AGGREGATE налаштовано на ігнорування помилок, він ігнорує помилки # DIV / 0 і повертає "n-те" найменше число в інших значеннях, використовуючи число у стовпці F для "n-го".
Управління ефективністю
Як і всі формули масиву, ця формула є "дорогою" з точки зору ресурсів з великим набором даних. Щоб мінімізувати вплив на продуктивність, вся формула INDEX і MATCH загортається в IF, як це:
=IF(F5>ct,"",formula)
де названий діапазон "ct" (D8) містить цю формулу:
=COUNTIF(data,"*"&search&"*")
Ця перевірка зупиняє запуск частини формули INDEX та AGGREGATE після вилучення всіх відповідних значень.
Формула масиву з МАЛИМ
Якщо у вашій версії Excel немає функції АГРЕГАТ, ви можете скористатися альтернативною формулою, заснованою на SMALL та IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
