Список усіх файлів у папці в 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, але є чудовий інструмент, який може це вирішити. І моє перше запитання було: "Ну яка у вас версія Excel?" Тому що якщо у вас трапляється Excel 2016, вони отримають цю дивовижну нову функціональність під назвою «Отримати та перетворити дані»! Зараз, в Office 365, це зліва, я думаю, що в оригінальній версії Excel 2016 він був у третій групі, добре, так що просто шукайте Get & Transform. Якщо ви працюєте в програмі Excel 2010 або Excel 2013 для Windows, ви можете завантажити Power Query, і у вас з’явиться власна вкладка з цією інформацією.

Тепер давайте швидко поглянемо на цю папку, добре, я щойно створив тут підроблену папку з деякими підробленими даними. Ви побачите, що в цій папці є файли Excel і PDF-файли, я хочу лише PDF-файли, а також є деякі підпапки, я не хочу ці PDF-файли, я хочу лише PDF-файли в головній папці. Отже C: Budgets, я збираюся скопіювати це, а потім повернусь сюди в Excel, і ми скажемо, що хочемо отримати дані, із файлу, із цілої папки, як це, а потім введіть туди шлях до папки або скористайтеся будь-якою кнопкою Огляд. І коли ви отримаєте цей перший екран, ви точно хочете редагувати, і тепер ми перебуваємо в редакторі Power Query.

Добре, тому моя мета тут, мені не потрібен Вміст, тому я збираюся клацнути правою кнопкою миші та сказати Видалити цей стовпець. Там мій список файлів, я хочу лише PDF-файли, тому, якщо є щось, що не є PDF-файлом, я хочу лише PDF-файли, натисніть OK, я бачу, що це лише PDF-файли. А, а потім загляньте сюди, бачите, тепер вони тягнуть речі лише з оригінальної папки та з папки Garbage, тому я відкриваю це і знімаю галочку з усього, що не є оригінальною папкою. Гаразд, отже, зараз у мене є приємний маленький список, і цей список - це, ви знаєте, 9 записів, але в реальному житті я впевнений, що їх, мабуть, десятки чи сотні. Добре, мені зараз не потрібні інші речі, тому я можу клацнути правою кнопкою миші та видалити ці стовпці.

Добре, тепер мені справді потрібно саме це - мені потрібен шлях до папки та ім’я файлу разом. Гаразд, отже, я збираюся взяти FolderPath і перетягнути його ліворуч і опустити туди, а потім магічний крок сюди: у звичайному Excel нам потрібно було б об’єднати це, але те, що я збираюся зробити, це, я Збираю стовпці. Отже, я збираюся додати стовпець і вибрати «Об’єднати стовпці», розділювач буде «Ні», новий стовпець буде називатися «Ім'я файлу», і натисніть «Гаразд», добре, отже, у нас є назва папки, коса риса та ім’я файлу , щось схоже на те. Тепер це насправді єдине, що нам потрібно, тому я збираюся клацнути правою кнопкою миші та сказати Видалити інші стовпці, а потім нарешті «Домашня сторінка», «Закрити та завантажити», і ми отримаємо абсолютно новий аркуш із нашими даними. Гаразд, він подається у вигляді таблиці, і тому я просто скопіюю це, Ctrl + C,а потім перейдіть сюди туди, де я справді хотів отримати дані тут, і Вставте спеціальні значення, натисніть кнопку ОК. Тепер це вже не таблиця, це просто мої чисті дані, ось такі, і ось, ось справді прекрасна річ про це.

Отже, ми налаштували це один раз, і нічого собі, це зайняло менше 3 хвилин, щоб налаштувати, але давайте повернемось до цієї папки Бюджети, і давайте перемістимо деякі речі. Давайте візьмемо один із цих записів сміття, і ми скопіюємо його в основну папку, Ctrl + V, добре, отже, тепер тут більше речей, там 10 файлів PDF замість 9. Якщо я прийду сюди, де запит, і праворуч на екрані, у Запитах та підключеннях, можливо, вам доведеться зробити це ширшим, я вже зробив свій ширшим, ви побачите наші бюджети із завантаженими 9 рядками. Я збираюся натиснути маленький піктограму Оновити тут, і дуже швидко бюджети тепер завантажують 10 рядків. Отже, це збирання нових записів, ви встановили це один раз, і тоді ви зможете просто оновитись, щоб отримати нові дані.

Ну, це суть у подкасті, де я зазвичай прошу вас купити мою книгу, але натомість сьогодні я попрошу вас придбати цю книгу "М - для (ДАНИХ) МАЙМУН" Кена Пулса та Мігеля Ескобара. ДИВУВАЛЬНА книга, яка навчить вас усім про використання Power Query або отримання та трансформації даних - усе, що я дізнався про Power Query, дізнався з цієї книги.

Добре, підсумовуємо підсумок цього епізоду: Наша мета - імпортувати список назв файлів у Excel. Якщо у вас є Excel 2016, ви можете використовувати нові Отримати та перетворити дані. Якщо у вас немає 2016 року, але у вас реальна версія Excel під управлінням Windows, тоді ви можете завантажити безкоштовну надбудову Power Query, призначену для Excel 2010 або Excel 2013. Вона не працюватиме на вашому телефоні Android, або ваш iPad, або iPhone, або Surface RT, або ваш Mac, так, це лише для версій Excel для Windows. Отже, ми почнемо з порожнього аркуша, Дані, Отримати дані, З файлу, З папки, введіть ім’я папки або Огляд, обов’язково натисніть Редагувати замість Завантажити. А потім у фільтрі фільтруйте за типом файлу, щоб позбутися від усього, що не є PDF-файлом, фільтруйте за назвою папки, щоб позбутися всіх сміттєвих папок. Зберігайте лише ім’я та папку файлу,так що клацніть правою кнопкою миші на цих інших і скажіть Видалити стовпець, а потім перетягніть заголовок папки ліворуч від файлу, що дозволить злиття працювати. Виділіть обидва стовпці, потім на вкладці Додати стовпець виберіть Об’єднати стовпці, введіть нове ім’я, натисніть кнопку ОК і клацніть правою кнопкою миші на цьому новому стовпці та Видаліть інші стовпці, Домашня сторінка, Закрити та Завантажити, і він надасть вам ваш список. Дивовижна частина, ви можете оновити запит пізніше, скориставшись цим значком Refresh у Запити та підключення.Ви можете оновити запит пізніше, скориставшись цим значком Оновити в розділі Запити та підключення.Ви можете оновити запит пізніше, скориставшись цим значком Оновити в розділі Запити та підключення.

Ну, привіт, я хочу подякувати вам за заїзд, ми побачимось наступного разу для чергової трансляції від!