Sổ cái Excel: cách lập sổ cái từng tài khoản từ nhật ký chung, có ví dụ tài khoản 112
Hướng dẫn lập sổ cái bằng Excel: dữ liệu nhật ký chung, lọc phát sinh theo tài khoản bằng FILTER/SUMIFS, số dư lũy kế, đối chiếu sổ phụ ngân hàng và bảng cân đối số phát sinh, kèm ví dụ tài khoản 112.
Sổ cái là sổ kế toán tổng hợp ghi lại toàn bộ nghiệp vụ phát sinh của từng tài khoản trong kỳ, gồm số dư đầu kỳ, số phát sinh Nợ, số phát sinh Có và số dư cuối kỳ. Khi làm bằng Excel, cách gọn nhất là nhập mọi nghiệp vụ một lần vào sheet nhật ký chung (mỗi dòng một bút toán với tài khoản Nợ, tài khoản Có và số tiền), rồi dùng công thức FILTER hoặc SUMIFS để tự sinh sổ cái cho tài khoản bất kỳ, kèm số dư lũy kế sau từng nghiệp vụ. Cuối kỳ, tổng phát sinh Nợ của tất cả tài khoản phải bằng tổng phát sinh Có — đó là phép kiểm tra cơ bản nhất.
Bài viết thuộc nhóm Ghi sổ thu chi. Nếu bạn chỉ cần theo dõi tiền mặt ra vào, mẫu sổ quỹ tiền mặt đơn giản hơn; sổ cái là bước tiếp theo khi bạn ghi sổ kép đầy đủ.
Lưu ý về mẫu sổ chính thức
Hình thức kế toán (nhật ký chung hay hình thức khác), mẫu sổ cái và hệ thống tài khoản phụ thuộc vào chế độ kế toán doanh nghiệp đang áp dụng. File Excel trong bài phục vụ việc ghi chép và kiểm soát nội bộ, giúp bạn hiểu dòng chảy số liệu. Sổ sách chính thức cần theo đúng mẫu, nguyên tắc lưu trữ của văn bản hiện hành và nên do kế toán phụ trách hoặc rà soát.
Sổ cái khác gì sổ nhật ký chung và sổ quỹ?
| Sổ | Cách sắp xếp | Dùng để |
|---|---|---|
| Sổ nhật ký chung | Mọi nghiệp vụ theo thứ tự thời gian | Ghi nhận đầy đủ, không bỏ sót nghiệp vụ |
| Sổ cái | Nghiệp vụ gom theo từng tài khoản | Xem phát sinh và số dư của mỗi tài khoản |
| Sổ quỹ tiền mặt | Thu chi tiền mặt theo ngày | Quản lý quỹ, đối chiếu kiểm kê |
| Bảng cân đối số phát sinh | Tổng hợp số dư và phát sinh mọi tài khoản | Kiểm tra cân đối, lập báo cáo tài chính |
Cấu trúc file Excel
Sheet “DanhMucTK”
Số hiệu tài khoản, tên tài khoản, tính chất số dư (Nợ/Có), số dư đầu kỳ. Định dạng cột số hiệu là văn bản để tránh lỗi khi tìm kiếm.
Sheet “NhatKy”
Các cột: ngày ghi sổ (A), số chứng từ (B), ngày chứng từ (C), diễn giải (D), TK Nợ (E), TK Có (F), số tiền (G). Mỗi bút toán chỉ có một tài khoản Nợ và một tài khoản Có; nghiệp vụ nhiều tài khoản thì tách thành nhiều dòng. Dùng danh sách thả xuống (Data → Data Validation) trỏ về DanhMucTK để không gõ sai số hiệu.
Sheet “SoCai”
Ô B1 chọn tài khoản, ô B2 lấy số dư đầu kỳ, bên dưới là danh sách nghiệp vụ liên quan và số dư lũy kế.
Công thức lập sổ cái
Lấy các nghiệp vụ của một tài khoản (Excel 365 hoặc Google Sheets)
=FILTER(NhatKy!A2:G1000,(NhatKy!E2:E1000=B1)+(NhatKy!F2:F1000=B1),"Không có phát sinh")
Kết quả trả về mọi dòng mà tài khoản đã chọn nằm ở cột Nợ hoặc cột Có.
Tách phát sinh Nợ, Có và tài khoản đối ứng
- Phát sinh Nợ:
=IF(E6=$B$1,G6,0) - Phát sinh Có:
=IF(F6=$B$1,G6,0) - Tài khoản đối ứng:
=IF(E6=$B$1,F6,E6)
Số dư lũy kế
Với tài khoản có số dư bên Nợ (tiền, phải thu, hàng tồn kho, chi phí): =K5+H6-I6. Với tài khoản có số dư bên Có (phải trả, vốn, doanh thu): =K5+I6-H6. Ô K5 là số dư đầu kỳ.
Tổng phát sinh trong kỳ cho bảng cân đối số phát sinh
- Tổng Nợ:
=SUMIFS(NhatKy!G:G,NhatKy!E:E,A5) - Tổng Có:
=SUMIFS(NhatKy!G:G,NhatKy!F:F,A5)
Với Excel thiết lập vùng Việt Nam, dấu phân cách đối số có thể là dấu chấm phẩy (;).
Ví dụ: sổ cái tài khoản 112 – Tiền gửi ngân hàng, tháng 10
Số dư đầu tháng: 250.000.000 đồng. Các nghiệp vụ trong tháng (đơn vị: đồng):
| Ngày | Diễn giải | TK đối ứng | Nợ | Có | Số dư |
|---|---|---|---|---|---|
| 01/10 | Số dư đầu kỳ | 250.000.000 | |||
| 02/10 | Khách hàng A trả nợ | 131 | 85.000.000 | 335.000.000 | |
| 05/10 | Thanh toán nhà cung cấp B | 331 | 60.000.000 | 275.000.000 | |
| 10/10 | Rút tiền gửi nhập quỹ tiền mặt | 111 | 20.000.000 | 255.000.000 | |
| 15/10 | Thu tiền bán hàng – doanh thu | 511 | 50.000.000 | 305.000.000 | |
| 15/10 | Thu tiền bán hàng – thuế GTGT đầu ra | 3331 | 5.000.000 | 310.000.000 | |
| 25/10 | Chuyển khoản trả lương nhân viên | 334 | 45.000.000 | 265.000.000 | |
| 28/10 | Phí dịch vụ ngân hàng | 642 | 330.000 | 264.670.000 | |
| Cộng phát sinh | 140.000.000 | 125.330.000 | |||
| Số dư cuối kỳ | 264.670.000 | ||||
Kiểm tra: 250.000.000 + 140.000.000 − 125.330.000 = 264.670.000 đồng. Nghiệp vụ bán hàng ngày 15/10 được tách thành hai dòng (doanh thu và thuế GTGT) để mỗi dòng chỉ có một tài khoản đối ứng. Số liệu là ví dụ minh họa.
Đối chiếu với sổ phụ ngân hàng
Cuối tháng, so sánh số dư 264.670.000 với số dư trên sổ phụ hoặc sao kê ngân hàng. Nếu chênh lệch, thường là do phí ngân hàng chưa ghi, khoản tiền đang chuyển hoặc nhập sai số tiền. Trong Excel có thể đặt ô: =IF(K20=SaoKe!B2,"Khớp ngân hàng","Chênh "&TEXT(K20-SaoKe!B2,"#,##0")).
Kiểm tra toàn bộ sổ sách
- Tổng phát sinh Nợ = tổng phát sinh Có trên bảng cân đối số phát sinh. Vì mỗi dòng nhật ký có đúng một tài khoản Nợ và một tài khoản Có với cùng số tiền, điều kiện này tự thỏa nếu không có dòng thiếu tài khoản.
- Không có dòng thiếu tài khoản:
=COUNTIFS(NhatKy!G:G,">0",NhatKy!E:E,"")+COUNTIFS(NhatKy!G:G,">0",NhatKy!F:F,"")phải bằng 0. - Tài khoản tiền không âm: số dư TK 111, 112 âm là dấu hiệu ghi đảo Nợ/Có.
- Đối chiếu công nợ: số dư TK 131, 331 khớp với sổ chi tiết theo từng khách hàng, nhà cung cấp.
Khi các bước kiểm tra đều đạt, số dư sổ cái là nguồn để lập bảng cân đối kế toán, báo cáo kết quả kinh doanh và báo cáo lưu chuyển tiền tệ.
Lỗi thường gặp
- Một dòng nhiều tài khoản đối ứng: làm hỏng công thức lọc; hãy tách dòng.
- Gõ số hiệu tài khoản khác định dạng (số và văn bản lẫn lộn) khiến SUMIFS bỏ sót.
- Quên số dư đầu kỳ khi chuyển sang năm mới.
- Sửa trực tiếp trên sổ cái thay vì sửa ở nhật ký: sổ cái nên chỉ là kết quả tính.
- Công thức bị ghi đè: khóa sheet SoCai theo hướng dẫn cách khóa công thức trong Excel.
Câu hỏi thường gặp
Excel phiên bản cũ không có hàm FILTER thì làm sao?
Dùng PivotTable trên sheet nhật ký, hoặc thêm cột phụ đánh số thứ tự các dòng liên quan đến tài khoản (dùng COUNTIF lũy kế) rồi lấy dữ liệu bằng INDEX/MATCH.
Doanh nghiệp nhỏ có nên tự ghi sổ cái bằng Excel?
Excel phù hợp để học, kiểm soát nội bộ hoặc khi số nghiệp vụ ít. Khi số lượng chứng từ lớn hoặc cần nộp báo cáo, phần mềm kế toán giúp giảm sai sót và đáp ứng yêu cầu lưu trữ tốt hơn.
Có thể theo dõi chi tiết theo khách hàng trong cùng file không?
Có. Thêm cột “Đối tượng” vào sheet nhật ký và lọc theo cả tài khoản lẫn đối tượng để có sổ chi tiết công nợ, kết hợp với file quản lý khách hàng.
Dùng được trên Google Sheets không?
Được. FILTER, SUMIFS, COUNTIFS, IF và TEXT đều hoạt động trên Google Sheets.
Bước tiếp theo: lập danh mục tài khoản bạn thực sự dùng, nhập các nghiệp vụ tháng gần nhất vào sheet nhật ký và dựng sổ cái TK 112 để đối chiếu với sao kê ngân hàng. Xem thêm tại Tài chính doanh nghiệp nhỏ.