Listează toate fișierele dintr-un folder în Excel folosind Power Query - Sfaturi Excel

Cuprins

Urmăriți videoclipul

Întrebarea de astăzi: Marcia trebuie să obțină o listă a tuturor fișierelor PDF cu facturi fiscale dintr-un dosar într-o foaie de calcul Excel. Acest lucru este ușor de făcut dacă utilizați Excel 2016 pe un computer Windows utilizând noile instrumente Get & Transform Data.

Dacă aveți Excel 2010 pentru Windows sau Excel 2013 pentru Windows, va trebui să descărcați suplimentul Power Query gratuit de la Microsoft. Accesați motorul dvs. de căutare preferat și tastați „Descărcați Power Query” pentru a găsi linkul curent. (Microsoft adoră să schimbe adresele URL în fiecare trimestru, iar minunatul meu tip de web urăște când linkurile noastre sunt depășite, așa că nici nu voi încerca să pun un link aici.)

Videoclipul de mai jos vă va arăta pașii completi, dar iată prezentarea generală:

Începeți de la o foaie de lucru goală
Date, Obțineți date, din fișier, din folder
Navigați la dosar
Faceți clic pe Editare în loc de Încărcare
Deschideți meniul drop-down de filtrare pentru tipul de fișier și eliminați orice nu este un PDF
Deschideți filtrul din folder și eliminați orice subfoldere de gunoi
Păstrați doar numele fișierului și folderul - faceți clic dreapta pe fiecare antet de coloană și alegeți Eliminare
Trageți antetul Folderului la stânga antetului Fișier Acest lucru permite îmbinarea să funcționeze.
Selectați ambele coloane. Faceți clic pe un singur titlu. Shift + Faceți clic pe celălalt titlu.
Alegeți Adăugare coloană, Îmbinare coloane, Tastați un nume nou pentru coloană. Faceți clic pe OK.
Faceți clic dreapta pe antetul pentru noua coloană și Eliminați alte coloane
Acasă, Închidere și încărcare
Partea uimitoare … puteți reîmprospăta interogarea mai târziu. Faceți clic pe pictograma Reîmprospătare din panoul Interogări și conexiuni.

În timp ce Power Query este uimitor de puternic, aceasta este una dintre sarcinile mele preferate. Vreau frecvent să rulez un Macro VBA pe fiecare fișier dintr-un folder. Obținerea unei liste cu toate fișierele PDF dintr-un folder este un bun punct de plecare.

Urmăriți videoclipul

Transcriere video

Aflați Excel din podcast, episodul 2181 - Listați fișierele de foldere în Excel!

Hei, bine ai venit din nou pe netcast, eu sunt Bill Jelen. Întrebarea de astăzi, cineva are o listă de fișiere PDF cu facturi fiscale într-un dosar și trebuie să obțină lista tuturor acelor nume de fișiere în Excel. Bine, și o modalitate de a face acest lucru este să le tastați pe toate sau să copiați și lipiți din Windows Explorer, dar există un instrument excelent care poate rezolva acest lucru. Și prima mea întrebare a fost „Ei bine, ce versiune de Excel aveți?” Pentru că, dacă se întâmplă să aveți Excel 2016, vor avea această nouă funcționalitate uimitoare numită „Obțineți și transformați date”! Acum, în Office 365, este pe partea stângă, cred că în versiunea originală a Excel 2016 se afla în al treilea grup, în regulă, așa că trebuie doar să căutați Get & Transform. Dacă sunteți în Excel 2010 sau Excel 2013 pentru Windows, puteți descărca Power Query și veți avea propria filă cu aceste lucruri exacte.

Acum să aruncăm o privire rapidă la acest folder, bine, tocmai am creat un folder fals aici, cu niște date false. Veți vedea că există fișiere Excel în acest folder și PDF-uri, vreau doar PDF-urile și există, de asemenea, unele subfoldere, nu vreau aceste PDF-uri, vreau doar PDF-urile din folderul principal. Deci, C: Budgets, voi copia asta, apoi mă voi întoarce aici la Excel și vom spune că vrem să obținem date, dintr-un fișier, dintr-un folder întreg, ca acesta, și apoi tastați calea folderului acolo sau utilizați butonul Răsfoire, fie unul. Și când veți obține acest prim ecran, cu siguranță doriți să editați, iar acum suntem în editorul Power Query.

Bine, deci obiectivul meu aici, nu am nevoie de conținut, așa că voi face clic dreapta și voi spune Elimină acea coloană. Există lista mea de fișiere, vreau doar fișiere PDF, deci dacă există ceva care nu este un PDF, vreau doar fișiere PDF, faceți clic pe OK, pot vedea că sunt doar fișierele PDF. Ah, apoi uitați-vă aici, vedeți, acum extrag lucrurile doar din folderul original și din folderul de gunoi, așa că deschid acest lucru și debifez tot ceea ce nu este folderul original. Bine, așa că acum am o mică listă drăguță, iar această listă este, știi ce, 9 înregistrări, dar în viața reală pariez că probabil au, știi, zeci sau sute dintre acestea. În regulă, nu am nevoie de alte lucruri acum, așa că pot face clic dreapta și elimina acele coloane.

Bine, acum ceea ce am cu adevărat nevoie aici este că am nevoie de calea folderului și numele de fișier împreună. Bine, așa că voi lua FolderPath și îl voi trage la stânga și îl voi lăsa acolo, apoi pasul magic aici: în Excel obișnuit ar trebui să facem concatenare pentru asta, dar ceea ce voi face este, eu Voi îmbina coloane. Deci, voi adăuga o coloană și voi alege Îmbinarea coloanelor, separatorul va fi Niciuna, noua coloană va fi numită FileName și faceți clic pe OK, bine, deci avem numele folderului, bară și numele fișierului , ca asta. Acum, acesta este de fapt singurul lucru de care avem nevoie, așa că voi face clic dreapta și voi spune Elimină celelalte coloane, apoi în cele din urmă Acasă, Închidere și Încărcare și vom obține o foaie nouă cu datele noastre. Bine acum, vine ca un tabel, și așa că voi copia doar acest lucru, Ctrl + C,și apoi veniți aici unde doream cu adevărat datele aici și apoi lipiți valori speciale, faceți clic pe OK. Acum nu mai este un tabel, ci doar datele mele pure, așa și acum, iată ce este cu adevărat frumos despre asta.

Așa că am configurat o dată, și wow, a durat mai puțin de 3 minute pentru a configura, dar să revenim la folderul Bugetelor și să mutăm câteva lucruri. Să luăm una dintre aceste înregistrări de gunoi și o vom copia în folderul principal, Ctrl + V, așa că acum sunt mai multe lucruri aici, există 10 fișiere PDF în loc de 9. Dacă vin aici unde este interogarea, și în partea dreaptă a ecranului, în interogări și conexiuni, este posibil să trebuiască să lărgiți acest lucru, eu l-am lărgit deja pe al meu, veți vedea bugetele noastre cu 9 rânduri încărcate. Voi face clic aici pe mica pictogramă Reîmprospătare și foarte repede Bugetele au acum 10 rânduri încărcate. Deci, preluați noile înregistrări, le configurați o dată și apoi veți putea actualiza doar pentru a obține noile date.

Ei bine, acesta este punctul din podcast în care, de obicei, vă rog să cumpărați cartea mea, dar în schimb astăzi vă voi cere să cumpărați această carte „M is for (DATA) MONKEY” de Ken Puls și Miguel Escobar. O carte AMAZING care vă va învăța totul despre utilizarea Power Query sau a datelor Get & Transform Data, tot ce am învățat despre Power Query am învățat din această carte.

OK, încheieți acest episod: Scopul nostru este cum să importăm o listă de nume de fișiere în Excel, dacă aveți Excel 2016, puteți utiliza noul Get & Transform Data. Dacă nu aveți 2016, dar aveți o versiune reală de Excel care rulează sub Windows, puteți descărca suplimentul Power Query gratuit pentru Excel 2010 sau Excel 2013. Nu va funcționa pe telefonul dvs. Android sau iPad-ul, iPhone-ul sau Surface RT sau Mac-ul dvs., nu, este doar pentru versiunile Windows ale Excel-ului. Deci, vom începe de la foaia de lucru goală, Date, Obțineți date, Din fișier, Din folder, introduceți numele folderului sau Răsfoiți, asigurați-vă că faceți clic pe Editare în loc de Încărcare. Și apoi pe Filtru, filtrați tipul de fișier pentru a scăpa de orice nu este un PDF, filtrați numele folderului pentru a scăpa de toate subfolderele de gunoi. Păstrați doar numele fișierului și folderul,deci faceți clic dreapta pe acele altele și spuneți Eliminați coloana, apoi trageți antetul folderului în stânga fișierului, care permite îmbinarea să funcționeze. Selectați ambele coloane, apoi în fila Adăugare coloană alegeți Îmbinare coloane, tastați un nume nou, faceți clic pe OK și faceți clic dreapta pe acea nouă coloană și Eliminați celelalte coloane, Acasă, Închideți și Încărcați și vă va oferi lista dvs. Partea uimitoare: puteți reîmprospăta interogarea ulterior folosind această pictogramă Reîmprospătare din Interogări și conexiuni.puteți reîmprospăta interogarea ulterior utilizând această pictogramă Reîmprospătare din Interogări și conexiuni.puteți reîmprospăta interogarea ulterior utilizând această pictogramă Reîmprospătare din Interogări și conexiuni.

Ei bine, vreau să-ți mulțumesc că ai trecut pe aici, ne vedem data viitoare pentru un alt netcast de la!