Hàm SUMPRODUCT trong Excel
Hàm SUMPRODUCT trong Excel

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ảng

Giá trị trả lại
Kết quả của mảng nhân và mảng tổng

Cú 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 – [optional] Mảng hoặc dải ô thứ hai cần nhân, sau đó cộng.
Phiên bản
Ghi 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 và 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ể đếm tổng doanh số cho Texas (“TX”) với công thức sau:

=SUMPRODUCT(--(A2:A6="TX"))

Và bạn có thể Tổng tổng doanh số cho Texas (“TX”) với công thức sau:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Lưu ý: Đừng nhầm lẫn bởi phủ định kép. Đâ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 âm kép – (kép một ngôi, 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, 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ụ trông giống như 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 toán 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ị. Sau đó 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ú:

  1. SUMPRODUCT xử lý các mục không phải số trong mảng là số không.
  2. Các đố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.
  3. 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.
  4. 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)

https://exceljet.net/excel-functions/excel-sumproduct-function