Indhold
At søge efter nøgleord i kildeteksten er en af de mest almindelige opgaver, når man arbejder med data. Lad os se på dens løsning på flere måder ved hjælp af følgende eksempel:
Lad os antage, at du og jeg har en liste med søgeord – navnene på bilmærker – og en stor tabel med alle slags reservedele, hvor beskrivelser nogle gange kan indeholde et eller flere sådanne mærker på én gang, hvis reservedelen passer til mere end én. bilmærke. Vores opgave er at finde og vise alle detekterede søgeord i tilstødende celler gennem et givet separatortegn (for eksempel et komma).
Metode 1. Power Query
Selvfølgelig gør vi først vores tabeller til dynamiske ("smarte") ved hjælp af en tastaturgenvej Ctrl+T eller kommandoer Hjem – Formater som en tabel (Hjem — Formater som tabel), giv dem navne (f.eks Frimærkerи Reservedele) og indlæs én efter én i Power Query-editoren ved at vælge på fanen Data – Fra tabel/område (Data — fra tabel/område). Hvis du har ældre versioner af Excel 2010-2013, hvor Power Query er installeret som et separat tilføjelsesprogram, så vil den ønskede knap være på fanen Strømforespørgsel. Hvis du har en helt ny version af Excel 365, så knappen Fra tabel/område ringede der nu Med blade (Fra ark).
Efter indlæsning af hver tabel i Power Query vender vi tilbage til Excel med kommandoen Hjem — Luk og indlæs — Luk og indlæs til... — Opret kun forbindelse (Hjem — Luk og indlæs — Luk og indlæs til... — Opret kun forbindelse).
Lad os nu oprette en dubletanmodning Reservedeleved at højreklikke på den og vælge Dublet anmodning (Dublet forespørgsel), og omdøb derefter den resulterende kopianmodning til Resultaterne og vi vil fortsætte med at arbejde med ham.
Handlingslogikken er følgende:
- På fanen Avanceret Tilføjelse af en kolonne vælge et hold Brugerdefineret kolonne (Tilføj kolonne – tilpasset kolonne) og indtast formlen = Mærker. Efter at have klikket på OK vi får en ny kolonne, hvor der i hver celle vil være en indlejret tabel med en liste over vores søgeord – bilfabrikanter:
- Brug knappen med dobbeltpile i overskriften på den tilføjede kolonne for at udvide alle indlejrede tabeller. Samtidig vil linjerne med beskrivelser af reservedele ganges med et multiplum af antallet af mærker, og vi får alle mulige par-kombinationer af "reservedelsmærke":
- På fanen Avanceret Tilføjelse af en kolonne vælge et hold Betinget kolonne (Betinget kolonne) og sæt en betingelse for at kontrollere forekomsten af et søgeord (brand) i kildeteksten (delbeskrivelse):
- For at gøre søgningens store og små bogstaver ufølsomme, skal du manuelt tilføje det tredje argument i formellinjen Compare.OrdinalIgnoreCase til hændelseskontrolfunktionen Tekst.Indeholder (hvis formellinjen ikke er synlig, kan den aktiveres på fanen anmeldelse):
- Vi filtrerer den resulterende tabel og efterlader kun en i den sidste kolonne, dvs. matcher og fjerner den unødvendige kolonne Forekomster.
- Gruppering af identiske beskrivelser med kommandoen Gruppe af fanen Transformation (Transformer - Gruppér efter). Vælg som en aggregeringsoperation Alle linjer (Alle rækker). Ved udgangen får vi en kolonne med tabeller, som indeholder alle detaljer for hver reservedel, inklusive de bilfabrikanter, vi har brug for:
- For at udtrække karakterer for hver del skal du tilføje en anden beregnet kolonne på fanen Tilføjelse af en kolonne – Brugerdefineret kolonne (Tilføj kolonne – tilpasset kolonne) og brug en formel bestående af en tabel (de er placeret i vores kolonne Detaljer) og navnet på den udtrukne kolonne:
- Vi klikker på knappen med dobbeltpile i overskriften på den resulterende kolonne og vælger kommandoen Udtræk værdier (Udtræk værdier)for at udskrive stempler med ethvert afgrænsningstegn, du ønsker:
- Fjernelse af en unødvendig kolonne Detaljer.
- For at tilføje til den resulterende tabel de dele, der forsvandt fra den, hvor ingen mærker blev fundet i beskrivelserne, udfører vi proceduren for at kombinere forespørgslen Resultat med original anmodning Reservedele . Kombiner fanen Home (Hjem – Flet forespørgsler). Tilslutningstype – Ydre Sammenføjning Højre (højre yderforbindelse):
- Tilbage er blot at fjerne de ekstra kolonner og omdøbe-flytte de resterende – og vores opgave er løst:
Metode 2. Formler
Hvis du har en version af Excel 2016 eller nyere, så kan vores problem løses på en meget kompakt og elegant måde ved hjælp af den nye funktion FORENE (TEXTJOIN):
Logikken bag denne formel er enkel:
- Funktion SØG (FINDE) søger efter forekomsten af hvert mærke efter tur i den aktuelle beskrivelse af delen og returnerer enten serienummeret på symbolet, startende fra hvilket mærket blev fundet, eller fejlen #VALUE! hvis mærket ikke er i beskrivelsen.
- Brug derefter funktionen IF (HVIS) и EOSHIBKA (FEJL) vi erstatter fejlene med en tom tekststreng "", og ordensnumrene på tegnene med selve mærkenavnene.
- Det resulterende array af tomme celler og fundne mærker samles til en enkelt streng gennem et givet separatortegn ved hjælp af funktionen FORENE (TEXTJOIN).
Præstationssammenligning og Power Query Query Buffering til Speedup
For ydeevnetestning, lad os tage en tabel med 100 reservedelsbeskrivelser som indledende data. På den får vi følgende resultater:
- Genberegningstid ved formler (metode 2) – 9 sek. når du først kopierer formlen til hele kolonnen og 2 sek. ved gentagne gange (puffer påvirker sandsynligvis).
- Opdateringstiden for Power Query-forespørgslen (metode 1) er meget værre – 110 sekunder.
Meget afhænger selvfølgelig af hardwaren på en bestemt pc og den installerede version af Office og opdateringer, men det overordnede billede, synes jeg, er klart.
For at fremskynde en Power Query-forespørgsel, lad os buffere opslagstabellen Frimærker, fordi det ikke ændrer sig i processen med udførelse af forespørgsler, og det er ikke nødvendigt konstant at genberegne det (som Power Query de facto gør). Til dette bruger vi funktionen Table.Buffer fra det indbyggede Power Query-sprog M.
For at gøre dette skal du åbne en forespørgsel Resultaterne og på fanen anmeldelse tryk på knappen Avanceret editor (Vis — Avanceret editor). Tilføj en linje med en ny variabel i det vindue, der åbnes Marky 2, som vil være en bufferversion af vores automaker-mappe, og brug denne nye variabel senere i følgende forespørgselskommando:
Efter en sådan forfining øges opdateringshastigheden af vores anmodning med næsten 7 gange - op til 15 sekunder. En helt anden ting 🙂
- Fuzzy tekstsøgning i Power Query
- Masseteksterstatning med formler
- Masseteksterstatning i Power Query med List.Accumulate-funktion