Inhalter
Formuléierung vum Problem
Loosst eis eng schéi Léisung kucken fir eng vun de ganz Standardsituatiounen, déi déi meescht Excel Benotzer fréier oder spéider stellen: Dir musst séier an automatesch Daten aus enger grousser Zuel vu Dateien an eng Finale Dësch sammelen.
Ugeholl mir hunn de folgenden Dossier, deen e puer Dateie mat Daten aus Filialstied enthält:
D'Zuel vun de Fichieren ass egal a kann an Zukunft änneren. All Fichier huet e Blat mam Numm Saleswou d'Daten Dësch läit:
D'Zuel vun de Reihen (Bestellungen) an den Dëscher ass natierlech anescht, awer d'Saach vu Sailen ass iwwerall Standard.
Aufgab: Daten aus all Dateien an ee Buch ze sammelen mat spéider automateschen Aktualiséierung wann Dir Staddateien oder Zeilen an Dëscher derbäigesat oder geläscht gëtt. No der Finale konsolidéiert Dësch, da wäert et méiglech sinn all Rapporten ze bauen, Pivot Dëscher, Filter-Sortéieren Daten, etc. Den Haapt Saach ass ze sammelen.
Mir wielen Waffen
Fir d'Léisung brauche mir déi lescht Versioun vun Excel 2016 (déi néideg Funktionalitéit ass schonn am Standard gebaut) oder fréier Versioune vun Excel 2010-2013 mat dem gratis Add-in installéiert Muecht Ufro vu Microsoft (download et hei). Power Query ass e super flexibelt a super mächtegt Tool fir Daten an Excel vun der Äussewelt ze lueden, duerno ze strippen an ze veraarbecht. Power Query ënnerstëtzt bal all existent Datequellen - vun Textdateien op SQL a souguer Facebook 🙂
Wann Dir net Excel 2013 oder 2016 hutt, da kënnt Dir net weider liesen (just Geck). An eelere Versioune vun Excel kann esou eng Aufgab nëmmen duerch Programméiere vun engem Macro an Visual Basic (wat fir Ufänger ganz schwéier ass) oder duerch monoton manuell Kopie (wat laang dauert a Feeler generéiert) erreecht ginn.
Schrëtt 1. Import eent Fichier als Prouf
Als éischt, loosst eis Daten aus engem Aarbechtsbuch als Beispill importéieren, sou datt Excel "d'Iddi ophëlt". Fir dëst ze maachen, erstellt eng nei eidel Aarbechtsbuch an ...
- wann Dir Excel 2016 hutt, da öffnen d'Tab Daten an dann Erstellt Ufro - Vun Datei - Vum Buch (Daten - Nei Ufro- Vun Datei - Vun Excel)
- wann Dir Excel 2010-2013 hutt mat der Power Query Add-in installéiert, da öffnen d'Tab Muecht Ufro a wielt op et Vun Fichier - Aus Buch (Aus Datei - Aus Excel)
Dann, an der Fënster déi opmaacht, gitt an eisen Dossier mat Berichter a wielt eng vun de Staddateien (et ass egal wéi eng, well se all typesch sinn). No e puer Sekonnen soll d'Navigator-Fënster erscheinen, wou Dir op der lénker Säit d'Blat auswiele musst, déi mir brauchen (Verkaf), a säin Inhalt gëtt op der rietser Säit ugewisen:
Wann Dir op de Knäppchen an der ënneschter rechter Ecke vun dëser Fënster klickt Download (Luet), da gëtt den Dësch direkt op d'Blat a senger ursprénglecher Form importéiert. Fir eng eenzeg Datei ass dëst gutt, awer mir musse vill esou Dateien lueden, also wäerte mir e bëssen anescht goen a klickt op de Knäppchen Ze korrigéieren (Ännerung). Duerno soll de Power Query Query Editor an enger separater Fënster mat eisen Daten aus dem Buch ugewise ginn:
Dëst ass e ganz mächtegt Tool dat Iech erlaabt den Dësch op d'Vue ze "fäerdegen" déi mir brauchen. Och eng iwwerflächlech Beschreiwung vun all senge Funktiounen dauert ongeféier honnert Säiten, awer, wann ganz kuerz, mat dëser Fënster kënnt Dir:
- Filter aus onnéideg Donnéeën, eidel Linnen, Linnen mat Feeler
- sortéiert Daten no enger oder méi Kolonnen
- lass vun Widderhuelung
- trennt plakeg Text duerch Kolonnen (duerch Ofgrenzer, Zuel vun Zeechen, asw.)
- Text an Uerdnung setzen (extra Plazen ewechhuelen, Fall korrekt, etc.)
- konvertéieren Datentypen op all méiglech Manéier (zifferen wéi Text an normal Zuelen ëmsetzen a vice versa)
- Transpose (rotéieren) Dëscher an erweidert zweedimensional Kräiztabellen a flaach
- füügt zousätzlech Kolonnen un den Dësch a benotzt Formelen a Funktiounen an hinnen mat der M Sprooch agebaut an Power Query.
- ...
Zum Beispill, loosst eis eng Kolonn mam Textnumm vum Mount op eisen Dësch setzen, fir datt et spéider méi einfach wier Pivot-Tabelberichter ze bauen. Fir dëst ze maachen, klickt riets op d'Kolonntitel Datuma wielt de Kommando Duplizéiert Kolonn (Duplizéiert Kolonn), a klickt dann op den Header vun der duplizéierter Kolonn déi erschéngt a wielt Kommandoen Transforméieren - Mount - Mount Numm:
Eng nei Kolonn soll mat den Textnimm vum Mount fir all Zeil geformt ginn. Andeems Dir op eng Kolonntitel duebel klickt, kënnt Dir se ëmbenennen Datum kopéieren zu engem méi bequem Mount, bv.
Wann an e puer Kolonnen de Programm den Datentyp net ganz korrekt erkannt huet, da kënnt Dir et hëllefen andeems Dir op d'Format Ikon op der lénker Säit vun all Kolonn klickt:
Dir kënnt Linnen mat Feeler oder eidel Linnen ausschléissen, souwéi onnéideg Manager oder Clienten, mat engem einfachen Filter:
Ausserdeem sinn all duerchgefouert Transformatiounen an der rietser Panel fixéiert, wou se ëmmer zréckgerullt kënne ginn (Kräiz) oder hir Parameteren änneren (Gear):
Liicht an elegant, ass et net?
Schrëtt 2. Loosst eis eis Ufro an eng Funktioun transforméieren
Fir duerno all d'Datentransformatiounen, déi fir all importéiert Buch gemaach goufen, ze widderhuelen, musse mir eis erstallt Ufro an eng Funktioun konvertéieren, déi dann op all eis Dateien applizéiert gëtt. Fir dëst ze maachen ass eigentlech ganz einfach.
Am Query Editor, gitt op de View Tab a klickt op de Knäppchen Fortgeschratt Redakter (View - Advanced Editor). Eng Fënster soll opmaachen wou all eis virdrun Aktiounen a Form vu Code an der M Sprooch geschriwwe ginn. Notéiert w.e.g. datt de Wee fir d'Datei, déi mir fir d'Beispill importéiert hunn, am Code hardcoded ass:
Loosst eis elo e puer Upassunge maachen:
Hir Bedeitung ass einfach: déi éischt Zeil (Dateipath) => mécht eis Prozedur an eng Funktioun mat engem Argument Dateipath, an ënnen änneren mir de fixe Wee op de Wäert vun dëser Variabel.
All. Klickt op Freck a soll dëst gesinn:
Gitt net Angscht datt d'Donnéeën verschwonnen sinn - tatsächlech ass alles OK, alles soll esou ausgesinn 🙂 Mir hunn eis personaliséiert Funktioun erfollegräich erstallt, wou de ganzen Algorithmus fir d'Importéieren an d'Veraarbechtung vun Donnéeën erënnert ouni un eng spezifesch Datei gebonnen ze sinn . Et bleift et e méi verständlechen Numm ze ginn (zum Beispill getDaten) am Panel riets am Feld éischte Numm an Dir kënnt ernimmen Home - Zoumaachen an eroflueden (Home - Zoumaachen a Lueden). Notéiert w.e.g. datt de Wee op d'Datei, déi mir fir d'Beispill importéiert hunn, am Code hardcoded ass. Dir gitt zréck an d'Haaptfenster vun Microsoft Excel, awer e Panel mat der erstallt Verbindung zu eiser Funktioun soll op der rietser Säit erscheinen:
Schrëtt 3. Sammelen all Fichieren
All déi haardsten Deel ass hannert, déi agreabel an einfach Deel bleift. Gitt op d'Tab Daten - Erstellt Ufro - Vun Datei - Vun Dossier (Daten - Nei Ufro - Vun Datei - Aus Dossier) oder, wann Dir Excel 2010-2013 hutt, ähnlech wéi d'Tab Muecht Ufro. An der Fënster déi erschéngt, spezifizéiert den Dossier wou all eis Quell Staddateien sinn a klickt OK. De nächste Schrëtt sollt eng Fënster opmaachen, wou all d'Excel-Dateien, déi an dësem Dossier fonnt goufen (a seng Ënnerfolder) an d'Detailer fir jidderee vun hinnen opgelëscht sinn:
klickt änneren (Ännerung) an erëm komme mir an déi vertraute Query Editor Fënster.
Elo musse mir eng aner Kolonn un eis Tabelle mat eiser erstallt Funktioun addéieren, déi d'Donnéeën aus all Datei "zitt". Fir dëst ze maachen, gitt op d'Tab Spalt derbäi - Benotzerdefinéiert Kolonn (Add Column - Add Custom Column) an an der Fënster déi erschéngt, gitt eis Funktioun getDaten, spezifizéiert dofir als Argument de komplette Wee fir all Datei:
Nodeems Dir op OK déi erstallt Kolonn soll an eiser Tabell op der rietser Säit bäigefüügt ginn.
Loosst eis elo all onnéideg Kolonnen läschen (wéi an Excel, mat der rietser Maustast - ewechhuelen), nëmmen déi addéiert Kolonn an d'Kolonn mam Dateinumm hannerloossen, well dësen Numm (méi präzis, d'Stad) nëtzlech ass fir an de Gesamtdaten fir all Zeil ze hunn.
An elo de "Wow Moment" - klickt op d'Ikon mat hiren eegene Pfeile an der oberer rechter Ecke vun der addéierter Kolonn mat eiser Funktioun:
… auschecken Benotzt den ursprénglechen Kolonnnumm als Präfix (Benotzt den originale Kolonnennumm als Präfix)a klickt op OK. An eis Funktioun luedt a veraarbecht d'Donnéeën aus all Datei, no dem opgeholl Algorithmus a sammelt alles an enger gemeinsamer Tabell:
Fir komplett Schéinheet kënnt Dir och d'.xlsx Extensiounen aus der éischter Kolonn mat Dateinumm ewechhuelen - duerch Standard Ersatz mat "näischt" (riets klickt op de Kolonn Header - Substitute) an ëmbenennen dës Kolonn op Gefiller. A korrigéiert och d'Datenformat an der Kolonn mam Datum.
Alles! Klickt op Heem - Zoumaachen a Lueden (Home - Zoumaachen & Lueden). All Donnéeën, déi vun der Ufro fir all Stied gesammelt ginn, ginn op den aktuellen Excel-Blatt am "Smart Table"-Format eropgelueden:
Déi erstallt Verbindung an eis Assembléefunktioun mussen op kee Fall getrennt gespäichert ginn - si ginn zesumme mat der aktueller Datei op déi üblech Manéier gespäichert.
An Zukunft, mat all Ännerungen am Dossier (derbäisetzen oder ewechhuelen vun Stied) oder an Dateien (Ännerung vun der Unzuel vun Zeilen), ass et genuch fir direkt op den Dësch oder op d'Ufro an der rietser Panel ze klickt a wielt de Kommando Update & späicheren (Erfrëschen) - Power Query wäert all d'Donnéeën an e puer Sekonnen erëm opbauen.
PS
Ännerung. No den Updates vum Januar 2017 huet Power Query geléiert wéi een Excel-Aarbechtsbicher selwer sammelt, also net néideg méi eng separat Funktioun ze maachen - et geschitt automatesch. Also ass den zweete Schrëtt vun dësem Artikel net méi gebraucht an de ganze Prozess gëtt merkbar méi einfach:
- wielt Erstellt Ufro - Vun Datei - Vun Dossier - Wielt Dossier - OK
- Nodeems d'Lëscht vun de Dateien erschéngt, dréckt änneren
- An der Query Editor Fënster erweidert d'Binär Kolonn mat engem Duebelpfeil a wielt de Blatnumm fir aus all Datei ze huelen
An dat ass alles! Lidd!
- Redesign vun der Crosstab zu engem flaach gëeegent fir Pivot Dëscher ze bauen
- Bauen en animéierten Bubble-Diagramm a Power View
- Macro fir Blieder aus verschiddene Excel Dateien an een ze sammelen