Bài hướng dẫn này của Daotaotinhoc sẽ giải thích cho bạn hiểu về công thức và cách sử dụng của hàm SUMPRODUCT trong Excel. Từ đây, bạn sẽ hiểu được cách so sánh mảng, tính tổng có điều kiện và đếm số ô có nhiều điều kiện khác nhau, hay tính trung bình cộng có trọng lượng và thậm chí là nhiều hơn thế nữa.
Hàm SUMPRODUCT trong Excel là hàm có rất nhiều tính năng hữu ích. Do khả năng đặc biệt có thể xử lý mảng một cách tinh tế và thông minh hơn, nên hàm này được dùng khá phổ biến khi so sánh dữ liệu ở hai hay nhiều dải ô và việc tính toán dữ liệu có nhiều điều kiện.
MỤC LỤC
Hàm SUMPRODUCT là hàm gì trong Excel?
Hàm SUMPRODUCT trong Excel là hàm được dùng để tính tích các phạm vi hoặc mảng với nhau và về kết quả là tổng số sản phẩm. Điều này nghe có vẻ khá bình thường, nhưng hàm này còn có những chức năng cực kỳ linh hoạt và có thể được sử dụng để đếm và tính tổng linh hoạt hơn. Vì vậy, đây là một trong những hàm được sử dụng khá phổ biến và cực kỳ hữu ích trong Excel.
Công thức của hàm SUMPRODUCT
Về phương diện kỹ thuật, hàm SUMPRODUCT sẽ nhân các số trong các số trong các mảng và trả về tổng của các sản phẩm đó.
Cú pháp của hàm PRODUCT trong Excel đơn giản như sau:
= SUMPRODUCT (array1, [array2], [array3],…)
Trong đó:
– array1: đây là mảng đầu tiên bắt buộc phải có nếu bạn muốn nhân các thành phần của nó rồi cộng tổng lại.
– [array2], [array3]…: đây là các phạm vi ô hoặc mảng liên tục tiếp theo nhau và có các phần tử mà bạn muốn nhân, sau đó cộng tổng lại.
– Số lượng mảng tối thiểu sẽ là 1. Trong trường hợp này, công thức hàm SUMPRODUCT chỉ đơn giản đó là cộng tất cả các phần tử của mảng và trả về tổng.
Có thể bạn quan tâm:
– Số lượng mảng tối đa sẽ là 255 đối với các phiên bản Excel 2016, Excel 2013, Excel 2010 và Excel 2017. Còn đối với các phiên bản trước đó thì tối đa là 30.
– Mặc dù hàm SUMPRODUCT hoạt động chung với các mảng, nhưng nó không yêu cầu sử dụng phím tắt mảng (Ctrl + Shift + Enter).
Xem thêm:
Hướng dẫn sử dụng hàm sumif 2 điều kiện để tính tổng nhiều điều kiện
Khóa học excel từ cơ bản đến nâng cao
Lưu ý:
+ Tất cả các mảng nằm nằm trong công thức của hàm SUMPRODUCT thì phải có cùng số lượng hàng và cột, nếu không, bạn sẽ nhận được lỗi #VALUE!
+ Nếu bất kỳ đối số nào của mảng chứa các giá trị nào không phải là số, thì chúng sẽ được coi là số 0.
+ Nếu một mảng là một bài kiểm tra logic, thì nó sẽ dẫn đến các giá trị là TRUE và FALSE. Trong hầu hết tất cả các trường hợp, bạn cần phải chuyển đổi chúng thành 1 và 0 bằng cách sử dụng toán tử đơn nguyên kép (-).
Tổng hợp sẽ không hỗ trợ các ký tự đại diện.
Ví dụ tham khảo về hàm SUMPRODUCT
Giả sử như trong một bài toán, bạn có các số lượng trong các ô A2:A4, giá nằm trong các ô B2:B4 và bạn muốn tìm hiểu tổng số. Nếu như để thực hiện bài toán này, bạn sẽ tính tích số lượng với giá cho mỗi mục, sau đó sẽ là các tổng phụ. Trong Microsoft Excel, bạn có thể nhận kết quả bằng một công thức hàm SUMPRODUCT duy nhất, đó là:
=SUMPRODUCT(A2:A4:B2:B4)
Cách sử dụng hàm SUMPRODUCT trong Excel
Có 3 trường hợp sử dụng hàm SUMPRODUCT như sau:
Trường hợp 1: Dùng hàm SUMPRODUCT trong Excel với một tiêu chí duy nhất
Ví dụ: Trong dữ liệu dưới đây có chứa 6 tên trong các hàng và 12 tháng trong các cột. Bạn hãy tưởng tượng sếp của bạn yêu cầu bạn cộng tất cả các số lại cho tháng Sáu. Hãy bắt đầu công thức của bạn bằng cách chọn phạm vi dữ liệu (không có tên và tháng) và bạn nhân nó với mảng có chứa các tháng bằng 6.
Các bước thực hiện như sau:
Bước 1: Bạn nhập công thức:SUMPRODUCT((B8:M12)*(B7:M7=7))
Trong đó:
– Mảng 1 của công thức hướng dẫn bạn cộng lại tất cả các số trong lựa chọn B8:M12.
– Array2 sẽ kiểm tra từng số tháng xem kết quả có bằng 7. Và kết quả nó sẽ trả về là TRUE (1) hoặc FALSE (0). Bạn sẽ thấy kết quả này nằm trong hàng 16. Chỉ có cột H kết quả là TRUE, vì vậy kết quả sẽ là 1.
– Để nhân array1 với array2 có nghĩa là nhân các số thực với 0 hoặc 1. Trong ví dụ này, bạn sẽ nhân tất cả các số trong cột C với 0 và tất cả các số từ cột H với 1. Tất cả các giá trị được nhân với 0 thì bạn cộng với 0. Các số duy nhất còn lại sẽ được nhân với 1, tưởng trường hợp trên là tháng 7.
– Việc dùng hàm SUMPRODUCT trong Excel sẽ giúp bạn thực hiện phép tính một cách nhanh chóng và cho ra kết quả nhanh, chính xác nhất.
Trường hợp 2: Dùng hàm SUMPRODUCT làm tiêu chí cho các cột
Cùng tiếp tục ví dụ trên bằng cách thêm các tiêu chí khác. Bây giờ yêu cầu sẽ là doanh số bán hàng trong tháng 2 là bao nhiêu, vì vậy từ tháng 4 trở lên và bao gồm cả tháng 6. Có nghĩa là mình có một phạm vi dữ liệu lớn hơn để thêm vào. Thì làm sao để bạn xử lý được nó?
Công thức sử dụng như sau:
=SUMPRODUCT((B8:M12)*(B7:M7>=7)*(B7:M7<=9))
Có 3 tháng sẽ thuộc về Q2. Lưu ý rằng bạn không cần phải đưa ra ba tuyên bố để chỉ định mỗi tháng. Thay vào đó, bạn có thể dùng một công trình trong đó các tháng lớn hơn hoặc bằng 7 (>=7) và nhỏ hơn hoặc bằng 9 (<=9).
Thay vì cứ phải mã hóa cứng các tháng để thêm, bạn có thể tham khảo các ô chỉ định các giá trị này. Và các ô thông thường được gọi là các tham số.
Để đạt được yêu cầu đó, bạn hãy thêm tháng giới hạn thấp hơn trong ô M3 và giới hạn cao hơn trong ô M4. Bây giờ bạn thay đổi công thức để phản ánh điều này. Thay vì tham khảo số 7, bạn hãy viết M3. Và thay số 9 bằng ô M4.
Bạn sẽ có được công thức cuối cùng là:
= SUMPRODUCT((B8:M12)*(B7:M7>=M3)*(B7:M7<=M4))
Trường hợp 3: Dùng hàm SUMPRODUCT trong Excel để làm tiêu chí cho cột và hàng
Trong những ví dụ trước đó cho thấy các tiêu chí đã xem xét các tháng. Bạn cũng có thể thực hiện một vài thao tác tương tự cho các hàng. Bây giờ, yêu cầu cho bạn đó là bạn hãy làm cho cột doanh thu của Lisa không được hiện ra. Cách làm như sau:
Như vậy, để tính một tiêu chí bổ sung trong các hàng, bạn hãy thêm một mảng khác vào trong công thức. Chỉ định rằng các tên đó phải bằng “Đặng Minh C”. Như chúng ta vừa tìm hiểu, thì bạn có thể muốn tham khảo một ô tham số hay vì bạn mã hóa nó. Vì vậy, bạn viết Lisa trong ô P3. Bây giờ bạn thêm một mảng chọn các ô A8:A12 bằng với ô tham số của bạn, trong trường hợp trên là P3. Cột O cho thấy hàng nào đáp ứng được điều kiện này, sẽ là hàng 10.
Công thức sử dụng sẽ như sau:
=SUMPRODUCT((B8:M12)*(B7:M7>=M3)*(B7:M7<=M4)*(A8:A12=P3))
Tổng số sẽ là 1629. Đây cũng chính là doanh số trong quý 2 từ Đặng Minh C.
Như vậy, Daotaotinhoc đã hướng dẫn cho bạn về hàm SUMPRODUCT trong Excel cũng như cách tính tổng có điều kiện trong Excel nhanh chóng và dễ hiểu. Hy vọng bài viết này sẽ có ích cho bạn trong quá trình làm việc. Chúc các bạn thành công!
Có thể bạn quan tâm: