2026-08-10 · Power Query & Excel
Ngày mùng 3 hàng tháng, quy trình của bạn quen tay đến mức nhắm mắt cũng làm được: tải file bán hàng từ phần mềm, tải tiếp file thu chi, mở file báo cáo tháng trước ra rồi lưu thành tên mới, dán dữ liệu vào, kéo lại VLOOKUP, kiểm tra vài ô bị lệch, refresh pivot, sửa lại tiêu đề tháng, in ra cho giám đốc. Hết một buổi chiều.
Tháng sau lại đúng chuỗi việc đó, không thêm không bớt. Vấn đề nằm ở chỗ này: những việc lặp lại y nguyên mỗi tháng là việc máy nên làm, không phải việc bạn nên làm. Và Excel đã có sẵn công cụ để làm, tên là Power Query.
Bài này đội ngũ EzTech chỉ cách dựng lại file báo cáo cuối tháng theo hướng tự động hóa báo cáo Excel: cuối tháng bạn bỏ file mới vào đúng thư mục, mở file báo cáo, bấm Refresh, xong.
Vì sao file báo cáo hiện tại không thể chỉ bấm một nút
Không phải vì bạn làm sai. Phần lớn file báo cáo trong doanh nghiệp Việt Nam được dựng theo lối thủ công vì nó có một ưu điểm rất thật: nhìn thấy ngay, sửa được ngay. Nhưng nó mang bốn đặc điểm khiến việc tự động hóa không thể diễn ra.
Dữ liệu và trình bày bị trộn vào nhau. Cùng một sheet vừa chứa dữ liệu thô dán vào, vừa chứa công thức, vừa chứa bảng trình bày có gộp ô và tô màu. Máy không biết đâu là phần được phép ghi lại.
Mỗi tháng là một file riêng. Tháng 7 một file, tháng 8 một file, đều "Save As" từ tháng trước. Muốn xem số cả năm phải mở 12 file rồi cộng tay.
Công thức trỏ vào vùng cứng. Kiểu =SUMIF(A2:A5000, ...). Tháng này dữ liệu có 5.200 dòng thì 200 dòng cuối rơi ra ngoài, âm thầm, không có cảnh báo nào.
Bước làm sạch nằm trong đầu bạn. Xóa 3 dòng tiêu đề của phần mềm, bỏ dòng "Tổng cộng" ở giữa, sửa cột ngày bị đảo ngày với tháng. Bạn nhớ hết, nhưng file thì không nhớ gì, nên tháng sau vẫn phải làm lại từ tay.
Nguyên tắc gốc: tách file báo cáo thành ba lớp
Đây là thay đổi tư duy quan trọng nhất, và cũng là thứ quyết định nút Refresh có hoạt động hay không. Một file báo cáo tự động luôn có ba lớp tách bạch, mỗi lớp một nhiệm vụ.
Lớp nguồn là một thư mục cố định trên máy, chứa các file bạn tải từ phần mềm kế toán hoặc từ sàn. Cuối tháng bạn chỉ làm đúng một việc ở lớp này: bỏ file mới vào.
Lớp xử lý là các query trong Power Query. Nó gom file, làm sạch, ghép danh mục, tạo cột phụ. Lớp này bạn dựng một lần, sau đó không mở ra nữa.
Lớp trình bày là pivot table, biểu đồ, các ô chỉ số trên trang báo cáo. Lớp này chỉ được đọc dữ liệu từ lớp xử lý, tuyệt đối không được gõ số vào.
Ranh giới giữa lớp 2 và lớp 3 là chỗ hầu hết file "tự động một nửa" bị vỡ: người làm dựng query rất tốt, nhưng rồi lại gõ tay vài con số vào bảng kết quả cho nhanh. Từ giây phút đó, mỗi lần Refresh là một lần mất số.
Bảy bước dựng file báo cáo chỉ cần Refresh
Bước 1: Tạo thư mục nguồn và quy ước tên file
Tạo một thư mục, ví dụ D:\BaoCao\NguonBanHang. Quy ước tên file phải đưa được thông tin kỳ vào chính tên: BanHang_2026-08.xlsx. Đặt tên kiểu năm rồi tháng có lý do rất thực dụng, đó là để sắp xếp theo tên cũng ra đúng thứ tự thời gian, và để lát nữa Power Query bóc kỳ báo cáo ra từ tên file.
Quan trọng: mọi file trong thư mục này phải cùng cấu trúc cột. Nếu phần mềm xuất ra hai loại báo cáo khác nhau, tách thành hai thư mục.
Bước 2: Kết nối tới cả thư mục, không kết nối tới từng file
Trong Excel, vào Data → Get Data → From File → From Folder, chọn thư mục vừa tạo, rồi bấm Transform Data. Đây là điểm khác biệt lớn nhất so với cách làm cũ: bạn kết nối tới thư mục, nên tháng sau thêm file mới vào là query tự nhận, không phải sửa gì. Cách gom nhiều file này đội ngũ EzTech đã viết chi tiết trong bài gộp nhiều file Excel bằng Power Query.
Bước 3: Làm sạch bằng các bước, không làm sạch bằng tay
Mọi việc bạn vẫn làm bằng tay giờ chuyển thành một bước trong Power Query, và bước đó được ghi lại vĩnh viễn:
- Xóa mấy dòng tiêu đề của phần mềm: Remove Top Rows, rồi Use First Row as Headers.
- Bỏ dòng tổng cộng chen giữa dữ liệu: lọc bỏ trên cột mã, hoặc Remove Blank Rows.
- Sửa kiểu dữ liệu: đặt cột ngày về Date, cột tiền về Decimal Number. Nếu ngày bị đảo ngày với tháng, dùng Using Locale và chọn English (United States) đúng như file gốc.
- Điền mã bị trống theo dòng trên: Fill Down. Bước này gần như luôn cần với file xuất từ phần mềm kế toán, xem thêm bài xử lý dữ liệu MISA trong Excel.
Bước 4: Ghép danh mục bằng Merge, bỏ hẳn VLOOKUP
Bảng giao dịch thường chỉ có mã hàng, mã khách. Tên hàng, nhóm hàng, tên khách nằm ở bảng danh mục. Thay vì kéo VLOOKUP, dùng Home → Merge Queries, chọn kiểu Left Outer. Sau khi merge, bấm nút mở rộng và chỉ lấy đúng vài cột cần dùng.
Lợi ích rất rõ khi dữ liệu nhiều: Merge không làm file chậm như vài nghìn công thức VLOOKUP, và nó không bao giờ bị kéo thiếu dòng.
Bước 5: Nạp về đúng chỗ
Bấm Close & Load To… và cân nhắc theo đúng vai của từng query:
- Query kết quả cuối: nạp thành PivotTable Report, hoặc nạp vào Data Model nếu dữ liệu vượt một triệu dòng hoặc bạn cần nhiều bảng liên kết.
- Query trung gian và query danh mục: chọn Only Create Connection. Đừng nạp chúng ra sheet, vừa nặng file vừa rối.
Bước 6: Dựng lớp trình bày mà không gõ số
Trên sheet báo cáo, dựng pivot và biểu đồ từ nguồn vừa nạp. Nếu cần lấy một con số riêng ra ô để trình bày, dùng GETPIVOTDATA hoặc SUMIFS trỏ vào tên bảng, ví dụ =SUMIFS(BanHang[ThanhTien], BanHang[Nhom], "Sữa"). Cách viết theo tên bảng này tự giãn ra khi dữ liệu dài thêm, nên tháng nào cũng đúng.
Bước 7: Bật refresh tự động khi mở file
Vào Data → Queries & Connections, chuột phải vào query, chọn Properties, tích Refresh data when opening the file. Từ đây, mở file là dữ liệu tự cập nhật. Muốn cập nhật giữa lúc đang làm thì bấm Data → Refresh All, hoặc Ctrl + Alt + F5.
Ba quy tắc giữ cho nút Refresh không vỡ
Không bao giờ sửa tay vào vùng dữ liệu do query nạp ra. Vùng đó là kết quả, mỗi lần Refresh sẽ bị viết lại toàn bộ. Cần thêm ghi chú hay cột phân loại riêng thì thêm bước trong Power Query, hoặc tạo một bảng phụ rồi Merge vào.
Không trỏ công thức vào địa chỉ ô của bảng kết quả. Tháng này dòng "Doanh thu miền Bắc" nằm ở dòng 14, tháng sau có thêm một nhóm hàng thì nó xuống dòng 15, còn công thức vẫn trỏ dòng 14. Trỏ theo tên bảng và tên cột thì không gặp chuyện này.
Không chôn đường dẫn thư mục vào query. Dùng Home → Manage Parameters → New Parameter tạo một tham số đường dẫn, rồi sửa bước Source để dùng tham số đó. Khi đổi máy hoặc đổi thư mục, bạn sửa một chỗ duy nhất thay vì mở từng query.
Bốn tình huống nút Refresh không cứu được
Tự động hóa không có nghĩa là không bao giờ lỗi. Nhưng lỗi sẽ hiện ra rõ ràng, và đây là bốn nguyên nhân chiếm gần hết số lần bị vỡ.
File nguồn đổi tên cột. Phần mềm nâng cấp, cột "Thành tiền" thành "Thanh tien". Power Query báo ngay rằng không tìm thấy cột. Cách phòng: sau bước promote header, thêm bước Rename Columns đưa tên về bộ tên chuẩn của bạn, và bám vào bộ tên đó ở mọi bước sau.
File nguồn thêm hoặc bớt cột. Nếu query của bạn chọn cột theo kiểu "giữ 8 cột này", thêm cột là lệch hết. Với bảng dạng nhiều tháng nằm ngang, luôn dùng Unpivot Other Columns thay vì chọn cứng từng cột, chi tiết ở bài unpivot trong Power Query.
File nguồn đang bị mở hoặc bị khóa. Một file đang mở bởi người khác trên thư mục chung có thể làm bước đọc file lỗi. Quy ước đơn giản: thư mục nguồn chỉ để chứa file đã tải xuống, không ai mở ra sửa trong đó.
Thư mục nguồn nằm trên OneDrive hoặc SharePoint chưa tải về máy. File hiện icon đám mây tức là nó chưa có trên đĩa, Power Query đọc có thể lỗi. Bấm chuột phải chọn giữ file luôn trên thiết bị, hoặc để thư mục nguồn ở ổ cục bộ. Các lỗi hay gặp khác cùng cách đọc thông báo, đội ngũ EzTech đã tổng hợp trong bài 7 lỗi Power Query thường gặp.
Khi nào nên dừng ở Excel, khi nào nên lên Power BI
Không phải cứ tự động là phải lên Power BI. Excel với Power Query xử lý rất tốt các bài toán sau: một hoặc hai nguồn dữ liệu, một người làm báo cáo, kết quả cần gửi đi dạng file, và dữ liệu dưới khoảng vài trăm nghìn dòng mỗi kỳ.
Nên tính tới Power BI khi báo cáo cần chia cho nhiều người xem cùng lúc và mỗi người xem một phạm vi riêng, khi dữ liệu nhiều kỳ tích lại thành hàng triệu dòng, hoặc khi bạn cần lịch tự động làm mới dữ liệu theo giờ. Điểm dễ chịu là kỹ năng Power Query bạn vừa học dùng lại nguyên trong Power BI, không phải học lại, xem thêm bài vì sao nên học Power Query trước Power BI.
Câu hỏi thường gặp
Dựng lần đầu mất bao lâu?
Một báo cáo có một nguồn dữ liệu, đã quen tay thì khoảng một buổi. Lần đầu chưa quen thì mất hơn, và gần như chắc chắn bạn sẽ phải sửa lại vài lần trong tháng đầu khi gặp các trường hợp dữ liệu lạ. Đó là bình thường, vì mỗi lần sửa là một bước được ghi lại, tháng sau không phải làm lại.
Đồng nghiệp mở file trên máy khác có chạy được không?
Chạy được nếu máy đó cũng thấy thư mục nguồn theo đúng đường dẫn. Đây chính là lý do nên đặt đường dẫn thành tham số. Với nhóm nhiều người, cách bền là để dữ liệu nguồn trên thư mục dùng chung của công ty và mọi người trỏ tới cùng đường dẫn.
Excel bản nào làm được?
Power Query có sẵn trong Excel 2016 trở lên và Microsoft 365, nằm ở tab Data với tên Get & Transform. Excel 2010 và 2013 cần cài add-in riêng. Bản Excel cho web hiện chưa dựng được query, nên hãy làm trên bản cài trên máy.
Cuối tháng vẫn phải tải file thủ công từ phần mềm, vậy có gọi là tự động không?
Bước tải file vẫn là tay, và phần lớn doanh nghiệp dừng ở mức đó. Nhưng chuỗi việc sau khi có file, tức là phần chiếm gần hết thời gian và cũng là phần dễ ra số sai nhất, thì đã tự động. Nếu phần mềm của bạn cho phép kết nối trực tiếp vào cơ sở dữ liệu, Power Query nối thẳng vào đó được và khi ấy bước tải file cũng biến mất.
Bắt đầu từ đâu
Đừng dựng lại tất cả báo cáo cùng lúc. Chọn đúng một báo cáo bạn làm hàng tháng và làm lâu nhất, dựng lại theo ba lớp ở trên. Khi tháng sau bạn bấm Refresh và thấy số ra đúng, bạn sẽ tự biết nên chuyển tiếp báo cáo nào.
Muốn kiểm tra nền Power Query của mình đang ở đâu, bạn có thể làm bài trắc nghiệm Power Query miễn phí của đội ngũ EzTech. Nếu muốn học bài bản theo đúng bài toán kế toán và tài chính, từ làm sạch dữ liệu trong Excel tới báo cáo trên Power BI, hãy xem khóa DATA FINANCE. Còn nếu bạn muốn hình dung đích đến, 41 báo cáo mẫu trên web đều xem demo trực tiếp được.
