Tóm lược
Hàm SUMPRODUCT nhân các dải hoặc mảng với nhau và trả về tổng các sản phẩm. Điều này nghe có vẻ nhàm chán, nhưng SUMPRODUCT là một hàm cực kỳ linh hoạt có thể được sử dụng để đếm và tính tổng như COUNTIFS hoặc SUMIFS, nhưng linh hoạt hơn. Các chức năng khác có thể dễ dàng được sử dụng bên trong SUMPRODUCT để mở rộng chức năng hơn nữa.
Mục đích
Nhân, sau đó tính tổng mảngGiá trị trả lại
Kết quả của mảng nhân và mảng tổngCú pháp
= SUMPRODUCT (array1, (array2),…)Tranh luận
- array1 - Mảng hoặc dải ô đầu tiên cần nhân, sau đó cộng.
- array2 - (tùy chọn) Mảng hoặc dải ô thứ hai để nhân, sau đó cộng.
Phiên bản
Excel 2003Ghi chú sử dụng
Hàm SUMPRODUCT hoạt động với mảng, nhưng nó không yêu cầu cú pháp mảng bình thường (Ctrl + Shift + Enter) để nhập. Mục đích của hàm SUMPRODUCT là nhân, sau đó tính tổng, các mảng. Nếu chỉ cung cấp một mảng, SUMPRODUCT sẽ chỉ đơn giản là tính tổng các mục trong mảng. Có thể cung cấp tới 30 mảng.
Khi bạn lần đầu tiên gặp SUMPRODUCT, nó có vẻ nhàm chán, phức tạp và thậm chí là vô nghĩa. Nhưng SUMPRODUCT là một chức năng linh hoạt đáng kinh ngạc với nhiều công dụng. Bởi vì nó sẽ xử lý các mảng một cách duyên dáng và không có khiếu nại, bạn có thể sử dụng nó để xử lý các dải ô theo những cách thông minh, trang nhã (xem liên kết đến các ví dụ về công thức trên trang này).
Để minh họa cách SUMPRODUCT hoạt động, đây là một vài ví dụ phổ biến.
SUMPRODUCT cho tổng và số có điều kiện
Giả sử bạn có một số dữ liệu đơn hàng trong A2: B6, với Trạng thái trong cột A, Doanh số trong cột B:
| A | B | |
| 1 | Tiểu bang | Bán hàng |
| 2 | UT | 75 |
| 3 | CO | 100 |
| 4 | TX | 125 |
| 5 | CO | 125 |
| 6 | TX | 150 |
Sử dụng SUMPRODUCT, bạn có thể tính tổng doanh số bán hàng cho Texas ("TX") với công thức sau:
=SUMPRODUCT(--(A2:A6="TX"))
Và bạn có thể tính tổng doanh số cho Texas ("TX") bằng công thức sau:
=SUMPRODUCT(--(A2:A6="TX"),B2:B6)
Lưu ý: Đừng nhầm lẫn bởi âm đôi. Đây là một thủ thuật phổ biến được sử dụng trong các công thức Excel nâng cao hơn để buộc các giá trị TRUE và FALSE thành 1 và 0. Xem thêm bên dưới…
Đối với ví dụ tổng hợp ở trên, đây là một biểu diễn ảo của hai mảng được xử lý lần đầu bởi SUMPRODUCT:
| array1 | array2 |
| SAI | 75 |
| SAI | 100 |
| THẬT | 125 |
| SAI | 125 |
| THẬT | 150 |
Mỗi mảng có 5 mục. Mảng đầu tiên chứa các giá trị TRUE / FALSE là kết quả của biểu thức A2: A6 = "TX" và mảng thứ hai là nội dung của B2: B6. Mỗi mục trong mảng đầu tiên sẽ được nhân với mục tương ứng trong mảng thứ hai. Tuy nhiên, ở trạng thái hiện tại, kết quả của SUMPRODUCT sẽ bằng 0 vì các giá trị TRUE và FALSE sẽ được coi là 0. Chúng ta cần các mục trong array1 là số - chúng cần được "ép buộc" thành các giá trị đơn và số không. Đây là nơi mà âm kép xuất hiện.
Bằng cách sử dụng phủ định kép - (đơn phân kép, dành cho bạn các loại kỹ thuật), chúng tôi có thể buộc TRUE / FALSE thành các giá trị số một và không, như được hiển thị trong biểu diễn ảo bên dưới. Cột cuối cùng "Sản phẩm" đại diện cho kết quả của việc nhân hai mảng với nhau. Kết quả tổng hợp, 275, là giá trị mà hàm SUMPRODUCT trả về.
| array1 | array2 | Sản phẩm | ||
| 0 | * | 75 | = | 0 |
| 0 | * | 100 | = | 0 |
| 1 | * | 125 | = | 125 |
| 0 | * | 125 | = | 0 |
| 1 | * | 150 | = | 150 |
| Tổng | 275 | |||
Sử dụng cú pháp ngoặc nhọn cho mảng, ví dụ sẽ giống như thế này sau khi ép buộc:
=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))
và như thế này sau khi nhân:
=SUMPRODUCT((0,0,125,0,150))
Ví dụ này mở rộng các ý tưởng ở trên với nhiều chi tiết hơn.
SUMPRODUCT với các chức năng khác
SUMPRODUCT có thể sử dụng trực tiếp các chức năng khác. Bạn có thể thấy hàm SUMPRODUCT được sử dụng với hàm LEN để đếm tổng số ký tự trong một dải ô hoặc với các hàm như ISBLANK, ISTEXT, v.v. Đây không phải là các hàm mảng thông thường, nhưng khi chúng được cung cấp một dải ô, chúng sẽ tạo ra một "mảng kết quả". Vì SUMPRODUCT được xây dựng để làm việc với các mảng nên nó có thể thực hiện các phép tính trên các mảng một cách trực tiếp. Đây có thể là một cách hay để tiết kiệm dung lượng trong trang tính, bằng cách loại bỏ sự cần thiết của cột "trợ giúp".
Ví dụ: giả sử bạn có 10 giá trị văn bản khác nhau trong A1: A10 và bạn muốn đếm tổng số ký tự cho tất cả 10 giá trị. Bạn có thể thêm một cột trợ giúp trong cột B sử dụng công thức này: LEN (A1) để tính các ký tự trong mỗi ô. Sau đó, bạn có thể sử dụng SUM để cộng tất cả 10 số. Tuy nhiên, bằng cách sử dụng SUMPRODUCT, bạn có thể viết một công thức như sau:
=SUMPRODUCT(LEN(A1:A10))
Khi được sử dụng với một phạm vi như A1: A10, LEN sẽ trả về một mảng gồm 10 giá trị. Khi đó hàm SUMPRODUCT sẽ chỉ đơn giản là tính tổng tất cả các giá trị và trả về kết quả mà không cần cột trợ giúp.
Xem ví dụ bên dưới nhiều cách khác để sử dụng SUMPRODUCT.
Ghi chú:
- SUMPRODUCT xử lý các mục không phải số trong mảng là số không.
- Đối số mảng phải có cùng kích thước. Nếu không, SUMPRODUCT sẽ tạo ra lỗi #VALUE! giá trị lỗi.
- Kiểm tra logic bên trong mảng sẽ tạo ra các giá trị TRUE và FALSE. Trong hầu hết các trường hợp, bạn sẽ muốn ép buộc chúng thành số 1 và số 0.
- Hàm SUMPRODUCT thường có thể sử dụng trực tiếp kết quả của các hàm khác (xem ví dụ công thức bên dưới)
