Hướng Dẫn Gộp File Excel Hàng Loạt Bằng Power Query: Từ A đến Z

Trong thế giới phân tích dữ liệu và quản lý công việc văn phòng, việc tổng hợp thông tin từ nhiều nguồn khác nhau là một nhiệm vụ quen thuộc nhưng cũng đầy thách thức. Bạn có thể đang phải đối mặt với việc gộp dữ liệu báo cáo bán hàng hàng tháng từ các chi nhánh, tổng hợp bảng chấm công từ nhiều bộ phận, hay kết hợp dữ liệu khảo sát từ các tệp riêng lẻ. Quá trình sao chép và dán thủ công không chỉ tốn hàng giờ đồng hồ quý báu mà còn tiềm ẩn vô số nguy cơ sai sót, nhầm lẫn dữ liệu, gây ảnh hưởng nghiêm trọng đến chất lượng của báo cáo cuối cùng.

May mắn thay, Microsoft đã tích hợp một “vũ khí bí mật” ngay trong Excel mang tên Power Query. Đây là một công cụ chuyển đổi và tổng hợp dữ liệu cực kỳ mạnh mẽ, cho phép bạn tự động hóa hoàn toàn quy trình gộp file Excel. Chỉ cần thiết lập một lần duy nhất, và từ đó về sau, bạn chỉ cần một cú nhấp chuột để cập nhật toàn bộ dữ liệu mới. Bài viết này sẽ là kim chỉ nam chi tiết, dẫn dắt bạn từ những khái niệm cơ bản đến các kỹ thuật nâng cao để làm chủ Power Query, biến công việc gộp file nhàm chán trở nên nhanh chóng và hiệu quả hơn bao giờ hết.

Power Query là gì và tại sao nên dùng để gộp file Excel?

Trước khi đi sâu vào các bước thực hành, việc hiểu rõ bản chất và ưu điểm của công cụ sẽ giúp bạn tự tin hơn khi sử dụng. Power Query, còn được biết đến với tên gọi Get & Transform Data trong các phiên bản Excel gần đây, là một công cụ ETL (Extract, Transform, Load) được tích hợp sẵn.

Nói một cách dễ hiểu, nó thực hiện ba nhiệm vụ chính:

• Extract (Trích xuất): Lấy dữ liệu từ rất nhiều nguồn khác nhau, không chỉ là các file Excel mà còn có thể là file văn bản (TXT, CSV), cơ sở dữ liệu (SQL, Access), trang web, và nhiều hơn nữa.
• Transform (Biến đổi): Đây là trái tim của Power Query. Tại đây, bạn có thể làm sạch, định hình, kết hợp, và thay đổi dữ liệu theo bất kỳ cách nào bạn muốn mà không làm ảnh hưởng đến file gốc.
• Load (Tải): Sau khi dữ liệu đã được “sơ chế” hoàn hảo, bạn có thể tải nó vào một trang tính Excel mới, một mô hình dữ liệu (Data Model) hoặc một bảng PivotTable để phân tích.

Ưu điểm vượt trội khi dùng Power Query để gộp file Excel

So với phương pháp thủ công hay thậm chí là dùng macro VBA, việc sử dụng Power Query để kết hợp file Excel mang lại những lợi ích không thể phủ nhận:

1. Tự động hóa hoàn toàn: Bạn chỉ cần thiết lập quy trình một lần. Khi có file dữ liệu mới, bạn chỉ cần đưa nó vào thư mục nguồn và nhấn “Refresh”. Power Query sẽ tự động gộp dữ liệu mới vào bảng tổng hợp.
2. Giảm thiểu sai sót: Máy móc thực hiện các bước lặp đi lặp lại một cách chính xác, loại bỏ hoàn toàn các lỗi do con người gây ra như sao chép nhầm, dán thiếu hoặc thừa dữ liệu.
3. Không phá hủy dữ liệu gốc: Mọi thao tác biến đổi đều diễn ra trong môi trường Power Query Editor. Các file dữ liệu nguồn của bạn vẫn được giữ nguyên vẹn, đảm bảo an toàn tuyệt đối.
4. Khả năng xử lý mạnh mẽ: Power Query có thể xử lý hàng triệu dòng dữ liệu một cách hiệu quả, điều mà việc copy-paste thủ công không thể làm được. Nó dễ dàng xử lý hàng chục, thậm chí hàng trăm file cùng lúc.
5. Khả năng biến đổi dữ liệu đa dạng: Ngoài việc gộp file, bạn có thể thực hiện hàng loạt các tác vụ làm sạch dữ liệu như: thay đổi định dạng, tách cột, gộp cột, lọc bỏ các hàng không cần thiết, thêm các cột tính toán tùy chỉnh, và nhiều hơn nữa.

Chuẩn bị dữ liệu: Yếu tố quyết định thành công khi gộp file Excel

Một câu nói kinh điển trong ngành dữ liệu là “Garbage In, Garbage Out” (Rác vào, Rác ra). Chất lượng của kết quả cuối cùng phụ thuộc hoàn toàn vào sự chuẩn bị kỹ lưỡng dữ liệu đầu vào. Để quá trình gộp file Excel bằng Power Query diễn ra suôn sẻ, bạn cần tuân thủ nghiêm ngặt các quy tắc sau.

Quy tắc 1: Thống nhất Cấu trúc Cột

Đây là quy tắc quan trọng nhất. Tất cả các file Excel bạn muốn gộp phải có cấu trúc cột hoàn toàn giống nhau. Điều này có nghĩa là:

• Cùng số lượng cột: Nếu file báo cáo tháng 1 có 5 cột (Ngày, Mã SP, Tên SP, Số lượng, Doanh thu), thì file của tháng 2, 3 và tất cả các tháng khác cũng phải có chính xác 5 cột đó.
• Cùng tiêu đề cột và thứ tự: Tên của các tiêu đề cột phải giống hệt nhau (kể cả khoảng trắng và viết hoa/thường) và được sắp xếp theo cùng một thứ tự. “Doanh Thu” khác với “doanh thu” hoặc “Doanh thu “.

Nếu không tuân thủ, Power Query sẽ bị “bối rối”, dẫn đến việc dữ liệu bị trộn lẫn sai cột hoặc báo lỗi không thể kết hợp. Hãy dành thời gian kiểm tra và chuẩn hóa tất cả các file trước khi bắt đầu.

Quy tắc 2: Thống nhất Tên Trang tính (Sheet)

Khi kết hợp file, Power Query sẽ hỏi bạn muốn lấy dữ liệu từ sheet nào. Để đơn giản hóa, hãy đảm bảo rằng trang tính chứa dữ liệu cần gộp trong tất cả các file đều có cùng một tên. Ví dụ, hãy đặt tên cho tất cả các sheet là “Data”, “Sales_Report” hoặc giữ nguyên là “Sheet1”. Việc này giúp Power Query tự động xác định đúng nơi để lấy dữ liệu từ mỗi file mà không cần can thiệp thủ công.

Quy tắc 3: Tổ chức Thư mục Nguồn

Power Query hoạt động hiệu quả nhất khi làm việc với một thư mục. Hãy tạo một thư mục riêng trên máy tính của bạn (ví dụ: “D:\DuLieuGopExcel\BaoCaoThang”). Sau đó, di chuyển tất cả và chỉ tất cả các file Excel cần gộp vào thư mục này. Không nên để các file không liên quan, file tạm hoặc phiên bản cũ trong thư mục này, vì Power Query sẽ cố gắng đọc tất cả các file có trong đó, có thể gây ra lỗi không mong muốn.

Quy tắc 4 (Khuyến nghị): Định dạng Dữ liệu thành Bảng (Excel Table)

Mặc dù không bắt buộc, việc chuyển đổi vùng dữ liệu của bạn thành một Bảng Excel (Excel Table) chính thức sẽ mang lại nhiều lợi ích. Để thực hiện, chỉ cần chọn vào một ô bất kỳ trong vùng dữ liệu và nhấn tổ hợp phím Ctrl + T, sau đó nhấn OK.

Lợi ích của việc này là gì?

• Phạm vi động: Khi bạn thêm dữ liệu mới vào cuối bảng, phạm vi của Table sẽ tự động mở rộng. Power Query sẽ luôn lấy được toàn bộ dữ liệu mà không cần bạn phải cập nhật lại vùng chọn.
• Tham chiếu có cấu trúc: Power Query làm việc với các Table một cách tự nhiên và hiệu quả hơn so với các vùng dữ liệu thông thường.

Sau khi hoàn tất bốn bước chuẩn bị này, bạn đã có một nền tảng vững chắc để bắt đầu quá trình gộp file Excel một cách tự động và chính xác.

Hướng dẫn chi tiết cách gộp file Excel bằng Power Query

Khi dữ liệu đã được sắp xếp gọn gàng trong một thư mục, chúng ta sẽ bắt đầu hành trình biến những file riêng lẻ thành một bảng dữ liệu thống nhất. Hãy mở một file Excel trắng hoàn toàn để chứa kết quả cuối cùng và làm theo các bước dưới đây.

Bước 1: Khởi tạo Truy vấn từ Thư mục (Get Data from Folder)

Đây là bước đầu tiên để “chỉ” cho Excel biết nơi chứa các file dữ liệu nguồn của bạn.

1. Trên thanh công cụ của Excel, chọn tab Data.
2. Ở nhóm công cụ Get & Transform Data, nhấp vào Get Data.
3. Một menu thả xuống sẽ hiện ra, bạn chọn From File > From Folder.
4. Cửa sổ Folder sẽ xuất hiện. Nhấp vào nút Browse… và điều hướng đến thư mục mà bạn đã tạo ở bước chuẩn bị (ví dụ: “D:\DuLieuGopExcel\BaoCaoThang”). Chọn thư mục đó và nhấn OK.
5. Excel sẽ hiển thị một cửa sổ xem trước, liệt kê tất cả các file có trong thư mục đó cùng với các thông tin siêu dữ liệu như tên file (Name), phần mở rộng (Extension), ngày sửa đổi (Date modified)…

Bước 2: Kết hợp và Chỉnh sửa Dữ liệu trong Power Query Editor

Tại cửa sổ xem trước, bạn sẽ thấy một vài tùy chọn ở phía dưới. Đây là một ngã rẽ quan trọng.

• Combine: Tùy chọn này có một menu con. “Combine & Transform Data” sẽ đưa bạn đến Power Query Editor để tinh chỉnh thêm, trong khi “Combine & Load” sẽ cố gắng gộp và tải thẳng dữ liệu vào Excel. Để có sự kiểm soát tốt nhất, chúng ta luôn nên chọn “Combine & Transform Data”.
• Load: Tải danh sách các file (chứ không phải nội dung bên trong) vào Excel. Chúng ta không dùng tùy chọn này.
• Transform Data: Mở Power Query Editor với danh sách các file, cho phép bạn thực hiện các thao tác nâng cao trước khi kết hợp. Người mới bắt đầu nên chọn “Combine & Transform Data”.
• Cancel: Hủy bỏ thao tác.

Hãy nhấp vào nút Combine và chọn Combine & Transform Data. Ngay lập tức, cửa sổ “Combine Files” sẽ hiện lên. Power Query đang yêu cầu bạn cung cấp một file mẫu để nó “học” cấu trúc dữ liệu. Nó sẽ tự động chọn file đầu tiên trong danh sách. Bên trái, bạn sẽ thấy các đối tượng có thể trích xuất từ file mẫu, thường là các Sheet hoặc các Table (nếu bạn đã định dạng bằng Ctrl + T). Hãy chọn đúng tên Sheet (ví dụ “Sheet1”) đã được thống nhất ở bước chuẩn bị. Một bản xem trước dữ liệu từ sheet đó sẽ hiện ra ở bên phải. Khi đã chắc chắn, nhấn OK.

Bước 3: Khám phá Power Query Editor – “Nhà bếp” dữ liệu của bạn

Sau khi nhấn OK, bạn sẽ được đưa đến một giao diện hoàn toàn mới: Power Query Editor. Đây chính là trung tâm điều khiển, nơi tất cả phép màu xảy ra. Đừng hoảng sợ bởi giao diện nhiều tùy chọn, hãy cùng làm quen với các thành phần chính:

• Ribbon (Thanh công cụ): Giống như trong Excel, đây là nơi chứa tất cả các lệnh và chức năng để biến đổi dữ liệu, được sắp xếp trong các tab như Home, Transform, Add Column, View.
• Queries Pane (Khung Truy vấn): Nằm bên trái, hiển thị tất cả các truy vấn có trong file Excel của bạn. Bạn sẽ thấy Power Query đã tự động tạo ra một loạt các truy vấn phụ trợ để xử lý việc gộp file. Bạn chỉ cần quan tâm đến truy vấn chính, thường có tên là tên thư mục của bạn.
• Data Preview (Vùng xem trước dữ liệu): Chiếm phần lớn màn hình, hiển thị dữ liệu đã được gộp từ tất cả các file. Bạn có thể cuộn để xem dữ liệu.
• Query Settings Pane (Khung Cài đặt Truy vấn): Nằm bên phải, đây là phần quan trọng nhất. Nó chứa ô Name (để đổi tên truy vấn) và mục APPLIED STEPS (Các bước đã áp dụng).

Mục APPLIED STEPS ghi lại từng thao tác bạn thực hiện. Mỗi khi bạn lọc, xóa cột, đổi tên… một bước mới sẽ được thêm vào danh sách này. Bạn có thể nhấp vào từng bước để xem trạng thái của dữ liệu tại thời điểm đó, hoặc nhấn dấu “X” để xóa bỏ một thao tác. Đây là tính năng cực kỳ mạnh mẽ để gỡ lỗi và chỉnh sửa quy trình.

Bước 4: Tinh chỉnh và làm sạch dữ liệu (Transform Data)

Bây giờ, hãy nhìn vào dữ liệu đã được gộp. Power Query đã tự động thêm một cột mới ở đầu tiên tên là Source.Name. Cột này chứa tên của file Excel gốc cho mỗi dòng dữ liệu, rất hữu ích để truy xuất nguồn gốc khi cần. Nếu bạn không cần cột này, chỉ cần nhấp chuột phải vào tiêu đề cột và chọn Remove.

Đây là lúc bạn vận dụng sức mạnh của Power Query để làm sạch dữ liệu:

• Kiểm tra và thay đổi định dạng dữ liệu: Power Query thường đoán khá tốt định dạng, nhưng bạn nên kiểm tra lại. Nhấp vào biểu tượng nhỏ ở bên trái mỗi tiêu đề cột (ABC, 123, Lịch) để đảm bảo cột ngày tháng có định dạng Date, cột số lượng/doanh thu có định dạng Number, cột văn bản có định dạng Text.
• Lọc bỏ các hàng không mong muốn: Nếu dữ liệu có các hàng trống hoặc hàng tổng cộng ở cuối mỗi file, bạn có thể dễ dàng loại bỏ chúng. Nhấp vào mũi tên lọc trên tiêu đề cột, bỏ chọn “(blank)” hoặc các giá trị không mong muốn, rồi nhấn OK.
• Xóa các cột thừa: Nếu có các cột không cần thiết cho báo cáo cuối cùng, hãy chọn chúng (giữ Ctrl để chọn nhiều cột), sau đó nhấp chuột phải và chọn Remove Columns.

Bước 5: Tải dữ liệu đã gộp vào trang tính Excel (Close & Load)

Khi bạn đã hài lòng với bảng dữ liệu sạch sẽ, đã đến lúc đưa nó ra ngoài Excel để sử dụng. Trên tab Home của Power Query Editor, ở góc trên cùng bên trái, bạn sẽ thấy nút Close & Load.

Nút này có hai lựa chọn:

1. Close & Load: Đây là tùy chọn mặc định. Power Query sẽ đóng trình chỉnh sửa, tạo một trang tính mới và tải toàn bộ dữ liệu của bạn vào đó dưới dạng một Bảng Excel (Excel Table) màu xanh lá. Đây là lựa chọn phổ biến và phù hợp nhất cho hầu hết các trường hợp.
2. Close & Load To…: Tùy chọn này cho bạn nhiều quyền kiểm soát hơn. Một hộp thoại sẽ hiện ra, cho phép bạn chọn cách tải dữ liệu:
   • Table: Tải thành bảng (giống như trên), nhưng bạn có thể chọn tải vào một trang tính mới (New worksheet) hoặc một vị trí cụ thể trên trang tính hiện có (Existing worksheet).
   • PivotTable Report: Tải dữ liệu trực tiếp vào một báo cáo PivotTable mới, sẵn sàng để bạn kéo thả và phân tích.
   • PivotChart: Tương tự nhưng tạo ra một biểu đồ PivotChart.
   • Only Create Connection: Không tải dữ liệu ra bảng tính. Thay vào đó, nó chỉ tạo một kết nối đến dữ liệu. Tùy chọn này hữu ích khi bạn có bộ dữ liệu khổng lồ và chỉ muốn dùng nó trong Mô hình Dữ liệu (Data Model) hoặc kết hợp với các truy vấn khác.

Đối với mục đích gộp file Excel, hãy chọn Close & Load. Chờ một lát, và bạn sẽ thấy một sheet mới xuất hiện với toàn bộ dữ liệu từ các file con đã được hợp nhất một cách hoàn hảo!

Tự động cập nhật dữ liệu: Sức mạnh thực sự của Power Query

Việc gộp file thành công mới chỉ là một nửa câu chuyện. Vẻ đẹp thực sự của Power Query nằm ở khả năng cập nhật dữ liệu một cách tự động. Giả sử tháng sau, bạn có thêm file “BaoCao_Thang4.xlsx”. Thay vì lặp lại toàn bộ quy trình, bạn chỉ cần:

1. Sao chép file “BaoCao_Thang4.xlsx” vào thư mục nguồn (“D:\DuLieuGopExcel\BaoCaoThang”).
2. Mở file Excel chứa bảng kết quả tổng hợp của bạn.
3. Đi đến tab Data và nhấp vào nút Refresh All. Hoặc, đơn giản hơn, nhấp chuột phải vào bất kỳ đâu trong bảng kết quả và chọn Refresh.

Trong nháy mắt, Power Query sẽ lặp lại toàn bộ các bước đã được ghi lại, đọc cả file mới và tự động nối dữ liệu của tháng 4 vào cuối bảng tổng hợp của bạn. Không cần copy, không cần paste, không có rủi ro sai sót. Đây chính là sự tự động hóa giúp bạn tiết kiệm vô số thời gian và công sức.

Thiết lập tự động làm mới khi mở file

Để quy trình trở nên “thông minh” hơn nữa, bạn có thể cài đặt để truy vấn tự động làm mới mỗi khi bạn mở file Excel.

1. Trong tab Data, nhấp vào Queries & Connections để mở khung bên phải.
2. Nhấp chuột phải vào truy vấn của bạn và chọn Properties….
3. Trong hộp thoại Query Properties, tại tab Usage, hãy đánh dấu vào ô Refresh data when opening the file.
4. Nhấn OK. Từ bây giờ, mỗi khi bạn mở file này, Excel sẽ tự động làm mới dữ liệu từ thư mục nguồn.

Xử lý các tình huống phức tạp và lỗi thường gặp khi gộp file Excel

Trong quá trình làm việc thực tế, bạn có thể gặp phải một số vấn đề. Dưới đây là cách chẩn đoán và khắc phục các lỗi phổ biến.

Lỗi “DataFormat.Error: Could not find file…”

Lỗi này xảy ra khi bạn đã di chuyển, đổi tên hoặc xóa thư mục nguồn. Power Query không còn tìm thấy đường dẫn đã được lưu.

• Cách khắc phục: Mở Power Query Editor (Data > Queries & Connections > Nhấp đúp vào truy vấn). Trong khung APPLIED STEPS, nhấp vào bước đầu tiên, thường có tên là “Source”. Trên thanh công thức, bạn sẽ thấy đường dẫn thư mục cũ. Nhấp vào biểu tượng bánh răng cưa bên cạnh bước “Source” và chọn lại đường dẫn thư mục chính xác.

Dữ liệu bị lệch cột hoặc lỗi “The column ‘[Tên cột]’ of the table wasn’t found”

Nguyên nhân chính là do có ít nhất một file trong thư mục nguồn không tuân thủ quy tắc thống nhất cấu trúc: tên tiêu đề cột bị sai, hoặc thiếu/thừa cột so với file mẫu. Để biết thêm các mẹo hay về Windows, bạn có thể tham khảo bài viết tại Thủ thuật Windows.

• Cách khắc phục: Lỗi này đòi hỏi sự kiên nhẫn. Bạn cần kiểm tra lại tất cả các file trong thư mục nguồn, so sánh cấu trúc cột của chúng với file mẫu. Hãy đặc biệt chú ý đến các khoảng trắng thừa hoặc các ký tự đặc biệt trong tiêu đề cột. Sau khi đã sửa lại file gây lỗi, hãy quay lại Excel và nhấn Refresh.

Gộp file từ các Sheet có tên khác nhau

Đây là một kịch bản nâng cao nhưng không hiếm gặp. Ví dụ, mỗi file báo cáo lại đặt tên sheet theo tháng (“Thang1”, “Thang2”,…). Lúc này, quy trình “Combine” tự động sẽ không hoạt động. Bạn cần can thiệp sâu hơn.

1. Bắt đầu lại từ Bước 1, nhưng ở cửa sổ xem trước danh sách file, hãy chọn Transform Data (thay vì Combine & Transform Data).
2. Trong Power Query Editor, bạn sẽ thấy một cột tên là Content, chứa giá trị “Binary”. Đây chính là nội dung của các file Excel.
3. Vào tab Add Column, chọn Custom Column.
4. Đặt tên cho cột mới là “Data” và nhập công thức: =Excel.Workbook([Content]). Nhấn OK.
5. Một cột mới tên “Data” sẽ xuất hiện, chứa giá trị “Table”. Nhấp vào nút có hai mũi tên đối nghịch ở tiêu đề cột này để mở rộng nó. Bỏ chọn “Use original column name as prefix” và nhấn OK.
6. Bây giờ bạn sẽ thấy tất cả các đối tượng (sheets, tables) từ tất cả các file. Bạn có thể lọc cột “Kind” để chỉ giữ lại “Sheet”, sau đó lọc cột “Name” nếu cần, và cuối cùng mở rộng cột “Data” cuối cùng để lấy dữ liệu.

Quy trình này phức tạp hơn nhưng cho bạn toàn quyền kiểm soát việc trích xuất dữ liệu từ các cấu trúc file không đồng nhất.

Kết luận

Việc gộp file Excel từ nhiều nguồn không còn là một cơn ác mộng tốn thời gian. Với Power Query, bạn đã có trong tay một công cụ tự động hóa mạnh mẽ, đáng tin cậy và linh hoạt. Bằng cách đầu tư một chút thời gian ban đầu để thiết lập quy trình và chuẩn hóa dữ liệu, bạn sẽ gặt hái được thành quả là hàng giờ làm việc hiệu quả, dữ liệu chính xác và các báo cáo luôn được cập nhật. Hãy bắt đầu áp dụng Power Query ngay hôm nay để nâng tầm kỹ năng Excel và giải phóng bản thân khỏi những công việc lặp đi lặp lại nhàm chán. Để tìm hiểu sâu hơn về ngôn ngữ M, bạn có thể tham khảo tài liệu chính thức từ Microsoft.