Tải Mẫu bảng quản lý nhà trọ để bắt đầu. Mẫu có tám sheet với Table, cột và công thức như bài hướng dẫn; các ô nhập liệu để trống. Đây là sổ làm việc thủ công để bạn tùy chỉnh theo cơ sở, không phải tệp import của ứng dụng. Kiểm tra quy trình và quyền xem trước khi nhập dữ liệu thật.
Các con số trong ví dụ của bài chỉ giúp kiểm phép tính, không phải mức giá áp dụng hay quy định bắt buộc. Tệp tải để trống. Lưu tiền bằng số nguyên đồng và không dùng số đã làm tròn để thay dữ liệu thực nhận.
- PhòngTên Table: tbPhong · Tiêu đề cột cần tạo: MaCoSo, MaPhong, Tang, DienTich_m2, TrangThaiPhong · Vai trò: Mỗi phòng một dòng, mã phòng duy nhất trong cơ sở
- Hợp đồngTên Table: tbHopDong · Tiêu đề cột cần tạo: MaHopDong, MaCoSo, MaPhong, MaNguoiThue, TuNgay, DenNgay, GiaThueDong, TienCocDong, TrangThaiHopDong · Vai trò: Giữ riêng từng hợp đồng và thời hạn
- Chỉ sốTên Table: tbChiSo · Tiêu đề cột cần tạo: MaChiSo, MaCoSo, MaPhong, MaKy, NgayDoc, SoDienCu_kWh, SoDienMoi_kWh, SoNuocCu_m3, SoNuocMoi_m3, DienTieuThu_kWh, NuocTieuThu_m3, CanCuGia · Vai trò: Một bản ghi cho mỗi phòng, kỳ và lần đọc đã xác nhận
- Hóa đơnTên Table: tbHoaDon · Tiêu đề cột cần tạo: MaHoaDon, MaCoSo, MaPhong, MaKy, NgayLap, HanThanhToan, TongPhaiThuDong, DaPhanBoDong, ConPhaiThuDong, TrangThai · Vai trò: Một dòng cho mỗi hóa đơn; các tổng được tính từ hai bảng dưới
Các dòng được trích từ bảng trong bài. Mở mục để xem phạm vi áp dụng và phần giải thích.
Bắt đầu từ danh mục và dữ liệu gốc
Mỗi dòng trong một bảng chỉ đại diện cho một bản ghi. Mã phòng giữ ổn định khi người thuê đổi. Mã hợp đồng và mã hóa đơn nhận diện hồ sơ riêng. Không ghi nhiều người, nhiều kỳ hoặc nhiều giao dịch vào cùng một ô. Tệp tải đã có sẵn Table theo đúng tên và cột dưới đây.
| Sheet | Tên Table | Tiêu đề cột cần tạo | Vai trò |
|---|---|---|---|
| Phòng | tbPhong | MaCoSo, MaPhong, Tang, DienTich_m2, TrangThaiPhong | Mỗi phòng một dòng, mã phòng duy nhất trong cơ sở |
| Hợp đồng | tbHopDong | MaHopDong, MaCoSo, MaPhong, MaNguoiThue, TuNgay, DenNgay, GiaThueDong, TienCocDong, TrangThaiHopDong | Giữ riêng từng hợp đồng và thời hạn |
| Chỉ số | tbChiSo | MaChiSo, MaCoSo, MaPhong, MaKy, NgayDoc, SoDienCu_kWh, SoDienMoi_kWh, SoNuocCu_m3, SoNuocMoi_m3, DienTieuThu_kWh, NuocTieuThu_m3, CanCuGia | Một bản ghi cho mỗi phòng, kỳ và lần đọc đã xác nhận |
| Hóa đơn | tbHoaDon | MaHoaDon, MaCoSo, MaPhong, MaKy, NgayLap, HanThanhToan, TongPhaiThuDong, DaPhanBoDong, ConPhaiThuDong, TrangThai | Một dòng cho mỗi hóa đơn; các tổng được tính từ hai bảng dưới |
| Khoản phải thu | tbKhoanPhaiThu | MaKhoanThu, MaHoaDon, LoaiKhoan, DienGiai, SoTienDong, CanCu, TrangThai | Mỗi khoản hoặc điều chỉnh là một dòng; điều chỉnh giảm ghi số âm và kèm căn cứ |
| Giao dịch nhận | tbGiaoDichNhan | MaGiaoDich, NgayGioNhan, KenhNhan, SoTienNhanDong, ThamChieuNguon, TrangThaiDoiSoat, DaPhanBoDong, ConChuaPhanBoDong | Mỗi lần tiền thực vào quỹ hoặc tài khoản là một dòng nguyên gốc; hai cột cuối tính số đã phân bổ và số còn chờ |
| Phân bổ | tbPhanBo | MaPhanBo, MaGiaoDich, MaHoaDon, NgayPhanBo, SoTienPhanBoDong, TrangThai, CanCu | Nối một khoản nhận với một hóa đơn; một giao dịch trả nhiều hóa đơn có nhiều dòng |
| Bảo trì | tbBaoTri | MaViec, MaCoSo, MaPhong, NgayTiepNhan, HangMuc, ChiPhiDong, BenChiTra, TrangThai | Theo dõi sửa chữa riêng với tiền thuê và tiền nhận |
Các tiêu đề là tên trường dữ liệu; Table đã tạo sẵn trong tệp tải. Khi tự dựng lại workbook, dùng đúng tên Table và tiêu đề cột như trong bảng. Công thức tại các cột hóa đơn và giao dịch nhận tham chiếu tên Table và tiêu đề cột, vì vậy không đổi các tên này. Công thức chỉ số dùng tham chiếu ô tương đối trên dòng đầu tiên. Excel tự điền công thức tương ứng xuống khi thêm dòng vào Table. Danh sách chọn và kiểm tra số tiền được áp dụng tới dòng 10.000; nếu cần thêm dòng, sao chép quy tắc nhập xuống các dòng mới. Mỗi trường tiền ...Dong lưu số nguyên đồng; nếu phép tính có phần lẻ, áp dụng quy tắc làm tròn đã thỏa thuận hoặc quy định áp dụng và kiểm lại kết quả trước khi chốt.
Tính tiêu thụ và khoản trên hóa đơn
Chỉ tính mức dùng khi hai số chỉ số đã được xác nhận thuộc cùng một công tơ và đúng kỳ. Trong Table tbChiSo, công thức tại ô J2 của sheet Chỉ số là:
=IF(OR(F2="",G2=""),"",G2-F2)Với nước, công thức tại ô K2 là =IF(OR(H2="",I2=""),"",I2-H2). Table có thể tự điền công thức tương ứng xuống các dòng mới. Nếu số mới nhỏ hơn số cũ, không tự đặt mức dùng về 0. Kiểm lại mã công tơ, ảnh và ngày đọc; nếu thay công tơ hoặc quay vòng số, ghi từng đoạn theo hồ sơ riêng.
Trong tbKhoanPhaiThu, tạo các dòng như tiền thuê, điện, nước, dịch vụ hoặc giảm trừ. Ghi căn cứ tính tại CanCu, chẳng hạn mã chỉ số, mức giá trong hợp đồng hoặc xác nhận giảm. Không đặt một đơn giá chung như 3.500 đồng/kWh nếu chưa xác định đó là giả định của ví dụ hay giá được áp dụng cho cơ sở. Với cách tính nhiều bậc, công tơ dùng chung hoặc tính giữa kỳ, ghi các đoạn và phép chia đủ để người khác kiểm lại.
Trong cột TongPhaiThuDong của Table tbHoaDon, cộng các dòng đã chốt của hóa đơn đó:
=IF([@MaHoaDon]="","",IF(COUNTIFS(tbKhoanPhaiThu[MaHoaDon],[@MaHoaDon],tbKhoanPhaiThu[TrangThai],"Đã chốt")=0,"",SUMIFS(tbKhoanPhaiThu[SoTienDong],tbKhoanPhaiThu[MaHoaDon],[@MaHoaDon],tbKhoanPhaiThu[TrangThai],"Đã chốt")))Trong cột DaPhanBoDong, chỉ cộng các phân bổ đã được kiểm và chốt cho hóa đơn đó:
=IF([@MaHoaDon]="","",SUMIFS(tbPhanBo[SoTienPhanBoDong],tbPhanBo[MaHoaDon],[@MaHoaDon],tbPhanBo[TrangThai],"Đã chốt"))Trong cột ConPhaiThuDong, tính:
=IF(OR([@MaHoaDon]="",[@TongPhaiThuDong]=""),"",[@TongPhaiThuDong]-[@DaPhanBoDong])Trong tbGiaoDichNhan, cột DaPhanBoDong tính tổng tiền đã phân bổ cho giao dịch đó; ConChuaPhanBoDong tính số còn chưa phân bổ. Nếu số phân bổ lớn hơn số tiền đã nhận, cột H hiển thị số âm để người kiểm tra xử lý sai lệch.
=IF([@MaGiaoDich]="","",SUMIFS(tbPhanBo[SoTienPhanBoDong],tbPhanBo[MaGiaoDich],[@MaGiaoDich],tbPhanBo[TrangThai],"Đã chốt"))
=IF(OR([@MaGiaoDich]="",[@SoTienNhanDong]=""),"",[@SoTienNhanDong]-[@DaPhanBoDong])Ví dụ một hóa đơn có tiền thuê 3.000.000 đồng, điện 420.000 đồng và giảm trừ đã duyệt 200.000 đồng. Tổng phải thu là 3.220.000 đồng. Nếu hai khoản nhận 1.500.000 đồng và 1.000.000 đồng đã được phân bổ, số còn phải thu là 720.000 đồng. Bảng tbGiaoDichNhan vẫn giữ nguyên hai số tiền nhận; không sửa giao dịch cuối thành 720.000 đồng.
Các công thức dùng SUMIFS và COUNTIFS để cộng theo nhiều điều kiện, theo cú pháp của Microsoft Support về SUMIFS. Excel có thể dùng dấu chấm phẩy thay dấu phẩy tùy thiết đặt vùng. Công thức ở các cột hóa đơn và giao dịch nhận dùng tham chiếu có cấu trúc để theo cột khi thêm dòng trong Table. Công thức chỉ số dùng tham chiếu ô tương đối F2, G2, H2, I2 và Table điền công thức tương ứng xuống dòng mới. Xem Microsoft Support về tham chiếu có cấu trúc trong Table.
Theo dõi nợ kỳ trước mà không cộng hai lần
Không chép số còn thiếu từ hóa đơn tháng trước vào cột “nợ kỳ trước” của hóa đơn mới nếu hóa đơn cũ vẫn còn trong bảng. Làm vậy sẽ tính một nghĩa vụ hai lần. Báo cáo công nợ tại một ngày được tính từ số còn phải thu của các hóa đơn trước ngày đó và hóa đơn hiện tại, sau khi trừ các phân bổ đã chốt. Hóa đơn cũ vẫn giữ mã, kỳ và tổng ban đầu.
Nếu chuyển từ sổ cũ mà không còn từng hóa đơn để nhập, tạo một hóa đơn đầu kỳ trong tbHoaDon và đúng một dòng số dư đầu kỳ trong tbKhoanPhaiThu cho mỗi phòng hoặc hợp đồng. Ghi ngày chốt, số tiền, nguồn đối chiếu và người xác nhận. Không nhập lại các hóa đơn cũ đã nằm trong số dư này. Nếu sau đó tìm lại được chi tiết hóa đơn cũ, thay số dư đầu kỳ bằng các bản ghi nguồn theo một lần chuyển đổi có đối chiếu, không cộng cả hai cách.
Tương tự, khoản đã nhận chưa chắc đã được phân bổ. Một giao dịch 3.000.000 đồng không rõ phòng vẫn nằm trong tbGiaoDichNhan, nhưng chưa làm giảm hóa đơn nào cho đến khi có căn cứ. Nếu một giao dịch trả cho hai phòng, thêm hai dòng trong tbPhanBo; tổng phân bổ của giao dịch không được lớn hơn số tiền còn có thể sử dụng. Tiền cọc được lưu và quyết toán riêng, không gộp vào doanh thu tiền thuê.
Kiểm tra bảng trước khi dùng
Trước kỳ thu đầu tiên, thử bằng các dòng giả định riêng trong bản sao làm việc. Kiểm tra hóa đơn trả đủ một lần, trả hai lần, trả thừa, giao dịch chưa rõ, giảm trừ đã duyệt và một khoản nợ cũ. Với mỗi tình huống, lần từ tổng hóa đơn tới dòng khoản phải thu, giao dịch nhận và phân bổ. Đối chiếu tổng tiền nhận với nguồn ngân hàng hoặc phiếu tiền mặt, rồi kiểm số còn phải thu. Xóa dữ liệu thử trước khi đưa vào vận hành.
Tệp mẫu có danh sách chọn cho trạng thái, loại khoản, kênh nhận và bên chịu chi phí, cùng kiểm tra số tiền nguyên không âm ở giao dịch nhận và phân bổ. Bảng khoản phải thu cho phép số âm để ghi giảm trừ. Dù vậy, hãy kiểm lại các dòng nhập bằng dán dữ liệu. Microsoft lưu ý Data Validation không tự báo mọi trường hợp dữ liệu được sao chép hoặc điền vào ô; xem Microsoft Support về Data Validation. Nếu phân bổ sai, giữ dòng cũ ở trạng thái Đã hủy và thêm dòng phân bổ đúng để còn lịch sử đối chiếu.
Bảo vệ công thức và dữ liệu
Khóa ô công thức và mở các ô nhập có thể giúp ngăn sửa nhầm khi bật bảo vệ sheet. Đây không phải biện pháp bảo mật dữ liệu hay phân quyền người xem. Người được cấp quyền truy cập file vẫn có thể xem dữ liệu theo khả năng của họ. Microsoft nêu rõ bảo vệ worksheet không phải tính năng bảo mật. Muốn hạn chế người xem, hãy cấp quyền ở nơi lưu file và chỉ chia sẻ cho đúng tài khoản; không giữ giấy tờ nhạy cảm trong workbook chung nếu người nhận không cần.
Giữ một bản sao lưu độc lập trước khi khóa kỳ và thử mở bản sao để chắc có thể đọc lại. Lưu ngày sao lưu và phạm vi kỳ trong tên file. Bản đồng bộ trực tuyến không thay thế bản sao lưu có thể khôi phục.
Khi nào bảng tính không còn phù hợp?
Không có ngưỡng số phòng chung. Hãy xem xét công cụ khác khi nhiều người cùng sửa, phải nhập lại cùng dữ liệu, không truy được lịch sử điều chỉnh hoặc thường xuyên ghép sai giao dịch. Trước khi đổi, thử xuất một nhóm dữ liệu và kiểm rằng mã phòng, hợp đồng, hóa đơn, khoản thu, phân bổ và số dư vẫn nối được. Xem lộ trình chuyển dữ liệu nhà trọ từ Excel sang phần mềm.
