De vraag van vandaag: Marcia heeft een lijst nodig van alle pdf-bestanden met belastingfacturen uit een map in een Excel-spreadsheet. Dit is gemakkelijk te doen als u Excel 2016 op een Windows-pc gebruikt met de nieuwe tools voor Gegevens ophalen en transformeren.
Als u Excel 2010 voor Windows of Excel 2013 voor Windows heeft, moet u de gratis Power Query-invoegtoepassing van Microsoft downloaden. Ga naar uw favoriete zoekmachine en typ "Download Power Query" om de huidige link te vinden. (Microsoft verandert graag elk kwartaal URL's en mijn geweldige webman heeft een hekel aan wanneer onze links verouderd zijn, dus ik ga niet eens proberen hier een link te plaatsen.)
De onderstaande video laat je de volledige stappen zien, maar hier is het overzicht:
- Begin met een leeg werkblad
- Gegevens, gegevens ophalen, uit bestand, uit map
- Blader naar de map
- Klik op Bewerken in plaats van Laden
- Open de vervolgkeuzelijst voor het bestandstype en verwijder alles dat geen pdf is
- Open het filter op de map en verwijder eventuele submappen voor afval
- Bewaar alleen bestandsnaam en map - klik met de rechtermuisknop op elke kolomkop en kies Verwijderen
- Sleep de mapkop naar de linkerkant van de bestandskop. Hierdoor kan het samenvoegen werken.
- Selecteer beide kolommen. Klik op een rubriek. Shift + klik op de andere kop.
- Kies Kolom toevoegen, Kolommen samenvoegen, typ een nieuwe naam voor de kolom. Klik OK.
- Klik met de rechtermuisknop op de kop voor de nieuwe kolom en Verwijder andere kolommen
- Home, sluiten en laden
- Het verbazingwekkende deel … je kunt de zoekopdracht later vernieuwen. Klik op het pictogram Vernieuwen in het paneel Query's en verbindingen.
Hoewel Power Query verbazingwekkend krachtig is, is dit een van mijn favoriete taken. Ik wil vaak een VBA-macro uitvoeren op elk bestand in een map. Een lijst krijgen van alle pdf's in een map is een goed startpunt.
Bekijk video
Videotranscriptie
Leer Excel van podcast, aflevering 2181 - Lijst met mapbestanden in Excel!
Hé, welkom terug bij de netcast, ik ben Bill Jelen. De vraag van vandaag: iemand heeft een lijst met pdf-bestanden met belastingfacturen in een map en ze moeten de lijst met al die bestandsnamen in Excel krijgen. Oké, en een manier om dat te doen is door ze allemaal te typen, of te kopiëren en plakken vanuit Windows Verkenner, maar er is een geweldige tool die dit kan oplossen. En mijn eerste vraag was: "Welke versie van Excel heb je?" Want als je toevallig Excel 2016 hebt, krijgen ze deze geweldige nieuwe functionaliteit genaamd "Gegevens ophalen en transformeren"! Nu, in Office 365, bevindt het zich aan de linkerkant, ik denk dat het in de originele versie van Excel 2016 in de derde groep zat, oké, dus zoek gewoon naar Ophalen en transformeren. Als u zich in Excel 2010 of Excel 2013 voor Windows bevindt, kunt u Power Query downloaden en heeft u uw eigen tabblad met dit exacte spul.
Laten we nu eens snel naar deze map kijken, oké, ik heb hier zojuist een nepmap gemaakt met wat nepgegevens. Je zult zien dat er Excel-bestanden in deze map staan en pdf's, ik wil alleen de pdf's, en er zijn ook enkele submappen, ik wil deze pdf's niet, ik wil alleen de pdf's in de hoofdmap. Dus C: Budgets, ik ga dat kopiëren en dan hier terugkomen naar Excel, en we gaan zeggen dat we gegevens willen ophalen, uit een bestand, uit een hele map, zoals dit, en dan typ daar het mappad of gebruik de knop Bladeren, een van beide. En wanneer u dit eerste scherm krijgt, wilt u zeker Bewerken, en nu bevinden we ons in de Power Query-editor.
Oké, dus mijn doel hier, ik heb de inhoud niet nodig, dus ik ga met de rechtermuisknop klikken en zeg die kolom verwijderen. Er is mijn lijst met bestanden, ik wil alleen pdf-bestanden, dus als er iets is dat geen pdf is, wil ik alleen pdf's, klik op OK, ik zie dat het alleen de pdf-bestanden zijn. Ah, en kijk dan hier, zie, nu halen ze dingen binnen uit alleen de originele map, en uit de Garbage-map, dus ik open dit en verwijder alles wat niet de originele map is. Oké, dus nu heb ik een mooie kleine lijst, en deze lijst is, weet je wat, 9 records, maar ik wed dat ze in het echte leven waarschijnlijk tientallen of honderden hiervan hebben. Oké, ik heb nu geen andere dingen nodig, dus ik kan met de rechtermuisknop klikken en die kolommen verwijderen.
Oké, wat ik hier nu echt nodig heb, is dat ik het mappad en de bestandsnaam samen nodig heb. Oké, dus ik ga FolderPath nemen en het naar links slepen en daar neerzetten, en dan de magische stap hier: in gewone Excel zouden we hiervoor aaneenschakeling moeten doen, maar wat ik ga doen is, ik ga kolommen samenvoegen. Dus ik ga Kolom toevoegen en kies Kolommen samenvoegen, het scheidingsteken wordt Geen, de kolom Nieuw wordt Bestandsnaam genoemd en klik op OK, oké, dus we hebben de mapnaam, de schuine streep en de bestandsnaam , zoals dat. Dat is eigenlijk het enige dat we nodig hebben, dus ik ga met de rechtermuisknop klikken en Zeg de andere kolommen verwijderen, en dan ten slotte Home, Sluiten en Laden, en we krijgen een geheel nieuw blad met onze gegevens. Oké, het komt binnen als een tabel, dus ik kopieer dit gewoon, Ctrl + C,en kom dan hier naar waar ik de gegevens echt wilde hebben hier, en Plak speciale waarden, klik op OK. Nu is het geen tabel meer, het zijn gewoon mijn pure gegevens, zoals dat, en nu, hier is het echt mooie hieraan.
Dus we hebben dit een keer opgezet, en wauw, dat kostte minder dan 3 minuten om op te zetten, maar laten we teruggaan naar die map Budgets, en laten we wat dingen verplaatsen. Laten we een van deze Garbage-records nemen, en we kopiëren het naar de hoofdmap, Ctrl + V, oké, dus nu zijn er hier meer dingen, er zijn 10 pdf-bestanden in plaats van 9. Als ik hier kom waar de zoekopdracht is, en verder aan de rechterkant van het scherm, in de Queries & Connections, moet je dit misschien breder maken, ik heb de mijne al breder gemaakt, je ziet onze budgetten met 9 geladen rijen. Ik klik hier op het kleine pictogram Vernieuwen en heel snel heeft Budgets nu 10 rijen geladen. Dus het pikt de nieuwe records op, u stelt dit één keer in, en dan kunt u gewoon vernieuwen om de nieuwe gegevens op te halen.
Welnu, dit is het punt in de podcast waar ik je meestal vraag om mijn boek te kopen, maar in plaats daarvan ga ik je vandaag vragen om dit boek "M is for (DATA) MONKEY" van Ken Puls en Miguel Escobar te kopen. Een GEWELDIG boek dat je alles leert over het gebruik van Power Query of Gegevens ophalen en transformeren, alles wat ik heb geleerd over Power Query heb ik uit dit boek geleerd.
OK, sluit deze aflevering af: ons doel is hoe u een lijst met bestandsnamen in Excel kunt importeren.Als u Excel 2016 heeft, kunt u de nieuwe Gegevens ophalen en transformeren gebruiken. Als je 2016 niet hebt, maar je hebt een echte versie van Excel die onder Windows draait, dan kun je de gratis Power Query-invoegtoepassing downloaden voor Excel 2010 of Excel 2013. Het werkt niet op je Android-telefoon, of je iPad, of je iPhone, of Surface RT, of je Mac, klopt, het is alleen voor Windows-versies van Excel. We beginnen dus met een leeg werkblad, Gegevens, Gegevens ophalen, Uit bestand, Uit map, voer de mapnaam in of Bladeren, zorg ervoor dat u op Bewerken klikt in plaats van Laden. En filter vervolgens op het filter op het bestandstype om alles te verwijderen dat geen pdf is, filter op de mapnaam om alle submappen van afval te verwijderen. Bewaar alleen de bestandsnaam en map,dus klik met de rechtermuisknop op die anderen en zeg Kolom verwijderen, en sleep vervolgens de mapkop naar de linkerkant van het bestand, zodat het samenvoegen werkt. Selecteer beide kolommen en kies vervolgens op het tabblad Kolom toevoegen Kolommen samenvoegen, typ een nieuwe naam, klik op OK en klik met de rechtermuisknop op die nieuwe kolom en Verwijder de andere kolommen, Start, Sluiten en Laden, en het zal je je lijst geven. Het verbazingwekkende is dat je de query later kunt vernieuwen door dit pictogram Vernieuwen te gebruiken in de Query's en verbindingen.u kunt de query later vernieuwen door dit pictogram Vernieuwen te gebruiken in de Query's en verbindingen.u kunt de query later vernieuwen door dit pictogram Vernieuwen te gebruiken in de Query's en verbindingen.
Nou hey, ik wil je bedanken voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
Om meer te weten te komen over Power Query, raad ik dit boek van Ken Puls en Miguel Escobar aan.
M is voor (DATA) MONKEY »
