01 trong 02
Tổng các ô nằm giữa hai giá trị
Lifewire
Hàm SUMPRODUCT trong Excel là một hàm rất linh hoạt sẽ cho các kết quả khác nhau tùy thuộc vào cách nhập các đối số của hàm.
Thông thường, như tên gọi của nó, SUMPRODUCT nhân các phần tử của một hoặc nhiều mảng để lấy sản phẩm của chúng và sau đó cộng hoặc tổng các sản phẩm lại với nhau.
Tuy nhiên, bằng cách điều chỉnh cú pháp của hàm, nó có thể được sử dụng để chỉ tính tổng dữ liệu trong các ô đáp ứng các tiêu chí cụ thể.
Kể từ Excel 2007, chương trình đã chứa hai hàm - SUMIF và SUMIFS - sẽ tính tổng dữ liệu trong 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 nhiều điều kiện liên quan đến cùng một phạm vi như được hiển thị trong hình trên.
Hàm SUMPRODUCT Cú pháp tính tổng các ô
Cú pháp được sử dụng để lấy SUMPRODUCT cho tổng dữ liệu trong các ô đáp ứng các điều kiện cụ thể là:
=SUMPRODUCT ([điều kiện1][điều kiện2][mảng])
condition1, condition2 - các điều kiện phải được đáp ứng trước khi hàm sẽ tìm thấy tích của mảng.
mảng - một dải ô liền kề
Ví dụ: Tổng hợp dữ liệu trong các ô đáp ứng nhiều điều kiện
Ví dụ trong hình trên thêm dữ liệu trong các ô trong phạm vi từ D1 đến E6 nằm trong khoảng từ 25 đến 75.
Nhập Hàm SUMPRODUCT
Vì ví dụ này sử dụng dạng bất thường của hàm SUMPRODUCT, hộp thoại của hàm không thể được sử dụng để nhập hàm và các đối số của nó. Thay vào đó, hàm phải được nhập theo cách thủ công vào một ô trang tính.
- Nhấp vào ô B7trong trang tính để đặt nó thành ô hoạt động;
- Nhập công thức sau vào ô B7: =SUMPRODUCT (($ A $ 2: $ B $ 6>25)($ A $ 2: $ B $ 6<75)(A2: B6))
- Câu trả lời 250sẽ xuất hiện trong ô B7
- Đã có câu trả lời bằng cách cộng năm số trong phạm vi (40, 45, 50, 55 và 60) từ 25 đến 75. Tổng số là 250
Phá vỡ Công thức SUMPRODUCT
Khi điều kiện được sử dụng cho các đối số của nó, hàm SUMPRODUCT sẽ đánh giá từng phần tử mảng so với điều kiện và trả về giá trị Boolean (TRUE hoặc FALSE).
Đối với mục đích tính toán, Excel chỉ định giá trị 1cho các phần tử mảng đó là TRUE (đáp ứng điều kiện) và giá trị 0cho các phần tử mảng FALSE (không thỏa mãn điều kiện).
Ví dụ: số 40:
- là TRUE cho điều kiện đầu tiên nên giá trị của 1được gán trong mảng đầu tiên;
- là TRUE cho điều kiện thứ hai nên giá trị của 1được gán trong mảng thứ hai.
Số 15:
- là FALSE cho điều kiện đầu tiên nên giá trị của 0được gán trong mảng đầu tiên;
- là TRUE cho điều kiện thứ hai nên giá trị của 1được gán trong mảng thứ hai.
Các số tương ứng và số không trong mỗi mảng được nhân với nhau:
- Đối với số 40 - chúng ta có 1 x 1 trả về giá trị là 1;
- Đối với số 15 - chúng ta có 0 x 1 trả về giá trị là 0.
Nhân các Ones và Zeros với Phạm vi
Những số này và số không sau đó được nhân với các số trong phạm vi A2: B6
Điều này được thực hiện để cung cấp cho chúng tôi các số sẽ được tính bằng hàm.
Điều này hoạt động vì:
- 1 lần bất kỳ số nào bằng số ban đầu
- 0 lần bất kỳ số nào bằng 0
Vì vậy, chúng tôi kết thúc với:
-
140=40
015=0
022=0
145=45
150=50
155=55
025=0
075=0
160=600100=0
Tổng hợp kết quả
SUMPRODUCT sau đó tổng hợp các kết quả trên để tìm câu trả lời.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250
