Power Query cho kế toán giúp gom file, chuẩn hóa dữ liệu và đối chiếu hai bảng theo một quy trình có thể làm mới. Với công nợ, bạn cần đưa hai nguồn về cùng kỳ, cùng mã khách và cùng cách tính số dư trước khi ghép bảng tìm chênh lệch. Bài này hướng dẫn một ví dụ nhỏ để bạn kiểm tra được từng kết quả.
Nên bắt đầu từ việc kế toán nào?
Hãy chọn công việc lặp lại có cấu trúc đầu vào tương đối ổn định: gom bảng bán hàng của các cửa hàng, chuẩn hóa ngày chứng từ, đối chiếu số dư khách hàng hoặc tổng hợp phát sinh theo tài khoản. Power Query lưu các bước biến đổi thành truy vấn; khi nguồn thay đổi, bạn làm mới để thực hiện lại những bước đó.
Công cụ không tự hiểu nghiệp vụ. Nếu một bảng ghi số tiền trước thuế, bảng kia gồm thuế, phép ghép đúng kỹ thuật vẫn tạo kết quả không dùng được. Trước khi mở trình soạn thảo, bạn nên ghi rõ mỗi dòng đại diện cho điều gì, dữ liệu chốt đến ngày nào và số tiền đang mang ý nghĩa gì.
Ví dụ dưới đây dùng số dư phải thu cuối kỳ theo khách hàng. Đây là dữ liệu giả lập để học thao tác, không phải số liệu doanh nghiệp hay hướng dẫn hạch toán. Quy trình có thể thực hiện trong Excel hoặc Power BI; vị trí lệnh có thể khác theo phiên bản.
Chuẩn bị hai bảng trước khi đối chiếu
Chốt cùng kỳ và cùng cách tính số dư
Giả sử bạn đối chiếu sổ công ty với bảng xác nhận ngày 30/09/2026. Cả hai cần là số dư cuối ngày này, cùng đơn vị đồng và cùng quy ước dấu. Khoản khách trả trước, bù trừ công nợ hoặc giao dịch ghi nhận sau ngày chốt cần được giải thích riêng, không tự cộng vào cho khớp.
Nếu sổ có nhiều dòng chi tiết, cần biết đó là các khoản phải thu còn lại có thể cộng được. Không cộng tất cả ảnh chụp số dư cuối ngày của một khách qua nhiều ngày: bạn sẽ cộng lặp cùng một khoản nợ. Khi đối chiếu theo hóa đơn, khóa phải bổ sung số hóa đơn và các trường phân biệt cần thiết.
Chuẩn hóa mã và kiểu dữ liệu
Giữ mã khách ở kiểu Text, nhất là mã có số 0 đầu. Xóa khoảng trắng thừa bằng Trim, thống nhất chữ hoa nếu quy ước mã không phân biệt hoa thường. Ngày và số tiền phải được chuyển về kiểu phù hợp với định dạng nguồn; số tiền dạng chuỗi có dấu phân cách cần kiểm tra locale trước khi tính tổng.
Không ghép theo tên khách nếu đã có mã ổn định. Cùng một đơn vị có thể xuất hiện dưới tên viết tắt và tên đầy đủ. Với mã trống, hãy tách thành danh sách lỗi để xử lý; ghép hàng loạt dòng thiếu mã có thể tạo những cặp khớp không mang ý nghĩa nghiệp vụ.
Nếu chưa quen giao diện, bạn có thể đọc hướng dẫn mở và dùng Power Query Editor trước. Trong Excel, đưa vùng dữ liệu thành Table rồi chọn Data → From Table/Range; đặt tên rõ cho hai truy vấn nguồn.
Quy trình đối chiếu công nợ bằng giao diện
- Nạp hai nguồn: đặt tên SoCongTy và XacNhan. Lọc cùng ngày chốt và giữ mã khách, số tiền cùng các cột truy vết cần thiết.
- Kiểm tra đầu vào: xử lý mã trống, lỗi chuyển kiểu, dòng tổng cộng và dòng không thuộc phạm vi. Ghi lại tổng tiền trước khi biến đổi.
- Đưa về cùng cấp chi tiết: dùng Group By theo MaKH và Sum cột số tiền khi các dòng là những khoản có thể cộng. Nếu bảng xác nhận đã có một dòng mỗi khách, vẫn kiểm tra mã trùng.
- Ghép hai bảng: chọn Merge Queries as New, khóa MaKH, kiểu Full Outer. Mở rộng mã khách và số tiền bên xác nhận, giữ cả mã phía phải để nhận diện khách chỉ có bên đó.
- Phân loại: kiểm tra nguồn hiện diện trước, sau đó mới tính số tiền chênh lệch và phân biệt khớp số với lệch số.
Full Outer giữ các dòng của cả hai phía, giúp bạn thấy khách chỉ xuất hiện trong một nguồn. Nếu dùng Left Outer từ sổ công ty, khách chỉ có ở bảng xác nhận sẽ không được đưa vào kết quả. Với danh sách chỉ cần tìm mã chưa có phía đối tác, bạn có thể dùng Left Anti rồi đổi chiều để kiểm tra phía còn lại.
Hãy đọc thêm cách phân biệt Merge và Append nếu bạn hay nhầm nối ngang với xếp dòng. Điểm cần kiểm soát ở đây là tính duy nhất của khóa sau tổng hợp: hai phía cùng lặp mã sẽ làm số dòng tăng khi mở rộng.
Mã M tự chứa để thực hành
Trong Power Query Editor, tạo Blank Query, mở Advanced Editor và thay toàn bộ nội dung bằng mã sau. Hai bảng nguồn được khai báo ngay trong truy vấn nên không cần file bên ngoài. Số tiền trong code là số nguyên đồng, không kèm dấu phân cách hàng nghìn.
let
So = #table(
type table [MaKH = text, SoDu = number],
{{"KH01", 3000000}, {"KH01", 2000000},
{"KH02", 2400000}, {"KH03", 1000000}}
),
XacNhan = #table(
type table [MaKH = text, SoDu = number],
{{"KH01", 5000000}, {"KH02", 2300000},
{"KH04", 700000}}
),
GomSo = Table.Group(So, {"MaKH"},
{{"SoDuSo", each List.Sum([SoDu]), type number}}),
GomXN = Table.Group(XacNhan, {"MaKH"},
{{"SoDuXN", each List.Sum([SoDu]), type number}}),
Ghep = Table.NestedJoin(GomSo, {"MaKH"},
GomXN, {"MaKH"}, "XN", JoinKind.FullOuter),
MoRong = Table.ExpandTableColumn(Ghep, "XN",
{"MaKH", "SoDuXN"}, {"MaKH_XN", "SoDuXN"}),
MaChung = Table.AddColumn(MoRong, "KhachHang",
each if [MaKH] = null then [MaKH_XN] else [MaKH], type text),
TrangThai = Table.AddColumn(MaChung, "TrangThai",
each if [MaKH] = null then "Chi co xac nhan"
else if [MaKH_XN] = null then "Chi co so"
else if [SoDuSo] = [SoDuXN] then "Khop"
else "Lech", type text),
ChenhLech = Table.AddColumn(TrangThai, "ChenhLech",
each (if [SoDuSo] = null then 0 else [SoDuSo])
- (if [SoDuXN] = null then 0 else [SoDuXN]), type number),
KetQua = Table.SelectColumns(ChenhLech,
{"KhachHang", "SoDuSo", "SoDuXN", "ChenhLech", "TrangThai"}),
SapXep = Table.Sort(KetQua, {{"KhachHang", Order.Ascending}})
in
SapXepMã này giả định nguồn đã được kiểm tra, mã không trống và số dư đều hợp lệ. Các dòng KH01 ở sổ là hai khoản phải thu riêng có thể cộng. Việc gom bảng xác nhận chỉ phù hợp nếu các dòng ở đó cũng là những khoản độc lập; bản xác nhận bị nhập lặp phải được điều tra, không dùng Group By để che lỗi.
Trạng thái được xác định bằng sự hiện diện của mã ở từng nguồn. Chỉ trong phép tính chênh lệch, giá trị thiếu do phép ghép mới được quy về 0 để lượng hóa phần chưa đối ứng. Cách này vẫn giữ được ý nghĩa “thiếu nguồn”, thay vì biến một khách thiếu dữ liệu thành khách có số dư bằng không.
Kết quả nào cho thấy truy vấn đang đúng?
| Khách | Sổ công ty | Xác nhận | Chênh lệch | Trạng thái |
|---|---|---|---|---|
| KH01 | 5.000.000 | 5.000.000 | 0 | Khớp |
| KH02 | 2.400.000 | 2.300.000 | 100.000 | Lệch |
| KH03 | 1.000.000 | Thiếu | 1.000.000 | Chỉ có sổ |
| KH04 | Thiếu | 700.000 | -700.000 | Chỉ có xác nhận |
Tổng sổ là 8.400.000 đồng, tổng xác nhận là 8.000.000 đồng; tổng chênh lệch phải bằng 400.000 đồng. Đồng thời, kết quả có bốn mã khách riêng biệt. Bạn cần đối chiếu cả từng khách lẫn tổng, vì hai sai lệch ngược dấu có thể bù nhau khiến tổng nhìn vẫn đúng.
Khi thay bằng dữ liệu thật, hãy chọn vài khách để truy ngược chứng từ. Một khách khớp tổng vẫn có thể có hóa đơn thiếu và thanh toán ghi nhầm bù nhau. Nếu nghiệp vụ yêu cầu xác nhận theo hóa đơn, cần xây lại khóa và cấp chi tiết tương ứng.
Làm mới hàng tháng mà không mất dấu kiểm tra
Giữ riêng truy vấn nguồn và truy vấn kết quả; lưu file đầu vào theo kỳ, tránh sửa đè bản đã dùng để đối chiếu. Nếu nhiều file cùng cấu trúc, áp dụng quy trình gộp file Excel từ thư mục, nhưng chỉ đưa vào đúng kỳ và tránh nạp cả file kết quả.
Sau mỗi lần Refresh, kiểm tra ngày chốt, số dòng, số mã riêng biệt và tổng tiền của hai nguồn. Đừng tự động xóa mọi dòng lỗi hay thay toàn bộ null bằng 0: những dòng đó thường chính là danh sách cần điều tra. Ghi chú xử lý nên lưu ở bảng riêng theo khóa ổn định, rồi ghép vào kết quả.
Câu hỏi thường gặp
Có cần biết lập trình mới dùng được không?
Bạn có thể bắt đầu bằng các lệnh đổi kiểu, Group By và Merge trên giao diện. Mã M giúp nhìn rõ quy trình và tái sử dụng, nhưng hiểu cấu trúc dữ liệu và nghiệp vụ đối chiếu vẫn là điều cần trước.
Power Query có sửa dữ liệu trong sổ nguồn không?
Các bước biến đổi thông thường tạo kết quả truy vấn mà không sửa bảng nguồn. Bạn cần quay lại quy trình kế toán để điều tra và xử lý sai lệch; bảng chênh lệch chỉ là căn cứ kiểm tra.
Tại sao ghép xong số tiền bị tăng?
Hãy kiểm tra khóa trùng ở cả hai phía và cấp chi tiết của từng bảng. Một mã khớp nhiều dòng sẽ tạo nhiều kết quả khi mở rộng; tổng hợp đúng cấp hoặc bổ sung khóa trước khi ghép.
Có thể áp dụng sang Power BI không?
Có thể dùng cùng nguyên tắc chuẩn hóa và đối chiếu trong Power Query của Power BI. Khi thay nguồn thực tế, bạn cần cấu hình kết nối, quyền truy cập và cách làm mới phù hợp môi trường triển khai.
Nếu bạn cần người thiết kế quy trình dữ liệu và báo cáo công nợ, hãy chuẩn bị cấu trúc hai bảng, ngày chốt cùng quy tắc đối chiếu rồi tham khảo báo giá làm Power BI trên SenData. Yêu cầu rõ sẽ giúp freelancer xác định đúng phạm vi cần làm.
Sẵn sàng xây dựng dashboard Power BI?
Kết nối với freelancer Power BI phù hợp, xem báo cáo nhúng ngay trên trình duyệt và tải file .pbix về sau khi thanh toán.
Đào Ngọc Tú
Cộng tác viên chuyên gia tại SenData. Chia sẻ kiến thức về phát triển Power BI, trực quan hóa dữ liệu và best practice business intelligence.
