Cách hợp nhất dữ liệu từ một số sổ làm việc Excel. Giả sử rằng bạn có một thư mục chứa đầy các tệp Excel. Mỗi người có một trang tính với số hàng khác nhau. Bạn có thể nhập tất cả các tệp trong thư mục vào một sổ làm việc Excel.
Xem video
- Kiểm tra lại dữ liệu sạch với podcast Power Query # 2037
- Power Query hiện có thể kết hợp tất cả các tệp Excel trong một thư mục.
- Cải thiện: Chúng tự động xóa các tiêu đề khỏi tất cả trừ tệp đầu tiên.
- Bạn chọn tệp nào sẽ sử dụng làm tệp Mẫu.
- Chọn Trang tính, Bảng, Phạm vi được đặt tên để nhập
- Sử dụng trình chỉnh sửa truy vấn để thực hiện bất kỳ chuyển đổi nào
- Đóng và tải để kết hợp tất cả các tệp
- Sau đó, hãy làm mới truy vấn để cập nhật nó
Bản ghi video
Học Excel từ Podcast, Tập 2077: Hợp nhất các sổ làm việc
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Chỉ 40 tập trước, nói về cách làm sạch dữ liệu bằng Power Query, cách lấy toàn bộ tệp CSV và đưa chúng vào một tệp Excel duy nhất. Tính năng này đã trở nên tốt hơn đáng kể.
Vì vậy, tôi có ở đây ngày hôm nay là một thư mục và trong thư mục này là một loạt các tệp Excel hàng tháng, 22 trong số đó trong gần hai năm. Tôi cần kết hợp tất cả các tệp này. Bây giờ, tất cả đều có bố cục cột giống nhau, được chứ? Vì vậy, tất cả chúng đều có 9 cột, tất cả đều có cùng tiêu đề và cứ tiếp tục như vậy. Vì vậy, chúng tôi sẽ kết hợp tất cả những thứ này mà không có macro.
Nếu bạn đang sử dụng Excel 2016, bạn sẽ đến tab Dữ liệu để Lấy & Chuyển đổi dữ liệu. Nếu bạn đang sử dụng Excel 2010 hoặc 2013, bạn sẽ ra ngoài và tải xuống một sản phẩm tuyệt vời từ Microsoft có tên Power Query. Bạn có thể tải xuống toàn bộ và cài đặt nó trong vòng chưa đầy một phút.
Vì vậy, chúng ta sẽ đến đây để Lấy Dữ liệu, Từ một Tệp chứ không phải từ một sổ làm việc. Chúng tôi muốn lấy nó từ một thư mục như thế này. Được rồi, chúng đây. Họ đang yêu cầu bạn Duyệt đến thư mục nhưng tôi chỉ nhấp vào đó và dán Ctrl + V vì tôi đã dán thư mục, nhấp OK. Vì vậy, họ đã cho tôi xem trước dữ liệu tôi có. Tôi mở menu thả xuống Kết hợp nhỏ này ở đây và chọn Kết hợp & Chỉnh sửa. Được rồi, bước ở đây là chúng ta sẽ chọn một tệp là tệp đại diện tốt, được thôi, và họ chỉ chọn tệp đầu tiên sắp xếp theo thứ tự bảng chữ cái. Không quan trọng bạn chọn cái nào. Và sau đó Truy vấn mẫu này mà chúng tôi đang xây dựng từ tệp đầu tiên mà chúng tôi chọn, trong trường hợp này, tôi muốn Sheet1.Nếu bạn có nhiều trang tính, bạn có thể nói Trang tính 2 hoặc trang tính được gọi là Báo cáo thu nhập hoặc Phạm vi tên hoặc một bảng. Và họ đã cho tôi thấy nó trông như thế nào và tôi bấm OK.
Bây giờ, có rất nhiều thứ đang diễn ra trong màn hình này. Những gì họ thực sự đã làm ở đây là họ đã tạo một Truy vấn mẫu, ahh Truy vấn mẫu, và sau đó tạo nó dưới dạng một hàm và chạy hàm đó với mọi thứ trong thư mục. Họ thêm một cột mới cho tôi biết tệp đến từ đâu và trong trường hợp cụ thể này, tôi đã biết tệp đó đến từ đâu vì chúng tôi có ngày được điền ở đây. Vì vậy, tôi không cần điều đó, tôi sẽ nhấp chuột phải và Xóa nó. Và có thể có một số dữ liệu trong đó mà tôi không cần. Có thể dữ liệu này sẽ được chuyển đến khách hàng nên tôi không muốn hiển thị Lợi nhuận để tôi có thể xóa cột đó và thậm chí xóa cột Chi phí, và có thể có một số điều khó chịu như, bạn biết đấy, họ đã sử dụng tiêu đề xấu và tôi muốn đổi tên các tiêu đề này. Vì vậy, nhấp chuột phải và Đổi tên, chúng tôi sẽ gọi nó là QTY cho Số lượng.Có thể mọi thứ không đúng trình tự, có thể tôi muốn lấy món đồ này và chuyển nó sang chỗ khác, được không? Chỉ là tất cả những điều khó chịu nhỏ này mà bạn có thể có và tất nhiên ở đây, họ đang thực hiện tất cả các bước đã áp dụng và sau đó bạn có thể tiếp tục thực hiện bất kỳ công việc Power Query nào mà bạn cần làm. Nhưng trong trường hợp này, nó chỉ đơn giản. Tôi chỉ muốn lấy dữ liệu vào. Vì vậy, tôi sẽ Đóng & Tải, một vài giây trôi qua và nó sẽ đến. Tôi có trận chung kết của tôi. Hãy chỉ sắp xếp điều này ở đây theo ngày; bạn thấy đó, chúng tôi sẽ bắt đầu từ tháng 1 năm 2018 đến hết tháng 10 năm 2019. Điều đó thật tuyệt làm sao.đang thực hiện tất cả các bước đã áp dụng và sau đó bạn có thể tiếp tục thực hiện bất kỳ công việc Power Query nào mà bạn cần làm. Nhưng trong trường hợp này, nó chỉ đơn giản. Tôi chỉ muốn lấy dữ liệu vào. Vì vậy, tôi sẽ Đóng & Tải, một vài giây trôi qua và nó sẽ đến. Tôi có trận chung kết. Hãy chỉ sắp xếp điều này ở đây theo ngày; bạn thấy đó, chúng tôi sẽ bắt đầu từ tháng 1 năm 2018 đến hết tháng 10 năm 2019. Điều đó thật tuyệt làm sao.đang thực hiện tất cả các bước đã áp dụng và sau đó bạn có thể tiếp tục thực hiện bất kỳ công việc Power Query nào mà bạn cần làm. Nhưng trong trường hợp này, nó chỉ đơn giản. Tôi chỉ muốn lấy dữ liệu vào. Vì vậy, tôi sẽ Đóng & Tải, một vài giây trôi qua và nó sẽ đến. Tôi có trận chung kết của tôi. Hãy chỉ sắp xếp điều này ở đây theo ngày; bạn thấy đó, chúng tôi sẽ bắt đầu từ tháng 1 năm 2018 đến hết tháng 10 năm 2019. Điều đó thật tuyệt làm sao.
Bây giờ đây là điều tuyệt vời, tuyệt vời về điều này. Giả sử rằng có gì đó thay đổi trong thư mục của chúng ta. Vì vậy, trở lại đây trong tệp Tháng, tôi nhận được hai tệp mới, Dán, và bây giờ có thêm hai bản ghi ở đây, hai tệp Excel nữa mà tôi phải kết hợp. Chỉ cần quay lại tệp Excel của tôi hiện có 516 hàng, tìm tệp Tháng và nhấp vào nút Làm mới. Và nhanh chóng, tôi đã có những kỷ lục mới từ tháng 11 và tháng 12. Power Query là một công cụ mới tuyệt vời miễn phí cho Excel 2010, miễn phí cho Excel 2013. Thật tuyệt vời khi chúng có thể kết hợp các tệp CSV nhưng giờ đây chúng cũng có thể kết hợp các tệp Excel, thật là phi thường.
Vì vậy, tôi đã cập nhật cuốn sách của mình, Power Excel với, Phiên bản 2017, bao gồm Power Query, Power BI và tất cả các công cụ mới. Kiểm tra nó ra. Nhấp vào chữ "i" ở góc trên bên phải.
Được rồi, tóm tắt lại tập này: Chúng tôi đang xem lại Dữ liệu sạch bằng podcast Power Query # 2037, nơi chúng tôi kết hợp nhiều tệp CSV. Bây giờ chúng ta có thể sử dụng cùng một công cụ đó, một phiên bản cải tiến của công cụ đó, để kết hợp tất cả các tệp Excel trong một thư mục. Một cải tiến khác, thay đổi từ năm 2037, chúng tự động xóa các tiêu đề khỏi tất cả trừ tệp đầu tiên và quảng bá các tiêu đề đó trong tệp đầu tiên. Bạn chọn tệp nào sẽ sử dụng làm tệp Mẫu. Trong trường hợp của tôi, tôi đã sử dụng tháng Giêng. Công việc- Chọn Trang tính, Bảng hoặc Phạm vi được Đặt tên nào để Nhập và sau đó thực hiện bất kỳ chuyển đổi nào trong trình chỉnh sửa truy vấn. Chúng tôi nhấp vào Đóng & Tải, nó sẽ kết hợp tất cả các tệp. Và sau đó, nếu bạn nhận được nhiều tệp bị rơi vào thư mục hoặc một số tệp bị lấy đi hoặc một số tệp bị thay đổi, chỉ cần làm mới truy vấn và nó sẽ thực hiện lại tất cả các bước đó. Power Query,một công cụ hoàn toàn tuyệt vời.
Tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải file mẫu tại đây: Podcast2077.xlsm
