Inhoud
Een van de meest bekeken video's op mijn YouTube-kanaal is een video over Flash Fill in Microsoft Excel. De essentie van deze tool is dat als u uw brongegevens op de een of andere manier moet transformeren, u gewoon het gewenste resultaat in de aangrenzende kolom hoeft te typen. Na verschillende handmatig getypte cellen (meestal zijn 2-3 voldoende), "begrijpt" Excel de logica van de transformaties die u nodig hebt en gaat automatisch verder met wat u hebt getypt, waarbij al het eentonige werk voor u wordt voltooid:
De kwintessens van efficiëntie. De magische "doe het goed"-knop waar we allemaal zo dol op zijn, toch?
In feite is er een analoog van zo'n tool in Power Query - daar wordt het genoemd Kolom uit voorbeelden (Kolom uit voorbeelden). In feite is dit een kleine kunstmatige intelligentie ingebouwd in Power Query die snel van uw gegevens kan leren en deze vervolgens kan transformeren. Laten we de mogelijkheden ervan in verschillende praktische scenario's nader bekijken om te begrijpen waar het voor ons nuttig kan zijn in echte taken.
Voorbeeld 1. Tekst plakken/knippen
Laten we zeggen dat we zo'n "slimme" tabel in Excel hebben met gegevens over werknemers:
Laad het op de standaard manier in Power Query - met de knop Van tafel/bereik tab Data (Gegevens — Uit tabel/bereik).
Stel dat we voor elke werknemer een kolom met achternaam en initialen moeten toevoegen (Ivanov SV voor de eerste werknemer, enz.). Om dit probleem op te lossen, kunt u een van de volgende twee methoden gebruiken:
- klik met de rechtermuisknop op de kolomkop met de brongegevens en selecteer de opdracht Kolom uit voorbeelden toevoegen (Voeg kolom uit voorbeelden toe);
- selecteer een of meer kolommen met gegevens en op het tabblad Een kolom toevoegen kies een team Kolom uit voorbeelden. Hier, in de vervolgkeuzelijst, kunt u aangeven of alle of alleen geselecteerde kolommen moeten worden geanalyseerd.
Dan is alles eenvoudig: in de kolom die aan de rechterkant verschijnt, beginnen we voorbeelden van de gewenste resultaten in te voeren, en de kunstmatige intelligentie die in Power Query is ingebouwd, probeert onze transformatielogica te begrijpen en zelfstandig verder te gaan:
Overigens kunt u in alle cellen van deze kolom de juiste opties invoeren, dus niet per se top-down en in een rij. U kunt later ook eenvoudig kolommen toevoegen aan of verwijderen uit de analyse met behulp van de selectievakjes in de titelbalk.
Besteed aandacht aan de formule bovenaan het venster - dit is wat slimme Power Query maakt om de resultaten te krijgen die we nodig hebben. Dit is trouwens het fundamentele verschil tussen deze tool en Direct vullen in Excel. Onmiddellijk vullen werkt als een “zwarte doos” – ze laten ons niet de logica van de transformaties zien, maar geven gewoon kant-en-klare resultaten en we nemen ze als vanzelfsprekend aan. Hier is alles transparant en kunt u altijd heel duidelijk begrijpen wat er precies met de gegevens gebeurt.
Als je ziet dat Power Query "het idee heeft gevangen", kun je veilig op de knop drukken OK of sneltoets Ctrl+Enter - er wordt een aangepaste kolom gemaakt met een formule die is uitgevonden door Power Query. Trouwens, het kan later gemakkelijk worden bewerkt als een gewone handmatig gemaakte kolom (met het commando Een kolom toevoegen – Aangepaste kolom) door op het tandwielpictogram rechts van de stapnaam te klikken:
Voorbeeld 2: Naamval zoals in zinnen
Als u met de rechtermuisknop op de kolomkop met tekst klikt en de opdracht selecteert Transformatie (Transformeren), dan ziet u drie opdrachten die verantwoordelijk zijn voor het wijzigen van het register:
Handig en cool, maar in deze lijst heb ik bijvoorbeeld persoonlijk altijd nog een optie gemist - hoofdletters zoals in zinnen, wanneer hoofdletters (hoofdletters) niet de eerste letter in elk woord worden, maar alleen de eerste letter in de cel, en de rest van de tekst wanneer Dit wordt weergegeven in kleine (kleine) letters.
Deze ontbrekende functie is eenvoudig te implementeren met kunstmatige intelligentie Kolommen uit voorbeelden - voer gewoon een paar opties in voor Power Query om in dezelfde geest door te gaan:
Als formule hier gebruikt Power Query een heleboel functies Tekst.Upper и Tekst. Lager, tekst converteren naar respectievelijk hoofdletters en kleine letters, en functies Tekst.Start и Tekst.Mid – analogen van de Excel-functies LEFT en PSTR, in staat om een substring uit de tekst van links en van het midden te extraheren.
Voorbeeld 3. Permutatie van woorden
Soms wordt het bij het verwerken van de ontvangen gegevens nodig om de woorden in de cellen in een bepaalde volgorde te herschikken. Natuurlijk kunt u de kolom door het scheidingsteken in afzonderlijke woordkolommen verdelen en deze vervolgens in de opgegeven volgorde teruglijmen (vergeet niet om spaties toe te voegen), maar met behulp van de tool Kolom uit voorbeelden alles zal veel gemakkelijker zijn:
Voorbeeld 4: Alleen cijfers
Een andere zeer belangrijke taak is om alleen getallen (nummers) uit de inhoud van de cel te halen. Ga zoals eerder, na het laden van gegevens in Power Query, naar het tabblad Een kolom toevoegen – Kolom uit voorbeelden en vul een paar cellen handmatig in zodat het programma begrijpt wat we precies willen krijgen:
Bingo!
Nogmaals, het is de moeite waard om naar de bovenkant van het venster te kijken om er zeker van te zijn dat Query de formule correct heeft gegenereerd - in dit geval bevat het een functie Tekst. Selecteer, die, zoals je zou kunnen raden, de gegeven tekens uit de brontekst haalt volgens de lijst. Vervolgens kan deze lijst, indien nodig, natuurlijk eenvoudig worden bewerkt in de formulebalk.
Voorbeeld 5: Alleen tekst
Net als in het vorige voorbeeld, kunt u uittrekken en vice versa - alleen de tekst, alle cijfers, leestekens, enz. verwijderen.
In dit geval wordt een functie gebruikt die al een tegengestelde betekenis heeft – Text.Remove, die tekens uit de originele string verwijdert volgens een bepaalde lijst.
Voorbeeld 6: Gegevens uit een alfanumerieke pap halen
Power Query kan ook helpen in moeilijkere gevallen, wanneer u nuttige informatie uit de alfanumerieke pap in een cel moet halen, bijvoorbeeld het rekeningnummer halen uit de beschrijving van het betalingsdoel op een bankafschrift:
Houd er rekening mee dat de door Power Query gegenereerde conversieformule behoorlijk complex kan zijn:
Om het lezen en begrijpen te vergemakkelijken, kan het worden omgezet in een veel gezondere vorm met behulp van een gratis online service. Power Query-formatter:
Heel handig ding - respect voor de makers!
Voorbeeld 7: Datums converteren
Gereedschap Kolom uit voorbeelden kan ook worden toegepast op datum- of datetime-kolommen. Wanneer u de eerste cijfers van een datum invoert, geeft Power Query handig een lijst weer met alle mogelijke conversie-opties:
U kunt dus eenvoudig de originele datum converteren naar elk exotisch formaat, zoals "jaar-maand-dag":
Voorbeeld 8: Categorisatie
Als we de tool gebruiken Kolom uit voorbeelden naar een kolom met numerieke gegevens, werkt het anders. Stel dat we testresultaten van medewerkers hebben geladen in Power Query (voorwaardelijke scores in het bereik van 0-100) en we gebruiken de volgende voorwaardelijke gradatie:
- Masters - degenen die meer dan 90 . scoorden
- Experts - gescoord van 70 tot 90
- Gebruikers – van 30 tot 70
- Beginners - degenen die minder dan 30 . scoorden
Als we een kolom uit de voorbeelden aan de lijst toevoegen en deze gradaties handmatig gaan ordenen, dan zal Power Query al snel ons idee oppikken en een kolom toevoegen met een formule, waar operators in elkaar genest zijn if logica zal worden geïmplementeerd, vergelijkbaar met wat we nodig hebben:
Nogmaals, je kunt de situatie niet tot het einde drukken, maar klik op OK en corrigeer vervolgens de drempelwaarden al in de formule - het is sneller op deze manier:
Conclusies
Zeker een hulpmiddel Kolom uit voorbeelden is geen "magische pil" en vroeg of laat zullen er niet-standaard situaties of bijzonder verwaarloosde gevallen van een "collectieve boerderij" in de gegevens zijn, wanneer Power Query zal mislukken en niet in staat zal zijn om uit te werken wat we willen correct voor ons. Als hulpmiddel is het echter erg goed. En door de formules te bestuderen die hij heeft gegenereerd, kunt u uw kennis van de functies van de M-taal uitbreiden, wat in de toekomst altijd van pas zal komen.
- Tekst ontleden met reguliere expressies (RegExp) in Power Query
- Fuzzy tekst zoeken in Power Query
- Flash-invullen in Microsoft Excel