Power Query là một công cụ mới của Microsoft để trích xuất, biến đổi và tải dữ liệu. Bài viết hôm nay là về xử lý tất cả các tệp trong một thư mục.
Power Query được tích hợp sẵn trong Excel 2016 và có sẵn dưới dạng tải xuống miễn phí trong một số phiên bản Excel 2010 và Excel 2013. Công cụ này được thiết kế để trích xuất, chuyển đổi và tải dữ liệu vào Excel từ nhiều nguồn khác nhau. Phần hay nhất: Power Query ghi nhớ các bước của bạn và sẽ phát lại khi bạn muốn làm mới dữ liệu. Khi cuốn sách này được phát hành, các tính năng Power Query trong Excel 2016 nằm trên tab Dữ liệu, trong nhóm Lấy & Chuyển đổi, bên dưới Truy vấn Mới. Thật khó để dự đoán liệu Microsoft có đổi tên Power Query thành Get & Transform trong Excel 2010 và Excel 2013 hay không.
Phần bổ trợ miễn phí này thật tuyệt vời, có thể có cả một cuốn sách về nó. Nhưng là một trong 40 mẹo hàng đầu của tôi, tôi muốn đề cập đến một điều rất đơn giản: đưa danh sách các tệp vào Excel, cùng với ngày tạo tệp và có thể là kích thước. Điều này rất hữu ích cho việc tạo danh sách sổ làm việc ngân sách hoặc danh sách ảnh.
Trong Excel 2016, bạn chọn Dữ liệu, Truy vấn Mới, Từ tệp, Từ thư mục. Trong các phiên bản Excel trước, sử dụng Power Query, From File, From Folder. Chỉ định thư mục:
Trong khi chỉnh sửa truy vấn, hãy nhấp chuột phải vào bất kỳ cột nào bạn không muốn và chọn Xóa.
Để có được Kích thước tệp, hãy nhấp vào biểu tượng này trong cột Thuộc tính:
Một danh sách các thuộc tính phụ xuất hiện. Chọn Kích thước.
Một danh sách lớn các tùy chọn Chuyển đổi có sẵn.
Khi bạn hoàn tất việc chỉnh sửa truy vấn, hãy nhấp vào Đóng & Tải.
Dữ liệu tải vào Excel dưới dạng bảng.
Sau đó, để cập nhật bảng, hãy chọn Dữ liệu, Làm mới Tất cả. Excel ghi nhớ tất cả các bước và cập nhật bảng với danh sách tệp hiện tại trong thư mục.
Để biết mô tả đầy đủ về tính năng trước đây được gọi là Power Query, hãy xem M là dành cho (Dữ liệu) Monkey của Ken Puls và Miguel Escobar.
M dành cho (DỮ LIỆU) MONKEY »
Cảm ơn Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser và Colin Michael đã đề cử Power Query.
Xem video
- Công cụ Power Query nằm trên tab Dữ liệu trong Excel 2016
- Bổ trợ miễn phí cho năm 2010 và 2013
- Liệt kê tất cả các tệp từ một thư mục vào lưới Excel bằng Power Query
- Chọn truy vấn mới, từ tệp, từ thư mục
- Không rõ ràng: mở rộng trường thuộc tính để lấy kích thước
- Nếu dữ liệu của bạn nằm trong tệp CSV, bạn có thể nhập tất cả các tệp cùng một lúc vào một lưới duy nhất
- Quảng cáo hàng tiêu đề
- Xóa các hàng tiêu đề còn lại
- Thay thế "" bằng null
- Điền vào để xem phác thảo
- Xóa cột tổng lớn
- Mở chia dữ liệu
- Công thức chuyển đổi tên tháng thành ngày tháng
- Danh sách đầy đủ các bước - Hoàn tác tuyệt vời nhất thế giới
- Ngày hôm sau - làm mới truy vấn để thực hiện lại tất cả các bước
Bản chuyển biên của video
- Power Query được tích hợp sẵn trong các phiên bản Windows của Excel 2016. Xem trên tab Dữ liệu trong nhóm Lấy & Chuyển đổi. Nếu bạn có 2010 hoặc
- 2013 miễn là bạn đang chạy Windows
- chứ không phải Mac, mọi thứ đều ở đây trong Get & Transform
- bạn có thể tải xuống miễn phí từ Microsoft. Chỉ cần tìm kiếm
- Tải xuống Power Query.
- Hôm nay, tôi quan tâm đến việc sử dụng Power Query để lấy danh sách tệp. Tôi
- muốn liệt kê tất cả các tệp trong một thư mục.
- Có lẽ tôi cần xem tệp nào là
- các tệp lớn hoặc tôi cần sắp xếp hoặc tôi cần
- bạn biết để có được sự kết hợp của bạn
- biết các tệp ngân sách mà chúng tôi đã gửi đi
- và sau đó là một thư mục khác
- chúng tôi đã trở lại.
- Để bắt đầu, hãy vào Data, Get & Tranform, From File, From Folder.
- Dán vào đường dẫn thư mục hoặc sử dụng nút Duyệt qua.
- Bấm OK và họ cho tôi xem cái này
- xem trước. Chọn Chỉnh sửa.
- Một vài điều ở đây bạn thấy chúng tôi có
- tên tệp phần mở rộng là ngày
- đã truy cập, ngày sửa đổi, ngày tạo.
- Thực sự không rõ ràng rằng biểu tượng này bên cạnh tiêu đề Thuộc tính có nghĩa là Mở rộng. Nhấp vào biểu tượng đó và có nhiều thứ hơn trong
- ở đây và nếu bạn nhấp vào biểu tượng này thì tôi
- có thể truy cập và nhận những thứ như kích thước tệp
- hoặc nếu nó ở chế độ chỉ đọc và những thứ như
- vì vậy trong trường hợp này tôi chỉ muốn tệp
- kích thước. Chọn Kích thước tệp. Nhấp vào ok. Họ cung cấp cho bạn một trường mới với tên Attributes.Size.
- Tôi có thể thấy có bao nhiêu byte trong
- mỗi tệp.
- Có lẽ tôi không cần mọi thứ ở đây có lẽ
- Tôi không cần ngày được tạo để tôi có thể
- nhấp chuột phải và nói rằng tôi muốn
- loại bỏ cột đó. Điều này
- nhị phân tôi không cần điều đó sẽ loại bỏ
- cột đó. Từ Ruy-băng, nhấp vào Đóng & Tải.
- Trong vài giây, bạn sẽ có một cái nhìn có thể sắp xếp về
- mọi thứ trong thư mục đó nếu thư mục
- những thay đổi tôi có thể vào đây và tôi có thể
- làm mới truy vấn và nó sẽ quay trở lại
- ra và kéo dữ liệu đó vào ngay đây là
- đối với tôi đây là một vấn đề mà chúng tôi đã từng
- có tất cả thời gian, chúng tôi sẽ gửi 200
- hồ sơ ngân sách
- và bạn lấy lại ai đó không phải tất cả
- trở lại bạn cần có thể so sánh như vậy
- bây giờ về cơ bản tôi có thể làm một vlookup
- giữa các thư mục.
- Nó chỉ là tuyệt vời làm thế nào
- tuyệt đấy nhưng hãy nhìn xa hơn
- what I have in the book and show you how
- that's just the tip of the iceberg.
- I'm going to create another query. Data, New Query, From File, From Folder.
- I'll copy that folder path here.
- click edit.
- As of October 2016, this trick only works with CSV
- files, but in 2017 it was updated to work with single-sheet Excel files. I
- have a folder a whole bunch of files and
- I want to create one excel grid with all
- of the data from all of these files.
- It's not intuitive at all. Look next to the heading for the Binary column. There is an icon with two arrows pointing down at a horizontal line.
- Click that.
- BAM! it just pulled in every single record from
- every single file in that folder!
- Isn't
- that amazing I mean that was a VBA macro
- before and it takes months to learn VBA
- macros you can learn power query in ten
- minutes.
- We have to select this column and
- go to replace values say that we're
- going to
- replace nothing with the word null click
- okay
- That'll give us Nulls in place of empty cells.
- Those nulls allow us to use this amazing
- featured called Fill Down. Watch that
- column when I choose Fill Down. BAM it
- just pulled in all of that outline view
- and brought the value down.
- I don't need the Grand Total column.
- Right-click and remove.
- Now at this point you say oh yeah hey we could
- pull this in and it'd be awesome. But if
- we wanted to create a pivot table from
- this data having a repeating group going
- across Jan Feb Mar is not a good format
- for a pivot tables.
- Right now we have 47
- rows I need to have 47 times twelve rows
- and to do this in a regular Excel file
- it is horrendous using a Multiple
- Consolidation Range that I learned from
- Mike Alexander at Data Pig Technologies.
- But it is easy in Power Query. Check this out I'm going to choose the
- label columns along the left. These are the things that I don't
- want to change and then on the Transform tab, choose Unpivot Other Columns.
- We go from 47 rows to 564 rows
- that's an amazing step.
- Here you can see
- that these values are text. It is easy enough to
- change it to either currency or a whole
- number. Right click the heading and choose Rename and call it
- revenue
- How about these months? They're
- all text such as Jan, Feb, Mar. Here's an awesome way to fix
- that we go to add column add a custom
- column doesn't matter what the name you use.
- The calculation, in quotes, is " 1, 2016". Click OK.
- Now we have this new custom column I'm going to take the
- attribute column containing Month names and the new custom column. Select both columns
- and say I want to merge those columns
- with a space in between and call it date.
- Click OK. That looks enough
- like a real date that when I go to
- transform and change it to a date it
- converts it to a true Excel date.
- At this point these two temporary
- columns I can right click and remove.
- Now you could be saying to yourself:
- Wait, Bill! we could have done all of this in Excel and that's absolutely true we could have done all of it in Excel it would have been harder to get all the CSV files into one file it would have taken longer to fill in the blanks it definitely would have taken longer to do the unpivot operation but here's the thing look over on the right-hand side we haven't talked about Applied Steps at all. The Applied Steps panel is like the world's greatest undo if you need an audit trail if the auditors come and say well how did you get from all these CSV files to this file that we're building our financial statements on you can go back and show what it looked like at each step along the way. If you screwed something up back here you could change or edit that step. Next, on the Power Query Home tab, choose Close and Load. So here's our data set this is based on all of the files in this folder. Let's build a little pivot table from here insert pivot table existing worksheet right here and I'll put revenue in the values area products down the left hand side you see that we have six million in revenue. Well that's today's data now tomorrow tomorrow let's say that we get a couple of new customers a couple of new files come along and our IT department takes those and dumps them into our folder all I have to do is reopen this file select the query come over here and refresh and then come here analyze refresh the pivot table and we have the new data! Power Query is faster on day one maybe by a factor of 20-30%. On day two is faster by 99% it's an absolutely amazing product Power Query. It's in Excel 2016 but if you're in 2010 or 2013 for Windows you're more than welcome to go out and download it for free. The book that will teach you about Power Query is M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
- Cuốn sách này sẽ dạy
- cho bạn mọi thứ về truy vấn nguồn
- giao diện nó là một cuốn sách tuyệt vời và tốt nhất
- cuốn sách về truy vấn nguồn mọi thứ tôi đã học
- Tôi đã học được từ cuốn sách này. Tôi đã lên một chuyến bay từ
- Orlando đến Dallas - tôi đã đọc toàn bộ cuốn sách
- và kiến thức của tôi về truy vấn nguồn chỉ
- tăng vọt trong hai giờ, bạn có thể lên đến
- tốc độ và thay thế những thứ mà bạn muốn
- đã từng có với VBA.
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2037.xlsx
