Buat daftar semua File dalam Folder di Excel Menggunakan Power Query - Tips Excel

Daftar Isi

Menonton video

Pertanyaan hari ini: Marcia perlu mendapatkan daftar semua file PDF faktur pajak dari folder ke dalam spreadsheet Excel. Ini mudah dilakukan jika Anda menggunakan Excel 2016 di PC Windows menggunakan alat Get & Transform Data yang baru.

Jika Anda memiliki Excel 2010 untuk Windows atau Excel 2013 untuk Windows, Anda harus mengunduh Add-in Power Query gratis dari Microsoft. Buka mesin pencari favorit Anda dan ketik "Unduh Power Query" untuk menemukan tautan saat ini. (Microsoft suka mengubah URL setiap kuartal dan orang web saya yang luar biasa benci jika tautan kami kedaluwarsa, jadi saya bahkan tidak akan mencoba memasang tautan di sini.)

Video di bawah ini akan menunjukkan kepada Anda langkah-langkah lengkapnya, tetapi berikut adalah ringkasannya:

Mulai dari lembar kerja kosong
Data, Dapatkan Data, Dari File, Dari Folder
Jelajahi folder tersebut
Klik Edit, bukan Muat
Buka dropdown filter pada jenis file dan hapus apa pun yang bukan PDF
Buka filter di folder dan hapus semua subfolder sampah
Pertahankan hanya Nama File dan Folder - klik kanan setiap judul kolom dan pilih Hapus
Tarik Folder menuju ke kiri dari judul File. Ini memungkinkan penggabungan berfungsi.
Pilih kedua kolom. Klik satu judul. Shift + Klik pada tajuk lainnya.
Pilih Tambahkan Kolom, Gabungkan Kolom, Ketikkan Nama baru untuk Kolom. Klik OK.
Klik kanan tajuk untuk kolom baru dan Hapus Kolom Lain
Beranda, Tutup & Muat
Bagian yang menakjubkan… Anda dapat menyegarkan kueri nanti. Klik ikon Refresh di panel Queries & Connections.

Meskipun Power Query luar biasa kuat, ini adalah salah satu tugas favorit saya. Saya sering ingin menjalankan Makro VBA pada setiap file dalam folder. Mendapatkan daftar semua PDF dalam folder adalah titik awal yang baik.

Menonton video

Transkrip Video

Pelajari Excel dari podcast, episode 2181 - Buat Daftar File Folder di Excel!

Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini, seseorang memiliki daftar file PDF faktur pajak dalam folder, dan mereka perlu mendapatkan daftar semua nama file tersebut di Excel. Baiklah, dan salah satu cara untuk melakukannya adalah dengan mengetik semuanya, atau salin dan tempel dari Windows Explorer, tetapi ada alat hebat yang dapat menyelesaikan ini. Dan pertanyaan pertama saya adalah "Apa versi Excel yang Anda miliki?" Karena jika Anda memiliki Excel 2016, mereka akan memiliki fungsi baru yang luar biasa ini yang disebut "Dapatkan & Transformasi Data"! Sekarang, di Office 365, ada di sisi kiri, saya pikir di versi asli Excel 2016 itu ada di grup ketiga, oke, jadi cari saja Dapatkan & Transformasikan. Jika Anda menggunakan Excel 2010 atau Excel 2013 untuk Windows, Anda dapat mengunduh Power Query dan Anda akan memiliki tab Anda sendiri dengan hal-hal yang sama persis.

Sekarang mari kita lihat sekilas folder ini, baiklah, saya baru saja membuat folder palsu di sini dengan beberapa data palsu. Anda akan melihat bahwa ada file Excel di folder ini dan PDF, saya hanya ingin PDF, dan ada juga beberapa subfolder, saya tidak ingin PDF ini, saya hanya ingin PDF di folder utama. Jadi C: Budgets, saya akan menyalinnya, lalu kembali ke Excel, dan kami akan mengatakan bahwa kami ingin Mendapatkan Data, Dari File, Dari Seluruh Folder, seperti ini, lalu ketikkan jalur folder di sana, atau gunakan tombol Jelajah, salah satunya. Dan saat Anda mendapatkan layar pertama ini, Anda pasti ingin Mengedit, dan sekarang kami berada di editor Power Query.

Baiklah, jadi tujuan saya di sini, saya tidak membutuhkan Konten, jadi saya akan mengklik kanan dan mengatakan Hapus kolom itu. Ada daftar file saya, saya hanya ingin file PDF, jadi jika ada sesuatu yang bukan PDF, saya hanya ingin PDF, klik OK, saya dapat melihat itu hanya file PDF. Ah, lalu lihat ke sini, lihat, sekarang mereka menarik hal-hal hanya dari folder aslinya, dan dari folder Sampah, jadi saya buka ini, dan saya hapus centang semua yang bukan folder aslinya. Baiklah, jadi sekarang saya punya daftar kecil yang bagus, dan daftar ini adalah, Anda tahu apa, 9 rekaman, tapi dalam kehidupan nyata saya yakin mereka mungkin punya, Anda tahu, lusinan atau ratusan ini. Oke, saya tidak butuh barang lain sekarang, jadi saya bisa klik kanan dan Hapus kolom itu.

Baiklah, sekarang yang saya butuhkan di sini adalah saya membutuhkan jalur folder dan nama file bersama. Baiklah, jadi saya akan mengambil FolderPath dan menyeretnya ke kiri dan meletakkannya di sana, lalu langkah ajaib di sini: Di Excel biasa kita harus melakukan penggabungan untuk ini, tapi yang akan saya lakukan adalah, saya saya akan menggabungkan kolom. Jadi saya akan menambahkan Kolom, dan memilih Gabungkan Kolom, Pemisah akan menjadi Tidak Ada, kolom Baru akan disebut Nama File, dan klik OK, baiklah, jadi kita memiliki nama folder, garis miring, dan nama file , seperti itu. Sekarang, sebenarnya itulah satu-satunya hal yang kita butuhkan, jadi saya akan mengklik kanan dan mengatakan Hapus Kolom Lain, lalu akhirnya Beranda, Tutup, dan Muat, dan kami mendapatkan lembar baru dengan data kami. Baiklah sekarang, itu datang sebagai tabel, jadi saya akan menyalin ini, Ctrl + C,dan kemudian datang ke sini ke tempat saya benar-benar menginginkan datanya di sini, dan Tempel Nilai Khusus, klik OK. Sekarang bukan tabel lagi, ini hanya data murni saya, seperti itu, dan sekarang, inilah hal yang sangat indah tentang ini.

Jadi kami menyiapkan ini sekali, dan wow, butuh waktu kurang dari 3 menit untuk menyiapkannya, tapi mari kembali ke folder Anggaran itu, dan mari kita pindahkan beberapa hal. Mari kita ambil salah satu catatan Sampah ini, dan kita akan menyalinnya ke folder utama, Ctrl + V, baiklah jadi sekarang ada lebih banyak barang di sini, ada 10 file PDF daripada 9. Jika saya datang ke sini ke tempat kueri, dan di sisi kanan layar, di Queries & Connections, Anda mungkin harus membuatnya lebih lebar, saya sudah membuat milik saya lebih lebar, Anda akan melihat anggaran kami dengan 9 baris dimuat. Saya akan mengklik ikon Refresh kecil di sini, dan dengan sangat cepat Anggaran sekarang memiliki 10 baris yang dimuat. Jadi itu mengambil catatan baru, Anda mengaturnya sekali, dan kemudian Anda akan bisa menyegarkan untuk mendapatkan data baru.

Ini adalah poin di podcast di mana saya biasanya meminta Anda untuk membeli buku saya, tetapi hari ini saya akan meminta Anda untuk membeli buku ini "M is for (DATA) MONKEY" oleh Ken Puls dan Miguel Escobar. Buku LUAR BIASA yang akan mengajari Anda semua tentang menggunakan Power Query atau Dapatkan & Transformasi Data, semua yang saya pelajari tentang Power Query yang saya pelajari dari buku ini.

Oke, akhiri episode ini: Tujuan kami adalah cara mengimpor daftar nama file ke Excel, jika Anda memiliki Excel 2016, Anda dapat menggunakan Get & Transform Data yang baru. Jika Anda tidak memiliki 2016, tetapi Anda memiliki versi nyata Excel yang berjalan di bawah Windows, maka Anda dapat mengunduh add-in Power Query gratis untuk Excel 2010 atau Excel 2013. Ini tidak akan berfungsi di ponsel Android Anda, atau iPad Anda, atau iPhone Anda, atau Surface RT, atau Mac Anda, benar, ini hanya untuk Excel versi Windows. Jadi kita akan mulai dari lembar kerja kosong, Data, Dapatkan Data, Dari File, Dari Folder, masukkan nama folder atau Jelajahi, pastikan untuk mengklik Edit, bukan Muat. Dan kemudian pada Filter, filter pada jenis file untuk menyingkirkan apa pun yang bukan PDF, filter pada nama folder untuk menyingkirkan semua subfolder sampah. Simpan hanya nama file dan foldernya,jadi klik kanan yang lain dan katakan Hapus kolom, lalu seret folder menuju ke kiri file, yang memungkinkan penggabungan berfungsi. Pilih kedua kolom, lalu pada tab Tambahkan Kolom pilih Gabungkan Kolom, ketik nama baru, klik OK, dan klik kanan pada kolom baru itu dan Hapus Kolom Lain, Beranda, Tutup, dan Muat, dan itu akan memberi Anda daftar. Bagian luar biasa, Anda dapat menyegarkan kueri nanti dengan menggunakan ikon Segarkan ini di Queries & Connections.Anda dapat me-refresh kueri nanti dengan menggunakan ikon Refresh ini di Queries & Connections.Anda dapat me-refresh kueri nanti dengan menggunakan ikon Refresh ini di Queries & Connections.

Baiklah, saya ingin mengucapkan terima kasih telah mampir, sampai jumpa di lain waktu untuk netcast lain dari!