Tổ chức số liệu kế toán trên Excel

IF(SoNKC!$E$7:$E$32;C9;SoNKC!$G$7:$G$32) Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản. + Bên Có: Nhập vào ô G9 hàm sau: =SUMIF(SoNKC!$F$7:$F$32;C9;SoNKC!$G$7:$G$32) Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản. - Cột Số dư cuối kỳ: + Bên Nợ: Nhập vào ô H9 hàm sau: =IF((D9+F9)-(E9+G9)<0;0;(D9+F9)-(E9+G9)) Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản. + Bên Có: Nhập vào ô I9 hàm sau: =IF((E9+G9)-(D9+F9)<0;0;(E9+G9)-(D9+F9)) Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản. Tại hàng cuối của Bảng cân đối số phát sinh ở cột Số dư đầu kỳ bên Nợ nhập hàm: =SUM(D9:D39) Sau đó dùng Fill handle kéo sang ngang cho hết Bảng cân đối số phát sinh. Bạn phải lưu ý rằng một điều rất cơ bản của Bảng cân đối số phát sinh là: Số dư đầu kỳ bên Nợ = Số dư đầu kỳ bên Có Số phát sinh trong kỳ bên Nợ = Số phát sinh trong kỳ bên Có Số dư cuối kỳ bên Nợ = Số dư cuối kỳ bên Có Vì vậy khi bạn thấy tổng bên Nợ và bên Có của cột Số dư đầu kỳ, Số phát sinh trong kỳ hoặc Số dư cuối kỳ thì phải kiểm tra lại ngay. 2.3. Sổ cái tài khoản 2.3.1. Thiết kế sổ cái tài khoản Ta sẽ có 3 mẫu sổ cái: - Sổ cái tài khoản có số dư bên Nợ - Sổ cái tài khoản có số dư bên Có - Sổ cái tài khoản không có số dư Sổ cái có số dư bên nợ Đổi tên sheet 3 thành SoCai1. Sổ cái tài khoản có số dư bên Nợ sẽ có mẫu như sau: Lưu ý: Bạn phải dùng AutoFilter cho vùng dữ liệu trong Sổ cái như sau: Chọn cả vùng sẽ chứa dữ liệu trong sổ cái sau đó vào menu Data/Filter/AutoFilter. Trong đó: - Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Chứng từ: + SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. + Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Ghi Nợ: Tài khoản đối ứng bên Nợ của tài khoản trên sổ cái hiện hành. - Ghi Có: Tài khoản đối ứng bên Có của tài khoản trên sổ cái hiện hành. - Số dư Nợ: Số dư nợ của tài khoản trên sổ cái hiện hành - SDĐK: Số dư đầu kỳ - SPSTK: Số phát sinh trong kỳ - Cộng SPS: Cộng số phát sinh trong kỳ - SDCK: Số dư cuối kỳ Sổ cái có số dư bên Có Thêm một sheet bên sheet SoCai1, đặt tên là SoCai2. Sổ cái tài khoản có số dư bên Có sẽ có mẫu như sau: Trong đó: - Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Chứng từ: + SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. + Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Ghi Nợ: Tài khoản đối ứng bên Nợ của tài khoản trên sổ cái hiện hành. - Ghi Có: Tài khoản đối ứng bên Có của tài khoản trên sổ cái hiện hành. - Số dư Có: Số dư Có của tài khoản trên sổ cái hiện hành - SDĐK: Số dư đầu kỳ - SPSTK: Số phát sinh trong kỳ - Cộng SPS: Cộng số phát sinh trong kỳ - SDCK: Số dư cuối kỳ Sổ cái không có số dư Thêm một sheet bên sheet SoCai2, đặt tên là SoCai3. Sổ cái tài khoản không có số dư có mẫu như sau: Trong đó: - Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Chứng từ: + SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. + Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Ghi Nợ: Tài khoản đối ứng bên Nợ của tài khoản trên sổ cái hiện hành. - Ghi Có: Tài khoản đối ứng bên Có của tài khoản trên sổ cái hiện hành. - SPSTK: Số phát sinh trong kỳ - Cộng SPS: Cộng số phát sinh trong kỳ Lưu ý: Bạn phải dùng AutoFilter cho vùng dữ liệu trong các Sổ cái như sau: Chọn cả vùng sẽ chứa dữ liệu trong sổ cái sau đó vào menu Data/Filter/AutoFilter. 2.3.2. Xử lý dữ liệu trên sổ cái tài khoản Ta thấy 3 mẫu sổ cái trên khá giống nhau và cách xử lý dữ liệu trên cả 3 sổ này cũng vậy, chúng chỉ hơi khác nhau một chút về cách lấy dữ liệu về số dư đầu và cuối từ một cơ sở dữ liệu khác (cụ thể là từ Bảng cân đối số phát sinh). Sổ cái có số dư bên Nợ Nhập vào ô A7 tài khoản trên sổ cái mà bạn muốn xem. Ô A7 được định dạng như sau: Nhấp chuột vào ô A7 sau đó vào menu Format/Cells. Hộp thoại Format Cells xuất hiện ta chọn trong hộp Category mục Custom sau đó trong hộp danh sách định dạng bên cạnh ta chọn định dạng là ba dấu chấm phẩy (;;;). Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện trên sổ nhưng vẫn tồn tại. Trên dòng tiêu đề sổ ta nhập vào hàm sau: =VLOOKUP($A$7,TAIKHOAN,3,0) Hàm này sẽ trả về tên của sổ cái tương ứng với tài khoản mà bạn đã nhập vào ô A7. - Tại ô I8 (Số dư nợ đầu kỳ) ta nhập vào hàm sau: =VLOOKUP($A$7,BCDSPS!$C$9:$I$39,2,0) Hàm này sẽ trả về số dư nợ đầu kỳ của tài khoản mà bạn đã nhập vào ô A7 được lấy từ Bảng cân đối tài khoản trong sheet BCDSPS. - Tại ô A10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!A7," ") Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô B10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7," ") Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô C10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7," ") Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô D10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7," ") Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô E10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!E7,"") Hàm này sẽ trả về tài khoản đối ứng bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô F10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!F7,"") Hàm này sẽ trả về tài khoản đối ứng bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô G10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!$G7,"") Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô H10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!$H7,"") Hàm này sẽ trả về giá trị phát sinh bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. Đặt chuột vào ô A10, kéo và giữ chuột đến ô H10, dùng Fill handle kéo chuột xuống hết Sổ cái tài khoản. - Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(G9:G39) Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào ô A7. - Tại ô H40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(H9:H39) Hàm này sẽ trả về tổng phát sinh có của tài khoản đã nhập vào ô A7. - Tại ô I41 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =I8+G40-H40 Hàm này sẽ trả về số dư Nợ cuối kỳ của tài khoản đã nhập vào ô A7. Lưu ý: Mẫu sổ cái thiết kế phải có số dòng nhiều hơn hoặc tốt nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu. Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ liệu là do không thỏa mãn điều kiện của hàng. Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp chuột vào đó sẽ có một danh sách những nội dung diễn giải các nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ (Nonblanks), nhấp chuột vào đó. Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và chỉ cho hiển thị những dòng nào có dữ liệu (không trống). Sổ cái có số dư bên Có Nhập vào ô A7 tài khoản trên sổ cái mà bạn muốn xem. Ô A7 được định dạng như ô A7 của Sổ cái có số dư bên Nợ. Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện trên sổ nhưng vẫn tồn tại. Trên dòng tiêu đề sổ ta nhập vào hàm sau: =VLOOKUP($A$7,TAIKHOAN,3,0) Hàm này sẽ trả về tên của sổ cái tương ứng với tài khoản mà bạn đã nhập vào ô A7. - Tại ô I8 (Số dư Có đầu kỳ) ta nhập vào hàm sau: =VLOOKUP($A$7,BCDSPS!$C$9:$I$39,3,0) Hàm này sẽ trả về số dư Có đầu kỳ của tài khoản mà bạn đã nhập vào ô A7 được lấy từ Bảng cân đối tài khoản trong sheet BCDSPS. - Tại ô A10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!A7," ") Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô B10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7," ") Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô C10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7," ") Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô D10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7," ") Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô E10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!E7,"") Hàm này sẽ trả về tài khoản đối ứng bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô F10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!F7,"") Hàm này sẽ trả về tài khoản đối ứng bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô G10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!G7,"") Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô H10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!H7,"") Hàm này sẽ trả về giá trị phát sinh bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. Đặt chuột vào ô A10, kéo và giữ chuột đến ô H10, dùng Fill handle kéo chuột xuống hết Sổ cái tài khoản. - Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(G9:G39) Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào ô A7. - Tại ô H40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(H9:H39) Hàm này sẽ trả về tổng phát sinh có của tài khoản đã nhập vào ô A7. - Tại ô I41 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =I8+G40-H40 Hàm này sẽ trả về số dư Có cuối kỳ của tài khoản đã nhập vào ô A7. Lưu ý: Mẫu sổ cái thiết kế phải có số dòng nhiều hơn hoặc tốt nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu. Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ liệu là do không thỏa mãn điều kiện của hàng. Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp chuột vào đó sẽ có một danh sách những nội dung diễn giải các nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ (Nonblanks), nhấp chuột vào đó. Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và chỉ cho hiển thị những dòng nào có dữ liệu (không trống). Sổ cái không có số dư Nhập vào ô A7 tài khoản trên sổ cái mà bạn muốn xem. Ô A7 được định dạng như ô A7 của Sổ cái có số dư bên Nợ. Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện trên sổ nhưng vẫn tồn tại. Trên dòng tiêu đề sổ ta nhập vào hàm sau: =VLOOKUP($A$7,TAIKHOAN,3,0) Hàm này sẽ trả về tên của sổ cái tương ứng với tài khoản mà bạn đã nhập vào ô A7. - Tại ô A10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!A7," ") Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô B10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7," ") Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô C10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7," ") Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô D10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7," ") Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô E10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!E7,"") Hàm này sẽ trả về tài khoản đối ứng bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô F10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!F7,"") Hàm này sẽ trả về tài khoản đối ứng bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô G10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!G7,"") Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô H10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!H7,"") Hàm này sẽ trả về giá trị phát sinh bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. Đặt chuột vào ô A10, kéo và giữ chuột đến ô H10, dùng Fill handle kéo chuột xuống hết Sổ cái tài khoản. - Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(G9:G39) Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào ô A7. - Tại ô H40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(H9:H39) Hàm này sẽ trả về tổng phát sinh có của tài khoản đã nhập vào ô A7. Lưu ý: Mẫu sổ cái thiết kế phải có số dòng nhiều hơn hoặc tốt nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu. Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ liệu là do không thỏa mãn điều kiện của hàm trong hàng đó. Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp chuột vào đó sẽ có một danh sách những nội dung diễn giải các nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ (Nonblanks), nhấp chuột vào đó. Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và chỉ cho hiển thị những dòng nào có dữ liệu (không trống). 2.4. Sổ chi tiết tài khoản Sổ chi tiết tài khoản là sổ chi tiết của một tài khoản, nhìn vào sổ chi tiết tài khoản ta có thể biết số phát sinh của các tài khoản con của tài khoản đó. Tổng phát sinh của các tài khoản con của một tài khoản bằng số phát sinh của tài khoản đó. 2.4.1. Thiết kế sổ chi tiết tài khoản Thêm vào một sheet bên cạnh sheet SoCai3 đặt tên là SCT_Tài khoản tương ứng, ví dụ SCT_642. Sổ chi tiết tài khoản bao gồm các cột sau: - Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ chi tiết hiện hành. - Chứng từ: + SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ chi tiết hiện hành. + Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ chi tiết hiện hành. - Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ chi tiết hiện hành. - TK Đối ứng: Tài khoản đối ứng với tài khoản trên sổ chi tiết hiện hành trong nghiệp vụ kế toán phát sinh. - Mã CP: Mã tài khoản con của tài khoản trên sổ chi tiết hiện hành. - Ghi nợ TK: Giá trị phát sinh bên Nợ của tài khoản trên sổ chi tiết hiện hành. Tùy thuộc là sổ chi tiết của tài khoản nào ta sẽ ghi số hiệu tài khoản đó. Ví dụ: Ghi nợ TK642. - Các cột Mã của tài khoản con: Tùy thuộc vào tài khoản trên sổ chi tiết có những tài khoản con nào mà sẽ có bấy nhiêu cột. Ví dụ như tài khoản 642 có các cột như: + CPPB: Chi phí trả trước phân bổ vào chi phí quản lý trong kỳ. + CPKH: Chi phí khấu hao tài sản cố định trích vào chi phí quản lý trong kỳ. + CPMN: Chi phí dịch vụ mua ngoài phục vụ quản lý trong kỳ. + CPDT: Chi phí điện thoại phục vụ nhu cầu quản lý trong kỳ. + CPK: Chi phí bằng tiền khác phục vụ quản lý trong kỳ. + CPTL: Chi phí tiền lương nhân viên quản lý. … Lưu ý: Đặt tên của các cột này phải giống hệt mã tài khoản con của tài khoản trên sổ chi tiết hiện hành. Những mã này được nhập trên cột Mã KH trong Sổ nhật ký chung tương ứng với các nghiệp vụ phát sinh có tài khoản phản ánh là tài khoản trên sổ chi tiết. - Ghi chú: Để ghi chú những gì cần thiết. - SPSTK: Số phát sinh trong kỳ - Cộng SPS: Cộng số phát sinh trong kỳ Chọn toàn bộ vùng dữ liệu trong sổ chi tiết sau đó vào menu Data/Filter/Autofilter Vậy là ta đã thiết lập xong chức năng lọc Autofilter cho sổ chi tiết. 2.4.2. Xử lý dữ liệu trong sổ chi tiết tài khoản Lưu ý trước tiên khi làm sổ chi tiết tài khoản là ta phải nhập mã cho tài khoản của sổ chi tiết trên Sổ nhật ký chung. Điều này rất dễ dàng, chỉ cần khi nhập dữ liệu vào Sổ nhật ký chung, với các nghiệp vụ phát sinh có tài khoản phản ánh mà sau này cần mở sổ chi tiết ta chỉ việc nhập thêm mã cho tài khoản đó vào cột Mã KH trong Sổ nhật ký chung. Cột này khi in sẽ được ẩn đi, ta chỉ sử dụng cột này cho mục đích xử lý dữ liệu được dễ dàng. Dữ liệu trên sổ chi tiết đều được lấy từ Sổ nhật ký chung. Ở đây sẽ giới thiệu Sổ chi tiết tài khoản 642 – Chi phí quản lý kinh doanh. Các sổ chi tiết khác làm tương tự, chỉ hơi khác một chút nhưng nếu hiểu cách làm của sổ chi tiết 642 bạn sẽ dễ dàng làm được các sổ chi tiết tài khoản khác. Nhập vào ô A7 tài khoản sổ chi tiết mà bạn muốn xem. Ô A7 được định dạng như ô A7 của Sổ cái. Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện trên sổ nhưng vẫn tồn tại. - Tại ô A9 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7;SoNKC!$F7=$A$7);SoNKC!A7;" ") Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô B9 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7," ") Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô C9 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7," ") Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô D9 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7," ") Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô E9 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7;SoNKC!$F7;IF(SoNKC!$F7=$A$7; SoNKC!$E7;"")) Hàm này sẽ trả về tài khoản đối ứng của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô F9 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7;SoNKC!I7;"") Hàm này sẽ trả về mã của tài khoản trên Sổ chi tiết đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô G9 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7;SoNKC!$G7;"") Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô H9 nhập vào hàm sau: =IF($F9=H$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPPB, được lấy từ sổ Nhật ký chung. - Tại ô I9 nhập vào hàm sau: =IF($F9=I$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPKH, được lấy từ sổ Nhật ký chung. - Tại ô J9 nhập vào hàm sau: =IF($F9=J$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPMN, được lấy từ sổ Nhật ký chung. - Tại ô K9 nhập vào hàm sau: =IF($F9=K$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPDT, được lấy từ sổ Nhật ký chung. - Tại ô L9 nhập vào hàm sau: =IF($F9=L$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPK, được lấy từ sổ Nhật ký chung. - Tại ô M9 nhập vào hàm sau: =IF($F9=M$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPTL, được lấy từ sổ Nhật ký chung. - Tại ô N9 nhập vào hàm sau: =IF(SoNKC!F7=$A$7;SoNKC!H7;"") Hàm này sẽ trả về số phát sinh bên có của tài khoản trên Sổ chi tiết, được lấy từ sổ Nhật ký chung. - Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(G9:G39) Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào ô A7. Dùng Fill handle kéo từ ô G40 đến ô N40. Đây là hàng tính tổng số phát sinh của các tài khoản con của tài khoản trên sổ chi tiết. Riêng ô N40 là tổng phát sinh bên Có của tài khoản trên sổ chi tiết đã nhập vào ô A7. Lưu ý: Mẫu sổ chi tiết thiết kế phải có số dòng nhiều hơn hoặc tốt nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu. Đặt chuột vào ô A9, giữ và kéo chuột đến ô N9, dùng Fill handle kéo đến hết sổ. Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ liệu là do không thỏa mãn điều kiện của hàm trong những hàng đó. Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp chuột vào đó sẽ có một danh sách những nội dung diễn giải các nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ (Nonblanks), nhấp chuột vào đó. Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và chỉ cho hiển thị những dòng nào có dữ liệu (không trống). 2.5. Sổ tổng hợp, chi tiết công nợ phải thu, phải trả Trước khi tạo sổ tổng hợp và chi tiết công nợ phải, phải trả ta sẽ thiết kế 1 bảng danh mục khách hàng và 1 bảng danh mục nhà cung cấp để tiện cho việc theo dõi công nợ. 2.5.1. Bảng danh mục khách hàng - Bên cạnh sheet DMTK thêm vào 1 sheet và đặt tên sheet là DMKH. - Thiết kế một bảng Danh mục khách hàng như bảng sau: - Với mỗi khách hàng bạn hãy tạo một mã để phân biệt và tuyệt đối không được có mã trùng nhau. - Nhập mã khách hàng vào cột Mã KH và tên khách hàng vào cột Tên khách hàng. - Sau khi tạo xong bảng Danh mục khách hàng ta sẽ tạo một bảng tham chiếu là bảng Danh mục khách hàng này để tiện sử dụng. Cách tạo bảng tham chiếu như sau: - Vào menu Insert/Name/Define… Hộp thoại Define Name xuất hiện Trong hộp Names in workbook nhập tên bảng tham chiếu. Nhấp chuột vào nút ở cuối hộp Refers to, sau đó bạn hãy chọn vùng tham trong bảng Danh mục tài khoản. Nhấp chuột vào nút đóng màu đỏ của hộp Define Name – Refers to. Nhấp chuột vào OK. Vậy là ta đã có một bảng tham chiếu có tên là DMKH. 2.5.2. Bảng danh mục nhà cung cấp - Bên cạnh sheet DMKH thêm vào 1 sheet và đặt tên sheet là DMNCC. - Thiết kế bảng Danh mục nhà cung cấp như sau: - Với mỗi nhà cung cấp bạn hãy tạo một mã để phân biệt và tuyệt đối không được có mã trùng nhau. - Nhập mã nhà cung cấp vào cột Mã NCC và tên nhà cung cấp vào cột Tên nhà cung cấp. - Sau khi tạo xong bảng Danh mục nhà cung cấp ta sẽ tạo một bảng tham chiếu là bảng Danh mục nhà cung cấp này để tiện sử dụng. Cách tạo bảng tham chiếu như sau: - Vào menu Insert/Name/Define… Hộp thoại Define Name xuất hiện Trong hộp Names in workbook nhập tên bảng tham chiếu. Nhấp chuột vào nút ở cuối hộp Refers to, sau đó bạn hãy chọn vùng tham trong bảng Danh mục nhà cung cấp. Nhấp chuột vào nút đóng màu đỏ của hộp Define Name – Refers to. Nhấp chuột vào OK. Vậy là ta đã có một bảng tham chiếu có tên là DMNCC. 2.5.3. Sổ tổng hợp công nợ phải thu Thiết kế sổ tổng hợp công nợ phải thu Thêm vào một sheet đặt tên là BTH131. Sổ tổng hợp công nợ phải thu bao gồm các cột sau: - STT: Số thứ tự khách hàng có phát sinh công nợ. - Mã khách: Mã khách hàng có công nợ. - Tên khách hàng: Tên khách hàng có công nợ. - Số dư đầu kỳ: + Nợ: Số dư Nợ TK 131 chi tiết theo từng khách hàng khách hàng (Số còn phải thu đầu kỳ) + Có: Số dư Có TK 131 chi tiết theo từng khách hàng (Số tiền ứng trước của khách hàng ở thời điểm đầu kỳ) - Số phát sinh trong kỳ: + Nợ: Phát sinh Nợ TK 131 chi tiết theo từng khách hàng (nghiệp vụ bán hàng hóa, dịch vụ). + Có: Phát sinh Có TK 131 chi tiết theo từng khách hàng (Thu tiền bán hàng hóa dịch vụ) - Số dư cuối kỳ: + Nợ: Số dư Nợ TK 131 chi tiết theo từng khách hàng khách hàng (Số còn phải thu cuối kỳ) + Có: Số dư Có TK 131 ch