Đối chiếu sổ tiền gửi 112 với sao kê ngân hàng bằng Power Query Merge

Power QueryNâng cao Video 3:55

Bài toán

Cuối tháng số dư sổ 112 lệch sao kê, và bạn tích từng giao dịch ở hai bên.

Làm xong được gì

Danh sách giao dịch chỉ có ở một bên, kèm bên nào thiếu; cộng lại đúng bằng số lệch.

Dùng trong bài

Text Between DelimitersMerge Queries (Left Anti)Append QueriesCustom ColumnTotal Row

File thực hành gồm

KeToan\DoiChieu112\So tien gui ngan hang.xlsx
Sổ tiền gửi ngân hàng xuất từ phần mềm, 95 bút toán tháng 9/2026, có 3 dòng tiêu đề gộp ô, dòng Số dư đầu kỳ và dòng Tổng cộng.
KeToan\DoiChieu112\LichSuGD_20260901_20260930.xls
Sao kê ngân hàng tháng 9, 97 giao dịch.
KeToan\XuatLai112\So tien gui ngan hang.xlsx
Sổ xuất lại sau khi ghi bổ sung, cùng tên với sổ gốc. Dùng ở bước cuối.
KeToan\Doi chieu 112.xlsx
File để làm theo bài. Đã nạp sẵn hai query So_112 và SaoKe, cùng tên cột Ngày, Số chứng từ, Diễn giải, Thu, Chi; ô tiền trống đã thay bằng 0.
KeToan\Bang doi chieu 112.xlsx
Bản đã dựng xong bảng đối chiếu. Mở ra bấm Data › Refresh All là chạy, dùng để so kết quả.

Giải nén gói file vào ổ C:\ để ra đúng thư mục C:\KeToan. Các query trong hai file Excel đọc sổ và sao kê ở C:\KeToan\DoiChieu112.

Các bước làm

1
Mở Power Query Editor từ file đã nạp sẵn
Mở Doi chieu 112.xlsx. Khung Queries & Connections có hai query So_112 và SaoKe. Nháy đúp SaoKe để mở Power Query Editor. Ngân hàng ghi Có là tiền vào tài khoản, bên sổ là Nợ 112, nên hai bảng đã được đặt chung tên cột Thu, Chi.
2
Tách số hoá đơn ở bảng sao kê
Bấm tiêu đề cột Diễn giải, vào Add Column › Extract › Text Between Delimiters. Ô Start delimiter gõ HD và một dấu cách, ô End delimiter gõ đúng một dấu cách, OK. Cột mới hiện số hoá đơn, dòng phí và dòng lương để trống. Nháy đúp tiêu đề cột mới, gõ Số HĐ, Enter.
3
Tách y hệt ở bảng sổ
Ở khung Queries bên trái bấm So_112. Lặp lại: bấm cột Diễn giải, Add Column › Extract › Text Between Delimiters, gõ hai ô như trên, OK, rồi đổi tên cột mới thành Số HĐ. Tên cột hai bên phải giống nhau.
4
Merge thứ nhất: dòng sổ có mà ngân hàng không có
Đang đứng ở So_112, vào Home › Merge Queries › Merge Queries as New. Ở bảng trên giữ Ctrl rồi bấm lần lượt Ngày, Thu, Chi, Số HĐ. Ô chọn bảng dưới chọn SaoKe, cũng giữ Ctrl bấm đúng thứ tự bốn cột đó. Join Kind chọn Left Anti (rows only in first) rồi OK. Query mới còn 3 dòng. Bấm tiêu đề cột SaoKe ở cuối bảng rồi bấm Delete. Vào Add Column › Custom Column, đặt tên Bên thiếu, ô công thức gõ dòng chữ dưới đây, có cả ngoặc kép.
"Ngân hàng chưa có"
5
Merge thứ hai: chiều ngược lại
Bấm query SaoKe, vào Home › Merge Queries › Merge Queries as New. Bảng trên là SaoKe, bảng dưới chọn So_112, mỗi bảng bấm bốn cột Ngày, Thu, Chi, Số HĐ, Join Kind chọn Left Anti, OK. Xoá cột So_112 ở cuối bảng, rồi thêm Custom Column tên Bên thiếu với công thức dưới đây. Query này ra 5 dòng.
"Sổ chưa ghi"
6
Gộp hai danh sách
Đang đứng ở query Merge vừa tạo, vào Home › Append Queries › Append Queries as New. Chọn Two tables, bảng thứ hai chọn query Merge của sổ, OK. Query mới có 8 dòng. Trong đó hoá đơn 0000386 nằm ở cả hai bên: sao kê 35.600.000, sổ ghi 36.500.000, lệch 900.000 do sổ gõ nhầm số tiền.
7
Thêm cột Chênh lệch
Vào Add Column › Custom Column, đặt tên Chênh lệch và gõ công thức dưới đây. Cột này là phần ngân hàng hơn sổ: dòng sổ chưa ghi lấy Thu trừ Chi, dòng ngân hàng chưa có thì đảo lại.
if [Bên thiếu] = "Sổ chưa ghi" then [Thu] - [Chi] else [Chi] - [Thu]
8
Chỉ đưa bảng gộp về Excel
Bấm mũi tên ở nút Close & Load, chọn Close & Load To, chọn Only Create Connection rồi OK. Hai query Merge trung gian nhờ vậy không sinh thêm sheet. Về Excel, ở khung Queries & Connections chuột phải query Append1, chọn Load To, chọn Table, New worksheet, OK. Sheet mới có bảng 8 dòng.
9
Bật dòng tổng để kiểm
Bấm một ô trong bảng rồi bấm Ctrl+Shift+T. Dòng Total của cột Chênh lệch ra 126.587.000, đúng bằng số dư sổ lệch sao kê. Dòng đầu bảng là khoản 50.000.000 ngày 15/09, hoá đơn 0000374, Bên thiếu ghi Sổ chưa ghi.
10
Ghi bổ sung, xuất lại sổ rồi Refresh
Chép file trong thư mục XuatLai112 đè lên file cùng tên trong thư mục DoiChieu112, rồi bấm Data › Refresh All. Bảng còn 2 dòng, đều là Ngân hàng chưa có: BC09037 thu 42.800.000 và UNC09035 chi 120.000.000. Hai khoản này sổ ghi ngày 30/09, sang 01/10 ngân hàng mới ghi. Dòng tổng còn 77.200.000.

Lưu ý khi làm với file thật

Khoá ghép phải có cả Số HĐ. Ngày 15/09 sao kê có hai khoản 50.000.000, sổ mới ghi một. Chỉ ghép theo Ngày, Thu, Chi thì cả hai khoản cùng khớp vào một dòng sổ, khoản thiếu không hiện ra.
Left Anti chỉ giữ những dòng của bảng trên không tìm thấy ở bảng dưới. Vì vậy phải làm hai lần, mỗi lần một chiều.

Excel 2019, 2021, 365 trên Windows đều làm được như trong bài. Excel 2016 cũng có Power Query nhưng menu hơi khác.

File thực hành của bài này

EzTech sẽ cập nhật link tải sau.

Dữ liệu trong bài là dữ liệu minh hoạ.

Bài học khác

Tất cả bài học