Inhalter
Ee vun de meescht gekuckte Videoen op mengem YouTube Kanal ass e Video iwwer Flash Fill a Microsoft Excel. D'Essenz vun dësem Tool ass datt wann Dir Är Quelldaten iergendwéi transforméiere musst, da musst Dir just ufänken d'Resultat ze tippen wat Dir wëllt an der niewendru Kolonn kréien. No e puer manuell getippten Zellen (normalerweis 2-3 sinn genuch), wäert Excel d'Logik vun den Transformatiounen déi Dir braucht "verstoen" an automatesch weiderféieren wat Dir aginn hutt, fir all déi monoton Aarbecht fir Iech ofzeschléissen:
D'Quintessens vun Effizienz. Déi magesch "maach et richteg" Knäppchen, déi mir all sou gär hunn, richteg?
Tatsächlech gëtt et en Analog vun sou engem Tool an der Power Query - do gëtt et genannt Kolonn aus Beispiller (Kolonn aus Beispiller). Tatsächlech ass dëst eng kleng kënschtlech Intelligenz a Power Query gebaut déi séier vun Ären Donnéeën léiere kann an se dann transforméieren. Loosst eis seng Fäegkeeten an e puer praktesch Szenarie méi no kucken fir ze verstoen wou et fir eis an realen Aufgaben nëtzlech ka sinn.
Beispill 1. Kleeblatt / opzedeelen Text
Loosst eis soen, mir hunn esou eng "intelligent" Tabell an Excel mat Daten iwwer Mataarbechter:
Lued et an Power Query am Standard Manéier - mam Knäppchen Vun Dësch / Gamme Reiter Daten (Daten - Vun Table / Range).
Ugeholl mir mussen eng Kolonn mat Familljennumm an Initialen fir all Employé derbäi (Ivanov SV fir den éischte Employé, etc.). Fir dëse Problem ze léisen, kënnt Dir eng vun zwou Methoden benotzen:
- Riets-klickt op d'Kolonntitel mat de Quelldaten a wielt de Kommando Füügt Kolonn aus Beispiller (Füügt Kolonn aus Beispiller);
- wielt een oder méi Kolonnen mat Daten an op der Reiter Dobäizemaachen eng Kolonn wielt eng Equipe Kolonn aus Beispiller. Hei kënnt Dir an der Dropdown-Lëscht uginn, ob all oder nëmmen ausgewielte Kolonnen analyséiert musse ginn.
Dann ass alles einfach - an der Kolonn, déi op der rietser Säit erschéngt, fänken mir un Beispiller vun de gewënschte Resultater unzefänken, an d'kënschtlech Intelligenz agebaut an Power Query probéiert eis Transformatiounslogik ze verstoen a weider eleng weiderzemaachen:
Iwwregens, Dir kënnt déi richteg Optiounen an all Zelle vun dëser Kolonn aginn, also net onbedéngt uewen erof an an enger Zeil. Och kënnt Dir Spalten aus der Analyse méi spéit derbäi oder ewechhuelen andeems Dir d'Checkboxen an der Titelbar benotzt.
Opgepasst op d'Formel uewen op der Fënster - dat ass wat Smart Power Query erstellt fir d'Resultater ze kréien déi mir brauchen. Dëst, iwwregens, ass de fundamentalen Ënnerscheed tëscht dësem Tool an Direkt fëllt an Excel. Instant Fëllung funktionnéiert wéi eng "schwaarz Këscht" - si weisen eis net d'Logik vun den Transformatiounen, awer ginn einfach fäerdeg Resultater a mir huelen se selbstverständlech. Hei ass alles transparent an Dir kënnt ëmmer absolut kloer verstoen wat genau mat den Donnéeën geschitt.
Wann Dir gesitt datt Power Query "d'Iddi gefaangen huet", da kënnt Dir sécher op de Knäppchen drécken OK oder Tastatur Ofkiirzung Ctrl+gitt - eng personaliséiert Kolonn mat enger Formel erfonnt duerch Power Query gëtt erstallt. Iwwregens, et kann spéider einfach geännert ginn als eng regulär manuell erstallt Kolonn (mat dem Kommando Eng Kolonn derbäisetzen - Benotzerdefinéiert Kolonn) andeems Dir op d'Gear Ikon riets vum Schrëttnumm klickt:
Beispill 2: Fall wéi a Sätz
Wann Dir riets klickt op d'Kolonntitel mat Text a wielt de Kommando Transformatioun (Transforméieren), da kënnt Dir dräi Kommandoen gesinn, déi verantwortlech sinn fir de Register z'änneren:
Praktesch a cool, awer an dëser Lëscht, zum Beispill, hunn ech perséinlech ëmmer nach eng Optioun gefeelt - Fall wéi a Sätz, wann Kapitaliséierung (Kapital) net den éischte Buschtaf an all Wuert gëtt, awer nëmmen den éischte Buschtaf an der Zell, an de Rescht vum Text wann Dëst ass a kleng Buschtawen ugewisen.
Dës fehlend Feature ass einfach mat kënschtlecher Intelligenz ëmzesetzen Kolonnen aus Beispiller - gitt just e puer Optiounen fir Power Query fir am selwechte Geescht weiderzemaachen:
Als Formel hei benotzt Power Query eng Rëtsch Funktiounen Text.Uewer и Text.Niddereg, Text ëmgewandelt an Uewer- an kleng Buschtawen, respektiv, a Funktiounen Text.Start и Text.Mëtt - Analoga vun den Excel Funktiounen LEFT a PSTR, fäeg en Ënnerstring aus dem Text vu lénks a vun der Mëtt ze extrahieren.
Beispill 3. Permutatioun vu Wierder
Heiansdo, beim Veraarbechtung vun den empfangenen Donnéeën, ass et néideg d'Wierder an den Zellen an enger bestëmmter Sequenz z'arrangéieren. Natierlech kënnt Dir d'Kolonn an getrennte Wuertsäulen duerch den Separator opdeelen an se dann an der spezifizéierter Uerdnung erëm pechen (vergiesst net Plazen ze addéieren), awer mat Hëllef vum Tool Kolonn aus Beispiller alles wäert vill méi einfach sinn:
Beispill 4: Nëmmen Zuelen
Eng aner ganz vital Aufgab ass nëmmen Zuelen (Zuelen) aus dem Inhalt vun der Zell erauszekréien. Wéi virdrun, nodeems Dir Daten an Power Query gelueden hutt, gitt op d'Tab Eng Kolonn derbäisetzen - Kolonn aus Beispiller a fëllt e puer Zellen manuell aus, fir datt de Programm versteet wat mir genau wëllen kréien:
Bingo!
Nach eng Kéier ass et derwäert uewen an der Fënster ze kucken fir sécher ze stellen datt Query d'Formel richteg generéiert huet - an dësem Fall enthält se eng Funktioun Text. Wielt, deen, wéi Dir et kéint roden, déi gegebene Zeechen aus dem Quelltext no der Lëscht extrahiert. Duerno kann dës Lëscht natierlech einfach an der Formelbar geännert ginn wann néideg.
Beispill 5: Text nëmmen
Ähnlech wéi dat viregt Beispill, kënnt Dir erauszéien a vice-versa - nëmmen den Text, läschen all Zuelen, Punktuatiounszeechen, etc.
An dësem Fall gëtt eng Funktioun benotzt, déi scho Géigendeel a Bedeitung ass - Text.Remove, déi Zeechen aus der ursprénglecher String no enger bestëmmter Lëscht läscht.
Beispill 6: Daten aus enger alphanumerescher Porridge extrahéieren
Power Query kann och hëllefen a méi schwieregen Fäll, wann Dir nëtzlech Informatioune vun der alphanumerescher Porridge an enger Zell extrahéiere musst, zum Beispill d'Kontosnummer vun der Beschreiwung vum Bezuelungszweck op engem Bankerklärung kréien:
Notéiert datt d'Power Query generéiert Konversiounsformel zimlech komplex ka sinn:
Fir d'Liesen a Versteesdemech ze vereinfachen, kann et an eng vill méi verstänneg Form ëmgewandelt ginn mat engem gratis Online Service. Power Query Formater:
Ganz praktesch Saach - Respekt un d'Creatoren!
Beispill 7: Konvertéieren Datumen
Instrument Kolonn aus Beispiller kann och op Datum oder Datetime Kolonnen applizéiert ginn. Wann Dir déi éischt Ziffere vun engem Datum aginn, wäert Power Query hëllefräich eng Lëscht vun all méiglech Konversioun Optiounen weisen:
Also kënnt Dir den ursprénglechen Datum ganz einfach an all exotesch Format konvertéieren, sou wéi "Joer-Mount-Dag":
Beispill 8: Kategoriséierung
Wa mir d'Tool benotzen Kolonn aus Beispiller zu enger Kolonn mat numereschen Donnéeën, funktionnéiert et anescht. Ugeholl datt mir Employé Testresultater an Power Query gelueden hunn (bedingte Partituren am Beräich 0-100) a mir benotzen déi folgend bedingungsgradatioun:
- Masters - déi, déi méi wéi 90 geschoss hunn
- Experten - vun 70 bis 90 geschoss
- Benotzer - vun 30 bis 70
- Ufänger - déi, déi manner wéi 30 geschoss hunn
Wa mir eng Kolonn aus de Beispiller op d'Lëscht addéieren an unzefänken dës Gradatiounen manuell ze arrangéieren, da wäert ganz séier Power Query eis Iddi ophuelen an eng Kolonn mat enger Formel addéieren, wou d'Bedreiwer anenee nestéiert hunn. if Logik wäert ëmgesat ginn, ganz ähnlech wéi wat mir brauchen:
Erëm, Dir kënnt d'Situatioun net bis zum Schluss drécken, awer klickt op OK a korrigéiert dann d'Schwellwäerter schonn an der Formel - et ass méi séier esou:
Conclusiounen
Sécherlech en Tool Kolonn aus Beispiller ass keng "Magie Pille" a fréier oder spéider ginn et net-Standard Situatiounen oder besonnesch vernoléissegt Fäll vun engem "kollektive Bauerenhaff" an den Donnéeën, wann Power Query fällt a wäert net fäeg sinn erauszefannen wat mir wëllen richteg fir eis. Wéi och ëmmer, als Hëllefsinstrument ass et ganz gutt. Plus, andeems Dir d'Formelen studéiert, déi hien generéiert huet, kënnt Dir Äert Wëssen iwwer d'Funktioune vun der M Sprooch ausbauen, wat ëmmer an der Zukunft praktesch wäert kommen.
- Parsing Text mat reegelméissegen Ausdréck (RegExp) a Power Query
- Fuzzy Text Sich an Power Query
- Flash Fëllt Microsoft Excel aus