Liệt kê tất cả các tệp trong một thư mục trong Excel bằng Power Query - Mẹo Excel

Mục lục

Xem video

Câu hỏi hôm nay: Marcia cần lấy danh sách tất cả các tệp PDF hóa đơn thuế từ một thư mục trong bảng tính Excel. Điều này rất dễ thực hiện nếu bạn đang sử dụng Excel 2016 trên PC chạy Windows bằng công cụ Lấy & Chuyển đổi Dữ liệu mới.

Nếu bạn có Excel 2010 cho Windows hoặc Excel 2013 cho Windows, bạn sẽ phải tải xuống Phần bổ trợ Power Query miễn phí từ Microsoft. Truy cập công cụ tìm kiếm yêu thích của bạn và nhập “Tải xuống Power Query” để tìm liên kết hiện tại. (Microsoft rất thích thay đổi URL mỗi quý và anh chàng web tuyệt vời của tôi ghét khi các liên kết của chúng tôi bị lỗi thời, vì vậy tôi thậm chí sẽ không thử đặt liên kết ở đây.)

Video dưới đây sẽ cho bạn thấy các bước đầy đủ, nhưng đây là tổng quan:

Bắt đầu từ một trang tính trống
Dữ liệu, Lấy dữ liệu, Từ tệp, Từ thư mục
Duyệt đến thư mục
Nhấp vào Chỉnh sửa thay vì Tải
Mở menu thả xuống của bộ lọc về loại tệp và xóa bất kỳ thứ gì không phải là PDF
Mở bộ lọc trên thư mục và loại bỏ mọi thư mục con rác
Chỉ giữ lại Tên tệp và Thư mục - bấm chuột phải vào từng tiêu đề cột và chọn Loại bỏ
Kéo tiêu đề Thư mục sang bên trái tiêu đề Tệp. Điều này cho phép hợp nhất hoạt động.
Chọn cả hai cột. Nhấp vào một tiêu đề. Shift + Nhấp vào tiêu đề khác.
Chọn Thêm cột, Hợp nhất cột, Nhập tên mới cho cột. Bấm OK.
Nhấp chuột phải vào tiêu đề cho cột mới và Xóa các Cột Khác
Trang chủ, Đóng & Tải
Phần tuyệt vời… bạn có thể làm mới truy vấn sau. Nhấp vào Biểu tượng Làm mới trong bảng Truy vấn & Kết nối.

Mặc dù Power Query có sức mạnh đáng kinh ngạc nhưng đây là một trong những tác vụ yêu thích của tôi. Tôi thường muốn chạy Macro VBA trên mỗi tệp trong một thư mục. Lấy danh sách tất cả các tệp PDF trong một thư mục là một điểm khởi đầu tốt.

Xem video

Bản ghi video

Học Excel từ podcast, tập 2181 - Liệt kê tệp thư mục trong Excel!

Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Câu hỏi hôm nay, ai đó có một danh sách các tệp PDF hóa đơn thuế trong một thư mục và họ cần lấy danh sách tất cả các tên tệp đó trong Excel. Được rồi, và một cách để làm điều đó là nhập tất cả chúng hoặc sao chép và dán từ Windows Explorer, nhưng có một công cụ tuyệt vời có thể giải quyết điều này. Và câu hỏi đầu tiên của tôi là "Bạn có phiên bản Excel nào?" Bởi vì nếu bạn tình cờ có Excel 2016, chúng sẽ có chức năng mới tuyệt vời này được gọi là “Lấy & chuyển đổi dữ liệu”! Bây giờ, trong Office 365, nó nằm ở phía bên trái, tôi nghĩ trong phiên bản gốc của Excel 2016, nó nằm trong nhóm thứ ba, vì vậy chỉ cần tìm Get & Transform. Nếu đang sử dụng Excel 2010 hoặc Excel 2013 cho Windows, bạn có thể tải xuống Power Query và bạn sẽ có tab của riêng mình với nội dung chính xác này.

Bây giờ chúng ta hãy xem nhanh thư mục này, được rồi, tôi vừa tạo một thư mục giả ở đây với một số dữ liệu giả. Bạn sẽ thấy rằng có các tệp Excel trong thư mục này và các tệp PDF, tôi chỉ muốn các tệp PDF và cũng có một số thư mục con, tôi không muốn các tệp PDF này, tôi chỉ muốn các tệp PDF trong thư mục chính. Vì vậy, C: Budgets, tôi sẽ sao chép nó, và sau đó quay lại đây vào Excel, và chúng tôi sẽ nói rằng chúng tôi muốn Lấy dữ liệu, Từ tệp, Từ toàn bộ thư mục, như thế này, và sau đó nhập đường dẫn thư mục vào đó hoặc sử dụng nút Duyệt qua. Và khi bạn nhận được màn hình đầu tiên này, bạn chắc chắn muốn Chỉnh sửa và bây giờ chúng tôi đang ở trong trình chỉnh sửa Power Query.

Được rồi, vì vậy mục tiêu của tôi ở đây, tôi không cần Nội dung, vì vậy tôi sẽ nhấp chuột phải và nói Xóa cột đó. Có danh sách tệp của tôi, tôi chỉ muốn tệp PDF, vì vậy nếu có bất kỳ thứ gì không phải là PDF, tôi chỉ muốn tệp PDF, hãy nhấp vào OK, tôi có thể thấy đó chỉ là tệp PDF. À, rồi nhìn qua đây, xem, bây giờ họ đang lấy những thứ chỉ từ thư mục gốc và từ thư mục Rác, vì vậy tôi mở cái này lên và bỏ chọn mọi thứ không phải là thư mục gốc. Được rồi, bây giờ tôi có một danh sách nhỏ rất hay, và danh sách này, bạn biết không, 9 bản ghi, nhưng trong đời thực, tôi cá là bạn có thể có hàng chục hoặc hàng trăm bản ghi này. Được rồi, tôi không cần bất kỳ nội dung nào khác, vì vậy tôi có thể nhấp chuột phải và Xóa các cột đó.

Được rồi, bây giờ những gì tôi thực sự cần ở đây là tôi cần đường dẫn thư mục và tên tệp cùng nhau. Được rồi, vì vậy tôi sẽ sử dụng FolderPath và kéo nó sang bên trái và thả nó ở đó, sau đó thực hiện bước kỳ diệu ở đây: Trong Excel thông thường, chúng tôi sẽ phải thực hiện nối cho việc này, nhưng những gì tôi sẽ làm là, tôi tôi sẽ hợp nhất các cột. Vì vậy, tôi sẽ Thêm Cột và chọn Hợp nhất Cột, Dấu phân tách sẽ là Không có, cột Mới sẽ được gọi là Tên tệp và nhấp vào OK, được rồi, vì vậy chúng tôi có tên thư mục, dấu gạch chéo và tên tệp , như vậy. Bây giờ, đó thực sự là điều duy nhất chúng tôi cần, vì vậy tôi sẽ nhấp chuột phải và nói Xóa các cột khác, và cuối cùng là Trang chủ, Đóng và Tải và chúng tôi nhận được một trang tính hoàn toàn mới với dữ liệu của mình. Được rồi, nó ở dạng bảng và vì vậy tôi sẽ chỉ sao chép nó, Ctrl + C,và sau đó quay lại đây đến nơi tôi thực sự muốn có dữ liệu ở đây và Dán các Giá trị Đặc biệt, bấm OK. Bây giờ nó không phải là một bảng nữa, nó chỉ là dữ liệu thuần túy của tôi, như vậy, và bây giờ, đây là điều thực sự tuyệt vời về điều này.

Vì vậy, chúng tôi đã thiết lập điều này một lần và thật tuyệt vời, chỉ mất chưa đến 3 phút để thiết lập, nhưng hãy quay lại thư mục Budgets và di chuyển một số nội dung xung quanh. Hãy lấy một trong những bản ghi Rác này và chúng ta sẽ sao chép nó vào thư mục chính, Ctrl + V, được rồi, vì vậy bây giờ có nhiều thứ hơn ở đây, có 10 tệp PDF thay vì 9. Nếu tôi đến đây để truy vấn, và ở phía bên tay phải của màn hình, trong Truy vấn & Kết nối, bạn có thể phải làm cho phần này rộng hơn, tôi đã làm rộng hơn rồi, bạn sẽ thấy ngân sách của chúng tôi với 9 hàng được tải. Tôi sẽ nhấp vào biểu tượng Làm mới nhỏ ở đây và rất nhanh chóng Ngân sách hiện có 10 hàng được tải. Vì vậy, nó chọn các bản ghi mới, bạn thiết lập điều này một lần, và sau đó bạn sẽ có thể làm mới để nhận dữ liệu mới.

Chà, đây là điểm trong podcast mà tôi thường yêu cầu bạn mua sách của tôi, nhưng thay vào đó hôm nay tôi sẽ yêu cầu bạn mua cuốn sách này “M là dành cho (DỮ LIỆU) MONKEY” của Ken Puls và Miguel Escobar. Một cuốn sách TUYỆT VỜI sẽ dạy cho bạn tất cả về cách sử dụng Power Query hoặc Get & Transform Data, mọi thứ tôi học được về Power Query mà tôi học được từ cuốn sách này.

OK, tóm tắt từ tập này: Mục tiêu của chúng tôi là cách nhập danh sách tên tệp vào Excel, nếu bạn có Excel 2016, bạn có thể sử dụng Dữ liệu Nhận & Chuyển đổi mới. Nếu bạn không có 2016, nhưng bạn có phiên bản Excel thực chạy trong Windows, thì bạn có thể tải xuống bổ trợ Power Query miễn phí dành cho Excel 2010 hoặc Excel 2013. Nó sẽ không hoạt động trên điện thoại Android của bạn hoặc iPad của bạn, iPhone của bạn, Surface RT hoặc Mac của bạn, đúng vậy, nó chỉ dành cho các phiên bản Windows của Excel. Vì vậy, chúng ta sẽ bắt đầu từ trang tính trống, Dữ liệu, Lấy dữ liệu, Từ tệp, Từ thư mục, nhập tên thư mục hoặc Duyệt, đảm bảo nhấp vào Chỉnh sửa thay vì Tải. Và sau đó trên Bộ lọc, lọc theo loại tệp để loại bỏ bất kỳ thứ gì không phải là PDF, lọc theo tên thư mục để loại bỏ tất cả các thư mục con rác. Chỉ giữ lại tên tệp và thư mục,vì vậy hãy bấm chuột phải vào những người khác và nói Xóa cột, sau đó kéo tiêu đề thư mục sang bên trái của tệp, điều này cho phép hợp nhất hoạt động. Chọn cả hai cột, sau đó trên tab Thêm cột, chọn Hợp nhất các cột, nhập tên mới, bấm OK, và bấm chuột phải vào cột mới đó và Loại bỏ các Cột khác, Trang chủ, Đóng và Tải, và nó sẽ cung cấp cho bạn danh sách của bạn. Phần tuyệt vời, bạn có thể làm mới truy vấn sau bằng cách sử dụng biểu tượng Làm mới này trong Truy vấn & Kết nối.bạn có thể làm mới truy vấn sau bằng cách sử dụng biểu tượng Làm mới này trong Truy vấn & Kết nối.bạn có thể làm mới truy vấn sau bằng cách sử dụng biểu tượng Làm mới này trong Truy vấn & Kết nối.

Ồ, tôi muốn cảm ơn bạn đã ghé qua, chúng tôi sẽ gặp lại bạn lần sau với một netcast khác từ!