Søk etter nøkkelord i teksten

Å søke etter nøkkelord i kildetekst er en av de vanligste oppgavene når man jobber med data. La oss se på løsningen på flere måter ved å bruke følgende eksempel:

La oss anta at du og jeg har en liste med nøkkelord – navnene på bilmerker – og en stor tabell over alle slags reservedeler, der beskrivelser noen ganger kan inneholde ett eller flere slike merker samtidig, hvis reservedelen passer til mer enn én. bilmerke. Vår oppgave er å finne og vise alle oppdagede nøkkelord i naboceller gjennom et gitt skilletegn (for eksempel et komma).

Metode 1. Power Query

Selvfølgelig gjør vi først tabellene våre til dynamiske ("smarte") ved hjelp av en hurtigtast Ctrl+T eller kommandoer Hjem – Formater som en tabell (Hjem — Formater som tabell), gi dem navn (for eksempel Frimerkerи Reservedeler) og last en etter en inn i Power Query-redigeringsprogrammet ved å velge på fanen Data – Fra tabell/område (Data – fra tabell/område). Hvis du har eldre versjoner av Excel 2010-2013, hvor Power Query er installert som et eget tillegg, vil ønsket knapp være på fanen Strømforespørsel. Hvis du har en helt ny versjon av Excel 365, så knappen Fra Tabell/Rekkevidde ringte dit nå Med blader (Fra ark).

Etter å ha lastet inn hver tabell i Power Query, går vi tilbake til Excel med kommandoen Hjem — Lukk og last — Lukk og last til... — Opprett kun tilkobling (Hjem — Lukk og last — Lukk og last til... — Opprett kun tilkobling).

La oss nå lage en duplikatforespørsel Reservedelerved å høyreklikke på den og velge Duplikatforespørsel (Duplisert søk), og gi nytt navn til den resulterende kopiforespørselen til Resultatene og vi vil fortsette å jobbe med ham.

Handlingslogikken er følgende:

1. I kategorien Avansert Legger til en kolonne velge et lag Egendefinert kolonne (Legg til kolonne – egendefinert kolonne) og skriv inn formelen = Merker. Etter å ha klikket på OK vi får en ny kolonne, der i hver celle vil det være en nestet tabell med en liste over søkeordene våre – bilprodusenter:

   

2. Bruk knappen med doble piler i overskriften til kolonnen som er lagt til for å utvide alle nestede tabeller. Samtidig vil linjene med beskrivelser av reservedeler multipliseres med et multiplum av antall merker, og vi vil få alle mulige par-kombinasjoner av "reservedelsmerke":

   

3. I kategorien Avansert Legger til en kolonne velge et lag Betinget kolonne (Betinget kolonne) og angi en betingelse for å kontrollere forekomsten av et nøkkelord (merke) i kildeteksten (delbeskrivelse):

   

4. For å gjøre søket ufølsomt for store og små bokstaver, legg til det tredje argumentet manuelt i formellinjen Compare.OrdinalIgnoreCase til funksjonen for forekomstkontroll Tekst.Inneholder (hvis formellinjen ikke er synlig, kan den aktiveres på fanen Anmeldelse):

   

5. Vi filtrerer den resulterende tabellen, og lar bare dem stå i den siste kolonnen, dvs. samsvarer og fjerner den unødvendige kolonnen forekomster.
6. Gruppering av identiske beskrivelser med kommandoen Gruppe av tab Transformation (Transformer – grupper etter). Velg som en aggregeringsoperasjon Alle linjer (Alle rader). Ved utgangen får vi en kolonne med tabeller, som inneholder alle detaljene for hver reservedel, inkludert merkene til bilprodusentene vi trenger:

   

7. For å trekke ut karakterer for hver del, legg til en annen beregnet kolonne på fanen Legge til en kolonne – egendefinert kolonne (Legg til kolonne – egendefinert kolonne) og bruk en formel som består av en tabell (de er plassert i kolonnen vår Detaljer) og navnet på den utpakkede kolonnen:

   

8. Vi klikker på knappen med doble piler i overskriften til den resulterende kolonnen og velger kommandoen Trekk ut verdier (trekk ut verdier)for å skrive ut stempler med et hvilket som helst skilletegn du ønsker:

   

9. Fjerne en unødvendig kolonne Detaljer.
10. For å legge til den resulterende tabellen delene som forsvant fra den, der ingen merker ble funnet i beskrivelsene, utfører vi prosedyren for å kombinere spørringen Resultat med original forespørsel Reservedeler knapp Kombiner tab Hjemprodukt (Hjem – Slå sammen spørringer). Tilkoblingstype - Ytre Sammenføyning Høyre (Høyre ytre skjøt):

    

11. Alt som gjenstår er å fjerne de ekstra kolonnene og gi nytt navn til-flytte de resterende – og oppgaven vår er løst:

    
    

Metode 2. Formler

Hvis du har en versjon av Excel 2016 eller nyere, kan problemet vårt løses på en svært kompakt og elegant måte ved å bruke den nye funksjonen KOMBINERE (TEKST BLI MED):

Logikken bak denne formelen er enkel:

• Funksjon SØK (FINNE) søker etter forekomsten av hvert merke etter tur i den gjeldende beskrivelsen av delen og returnerer enten serienummeret til symbolet, fra hvilket merket ble funnet, eller feilen #VERDI! hvis merket ikke er i beskrivelsen.
• Bruk deretter funksjonen IF (HVIS) и EOSHIBKA (FEIL) vi erstatter feilene med en tom tekststreng "", og ordenstallene til tegnene med selve merkenavnene.
• Den resulterende matrisen av tomme celler og funnet merkevarer settes sammen til en enkelt streng gjennom et gitt skilletegn ved å bruke funksjonen KOMBINERE (TEKST BLI MED).

Ytelsessammenligning og Power Query Query Buffering for Speedup

For ytelsestesting, la oss ta en tabell med 100 reservedelsbeskrivelser som innledende data. På den får vi følgende resultater:

• Omberegningstid ved formler (metode 2) – 9 sek. når du først kopierer formelen til hele kolonnen og 2 sek. ved gjentatte (bufring påvirker, sannsynligvis).
  
• Oppdateringstiden for Power Query-spørringen (metode 1) er mye dårligere – 110 sekunder.
  

Mye avhenger selvfølgelig av maskinvaren til en bestemt PC og den installerte versjonen av Office og oppdateringer, men helhetsbildet synes jeg er klart.

For å øke hastigheten på en Power Query-spørring, la oss bufre oppslagstabellen Frimerker, fordi den ikke endres i prosessen med utførelse av spørringer, og det er ikke nødvendig å hele tiden beregne den på nytt (som Power Query de facto gjør). Til dette bruker vi funksjonen Tabell.Buffer fra det innebygde Power Query-språket M.

For å gjøre dette, åpne en spørring Resultatene og på fanen Anmeldelse trykk på knappen Avansert redaktør (Vis — Avansert redaktør). I vinduet som åpnes legger du til en linje med en ny variabel Marky 2, som vil være en bufret versjon av bilprodusentkatalogen vår, og bruk denne nye variabelen senere i følgende spørringskommando:

Etter en slik foredling øker oppdateringshastigheten på forespørselen vår med nesten 7 ganger – opptil 15 sekunder. En helt annen ting 🙂

• Uklar tekstsøk i Power Query
• Masseteksterstatning med formler
• Bulkteksterstatning i Power Query med List.Accumulate-funksjon