Thông thường, khi lập bảng cân đối số phát sinh (CĐPS) trên Excel, hàm SUMIF được sử dụng phổ biến. Tuy nhiên, hàm này có một hạn chế: phải sử dụng công thức khác nhau cho tài khoản (TK) tổng hợp và TK chi tiết, gây bất tiện và dễ nhầm lẫn. Bài viết này sẽ hướng dẫn bạn cách sử dụng hàm SUMPRODUCT để lập bảng CĐPS trên Excel một cách hiệu quả, chỉ với một công thức duy nhất cho cả TK tổng hợp và TK chi tiết.
Bảng cân đối số phát sinh với SUMIF
Như hình trên, nếu chỉ sử dụng SUMIF, ta chỉ tính được cho các TK chi tiết mà không tính được cho TK tổng hợp.
Phân Tích Yêu Cầu và Giải Pháp
Đặc điểm của tài khoản kế toán là TK tổng hợp có 3 ký tự, còn TK chi tiết luôn có 3 ký tự đầu trùng với 3 ký tự của TK tổng hợp tương ứng. Do đó, khi xét trên mảng 3 ký tự đầu của tài khoản trong sổ nhật ký chung (NKC), ta có thể tính được cho cả TK tổng hợp.
Phân tích tài khoản kế toán
Để xét từng tài khoản trong cột TK Nợ và TK Có cho 3 ký tự đầu, ta cần sử dụng công thức mảng với hàm SUMPRODUCT. Hàm LEFT sẽ được dùng để xét phần bên trái của mảng, và hàm LEN để kiểm tra số ký tự của tài khoản.
Kỹ năng Excel vẫn rất quan trọng đối với kế toán, ngay cả khi đã có phần mềm hỗ trợ. Nếu bạn muốn nâng cao kỹ năng Excel của mình, hãy tham khảo khóa học Excel tại:
Khóa học Excel
Hướng Dẫn Xây Dựng Công Thức
Phát Sinh Nợ
Để tính phát sinh nợ, ta xét trên cột TK Nợ trong sổ NKC (ví dụ cột G, từ G7 đến G27).
- Phần bên trái của TK Nợ:
LEFT(NKC!$G$7:$G$27, Số ký tự cần lấy)(vùng$G$7:$G$27được cố định). - Số ký tự cần lấy:
LEN(Tài khoản đang xét trong bảng CĐPS)
Ví dụ với TK 111 (ô A7):
- Độ dài số ký tự:
LEN(CDPS!A7) = 3 - Phần bên trái của TK Nợ:
LEFT(NKC!$G$7:$G$27,LEN(CDPS!A7)) = LEFT(NKC!$G$7:$G$27,3)(lấy 3 ký tự đầu của mỗi phần tử trong mảng G7:G27). - So sánh với TK đang xét:
LEFT(CDPS!A7,LEN(CDPS!A7)) - Nếu hai phần bằng nhau, lấy giá trị tương ứng từ cột Số tiền (cột I) trong sổ NKC.
Công thức hoàn chỉnh cho phát sinh nợ (ô E7):
=SUMPRODUCT((LEFT(NKC!$G$7:$G$27,LEN(CDPS!A7))=LEFT(CDPS!A7,LEN(CDPS!A7)))*(NKC!$I$7:$I$27))Phát Sinh Có
Tương tự, công thức cho phát sinh có (ô F7) xét trên cột TK Có trong sổ NKC (ví dụ cột H, từ H7 đến H27):
=SUMPRODUCT((LEFT(NKC!$H$7:$H$27,LEN(CDPS!A7))=LEFT(CDPS!A7,LEN(CDPS!A7)))*(NKC!$I$7:$I$27))
Kết quả sau khi áp dụng công thức
Kết Luận
Công thức SUMPRODUCT tuy phức tạp hơn SUMIF nhưng mang lại hiệu quả tối ưu hơn, giúp tiết kiệm thời gian và tránh sai sót. Chỉ cần một công thức duy nhất cho toàn bộ bảng CĐPS. Việc nắm vững cách sử dụng SUMPRODUCT và LEFT dạng mảng sẽ giúp bạn ứng dụng công thức này hiệu quả.
Để hiểu rõ hơn, bạn có thể tải file ví dụ tại: http://bit.ly/2rljhPZ
Kế toán tổng hợp trên Excel là kỹ năng cần thiết, giúp bạn nắm được cách tổ chức và quản lý dữ liệu kế toán, đồng thời tự xây dựng công thức cho các báo cáo tài chính. Điều này cho phép bạn dễ dàng kiểm tra kết quả và hiểu được ảnh hưởng của thay đổi dữ liệu đến các báo cáo.
Discussion about this post