Inhoud
Formulering van het probleem
Laten we eens kijken naar een mooie oplossing voor een van de zeer standaardsituaties waarmee de meeste Excel-gebruikers vroeg of laat te maken krijgen: u moet snel en automatisch gegevens uit een groot aantal bestanden verzamelen in één finaletafel.
Stel dat we de volgende map hebben, die verschillende bestanden bevat met gegevens uit filiaalsteden:
Het aantal bestanden maakt niet uit en kan in de toekomst veranderen. Elk bestand heeft een blad met de naam Verkoopwaar de gegevenstabel zich bevindt:
Het aantal rijen (orders) in de tabellen is natuurlijk anders, maar de set kolommen is overal standaard.
Taak: om gegevens van alle bestanden in één boek te verzamelen met daaropvolgende automatische updates bij het toevoegen of verwijderen van stadsbestanden of rijen in tabellen. Volgens de definitieve geconsolideerde tabel zal het mogelijk zijn om rapporten, draaitabellen, filter-sorteergegevens, enz. te bouwen. Het belangrijkste is om te kunnen verzamelen.
We selecteren wapens
Voor de oplossing hebben we de nieuwste versie van Excel 2016 nodig (de benodigde functionaliteit is standaard al ingebouwd) of eerdere versies van Excel 2010-2013 met de gratis invoegtoepassing geïnstalleerd Power Query van Microsoft (download het hier). Power Query is een superflexibele en superkrachtige tool om gegevens van de buitenwereld in Excel te laden, ze vervolgens te strippen en te verwerken. Power Query ondersteunt bijna alle bestaande gegevensbronnen - van tekstbestanden tot SQL en zelfs Facebook 🙂
Als je Excel 2013 of 2016 niet hebt, dan kan je (grapje) niet verder lezen. In oudere versies van Excel kan zo'n taak alleen worden uitgevoerd door een macro te programmeren in Visual Basic (wat erg moeilijk is voor beginners) of door eentonig handmatig te kopiëren (wat lang duurt en fouten oplevert).
Stap 1. Importeer één bestand als voorbeeld
Laten we eerst als voorbeeld gegevens uit één werkmap importeren, zodat Excel "het idee oppikt". Om dit te doen, maakt u een nieuwe lege werkmap en ...
- als je Excel 2016 hebt, open dan het tabblad Data en Query maken – Van bestand – Van boek (Gegevens — Nieuwe zoekopdracht - Uit bestand — Uit Excel)
- als je Excel 2010-2013 hebt met de Power Query-invoegtoepassing geïnstalleerd, open dan het tabblad Power Query en selecteer erop Uit bestand – Uit boek (Van bestand — Van Excel)
Ga vervolgens in het geopende venster naar onze map met rapporten en selecteer een van de stadsbestanden (het maakt niet uit welke, omdat ze allemaal typisch zijn). Na een paar seconden zou het Navigator-venster moeten verschijnen, waar u het blad dat we nodig hebben (Sales) aan de linkerkant moet selecteren, en de inhoud ervan wordt aan de rechterkant weergegeven:
Als u op de knop in de rechterbenedenhoek van dit venster klikt Downloaden (Laden), dan wordt de tabel onmiddellijk in zijn oorspronkelijke vorm in het werkblad geïmporteerd. Voor een enkel bestand is dit goed, maar we moeten veel van dergelijke bestanden laden, dus we gaan een beetje anders en klikken op de knop Correctie (Edit). Daarna zou de Power Query-query-editor in een apart venster moeten worden weergegeven met onze gegevens uit het boek:
Dit is een zeer krachtige tool waarmee u de tabel kunt "afwerken" tot de weergave die we nodig hebben. Zelfs een oppervlakkige beschrijving van al zijn functies zou ongeveer honderd pagina's in beslag nemen, maar, als het heel kort is, met dit venster kun je:
- filter onnodige gegevens, lege regels, regels met fouten uit
- gegevens sorteren op een of meer kolommen
- ontdoen van herhaling
- plaktekst op kolommen verdelen (door scheidingstekens, aantal tekens, enz.)
- zet tekst op volgorde (verwijder extra spaties, corrigeer hoofdletters, etc.)
- converteer datatypes op elke mogelijke manier (draai getallen zoals tekst om in normale getallen en vice versa)
- tabellen transponeren (draaien) en tweedimensionale kruistabellen uitbreiden tot platte tabellen
- voeg extra kolommen toe aan de tabel en gebruik formules en functies daarin met behulp van de M-taal die is ingebouwd in Power Query.
- ...
Laten we bijvoorbeeld een kolom met de tekstnaam van de maand aan onze tabel toevoegen, zodat het later gemakkelijker is om draaitabelrapporten te maken. Klik hiervoor met de rechtermuisknop op de kolomkop gegevensen selecteer de opdracht Dubbele kolom (Dubbele kolom)en klik vervolgens met de rechtermuisknop op de kop van de dubbele kolom die verschijnt en selecteer Opdrachten Transformeren – Maand – Maand Naam:
Er moet een nieuwe kolom worden gevormd met de tekstnamen van de maand voor elke rij. Door te dubbelklikken op een kolomkop, kunt u deze hernoemen van Datum kopiëren naar een meer comfortabele Maand, bijv.
Als het programma in sommige kolommen het gegevenstype niet helemaal correct herkende, kunt u het helpen door op het formaatpictogram aan de linkerkant van elke kolom te klikken:
U kunt regels met fouten of lege regels uitsluiten, evenals onnodige managers of klanten, met behulp van een eenvoudig filter:
Bovendien worden alle uitgevoerde transformaties vastgelegd in het rechterpaneel, waar ze altijd kunnen worden teruggedraaid (cross) of hun parameters kunnen wijzigen (versnelling):
Licht en elegant, nietwaar?
Stap 2. Laten we ons verzoek omzetten in een functie
Om vervolgens alle gegevenstransformaties die voor elk geïmporteerd boek zijn gemaakt, te herhalen, moeten we ons gecreëerde verzoek omzetten in een functie, die vervolgens op al onze bestanden wordt toegepast. Om dit te doen is eigenlijk heel eenvoudig.
Ga in de Query-editor naar het tabblad Weergave en klik op de knop Geavanceerde editor (Bekijken - Geavanceerde editor). Er zou een venster moeten openen waarin al onze eerdere acties worden geschreven in de vorm van code in de M-taal. Houd er rekening mee dat het pad naar het bestand dat we voor het voorbeeld hebben geïmporteerd, hard gecodeerd is in de code:
Laten we nu een paar aanpassingen maken:
Hun betekenis is eenvoudig: de eerste regel (bestandspad)=> verandert onze procedure in een functie met een argument bestandspad, en hieronder veranderen we het vaste pad naar de waarde van deze variabele.
Allemaal. Klik op Finish en zou dit moeten zien:
Wees niet bang dat de gegevens zijn verdwenen - in feite is alles in orde, alles zou er zo uit moeten zien 🙂 We hebben met succes onze aangepaste functie gemaakt, waarbij het hele algoritme voor het importeren en verwerken van gegevens wordt onthouden zonder gebonden te zijn aan een specifiek bestand . Het blijft om het een meer begrijpelijke naam te geven (bijvoorbeeld: gegevens verkrijgen) in het paneel rechts in het veld Voornaam en je kunt oogsten Home — Sluiten en downloaden (Home — Sluiten en laden). Houd er rekening mee dat het pad naar het bestand dat we voor het voorbeeld hebben geïmporteerd, hard gecodeerd is in de code. U keert terug naar het hoofdvenster van Microsoft Excel, maar een paneel met de gemaakte verbinding met onze functie zou aan de rechterkant moeten verschijnen:
Stap 3. Alle bestanden verzamelen
Al het moeilijkste deel is achter de rug, het aangename en gemakkelijke deel blijft. Ga naar het tabblad Gegevens – Query maken – Van bestand – Van map (Data — Nieuwe query — Van bestand — Van map) of, als je Excel 2010-2013 hebt, vergelijkbaar met het tabblad Power Query. Geef in het venster dat verschijnt de map op waarin al onze bronstadsbestanden zich bevinden en klik op OK. De volgende stap zou een venster moeten openen waarin alle Excel-bestanden in deze map (en de bijbehorende submappen) en details voor elk ervan worden vermeld:
Klik Veranderen (Edit) en opnieuw komen we in het bekende venster van de query-editor.
Nu moeten we nog een kolom aan onze tabel toevoegen met onze gemaakte functie, die de gegevens uit elk bestand "trekt". Ga hiervoor naar het tabblad Kolom toevoegen – Aangepaste kolom (Kolom toevoegen — Aangepaste kolom toevoegen) en voer in het venster dat verschijnt onze functie in gegevens verkrijgen, waarbij u als argument het volledige pad naar elk bestand specificeert:
Na het klikken op OK de gemaakte kolom moet worden toegevoegd aan onze tabel aan de rechterkant.
Laten we nu alle onnodige kolommen verwijderen (zoals in Excel, met de rechtermuisknop - verwijderen), laat alleen de toegevoegde kolom en de kolom met de bestandsnaam over, omdat deze naam (meer precies, de stad) handig is om in de totale gegevens voor elke rij te hebben.
En nu het "wauw-moment" - klik op het pictogram met zijn eigen pijlen in de rechterbovenhoek van de toegevoegde kolom met onze functie:
… vink uit Gebruik de originele kolomnaam als voorvoegsel (Gebruik de originele kolomnaam als voorvoegsel)en klik op OK. En onze functie laadt en verwerkt de gegevens van elk bestand, volgens het opgenomen algoritme en verzamelt alles in een gemeenschappelijke tabel:
Voor volledige schoonheid kun je ook de .xlsx-extensies uit de eerste kolom met bestandsnamen verwijderen - door standaard te vervangen door "niets" (klik met de rechtermuisknop op de kolomkop - Plaatsvervanger) en hernoem deze kolom naar Plaats. En corrigeer ook het gegevensformaat in de kolom met de datum.
Allemaal! Klik op Home – Sluiten en laden (Home — Sluiten & Laden). Alle gegevens die door de zoekopdracht voor alle steden worden verzameld, worden geüpload naar het huidige Excel-blad in het "slimme tabel" -formaat:
De gemaakte verbinding en onze assembly-functie hoeven op geen enkele manier afzonderlijk te worden opgeslagen - ze worden op de gebruikelijke manier samen met het huidige bestand opgeslagen.
In de toekomst, bij eventuele wijzigingen in de map (steden toevoegen of verwijderen) of in bestanden (het aantal regels wijzigen), volstaat het om met de rechtermuisknop op de tabel of op de zoekopdracht in het rechterpaneel te klikken en de opdracht Bijwerken en opslaan (Vernieuwen) – Power Query zal alle gegevens binnen enkele seconden opnieuw "herbouwen".
PS
Wijziging. Na de updates van januari 2017 heeft Power Query geleerd om zelf Excel-werkmappen te verzamelen, dat wil zeggen dat er geen aparte functie meer nodig is - het gebeurt automatisch. De tweede stap uit dit artikel is dus niet langer nodig en het hele proces wordt merkbaar eenvoudiger:
- Kies Aanvraag maken – Uit bestand – Uit map – Map selecteren – OK
- Nadat de lijst met bestanden verschijnt, drukt u op Veranderen
- Vouw in het venster Query-editor de kolom Binair uit met een dubbele pijl en selecteer de bladnaam die uit elk bestand moet worden gehaald
En dat is alles! Liedje!
- Herontwerp van de kruistabel in een platte die geschikt is voor het bouwen van draaitabellen
- Een geanimeerd bellendiagram maken in Power View
- Macro om bladen uit verschillende Excel-bestanden samen te voegen tot één