2026-09-08 · Power Query & Excel
Sàn báo doanh thu kỳ này là một con số. Ngân hàng báo về một con số khác. Sổ kế toán ghi một con số thứ ba. Cả ba đều đúng, và không con số nào bằng con số nào.
Đó là lý do việc đối chiếu tiền về từ sàn thương mại điện tử ngốn của kế toán vài buổi mỗi kỳ, rồi vẫn còn một khoản lệch không giải thích được. Phần lớn thời gian đó không mất vào việc dò, mà mất vào việc cộng nhầm một cột — file đối soát của sàn có nhiều bẫy nằm ngay trong cấu trúc của nó.
Bài này mổ một file đối soát thật, chỉ ra bốn cái bẫy khiến số tự nhân đôi hoặc tự hụt đi, rồi dựng quy trình sáu bước bằng Power Query để kỳ sau chỉ còn thay tệp và bấm Refresh.
File đối soát của sàn trông như thế nào
Đội ngũ EzTech dùng một file đối soát thật trong tài liệu giảng dạy: bản kết xuất doanh thu của một shop trên sàn, kỳ 45 ngày. Vì là dữ liệu thật của một shop nên bài này chỉ nêu tỷ lệ và cấu trúc, không nêu số tiền.
Ba thứ đập vào mắt ngay khi mở:
- Tiêu đề xếp ba tầng. Dòng 1 là nhóm lớn (Thông tin đơn hàng, Chi tiết doanh thu, Thông tin người mua, Thông tin tham chiếu), dòng 2 là nhóm con, dòng 3 mới là tên cột thật. Bấm "Use First Row as Headers" là hỏng ngay bước đầu.
- 43 cột có tên trên một sheet, trong đó hơn một nửa là các loại phí rải ngang: phí cố định, phí dịch vụ, phí thanh toán, hoa hồng tiếp thị liên kết, phí vận chuyển thực tế, phí vận chuyển người mua trả, phí vận chuyển được trợ giá, phí trả hàng…
- Phí không nằm hết ở một chỗ. Hai loại phí gói dịch vụ nằm ở một sheet riêng, khóa nối là mã đơn hàng.
Ngoài ra file còn một sheet tổng hợp, ghi sẵn ba dòng: tổng doanh thu, tổng chi phí, tổng số tiền. Đây là mốc đối chiếu quý nhất của cả file — giữ lại để kiểm cuối bài.
Bẫy 1: mỗi đơn có hai tầng dòng, cộng cả cột là gấp đôi
Cột thứ hai của sheet tên là "Đơn hàng / Sản phẩm", nhận đúng hai giá trị: Order và Sku. Mỗi đơn có một dòng Order tổng hợp, kèm một hoặc nhiều dòng Sku cho từng mặt hàng trong đơn.
Trên file này: 625 dòng dữ liệu, nhưng chỉ 240 mã đơn — 240 dòng Order và 385 dòng Sku.
Điểm chết người: cột "Tổng tiền đã thanh toán" ở tầng Sku lặp lại đúng giá trị của đơn, chứ không chia nhỏ theo mặt hàng. Cộng riêng tầng Order và cộng riêng tầng Sku ra hai con số bằng nhau đến từng đồng. Nghĩa là kéo chuột chọn cả cột rồi xem thanh trạng thái, bạn nhận đúng gấp đôi số thật.
Cách xử lý gọn nhất: lọc Order ngay sau bước nạp, trước mọi phép cộng. Chỉ giữ tầng Sku khi bạn thật sự cần bóc theo mặt hàng — và khi đó thì đừng cộng cột tiền của đơn.
Bẫy 2: không phải cột nào hai tầng cũng bằng nhau
Đây là chỗ mà việc "lọc đại một tầng cho xong" quay lại cắn.
Trên chính file đó, cột "Giá sản phẩm" cộng ở tầng Order và cộng ở tầng Sku ra hai con số khác nhau. Phần chênh không phải sai số làm tròn: nó bằng đúng tổng cột "Số tiền hoàn lại" của kỳ, ứng với 7 đơn có phát sinh hoàn tiền. Tầng Sku đã trừ khoản hoàn, tầng Order thì chưa.
Còn một cột phí nữa cũng lệch giữa hai tầng, với mức nhỏ hơn.
Nghĩa là: cùng một file, cùng một tên cột, chọn tầng khác nhau ra kết quả khác nhau — mà cả hai con số đều "trông hợp lý", không có gì báo cho bạn biết. Nếu bạn dựng báo cáo doanh thu từ tầng này còn dựng báo cáo hoàn hàng từ tầng kia, hai báo cáo sẽ không bao giờ khớp nhau.
Quy tắc rút ra: một báo cáo chỉ đọc một tầng, và ghi rõ tầng nào ngay trong tên query.
Bẫy 3: phí nằm ở sheet khác, ghép sai kiểu là mất đơn
Hai loại phí gói dịch vụ — gói voucher và gói ưu đãi phí vận chuyển — không nằm trên sheet doanh thu. Chúng ở một sheet riêng, mỗi dòng một mã đơn.
Phản xạ thông thường là Merge Queries theo mã đơn hàng rồi chọn Inner cho chắc. Trên file này, làm vậy là mất 7 đơn: chúng có doanh thu nhưng không phát sinh phí gói nào, nên không có dòng nào ở sheet phí.
Bảy đơn trên hai trăm bốn mươi thì tổng vẫn "trông gần đúng", và không dòng log nào báo rằng chúng đã biến mất.
Đúng phải là Left Outer: giữ toàn bộ đơn bên trái, đơn nào không có phí thì cột phí trả về null, rồi thay null bằng 0 ở bước sau. Nếu bạn dùng Table.RowCount để đếm trước và sau khi merge, số phải bằng nhau — đó là phép kiểm rẻ nhất cho bước này.
Bẫy 4: cột phí đã mang dấu âm sẵn, trừ thêm là nhân đôi
Mọi cột phí trong file đối soát này đã ghi số âm. Phí dịch vụ hiện ra là số âm, phí thanh toán số âm, hoa hồng số âm.
Nên công thức đúng là cộng, không phải trừ:
Tiền về = Tổng tiền đã thanh toán + (các cột phí, đang mang dấu âm)
Viết thành trừ, bạn trừ hai lần: một lần vì dấu âm sẵn có, một lần vì phép trừ. Kết quả ra thấp hơn thực tế đúng bằng tổng phí — và vì nó vẫn là một con số nhỏ hơn doanh thu nên nhìn qua rất hợp lý.
Cách kiểm: lấy ba dòng ở sheet tổng hợp cuối file. Tổng doanh thu cộng tổng chi phí phải ra đúng tổng số tiền. Trên file này phép cân đó khớp tuyệt đối. Nếu bạn tính ra khác, sai nằm ở phía bạn chứ không phải phía sàn.
Sáu bước dựng quy trình bằng Power Query
Sáu bước dưới đây dựng một lần, kỳ sau chỉ thay tệp mới vào thư mục rồi bấm Refresh.
Bước 1 — Nạp sheet doanh thu, cắt đúng hai dòng tiêu đề thừa
Data → Get Data → From File → From Workbook, chọn sheet doanh thu. Trong Power Query Editor: Home → Remove Rows → Remove Top Rows → 2, rồi Home → Use First Row as Headers. Làm ngược thứ tự này là tên cột thành "Column1, Column2".
Bước 2 — Lọc tầng dòng ngay lập tức
Bấm mũi tên cột "Đơn hàng / Sản phẩm" → bỏ chọn Sku, chỉ giữ Order. Đặt tên query rõ ràng, ví dụ DoanhThu_Order. Bước này phải đứng trước mọi bước tính toán.
Bước 3 — Giữ đúng cột cần và ép kiểu có Locale
Home → Choose Columns, giữ: mã đơn hàng, ngày hoàn thành thanh toán, tổng tiền đã thanh toán, và các cột phí. Bốn mươi ba cột mà bạn chỉ dùng chừng mười.
Cột ngày phải ép kiểu bằng Transform → Data Type → Using Locale → Date → English (United States) nếu file xuất theo định dạng tháng/ngày. Bỏ bước này thì ngày 03/12 và 12/03 đổi chỗ cho nhau mà không báo lỗi — chi tiết cách xử lý nằm ở bài sửa lỗi ngày tháng trong Power Query.
Bước 4 — Nạp sheet phí và gộp về một dòng một đơn
Nạp sheet phí gói dịch vụ, bỏ dòng tiêu đề thừa, rồi Transform → Group By theo mã đơn hàng, tính Sum cho từng cột phí. Bước gộp này bắt buộc: nếu sheet phí có nhiều dòng cho cùng một đơn mà bạn merge thẳng, đơn đó bị nhân bản ở bảng kết quả.
Bước 5 — Merge Left Outer rồi thay null bằng 0
Home → Merge Queries, khóa là mã đơn hàng, Join Kind chọn Left Outer. Expand hai cột phí. Sau đó chọn hai cột vừa expand → Transform → Replace Values → null → 0.
Đừng bỏ qua bước thay null: trong Power Query, một phép cộng có null tham gia sẽ trả về null cho cả dòng, và dòng đó biến mất khỏi mọi phép tổng phía sau.
Bước 6 — Cột tiền về và bảng theo ngày
Add Column → Custom Column, cộng cột tổng tiền đã thanh toán với tất cả cột phí. Đóng và nạp về Excel, dựng Pivot Table theo ngày hoàn thành thanh toán để có bảng "tiền về theo từng ngày" — đây chính là bảng đem đi khớp với sổ phụ ngân hàng.
Ba phép kiểm phải chạy trước khi tin kết quả
Bảng ra rồi vẫn chưa dùng được. Ba phép kiểm dưới đây mất thêm mười phút và bắt được gần hết lỗi:
- Cân theo sheet tổng hợp. Tổng doanh thu cộng tổng chi phí bạn tính ra phải bằng tổng số tiền sàn ghi ở sheet tổng hợp. Lệch bao nhiêu thì đi tìm bấy nhiêu, đừng làm tròn cho qua.
- Đếm đơn trước và sau merge. Số dòng sau bước 5 phải đúng bằng số dòng sau bước 2. Khác đi nghĩa là hoặc mất đơn (join sai kiểu), hoặc nhân đơn (quên Group By ở bước 4).
- Đếm mã đơn duy nhất. Trong Power Query, chọn cột mã đơn rồi xem dòng "Column profile" ở tab View — số Distinct phải bằng số dòng. Bằng nhau nghĩa là bạn đang ở đúng một tầng.
Sau ba phép kiểm này, phần lệch còn lại mới là lệch thật, và thường rơi vào ba nhóm: đơn của kỳ trước mới về tiền trong kỳ này, đơn đã bán nhưng sàn còn giữ, và khoản bồi thường hoặc điều chỉnh sàn ghi riêng.
Sau khi khớp thì làm gì với bảng đó
Bảng tiền về theo ngày dùng được ngay cho hai việc tiếp theo.
Một là hạch toán: từ bảng này chuyển thẳng sang tệp nhập khẩu đúng cấu trúc phần mềm kế toán, đẩy một lần cho cả kỳ thay vì gõ tay từng bút toán — cách làm cụ thể nằm ở bài hạch toán tự động bằng Power Query.
Hai là đọc số để biết lãi thật: cùng bộ dữ liệu đó cho biết mỗi đồng doanh thu bị giữ lại bao nhiêu, và phần nào trong đó là do chính shop chọn. Trên file này, tổng chi phí sàn chiếm khoảng 17,8% doanh thu của kỳ, chia ra: phí dịch vụ khoảng 9%, phí thanh toán khoảng 5,1%, phí cố định khoảng 3%, hoa hồng tiếp thị liên kết khoảng 0,6%, còn khối vận chuyển sau khi trừ phần được trợ giá chỉ còn khoảng 0,1%. Bài báo cáo doanh thu sàn: đọc đúng số để biết lãi thật đi sâu vào phần này.
Nếu công ty bạn bán trên nhiều sàn, quy trình trên lặp lại cho từng sàn rồi gộp bằng Append Queries. Mẫu báo cáo bán hàng trên sàn và phân tích doanh số trong kho báo cáo của EzTech xem trực tiếp được, để hình dung bảng cuối cùng nên có những gì.
Câu hỏi thường gặp
Sàn đổi mẫu file đối soát thì quy trình có chết không?
Bước nạp và bước lọc thì không. Bước dễ gãy nhất là Choose Columns ở bước 3, vì nó gọi tên cột. Sàn đổi tên hoặc bỏ một cột là query báo lỗi ngay tại bước đó — và đây là kiểu hỏng tốt, vì nó ồn ào chứ không im lặng. Sửa mất vài phút: mở lại bước 3, chọn lại cột.
Có cần Power BI không hay Excel là đủ?
Excel là đủ. Power Query nằm sẵn trong Excel từ bản 2016 trở đi, không phải cài thêm gì. Power BI chỉ cần khi bạn muốn nhiều người cùng xem một màn hình và tự lọc — xem thêm vì sao nên học Power Query trước.
File đối soát có dữ liệu cá nhân không?
Có. Cột thông tin người mua trong file này chứa số điện thoại và tên tài khoản của khách. Trước khi gửi file cho ai ngoài công ty, hoặc trước khi tải lên bất kỳ công cụ trực tuyến nào, hãy bỏ hẳn cột đó ở bước Choose Columns. Bài bảo mật dữ liệu khi dùng AI nói kỹ hơn về chuyện này.
Bao lâu thì dựng xong lần đầu?
Với người đã quen Power Query, khoảng một buổi cho sàn đầu tiên. Phần lâu nhất không phải thao tác mà là đọc hiểu ý nghĩa từng cột phí của sàn đó. Sàn thứ hai nhanh hơn nhiều vì cấu trúc na ná nhau.
Làm tiếp thế nào
Nếu bạn đang mất vài buổi mỗi kỳ cho việc đối soát sàn, hãy bắt đầu bằng một việc nhỏ: mở file đối soát kỳ gần nhất, đếm số mã đơn duy nhất rồi so với số dòng. Hai con số khác nhau nghĩa là mọi phép cộng bạn từng làm trên file đó đều cần xem lại.
Khóa DATA FINANCE của EzTech đi trọn phần Power Query chuyên sâu cho kế toán, làm trên đúng các bộ dữ liệu kiểu này. Cần tư vấn cho trường hợp cụ thể của công ty thì để lại thông tin ở trang liên hệ, đội ngũ EzTech sẽ trao đổi trực tiếp.
