Giao dịch đã tự chảy về Google Sheet nhờ tích hợp Google Sheet với Pay2S, nhưng câu hỏi mỗi ngày của kế toán vẫn là: tiền này của đơn nào? Đơn này đã có tiền chưa? Có khoản nào lạ không?
Đó là việc của template đối soát giao dịch: một bộ tab biến bảng giao dịch thô thành danh sách “đã khớp — chưa khớp — sai tiền — trùng” tự động. Bài này hướng dẫn dựng template theo đúng cấu trúc 13 cột A–M của tab Pay2S thật.
Trước khi bắt đầu: hiểu đúng bảng dữ liệu Pay2S
Tab Pay2S có 13 cột: A ID Pay2S, B Thời gian, C Ngân hàng, D Số tài khoản, E Tài khoản ảo, F Mã giao dịch, G Loại giao dịch (IN/OUT), H Số tiền, I Nội dung, J Tên người chuyển, K Số tài khoản người chuyển, L Ngân hàng người chuyển, M Mã ngân hàng đối ứng.
Ba đặc điểm quyết định cách viết công thức đối soát:
- Không có cột mã đơn hàng. Mã đơn (nếu khách ghi) nằm lẫn trong cột I — Nội dung, nên template phải tách nó ra.
- Cột H luôn là số dương. Chiều tiền nằm ở cột G: IN là tiền vào, OUT là tiền ra.
- Cột B lưu dạng chữ (
YYYY-MM-DD HH:MM:SS), muốn tính theo ngày phải chuyển sang ngày thật trước.
Cấu trúc template đối soát gồm 5 tab
- Pay2S — tab gốc hệ thống tự ghi. Không sửa, không sắp xếp, không xoá dòng.
- Chuan_hoa — tab chuẩn hoá dùng chung cho cả series template: tách ngày, tháng, số tiền có dấu và mã đơn.
- Don_hang — danh sách đơn hàng bạn dán vào từ website/POS: Mã đơn | Khách hàng | Số tiền đơn.
- Doi_soat — bảng khớp chính: mỗi đơn một dòng, tự tính tiền đã nhận và trạng thái.
- Ngoai_le — chỉ hiện những gì cần người xử lý: giao dịch vào không thấy mã đơn, giao dịch nghi trùng.
Bước 1: Dựng tab Chuan_hoa (làm một lần, dùng mãi)
Tạo tab Chuan_hoa với các cột: A ID | B Ngày | C Tháng | D Ngân hàng | E Mã GD | F Loại | G Số tiền | H Số tiền có dấu | I Nội dung | J Mã đơn | K Người chuyển.
Ở hàng 2, nhập các công thức mảng sau (tự áp cho toàn bộ cột, giao dịch mới về là tự có):
B (Ngày): =ARRAYFORMULA(IF(Pay2S!B2:B="","",DATEVALUE(LEFT(Pay2S!B2:B,10)))) C (Tháng): =ARRAYFORMULA(IF(B2:B="","",TEXT(B2:B,"yyyy-mm"))) H (Có dấu): =ARRAYFORMULA(IF(Pay2S!G2:G="","",IF(Pay2S!G2:G="IN",Pay2S!H2:H,-Pay2S!H2:H))) J (Mã đơn): =ARRAYFORMULA(IF(Pay2S!I2:I="","",IFERROR(REGEXEXTRACT(Pay2S!I2:I,"DH[0-9]+"),"")))
Các cột còn lại kéo thẳng từ tab Pay2S, ví dụ D = =ARRAYFORMULA(Pay2S!C2:C).
Lưu ý với công thức tách mã đơn: DH[0-9]+ là mẫu cho mã đơn bắt đầu bằng “DH” và theo sau là các chữ số. Shop của bạn dùng mẫu mã khác (ví dụ OD12345, #12345) thì sửa đoạn trong dấu ngoặc kép cho đúng mẫu mã của mình, rồi kiểm tra vài dòng đầu xem đã tách đúng chưa. Đây là bước quan trọng nhất của cả template — mã đơn tách sai thì mọi khớp phía sau đều sai.
Bước 2: Nạp danh sách đơn hàng
Vào tab Don_hang, dán danh sách đơn từ website hoặc phần mềm bán hàng với tối thiểu 3 cột: A Mã đơn | B Khách hàng | C Số tiền đơn. Mỗi lần đối soát một đợt đơn mới, chỉ cần dán thêm vào cuối bảng — không cần sửa công thức nào ở các tab khác.
Bước 3: Bảng Doi_soat tự khớp
Trong tab Doi_soat, với mỗi mã đơn ở cột A:
Tiền đã nhận: =SUMIFS(Chuan_hoa!G:G, Chuan_hoa!J:J, A2, Chuan_hoa!F:F, "IN") Số giao dịch: =COUNTIFS(Chuan_hoa!J:J, A2, Chuan_hoa!F:F, "IN") Trạng thái: =IF(B2=0,"Chưa thấy tiền",IF(C2=D2,"Đã khớp",IF(C2<D2,"Thiếu tiền","Thừa tiền")))
(trong đó B là số giao dịch, C là tiền đã nhận, D là số tiền đơn — chỉnh chữ cột theo đúng bố cục tab của bạn.)
Nhờ cột “Số giao dịch”, template còn bắt được trường hợp khách chuyển 2 lần cho cùng một đơn: tiền nhận sẽ gấp đôi và trạng thái nhảy sang “Thừa tiền” kèm số giao dịch = 2, thay vì lặng lẽ lọt qua như khi đối soát bằng mắt.
Bước 4: Tab Ngoai_le — nơi kế toán thực sự phải nhìn
Tab này chỉ lọc ra 2 nhóm:
- Giao dịch tiền vào (IN) nhưng không tách được mã đơn — khách quên ghi mã, ghi sai mã, hoặc tiền từ nguồn lạ:
=FILTER(Chuan_hoa!A:K, Chuan_hoa!F:F="IN", Chuan_hoa!J:J="")
- Giao dịch nghi trùng — cùng Mã giao dịch (cột F của tab Pay2S) xuất hiện hơn 1 lần. Thêm cột phụ trong Chuan_hoa:
=COUNTIF(Pay2S!F:F, E2)>1, rồi lọc các dòng mang giá trị TRUE sang tab Ngoai_le.
Mục tiêu của template là đây: thay vì dò 500 dòng sao kê, kế toán chỉ mở tab Ngoai_le xử lý vài dòng mỗi ngày.
3 lỗi hay gặp khi tự đối soát trên Sheet
- Sắp xếp lại tab Pay2S cho “dễ nhìn” — hỏng toàn bộ thứ tự hệ thống ghi và làm các báo cáo khác sai theo. Muốn sắp xếp, hãy làm ở tab Chuan_hoa hoặc một bản sao.
- Đối soát bằng số tiền đơn thuần (chỉ SUMIFS theo số tiền) — hai đơn cùng số tiền sẽ khớp nhầm vào nhau. Luôn khớp bằng mã đơn trước, số tiền chỉ để kiểm tra.
- Quên mất giao dịch OUT — hoàn tiền, chuyển nhầm phải trả lại… nếu chỉ đối soát chiều IN, số “đã khớp” sẽ đẹp giả tạo. Tab Chuan_hoa có cột số tiền có dấu chính là để chiều OUT được trừ lại đúng.
Câu hỏi thường gặp
Khách không ghi mã đơn khi chuyển khoản thì khớp bằng gì?
Template sẽ đưa giao dịch đó vào tab Ngoai_le. Bạn khớp tay dự phòng theo bộ ba: số tiền + tên người chuyển (cột J) + thời gian gần lúc đặt đơn. Về lâu dài, hãy để hệ thống thanh toán sinh nội dung chuyển khoản chứa mã đơn sẵn — đó là cách duy nhất để đối soát tự động 100%.
Mã đơn của shop tôi không bắt đầu bằng DH thì sao?
Sửa mẫu trong REGEXEXTRACT cho đúng định dạng mã của bạn, ví dụ OD[0-9]+ hoặc [0-9]{6} nếu mã là 6 chữ số. Kiểm tra cột Mã đơn ở tab Chuan_hoa sau khi sửa.
Template này xử lý được nhiều tài khoản ngân hàng không?
Được. Cột C của tab Chuan_hoa đã giữ lại ngân hàng của từng giao dịch. Muốn đối soát riêng từng tài khoản, thêm điều kiện ngân hàng vào các công thức SUMIFS/COUNTIFS. Xem thêm bài template báo cáo thu chi đa ngân hàng trong cùng series: Template báo cáo thu chi đa ngân hàng Pay2S
Kết luận
Một template đối soát giao dịch tốt không phải là bảng tính đẹp, mà là bảng tính khiến kế toán chỉ phải nhìn vào ngoại lệ. Dựng xong 5 tab trên, việc đối soát hằng ngày rút xuống còn: dán đơn mới → mở tab Ngoai_le → xử lý vài dòng.
Cần hỗ trợ dựng template theo đúng mẫu mã đơn và quy trình bán hàng của bạn, hoặc kết nối tiếp sang hoá đơn tự động sau đối soát? Liên hệ Hóa Đơn Tự Động — đại lý tư vấn triển khai Pay2S — qua số 0868461773.
Bài tiếp theo trong series: Template dashboard dòng tiền — theo dõi tiền vào, tiền ra theo ngày trên Google Sheet
