Innhold
En av de mest viste videoene på YouTube-kanalen min er en video om Flash Fill i Microsoft Excel. Essensen av dette verktøyet er at hvis du på en eller annen måte trenger å transformere kildedataene dine, trenger du bare å begynne å skrive inn resultatet du vil få i den tilstøtende kolonnen. Etter flere manuelt tastede celler (vanligvis 2-3 er nok), vil Excel "forstå" logikken til transformasjonene du trenger og automatisk fortsette det du har skrevet, og fullføre alt det monotone arbeidet for deg:
Kvintessensen av effektivitet. Den magiske "gjør det riktig"-knappen som vi alle elsker så mye, ikke sant?
Faktisk er det en analog av et slikt verktøy i Power Query - der heter det Kolonne fra eksempler (Kolonne fra eksempler). Faktisk er dette en liten kunstig intelligens innebygd i Power Query som raskt kan lære av dataene dine og deretter transformere dem. La oss se nærmere på dens evner i flere praktiske scenarier for å forstå hvor den kan være nyttig for oss i virkelige oppgaver.
Eksempel 1. Liming/kutting av tekst
La oss si at vi har en slik "smart" tabell i Excel med data om ansatte:
Last den inn i Power Query på standard måte – med knappen Fra Tabell/Rekkevidde tab Data (Data – fra tabell/område).
Anta at vi må legge til en kolonne med etternavn og initialer for hver ansatt (Ivanov SV for den første ansatte, etc.). For å løse dette problemet kan du bruke en av to metoder:
- høyreklikk på kolonneoverskriften med kildedataene og velg kommandoen Legg til kolonne fra eksempler (Legg til kolonne fra eksempler);
- velg én eller flere kolonner med data og på fanen Legger til en kolonne velge et lag Kolonne fra eksempler. Her kan du i nedtrekkslisten angi om alle eller kun utvalgte kolonner skal analyseres.
Da er alt enkelt – i kolonnen som vises til høyre begynner vi å legge inn eksempler på de ønskede resultatene, og den kunstige intelligensen innebygd i Power Query prøver å forstå transformasjonslogikken vår og fortsette videre på egen hånd:
Du kan forresten angi de riktige alternativene i alle cellene i denne kolonnen, altså ikke nødvendigvis ovenfra og ned og på rad. Du kan også enkelt legge til eller fjerne kolonner fra analysen senere ved å bruke avmerkingsboksene i tittellinjen.
Vær oppmerksom på formelen øverst i vinduet – dette er hva smart Power Query skaper for å få resultatene vi trenger. Dette er forresten den grunnleggende forskjellen mellom dette verktøyet og Umiddelbar fylling i Excel. Øyeblikkelig fylling fungerer som en "svart boks" - de viser oss ikke logikken i transformasjonene, men gir rett og slett ferdige resultater og vi tar dem for gitt. Her er alt gjennomsiktig og du kan alltid helt klart forstå hva som skjer med dataene.
Hvis du ser at Power Query "fanget ideen", kan du trygt trykke på knappen OK eller hurtigtast Ctrl+Enter – en egendefinert kolonne med en formel oppfunnet av Power Query vil bli opprettet. Forresten, den kan senere enkelt redigeres som en vanlig manuelt opprettet kolonne (med kommandoen Legge til en kolonne – egendefinert kolonne) ved å klikke på tannhjulikonet til høyre for trinnnavnet:
Eksempel 2: Kasus som i setninger
Hvis du høyreklikker på kolonneoverskriften med tekst og velger kommandoen Transformation (Forvandle), så kan du se tre kommandoer som er ansvarlige for å endre registeret:
Praktisk og kult, men i denne listen, for eksempel, har jeg personlig alltid manglet ett alternativ til – store og små bokstaver som i setninger, når store bokstaver (stor) ikke blir den første bokstaven i hvert ord, men bare den første bokstaven i cellen, og resten av teksten når Dette vises med små (små) bokstaver.
Denne manglende funksjonen er enkel å implementere med kunstig intelligens Kolonner fra eksempler – bare skriv inn et par alternativer for at Power Query skal fortsette i samme ånd:
Som en formel her bruker Power Query en haug med funksjoner Tekst.Øvre и Tekst. Nedre, konvertering av tekst til henholdsvis store og små bokstaver, og funksjoner Tekst.Start и Tekst.Mid – analoger av Excel-funksjonene VENSTRE og PSTR, i stand til å trekke ut en delstreng fra teksten fra venstre og fra midten.
Eksempel 3. Permutasjon av ord
Noen ganger, når du behandler de mottatte dataene, blir det nødvendig å omorganisere ordene i cellene i en gitt sekvens. Selvfølgelig kan du dele kolonnen i separate ordkolonner ved hjelp av skilletegnet og deretter lime den tilbake i den angitte rekkefølgen (ikke glem å legge til mellomrom), men ved hjelp av verktøyet Kolonne fra eksempler alt blir mye enklere:
Eksempel 4: Kun tall
En annen svært viktig oppgave er å trekke ut bare tall (tall) fra innholdet i cellen. Som før, etter å ha lastet data inn i Power Query, gå til fanen Legge til en kolonne – Kolonne fra eksempler og fyll ut et par celler manuelt slik at programmet forstår nøyaktig hva vi ønsker å få:
Bingo!
Igjen, det er verdt å se på toppen av vinduet for å sikre at Query genererte formelen riktig – i dette tilfellet inneholder den en funksjon Tekst. Å velge, som, som du kanskje gjetter, trekker ut de gitte tegnene fra kildeteksten i henhold til listen. Deretter kan denne listen selvfølgelig enkelt redigeres i formellinjen om nødvendig.
Eksempel 5: Kun tekst
I likhet med forrige eksempel kan du trekke ut og omvendt - bare teksten, slette alle tall, skilletegn, etc.
I dette tilfellet brukes en funksjon som allerede har motsatt betydning – Text.Remove, som fjerner tegn fra den opprinnelige strengen i henhold til en gitt liste.
Eksempel 6: Uttrekk av data fra en alfanumerisk grøt
Power Query kan også hjelpe i vanskeligere tilfeller, når du trenger å trekke ut nyttig informasjon fra den alfanumeriske grøten i en celle, for eksempel få kontonummeret fra beskrivelsen av betalingsformålet på en kontoutskrift:
Merk at den Power Query-genererte konverteringsformelen kan være ganske kompleks:
For enkel lesing og forståelse kan den konverteres til en mye mer fornuftig form ved å bruke en gratis nettjeneste. Power Query Formatter:
Veldig praktisk ting – respekt til skaperne!
Eksempel 7: Konvertering av datoer
Tool Kolonne fra eksempler kan også brukes på dato- eller datetime-kolonner. Når du skriver inn de første sifrene i en dato, vil Power Query nyttig vise en liste over alle mulige konverteringsalternativer:
Så du kan enkelt konvertere den opprinnelige datoen til et hvilket som helst eksotisk format, for eksempel "år-måned-dag":
Eksempel 8: Kategorisering
Hvis vi bruker verktøyet Kolonne fra eksempler til en kolonne med numeriske data, fungerer det annerledes. Anta at vi har ansatttestresultater lastet inn i Power Query (betinget poengsum i området 0-100), og vi bruker følgende betingede gradering:
- Masters - de som scoret mer enn 90
- Eksperter - scoret fra 70 til 90
- Brukere – fra 30 til 70
- Nybegynnere – de som scoret mindre enn 30
Hvis vi legger til en kolonne fra eksemplene til listen og begynner å ordne disse graderingene manuelt, vil Power Query snart plukke opp ideen vår og legge til en kolonne med en formel, der operatorer nestet inn i hverandre if logikk vil bli implementert, veldig lik det vi trenger:
Igjen, du kan ikke trykke situasjonen til slutten, men klikk videre OK og korriger deretter terskelverdiene allerede i formelen – det er raskere på denne måten:
Konklusjoner
Absolutt et verktøy Kolonne fra eksempler er ikke en "magisk pille", og før eller siden vil det være ikke-standardiserte situasjoner eller spesielt neglisjerte tilfeller av en "kollektiv gård" i dataene, når Power Query vil mislykkes og ikke vil være i stand til å finne ut hva vi vil riktig for oss. Men som et hjelpeverktøy er det veldig bra. I tillegg, ved å studere formlene han genererte, kan du utvide kunnskapen din om funksjonene til M-språket, som alltid vil komme godt med i fremtiden.
- Parsing tekst med regulære uttrykk (RegExp) i Power Query
- Uklar tekstsøk i Power Query
- Flash Fyll i Microsoft Excel