Hàm SUMPRODUCT trong Excel là một hàm rất linh hoạt sẽ cho kết quả khác nhau tùy thuộc vào các đối số được nhập.
Chức năng SUMPRODUCT thường làm là nhân các phần tử của một hoặc nhiều mảng và sau đó thêm hoặc tổng hợp các sản phẩm lại với nhau.
Nhưng bằng cách điều chỉnh dạng của các đối số, SUMPRODUCT sẽ đếm số lượng ô trong một phạm vi nhất định chứa dữ liệu đáp ứng các tiêu chí cụ thể.
01 trên 04
SUMPRODUCT so với COUNTIF và COUNTIFS
Kể từ Excel 2007, chương trình cũng có COUNTIF và COUNTIFS chức năng cho phép bạn đếm các ô đáp ứng một hoặc nhiều tiêu chí đã đặt.
Tuy nhiên, đôi khi, SUMPRODUCT dễ làm việc hơn khi tìm thấy nhiều điều kiện liên quan đến cùng phạm vi như được hiển thị trong ví dụ nằm trong hình trên.
02 trên 04
Hàm SUMPRODUCT Cú pháp hàm và đối số đếm ô
Cú pháp của hàm liên quan đến cách bố trí của hàm và bao gồm tên, dấu ngoặc, dấu phẩy và các đối số của hàm .
Để có được chức năng đếm các ô thay vì thực hiện mục đích tiêu chuẩn của nó, cú pháp không chuẩn này phải được sử dụng với hàm SUMPRODUCT:
= SUMPRODUCT ([điều kiện1] * [điều kiện2])
Giải thích cách cú pháp này hoạt động như thế nào được trình bày bên dưới ví dụ sau.
Ví dụ: Đếm các ô đáp ứng nhiều điều kiện
Như được hiển thị trong ví dụ trong hình trên, SUMPRODUCT được sử dụng để tìm tổng số ô trong dải dữ liệu A2 đến B6 chứa dữ liệu giữa các giá trị 25 và 75.
03 trên 04
Nhập hàm SUMPRODUCT
Thông thường, cách tốt nhất để nhập các hàm vào Excel là sử dụng hộp thoại của chúng, giúp dễ dàng nhập các đối số cùng một lúc mà không phải nhập dấu ngoặc hoặc dấu phẩy hoạt động như dấu phân cách giữa các đối số.
Tuy nhiên, vì ví dụ này sử dụng một dạng bất thường của hàm SUMPRODUCT, nên không thể sử dụng cách tiếp cận hộp thoại. Thay vào đó, hàm phải được nhập vào ô bảng tính .
Trong hình trên, các bước sau được sử dụng để nhập SUMPRODUCT vào ô B7:
- Nhấp vào ô B7 trong trang tính - vị trí nơi kết quả hàm sẽ được hiển thị
- Nhập công thức sau vào ô E6 của trang tính:
= SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75))
- Câu trả lời 5 sẽ xuất hiện trong ô B7 vì chỉ có năm giá trị trong phạm vi - 40, 45, 50, 55 và 60 - nằm trong khoảng từ 25 đến 75
- Khi bạn bấm vào ô B7, công thức đã hoàn thành = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75)) xuất hiện trong thanh công thức phía trên trang tính
04/04
Phá vỡ chức năng SUMPRODUCT
Khi điều kiện được đặt cho các đối số, SUMPRODUCT sẽ đánh giá từng phần tử mảng dựa vào điều kiện và trả về một giá trị Boolean (TRUE hoặc FALSE).
Với mục đích tính toán, Excel gán giá trị 1 cho các phần tử mảng đó là TRUE và giá trị 0 cho các phần tử mảng là FALSE.
Những cái tương ứng và số không trong mỗi mảng được nhân với nhau:
- với 1 x 1 trả về giá trị 1
- với 1 x 0 trả về giá trị 0
Những cái này và số không sau đó được tổng kết bởi hàm để cho chúng ta một số lượng các giá trị đáp ứng cả hai điều kiện.
Hoặc, nghĩ về nó theo cách này ...
Một cách khác để nghĩ về những gì SUMPRODUCT đang làm là suy nghĩ về dấu nhân như một điều kiện AND .
Với điều này trong tâm trí, nó chỉ khi cả hai điều kiện được đáp ứng - số lớn hơn 25 VÀ ít hơn 75 - rằng một giá trị TRUE (mà là bằng một nhớ) được trả về.
Hàm sau đó cộng tất cả các giá trị thực để đến kết quả của 5.