Indhold
En af de mest sete videoer på min YouTube-kanal er en video om Flash Fill i Microsoft Excel. Essensen af dette værktøj er, at hvis du på en eller anden måde skal transformere dine kildedata, så skal du bare begynde at skrive det resultat, du vil have, i den tilstødende kolonne. Efter flere manuelt indtastede celler (normalt er 2-3 nok), vil Excel "forstå" logikken i de transformationer, du har brug for og automatisk fortsætte, hvad du har skrevet, og fuldføre alt det monotone arbejde for dig:
Kvintessensen af effektivitet. Den magiske "gør det rigtigt" knap, som vi alle elsker så højt, ikke?
Faktisk er der en analog til et sådant værktøj i Power Query - der hedder det Kolonne fra eksempler (Kolonne fra eksempler). Faktisk er dette en lille kunstig intelligens indbygget i Power Query, som hurtigt kan lære af dine data og derefter transformere dem. Lad os se nærmere på dets muligheder i flere praktiske scenarier for at forstå, hvor det kan være nyttigt for os i virkelige opgaver.
Eksempel 1. Limning/skæring af tekst
Lad os sige, at vi har sådan en "smart" tabel i Excel med data om medarbejdere:
Indlæs det i Power Query på standardmåden – med knappen Fra tabel/område fanen data (Data — fra tabel/område).
Antag, at vi skal tilføje en kolonne med efternavne og initialer for hver medarbejder (Ivanov SV for den første medarbejder osv.). For at løse dette problem kan du bruge en af to metoder:
- højreklik på kolonneoverskriften med kildedataene og vælg kommandoen Tilføj kolonne fra eksempler (Tilføj kolonne fra eksempler);
- vælg en eller flere kolonner med data og på fanen Tilføjelse af en kolonne vælge et hold Kolonne fra eksempler. Her kan du i rullelisten angive, om alle eller kun udvalgte kolonner skal analyseres.
Så er alt enkelt - i kolonnen, der vises til højre, begynder vi at indtaste eksempler på de ønskede resultater, og den kunstige intelligens, der er indbygget i Power Query, forsøger at forstå vores transformationslogik og fortsætte videre på egen hånd:
I øvrigt kan du indtaste de korrekte muligheder i alle celler i denne kolonne, dvs. ikke nødvendigvis top-down og i en række. Du kan også nemt tilføje eller fjerne kolonner fra analysen senere ved at bruge afkrydsningsfelterne i titellinjen.
Vær opmærksom på formlen øverst i vinduet – det er, hvad smart Power Query skaber for at få de resultater, vi har brug for. Dette er i øvrigt den grundlæggende forskel mellem dette værktøj og Øjeblikkelig påfyldning i Excel. Øjeblikkelig påfyldning fungerer som en "sort boks" – de viser os ikke logikken i transformationerne, men giver blot færdige resultater, og vi tager dem for givet. Her er alt gennemsigtigt, og du kan altid helt klart forstå, hvad der præcist sker med dataene.
Hvis du ser, at Power Query "fangede ideen", så kan du roligt trykke på knappen OK eller tastaturgenvej Ctrl+Indtast – en brugerdefineret kolonne med en formel opfundet af Power Query vil blive oprettet. Forresten kan det senere nemt redigeres som en almindelig manuelt oprettet kolonne (med kommandoen Tilføjelse af en kolonne – Brugerdefineret kolonne) ved at klikke på tandhjulsikonet til højre for trinnavnet:
Eksempel 2: Case som i sætninger
Hvis du højreklikker på kolonneoverskriften med tekst og vælger kommandoen Transformation (Transformer), så kan du se tre kommandoer, der er ansvarlige for at ændre registeret:
Praktisk og sejt, men på denne liste, for eksempel, har jeg personligt altid manglet en mulighed mere – store og små bogstaver som i sætninger, når stort (store bogstaver) ikke bliver det første bogstav i hvert ord, men kun det første bogstav i cellen, og resten af teksten, når Dette vises med små (små) bogstaver.
Denne manglende funktion er nem at implementere med kunstig intelligens Spalter fra eksempler – indtast blot et par muligheder for at Power Query kan fortsætte i samme ånd:
Som en formel her bruger Power Query en masse funktioner Tekst.Øvre и Tekst. Lavere, konvertering af tekst til henholdsvis store og små bogstaver og funktioner Tekst.Start и Tekst.Mid – analoger til Excel-funktionerne VENSTRE og PSTR, i stand til at udtrække en understreng fra teksten fra venstre og fra midten.
Eksempel 3. Permutation af ord
Nogle gange, når de modtagne data behandles, bliver det nødvendigt at omarrangere ordene i cellerne i en given rækkefølge. Selvfølgelig kan du opdele kolonnen i separate ordkolonner ved hjælp af separatoren og derefter lime den tilbage i den angivne rækkefølge (glem ikke at tilføje mellemrum), men ved hjælp af værktøjet Kolonne fra eksempler alt bliver meget nemmere:
Eksempel 4: Kun tal
En anden meget vigtig opgave er kun at trække tal (tal) ud af cellens indhold. Som før, efter indlæsning af data i Power Query, skal du gå til fanen Tilføjelse af en kolonne – Kolonne fra eksempler og udfyld et par celler manuelt, så programmet forstår, hvad vi præcist vil have:
Bingo!
Igen, det er værd at se øverst i vinduet for at sikre, at Query genererede formlen korrekt – i dette tilfælde indeholder den en funktion Tekst. Vælg, som, som du måske kan gætte, udtrækker de givne tegn fra kildeteksten i henhold til listen. Efterfølgende kan denne liste selvfølgelig nemt redigeres i formellinjen, hvis det er nødvendigt.
Eksempel 5: Kun tekst
På samme måde som i det foregående eksempel kan du trække ud og omvendt - kun teksten, slette alle tal, tegnsætningstegn osv.
I dette tilfælde bruges en funktion, der allerede er modsat i betydningen – Text.Remove, som fjerner tegn fra den originale streng i henhold til en given liste.
Eksempel 6: Udtræk af data fra en alfanumerisk grød
Power Query kan også hjælpe i vanskeligere tilfælde, når du skal udtrække nyttige oplysninger fra den alfanumeriske grød i en celle, for eksempel få kontonummeret fra beskrivelsen af betalingsformålet på et kontoudtog:
Bemærk, at den Power Query-genererede konverteringsformel kan være ret kompleks:
For at lette læsning og forståelse kan den konverteres til en meget mere fornuftig form ved hjælp af en gratis onlinetjeneste. Power Query Formatter:
Meget praktisk ting - respekt for skaberne!
Eksempel 7: Konvertering af datoer
Værktøj Kolonne fra eksempler kan også anvendes på dato- eller dato-tidskolonner. Når du indtaster de første cifre i en dato, vil Power Query på en hjælpsom måde vise en liste over alle mulige konverteringsmuligheder:
Så du kan nemt konvertere den originale dato til ethvert eksotisk format, såsom "år-måned-dag":
Eksempel 8: Kategorisering
Hvis vi bruger værktøjet Kolonne fra eksempler til en kolonne med numeriske data, fungerer det anderledes. Antag, at vi har medarbejdertestresultater indlæst i Power Query (betingede score i området 0-100), og vi bruger følgende betingede graduering:
- Masters - dem, der scorede mere end 90
- Eksperter – scorede fra 70 til 90
- Brugere – fra 30 til 70
- Begyndere – dem, der scorede mindre end 30
Hvis vi tilføjer en kolonne fra eksemplerne til listen og begynder at arrangere disse gradueringer manuelt, så vil Power Query meget snart opfange vores idé og tilføje en kolonne med en formel, hvor operatorer indlejret i hinanden if logik vil blive implementeret, meget lig det vi har brug for:
Igen kan du ikke presse situationen til ende, men klikke videre OK og ret så tærskelværdierne allerede i formlen - det er hurtigere på denne måde:
konklusioner
Helt sikkert et værktøj Kolonne fra eksempler er ikke en "magisk pille", og før eller siden vil der være ikke-standardiserede situationer eller særligt forsømte tilfælde af en "kollektiv farm" i dataene, når Power Query vil fejle og ikke vil være i stand til at finde ud af, hvad vi ønsker rigtigt for os. Men som et hjælpeværktøj er det meget godt. Plus, ved at studere de formler, han genererede, kan du udvide din viden om funktionerne i M-sproget, hvilket altid vil være nyttigt i fremtiden.
- Parsing af tekst med regulære udtryk (RegExp) i Power Query
- Fuzzy tekstsøgning i Power Query
- Flash Udfyld Microsoft Excel