Excel là ứng dụng hỗ trợ làm việc văn phòng rất hiệu quả. Trong excel có hỗ trợ nhiều hàm giúp việc tính toán trở nên đơn giản hơn, một trong số đó phải kể đến hàm Sumproduct. Vậy hàm sumproduct để làm gì?. Bài viết dưới đây sẽ cung cấp những thông tin bổ ích về loại hàm này và hướng dẫn bạn cách dùng hàm sumproduct tính tổng nhiều điều kiện

su-dung-Ham-sum-product-3
Sumproduct là hàm gì trong Excel?

Xem thêm: Tổng hợp các  thủ thuật Excel mới nhất

Tìm hiểu về hàm Sumproduct là gì?

Hàm sumproduct là một hàm công thức trong excel thuộc nhóm dữ liệu dạng mảng. Đây là sự kết hợp của hai hàm sum – hàm tính tổng và hàm product – hàm dùng tính tích. 

Cú pháp sử dụng hàm: =sumproduct (array1, [array2], [array3],…)

Trong đó: 

  • array1 là tham số bắt buộc. Đây chính là đối số của mảng thứ nhất, tại đây chứa các thành phần mà bạn muốn nhân, sau đó cộng tổng chúng lại với nhau.
  • [array2], [array3] …chính là tham số tuỳ chọn. Từ mảng thứ hai trở đi là các mảng mà bạn muốn nhân các thành phần trong nó rồi cộng chúng lại với nhau.

Nguyên tắc đếm của hàm sumproduct trong excel

Giá trị của hàm sumproduct tới từ các mảng array trong công thức tính của nó. Phạm vi hoạt động của các mảng này khá linh hoạt, bạn có thể gộp nhiều vùng dữ liệu vào hoặc một vài phần tử của một vùng dữ liệu. Việc tính toán theo các mảng sẽ không gặp giới hạn, việc tính toán trở nên dễ dàng hơn.

Để đạt điều kiện thì mỗi phần tử trong vung đó phải thỏa mãn điều kiện thì sẽ được tính là 1. Kết quả của mỗi lần đếm đưa ra chính là tổng các giá trị thỏa mãn những điều kiện trên. Việc này tương tự với việc tính tổng các giá trị thỏa mãn điều kiện mà chúng ta hay dùng. Tuy nhiên điểm khác biệt chính là mỗi giá trị thỏa mãn sẽ không phụ thuộc vào bất kỳ giá trị nào khác ngoài 1. 

Những lưu ý khi sử dụng hàm sumproduct

  • Các mảng trong hàm sumproduct đều phải có cùng số cột, số hàng. Nếu không cùng số cột số hàng, khi tính kết quả sẽ trả về giá trị lỗi, bạn sẽ không thể thực hiện được phép tính.
  • Trường hợp có một ô nào đó trong mảng không có dạng số, hàm sumproduct tự mặc định giá trị của nó là 0 và tiếp tục tính toán.
  • Số mảng tối đa có thể tính toán bởi hàm sumproduct là 255 mảng với các đời excel từ 2007 trở đi.
  • Hàm này không hỗ trợ ký tự đại diện.
  • Nếu mảng là một phép thử logic, giá trị trả về sẽ là FALSE hoặc TRUE.

Hướng dẫn sử dụng hàm sumproduct tính tổng trong excel

Có nhiều cách dùng hàm sumproduct tính tổng trong excel. Sau đây chính là hướng dẫn cách dùng hàm trong một số trường hợp thường gặp.

Dùng hàm sumproduct tính tổng của các tích

Đây là phần sử dụng cơ bản của hàm này, bạn có thể đơn giản phép tính của mình mà kết quả trả về hoàn toàn chính xác. Điều này giúp tiết kiệm thời gian, công sức của bạn trong làm việc.

Ví dụ 1: tính tổng doanh thu của một cửa hàng có bán nhiều loại mặt hàng. Nếu bạn không biết cách sử dụng hàm sumproduct trong excel thì phép tính của bạn sẽ dài hơn. Bạn phải tính tích từng cột riêng sau đó lại gộp tổng của chúng lại. Hàm sumproduct sẽ hỗ trợ giải quyết rất đơn giản: 

Nhập cú pháp: =SUMPRODUCT (A2:A5,B2:B5) và enter, bạn sẽ nhận được kết quả trả về chính xác.

Ham-sum-product
Dùng hàm sumproduct tính tổng của các tích

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

Đây chính là điểm mạnh của hàm này, giúp hỗ trợ tính toán những trường hợp khó hơn với nhiều mảng phức tạp hơn. Là một nhân viên văn phòng, đặc biệt là kế toán, việc nắm vững các hàm tính toán trong excel là điều cần thiết. Điều này giúp công việc của bạn trở nên suôn sẻ hơn, bạn không còn lo lắng khi phải tính toán quá nhiều số liệu khác nhau. Hãy xem và ứng dụng nó trong công việc nhé, chắc chắn bạn sẽ có hiệu quả làm việc tốt hơn. 

Ví dụ: bạn hãy tính tổng các Sp1 thuộc miền Nam theo dữ liệu trong bảng dưới đây. 

tinh-tong-trong-Ham-sum-product-1
Dùng hàm sumproduct nhiều điều kiện

Chúng ta cùng phân tích đề bài để hiểu rõ hơn nhé. Theo yêu cầu, ta phải tính tổng Sp1 với hai điều kiện phải đáp ứng đó là sản phẩm đó thuộc miền Nam và có tên là Sp1.

Cách tính bài toán này được trình bài theo các bước sau:

Bước 1: Khi xác định được các điều kiện rõ ràng, ta bắt tay vào tính toán. Với điều kiện tên sản phẩm là sp1, nhìn vào bảng ta thấy được không chỉ một mà có nhiều mã hàng có chứ Sp1. Bây giờ, ta phải tách sp1 ra khỏi các mã hàng đó bằng cách dùng công thức sau: =LEFT(A2:A7,3). Trong công thức này, 3 chính là số ký tự cần lấy, A2: A7 chính là mảng tên.

Bước 2: Với điều kiện miền Nam, bạn phải tách chúng ra khỏi mảng các miền. Sử dụng công thức sau để tách: =RIGHT(B1:E1, 3). 

Bước 3: Khi đã xử lý tách điều kiện xong, bây giờ ta dùng hàm sumproduct để tính toán kết quả.

Ta nhập công thức sau: =SUMPRODUCT ((LEFT(A2:A7, 3)= “SP1”)*RIGHT(B1:(B2:E7)). 

Sau đó nhấn enter và ta thu được kết quả chính xác cần tính. 

tinh-tong-theo-dieu-kien-ham-sumproduct_1
Kết quả dùng hàm sumproduct trong Excel 2007 để tính tổng

Một số ví dụ về hàm Sumproduct nâng cao

Hàm SUMPRODUCT linh hoạt và nhiều công dụng hơn rất nhiều khi tính tổng theo điều kiện. Dưới đây là một số hàm Sumproduct tính tổng nhiều điều kiện nâng cao bạn có thể tham khảo

Tính hàm sumproduct có điều kiện

Ví dụ:

vi-du-ham-sumproduct-co-dieu-kien
Ví dụ hàm Sumproduct có điều kiện

Cách dùng hàm SUMPRODUCT có điều kiện ở trên:

  • Biểu thức (C4:C8>140): thực hiện so sánh từng phần tử của mảng với 140.
  • Sau khi so sánh xong các phần tử trong mảng, hàm SUMPRODUCT trở thành: SUMPRODUCT(- {FALSE, FALSE, TRUE, TRUE, FALSE})
  • Dấu âm “ – ” đặt trước mảng để biến các giá trị TRUE/ FALSE về dạng số nguyên -1 và 0 thì kết quả hàm SUMPRODUCT mới trả về số chính xác, nếu không hàm sẽ trả về 0. Có thể sử dụng 2 dấu “-” để trả TRUE/ FALSE về 1 và 0.

Sử dụng hàm SUMPRODUCT nhiều điều kiện

Ví dụ này sử dụng hàm SUMPRODUCT nhiều điều kiện thay cho hàm COUNTIFS vẫn thường sử dụng

vi-du-ham-sumproduct-2-dieu-kien-trong-excel
Ví dụ hàm Sumproduct nhiều điều kiện

Giải thích cách sử dụng hàm SUMPRODUCT nhiều điều kiện:

  • Biểu thức (A4:A8=”táo”): thực hiện kiểm tra từng phần tử của mảng có phải là Táo hay không: kết quả {TRUE, TRUE, FALSE, FALSE, TRUE}
  • Biểu thức (C4:C8>99): thực hiện kiểm tra từng phần tử của mảng có lớn hơn 99 hay không: kết quả {TRUE, TRUE, TRUE, TRUE, FALSE}
  • Dấu * trong hàm biến TRUE/ FALSE về dạng số 1/ 0
    Hàm SUMPRODUCT thực hiện chức năng trả về số sản phẩm thỏa mãn cả 2 điều kiện

Nguyên nhân gặp lỗi value trong hàm Sumproduct

Khi mới học cách sử dụng hàm sumproduct, nhiều người thường mắc phả lỗi #VALUE và dưới đây là một trong những nguyên nhân gây nên tình trạng này

Vùng tham chiếu bị lệch kích thước

Vùng tham chiếu trong đối số của hàm Sumproduct có kích thước không giống nhau, điều này sẽ tạo ra lỗi #VALUE!

Trong vùng tham chiếu có ô chứa lỗi #VALUE!

Trong vùng tham chiếu có 1 hay nhiều ô chứa lỗi #VALUE! cũng là lý do gây ra lỗi #VALUE!

Cách khắc phục: chỉnh sửa lại dữ liệu dùng để tính toán trước khi áp dụng hàm Sumproduct

Kết luận

Trên đây mình đã giới thiệu cho các bạn về hàm sumproduct. Hy vọng với những gì hướng dẫn, mọi người nắm rõ và có thể ứng dụng được vào trong công việc của mình.