Hướng dẫn cách làm sổ cái trên Excel lấy dữ liệu từ Nhật ký chung

Ngày trước khi làm sổ cái trên Excel, mình thường gặp khó khăn với khối lượng dữ liệu lớn, hay sai sót trong quá trình sao chép số liệu từ Nhật ký chung sang sổ cái, chưa hiểu hết về cách sử dụng các hàm Excel khiến quá trình tính toán bị trở ngại.

Nếu bạn đang gặp rắc rối như mình đã từng, thì để mình mách bạn cách làm Sổ cái từ dữ liệu của Nhật ký chung trên phần mềm kế toán Excel nhé.

Mẫu Sổ cái và Nhật ký chung trên Excel

Trước khi tìm hiểu cách làm Sổ cái, chúng ta cần chuẩn bị một mẫu Sổ cái Excel. Bạn hãy tải về file đính kèm trong bài viết để tiết kiệm thời gian thiết lập sổ sách kế toán của mình nhé. Mẫu Sổ cái Excel của chúng ta như sau:

Bên cạnh đó, chúng ta có mẫu Nhật ký chung trên Excel như hình dưới đây:

Các bạn hãy tải về sổ Nhật ký chung trên phần mềm kế toán Excel và điền các thông tin cần thiết vào file sổ sách này nhé.

Xem thêm: Chia sẻ mẫu sổ Nhật ký - sổ Cái trên Excel theo thông tư 133

Cách làm Sổ cái từ Nhật ký chung

Sau khi đã có mẫu Sổ cái Excel, chúng ta sẽ thực hành làm Sổ cái dựa trên file đã tải. Hãy cùng tìm hiểu nguyên tắc thực hiện và xây dựng công thức lấy dữ liệu trên Excel để làm Sổ cái từ Nhật ký chung nhé.

Nguyên tắc lấy dữ liệu từ Nhật ký chung sang Sổ cái

Nếu bạn muốn đảm bảo độ chính xác 100% của dữ liệu được trích xuất từ Nhật ký chung sang Sổ cái, hãy lưu ý tuân thủ các nguyên tắc dưới đây:

1. Thiết lập các điều kiện dữ liệu rõ ràng, đặc biệt về định dạng dữ liệu. Ví dụ: Chú ý giá trị tại ô Tên tài khoản (ô K1) đang ở định dạng chữ hay số. Bạn cần đảm bảo định dạng dữ liệu này giống với định dạng dữ liệu trong các cột Danh mục tài khoản, TK nợ/có của Nhật ký chung để tham chiếu chính xác.
2. Tổ chức các cột dữ liệu trong mẫu Sổ cái Excel rõ ràng, không sử dụng gộp ô (Merge Cells) trong phạm vi Số phát sinh trong kỳ tại Sổ cái.
3. Cân bằng số dòng của Sổ cái và Nhật ký chung để đảm bảo lấy đầy đủ nội dung. Hãy lấy dòng đầu tiên của Sổ cái dựa theo dòng đầu tiên của Nhật ký chung.
4. Đối chiếu Sổ cái và Nhật ký chung để đảm bảo tài khoản đối ứng trong Sổ cái tuân theo đúng nguyên tắc sau: Nếu tài khoản Sổ cái nằm ở cột TK nợ trong Nhật ký chung thì tài khoản đối ứng phải là tài khoản xuất hiện cùng dòng trong cột TK có của Nhật ký chung.
5. Ghi số phát sinh vào cột Nợ hoặc cột Có trong Sổ cái dựa vào vị trí của tài khoản trong Nhật ký chung, ghi số tiền Sổ cái tương ứng với cột Số tiền của Nhật ký chung.

Hồi mới làm kế toán, mình gặp khó khăn trong quá trình làm sổ cái, bởi không nắm được các quy trình thực tế tại doanh nghiệp, mà cũng không giỏi Excel nên quá tình làm việc rất vất vả.

May mắn là mình đã nhanh chóng thành thạo các bước lên sổ kế toán, quy trình lấy dữ liệu vào sổ cái, cùng các kiến thức kế toán tổng hợp khác bởi tham gia học với 2 khóa học:

Cột Tài khoản đối ứng

Hãy bắt đầu với cột phức tạp nhất trong Sổ cái của chúng ta - cột Tài khoản. Dựa vào nguyên tắc của tài khoản đối ứng và điều kiện dữ liệu khi làm Sổ cái, chúng ta sẽ phải tạo công thức đáp ứng một số yêu cầu nhất định dưới đây:

Yêu cầu 1

Nếu Số tài khoản tại ô I1 trong Sổ cái trùng với giá trị TK Nợ tại Nhật ký chung, đồng thời Ngày tháng ghi Nhật ký chung nằm trong khoảng thời gian tại ô I2:I3 của Sổ cái thì chúng ta sẽ lấy dữ liệu theo tài khoản ở cột TK Có. Ngược lại, chúng ta sẽ xét đến các trường hợp tiếp theo.

Như vậy, công thức của chúng ta được tạo bước đầu như sau:

=IF(AND(NKC!G8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!H8,…)

Yêu cầu 2

Tại trường hợp tiếp theo: Nếu Số tài khoản tại ô I11 trong Sổ cái trùng với giá trị TK Có tại Nhật ký chung, đồng thời Ngày tháng ghi Nhật ký chung nằm trong khoảng thời gian tại ô I2:I3 của Sổ cái thì chúng ta sẽ lấy dữ liệu theo tài khoản ở cột TK Nợ. Nếu dữ liệu không thỏa mãn cả 2 điều kiện này thì Excel trả về kết quả rỗng.

Công thức hàm IF tiếp tục được phát triển như sau:

=IF(AND(NKC!H8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!G8,"")

Đối với cột Tài khoản đối ứng, chúng ta phải xét cả 2 yêu cầu trên. Do đó, hãy lồng 2 công thức hàm IF để tạo một công thức mới tại ô E12 như sau:

=IF(AND(NKC!G8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!H8,IF(AND(NKC!H8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!G8,""))

Trong các công thức trên:

• Cột G của Nhật ký chung đại diện cho TK Nợ.
• Cột H của Nhật ký chung đại diện cho TK Có.
• Cột A của Nhật ký chung đại diện cho Ngày ghi sổ.

Xem thêm: Hướng dẫn cách dùng hàm IF trong Excel qua bài tập

Cột Số phát sinh Nợ và Có

Tương tự với cột Tài khoản đối ứng, chúng ta cũng cần xây dựng công thức hàm lấy dữ liệu làm Sổ cái từ Nhật ký chung dựa trên một vài yêu cầu.

Yêu cầu 1

Nếu Số tài khoản tại ô I11 trong Sổ cái trùng với giá trị TK Nợ tại Nhật ký chung, đồng thời Ngày tháng ghi Nhật ký chung nằm trong khoảng thời gian tại ô I2:I3 của Sổ cái thì chúng ta sẽ lấy dữ liệu Số tiền vào cột Số phát sinh Nợ của Sổ cái. Ngược lại, chúng ta điền giá trị 0.

Công thức điền tại ô F12 trong Sổ cái như sau:

=IF(AND(NKC!G8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!I8,0)

Yêu cầu 2

Nếu Số tài khoản tại ô I11 trong Sổ cái trùng với giá trị TK Có tại Nhật ký chung, đồng thời Ngày tháng ghi Nhật ký chung nằm trong khoảng thời gian tại ô I2:I3 của Sổ cái thì chúng ta sẽ lấy dữ liệu Số tiền vào cột Số phát sinh Có của Sổ cái. Ngược lại, chúng ta điền giá trị 0.

Công thức hàm IF lấy dữ liệu từ Nhật ký chung sang Sổ cái như sau:

=IF(AND(NKC!H8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!I8,0)

Trong các công thức trên: Cột I của Nhật ký chung đại diện cho Số tiền.

Các cột còn lại

Với các cột còn lại trong Sổ cái, chúng ta chỉ cần kiểm tra số phát sinh trong cột Tài khoản đối ứng (cột E) có tồn tại hay không. Dựa vào nguyên tắc này, chúng ta sẽ sử dụng hàm IF như sau: Nếu giá trị ở cột E là rỗng thì Excel trả về giá trị rỗng, ngược lại thì lấy giá trị theo cột đó trong sổ Nhật ký chung.

Công thức lần lượt cho các cột như sau:

• A12=IF($E12="","",NKC!A8)
• B12=IF($E12="","",NKC!B8)
• C12=IF($E12="","",NKC!C8)
• D12=IF($E12="","",NKC!D8)

Sau khi đã điền các công thức cho bảng tính, chúng ta sẽ nhận được kết quả tương tự hình dưới đây:

Sau khi đã điền toàn bộ công thức cho dòng đầu tiên, bạn có thể FillDown công thức bằng cách sử dụng phím tắt Ctrl+D.

Lưu ý: Bạn cần cố định tất cả các tham chiếu tới ô K1, K2 và K3 trong Sổ cái để không làm sai lệch công thức khi sao chép xuống các dòng dưới.

Xem thêm: Tổng hợp các phím tắt Excel thần thánh cho dân kế toán

Lọc các dữ liệu phát sinh tại Sổ cái

Sau khi làm Sổ cái từ Nhật ký chung, bạn có thể nhận ra một số dòng dữ liệu xuất hiện trong Sổ cái không liền kề nhau. Điều này xảy ra vì vị trí dữ liệu phụ thuộc vào vị trí phát sinh trong Nhật ký chung. Để xóa các dòng trống này, chúng ta sẽ cần tạo thêm một cột phụ ngoài bảng để lọc dữ liệu.

Bạn có thể bắt đầu lọc từ dòng 9 của Sổ cái. Cách làm Sổ cái tại bước này như sau:

• Không áp dụng công thức cho các ô luôn xuất hiện trên Sổ cái như các ô Số dư đầu kỳ, Số phát sinh trong kỳ, Công số phát sinh,... Sau đó đặt một dấu x xác nhận luôn hiển thị khi lọc.
• Xét các dòng dữ liệu phát sinh theo nguyên tắc: Lấy giá trị rỗng nếu dữ liệu tại cột E là rỗng. Trong trường hợp ngược lại thì hiển thị dấu x khi lọc, nghĩa là giá trị khác rỗng. Như vậy, chúng ta có công thức tại ô H12 như sau: 

=IF(E12="","","x")

Sau khi áp dụng công cụ lọc AutoFilter cho cột H trong Sổ cái, chúng ta sẽ có kết quả như sau:

Vậy là chúng ta đã hoàn thành các bước làm Sổ cái từ Nhật ký chung rồi.

Xem thêm: Hướng dẫn ghi Sổ cái theo hình thức kế toán Nhật ký chung

Tổng kết

Như vậy, chúng ta đã cùng nhau thực hành cách làm Sổ cái dựa trên Nhật ký chung trên phẩn mềm kế toán Excel. Các bạn hãy tải file đính kèm mẫu Sổ cái Excel ở cuối bài viết và theo dõi hướng dẫn để thuần thục thủ thuật này nhé. Hy vọng kiến thức Gitiho chia sẻ ngày hôm nay thành công giúp bạn cải thiện công việc của mình.

Gitiho xin cảm ơn bạn đọc và chúc bạn thành công!