Hướng dẫn cách xây dựng công thức Sumproduct trên bảng cân đối số phát sinh trên Excel

1. Đặt vấn đề

Thông thường khi lập bảng cân đối số phát sinh trên Excel, chúng ta thường sử dụng hàm SUMIF. Tuy nhiên có 1 hạn chế đó là:

Việc sử dụng xen lẫn 2 công thức khác nhau như vậy thật bất tiện và dễ nhầm lẫn, bởi trong bảng CĐPS có rất nhiều tài khoản, bao gồm cả TK tổng hợp và TK chi tiết.

Trong hình trên, chỉ sử dụng Sumif thì chúng ta thấy có tính được cho các TK chi tiết nhưng không tính được cho TK tổng hợp

Có cách nào chỉ cần sử dụng 1 công thức duy nhất để tính được cho cả TK tổng hợp và TK chi tiết? Hãy cùng Học Excel Online tìm hiểu nhé.

2. Phân tích yêu cầu

Dựa theo đặc điểm của tài khoản kế toán: Các TK tổng hợp có 3 ký tự, các TK chi tiết sẽ luôn có 3 ký tự đầu là 3 ký tự của TK tổng hợp của nó => Khi xét trên mảng 3 ký tự đầu của tài khoản trong sổ NKC thì chúng ta có thể tính được cho TK tổng hợp.

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

Vậy để xét được mỗi tài khoản trong các cột TK Nợ, TK Có cho 3 ký tự đầu thì chúng ta cần sử dụng tới công thức mảng. Cụ thể là ở đây chúng ta cần dùng tới hàm SUMPRODUCT;

Để xét phần bên trái của mảng, chúng ta sử dụng hàm LEFT;

Để kiểm tra xem tài khoản đó có mấy ký tự, chúng ta sử dụng hàm LEN.

3. Cách xây dựng công thức

Với phát sinh Nợ, chúng ta sẽ xét trên cột TK Nợ trong sổ NKC (cột G)

Phần bên trái của TK Nợ sẽ là LEFT(NKC!$G$7:$G$27, Số ký tự cần lấy)   (ở đây ví dụ phần cột TK Nợ từ ô G7 tới G27, vùng này sẽ được cố định để không thay đổi khi sao chép công thức)

Số ký tự cần lấy = LEN(Tài khoản đang xét trong bảng CDPS)

Ví dụ xét với TK 111 (ô A7)

  • Độ dài số ký tự trong ô A7 = 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) = Xét trong mảng G7:G27 với mỗi phần tử trong mảng chỉ lấy tới 3 ký tự đầu
  • So sánh với chính TK đang xét = LEFT(CDPS!A7,LEN(CDPS!A7))
  • Nếu 2 phần này bằng nhau thì sẽ lấy kết quả tương ứng theo cột Số tiền (cột I) trong sổ NKC

Khi đó công thức hoàn chỉnh là:

E7=SUMPRODUCT((LEFT(NKC!$G$7:$G$27,LEN(CDPS!A7))=LEFT(CDPS!A7,LEN(CDPS!A7)))*(NKC!$I$7:$I$27))

Tương tự với Phát sinh có ở ô F7 xét theo cột TK Có trong sổ NKC (Cột H)

F7=SUMPRODUCT((LEFT(NKC!$H$7:$H$27,LEN(CDPS!A7))=LEFT(CDPS!A7,LEN(CDPS!A7)))*(NKC!$I$7:$I$27))

Lưu ý:

Công thức SUMPRODUCT là công thức dạng mảng, do đó khó hình dung hơn so với công thức SUMIF, tuy nhiên hiệu quả mang lại thì tối ưu hơn nhiều so với SUMIF

Chỉ cần dùng duy nhất 1 công thức SUMPRODUCT này là có thể áp dụng cho toàn bộ bảng CDPS. Do đó tiết kiệm thời gian, tránh sai sót so với cách dùng kết hợp 2 hàm cho 2 loại tài khoản.

Cần hiểu rõ cách viết của hàm SUMPRODUCT và cách sử dụng hàm LEFT dạng mảng

Để hiểu rõ hơn cách làm trên Excel, mời các bạn tải về file đính kèm: http://bit.ly/2rljhPZ

——

Làm kế toán tổng hợp trên Excel rất cần thiết, bởi trên Excel bạn có thể nắm được cách tổ chức, quản lý dữ liệu kế toán, đồng thời tự tay xây dựng công thức lên các báo cáo tài chính. Khi đó bạn có thể dễ dàng kiểm tra kết quả, biết được những thay đổi dữ liệu trong sổ NKC sẽ ảnh hưởng như thế nào tới kết quả trong các báo cáo.

Tác giả: duongquan211287

Bài viết liên quan