Căutați cuvinte cheie în text

Căutarea cuvintelor cheie în textul sursă este una dintre cele mai frecvente sarcini atunci când lucrați cu date. Să ne uităm la soluția sa în mai multe moduri folosind următorul exemplu:

Să presupunem că tu și cu mine avem o listă de cuvinte cheie – numele mărcilor de mașini – și un tabel mare cu tot felul de piese de schimb, unde descrierile pot conține uneori una sau mai multe astfel de mărci deodată, dacă piesa de schimb se potrivește mai mult de una. marca de mașină. Sarcina noastră este să găsim și să afișăm toate cuvintele cheie detectate în celulele învecinate printr-un anumit caracter separator (de exemplu, o virgulă).

Metoda 1. Power Query

Desigur, mai întâi ne transformăm tabelele în dinamice („inteligente”) folosind o comandă rapidă de la tastatură Ctrl+T sau comenzi Acasă – Formatați ca tabel (Acasă — Format ca tabel), dați-le nume (de exemplu Timbreи Piese de schimb) și încărcați unul câte unul în editorul Power Query selectând din filă Date – Din tabel/gamă (Date - Din tabel/gamă). Dacă aveți versiuni mai vechi de Excel 2010-2013, în care Power Query este instalat ca supliment separat, atunci butonul dorit va fi pe fila Interogare de alimentare. Dacă aveți o versiune nouă de Excel 365, atunci butonul Din masă/gamă chemat acolo acum Cu frunze (Din foaie).

După încărcarea fiecărui tabel în Power Query, revenim la Excel cu comanda Acasă — Închideți și încărcați — Închideți și încărcați în... — Numai pentru a crea conexiune (Acasă — Închideți și încărcați — Închideți și încărcați în... — Creați doar conexiune).

Acum să creăm o cerere duplicată Piese de schimbfăcând clic dreapta pe el și selectând Solicitare duplicat (interogare duplicat), apoi redenumiți cererea de copiere rezultată în Rezultatele și vom continua să lucrăm cu el.

Logica acțiunilor este următoarea:

1. În fila Avansat Adăugarea unei coloane alege o echipa Coloană personalizată (Adăugați coloană — coloană personalizată) și introduceți formula = Mărci. După ce faceți clic pe OK vom obține o nouă coloană, unde în fiecare celulă va exista un tabel imbricat cu o listă a cuvintelor noastre cheie – mărci de producători de automobile:

   

2. Utilizați butonul cu săgeți duble din antetul coloanei adăugate pentru a extinde toate tabelele imbricate. În același timp, liniile cu descrieri ale pieselor de schimb se vor înmulți cu un multiplu al numărului de mărci și vom obține toate perechile-combinații posibile de „piese de schimb-brand”:

   

3. În fila Avansat Adăugarea unei coloane alege o echipa Coloana condiționată (coloana condiționată) și setați o condiție pentru verificarea apariției unui cuvânt cheie (brand) în textul sursă (descrierea părții):

   

4. Pentru a face căutarea să nu țină seama de majuscule și minuscule, adăugați manual al treilea argument în bara de formule Compare.OrdinalIgnoreCase la funcția de verificare a apariției Text.Conține (dacă bara de formule nu este vizibilă, atunci poate fi activată în filă Recenzie):

   

5. Filtrăm tabelul rezultat, lăsând doar unul în ultima coloană, adică potriviri și eliminăm coloana care nu este necesară Apariții.
6. Gruparea descrierilor identice cu comanda A se grupa cu fila Transformare (Transformare — Grupați după). Ca operație de agregare, alegeți Toate liniile (Toate rândurile). La ieșire, obținem o coloană cu tabele, care conține toate detaliile pentru fiecare piesă de schimb, inclusiv mărcile producătorilor auto de care avem nevoie:

   

7. Pentru a extrage note pentru fiecare parte, adăugați o altă coloană calculată în filă Adăugarea unei coloane – coloană personalizată (Adăugați coloană — coloană personalizată) și folosiți o formulă constând dintr-un tabel (acestea sunt situate în coloana noastră Detalii) și numele coloanei extrase:

   

8. Facem clic pe butonul cu săgeți duble din antetul coloanei rezultate și selectăm comanda Extrageți valori (Extrage valori)pentru a scoate ștampile cu orice caracter delimitator doriți:

   

9. Eliminarea unei coloane inutile Detalii.
10. Pentru a adăuga la tabelul rezultat părțile care au dispărut din acesta, unde nu au fost găsite mărci în descrieri, efectuăm procedura de combinare a interogării Rezultat cu cererea originala Piese de schimb buton Combina fila Acasă (Acasă — Îmbinați interogări). Tipul conexiunii - Unire exterioară Dreapta (Uniunea exterioară dreaptă):

    

11. Tot ce rămâne este să eliminați coloanele suplimentare și să le redenumim-le mutați pe cele rămase - iar sarcina noastră este rezolvată:

    
    

Metoda 2. Formule

Dacă aveți o versiune de Excel 2016 sau o versiune ulterioară, atunci problema noastră poate fi rezolvată într-un mod foarte compact și elegant folosind noua funcție COMBINA (TEXTJOIN):

Logica din spatele acestei formule este simplă:

• Funcţie Căutare (GĂSI) caută pe rând apariția fiecărei mărci în descrierea curentă a piesei și returnează fie numărul de serie al simbolului, începând de la care a fost găsită marca, fie eroarea #VALOARE! daca marca nu este in descriere.
• Apoi, folosind funcția IF (DACĂ) и EOSHIBKA (ISEROARE) înlocuim erorile cu un șir de text gol „”, iar numerele ordinale ale caracterelor cu numele mărcilor în sine.
• Matricea rezultată de celule goale și mărci găsite este asamblată într-un singur șir printr-un anumit caracter separator folosind funcția COMBINA (TEXTJOIN).

Comparație de performanță și interogare de interogare Power Query pentru accelerare

Pentru testarea performanței, să luăm ca date inițiale un tabel cu 100 de descrieri de piese de schimb. Pe el obținem următoarele rezultate:

• Timp de recalculare prin formule (Metoda 2) – 9 sec. când copiați prima dată formula în întreaga coloană și 2 sec. la repetate (buffering afectează, probabil).
  
• Timpul de actualizare al interogării Power Query (Metoda 1) este mult mai rău – 110 secunde.
  

Desigur, multe depind de hardware-ul unui anumit PC și de versiunea instalată de Office și de actualizări, dar imaginea de ansamblu, cred, este clară.

Pentru a accelera o interogare Power Query, să tamponăm tabelul de căutare Timbre, deoarece nu se modifică în procesul de execuție a interogării și nu este necesar să o recalculăm în mod constant (cum o face de facto Power Query). Pentru aceasta folosim funcția Tabel.Tampon din limbajul Power Query încorporat M.

Pentru a face acest lucru, deschideți o interogare Rezultatele iar pe filă Recenzie apasa butonul Editor avansat (Vizualizare — Editor avansat). În fereastra care se deschide, adăugați o linie cu o nouă variabilă Marky 2, care va fi o versiune tampon a directorului nostru de producători de automobile și utilizați această nouă variabilă mai târziu în următoarea comandă de interogare:

După o astfel de rafinare, viteza de actualizare a solicitării noastre crește de aproape 7 ori – până la 15 secunde. Cu totul altceva 🙂

• Căutare de text neclar în Power Query
• Înlocuirea în bloc a textului cu formule
• Înlocuire în bloc de text în Power Query cu funcția List.Acumulate