Vispārēja formula
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Kopsavilkums
Lai izvilktu visas atbilstības, pamatojoties uz daļēju atbilstību, varat izmantot masīva formulu, kas balstīta uz INDEX un AGGREGATE funkcijām, ar ISNUMBER un SEARCH atbalstu. Parādītajā piemērā formula G5 ir:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
ar šādiem nosauktajiem diapazoniem: "meklēt" = D5, "ct" = D8, "dati" = B5: B55.
Piezīme: šī ir masīva formula, taču tai nav nepieciešama vadība + shift + enter, jo AGGREGATE var apstrādāt masīvus dabiski.
Paskaidrojums
Šīs formulas kodols ir INDEX funkcija, un AGGREGATE izmanto, lai noskaidrotu "n-to atbilstību" katrai rindai ekstrakta apgabalā:
INDEX(data,nth_match_formula)
Gandrīz viss darbs ir, lai noskaidrotu un ziņotu, kuras rindas "datos" atbilst meklēšanas virknei, un ziņo katras atbilstošās vērtības pozīciju INDEX. Tas tiek darīts ar funkciju AGGREGATE, kas konfigurēta šādi:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Pirmais arguments - 15 - liek AGGREGATE uzvesties kā MAZI un atgriež n-tās mazākās vērtības. Otrais arguments 6 ir iespēja ignorēt kļūdas. Trešais arguments ir izteiksme, kas ģenerē atbilstošu rezultātu masīvu (aprakstīts tālāk). Ceturtais arguments F5 SMALL darbojas kā "k", lai norādītu "n" vērtību.
AGGREGATE darbojas masīvos, un zemāk esošā izteiksme veido masīvu trešajam argumentam AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Šeit funkcija ROW tiek izmantota, lai ģenerētu relatīvo rindu numuru masīvu, un ISNUMBER un SEARCH tiek izmantoti kopā, lai meklēšanas virkni saskaņotu ar datu vērtībām, kas ģenerē TRUE un FALSE vērtību masīvu.
Gudrs bits ir sadalīt rindu numurus ar meklēšanas rezultātiem. Šādās matemātikas operācijās TRUE uzvedas kā 1, un FALSE - kā nulle. Rezultāts ir tāds, ka rindu numuri, kas saistīti ar pozitīvu atbilstību, tiek dalīti ar 1 un izdzīvo pēc operācijas, savukārt ar neatbilstošām vērtībām saistītie rindu numuri tiek iznīcināti un kļūst par # DIV / 0 kļūdām. Tā kā AGGREGATE ir iestatīts ignorēt kļūdas, tas ignorē kļūdas # DIV / 0 un atgriež "n" mazāko skaitli atlikušajās vērtībās, izmantojot skaitli F slejā kā "n".
Darbības pārvaldība
Tāpat kā visas masīvu formulas, arī šī formula ir "dārga" attiecībā uz resursiem ar lielu datu kopu. Lai samazinātu veiktspējas ietekmi, visa INDEX un MATCH formula tiek iesaiņota IF šādi:
=IF(F5>ct,"",formula)
kur nosauktais diapazons "ct" (D8) satur šo formulu:
=COUNTIF(data,"*"&search&"*")
Šī pārbaude pārtrauc formulas INDEX un AGGREGATE daļas darbību, tiklīdz ir iegūtas visas atbilstošās vērtības.
Masīva formula ar MAZU
Ja jūsu Excel versijā nav funkcijas AGGREGATE, varat izmantot alternatīvu formulu, kuras pamatā ir SMALL un IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + shift + enter.
