Gegevens uit verschillende Excel-werkmappen samenvoegen. Stel dat u een map vol Excel-bestanden heeft. Elk heeft een werkblad met een verschillend aantal rijen. U kunt alle bestanden in de map in één Excel-werkmap importeren.
Bekijk video
- De schone gegevens opnieuw bekijken met Power Query-podcast # 2037
- Power Query kan nu alle Excel-bestanden in een map combineren.
- Verbeterd: ze verwijderen automatisch de kopteksten van alles behalve het eerste bestand.
- U kiest welk bestand u als voorbeeldbestand wilt gebruiken.
- Kies welk werkblad, tabel, benoemd bereik u wilt importeren
- Gebruik de query-editor om transformaties uit te voeren
- Sluiten en laden om alle bestanden te combineren
- Vernieuw de query later om deze bij te werken
Videotranscriptie
Leer Excel van Podcast, aflevering 2077: werkmappen samenvoegen
Hé, welkom terug bij de netcast, ik ben Bill Jelen. Nog maar 40 afleveringen geleden, over het opschonen van gegevens met Power Query, hoe je een hele reeks CSV-bestanden kunt nemen en ze in één Excel-bestand kunt overbrengen. Nou, deze functie is zojuist dramatisch beter geworden.
Dus ik heb hier vandaag een map en in deze map staan een hele reeks maandelijkse Excel-bestanden, waarvan 22 voor bijna twee jaar. Ik moet al deze bestanden combineren. Deze hebben allemaal dezelfde kolomindeling, oké? Ze hebben dus allemaal 9 kolommen, allemaal dezelfde kopteksten enzovoort, oké. Dus we gaan deze allemaal combineren zonder macro.
Als u zich in Excel 2016 bevindt, gaat u naar het tabblad Gegevens om gegevens op te halen en te transformeren. Als je Excel 2010 of 2013 gebruikt, ga je eropuit en download je een geweldig product van Microsoft genaamd Power Query. Je kunt het hele ding downloaden en het in minder dan een minuut installeren, oké.
Dus we komen hier om gegevens op te halen, uit een bestand, maar niet uit een enkele werkmap. We willen het uit een map als deze halen. Oké, hier zijn ze. Ze vragen je om naar de map te bladeren, maar ik ga daar gewoon in klikken en Ctrl + V plakken omdat ik de map heb geplakt, klik op OK. Dus lieten ze me een voorbeeld zien van de gegevens die ik heb. Ik open deze kleine vervolgkeuzelijst Combineren en kies Combineren en bewerken. Oké, en dan is de stap hier dat we een bestand gaan kiezen dat een goed representatief bestand is, oké, en ze kiezen gewoon het eerste dat toevallig alfabetisch is. Het maakt niet uit welke je kiest. En dan deze voorbeeldquery die we bouwen op basis van het eerste bestand dat we kiezen, in dit geval wil ik Sheet1.Als u meerdere bladen heeft, kunt u Blad2 of het blad met de naam Inkomstenoverzicht of de Naamreeks of een tabel zeggen. En ze lieten me zien hoe het eruit ziet en ik klik op OK.
Nu is er veel gaande in dit scherm. Wat ze hier eigenlijk hebben gedaan, is dat ze een voorbeeldquery hebben gemaakt, ahh voorbeeldquery, en die vervolgens als een functie hebben gemaakt en die functie hebben uitgevoerd tegen alles in de map. Ze voegen een nieuwe kolom toe die me vertelt waar het bestand vandaan kwam en in dit specifieke geval weet ik al waar het vandaan kwam omdat we hier een datum hebben ingevuld. Dus ik heb dat niet nodig, ik ga met de rechtermuisknop klikken en dat verwijderen. En misschien staan er gegevens in die ik niet nodig heb. Misschien gaan deze gegevens naar een klant, dus ik wil de winst niet tonen, zodat ik die kolom kan verwijderen en zelfs de kolom Kosten kan verwijderen, en misschien zijn er vervelende dingen zoals, weet je, ze gebruikten slechte kopteksten en ik wil deze headers hernoemen. Dus klik met de rechtermuisknop en hernoem, we noemen het QTY voor hoeveelheid.Misschien staan de dingen in de verkeerde volgorde, misschien wil ik dit item meenemen en naar een andere plek verplaatsen, oké? Alleen al deze kleine vervelende dingen die u mogelijk heeft en, natuurlijk, hier doen ze alle toegepaste stappen en dan kunt u doorgaan met alle Power Query-dingen die u moet doen. Maar in dit geval is het gewoon simpel. Ik wil gewoon de gegevens binnen krijgen. Dus ik ga sluiten en laden, een paar seconden gaan voorbij en daar is het. Ik heb mijn finale. Laten we dit hier gewoon op datum sorteren; zie je daar gaan we van januari 2018 helemaal naar oktober 2019. Hoe gaaf is dat.Als u alle toegepaste stappen opnieuw uitvoert, kunt u doorgaan met het doen van alle Power Query-dingen die u moet doen. Maar in dit geval is het gewoon simpel. Ik wil gewoon de gegevens binnen krijgen. Dus ik ga sluiten en laden, een paar seconden gaan voorbij en daar is het. Ik heb mijn finale. Laten we dit hier gewoon op datum sorteren; zie je daar gaan we van januari 2018 helemaal naar oktober 2019. Hoe gaaf is dat.Als u alle toegepaste stappen opnieuw uitvoert, kunt u doorgaan met het doen van alle Power Query-dingen die u moet doen. Maar in dit geval is het gewoon simpel. Ik wil gewoon de gegevens binnen krijgen. Dus ik ga sluiten en laden, een paar seconden gaan voorbij en daar is het. Ik heb mijn finale. Laten we dit hier gewoon op datum sorteren; zie je daar gaan we van januari 2018 helemaal naar oktober 2019. Hoe gaaf is dat.
Hier is het verbazingwekkende, verbazingwekkende hieraan. Laten we zeggen dat er iets verandert in onze map. Dus hier terug in de maandbestanden, ik krijg twee nieuwe bestanden, Plakken, en nu zijn er nog twee records hier, nog twee Excel-bestanden die ik moet combineren. Ga gewoon terug naar mijn Excel-bestand dat momenteel 516 rijen heeft, zoek de MonthFiles en klik op de knop Vernieuwen. En zo snel heb ik nu de nieuwe records van november en december. Power Query is een geweldige nieuwe tool gratis voor Excel 2010, gratis voor Excel 2013. Het was prachtig toen ze CSV-bestanden konden combineren, maar nu ze ook Excel-bestanden kunnen combineren, gewoon fenomenaal.
Dus ik heb mijn boek, Power Excel bijgewerkt met, de editie van 2017, bevat Power Query, Power BI en alle nieuwe tools. Bekijken. Klik op die "i" in de rechterbovenhoek.
Oké, afsluiting van deze aflevering: we bezoeken de Clean Data opnieuw met de Power Query-podcast # 2037 waarin we meerdere CSV-bestanden combineren. We kunnen nu diezelfde tool, een verbeterde versie van die tool, gebruiken om alle Excel-bestanden in een map te combineren. Een andere verbetering, de wijziging ten opzichte van 2037, ze verwijderen automatisch de kopteksten van alles behalve het eerste bestand en promoten die kopteksten in het eerste bestand. U kiest welk bestand u als voorbeeldbestand wilt gebruiken. In mijn geval heb ik januari gebruikt. Werk - Kies welk werkblad, tabel of benoemd bereik u wilt importeren en voer vervolgens eventuele transformaties uit in de query-editor. We klikken op Sluiten en laden, het zal alle bestanden combineren. En als u dan meer bestanden in de map laat vallen of als sommige bestanden zijn verwijderd of als sommige bestanden zijn gewijzigd, vernieuw dan gewoon de query en het zal al deze stappen opnieuw uitvoeren. Power Query,een absoluut geweldige tool.
Ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2077.xlsm
