Избройте всички файлове в папка в Excel с помощта на Power Query - Съвети на Excel

Съдържание

Гледам видео

Днешният въпрос: Марсия трябва да получи списък с всички PDF файлове с данъчна фактура от папка в електронна таблица на Excel. Това е лесно да се направи, ако използвате Excel 2016 на компютър с Windows, като използвате новите инструменти за получаване и трансформиране на данни.

Ако имате Excel 2010 за Windows или Excel 2013 за Windows, ще трябва да изтеглите безплатната добавка Power Query от Microsoft. Отидете до любимата си търсачка и напишете „Изтегляне на Power Query“, за да намерите текущата връзка. (Microsoft обича да променя URL адресите на всяко тримесечие и моят страхотен човек в мрежата мрази, когато връзките ни са остарели, така че дори няма да се опитвам да поставя връзка тук.)

Видеото по-долу ще ви покаже пълните стъпки, но ето и общия преглед:

Започнете от празен работен лист
Данни, Получаване на данни, От файл, От папка
Прегледайте папката
Щракнете върху Редактиране вместо Зареждане
Отворете падащото меню на филтъра за типа файл и премахнете всичко, което не е PDF
Отворете филтъра в папката и премахнете всички подпапки за боклук
Запазете само Име на файл и папка - щракнете с десния бутон върху заглавието на всяка колона и изберете Премахване
Плъзнете заглавието на папката вляво от заглавието на файла. Това позволява сливането да работи.
Изберете и двете колони. Кликнете върху едно заглавие. Shift + Щракнете върху другото заглавие.
Изберете Добавяне на колона, Обединяване на колони, Въведете ново име за колоната. Щракнете върху OK.
Щракнете с десния бутон върху заглавието на новата колона и премахнете други колони
Начало, затваряне и зареждане
Удивителната част … можете да опресните заявката по-късно. Щракнете върху иконата за опресняване в панела Заявки и връзки.

Въпреки че Power Query е невероятно мощен, това е една от любимите ми задачи. Често искам да стартирам VBA макрос за всеки файл в папка. Получаването на списък с всички PDF файлове в папка е добра отправна точка.

Гледам видео

Видео транскрипция

Научете Excel от подкаст, епизод 2181 - Списък на папките в Excel!

Хей, добре дошъл в мрежата, аз съм Бил Джелен. Днешният въпрос, някой има списък с PDF файлове с данъчна фактура в папка и той трябва да получи списъка с всички тези имена на файлове в Excel. Добре, и един от начините да направите това е да ги въведете всички или да копирате и поставите от Windows Explorer, но има чудесен инструмент, който може да реши това. И първият ми въпрос беше „Е, каква версия на Excel имате?“ Защото ако случайно имате Excel 2016, те ще имат тази невероятна нова функционалност, наречена „Получаване и трансформиране на данни“! Сега в Office 365 е отляво, мисля, че в оригиналната версия на Excel 2016 беше в третата група, добре, така че просто потърсете Get & Transform. Ако сте в Excel 2010 или Excel 2013 за Windows, можете да изтеглите Power Query и ще имате свой собствен раздел с точно тези неща.

Сега нека да разгледаме набързо тази папка, добре, току-що създадох фалшива папка тук с някои фалшиви данни. Ще видите, че в тази папка има PDF файлове и PDF файлове, искам само PDF файловете, а има и някои подпапки, не искам тези PDF файлове, искам само PDF файловете в основната папка. Така че C: Бюджети, ще копирам това и след това ще се върна тук в Excel и ще кажем, че искаме да получаваме данни, от файл, от цяла папка, като тази, и след това въведете пътя до папката там или използвайте бутона Преглед, или единия. И когато получите този първи екран, определено искате да редактирате и сега сме в редактора на Power Query.

Добре, така че целта ми тук нямам нужда от Съдържанието, затова щраквам с десния бутон и казвам Премахване на тази колона. Там е моят списък с файлове, искам само PDF файлове, така че ако има нещо, което не е PDF, искам само PDF файлове, щракнете върху OK, виждам, че това са само PDF файловете. А, а след това погледнете тук, вижте, сега те изтеглят неща само от оригиналната папка и от папката Garbage, така че отварям това и премахвам отметката от всичко, което не е оригиналната папка. Добре, така че сега имам хубав малък списък и този списък е, знаете какво, 9 записа, но в реалния живот се обзалагам, че те вероятно имат, знаете ли, десетки или стотици от тях. Добре, нямам нужда от други неща сега, така че мога да щракнете с десния бутон и да премахнете тези колони.

Добре, сега това, от което наистина се нуждая тук, е, че се нуждая от пътя на папката и името на файла заедно. Добре, така че ще взема FolderPath и ще го плъзна наляво и ще го пусна там, а след това магическата стъпка тук: В обикновения Excel ще трябва да направим конкатенация за това, но това, което ще направя, е, че ще обединя колони. Така че ще добавя колона и ще избера Обединяване на колони, разделителят ще бъде None, новата колона ще се нарича FileName и щракнете върху OK, така че имаме името на папката, наклонената черта и името на файла , така. Сега това всъщност е единственото нещо, от което се нуждаем, така че щраквам с десния бутон и казвам Премахване на другите колони и след това накрая Начало, затваряне и зареждане и получаваме чисто нов лист с нашите данни. Добре сега, той идва като таблица и затова просто ще копирам това, Ctrl + C,и след това дойдете тук, където наистина исках данните тук, и поставете специални стойности, щракнете върху OK. Сега вече не е таблица, това са просто моите чисти данни, такива, а сега, ето наистина красивото нещо за това.

Така че настроихме това веднъж и уау, за настройването на които отне по-малко от 3 минути, но нека се върнем към тази папка Бюджети и да преместим някои неща. Нека вземем един от тези записи за боклук и ще го копираме в основната папка, Ctrl + V, така че сега има повече неща тук, има 10 PDF файла вместо 9. Ако дойда тук, където е заявката, и повече от дясната страна на екрана, в Заявки и връзки, може да се наложи да направите това по-широко, аз вече разширих своите, ще видите нашите бюджети с заредени 9 реда. Ще щракна малката икона за опресняване тук и много бързо бюджетите вече са заредени 10 реда. Така че това е събирането на новите записи, вие го настроите веднъж и след това ще можете просто да се опресните, за да получите новите данни.

Е, това е точката в подкаста, където обикновено ви моля да купите моята книга, но вместо това днес ще ви помоля да купите тази книга „M is for (DATA) MONKEY“ от Ken Puls и Miguel Escobar. УДИВИТЕЛНА книга, която ще ви научи как да използвате Power Query или Get & Transform Data - всичко, което научих за Power Query, научих от тази книга.

Добре, завършете от този епизод: Нашата цел е как да импортираме списък с имена на файлове в Excel, ако имате Excel 2016, можете да използвате новия Get & Transform Data. Ако нямате 2016 г., но имате реална версия на Excel, работеща под Windows, можете да изтеглите безплатната добавка Power Query, която е за Excel 2010 или Excel 2013. Няма да работи на вашия телефон с Android или вашия iPad или вашия iPhone, или Surface RT или вашия Mac, нали, това е само за Windows версии на Excel. Така че ще започнем от празен работен лист, Данни, Получаване на данни, От файл, От папка, въведете името на папката или Преглед, не забравяйте да щракнете върху Редактиране вместо Зареждане. И след това във филтъра, филтрирайте типа файл, за да се отървете от всичко, което не е PDF, филтрирайте името на папката, за да се отървете от всички подпапки за боклук. Запазете само името и папката на файла,така че щракнете с десния бутон върху тези други и кажете Премахване на колона, след което плъзнете заглавието на папката вляво от файла, което позволява обединяването да работи. Изберете и двете колони, след това в раздела Добавяне на колона изберете Обединяване на колони, въведете ново име, щракнете върху OK и щракнете с десния бутон върху тази нова колона и Премахнете другите колони, Начало, Затваряне и Зареждане и ще ви даде списъка. Невероятната част, можете да опресните заявката по-късно, като използвате тази икона за опресняване в Заявки и връзки.можете да опресните заявката по-късно, като използвате тази икона за опресняване в Заявки и връзки.можете да опресните заявката по-късно, като използвате тази икона за опресняване в Заявки и връзки.

Е, хей, искам да ви благодаря, че отбихте, ще се видим следващия път за поредното излъчване от!