Làm sổ sách kế toán trên Excel: cấu trúc tệp, hàm SUMIFS, VLOOKUP, SUBTOTAL và lúc Excel không còn đủ

Hai quy ước giữ cho công thức chạy đúng. Mã tài khoản luôn nhập dạng chữ (đặt định dạng ô là Text trước khi nhập), vì 1111 dạng số và dạng chữ không khớp nhau trong SUMIFS. Mỗi bút toán Nợ một tài khoản, Có một tài khoản nằm trên một dòng; bút toán nhiều Nợ nhiều Có thì tách thành nhiều dòng cùng số chứng từ. Các cột của trang NhatKy cố ý đặt giống mẫu nhập chứng từ của KetoanX, để sau này chuyển sang phần mềm không phải làm lại.

SUMIFS cộng cột số tiền theo điều kiện. Trên trang SoCai, giả sử ô B1 là tài khoản cần xem, B2 và B3 là từ ngày và đến ngày. Phát sinh Nợ trong kỳ là:

=SUMIFS(NhatKy!$G:$G, NhatKy!$E:$E, $B$1, NhatKy!$A:$A, ">="&$B$2, NhatKy!$A:$A, "<="&$B$3)

Phát sinh Có giống hệt, chỉ thay cột điều kiện E (TK Nợ) bằng cột F (TK Có). Muốn gộp cả tài khoản con thì thêm dấu sao vào điều kiện, ví dụ $B$1&"*", để tài khoản 111 gom cả 1111 lẫn 1112. Số dư đầu kỳ của tài khoản dư Nợ bằng số dư đầu năm cộng phát sinh Nợ trừ phát sinh Có của các kỳ trước ngày bắt đầu, tính bằng chính hai công thức trên với điều kiện ngày nhỏ hơn B2.

VLOOKUP lấy tên tài khoản từ DanhMuc: =VLOOKUP(A5, DanhMuc!$A:$B, 2, FALSE). Đối số cuối FALSE bắt buộc, vì bỏ nó thì VLOOKUP mặc định tìm gần đúng, kiểu tìm giả định cột mã đã sắp xếp, nên mã gõ sai có thể ra tên của tài khoản khác. Lỗi #N/A nghĩa là mã chưa có trong danh mục, đó là tín hiệu có ích hơn là một lỗi cần che.

SUBTOTAL cộng đúng các dòng đang hiện sau khi lọc. Lọc trang NhatKy theo cột TK Nợ bằng 1111, rồi =SUBTOTAL(9, G2:G5000) cho tổng các dòng còn hiện, còn SUM cho tổng cả cột bất kể lọc. Số 9 là phép cộng và vẫn tính dòng bạn ẩn bằng lệnh Hide Rows; số 109 cũng cộng nhưng bỏ cả những dòng ấy. Dòng bị bộ lọc loại thì cả hai đều bỏ. Đặt SUBTOTAL ở chân sổ chi tiết công nợ rồi lọc theo đối tượng thì có ngay số phát sinh của từng khách.

Trên SoCai chọn tài khoản 1111, từ 01/03 đến 31/03. SUMIFS cột TK Nợ ra 15.000.000 (dòng 10/03), SUMIFS cột TK Có ra 2.000.000 (dòng 05/03). Nếu đầu tháng quỹ có 40.000.000 thì số dư cuối kỳ là 40.000.000 + 15.000.000 - 2.000.000 = 53.000.000 đồng. Chứng từ thứ ba không đụng tới quỹ: nó tăng 131 thêm 30.000.000 và tăng doanh thu 5111 cùng số, còn chứng từ thứ hai đã giảm 131 đi 15.000.000.

Thêm ba ô kiểm ở chân bảng, mỗi ô bằng tổng Nợ trừ tổng Có của một cặp, phải ra 0. Thêm một cột kiểm ở trang NhatKy: ô đó trả về nội dung báo lỗi nếu TK Nợ hoặc TK Có bỏ trống, hoặc nếu số tiền không phải số. Các ô kiểm này chặn được lỗi gõ thiếu và lệch Nợ Có, nhưng không phát hiện ghi nhầm sang tài khoản cùng bên.

Sổ điện tử vẫn phải khóa cuối kỳ, rồi in ra giấy đóng quyển cho từng kỳ kế toán năm, hoặc lưu điện tử an toàn và tra cứu được suốt thời hạn lưu trữ. Chi tiết nằm ở bài [ghi sổ trên máy vi tính](/kien-thuc/ghi-so-tren-may-vi-tinh-phan-mem).

Có bốn dấu hiệu cho thấy Excel hết đủ. Một là nhiều hơn một người nhập chứng từ: hai người mở chung một tệp sẽ ghi đè lên nhau. Hai là số dòng nhật ký lớn: một trang tính chứa tối đa 1.048.576 dòng, và công thức tính trên cả cột chậm dần khi dữ liệu dày lên. Ba là cần hóa đơn điện tử, công nợ theo hóa đơn, kho, tài sản, lương, vì mỗi thứ là một bộ công thức khác và dễ lệch nhau. Bốn là cần tờ khai thuế xuất ra tệp XML để nộp, việc mà bảng tính không làm.

ketoanx.com/import

Các bước

1. 1

   Vào Hệ thống→, Nhập từ Excel. Ô Loại dữ liệu chọn Số dư đầu kỳ trước, bấm Tải tệp mẫu để lấy đúng tên cột. Tệp có các cột Tài khoản, Mã đối tượng, Dư Nợ, Dư Có; mỗi dòng chỉ có dư Nợ hoặc dư Có, và tài khoản phải là tài khoản chi tiết.

2. 2

   Chép số dư cuối năm cũ từ trang CanDoi sang tệp mẫu, rồi chọn tệp ở ô Tệp Excel hoặc CSV (.xlsx, .csv mã UTF-8; tối đa 5 MB, 5.000 dòng). Mỗi cột của tệp hiện một ô chọn trường tương ứng; kiểm lại cột nào ghép với trường nào, trường có dấu * là bắt buộc.

3. 3

   Tích ô Xóa số dư đã nhập trước đó của các tài khoản có trong tệp rồi nhập lại nếu bạn đã nhập một lần và muốn làm lại. Bấm nút Nhập … dòng.

4. 4

   Lặp lại với ô Loại dữ liệu chọn Chứng từ kế toán cho trang NhatKy. Tệp có các cột Ngày hạch toán, Loại (NK, PT, PC, BC, BN), Số chứng từ, Diễn giải, TK Nợ, TK Có, Số tiền, Đối tượng Nợ, Đối tượng Có. Dòng cùng ngày, loại và số chứng từ gộp thành một chứng từ. Với ví dụ của bài, ba dòng PC01, PT01 và NK01 thành ba chứng từ.

5. 5

   Dòng nào lỗi sẽ được báo kèm số dòng; bấm Tải tệp các dòng lỗi để sửa trong Excel rồi nhập lại. Một dòng lỗi chỉ làm dòng đó không được nhập.

Phần mềm tự ghi gì

Với Chứng từ kế toán, mỗi nhóm dòng thành một chứng từ đã ghi sổ ngay với đúng TK Nợ, TK Có và số tiền trong tệp; ví dụ NK01 ghi Nợ 131 / Có 5111 là 30.000.000 đồng. Số dư đầu kỳ ghi vào số dư từng tài khoản và từng đối tượng, công nợ thì theo mã đối tượng. Phần mềm không tự thêm thuế GTGT, kho hay công nợ theo hóa đơn từ mẫu này: chứng từ cần các thứ đó phải dùng mẫu Phiếu thu, Phiếu chi, mua hàng, bán hàng hoặc Chứng từ nghiệp vụ khác.

Kiểm lại và giới hạn

Sau khi nhập, mở Báo cáo→, Cân đối phát sinh và so với trang CanDoi của Excel: từng tài khoản phải bằng nhau. Mỗi lần nhập tối đa 5 MB và 5.000 dòng; tệp lớn hơn dùng Hệ thống→, Nhập tệp lớn (CSV). Tệp .xls đời cũ phải mở bằng Excel rồi lưu lại dạng .xlsx. Xem thêm [phần mềm kế toán online và offline](/phan-mem-ke-toan-online-va-offline) khi cân nhắc chuyển.

Muốn xem đủ mọi ô, mọi nút và lỗi hay gặp của màn này, đọc bài hướng dẫn sử dụng nhập từ Excel.