Meklējiet tekstā atslēgvārdus

Atslēgvārdu meklēšana avota tekstā ir viens no biežākajiem uzdevumiem, strādājot ar datiem. Apskatīsim tā risinājumu vairākos veidos, izmantojot šādu piemēru:

Pieņemsim, ka mums un jums ir atslēgvārdu saraksts – automašīnu marku nosaukumi – un liela visu veidu rezerves daļu tabula, kur aprakstos dažkārt var būt viena vai vairākas šādas markas uzreiz, ja rezerves daļa der vairākām auto marka. Mūsu uzdevums ir atrast un parādīt visus atklātos atslēgvārdus blakus esošajās šūnās, izmantojot doto atdalītāju (piemēram, komatu).

1. metode. Power Query

Protams, vispirms mēs pārvēršam savas tabulas par dinamiskām (“viedām”), izmantojot īsinājumtaustiņus Ctrl+T vai komandas Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu), piešķiriet tiem vārdus (piemēram Zīmogiи Rezerves daļas) un pa vienam ielādējiet Power Query redaktorā, atlasot cilnē Dati — no tabulas/diapazona (Dati — no tabulas/diapazona). Ja jums ir vecākas Excel 2010–2013 versijas, kurās Power Query ir instalēta kā atsevišķa pievienojumprogramma, cilnē būs vēlamā poga. Jaudas vaicājums. Ja jums ir pavisam jauna Excel 365 versija, tad poga No tabulas/diapazona tagad tur zvanīja Ar lapām (No lapas).

Pēc katras tabulas ielādes programmā Power Query mēs atgriežamies programmā Excel ar komandu Sākums — Aizvērt un ielādēt — Aizvērt un ielādēt... — Izveidot tikai savienojumu (Sākums — Aizvērt un ielādēt — Aizvērt un ielādēt... — tikai izveidot savienojumu).

Tagad izveidosim pieprasījuma dublikātu Rezerves daļasar peles labo pogu noklikšķinot uz tā un atlasot Dublēts pieprasījums (Vaicājuma dublikāts), pēc tam pārdēvējiet iegūto kopēšanas pieprasījumu uz Rezultāti un mēs turpināsim ar viņu strādāt.

Darbību loģika ir šāda:

1. Cilnē Papildu Kolonnas pievienošana izvēlēties komandu Pielāgota kolonna (Pievienot kolonnu — pielāgota kolonna) un ievadiet formulu = Zīmoli. Pēc noklikšķināšanas uz OK iegūsim jaunu kolonnu, kur katrā šūnā būs ligzdota tabula ar mūsu atslēgvārdu sarakstu – autoražotāju zīmoli:

   

2. Izmantojiet pogu ar dubultbultiņām pievienotās kolonnas galvenē, lai izvērstu visas ligzdotās tabulas. Tajā pašā laikā rindas ar rezerves daļu aprakstiem reizināsies ar zīmolu skaita reizinājumu, un mēs iegūsim visus iespējamos “rezerves daļu zīmola” pārus-kombinācijas:

   

3. Cilnē Papildu Kolonnas pievienošana izvēlēties komandu Nosacījuma kolonna (nosacījuma kolonna) un iestatiet nosacījumu atslēgvārda (zīmola) sastopamības pārbaudei avota tekstā (daļas aprakstā):

   

4. Lai meklēšanas reģistrs būtu nejutīgs, manuāli pievienojiet trešo argumentu formulas joslā Compare.OrdinalIgnoreCase uz notikumu pārbaudes funkciju Teksts.Satur (ja formulas josla nav redzama, to var iespējot cilnē Pārskats):

   

5. Mēs filtrējam iegūto tabulu, pēdējā kolonnā atstājot tikai vienus, ti, atbilstības, un noņemam nevajadzīgo kolonnu Notikumi.
6. Identisku aprakstu grupēšana ar komandu Grupēt pēc tab transformācija (Pārveidot — grupēt pēc). Kā apkopošanas darbību izvēlieties Visas līnijas (Visas rindas). Izvadā mēs iegūstam kolonnu ar tabulām, kurā ir visa informācija par katru rezerves daļu, ieskaitot mums nepieciešamo autoražotāju zīmolus:

   

7. Lai katrai daļai iegūtu atzīmes, cilnē pievienojiet vēl vienu aprēķināto kolonnu Kolonnas pievienošana — pielāgota kolonna (Pievienot kolonnu — pielāgota kolonna) un izmantojiet formulu, kas sastāv no tabulas (tās atrodas mūsu kolonnā Sīkāka informācija) un izvilktās kolonnas nosaukums:

   

8. Mēs noklikšķinām uz pogas ar dubultajām bultiņām iegūtās kolonnas galvenē un atlasām komandu Izvilkt vērtības (Izvilkt vērtības)lai izvadītu zīmogus ar jebkuru vēlamo norobežotāju:

   

9. Nevajadzīgas kolonnas noņemšana Sīkāka informācija.
10. Lai iegūtajai tabulai pievienotu no tās pazudušās daļas, kur aprakstos zīmoli netika atrasti, veicam vaicājuma apvienošanas procedūru Rezultāts ar sākotnējo pieprasījumu Rezerves daļas poga Apvienot tab Sākumlapa (Sākums — apvienot vaicājumus). Savienojuma veids - Ārējais savienojums pa labi (Labās puses ārējais savienojums):

    

11. Atliek tikai noņemt papildu kolonnas un pārdēvēt-pārvietot atlikušās - un mūsu uzdevums ir atrisināts:

    
    

2. metode. Formulas

Ja jums ir Excel 2016 vai jaunāka versija, mūsu problēmu var atrisināt ļoti kompaktā un elegantā veidā, izmantojot jauno funkciju COMBINE (TEXTJOIN):

Šīs formulas loģika ir vienkārša:

• funkcija MEKLĒT (Atrast) meklē katras markas rašanos pēc kārtas aktuālajā daļas aprakstā un atgriež vai nu simbola sērijas numuru, no kura zīmols tika atrasts, vai kļūdu #VĒRTĪBA! ja zīmols nav aprakstā.
• Pēc tam izmantojiet funkciju IF (JA) и EOSHIBKA (ISERROR) mēs aizstājam kļūdas ar tukšu teksta virkni “”, un rakstzīmju kārtas numurus ar pašiem zīmolu nosaukumiem.
• Iegūtais tukšo šūnu un atrasto zīmolu masīvs tiek apkopots vienā virknē, izmantojot doto atdalīšanas rakstzīmi, izmantojot funkciju COMBINE (TEXTJOIN).

Veiktspējas salīdzinājums un Power Query vaicājumu buferizācija paātrināšanai

Veiktspējas pārbaudei kā sākotnējos datus ņemsim tabulu ar 100 rezerves daļu aprakstiem. Uz tā mēs iegūstam šādus rezultātus:

• Pārrēķina laiks pēc formulām (2. metode) – 9 sek. kad pirmo reizi nokopējat formulu visā kolonnā un 2 sek. pie atkārtotas (iespējams, buferizācijas ietekme).
  
• Power Query vaicājuma (1. metode) atjaunināšanas laiks ir daudz sliktāks – 110 sekundes.
  

Protams, daudz kas ir atkarīgs no konkrētā datora aparatūras un instalētās Office versijas un atjauninājumiem, bet kopējā aina, manuprāt, ir skaidra.

Lai paātrinātu Power Query vaicājumu, buferizējiet uzmeklēšanas tabulu Zīmogi, jo tas nemainās vaicājuma izpildes procesā un nav nepieciešams to pastāvīgi pārrēķināt (kā to dara Power Query de facto). Šim nolūkam mēs izmantojam funkciju Tabula.Buferis no iebūvētās Power Query valodas M.

Lai to izdarītu, atveriet vaicājumu Rezultāti un cilnē Pārskats nospiediet pogu Uzlabots redaktors (Skatīt — uzlabotais redaktors). Atvērtajā logā pievienojiet rindu ar jaunu mainīgo Markijs 2, kas būs mūsu autoražotāju direktorija buferizētā versija, un izmantojiet šo jauno mainīgo vēlāk šajā vaicājuma komandā:

Pēc šādas pilnveidošanas mūsu pieprasījuma atjaunināšanas ātrums palielinās gandrīz 7 reizes – līdz 15 sekundēm. Pavisam cita lieta 🙂

• Izplūdusi teksta meklēšana programmā Power Query
• Lielapjoma teksta aizstāšana ar formulām
• Lielapjoma teksta aizstāšana programmā Power Query ar funkciju List.Accumulate