Trong bài viết này chúng ta hãy cùng tím hiểu cách gộp nhiều file excel trong cùng một tập tin. Power Query là một công cụ mạnh mẽ cho phép bạn làm việc này chỉ với vài cú click chuột.
Cách hoạt động của nó như sau: bạn chỉ cần chỉ định thư mục, cho Power Query biết tập tin nào cần nhập và tổng hợp dữ liệu tương ứng. Nhưng tại sao lại cần làm điều đó?
Hãy tưởng tượng bạn vừa nhận được một kho dữ liệu bán hàng khổng lồ gồm 60 tập tin Excel. Bây giờ, trước khi bắt tay vào phân tích chúng, bạn cần phải tổng hợp toàn bộ số liệu. Nhưng làm sao để xử lý khối lượng dữ liệu khổng lồ này?
Có thể bạn đã từng thử cách cũ, tức là mở lần lượt từng tập tin và sao chép dán chúng vào một tập tin mới. Nhưng công việc đó tốn kém thời gian và công sức đấy. Hoặc có thể bạn đã từng dùng những công thức phức tạp để giải quyết vấn đề này. Nhưng rủi ro mắc phải sai sót và mất thời gian để đảm bảo tính chính xác của số liệu.
Nhưng đừng lo lắng! Với Power Query, bạn sẽ không còn phải làm việc đơn điệu và lặp đi lặp lại này nữa!
Contents
Excel Sample Data
Chúng ta bắt đầu với ví dụ bên dưới, folder Sale_2023 tổng hợp dữ liệu
Các bạn tải file cùng thực hành với mình ở đây
Import excel folder vào Power Query
Các bạn làm theo như ảnh dưới đây
- Mở excel chọn tab Data > From File > From Folder
- Chọn thư mục bạn muốn gộp file excel
Ảnh dưới đây cho thấy tất cả các tập tin có sẵn trong thư mục. Để tổng hợp, chúng ta cần dữ liệu bán hàng cho các tháng từ tháng 1 đến tháng 4. Bên cạnh các tập tin đó, thư mục còn chứa hai tập tin mà chúng ta không muốn tổng hợp.
Có ba tùy chọn để xem xét ở màn hình trên là Combine, Load và Transform Data.
- Combine: đưa bạn đến một màn hình để chọn dữ liệu cần kết hợp. Tùy chọn này sẽ bỏ qua bước chỉnh sửa và không cho phép bạn kiểm soát các tập tin nào sẽ được tổng hợp. Mình không đề xuất tùy chọn này vì nó không cho phép bạn kiểm soát lỗi trong quá trình tổng hợp dữ liệu.
- Load: Sẽ tải bảng dữ liệu như hiển thị ở trên vào Excel (không bao gồm dữ liệu bán hàng thực tế).
- Transform Data: Một màn hình mới xuất hiện. Trong màn hình này, bạn có cơ hội thay đổi các tập tin mà bạn muốn tổng hợp.
Mình khuyến khích các bạn chọn “Transform Data” để lọc ra các tập tin không cần thiết. Trong phần tiếp theo, bạn sẽ học cách áp dụng bộ lọc phù hợp.
Lọc file cần gộp
Sau khi bấm vào “Transform Data“, bạn sẽ đến màn hình hiển thị thông tin về các tập tin có sẵn trong thư mục nguồn. Tại đây, bạn có thể chọn các tập tin cần thiết bằng cách thêm bộ lọc. Trong ví dụ này, bạn chỉ cần bốn file bán hàng.
Để tránh lỗi, thì tốt nhất chúng ta lọc ra những Extension file nào sẽ được gộp lại. Trong trường hợp này, chung ta sẽ lọc ra chỉ lấy những file có đuôi “.xlsx”.
- Nhấp vào mũi tên thả xuống bên cạnh cột Extension -> chỉ tick chọn “.xlsx“
Kết quả giống như hình bên dưới, sau khi đã lọc ra các file “.xlsx” cần gộp, thì vẫn tồn tại file “SaleForce.xlsx” mà chúng ta không cần tổng hợp file này.
Để loại bỏ file này các bạn có thể đơn giản nhấp vào mũi tên thả xuống bên cạnh cột Name -> bỏ chọn “SaleForce.xlsx”
Tuy nhiên đây không phải là cách làm tổng quát, các bạn hãy chú ý tới những file cần gộp sẽ có format “Sale_yyyyMM.xlsx“. Vậy đơn giản chúng ta chỉ cần lấy những file bắt đầu bằng “Sale_”. Cách thực hiện như sau:
- nhấp vào mũi tên thả xuống bên cạnh cột Name -> Text Filters > Begins With…
- Các bạn điền “Sale_” vào ô bên cạnh “Begins With“
Tới bước này thì bảng của chúng ta chỉ còn lại 4 file đúng theo format cần được tổng hợp
Hướng dẫn gộp file bằng Power Query
Bước tiếp theo là cấu hình Power Query và chọn các tùy chọn. Trước tiên, Power Query yêu cầu bạn cung cấp một tập tin mẫu để dựa vào đó chuẩn hóa cho toàn bộ những tập tin còn lại.
- Chọn góc Content như trong ảnh
Power Query sẽ hiện lên một thông báo để các bạn chọn file làm mẫu
- Mặc định Power Query sẽ để Sample File: First file. Các bạn để như vậy và chọn Sheet1 bên dưới rồi chọn OK
Load dữ liệu đã tổng hợp vào Excel
Sau đó, Power Query sẽ thực hiện quá trình tổng hợp. Và như ma thuật! Các tệp dữ liệu xuất hiện được kết hợp trong bảng dưới đây.
Các bạn có thể check lại xem Power Query đã gộp các file excel cần thiết chưa bằng cách
- Chọn vào mũi tên bên Source.Name
Sau khi kiểm tra, quan trọng là xác định các kiểu dữ liệu đúng trong các cột. Trong ví dụ này, các cột đã có các kiểu dữ liệu đúng. Như một bước cuối cùng, bạn thường sẽ đưa bảng dữ liệu vào bảng Excel của mình. Để làm điều đó:
- Chọn Close & Load
Và đây là kết quả
Kết luận
Trong bài viết này, bạn đã chứng kiến quá trình kết hợp ba tệp tin, nhưng bạn hoàn toàn có thể lặp lại quá trình này cho 10, 100 hoặc 1000 tệp tin khác!
Và điều tuyệt vời nhất: khi bạn thêm các tệp bán hàng cho phần còn lại của năm, bạn chỉ cần nhấn “Refresh” để cập nhật kết hợp! Quá trình này sẽ hoàn toàn tự động gộp các file cần gộp. Bạn chỉ cần thêm file excel mới vào folder theo đúng định dạng đã thiết lập trong Power Query.
Mình hy vọng điều này có giá trị đối với bạn. Cùng tìm hiểu thêm các tips hay Power Query và Power BI
