Generieke formule
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Samenvatting
Om alle overeenkomsten te extraheren op basis van een gedeeltelijke overeenkomst, kunt u een matrixformule gebruiken die is gebaseerd op de functies INDEX en AGGREGATE, met ondersteuning van ISNUMBER en SEARCH. In het getoonde voorbeeld is de formule in G5:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
met de volgende benoemde bereiken: "zoeken" = D5, "ct" = D8, "data" = B5: B55.
Opmerking: dit is een matrixformule, maar het vereist geen control + shift + enter, aangezien AGGREGATE arrays native kan verwerken.
Uitleg
De kern van deze formule is de INDEX-functie, waarbij AGGREGATE wordt gebruikt om de "n-de overeenkomst" voor elke rij in het extractiegebied te bepalen:
INDEX(data,nth_match_formula)
Bijna al het werk bestaat uit het uitzoeken en rapporteren welke rijen in "data" overeenkomen met de zoekreeks, en het rapporteren van de positie van elke overeenkomende waarde aan INDEX. Dit wordt gedaan met de AGGREGATE-functie die als volgt is geconfigureerd:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Het eerste argument, 15, vertelt AGGREGATE om zich als SMALL te gedragen en de n-de kleinste waarden te retourneren. Het tweede argument, 6, is een optie om fouten te negeren. Het derde argument is een uitdrukking die een reeks overeenkomende resultaten genereert (hieronder beschreven). Het vierde argument, F5, werkt als "k" in SMALL om de "n-de" waarde te specificeren.
AGGREGATE werkt op arrays en de onderstaande uitdrukking bouwt een array op voor het derde argument binnen AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Hier wordt de ROW-functie gebruikt om een array van relatieve rijnummers te genereren, en ISNUMBER en SEARCH worden samen gebruikt om de zoekreeks te vergelijken met waarden in de gegevens, wat een array van TRUE en FALSE waarden genereert.
Het slimme is om de rijnummers te delen door de zoekresultaten. In een wiskundige bewerking als deze gedraagt TRUE zich als 1 en FALSE gedraagt zich als nul. Het resultaat is dat rijnummers die zijn gekoppeld aan een positieve overeenkomst, worden gedeeld door 1 en de bewerking overleven, terwijl rijnummers die zijn gekoppeld aan niet-overeenkomende waarden worden vernietigd en # DIV / 0-fouten worden. Omdat AGGREGATE is ingesteld om fouten te negeren, negeert het de # DIV / 0-fouten en retourneert het "n-de" kleinste getal in de resterende waarden, waarbij het getal in kolom F voor "n-de" wordt gebruikt.
Prestaties beheren
Zoals alle matrixformules is deze formule "duur" in termen van bronnen met een grote gegevensset. Om de impact op de prestaties te minimaliseren, is de volledige INDEX- en MATCH-formule als volgt in IF verpakt:
=IF(F5>ct,"",formula)
waar het benoemde bereik "ct" (D8) deze formule bevat:
=COUNTIF(data,"*"&search&"*")
Deze controle stopt het INDEX- en AGGREGATE-gedeelte van de formule zodra alle overeenkomende waarden zijn geëxtraheerd.
Matrixformule met SMALL
Als uw versie van Excel de AGGREGATE-functie niet heeft, kunt u een alternatieve formule gebruiken op basis van SMALL en IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
