Všeobecný vzorec
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Zhrnutie
Ak chcete extrahovať všetky zhody na základe čiastočnej zhody, môžete použiť vzorec poľa založený na funkciách INDEX a AGGREGATE s podporou ISNUMBER a SEARCH. V zobrazenom príklade je vzorec v G5:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
s nasledujúcimi pomenovanými rozsahmi: „search“ = D5, „ct“ = D8, „data“ = B5: B55.
Poznámka: toto je vzorec poľa, ale nevyžaduje ovládanie + shift + enter, pretože AGGREGATE dokáže polia spracovať natívne.
Vysvetlenie
Jadrom tohto vzorca je funkcia INDEX, pričom funkcia AGGREGATE slúži na zistenie „n-tej zhody“ pre každý riadok v oblasti extraktu:
INDEX(data,nth_match_formula)
Takmer celá práca spočíva v zisťovaní a hlásení, ktoré riadky v údajoch sa zhodujú s hľadaným reťazcom, a v hlásení polohy každej zhodnej hodnoty s indexom INDEX. To sa deje pomocou takto nakonfigurovanej funkcie AGGREGATE:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Prvý argument, 15, hovorí, že AGGREGATE sa má správať ako MALÝ a vráti n-té najmenšie hodnoty. Druhým argumentom, 6, je možnosť ignorovať chyby. Tretím argumentom je výraz, ktorý generuje pole výsledkov zhody (popísané nižšie). Štvrtý argument, F5, slúži ako "k" v MALOM na určenie "n-tej" hodnoty.
AGGREGATE pracuje na poliach a výraz uvedený nižšie vytvára pole pre tretí argument vo vnútri AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Tu sa funkcia ROW používa na generovanie poľa relatívnych čísel riadkov a ISNUMBER a SEARCH sa používajú spolu na priradenie vyhľadávacieho reťazca k hodnotám v dátach, čím sa vygeneruje pole hodnôt TRUE a FALSE.
Chytrým bitom je vydelenie čísel riadkov výsledkami vyhľadávania. V matematickej operácii, ako je táto, sa TRUE správa ako 1 a FALSE ako nula. Výsledkom je, že čísla riadkov spojené s pozitívnou zhodou sa vydelia 1 a prežijú operáciu, zatiaľ čo čísla riadkov spojené s nezhodnými hodnotami sa zničia a stanú sa chybami # DIV / 0. Pretože AGGREGATE je nastavený na ignorovanie chýb, ignoruje chyby # DIV / 0 a vráti „n-té“ najmenšie číslo v zostávajúcich hodnotách, pričom použije číslo v stĺpci F pre „n-té“.
Riadenie výkonu
Rovnako ako všetky vzorce v poli, aj tento vzorec je z hľadiska zdrojov s veľkým súborom údajov „drahý“. Aby sa minimalizovali dopady na výkon, celý vzorec INDEX a MATCH je zabalený do IF takto:
=IF(F5>ct,"",formula)
kde pomenovaný rozsah „ct“ (D8) obsahuje tento vzorec:
=COUNTIF(data,"*"&search&"*")
Táto kontrola zastaví spustenie časti INDEX a AGGREGATE vzorca po extrakcii všetkých zodpovedajúcich hodnôt.
Pole vzorca s MALÝM
Ak vaša verzia programu Excel nemá funkciu AGGREGATE, môžete použiť alternatívny vzorec založený na MALOM a AK:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
