Hàm SUMPRODUCT trong Excel – Cách dùng và ví dụ minh họa

Hàm SUMPRODUCT là sự kết hợp của hàm SUM (tính tổng) và hàm PRODUCT (tính tích). Nó giúp bạn nhân tích của từng cặp phần tử rồi cộng tổng chúng lại với nhau.

Vừa nghe qua có vẻ là một hàm rất hữu ích để sử dụng trong việc cần tính tổng doanh thu các mặt hàng tùy đơn giá. Nhưng hàm SUMPRODUCT còn có thể được sử dụng linh hoạt hơn thế.

1. Công dụng của hàm SUMPRODUCT

Một trường hợp đơn giản mà ta thường hay gặp trong đời sống. Khi đi mua sắm với những các loại mặt hàng cùng đơn giá và số lượng khác nhau.

Nếu muốn tính tổng số tiền phải chi trả, cơ bản ta sẽ làm theo 2 bước sau :
– Tính số tiền phải trả cho từng món hàng : lấy tích của Đơn giá với Số lượng .
– Tính hàng loạt số tiền phải trả cho tổng thể những món hàng : cộng tổng những tích vừa tính được .

Với phép tính tích của từng món hàng, ta có thể dùng hàm PRODUCT hoặc sử dụng phép tính Đơn giá*Số lượng.

Với phép tính tổng tiền các món hàng, ta có thể dùng hàm SUM.

Nhưng như các bạn thấy đấy, nếu sử dụng cách như trên ta phải đi qua hai bước tính toán. Nếu thực sự không cần thiết với phép tính trung gian lấy tích từng món hàng kia, bạn có thể tính trực tiếp tất cả số tiền phải chi trả bằng hàm SUMPROUDCT.

Công dụng của SUMPRODUCT là sự kết hợp giữa hai hàm tính tích PRODUCT và hàm tính tổng SUM.

2. Cách dùng hàm SUMPRODUCT

Hàm SUMPRODUCT có công thức như sau:

=SUMPRODUCT(array1, array2, array3,…)

Trong đó những array1, 2, 3 … là những mảng tham số với array1 chứa những thành phần tiên phong của phép tính nhân, array 2 chứa những thành phần thứ 2 của phép tính nhân, array 3 chứa những thành phần thứ 3 của phép tính nhân … v.v …
Phần tử đứng đầu của array1 sẽ nhân với thành phần đứng đầu của array2 và nhân với thành phần đứng đầu của array3, cứ lần lượt như vậy cho tới hết. Và sau cuối chúng sẽ được cộng tổng hết lại .

Chính vì điều này nên khi nhập dữ liệu mảng cho hàm SUMPRODUCT, bạn cần phải chú ý các mảng phải có kích cỡ bằng nhau (cùng số hàng và cùng số cột), nếu không hàm sẽ trả về kết quả lỗi: #VALUE!

Ngoài ra:

Hàm SUMPRODUCT sẽ coi những ô dữ liệu trống hoặc không chứa dữ liệu con số là 0 khi thực hiện phép tính.

3. Một số biến thể về cách dùng hàm SUMPRODUCT

3.1. Hàm SUMPRODUCT 1 điều kiện

Hàm SUMPRODUCT còn có thể sử dụng như một hàm đếm số theo điều kiện.

Với ví dụ sau đây :

Đây là bảng thống kê tiền góp vốn đầu tư cho những dự án Bất Động Sản của một công ty và số tiền mà công ty đó thu về được. Vấn đề được đặt ra ở đây là có bao nhiêu dự án Bất Động Sản đã kiếm được lời cho công ty ( lệch giá > vốn ), bao nhiêu dự án Bất Động Sản bị lỗ ( lệch giá < vốn ) và có có bao nhiêu dự án Bất Động Sản hòa vốn ( lệch giá = vốn ) .

Hàm SUMPRODUCT sẽ rất hân hạnh được giúp đỡ bạn trong bài toán này. Lúc này, công thức của hàm sẽ biến thành:

=SUMPRODUCT(–(<điều kiện tương tác>))

Trong đó, thay vì làm phép tính nhân giữa các phần tử tương ứng của mảng thì hàm SUMPRODUCT lúc này sẽ so sánh các phần tử đó với nhau với điều kiện tương tác. Nếu các phần tử đó đúng với điều kiện thì hàm SUMPRODUCT sẽ trả về số 1 còn sai thì sẽ trả về số 0. Sau đó, tất cả các số 1 đó sẽ được cộng lại với nhau để cho ra kết quả cuối cùng.

3.2. Hàm SUMPRODUCT nhiều điều kiện

Bạn cũng có thể sử dụng nhiều điều kiện cùng một lúc cho hàm SUMPRODUCT.

Cùng với ví dụ trên, với điều kiện kèm theo là tìm số dự án Bất Động Sản góp vốn đầu tư có lãi và vốn góp vốn đầu tư trên 10 tỷ. Ta có hai điều kiện kèm theo ở đây là Vốn góp vốn đầu tư < Doanh thu và Vốn > 10 .

Khác biệt giữa hai phương pháp trên đó là dấu phẩy nằm giữa hai vùng điều kiện kèm theo .
– Khi có dấu phẩy nghĩa là bạn đưa ra điều kiện kèm theo “ và ”, cần phải phân phối cả hai điều kiện kèm theo mới cho ra điều kiện kèm theo đúng .
– Bỏ dấu phẩy đi nghĩa là bạn đưa ra điều kiện kèm theo “ hoặc ”, chỉ cần phân phối một trong hai điều kiện kèm theo thì đã là điều kiện kèm theo đúng .

3.3. Hàm SUMPRODUCT để tính tổng các phần tử phù hợp điều kiện

Ngoài việc đếm số các phần tử trong mảng phù hợp với điều kiện, hàm SUMPRODUCT còn có thể giúp bạn cộng tổng các phần tử thuộc mảng mà phù hợp với điều kiện.

Để hiểu rõ hơn hãy theo dõi ví dụ dưới đây :

Tính tổng số vốn đổ vào những dự án Bất Động Sản lỗ vốn. Để làm được phép tính này, ta cần phải đưa ra điều kiện kèm theo Vốn > Doanh thu, sau đó tính tổng những dòng Vốn góp vốn đầu tư tương thích với điều kiện kèm theo Vốn > Doanh thu .
Công thức của bài toán này là :

=SUMPRODUCT(–(<điều kiện tương tác>),)

Trong đó, phần trước dấu phẩy thì giống với trong phần hàm SUMPRODUCT điều kiện 3.2 và 3.1. Nhưng khác với hai cách dùng ở phần 3.2 và 3.1, thay vì đếm số lượng các phần tử phù hợp với điều kiện thì hàm SUMPRODUCT sẽ cộng tổng các phần tử ở trong mảng chứa dữ liệu tính tổng nằm ở dòng đáp ứng đúng với điều kiện.

Sau khi đọc xong bài viết này, những bạn đã có thêm nhiều hiểu biết về hàm SUMPRODUCT chưa ? Hãy cùng comment ở dưới để bàn luận thêm nhé .

Theo dõi excel.net.vn để biết thêm nhiều bài viết bổ ích về Excel!

Source: http://wp.ftn61.com
Category: Thủ Thuật