Maghanap ng mga keyword sa teksto

Ang paghahanap ng mga keyword sa pinagmulang teksto ay isa sa mga pinakakaraniwang gawain kapag nagtatrabaho sa data. Tingnan natin ang solusyon nito sa maraming paraan gamit ang sumusunod na halimbawa:

Ipagpalagay natin na ikaw at ako ay may listahan ng mga keyword – ang mga pangalan ng mga tatak ng kotse – at isang malaking talahanayan ng lahat ng uri ng mga ekstrang bahagi, kung saan ang mga paglalarawan ay maaaring maglaman ng isa o ilang mga naturang tatak nang sabay-sabay, kung ang ekstrang bahagi ay magkasya sa higit sa isa tatak ng sasakyan. Ang aming gawain ay hanapin at ipakita ang lahat ng nakitang keyword sa mga kalapit na cell sa pamamagitan ng ibinigay na karakter ng separator (halimbawa, isang kuwit).

Paraan 1. Power Query

Siyempre, gagawin muna namin ang aming mga talahanayan sa dynamic ("smart") gamit ang isang keyboard shortcut Ctrl+T o mga utos Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan), bigyan sila ng mga pangalan (halimbawa Stampи Mga kasangkapang labi) at i-load nang isa-isa sa editor ng Power Query sa pamamagitan ng pagpili sa tab Data – Mula sa Talahanayan/Saklaw (Data — Mula sa Talahanayan/Saklaw). Kung mayroon kang mas lumang mga bersyon ng Excel 2010-2013, kung saan naka-install ang Power Query bilang isang hiwalay na add-in, ang gustong button ay nasa tab. kapangyarihan query. Kung mayroon kang bagong bersyon ng Excel 365, pagkatapos ay ang pindutan Mula sa Table/Range tinatawag na doon ngayon May mga dahon (Mula sa Sheet).

Pagkatapos i-load ang bawat talahanayan sa Power Query, bumalik kami sa Excel kasama ang command Home — Isara at i-load — Isara at i-load sa… — Lumikha ng koneksyon lamang (Home — Isara at I-load — Isara at I-load sa… — Lumikha lamang ng koneksyon).

Ngayon, gumawa tayo ng duplicate na kahilingan Mga kasangkapang labisa pamamagitan ng pag-right-click dito at pagpili Duplicate na kahilingan (Dobleng query), pagkatapos ay palitan ang pangalan ng nagresultang kahilingan sa pagkopya sa Ang mga resulta at patuloy kaming makikipagtulungan sa kanya.

Ang lohika ng mga aksyon ay ang mga sumusunod:

1. Sa Advanced tab Pagdaragdag ng column pumili ng isang pangkat Custom na column (Magdagdag ng column — Custom na column) at ipasok ang formula = Mga tatak. Pagkatapos mag-click sa OK makakakuha tayo ng bagong column, kung saan sa bawat cell ay magkakaroon ng nested table na may listahan ng aming mga keyword – mga tatak ng automaker:

   

2. Gamitin ang button na may double arrow sa header ng idinagdag na column para palawakin ang lahat ng nested table. Kasabay nito, ang mga linya na may mga paglalarawan ng mga ekstrang bahagi ay dadami sa maramihang bilang ng mga tatak, at makukuha namin ang lahat ng posibleng pares-kumbinasyon ng "mga ekstrang bahagi-tatak":

   

3. Sa Advanced tab Pagdaragdag ng column pumili ng isang pangkat Kolumn na may kondisyon (Conditional column) at magtakda ng kundisyon para sa pagsuri sa paglitaw ng isang keyword (tatak) sa pinagmulang teksto (paglalarawan ng bahagi):

   

4. Upang gawing insensitive ang case ng paghahanap, manu-manong idagdag ang ikatlong argumento sa formula bar Ikumpara.OrdinalIgnoreCase sa function ng pagsuri ng pangyayari Teksto.Naglalaman (kung hindi nakikita ang formula bar, maaari itong paganahin sa tab Pagsusuri):

   

5. Sinasala namin ang resultang talahanayan, nag-iiwan lamang ng mga isa sa huling column, ibig sabihin, mga tugma at alisin ang hindi kinakailangang column pangyayari.
6. Pagpapangkat ng magkaparehong paglalarawan sa utos Grupo ng tab Pagbabagong-anyo (Transform — Igrupo ayon sa). Bilang pagpapatakbo ng pagsasama-sama, piliin Lahat ng linya (Lahat ng row). Sa output, nakakakuha kami ng column na may mga talahanayan, na naglalaman ng lahat ng detalye para sa bawat ekstrang bahagi, kabilang ang mga tatak ng mga automaker na kailangan namin:

   

7. Upang kunin ang mga marka para sa bawat bahagi, magdagdag ng isa pang nakalkulang column sa tab Pagdaragdag ng Column – Custom Column (Magdagdag ng column — Custom na column) at gumamit ng formula na binubuo ng isang talahanayan (matatagpuan ang mga ito sa aming column Detalye) at ang pangalan ng na-extract na column:

   

8. Nag-click kami sa pindutan na may dobleng mga arrow sa header ng resultang column at piliin ang command I-extract ang mga halaga (Mga halaga ng extract)upang mag-output ng mga selyo na may anumang delimiter na character na gusto mo:

   

9. Pag-alis ng hindi kinakailangang column Detalye.
10. Upang idagdag sa resultang talahanayan ang mga bahaging nawala mula rito, kung saan walang nakitang mga tatak sa mga paglalarawan, ginagawa namin ang pamamaraan para sa pagsasama-sama ng query Resulta na may orihinal na kahilingan Mga kasangkapang labi butones Pagsamahin tab Tahanan (Home — Pagsamahin ang mga query). Uri ng koneksyon - Outer Join Right (Kanang panlabas na pagsali):

    

11. Ang natitira na lang ay alisin ang mga karagdagang column at palitan ang pangalan-ilipat ang mga natitira – at ang aming gawain ay nalutas:

    
    

Paraan 2. Mga Formula

Kung mayroon kang bersyon ng Excel 2016 o mas bago, maaaring malutas ang aming problema sa isang napaka-compact at eleganteng paraan gamit ang bagong function. Pagsamahin (TEXTJOIN):

Ang lohika sa likod ng formula na ito ay simple:

• tungkulin Paghahanap (HANAPIN) hinahanap ang paglitaw ng bawat brand sa kasalukuyang paglalarawan ng bahagi at ibinabalik ang alinman sa serial number ng simbolo, simula kung saan natagpuan ang brand, o ang error na #VALUE! kung ang tatak ay wala sa paglalarawan.
• Pagkatapos ay ginagamit ang function IF (KUNG) и EOSHIBKA (ISERROR) pinapalitan namin ang mga error ng walang laman na text string "", at ang mga ordinal na numero ng mga character na may mga pangalan ng tatak mismo.
• Ang nagreresultang hanay ng mga walang laman na cell at nahanap na mga tatak ay pinagsama-sama sa isang string sa pamamagitan ng ibinigay na karakter ng separator gamit ang function. Pagsamahin (TEXTJOIN).

Paghahambing ng Pagganap at Pag-buffer ng Power Query Query para sa Speedup

Para sa pagsubok sa pagganap, kunin natin ang isang talahanayan ng 100 mga paglalarawan ng ekstrang bahagi bilang paunang data. Dito nakukuha namin ang mga sumusunod na resulta:

• Oras ng muling pagkalkula sa pamamagitan ng mga formula (Paraan 2) – 9 seg. kapag una mong kinopya ang formula sa buong column at 2 sec. sa paulit-ulit (buffering nakakaapekto, marahil).
  
• Ang oras ng pag-update ng query ng Power Query (Paraan 1) ay mas malala – 110 segundo.
  

Siyempre, marami ang nakasalalay sa hardware ng isang partikular na PC at ang naka-install na bersyon ng Office at mga update, ngunit ang pangkalahatang larawan, sa palagay ko, ay malinaw.

Para mapabilis ang isang query sa Power Query, i-buffer natin ang lookup table Stamp, dahil hindi ito nagbabago sa proseso ng pagpapatupad ng query at hindi kinakailangan na patuloy na kalkulahin ito (tulad ng ginagawa ng Power Query de facto). Para dito ginagamit namin ang function Talahanayan.Buffer mula sa built-in na Power Query na wika na M.

Upang gawin ito, magbukas ng query Ang mga resulta at sa tab Pagsusuri pindutin ang pindutan Advanced na Editor (Tingnan — Advanced na Editor). Sa window na bubukas, magdagdag ng isang linya na may bagong variable Marky 2, na magiging isang buffered na bersyon ng aming direktoryo ng automaker, at gamitin ang bagong variable na ito mamaya sa sumusunod na query command:

Pagkatapos ng naturang pagpipino, ang bilis ng pag-update ng aming kahilingan ay tataas ng halos 7 beses - hanggang 15 segundo. Medyo ibang bagay 🙂

• Malabo na paghahanap ng teksto sa Power Query
• Maramihang pagpapalit ng text ng mga formula
• Maramihang pagpapalit ng text sa Power Query na may function na List.Accumulate