Pangitaa ang mga keyword sa teksto

Ang pagpangita alang sa mga keyword sa gigikanan nga teksto usa sa labing kasagaran nga mga buluhaton kung nagtrabaho uban ang datos. Atong tan-awon ang solusyon niini sa daghang mga paagi gamit ang mosunod nga pananglitan:

Ibutang ta nga ikaw ug ako adunay usa ka lista sa mga keyword - ang mga ngalan sa mga tatak sa awto - ug usa ka dako nga lamesa sa tanan nga mga matang sa mga spare parts, diin ang mga paghulagway usahay adunay usa o daghang ingon nga mga tatak sa usa ka higayon, kung ang ekstra nga bahin mohaum sa labaw sa usa. brand sa sakyanan. Ang among tahas mao ang pagpangita ug pagpakita sa tanan nga nakit-an nga mga keyword sa kasikbit nga mga selyula pinaagi sa gihatag nga karakter sa separator (pananglitan, usa ka comma).

Pamaagi 1. Power Query

Siyempre, una natong himoon ang atong mga lamesa ngadto sa dinamikong (“smart”) gamit ang keyboard shortcut Ctrl+T o mga sugo Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan), hatagi sila og mga ngalan (pananglitan Mga selyoи Mga piyesa) ug i-load ang tagsa-tagsa ngadto sa editor sa Power Query pinaagi sa pagpili sa tab Data – Gikan sa Talaan/Sakop (Data — Gikan sa Table/Range). Kung ikaw adunay mga daan nga bersyon sa Excel 2010-2013, diin ang Power Query gi-install isip usa ka bulag nga add-in, nan ang gusto nga buton naa sa tab. Kusog nga Pagpangutana. Kung ikaw adunay bag-ong bersyon sa Excel 365, dayon ang buton Gikan sa Table/Range gitawag didto karon Uban sa mga dahon (Gikan sa Sheet).

Pagkahuman sa pagkarga sa matag lamesa sa Power Query, mobalik kami sa Excel uban ang mando Panimalay — Pagsira ug pagkarga — Pagsira ug pagkarga sa… — Paghimo koneksyon lamang (Home — Close & Load — Close & Load to… — Paghimo lang ug koneksyon).

Karon maghimo kita ug duplicate nga hangyo Mga piyesapinaagi sa pag-right-click niini ug pagpili Duplicate nga hangyo (Duplicate nga pangutana), unya ilisan ang ngalan sa resulta nga hangyo sa kopya sa ang mga resulta ug magpadayon kami sa pagtrabaho uban kaniya.

Ang lohika sa mga aksyon mao ang mosunod:

1. Sa Advanced tab Pagdugang ug kolum pagpili og team Pasadya nga kolum (Idugang ang kolum — Custom nga kolum) ug isulod ang pormula = Mga tatak. Human sa pag-klik sa OK makakuha kami usa ka bag-ong kolum, diin sa matag cell adunay usa ka nested nga lamesa nga adunay lista sa among mga keyword - mga tatak sa awto:

   

2. Gamita ang buton nga adunay doble nga mga arrow sa ulohan sa gidugang nga kolum aron mapalapad ang tanan nga mga salag nga mga lamesa. Sa parehas nga oras, ang mga linya nga adunay mga paghulagway sa mga spare parts modaghan sa daghang gidaghanon sa mga tatak, ug makuha namon ang tanan nga posible nga mga pares-kombinasyon sa "spare part-brand":

   

3. Sa Advanced tab Pagdugang ug kolum pagpili og team Kondisyonal nga kolum (Kondisyonal nga kolum) ug pagtakda og kondisyon alang sa pagsusi sa panghitabo sa usa ka keyword (brand) sa tinubdan nga teksto (bahin nga paghulagway):

   

4. Aron mahimong insensitive ang search case, idugang sa kamut ang ikatulo nga argumento sa formula bar Itandi.OrdinalIgnoreCase sa panghitabo check function Teksto.Naglangkob (kung ang formula bar dili makita, nan mahimo kini nga mahimo sa tab Review):

   

5. Among gisala ang resulta nga lamesa, nagbilin lang ug usa sa kataposang kolum, ie mga posporo ug tangtangon ang wala kinahanglana nga kolum. Nahitabo.
6. Pag-grupo sa parehas nga mga paghulagway sa mando Grupo sa tab kausaban (Pagbag-o - Grupo sa). Isip usa ka aggregation operation, pilia Tanang linya (Tanan nga laray). Sa output, nakakuha kami usa ka kolum nga adunay mga lamesa, nga naglangkob sa tanan nga mga detalye alang sa matag ekstra nga bahin, lakip ang mga tatak sa mga awto nga kinahanglan namon:

   

7. Aron makuha ang mga grado sa matag bahin, idugang ang laing kalkulado nga kolum sa tab Pagdugang ug Column – Custom Column (Idugang ang kolum — Custom nga kolum) ug gamita ang usa ka pormula nga gilangkuban sa usa ka lamesa (naa sila sa among kolum Details) ug ang ngalan sa gikuha nga kolum:

   

8. Gi-klik namon ang buton nga adunay doble nga mga pana sa ulohan sa sangputanan nga kolum ug pilia ang mando Mga bili sa pagkuha (Mga bili sa pagkuha)sa pag-output sa mga selyo nga adunay bisan unsang delimiter nga karakter nga gusto nimo:

   

9. Pagtangtang sa wala kinahanglana nga kolum Details.
10. Aron idugang sa resulta nga lamesa ang mga bahin nga nawala gikan niini, diin walay mga tatak nga nakit-an sa mga paghulagway, among gihimo ang pamaagi alang sa paghiusa sa pangutana resulta uban ang orihinal nga hangyo Mga piyesa button Pagsagol tab Home (Balay - Paghiusa sa mga pangutana). Uri sa koneksyon - Outer Join Right (Tuo nga gawas nga pag-apil):

    

11. Ang nahabilin mao ang pagtangtang sa dugang nga mga kolum ug pagbag-o sa ngalan-pagbalhin ang nahabilin - ug ang among buluhaton nasulbad:

    
    

Pamaagi 2. Mga pormula

Kung ikaw adunay usa ka bersyon sa Excel 2016 o sa ulahi, nan ang among problema mahimong masulbad sa usa ka compact ug elegante nga paagi gamit ang bag-ong function. KOMBINTO (TEXTJOIN):

Ang lohika sa likod niini nga pormula yano ra:

• function PAGPANGITA (PANGITA) nangita alang sa panghitabo sa matag brand sa baylo sa kasamtangan nga paghulagway sa bahin ug ibalik ang serial number sa simbolo, sugod diin ang brand nakit-an, o ang sayup nga #VALUE! kung wala sa description ang brand.
• Dayon gamita ang function IF (KUNG) и EOSHIBKA (ISERROR) gipulihan namo ang mga sayop sa usa ka walay sulod nga text string "", ug ang ordinal nga mga numero sa mga karakter nga adunay mga ngalan sa brand mismo.
• Ang resulta nga han-ay sa mga walay sulod nga mga selula ug nakit-an nga mga tatak gitigum sa usa ka hilo pinaagi sa usa ka gihatag nga karakter sa separator gamit ang function KOMBINTO (TEXTJOIN).

Pagtandi sa Performance ug Power Query Query Buffering para sa Speedup

Alang sa pagsulay sa pasundayag, magkuha kita usa ka lamesa sa 100 nga mga paghulagway sa mga spare parts isip pasiunang datos. Niini atong makuha ang mosunod nga mga resulta:

• Panahon sa pagkalkula pag-usab pinaagi sa mga pormula (Pamaagi 2) - 9 sec. kung una nimong kopyahon ang pormula sa tibuuk nga kolum ug 2 segundos. sa gibalikbalik (buffering makaapekto, tingali).
  
• Ang oras sa pag-update sa pangutana sa Power Query (Paagi 1) mas grabe - 110 segundos.
  

Siyempre, daghan ang nagdepende sa hardware sa usa ka partikular nga PC ug ang na-install nga bersyon sa Office ug mga update, apan ang kinatibuk-ang hulagway, sa akong hunahuna, klaro.

Aron mapadali ang pangutana sa Power Query, atong i-buffer ang lookup table Mga selyo, tungod kay dili kini mausab sa proseso sa pagpatuman sa pangutana ug dili kinahanglan nga kanunay nga kalkulahon kini (sama sa Power Query de facto). Alang niini gigamit namon ang function Lamesa.Buffer gikan sa built-in nga Power Query nga pinulongan nga M.

Aron mahimo kini, ablihi ang usa ka pangutana ang mga resulta ug sa tab Review ipadayon ang buton Abanteng Editor (Tan-awa — Advanced Editor). Sa bintana nga nagbukas, pagdugang usa ka linya nga adunay bag-ong variable Marky 2, nga usa ka buffered nga bersyon sa among automaker directory, ug gamita kining bag-ong variable sa ulahi sa mosunod nga query command:

Pagkahuman sa ingon nga pagpino, ang katulin sa pag-update sa among hangyo nagdugang hapit 7 ka beses - hangtod sa 15 segundos. Lahi ra kaayo 🙂

• Fuzzy nga pagpangita sa teksto sa Power Query
• Pagpuli sa daghang teksto nga adunay mga pormula
• Bulk nga pag-ilis sa teksto sa Power Query nga adunay List.Accumulate function