Formula generică
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
rezumat
Pentru a extrage toate potrivirile pe baza unei potriviri parțiale, puteți utiliza o formulă matrice bazată pe funcțiile INDEX și AGGREGATE, cu suport de la ISNUMBER și SEARCH. În exemplul prezentat, formula din G5 este:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
cu următoarele intervale denumite: „căutare” = D5, „ct” = D8, „date” = B5: B55.
Notă: aceasta este o formulă matrice, dar nu necesită control + shift + enter, deoarece AGGREGATE poate gestiona matrici în mod nativ.
Explicaţie
Nucleul acestei formule este funcția INDEX, cu AGGREGATE folosit pentru a afla „a n-a potrivire” pentru fiecare rând din zona de extragere:
INDEX(data,nth_match_formula)
Aproape toată munca constă în a afla și a raporta care rânduri din „date” se potrivesc cu șirul de căutare și a raporta poziția pentru fiecare valoare potrivită la INDEX. Acest lucru se face cu funcția AGGREGATE configurată astfel:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Primul argument, 15, îi spune lui AGGREGATE să se comporte ca MIC și să întoarcă cele mai mici valori. Al doilea argument, 6, este o opțiune de ignorare a erorilor. Al treilea argument este o expresie care generează o serie de rezultate potrivite (descrise mai jos). Al patrulea argument, F5, acționează ca "k" în SMALL pentru a specifica valoarea "nth".
AGGREGATE funcționează pe tablouri, iar expresia de mai jos construiește o matrice pentru al treilea argument din interiorul AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Aici, funcția ROW este utilizată pentru a genera o serie de numere de rând relative, iar ISNUMBER și SEARCH sunt utilizate împreună pentru a potrivi șirul de căutare cu valorile din date, care generează o serie de valori ADEVĂRATE și FALSE.
Bitul inteligent este să împărțiți numerele rândurilor la rezultatele căutării. Într-o operație de matematică de genul acesta, TRUE se comportă ca 1, iar FALSE se comportă ca zero. Rezultatul este că numerele de rând asociate cu o potrivire pozitivă sunt împărțite la 1 și supraviețuiesc operației, în timp ce numerele de rând asociate cu valori care nu se potrivesc sunt distruse și devin erori # DIV / 0. Deoarece AGGREGATE este setat să ignore erorile, acesta ignoră erorile # DIV / 0 și returnează cel de-al "-lea" cel mai mic număr din valorile rămase, folosind numărul din coloana F pentru "al n-lea".
Gestionarea performanței
Ca toate formulele matrice, această formulă este „scumpă” în ceea ce privește resursele cu un set mare de date. Pentru a minimiza impactul asupra performanței, întreaga formulă INDEX și MATCH este înfășurată în IF astfel:
=IF(F5>ct,"",formula)
unde intervalul numit "ct" (D8) deține această formulă:
=COUNTIF(data,"*"&search&"*")
Această verificare oprește executarea părții INDEX și AGGREGATE a formulei odată ce toate valorile potrivite au fost extrase.
Formula matricei cu MICI
Dacă versiunea dvs. de Excel nu are funcția AGREGARE, puteți utiliza o formulă alternativă bazată pe MIC și IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
