Hàm SUMPRODUCT là gì và cách dùng cụ thể thế nào?

Hàm SUMPRODUCT là gì, cách dùng cơ bản, cách dùng có điều kiện và cách dùng nâng cao.

Hàm SUMPRODUCT là gì?

Trong Excel. hàm SUMPRODUCT là một trong những hàm được sử dụng nhiều nhất. Trả lời một cách ngắn gọn cho câu hỏi hàm SUMPRODUCT là gì, thì hàm này có chức năng so sánh mảng, đếm số các ô có điều kiện, tính tổng có điều kiện hoặc tính trung bình cộng có trọng lượng. Đối với yêu cầu so sánh dữ liệu, hàm SUMPRODUCT hoàn toàn có thể so sánh các dữ liệu trong nhiều ô hoặc nhiều mảng khác nhau.

Khi tìm hiểu hàm SUMPRODUCT là gì, chúng ta chắc chắn không thể bỏ qua tìm hiểu về công thức của nó:

=SUMPRODUCT(array1, [array2], [array3], …)

Trong đó: array1, array2, … chính là các dải ô dữ liệu liên tục hoặc các mảng dữ liệu có chứa một thành phần mà bạn đang cần tính toán. 

Cách dùng hàm SUMPRODUCT cơ bản

Sau khi tìm hiểu hàm SUMPRODUCT là gì, hãy cùng nói chi tiết hơn về cách sử dụng của hàm này nhé. 

Về cơ bản hàm SUMPRODUCT được dùng để trả về kết quả sau khi đã nhân giá trị của các cột và cộng tổng chúng lại với nhau. Xét ví dụ đơn giản: bạn có dữ liệu ở các ô A2:A4, yêu cầu đưa ra là cần tính tổng. Khi đó, hãy sử dụng hàm SUMPRODUCT để thực hiện yêu cầu này với công thức như sau:

=SUMPRODUCT(A2:A4,B2:B4).

Giải thích rõ hơn thì công thức này thực hiện phép tính =A2*B2 + A3*B3 + A4*B4. Rất đơn giản đúng không!

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

Chúng ta đã tìm hiểu hàm SUMPRODUCT là gì và cách dùng hàm SUMPRODUCT cơ bản nhất. Vậy với cách dùng có điều kiện thì sao? Đây là câu trả lời.

Cách dùng hàm SUMPRODUCT 1 điều kiện

Cách dùng này khá phổ biến với dân văn phòng vì có thể so sánh các con số nằm trong hai cột theo kiểu hàng theo hàng, đồng thời như trong ví dụ này cho chúng ta biết số lần cột C ít hơn cột B. Công thức cụ thể như sau:

=SUMPRODUCT(–(C2:C10<B2:B10)).

Như vậy giá trị hàm được thể hiện sẽ là BOOLEAN TRUE và FALSE. Trong đó, giá trị TRUE có nghĩa là đã đáp ứng được điều kiện xác định (ví dụ, một giá trị ở cột B ít hơn một giá trị ở cột C trong cùng một hàng), và giá trị FALSE sẽ cho thấy điều ngược lại.

Cách dùng hàm SUMPRODUCT nhiều điều kiện

Xét ví dụ bạn có một danh sách gồm các món hàng nằm ở cột A, doanh số bán hàng dự kiến nằm ở cột B và cột C là doanh số bán hàng thực tế đạt được. Yêu cầu ở đây là tìm xem có tất cả bao nhiêu món hàng có doanh số bán thực tế thấp hơn dự kiến.

Đối với yêu cầu này, chúng ta cần sử dụng một trong số những biến phân của công thức hàm SUMPRODUCT, cụ thể là:

=SUMPRODUCT(–(C2:C10<B2:B10))

Để hiểu rõ về cách sử dụng hàm SUMPRODUCT nhiều điều kiện, bạn hãy nghiền ngẫm thật kỹ ví dụ này nhé.

Cách dùng hàm SUMPRODUCT nâng cao

Lồng ghép hàm khác trong hàm SUMPRODUCT để thay đổi giá trị của mảng cần tính

Rất ít người dùng biết rằng hàm SUMPRODUCT cho phép được lồng ghép với nhiều hàm khác trong Excel nhằm mục đích tổng hợp được nhiều tính năng.

Để minh họa rõ hơn về cách sử dụng hàm SUMPRODUCT này, dưới đây chúng ta hãy cùng xét ví dụ tính tổng tiền của những mặt hàng đã được bán trong tháng 3. Tuy nhiên trở ngại ở đây là dữ liệu chỉ có cột Ngày bán, không có riêng cột Tháng. Như vậy để thực hiện yêu cầu, bạn cần tách được Tháng từ cột Ngày bán là được.

Dùng hàm SUMPRODUCT xét 3 mảng:

• Mảng Ngày bán: B2:B5 chúng ta sẽ kết hợp hàm MONTH xử lý mảng này để nhận được kết quả là giá trị số tháng, từ đó so sánh với số 3.
• Mảng Số lượng: C2:C5
• Mảng Đơn giá: D2:D5

Cuối cùng, chúng ta chỉ cần nhân 3 mảng này lại với nhau là sẽ thu được kết quả như trong hình dưới đây.

Hàm SUMPRODUCT tính toán cho bảng dữ liệu 2 chiều

Để bạn dễ hình dung hơn khi nói về bảng dữ liệu 2 chiều, hãy cùng xét ví dụ về bảng mã công ty và mã hàng. Yêu cầu đặt ra ở đây là tìm đơn giá của các mặt hàng. Khi đó, công thức cần sử dụng sẽ là:

G3=SUMPRODUCT(($B$14:$B$17=C3)*($C$13:$F$13=B3)*$C$14:$F$17).

Giải thích rõ hơn, trong trường hợp này hàm SUMPRODUCT sẽ trả lời cho câu hỏi: Mã công ty trong ô C2 có thuộc trong mảng Mã công ty (B14:B17) hay không?

Mã công ty tại ô B2 có thuộc trong mảng Mã công ty (C13:F13) hay không?

Như vậy nếu kết quả là có xuất hiện cả ở cả 2 mảng trên, bạn sẽ lấy giá trị đơn giá nằm trong mảng C14:C17 theo dòng và theo cột là hoàn tất yêu cầu.

Trong bài viết trên, chúng ta đã cùng nhau tìm hiểu hàm SUMPRODUCT là gì và toàn bộ các cách sử dụng hàm này từ cơ bản đến nâng cao. Để có thể làm chủ được nó, bạn hãy bỏ ra một chút thời gian để luyện tập. Cuối cùng, đừng quên bấm theo dõi trang ngay để liên tục cập nhật thêm các thủ thuật tin học văn phòng thú vị khác bạn nhé!