Excel kế toán: Ứng dụng hàm Sumproduct – Phần 2

Chia sẻ bài viết này:
  •  
  •  
  •  
  •  
  •  
  •  
  •   
  •   

Hôm nay hocexcel.online gửi tới các bạn seri bài viết về chủ đề Excel với kế toán: “Ứng dụng hàm SUMPRODUCT”. Đây là một hàm có sức mạnh rất lớn, có thể thay thế hầu hết mọi hàm khi làm kế toán trên excel, hay nói cách khác khi nghĩ tới làm kế toán trên excel là chúng ta phải nghĩ ngay tới hàm SUMPRODUCT. Tại sao nó lại quan trọng như vậy? Sau đây chúng ta cùng tìm hiểu cách ứng dụng hàm SUMPRODUCT sẽ rõ.

Nội dung Seri bài viết:

Phần 1: Cấu trúc hàm SUMPRODUCT, ứng dụng vào việc tính tổng theo nhiều điều kiện, so sánh với hàm SUMIFS

Phần 2: Tính tổng phát sinh của tài khoản tổng hợp dựa trên ghi sổ của tài khoản chi tiết

Phần 3: Ứng dụng hàm SUMPRODUCT trong kế toán kho

Phần 4: Ứng dụng hàm SUMPRODUCT trong kế toán tiền lương: xử lý dữ liệu máy chấm công

Ứng dụng 2: Tính tổng phát sinh của tài khoản tổng hợp dựa trên ghi sổ của tài khoản chi tiết

Đề bài: Cho bảng dữ liệu từ vùng A1:E9, yêu cầu tính tổng phát sinh Nợ và phát sinh Có theo thời gian xác định tại vùng H3:H4, theo tài khoản chi tiết 1111 và tài khoản tổng hợp 111 (áp dụng công thức SUMPRODUCT)

Cách làm:

Bước 1: Phân tích đề bài:

Các điều kiện cần tính:

  • H6 = Tổng sốtiền + Phát sinh Nợ Tài khoản 111 + Thời gian từ ngày H3 + Thời gian đến ngày H4
  • I6 = Tổng sốtiền + Phát sinh Cợ Tài khoản 111 + Thời gian từ ngày H3 + Thời gian đến ngày H4
  • H7 = Tổng sốtiền + Phát sinh Nợ Tài khoản 1111 + Thời gian từ ngày H3 + Thời gian đến ngày H4
  • I7 = Tổng sốtiền + Phát sinh Cợ Tài khoản 1111 + Thời gian từ ngày H3 + Thời gian đến ngày H4

Như vậy khi áp dụng hàm SUMPRODUCT chúng ta cần sử dụng 4 Array

Bước 2: Xây dựng công thức (hàm) SUMPRODUCT

Bằng việc thấu hiểu rằng hầu hết nhân viên văn phòng thường mất nhiều thời gian tra cứu cách sử dụng các hàm, các tính năng trong Excel, gặp khó khăn trong lồng ghép hàm, xử lý dữ liệu, công việc bận rộn không có thời gian học. Nên Học Excel Online đã thiết kế ra chương trình Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là chương trình học online, học viên có thể chủ động học mọi lúc mọi nơi, trao đổi trực tiếp với giảng viên tại bài giảng trong suốt quá trình học.

Với khoá học này học viên sẽ nắm được: :

  • Trọn bộ công cụ định dạng báo cáo chuyên nghiệp: Format, Cell, Number, Style,...không mất thời gian cả ngày ngồi sửa báo cáo
  • Toàn bộ kỹ năng lọc dữ liệu, lọc dữ liệu nâng cao trong Excel phục vụ cho mục đích trích xuất dữ liệu
  • Data Validation kiểm soát dữ liệu khi nhập vào một vùng trong bảng tính
  • TẤT TẦN TẬT hơn 100 hàm thông dụng, công thức mảng trong Excel, học tới đâu nhớ tới đó
  • Bộ 36 PHÍM TẮT giúp thao tác nhanh gấp đôi
  • BÁO CÁO SIÊU TỐC trong 1 phút với Pivot Table
  • Hỏi đáp trực tiếp tại bài giảng với giảng viên trong suốt quá trình học
  • CHUYÊN NGHIỆP trong báo cáo bằng Biểu đồ, đồ thị,....
  • 142+ bài giảng, 13+ giờ học, 200+ ví dụ thực tế cùng chuyên gia
  • 100+ Kỹ năng, thủ thuật Excel thông dụng với các hàm: SUMIF, HLOOKUP, VLOOKUP, DATEDIF…

Xét tại ô H6:

  • Array1 là mảng cần tính tổng, ở đây là cột Số tiền =E2:E9
  • Array2 là mảng Tài khoản Nợ với những giá trị là 111,  là vùng C2:C9=G6
  • Array3 là mảng Ngày với những giá trị là Từ ngày H3, là vùng A2:A9>=H3
  • Array4 là mảng Ngày với những giá trị là Đến ngày H4, là vùng A2:A9<=H4

H6 =SUMPRODUCT($E$2:$E$9*($C$2:$C$9=$G6)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

Tương tự chúng ta làm cho các kết quả khác, lưu ý những yêu cầu về Phát sinh Có sẽ tham chiếu vùng D2:D9 thay cho C2:C9

I6 =SUMPRODUCT($E$2:$E$9*($D$2:$D$9=$G6)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

H7 =SUMPRODUCT($E$2:$E$9*($C$2:$C$9=$G7)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

I7 =SUMPRODUCT($E$2:$E$9*($D$2:$D$9=$G7)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

Công thức trên chỉ đúng với tài khoản 1111 vì các nội dung trên ghi sổ tại cột TK Nợ và TK Có có tài khoản này, còn không có tài khoản 111.

Vậy làm thế nào để tùy biến công thức trên để vẫn  tính được với tài khoản 111 (được hiểu là tổng của các tài khoản chi tiết của tài khoản 111)?

Xem thêm: học Excel kế toán online cùng các chuyên gia

Bước 3: Xử lý mảng cho Tài khoản tổng hợp

Ở cột TK Nợ, chúng ta thấy tài khoản tổng hợp có 3 chữ số, nên chúng ta xét phần bên trái của mỗi đối tượng trong mảng này như sau:

Array2 = LEFT(C2:C9,3)=”111″

Áp dụng trong hàm SUMPRODUCT như sau:

H6 = SUMPRODUCT($E$2:$E$9*(LEFT($C$2:$C$9,3)=”111″)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

I6 =  SUMPRODUCT($E$2:$E$9*(LEFT($D$2:$D$9,3)=”111″)*($A$2:$A$9>=$H$3)*($A$2:$A$9<=$H$4))

Như vậy chúng ta đã có thể tùy biến mảng trong hàm SUMPRODUCT để áp dụng cho trường hợp đối tượng cần tính chỉ gồm một phần trong mảng.

Trong các bài học sau chúng ta sẽ tìm hiểu thêm những trường hợp tùy biến khác của hàm SUMPRODUCT trong kế toán để sử dụng hàm này hiệu quả hơn.

Cảm ơn các bạn đã luôn theo dõi và đồng hành cùng Học Excel Online chúng tôi.!


Chia sẻ bài viết này:
  •  
  •  
  •  
  •  
  •  
  •  
  •   
  •