Đối Soát Tiền Thuê Trọ Qua Sao Kê Bằng Google Sheets (2026)

Trả lời nhanh: Xuất sao kê ra CSV, dán vào sheet, yêu cầu khách ghi nội dung chuyển khoản theo mẫu mã phòng + tháng (ví dụ P203 T10), tách mã bằng REGEXEXTRACT, cộng tiền về từng phòng bằng SUMIFS rồi trừ cho số phải thu. Mỗi phòng ra một trạng thái: đủ, còn nợ, thu một phần hoặc dư.

Thử hình dung anh Khoa, chủ một dãy trọ 12 phòng (đây là tình huống minh họa, không phải câu chuyện có thật). Đến mùng 5, anh mở ứng dụng ngân hàng, cuộn danh sách giao dịch và gặp những dòng như "NGUYEN VAN A CK TIEN NHA", "tien phong thang 10", "P2O3" (chữ O chứ không phải số 0). Anh vừa cuộn vừa lật cuốn sổ phải thu, gạch từng dòng bằng bút đỏ. Hết buổi tối vẫn còn hai khoản không biết của ai, và một phòng anh nhớ mang máng là chuyển thiếu một ít nhưng không nhớ thiếu bao nhiêu.

Việc này không khó, chỉ tốn thời gian và dễ nhầm. Phần dưới là cách làm bằng hai sheet và vài công thức. Bạn dựng được trong khoảng một giờ và dùng lại mỗi tháng.

Dựng hai sheet: một cho sao kê, một cho số phải thu

Đừng trộn sao kê với bảng tiền phòng trong cùng một sheet. Sao kê là dữ liệu thô, mỗi tháng dán thêm một đợt. Bảng phải thu là dữ liệu của bạn, mỗi tháng lập một lần. Tách riêng thì dán sao kê không làm hỏng công thức bên kia.

Sheet SaoKe có sáu cột, hàng 1 là tiêu đề:

• A: Ngày giao dịch
• B: Nội dung chuyển khoản
• C: Số tiền về (chỉ lấy khoản tiền vào)
• D: Mã phòng (công thức)
• E: Tháng (công thức)
• F: Trạng thái khớp (công thức)

Sheet PhaiThu cũng có sáu cột, mỗi hàng là một phòng trong một tháng:

• A: Mã phòng, ví dụ P101
• B: Tháng, ghi số, ví dụ 10
• C: Phải thu (tiền phòng + điện nước + dịch vụ của tháng đó)
• D: Đã thu (công thức)
• E: Còn lại (công thức)
• F: Trạng thái (công thức)

Khi có sao kê, hãy nhập file CSV vào một sheet tạm. Tại đó bạn xóa các cột không cần, chỉ giữ ngày, nội dung và số tiền về. Sao kê của bạn thường có cột tiền ra và cột tiền vào, nên chỉ lấy cột tiền vào. Sau đó copy ba cột này sang A:C của SaoKe bằng dán giá trị (Ctrl+Shift+V). Dán thường có thể kéo theo định dạng và ghi đè công thức ở D:F.

Một lỗi hay gặp: số tiền nhập từ CSV đôi khi thành chữ, ô căn trái và SUMIFS bỏ qua. Nếu thấy vậy, vào Tệp → Cài đặt, chọn ngôn ngữ/vùng là Việt Nam. Hoặc đổi cột C bằng công thức =VALUE(SUBSTITUTE(C2,".","")) ở cột phụ, với giả định số tiền dùng dấu chấm ngăn nghìn và không có phần thập phân.

Quy ước nội dung chuyển khoản để máy dò được

Công thức thông minh đến đâu cũng không đọc nổi "NGUYEN VAN A CK TIEN NHA". Phần quyết định nằm ở việc khách ghi gì khi chuyển, nên bước này làm bằng con người trước.

Mẫu gọn nhất là mã phòng + chữ T + số tháng, ví dụ P203 T10. Vài lý do cho mẫu này:

• Mã phòng cố định số chữ số (P101, P102... P203), nên regex bắt được đúng một kiểu.
• Không dấu tiếng Việt, nên không sợ ứng dụng ngân hàng của khách bỏ dấu hay đổi dấu.
• Khách gõ nhanh trong năm giây, ít bị bỏ qua hơn một câu dài.

Cách phổ biến để khách nhớ là ghi mẫu này ngay trên phiếu báo tiền gửi cho họ mỗi tháng, và dán thêm vào nội quy hoặc nhóm chat của dãy trọ. Với khách mới, nhắc ngay lúc ký hợp đồng. Đừng chờ đến lần đóng tiền đầu mới nhắc, vì lúc đó họ đã chuyển với nội dung tự nghĩ ra.

Mẫu này cũng có giới hạn. Khách vẫn gõ nhầm (P2O3, P23, thiếu tháng), và vẫn có người nhờ người nhà chuyển nên ghi tên người nhà. Quy ước chỉ giảm số khoản phải xử lý bằng tay, không xóa hết được. Phần xử lý ngoại lệ ở dưới sẽ lo chuyện đó.

Tách mã phòng và tháng từ nội dung sao kê

Giả sử sao kê tháng 10 của dãy trọ anh Khoa sau khi dán vào SaoKe trông như sau (số liệu giả định):

Ô	A: Ngày	B: Nội dung	C: Số tiền về
Hàng 2	02/10	P101 T10	3.250.000
Hàng 3	03/10	p102 t10 tien nha	2.000.000
Hàng 4	04/10	P203T10	3.500.000
Hàng 5	04/10	P204 T10 dot 1	2.000.000
Hàng 6	05/10	P204 T10 dot 2	1.600.000
Hàng 7	05/10	NGUYEN VAN A CK TIEN NHA	3.000.000

Ở D2, tách mã phòng. Đổi nội dung sang chữ hoa trước để "p102" và "P102" thành một:

=IFERROR(REGEXEXTRACT(UPPER(B2),"P\d{3}"),"")

Công thức tìm chữ P theo sau là đúng ba chữ số. "P203T10" dính liền vẫn bắt được vì regex không đòi có khoảng trắng. Không tìm thấy thì IFERROR trả ô trống, để dòng "NGUYEN VAN A..." tự lộ ra là chưa có mã.

Ở E2, tách tháng:

=IFERROR(VALUE(REGEXEXTRACT(UPPER(B2),"T(\d{1,2})")),"")

Công thức lấy chữ T theo sau là một đến hai chữ số, và VALUE đổi kết quả thành số, nên "T10" ra 10 và "T9" ra 9. Chữ T trong từ "TIEN" không gây nhầm vì sau nó là chữ I chứ không phải số. Cần lưu ý: cột tháng chỉ có số tháng, nên khi sao kê vắt sang năm sau, hãy thêm cột năm hoặc tách bảng theo năm để tháng 1 năm này không dồn với tháng 1 năm trước.

Kéo D2:E2 xuống hết các dòng sao kê. Nếu một số khách vẫn ghi không theo mẫu nhưng có ghi mã phòng, bạn có thể thêm một công thức dự phòng dò trực tiếp từ danh sách phòng. Giả sử mã phòng nằm ở H2:H13 của cùng sheet:

=IFERROR(INDEX($H$2:$H$13,MATCH(TRUE,ARRAYFORMULA(ISNUMBER(SEARCH($H$2:$H$13,B2))),0)),"")

Công thức này dùng SEARCH quét xem nội dung có chứa mã phòng nào trong danh sách không. Nó chạy được khi mã phòng có độ dài cố định. Nếu bạn đặt mã P1 và P12, SEARCH sẽ thấy "P1" trong "P12" và gán nhầm phòng, nên đặt mã cùng số chữ số ngay từ đầu.

Cộng tiền về theo phòng và ra trạng thái còn nợ hay dư

Sang sheet PhaiThu. Giả sử tháng 10 có bốn phòng sau, số liệu phải thu giả định:

A: Mã phòng	B: Tháng	C: Phải thu
P101	10	3.250.000
P102	10	3.100.000
P203	10	3.400.000
P204	10	3.600.000

Ở D2, cộng mọi khoản tiền về mang đúng mã phòng và đúng tháng:

=SUMIFS(SaoKe!$C$2:$C$500,SaoKe!$D$2:$D$500,$A2,SaoKe!$E$2:$E$500,$B2)

SUMIFS cộng cột C của SaoKe, chỉ những dòng có mã phòng (cột D) bằng A2 và tháng (cột E) bằng B2. Phòng P204 chuyển hai đợt (2.000.000 và 1.600.000) sẽ được cộng thành 3.600.000 mà bạn không phải làm gì thêm. Đó là lý do nên dùng SUMIFS thay vì tìm một dòng duy nhất.

Ở E2, tính còn lại:

=C2-D2

Ở F2, ra trạng thái:

=IF(D2=0,"Chưa thu",IF(E2>0,"Thu một phần",IF(E2=0,"Đủ","Dư")))

Công thức kiểm tra theo thứ tự: chưa có đồng nào về thì "Chưa thu", còn thiếu thì "Thu một phần", đúng số thì "Đủ", còn lại là "Dư". Kết quả với số liệu giả định ở trên:

Phòng	Phải thu	Đã thu	Còn lại	Trạng thái
P101	3.250.000	3.250.000	0	Đủ
P102	3.100.000	2.000.000	1.100.000	Thu một phần
P203	3.400.000	3.500.000	-100.000	Dư
P204	3.600.000	3.600.000	0	Đủ

Nhìn vào bảng này, anh Khoa biết ngay phải nhắc P102 đóng nốt 1.100.000, và P203 đang dư 100.000 (có thể trừ vào tháng sau hoặc hoàn lại, tùy anh thỏa thuận với khách). Số "dư" cũng là thông tin hữu ích, vì nhiều chủ trọ chỉ nhìn cột nợ mà quên rằng khách đã chuyển thừa.

Nếu bạn muốn bảng công nợ theo khách tách khỏi dãy trọ, có thể tham khảo Template Quản Lý Công Nợ Khách Hàng làm điểm xuất phát.

Xử lý khoản không khớp: nơi tốn thời gian nhất

Mọi bảng đối soát đều hứa hẹn khớp 100% rồi bị vỡ ở các ngoại lệ. Thay vì dò bằng mắt, hãy để chính sheet báo dòng nào cần xem. Ở F2 của SaoKe, thêm trạng thái cho từng dòng sao kê:

=IF(C2="","",IF(D2="","Không có mã phòng",IF(COUNTIFS(PhaiThu!$A$2:$A$13,D2,PhaiThu!$B$2:$B$13,E2)=0,"Sai mã hoặc sai tháng","Đã khớp")))

Công thức trả "Đã khớp" khi mã phòng và tháng có trong sổ phải thu. Còn lại là một trong hai lỗi: không đọc được mã, hoặc mã đọc được nhưng không có cặp phòng-tháng đó. Bật bộ lọc (Dữ liệu → Tạo bộ lọc) ở cột F, bỏ chọn "Đã khớp", bạn chỉ còn nhìn đúng những dòng cần xử lý. Với dữ liệu giả định ở trên, đó là dòng "NGUYEN VAN A CK TIEN NHA".

Thêm một phép kiểm tra tổng ở một ô trống bất kỳ:

=SUM(SaoKe!C2:C500)-SUM(PhaiThu!D2:D13)

Ô này cho biết tổng tiền đã về nhưng chưa được gán vào phòng nào. Trong ví dụ: tổng sao kê là 15.350.000, tổng đã gán là 12.350.000, chênh 3.000.000, đúng bằng khoản "NGUYEN VAN A". Nếu con số này bằng 0 mà bạn vẫn thấy lệch với ngân hàng, thì lỗi nằm ở việc dán thiếu hoặc dán trùng dòng.

Các ngoại lệ thường gặp và cách gỡ

• Không có mã phòng: nhắn hỏi khách đó là khoản của phòng nào, rồi sửa tay cột D của dòng đó thành mã phòng, hoặc sửa nội dung ở cột B. Đừng sửa công thức cả cột chỉ vì một dòng.
• Sai tháng: khách chuyển tiền tháng 10 nhưng ghi T9. Sửa tay cột E, hoặc đối chiếu với số phải thu của đúng tháng để xác nhận.
• Một lần chuyển cho hai phòng: ví dụ một người thuê hai phòng và chuyển gộp. Tách dòng đó thành hai dòng trong SaoKe, mỗi dòng một mã phòng và số tiền riêng, rồi ghi chú lý do ở cột cuối.
• Dán trùng: khi dán sao kê hai lần với ngày chồng nhau, cùng một giao dịch bị tính hai lần và phòng bị báo "Dư". Nếu sao kê của bạn có cột mã giao dịch, đặt nó ở cột G và thêm công thức =IF(COUNTIF($G$2:G2,G2)>1,"Trùng","") để đánh dấu. Dòng nào đã đánh dấu thì xóa trước khi đối soát.
• Chuyển thừa hoặc thiếu: đã có trạng thái "Dư" và "Thu một phần". Quy tắc xử lý (trừ vào kỳ sau hay hoàn lại) là thỏa thuận giữa bạn và khách, không phải việc của công thức.

Khi nào nên để phần mềm làm thay phần đối soát

Cách trên chạy tốt với dãy trọ vài chục phòng nếu bạn kỷ luật: mỗi tháng xuất sao kê, dán đúng chỗ, kéo công thức và lọc ngoại lệ. Điểm yếu của nó nằm ở những việc lặp lại: chép số phải thu từ nơi tính tiền sang PhaiThu, tự gõ nội dung chuyển khoản cho khách, và ghi từng đợt thu. Quên một bước là số liệu lệch.

Nếu bạn muốn bỏ các bước đó, Phần Mềm Quản Lý Phòng Trọ trên Google Sheets làm sẵn phần việc này: sinh mã QR VietQR cho từng hóa đơn với số tiền và nội dung chuyển khoản đã điền sẵn, nên khách quét là chuyển đúng. Phần mềm cũng tự động đối soát sao kê ngân hàng, dò khoản tiền về đúng phòng kèm mức độ tin cậy để bạn biết dòng nào cần xem lại. Thu tiền từng phần và sổ thu tiền tra cứu theo thời gian hoặc hình thức thanh toán nằm cùng một chỗ.

Phần mềm chạy trên Google Sheets và Apps Script trong tài khoản Google của bạn, dữ liệu nằm trong Google Drive của bạn, nên bạn cần có tài khoản Google. Giá 459.000đ, mua một lần dùng trọn đời, không phí hằng tháng, có thể dùng thử 7 ngày để kiểm tra xem cách đối soát của nó có hợp dãy trọ của bạn không.

Câu hỏi thường gặp

Khách ghi nội dung chuyển khoản thế nào để công thức tự nhận ra phòng và tháng?

Nên dùng mẫu mã phòng + chữ T + số tháng, ví dụ P203 T10. Mã phòng cố định số chữ số nên regex bắt đúng một kiểu, không dấu tiếng Việt nên không bị ứng dụng ngân hàng đổi dấu, và khách gõ nhanh. Nhắc mẫu này trên phiếu báo tiền, trong nhóm chat và ngay lúc ký hợp đồng với khách mới.

Phòng chuyển tiền làm hai đợt thì có cộng đúng không?

Có. SUMIFS cộng mọi dòng sao kê có cùng mã phòng và cùng tháng, nên không cần gộp tay. Ví dụ phòng P204 chuyển 2.000.000 rồi 1.600.000 sẽ ra 3.600.000, đúng bằng số phải thu và được gắn trạng thái Đủ. Đó là lý do nên dùng SUMIFS thay vì tìm một dòng duy nhất.

Làm sao tìm ra khoản tiền về không gán được vào phòng nào?

Thêm công thức trạng thái ở cột F của SaoKe rồi bật bộ lọc, bỏ chọn Đã khớp để chỉ còn các dòng cần xử lý như Không có mã phòng hay Sai mã hoặc sai tháng. Ngoài ra, lấy tổng sao kê trừ tổng Đã thu: trong ví dụ chênh 3.000.000, đúng bằng khoản của NGUYEN VAN A.

Vì sao phòng bị báo Dư dù khách không chuyển thừa?

Nguyên nhân hay gặp là dán sao kê hai lần với ngày chồng nhau, khiến một giao dịch bị tính hai lần. Nếu sao kê có cột mã giao dịch, đặt ở cột G rồi dùng công thức COUNTIF để đánh dấu Trùng, sau đó xóa các dòng đó trước khi đối soát.