Hàm Sumproduct trong excel là gì? Cách sử dụng có điều kiện

Excel là một trong những công cụ giúp tối ưu hóa việc lưu trữ và trình bày thông tin dưới dạng văn bản đồng thời xử lý nhanh, chính xác hàng triệu phép tính. Mỗi một hàm trong excel mang một ý nghĩa riêng và thực hiện một phép tính riêng biệt. Một trong số hàm có công dụng hữu ích nhưng chưa được đông đảo người dùng biết đến đó là sumproduct. Vậy hàm sumproduct trong excel là gì? Cách sử dụng có điều kiện như thế nào? Hãy cùng tìm hiểu và khám phá những tính năng đặc biệt của hàm trong bài viết này nhé.

Hàm sumproduct là gì? Ý nghĩa như thế nào trong kế toán, excel

Hàm sumproduct là gì?

Hàm sumproduct có thể hiểu đơn giản là sự kết hợp giữa hai hàm sum – sử dụng để tính tổng và hàm product – dùng để tính tích.

Có thể hiểu đơn giản bằng phép dưới đây:

Sumproduct = Sum (Product)

Trong đó: Product là tích

• Sum là tổng
• Sumproduct đơn giản là Tổng của các Tích

Ý nghĩa hàm sumproduct

Khi thực hiện hàm sumproduct sẽ trả về tổng của các sản phẩm trong các phạm vi hoặc mảng tương ứng. Thao tác được mặc định là phép nhân, nhưng bổ sung, trừ và phân vùng cũng có thể.

Công thức: =SUMPRODUCT(array1, [array2], [array3], [array4]…)

Trong đó:

• array1: Là tham số bắt buộc, là đối số của mảng thứ nhất có chứa các thành phần bạn muốn nhân rồi sau đó cộng tổng lại với nhau.
• [array2], [array3],… là những tham số tùy chọn. Từ mảng thứ 2 trở đi (tối đa có thể lên đến 255) là các mảng mà bạn muốn nhân các thành phần của nó và cộng tổng.

Cách dùng hàm sumproduct

Hàm sumproduct

Tập dữ liệu dưới đây có chứa 6 tên trong các hàng và trong các cột có chứa. Bây giờ Sếp bạn muốn bạn cộng tất cả cho tháng Sáu.

Bạn sẽ thực hiện như sau:

= SUMPRODUCT(array 1,[array 2],[array 3],…)

Ta sẽ nhận được kết quả trả về như bảng trên.

Kết hợp với hàm if

Để có thể sử dụng hàm sumproduct kết hợp với if chúng ta hãy cùng tìm hiểu ví dụ chi tiết dưới đây:

Với đề bài trên, ta phải tính tổng Sp-1 với điều kiện đó là tên sản phẩm là Sp-1 và thuộc miền Nam.

Với điều kiện tên sản phẩm Sp1 thì bước đầu tiên chúng ta phải tách lấy Sp1 từ các mã hàng đó bằng cách sử dụng hàm LEFT để lấy ký tự bên trái “Sp1”

Công thức =LEFT(A2:A7,3).

Trong đó A2: A7 là mảng tên, còn 3 là số ký tự cần lấy.

Tiếp đó sử dụng hàm RIGHT để lấy 3 ký tự bên phải “Nam”

Công thức =RIGHT(B1:E1,3).

Trong đó B1: E1 là mảng tên miền, còn 3 là số ký cần lấy.

Sau khi xử lý được 2 điều kiện thì hàm SUMPRODUCT sẽ tổng hợp dữ liệu theo công thức:

= SUMPRODUCT((LEFT(A2:A7,3)=”Sp1″)*RIGHT(B1:E1,3)=”Nam”)*(B2:E7)).

Trong đó:

• LEFT(A2:A7,3)=”Sp1″ : 3 ký tự tách tên sản phẩm = “Sp1”
• RIGHT(B1:E1,3)= “Nam”: 3 ký tự miền lấy =”Nam”
• B2:E7: vùng số lượng của tất cả sản phẩm và các miền
• Dấu * để kết nối các điều kiện với nhau.

Dưới đây là kết quả cần tính:

Có nhiều điều kiện dùng để đếm

Để có thể sử dụng ta cùng xét ví dụ dưới đây:

Đếm số giao dịch của mặt hàng A thỏa mãn được điều kiện: Số lượng bán thực tế < số lượng bán dự kiến.

Thực sự là bài toán khó khăn hơn rất nhiều.

Ta áp dụng hàm như sau:

= Sumproduct((A2:A10 = “A”)*(B2:B10<C2:C10)) = 2

Dấu * giúp kết nối các điều kiện với nhau.

Đây là một cách vận dụng rất phổ biến của hàm Sumproduct trong thực tế công việc tại các doanh nghiệp.

Hàm sumproduct tính tổng nhiều điều kiện

Ngoài việc ứng dụng để đếm số các phần tử trong mảng phù hợp với điều kiện thì hàm sumproduct còn có thể cộng tổng các phần tử thuộc mảng phù hợp với điều kiện.

Dưới đây là ví dụ chi tiết:

Tính tổng số vốn đã đổ vào các dự án lỗ. Để làm được bài toán này, ta cần đưa ra điều kiện Vốn > Doanh thu, sau đó tính tổng dòng Vốn đầu tư phù hợp điều kiện Vốn > Doanh thu.

Áp dụng công thức:

= SUMPRODUCT(–(<mảng 1><điều kiện tương tác><mảng 2>),<mảng chứa dữ liệu tính tổng>)

Ta sẽ nhận được kết quả như mong muốn

Cách sử dụng nâng cao

Bạn cũng có thể sử dụng hàm sumproduct nâng cao để tính tổng nhiều điều kiện.

Với điều kiện là tìm số dự án đầu tư có mức lãi và vốn trên 10 tỷ. Ta có hai điều kiện là Vốn đầu tư < Doanh thu và Vốn >10.

Điều kiện Vốn đầu tư < Doanh thu và Vốn > 10 tỷ

Kết quả chỉ cần thỏa mãn với một trong các điều kiện

Điểm khác biệt giữa hai phương thức trên đó là có sự xuất hiện của dấu phẩy nằm giữa hai vùng điều kiện.

– Khi có dấu phẩy có nghĩa là bạn đưa ra điều kiện “và”, và cần phải đáp ứng đủ cả hai điều kiện mới cho ra điều kiện đúng.

– Bỏ dấu phẩy đi tức là bạn đưa ra điều kiện “hoặc”, bạn chỉ cần đáp ứng một trong hai điều kiện thì đã thu được điều kiện đúng.

Trên đây là các thủ thuật excel hướng dẫn cách sử dụng chi tiết hàm sumproduct trong excel. Chúc các bạn có thể ứng dụng linh hoạt hàm trong thực tiễn, từ đó giúp cho việc tính toán và xử lý dữ liệu được trở nên đơn giản hơn.