Cuối mỗi tuần, nhiều phòng kế toán và phòng kinh doanh nhận về một loạt file Excel: mỗi cửa hàng một file doanh số, mỗi kho một file tồn, mỗi nhân viên một file chấm công. Việc tiếp theo quen thuộc đến mức không ai nghĩ đến chuyện thay đổi: mở từng file, bôi đen, sao chép, dán xuống cuối bảng tổng, rồi lặp lại mười lần, hai mươi lần. Mất một buổi chiều, và chỉ cần dán lệch một dòng là số tổng sai mà không ai phát hiện.
Power Query có sẵn trong Excel trên Windows từ bản 2016 trở đi (bản 2010 và 2013 cài thêm dưới dạng phần bổ trợ) làm được đúng việc này theo cách khác hẳn: bạn chỉ cho nó một thư mục, nó đọc mọi file trong đó, xếp chồng lên nhau thành một bảng, và nhớ các bước. Tuần sau bỏ file mới vào thư mục, bấm làm mới, bảng tổng tự cập nhật. Bài này đi từng bước, chỉ ra chỗ AI giúp được và những lỗi khiến việc gộp hỏng sau vài tuần chạy.
Khi nào gộp bằng thư mục là đúng cách
Không phải tình huống nào có nhiều file cũng nên dùng cách lấy dữ liệu từ thư mục. Cách này phát huy tác dụng khi các file có chung một khuôn, đến đều đặn và bạn cần gộp lại nhiều lần. Đánh dấu xem việc của bạn có những đặc điểm sau không:
- Các file có cùng cấu trúc: cùng tên cột, cùng thứ tự hoặc ít nhất cùng bộ cột, cùng tên trang tính hoặc cùng một bảng được đặt tên.
- File đến theo nhịp lặp lại: hằng ngày, hằng tuần, hằng tháng, và số lượng file có thể tăng khi mở thêm chi nhánh.
- Bạn cần biết dòng nào đến từ file nào, để khi có số lạ còn quay lại hỏi đúng người gửi.
- Tổng số dòng sau khi gộp vượt quá mức sao chép tay thoải mái, ví dụ vài nghìn dòng trở lên.
- Người làm việc này có thể thay đổi, nên quy trình phải nằm trong file chứ không nằm trong trí nhớ của một người.
Nếu chỉ gộp hai file một lần duy nhất, sao chép tay vẫn nhanh hơn. Nếu các file mỗi nơi một kiểu, cột đặt tên tuỳ hứng, việc đầu tiên không phải là gộp mà là thống nhất mẫu file với các chi nhánh; bài về đặt tên tệp và thư mục trong cùng danh mục này bàn kỹ phần đó. Power Query gộp tốt khi đầu vào đã có kỷ luật, và nó sẽ phơi bày ngay những chỗ thiếu kỷ luật.
Chuẩn bị thư mục và file mẫu trước khi mở Power Query
Phần lớn sự cố khi gộp file đến từ khâu chuẩn bị chứ không phải từ Power Query. Dành mười lăm phút cho các bước dưới đây sẽ tiết kiệm nhiều giờ sửa lỗi về sau.
- Tạo một thư mục riêng chỉ để chứa file cần gộp, ví dụ thư mục "Doanh so tuan" trên ổ chung. Không để lẫn file khác, không để file tổng nằm trong chính thư mục này, vì Power Query sẽ đọc cả nó và nhân đôi dữ liệu.
- Chọn một file chuẩn làm mẫu, kiểm xem dòng tiêu đề nằm ở dòng mấy, có dòng tiêu đề phụ hay ô gộp phía trên không. Tốt nhất mỗi file chỉ có một bảng bắt đầu từ ô A1.
- Thống nhất tên trang tính, ví dụ trang nào cũng tên "DuLieu". Nếu chi nhánh đặt "Sheet1", "Tháng 10", "Báo cáo" lẫn lộn, Power Query sẽ không biết lấy trang nào.
- Đặt tên file có quy luật, ví dụ mã chi nhánh và ngày theo dạng năm tháng ngày. Tên file sau này sẽ thành một cột trong bảng gộp, nên tên có quy luật là có thêm thông tin miễn phí.
- Sao chép vài file thật sang một thư mục thử nghiệm và làm toàn bộ bài này trên thư mục đó trước. Khi chạy ổn mới trỏ sang thư mục thật.
Một chi tiết hay bị bỏ qua: khi ai đó đang mở file trên máy dùng chung, Excel tạo ra một file tạm có tên bắt đầu bằng dấu ngã và dấu đô la. Power Query nhìn thấy file này và báo lỗi không đọc được. Bước lọc file ở phần sau sẽ xử lý chuyện này.
Các bước gộp trong Excel
Tên menu dưới đây theo Excel bản tiếng Anh trên Windows; bản tiếng Việt hoặc bản cập nhật mới có thể đặt chữ khác một chút, bạn đối chiếu theo biểu tượng và vị trí.
- Mở một file Excel mới làm file tổng, vào thẻ Data, chọn Get Data, From File, From Folder, rồi trỏ tới thư mục đã chuẩn bị.
- Cửa sổ xem trước liệt kê các file trong thư mục kèm cột Name, Extension, Date modified. Chọn Combine & Transform Data thay vì Load ngay, để còn chỉnh trước khi đưa vào bảng.
- Excel hỏi lấy đối tượng nào trong file mẫu: chọn trang tính hoặc bảng có tên thống nhất ở bước chuẩn bị.
- Power Query Editor mở ra với bảng đã xếp chồng. Ở khung bên trái sẽ thấy thêm vài truy vấn phụ như Sample File và Transform Sample File; đây là nơi chứa các bước áp dụng cho từng file, đừng xoá.
- Thêm bước lọc bỏ file tạm: ở truy vấn chính, lọc cột Name để loại các tên bắt đầu bằng dấu ngã, và lọc Extension chỉ giữ .xlsx.
- Giữ lại cột tên file (thường có tên Source.Name), đổi tên thành "Tep nguon" để biết dòng nào từ đâu.
- Kiểm kiểu dữ liệu từng cột: ngày là kiểu ngày, số tiền là kiểu số, mã hàng và số điện thoại là kiểu chữ.
- Chọn Close & Load To, đưa kết quả vào một trang tính mới dưới dạng bảng.
Từ đây trở đi, mỗi lần có file mới, bạn chỉ cần bỏ file vào thư mục rồi vào Data, chọn Refresh All. Nếu Excel trên máy bạn là bản Mac hoặc bản dùng trên trình duyệt, khả năng lấy dữ liệu từ thư mục có thể hạn chế hơn hoặc khác cách làm; hãy kiểm tài liệu của phiên bản bạn đang dùng.
AI giúp được gì trong quá trình này
Giải thích bước lạ. Khi Power Query tự sinh ra các truy vấn phụ với tên khó hiểu, bạn có thể mở Advanced Editor, sao chép đoạn mã M, dán vào chatbot và hỏi: "Giải thích từng dòng đoạn mã Power Query này cho người không biết lập trình, dòng nào quyết định lấy trang tính nào". Đoạn mã này chỉ chứa tên cột và tên bước, thường không có dữ liệu khách hàng, nhưng vẫn nên xem lại trước khi dán vì đường dẫn thư mục có thể lộ tên công ty hoặc tên người.
Gợi ý bước xử lý. Mô tả bằng lời: "Sau khi gộp, cần bỏ các dòng trống, bỏ dòng có chữ Tổng cộng ở cột A, tách cột Tep nguon lấy ba ký tự đầu làm mã chi nhánh". AI sẽ trả lời bằng chuỗi thao tác trên giao diện hoặc bằng mã M. Nên yêu cầu cả hai, rồi làm theo giao diện để tự thấy từng bước hiện ra ở khung Applied Steps.
Đọc thông báo lỗi. Lỗi của Power Query thường viết bằng tiếng Anh khá cụt. Chép nguyên câu báo lỗi, kèm mô tả bạn vừa làm gì, AI giải thích khá tốt nguyên nhân thường gặp. Điều nó không biết là file của chi nhánh nào đang sai mẫu; phần đó bạn phải tự mở ra xem.
Viết hướng dẫn bàn giao. Khi đã chạy ổn, nhờ AI soạn một trang hướng dẫn từ danh sách bước bạn đã làm, để người khác thay bạn khi nghỉ phép. Đọc lại và sửa tên thư mục cho đúng thực tế.
Năm lỗi hay gặp sau vài tuần chạy và cách sửa
Gộp file chạy đẹp tuần đầu không có nghĩa là sẽ chạy đẹp mãi. Các lỗi dưới đây xuất hiện khi người gửi file thay đổi thói quen, và cách sửa đều nằm ở khâu kỷ luật đầu vào hơn là ở Power Query.
| Biểu hiện | Nguyên nhân thường gặp | Cách xử lý |
|---|---|---|
| Làm mới báo lỗi không tìm thấy trang tính | Một chi nhánh đổi tên trang tính hoặc gửi file có nhiều trang | Thống nhất tên trang, hoặc chuyển sang lấy theo bảng có tên; nhắc chi nhánh dùng file mẫu |
| Một cột toàn ô trống ở một số dòng | File đó đặt tên cột khác, ví dụ "Doanh thu" thay vì "Doanh so" | Đổi tên cột trong file nguồn; Power Query ghép theo tên cột chứ không theo vị trí |
| Số dòng tăng gấp đôi | File tổng hoặc bản sao file cũ nằm trong thư mục nguồn | Giữ thư mục nguồn chỉ chứa file cần gộp; thêm bước lọc theo tên |
| Báo lỗi kiểu dữ liệu ở cột ngày | Có file ghi ngày dạng chữ hoặc theo thứ tự tháng trước ngày sau | Đặt kiểu ngày với tuỳ chọn vùng phù hợp; sửa file mẫu cho đúng |
| Làm mới chạy rất chậm | Thư mục chứa file của nhiều năm | Chuyển file cũ sang thư mục lưu trữ; chỉ gộp kỳ đang làm việc |
Mỗi lần sửa lỗi, nên ghi một dòng vào nhật ký: ngày, lỗi gì, file nào, sửa thế nào. Sau vài tháng, nhật ký này cho thấy chi nhánh nào hay gửi file sai mẫu, và đó là thông tin để nói chuyện với họ một lần thay vì sửa tay mỗi tuần.
Kiểm tra kết quả gộp trước khi tin
Bảng gộp ra trông gọn gàng không có nghĩa là đủ. Cách kiểm đơn giản nhất là đếm: tổng số dòng của bảng gộp phải bằng tổng số dòng dữ liệu của các file cộng lại. Bạn có thể tạo một bảng phụ đếm số dòng theo cột "Tep nguon" bằng PivotTable hoặc hàm COUNTIF, rồi so với số dòng thực trong vài file mở ra kiểm tay.
Kiểm thêm một con số tổng. Ví dụ giả định: file của chi nhánh A có tổng doanh số ở dòng cuối là một con số cụ thể; trong bảng gộp, lọc theo tên file của chi nhánh A rồi cộng cột doanh số, hai con số phải khớp. Nếu lệch, khả năng cao là file nguồn có dòng tổng cộng bị đọc như một dòng dữ liệu, hoặc có dòng bị lọc mất vì ô trống.
Cuối cùng, nhìn vào cột ngày: có ngày nào nằm ngoài kỳ đang gộp không. Một file tuần trước bị gửi lại với tên mới là lỗi rất phổ biến, và chỉ lộ ra khi bạn nhìn khoảng ngày.
Ba phép kiểm này chỉ tốn vài phút mỗi tuần và nên làm thành thói quen ngay sau mỗi lần làm mới. Bảng đã sạch và đủ rồi thì việc đọc ý nghĩa con số, vẽ biểu đồ hay so sánh chi nhánh thuộc về khâu phân tích; trang AI Phân Tích của cùng hệ viết riêng phần đó.
Giữ an toàn cho dữ liệu khi gộp và khi hỏi AI
Power Query chỉ đọc file nguồn, không sửa chúng, nên bản thân việc gộp khá an toàn. Rủi ro nằm ở hai chỗ khác. Thứ nhất là file tổng: nó chứa dữ liệu của mọi chi nhánh trong một chỗ, nên quyền xem file này phải chặt hơn từng file con. Đừng gửi file tổng qua nhóm chat chung chỉ vì tiện.
Thứ hai là khi nhờ AI. Nếu bạn muốn AI xem dữ liệu để gợi ý bước xử lý, chỉ dán vài dòng mẫu đã thay tên khách hàng, số điện thoại, số tài khoản bằng giá trị giả. AI cần biết cấu trúc và kiểu dữ liệu, không cần biết khách thật là ai. Một câu lệnh mẫu: "Dưới đây là năm dòng mẫu đã che thông tin, gồm các cột Ngày, Ma CN, Ma hang, So luong, Doanh so. Đề xuất các bước Power Query để bỏ dòng tổng cộng và chuẩn hoá cột Ma hang về chữ hoa."
Nếu công ty có quy định riêng về công cụ AI được phép dùng, làm theo quy định đó. Những vấn đề sâu hơn về bảo vệ dữ liệu cá nhân có ở trang AI Bảo Mật trong cùng hệ.
Câu hỏi thường gặp
Power Query có làm thay đổi file gốc của các chi nhánh không?
Không. Power Query chỉ đọc dữ liệu từ file nguồn rồi tạo kết quả trong file tổng của bạn. File của chi nhánh giữ nguyên. Tuy vậy, nếu ai đó mở file nguồn đúng lúc bạn làm mới, có thể gặp lỗi khoá file, chỉ cần chờ họ đóng rồi làm lại.
Các file có cột xếp khác thứ tự thì có gộp được không?
Được, vì Power Query ghép theo tên cột. Điều quan trọng là tên cột phải giống hệt nhau, kể cả dấu và khoảng trắng. Khi gộp từ thư mục, danh sách cột lấy theo file mẫu, nên cột đặt tên khác ở một file sẽ bị bỏ qua và cột tương ứng của file đó toàn ô trống; hãy kiểm ô trống theo từng file sau khi gộp.
File CSV và file Excel lẫn trong một thư mục thì làm sao?
Nên tách thành hai thư mục và hai truy vấn, rồi nối lại bằng chức năng Append nếu cấu trúc giống nhau. Gộp lẫn hai loại trong một truy vấn dễ sinh lỗi vì cách đọc CSV và Excel khác nhau.
Thư mục nằm trên OneDrive hoặc ổ mạng có dùng được không?
Dùng được, nhưng đường dẫn phải ổn định trên máy của người làm mới. Nếu mỗi người đồng bộ OneDrive về một đường dẫn khác nhau, truy vấn sẽ lỗi trên máy người kia. Nên thống nhất một đường dẫn hoặc dùng tham số để đổi đường dẫn ở một chỗ.
Có cần biết lập trình để dùng Power Query không?
Không cần. Phần lớn thao tác làm trên giao diện bằng chuột, Power Query tự ghi lại thành mã M. Bạn chỉ cần đọc mã khi muốn sửa nâng cao, và lúc đó có thể nhờ AI giải thích từng dòng.
Google Sheets có chức năng tương tự không?
Google Sheets không có Power Query. Có thể gộp nhiều bảng bằng IMPORTRANGE kết hợp QUERY hoặc dùng Apps Script, nhưng cách làm khác và có giới hạn riêng. Nếu nhóm bạn dùng Google Sheets, xem các bài về Apps Script trong danh mục này.