Mọi thứ bạn cần biết về hàm SUMPRODUCT trong Excel và các ứng dụng khi kết hợp có nhiều điều kiện nâng cao với các ví dụ và giải thích.
Hàm SUMPRODUCT được sử dụng để nhân các giá trị ô, sau đó tính tổng các tích kết quả của chúng. Nó là một trong những hàm toán học nâng cao và hữu ích nhất trong Excel. Nó được sử dụng để nhân các số trong các mảng hoặc phạm vi tương ứng và trả về tổng của các kết quả tích đó.
Hàm SUMPRODUCT là một hàm rất linh hoạt và nó có thể được sử dụng cho nhiều mục đích như đếm ô, tính tổng các giá trị dựa trên nhiều điều kiện, tìm kiếm giá trị, so sánh dữ liệu giữa hai phạm vi và hơn thế nữa. Nó cũng thường được sử dụng để tính giá trị trung bình có trọng số. Bên cạnh phép cộng và phép trừ, nó cũng có thể được sửa đổi để thực hiện phép trừ và phép chia.
Trong bài đăng này, chúng ta sẽ thảo luận về hàm SUMPRODUCT là gì, các cách sử dụng cơ bản và nâng cao khi có nhiều điều kiện trong Excel với các ví dụ.
Xem nhanh
Hàm SUMPRODUCT là gì?
Hàm SUMPRODUCT được thiết kế để nhân các mảng hoặc bộ kiểm tra tương ứng và sau đó tổng hợp các kết quả.
Cú pháp
=SUMPRODUCT(array1, [array2], [array3],...)Giải thích:
- array1 (bắt buộc) – Đây là mảng hoặc dải ô đầu tiên có giá trị bạn muốn nhân và sau đó cộng.
- [array2], [array3],… (tùy chọn) – Đây là mảng hoặc dải ô thứ hai (và thứ ba) có giá trị bạn muốn nhân và sau đó cộng. Hàm chiếm 30 mảng trong các phiên bản Excel cũ nhưng trong các phiên bản hiện đại, nó có thể chấp nhận tối đa 255 mảng.
Mảng hoặc phạm vi ô phải có cùng số cột hoặc số hàng, nếu không, bạn sẽ nhận được lỗi #VALUE! error. Nếu có bất kỳ giá trị mảng không phải là số nào trong mảng, chúng sẽ được coi là 0.
Sử dụng cơ bản của hàm SUMPRODUCT
Để biết rõ hơn về hàm SUMPRODUCT, trước tiên, chúng ta hãy xem cách hoạt động của hàm với một số ví dụ cơ bản.
Ví dụ 1:
Ví dụ: giả sử bạn có bảng dưới đây chứa danh sách các sản phẩm, số lượng và giá cả của chúng.
Bây giờ, bạn muốn tìm tổng chi phí của tất cả các sản phẩm. Để làm điều đó, bạn có thể sử dụng công thức dưới đây:
=SUMPRODUCT(B2:B6,C2:C6)Công thức trên nhân từng ô từ B2: B6 với các ô tương ứng C2: C6 và tính tổng kết quả phép nhân.
Đây là hoạt động của hàm SUMPRODUCT trông giống như bên trong công thức:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)
=(25*8)+(5*9)+(33*12)+(14*15)+(51*5)
=200+45+396+210+255=1106Ví dụ 2:
Trong công thức trên, cả mảng 1 và mảng 2 đều có các cột đơn. Vì vậy, nó nhân hai cột số đơn lẻ và tổng hợp kết quả của chúng. Nhưng nếu mảng của bạn bao gồm nhiều cột (ví dụ B2: C6), bạn có thể viết công thức như sau:
=SUMPRODUCT(B2:C6,D2:E6)Công thức nhân từng giá trị của A2: B6 với giá trị tương ứng của B2: C6 và sau đó tổng hợp tất cả các giá trị của sản phẩm:
=(B2*D2)+(B3*D3)+(B4*D4)+(B5*D5)+(B6*D6)+(C2*E2)+(C3*E3)+(C4*E4)+(C5*E5)+(C6*E6)
=(25*8)+(5*9)+(33*12)+(14*15)+(51*5)+(17*9)+(7*7)+(53*15)+(24*28)+(82*6)
=200+45+396+210+255+153+49+795+672+492
=3267
Ví dụ 3:
Bất kể bạn chỉ định bao nhiêu mảng trong hàm SUMPRODUCT, tất cả chúng phải có cùng kích thước, nếu không, bạn sẽ gặp lỗi #VALUE. Trong công thức dưới đây, các mảng B2: B6 và C2: C7 không khớp, do đó, lỗi #VALUE.
=SUMPRODUCT(B2:B6,C2:C7)
Thực hiện các phép toán số học khác bằng SUMPRODUCT
SUMPRODUCT cũng có thể được sử dụng để thực hiện các phép toán số học khác như chia, cộng hoặc trừ mảng. Để làm điều này, tất cả những gì bạn phải làm là tách từng mảng bằng các toán tử toán học thích hợp (+, -, /) thay vì dấu phẩy (,) mặc định. Sau đó, hàm sẽ thực hiện các phép toán do người dùng chỉ định và tổng hợp kết quả. Bằng cách sử dụng các toán tử toán học, bạn cũng có thể tùy chỉnh các hoạt động giữa các mảng. Hãy để chúng tôi xem cách thực hiện việc này với một số ví dụ:
SUMPRODUCT cho phép Cộng :
Để cộng hai mảng số (thay vì nhân) chúng rồi cộng lại kết quả, bạn cần sử dụng công thức dưới đây:
=SUMPRODUCT(A1:A5+B2:B5)Tại đây, hàm sẽ thêm từng giá trị với các giá trị ô tương ứng và thêm vào kết quả.
SUMPRODUCT cho phép Trừ :
Trong trường hợp bạn muốn thực hiện các phép tính trừ thay vì phép nhân, hãy viết một công thức tương tự như sau:
=SUMPRODUCT(A1:A5-B1:B5)Ở đây, công thức trừ từng giá trị từ A1: A5 từ B1: B5 tương ứng và tính tổng các giá trị kết quả.
GIỚI THIỆU cho Phép chia:
Nếu bạn muốn sử dụng SUMPRODUCT để chia, hãy sử dụng công thức dưới đây:
=SUMPRODUCT(A1:A5/B1:B5)Công thức trên chia giá trị trong A1 cho giá trị trong B1, giá trị trong A2 cho B2, sau đó là A3 cho B3 và cuối cùng cộng tất cả các kết quả để cung cấp kết quả: 17,563
Với Hàm SUMPRODCUT, bạn cũng có thể tùy chỉnh các phép toán số học giữa các phần tử mảng (đối số) bằng cách sử dụng các toán tử khác nhau.
=SUMPRODUCT((A1:A5+B1:B5)/B1:B5)Ở đây, công thức sẽ thêm các phạm vi A1: A5 và B1: B5 trước vì nó được đặt trong dấu ngoặc đơn và các giá trị kết quả được chia cho B1: B5, tạo ra kết quả: 22,56.
SUMPRODUCT với một điều kiện
Sumproduct có thể được sử dụng để tính tổng hoặc đếm các giá trị đáp ứng một điều kiện hoặc tiêu chí cụ thể như SUMIF và COUNTIF nhưng linh hoạt hơn. Trước tiên, hãy để chúng tôi xem bạn có thể làm điều đó như thế nào với một điều kiện duy nhất.
Cú pháp
=SUMPRODUCT(--(criteria_range=criteria),sum_range)Ở đâu
- sum_range – Dải ô hoặc mảng mà bạn muốn tính tổng các giá trị nếu điều kiện được đáp ứng.
- criteria – Đây là điều kiện cần được đáp ứng để tính tổng các giá trị.
- criteria_range – Mảng hoặc dải ô mà điều kiện được kiểm tra.
Để kiểm tra một điều kiện, chúng ta phải sử dụng một phép toán logic chỉ cho kết quả là các giá trị Boolean (TRUE hoặc FALSE). Nhưng nhân TRUE hoặc FALSE với sum_range sẽ chỉ cho kết quả là 0. Vì vậy, để chuyển đổi các giá trị boolean đó thành các giá trị 1s (TRUE) và 0s (FALSE) có thể sử dụng được, chúng ta cần đặt điều kiện trong dấu ngoặc và thêm toán tử phủ định kép (-) trước đó.
Ví dụ 1:
Ví dụ: bạn có một tập dữ liệu như được hiển thị bên dưới, nơi bạn muốn tìm hiểu tổng doanh số bán hàng ‘Cam’.
Để tính tổng doanh số bán hàng của Cam, hãy sử dụng công thức dưới đây:
=SUMPRODUCT(--(A2:A9="Oranges"),C2:C9)Trong công thức trên, A2:A9="Oranges" trả về một mảng giá trị lôgic: {FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE}. Công thức chỉ trả về TRUE cho các ô chứa giá trị “Oranges” trong phạm vi A2: A9 và đối với các ô chứa các giá trị khác, nó trả về FALSE. Và phép phủ định kép (phép phủ định kép) chuyển đổi các giá trị này thành một mảng 1 và 0: {0, 1, 0, 0, 0, 1, 0, 1}.
Đây là công thức sẽ trông như thế nào sau đó:
=SUMPRODUCT({0, 1, 0, 0, 0, 1, 0, 1}, {5620, 854, 623, 452, 56, 56, 85, 100})
=(0, 1, 0, 0, 0, 1, 0, 1) x (5620, 854, 623, 452, 56, 56, 85, 100)
=854+56+100
=1010Sau đó, SUMPRODUCT sẽ nhân từng giá trị trong mảng1 với giá trị tương ứng trong mảng 2, rồi cộng vào mảng kết quả: 1010.
Ví dụ 2:
Bạn cũng có thể sử dụng công thức SUMPRODUCT để tìm tổng của các giá trị đáp ứng một điều kiện cụ thể. Ví dụ, trong ví dụ dưới đây, chúng tôi muốn nhân Sumproduct cho hai mảng nhưng chỉ cho sản phẩm có tên ‘Oranges’.
Để làm điều đó, hãy sử dụng công thức dưới đây:
=SUMPRODUCT((B2:B9*C2:C9)*(A2:A9="Oranges"))Tại đây, công thức A2:A9="Oranges"tìm kiếm tên sản phẩm ‘Oranges’ trong phạm vi A2: A9 và trả về mảng – {FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE}. Sau đó, công thức nhân hai mảng B2: B9 và C2: C9 và trả về một mảng kết quả khác.
Không cần sử dụng phủ định kép (-) trước đối số điều kiện vì toán tử nhân giữa hai đối số cũng hoạt động như một adapter và buộc các giá trị TRUE và FALSE từ (A2: A9 = ”Oranges”) thành 1s và 0s . Do đó, chỉ những giá trị có ‘Oranges’ trong phạm vi sẽ có kết quả là 1. Vì vậy, công thức sẽ tải thành như thế này =SUMPRODUCT({280, 360, 30, 252, 225, 90, 288, 160}*{0, 1, 0, 0, 0, 1, 0, 1}):. Do đó, chúng tôi lấy ‘610’ làm đầu ra.
SUMPRODUCT với nhiều điều kiện
SUMPRODUCT là một giải pháp thay thế tốt cho cả COUNTIFS và SUMIFS để đếm và tính tổng các giá trị dựa trên nhiều điều kiện. Không giống như COUNTIFS và SUMIFS, nó có thể hoạt động với cả công thức logic AND và OR.
Tổng số ô dựa trên nhiều điều kiện với AND logic
Đối với công thức logic AND, tất cả các điều kiện hoặc tiêu chí phải được đáp ứng trên một hàng để tính tổng các giá trị tương ứng trong mảng được chỉ định.
Cú pháp
=SUMPRODUCT(--(criteria_range1=criteria1),--(criteria_range2=criteria2), sum_range)Để rút ngắn công thức, bạn cũng có thể thay thế dấu phẩy giữa các phần tử của mảng bằng dấu sao của toán tử AND (*).
Ví dụ 1:
Giả sử, chúng ta có tập dữ liệu dưới đây và chúng ta muốn tính tổng các giá trị Bán hàng của quả “Peach” cho vùng “East”.
Để tính tổng doanh số bán quả ‘Peach’ cho vùng ‘East’, hãy viết công thức dưới đây:
=SUMPRODUCT(--(A2:A15="Peach"),--(B2:B15="East"),D2:D15)hoặc
=SUMPRODUCT(--(A2:A15=G2),--(B2:B15=G3),D2:D15)Cả hai công thức trên đều cho bạn cùng một kết quả. Nhưng trong công thức thứ hai, chúng tôi đã tham chiếu đến các ô chứa giá trị tiêu chí để rút ngắn công thức.
Trong công thức trên, Peach và East là hai tiêu chí xác định.
A2:A15=G2kiểm tra phạm vi A2: A15 để tìm giá trị bằng với giá trị được nhập trong ô G2 (Peach) và trả về một mảng đúng và sai, sau đó được chuyển đổi bằng kép âm (-) thành giá trị này – {0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 1, 0, 1, 0}. Ở đây, 1 là viết tắt của Peach và 0 cho các loại trái cây khác.- Sau đó,
B2:B15=G3tìm kiếm giá trị đã nhập trong ô G3 (East) và trả về mảng kết quả này – {1, 0, 1, 0, 0, 0, 0, 1, 1, 1, 0, 0, 0, 0}. Ở đây, 1 là viết tắt của ‘East’ và 0 cho bất kỳ vùng nào khác. - Sau đó, cả hai mảng kết quả được nhân và xuất ra mảng này – {0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0}. Điều này có nghĩa là chúng tôi chỉ nhận được ‘1’ nếu cả hai điều kiện được đáp ứng, 0 nếu không. Vì bạn chỉ có thể có hàng 3 và 10 có ‘1’ trong mảng kết quả vì chúng là hai hàng duy nhất đáp ứng các tiêu chí đã chỉ định.
- Bây giờ, mảng kết quả cuối cùng được nhân với giá trị bán hàng tương ứng trong D2: D15. Khi nhân 1 với một số sẽ cho cùng một số, nhân với 0 luôn cho ta số không. Kết quả là, chỉ có giá trị bán hàng trong hàng 3 và 10 được cộng lại với nhau để cho chúng ta kết quả: 425.
Ngoài ra, bạn có thể thực hiện tính toán tương tự ở trên bằng cách sử dụng công thức dưới đây ngắn hơn một chút so với công thức trên:
=SUMPRODUCT((A2:A15=G2)*(B2:B15=G3)*D2:D15)Trong công thức trên, Dấu hoa thị (*) được sử dụng để nhân hai điều kiện và dấu hoa thị cũng được coi là toán tử AND trong SUMPRODUCT. Nếu bạn thêm các ký tự dấu hoa thị giữa các mảng, bạn không cần thêm phủ định kép (-) trước điều kiện logic.
Ví dụ 2:
Ví dụ: tìm tổng doanh số bán quả Peach ở khu vực phía East bằng cách nhân số lượng và giá cả rồi tính tổng kết quả. Để làm điều đó, bạn có thể sử dụng công thức dưới đây
=SUMPRODUCT((A2:A15=G2)*(B2:B15=G3)*(C2:C15*D2:D15))Công thức này kiểm tra các điều kiện (Peach và East) trong cột A và cột B. Và nếu cả hai giá trị được tìm thấy trong cùng một hàng (nếu cả hai điều kiện được đáp ứng), công thức sẽ nhân và cộng các giá trị tương ứng trong cột C và D.
Tính tổng các ô dựa trên nhiều điều kiện với OR Logic
Không giống như COUNTIFS và SUMIFS, SUMPRODUCT cho phép bạn tính tổng hoặc đếm các giá trị có điều kiện với logic HOẶC. Đối với công thức logic OR, một trong hai tiêu chí phải được đáp ứng trên một hàng để tính tổng các giá trị tương ứng trong mảng đã chỉ định. Để áp dụng logic HOẶC, bạn cần sử dụng biểu tượng dấu cộng (+) ở giữa các mảng thay vì dấu hoa thị.
Dấu cộng (+) hoạt động như một toán tử OR và trả về TRUE nếu bất kỳ tiêu chí nào trong công thức được đánh giá là TRUE.
Cú pháp :
=SUMPRODUCT((criteria_range1=criteria1)+(criteria_range2=criteria2), sum_range)Ví dụ:
Nếu bạn muốn tìm tổng doanh số của Peach và / hoặc Apples bất kể khu vực nào, hãy viết công thức như sau:
=SUMPRODUCT((A2:A15=G2)+(A2:A15=I3),C2:C15*D2:D15)Công thức trên tìm kiếm các sản phẩm ‘Apples’ (I2) và ‘Peach’ (G2) trong cột A, sau đó nhân các giá trị tương ứng trong cột C (Số lượng) và D (Giá) và cộng kết quả nếu một trong hai điều kiện được đáp ứng . Điều đó có nghĩa là nó sẽ nhân hai cột C và D và thêm giá trị cho tất cả các sản phẩm của Apples và Peach bất kể khu vực.
Tổng các ô dựa trên nhiều điều kiện với AND cũng như OR Logic
Đôi khi, bạn có thể cần tính tổng có điều kiện các ô có cả logic OR và AND cùng một lúc, điều này bạn có thể dễ dàng thực hiện với hàm SUMPRODUCT.
Ví dụ 1:
Ví dụ: nếu bạn muốn tìm tổng doanh số bán Apples và Peach ở khu vực phía East, thì sự kết hợp của logic AND và OR sẽ trông như thế nào (cú pháp):
=Sum If ((Product="Apples") OR (Product="Peach")) AND (Region="East"))Bây giờ, hãy áp dụng logic trên vào công thức thực tế bằng cách thay thế ‘AND’ bằng (*) và OR bằng (+).
Để tính tổng doanh số bán hàng ‘Peach’ và ‘Apples’ ở khu vực miền East, hãy sử dụng công thức dưới đây:
=SUMPRODUCT(((A2:A15=G2)+(A2:A15=I2))*(B2:B15=G3),D2:D15)Hãy phân tích công thức:
- Trong công thức, chúng ta có ba điều kiện:
(A2:A15=G2)cho ‘Peach’ trong cột A,(A2:A15=I2)cho ‘Apples’ trong cột A và (B2: B15 = G3) cho ‘East’ trong cột B. Để tính tổng các giá trị tương ứng trong cột D, một trong hai tiêu chí đầu tiên phải được thỏa mãn (HOẶC) và tiêu chí thứ ba phải được thỏa mãn (VÀ).
((A2:A15=G2)+(A2:A15=I2))một phần của công thức kiểm tra mục ‘Peach’ hoặc ‘Apples’ trong Cột A. Nếu một trong hai mục được tìm thấy trong danh sách A2: A15, nó sẽ cho kết quả là TRUE (nếu không thì là FALSE) có nghĩa là “1”.(B2:B15=G3)kiểm tra giá trị ‘East’ trong cột B và nếu nó được tìm thấy, nó sẽ đánh giá là TRUE, giá trị này cũng được chuyển đổi thành ‘1’.- Khi cả hai mảng kết quả được nhân từ logic OR và AND, chúng ta sẽ nhận được mảng này – {1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0}.
- Cuối cùng, công thức nhân mảng kết quả cuối cùng với các giá trị của cột D và sau đó tổng kết quả thành – 880.
Ví dụ 2:
Công thức trên tìm tổng các giá trị dựa trên nhiều tiêu chí với logic AND và OR. Nhưng nếu bạn muốn tìm Tổng-Tích của các giá trị, bạn cần sử dụng công thức dưới đây:
=SUMPRODUCT(((A2:A15=G2)+(A2:A15=I2))*(B2:B15=G3),C2:C15*D2:D15)Công thức này gần giống nhau ngoại trừ chúng ta đã thêm một mảng khác vào công thức. Bây giờ, nếu ‘Apples’ hoặc ‘Peach’ và giá trị ‘East’ được tìm thấy trong cùng một hàng, các giá trị tương ứng trong cột C và D sẽ được nhân và cộng lại để tạo ra kết quả: 637.
Tính tổng các ô bằng hàm SUMPRODUCT và hàm chính xác
Trong trường hợp bạn cần tra cứu giá trị phân biệt chữ hoa chữ thường và tính tổng các giá trị tương ứng của chúng bằng hàm SUMPRODUCT, bạn cần đưa hàm EXACT vào bên trong công thức.
Giả sử bạn có một số giá trị phân biệt chữ hoa chữ thường trong bảng và bạn muốn tính tổng các giá trị tương ứng, hãy thử công thức dưới đây:
=SUMPRODUCT(--EXACT("Oranges",A2:A15),D2:D15)Phần EXACT("Oranges",A2:A15)của công thức tìm kiếm chuỗi được chỉ định chính xác (Oranges) với các ký tự chữ hoa giống nhau trong phạm vi A2: A15. Nếu tìm thấy một kết quả phù hợp, nó sẽ trả về TRUE hoặc nếu không thì FALSE. Các kết quả đúng hoặc sai này được chuyển đổi thành các giá trị số bằng âm kép – {0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 1}. Sau đó, mảng kết quả được nhân với mảng tổng (D2: D15). Và, kết quả của sản phẩm cuối cùng được cộng lại để cung cấp cho bạn tổng tổng – 248.
Đếm ô bằng hàm SUMPRDUCT
Chúng ta đã thấy cách tính tổng các ô có điều kiện trong Excel, bây giờ, hãy xem cách đếm các ô đáp ứng tiêu chí / tiêu chí bằng cách sử dụng hàm SUMPRODUCT.
Cú pháp :
=SUMPRODUCT(--(array=condition))Đếm ô với một điều kiện duy nhất
Giả sử bạn có một danh sách các loại trái cây, số lượng và giá của chúng trong các cột A, B và C tương ứng.
Bây giờ, chúng ta muốn tìm xem có bao nhiêu mặt hàng có số lượng ít hơn 20. Để làm điều đó, chúng ta có thể sử dụng bất kỳ công thức nào dưới đây:
=SUMPRODUCT(--(C2:C15<20))Trong đó, C2:C15<20điều kiện kiểm tra xem mỗi giá trị trong cột C có nhỏ hơn 20 hay không và trả về mảng kết quả này:
{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}.
Sau đó, mảng kết quả được chuyển đổi thành 1 và 0 bằng cách phủ định kép: {0, 0, 1, 1, 1, 0, 1, 0, 0, 1, 0, 1, 0,1}. Sau đó, SUMPRODUCT tổng hợp tất cả các kết quả để cung cấp cho chúng tôi số lượng ô – ‘7’.
Ngoài ra, bạn cũng có thể chuyển đổi các giá trị boolean thành các giá trị số bằng cách nhân mảng kết quả với 1:
SUMPRODUCT((C2:C15<20)*1)Dù bằng cách nào, cả hai công thức sẽ cung cấp cho bạn số lượng như nhau.
Đếm ô dựa trên nhiều điều kiện với AND logic
Nếu bạn có nhiều hơn một điều kiện và bạn chỉ muốn đếm ô khi tất cả các điều kiện đó được đáp ứng, bạn có thể sử dụng công thức SUMPRODUCT với logic AND.
Ví dụ:
Ví dụ: nếu bạn muốn đếm bao nhiêu lần doanh số bán Peach nhỏ hơn 100, bạn có thể thêm hai tiêu chí vào công thức SUMPRODUCT – một tiêu chí để tìm kiếm mục ‘Peach’ trong cột A và một tiêu chí khác để kiểm tra xem doanh số tương ứng có ít hơn không hơn 100:
=SUMPRODUCT(--(A2:A15="Peach"),--(D2:D15<100))hoặc, nếu bạn muốn một công thức ngắn, bạn có thể thử cách này để thay thế:
=SUMPRODUCT((A2:A15=G3)*(D2:D15<100))A2:A15="Peach"hoặc A2:A15=G3 kiểm tra mục ‘Peach’ trong cột A2: A15 và D2:D15<100 kiểm tra số lượng bán hàng nhỏ hơn 100. Nếu cả hai điều kiện được thỏa mãn trong cùng một hàng (hàng 4 và hàng 6), nó sẽ đếm các hàng đó và trả về kết quả .
Bên trong công thức, A2:A15="Peach"điều kiện trả về mảng kết quả được chuyển đổi này: {0, 0, 1, 0, 1, 0, 0, 0, 1, 0, 1, 0, 1, 0} và D2: D15<100 trả về mảng kết quả được chuyển đổi này : {0, 0, 1, 1, 1, 1, 1, 0, 0, 0,0,1, 0, 0}. Và khi cả hai đều là mảng được nhân lên, bạn sẽ nhận được số “2”.
Đếm ô dựa trên nhiều điều kiện với OR logic
Bạn có thể sử dụng biểu tượng dấu cộng (+) ở giữa các mảng để đếm ô với logic HOẶC.
Ví dụ:
Để tìm số lượng bán Peach và Oranges bất kể khu vực, hãy nhập công thức dưới đây:
=SUMPRODUCT((A2:A15="Peach")+(A2:A15="Oranges"))Trong công thức trên, dấu cộng (+) giữa các mảng hoạt động như một toán tử OR và trả về TRUE nếu bất kỳ điều kiện nào trong hai điều kiện đã cho được thỏa mãn. Nếu một trong hai mục ‘Peach’ hoặc ‘Orange’ được tìm thấy trong danh sách, công thức sẽ trả về TRUE. Toán tử OR cũng hoạt động như một công cụ chuyển đổi và tạo ra mảng giá trị này: {0, 1, 1, 0, 1, 1, 0, 1, 1, 0, 1, 0, 1, 0}. Sau đó, mảng kết quả được thêm vào để tạo ra đầu ra: 8.
Đếm ô dựa trên nhiều điều kiện với AND cũng như OR Logic
Tương tự, bạn có thể đếm ô có điều kiện bằng cách sử dụng hàm SUMPRODUCT với cả logic OR và AND cùng một lúc.
Ví dụ:
Để tìm số lượng bán Oranges và Peach ở khu vực phía East, hãy sử dụng công thức dưới đây:
=SUMPRODUCT(((A2:A15="Peach")+(A2:A15="Oranges"))*(B2:B15="East"))Tại đây, (A2:A15="Peach")+(A2:A15="Oranges")một phần của công thức sẽ kiểm tra mục “Peach” hoặc “Oranges” trong A2: A15. Nếu một trong hai mục được tìm thấy trong cột A, nó sẽ đánh giá là TRUE (nếu không thì là FALSE) được chuyển thành “1”, nếu không thì là “0”.
(B2:B15="East") tìm kiếm giá trị ‘East’ trong cột B và nếu được tìm thấy, giá trị này sẽ cho giá trị TRUE, giá trị này cũng được chuyển thành ‘1’. Sau đó, cả hai kết quả từ lôgic OR và AND đều được nhân để tạo ra – {0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 0, 1, 0,0}. Cuối cùng, mảng kết quả cuối cùng được tính bằng – ‘4’.
Đếm các giá trị riêng biệt bằng cách sử dụng công thức SUMPRODUCT và COUNTIF
Công thức SUMPRODUCT cũng hữu ích khi bạn muốn đếm các giá trị riêng biệt từ danh sách các giá trị. Các giá trị Phân biệt là tất cả các giá trị khác nhau bao gồm các mục nhập duy nhất trong danh sách. Các giá trị khác biệt cũng được coi là giá trị duy nhất trong một số trường hợp, vì vậy biết cách tìm chúng rất hữu ích.
Không quan trọng giá trị có bao nhiêu bản sao, chỉ một trường hợp của giá trị đó được đưa vào số lượng. Ví dụ: nếu giá trị ‘New York’ được lặp lại 5 lần trong danh sách, thì giá trị đó vẫn chỉ được tính là ‘1’.
Công thức đơn giản nhất để đếm các giá trị riêng biệt là sự kết hợp của SUMPRODUCT và COUNTIF.
Đây là cú pháp để đếm các giá trị duy nhất và khác biệt trong một cột:
=SUMPRODUCT(1/COUNTIF(data,data))data Phạm vi dữ liệu mà bạn muốn đếm giá trị ở đâu .
Ví dụ: chúng tôi muốn tìm số lượng các giá trị khác biệt trong danh sách sau:
Để làm điều đó, hãy sử dụng công thức sau:
=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))Hãy để chúng tôi chia nhỏ nó cho bạn:
COUNTIF(A2:A10,A2:A10):Hàm COUNTIF lồng nhau đếm số lần mỗi giá trị xuất hiện trong phạm vi ô (A2: A10) và trả về một mảng các số như sau{1;4;1;4;2;1;4;2;4}:. tức là Giá trị chỉ xuất hiện một lần là 1, giá trị xuất hiện 4 lần là 4, v.v.1/COUNTIF(A2:A10,A2:A10): Sau đó, mảng số kết quả từ công thức COUNTIF được dùng làm số chia / số chia cho phép chia với1làm tử số của chúng. Vì vậy, 1 được chia cho mỗi giá trị của mảng kết quả của COUNTIF. Kết quả là, bạn sẽ nhận được một mảng kết quả khác{1;0.25;1;0.25;0.5;1;0.25;0.5;0.25}:.
Như bạn có thể thấy, giá trị chỉ xuất hiện một lần (duy nhất) là 1 và các giá trị xuất hiện nhiều lần sẽ trở thành phân số. Ví dụ: giá trị ‘San Francisco’ xuất hiện hai lần, vì vậy khi 1 chia cho 2, bạn sẽ nhận được ‘0,5’.
Cuối cùng, hàm SUMPRODUCT cộng tất cả các số trong mảng và trả về số lượng các giá trị riêng biệt: ‘5’. Số lượng bao gồm tất cả các giá trị khác nhau xuất hiện ít nhất một lần trong danh sách, không bao gồm các giá trị trùng lặp.
Đếm các giá trị phân biệt bỏ qua / bao gồm các ô trống bằng cách sử dụng công thức SUMPRODUCT và COUNTIF
Tuy nhiên, khi sử dụng công thức trên nếu bất kỳ ô nào trong phạm vi trống hoặc trống, công thức có thể gây ra lỗi # DIV / 0. Đó là bởi vì một ô trống sẽ tạo ra 0 trong mảng kết quả được tạo bởi công thức COUNTIF. Vì vậy, khi 1 chia cho 0, nó sẽ dẫn đến lỗi # DIV / 0 như hình dưới đây.
Để bao gồm các ô trống hoặc ô trống trong bộ đếm trong khi đếm các giá trị riêng biệt, hãy thử công thức dưới đây:
=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10&""))Như bạn có thể thấy ở trên, khi chúng ta nối một chuỗi trống (“”) với dữ liệu trong đối số tiêu chí của hàm COUNTIF, nó sẽ trả về 1 cho một ô trống trong mảng kết quả {1;4;1;4;2;1;1;2;4}:. Vì vậy, công thức, 1/COUNTIF(A2:A10,A2:A10&"") = {1;0.25;1;0.25;0.5;1;1;0.5;0.25}. Cuối cùng, hàm SUMPRODUCT cộng tất cả các số từ mảng và cung cấp cho chúng ta các giá trị riêng biệt – 6 cũng bao gồm ô trống trong số.
Để bỏ qua các ô trống hoặc ô trống khỏi số lượng trong khi đếm các giá trị riêng biệt, hãy sử dụng công thức dưới đây:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))Tại đây, A2:A10<>"" tạo ra một mảng kết quả TRUE hoặc FALSE. Khi một ô trống hoặc trống trong phạm vi, nó sẽ trả về FALSE. Vì vậy, khi giá trị FALSE được chia cho bất kỳ số nào, nó sẽ trả về 0. Kết quả là bây giờ công thức này (A2: A10 <> ””) / COUNTIF (A2: A10, A2: A10 & ””) trả về mảng này: {1 ; 0,25; 1; 0,25; 0,5; 1; 0; 0,5; 0,25}. Khi SUMPRODUCT tổng hợp mảng kết quả, bạn sẽ nhận được ‘5’ làm số cuối cùng.
Tra cứu hai chiều với hàm SUMPRODUCT
Thông thường, bạn có thể sử dụng hàm INDEX và MATCH để tra cứu giá trị theo hai chiều, nhưng trong các phiên bản Excel trước, hàm SUMPRODUCT thường được sử dụng để tra cứu và trích xuất hai chiều. SUMPRODCUT cũng hoàn toàn có khả năng tra cứu các giá trị theo hai hướng.
Giả sử chúng ta có tập dữ liệu dưới đây, nơi chúng ta muốn tra cứu và trả về điểm của Eric trong môn Lịch sử.
Để thực hiện tra cứu hai chiều, hãy viết công thức SUMPRODUCT sau:
=SUMPRODUCT((A2:A10=D13)*(A1:G1=D14),A2:G10)Trong công thức, hãy A2:A10=D13tìm giá trị của ô D13 xuống cột A2: A10 và A1:G1=D14tìm giá trị của ô D14 dọc theo hàng A2: A10. Sau đó, hàm SUMPRODUCT trả về giá trị tại giao điểm của hai lần tìm kiếm, là 67.
Nếu có nhiều giá trị thỏa mãn cùng một điều kiện, thì công thức sẽ trả về tổng các giá trị trả về.
Ví dụ: nếu có hai Erics ‘trong cột tên học sinh, thì hàm sẽ trả về tổng điểm Lịch sử của cả hai:
Tính giá trị trung bình có trọng số bằng hàm SUMPRODUCT
Một ứng dụng phổ biến khác của hàm SUMPRODUCT là tính giá trị trung bình có trọng số trong đó mỗi giá trị trong tập dữ liệu được gán một trọng số giống hệt nhau. Trung bình có trọng số là giá trị trung bình của một tập dữ liệu có tính đến các mức độ quan trọng khác nhau.
Cú pháp của công thức trung bình có trọng số SUMPRODUCT:
=SUMPRODUCT(values, weights) / SUM(weights)Ví dụ: giả sử bạn có bảng thống kê lớp dưới đây trong cột B và trọng lượng của chúng trong cột C.
=SUMPRODUCT(C2:C6, B2:B6) / SUM(B2:B6)Trong công thức trên, bạn nhân từng phần trăm (trọng lượng) với loại tương ứng và cộng các giá trị sản phẩm thu được với nhau. Sau đó, chia tổng số sản phẩm đó cho tổng của năm trọng lượng.
Đây là những gì công thức trên trông giống như bên trong:=(B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(B2+B3+B4+B5+B6)
SUMPRODUCT với hàm IF
Bạn có thể kết hợp hàm ‘SUMPRODUCT’ và hàm ‘IF’ để tính tổng các giá trị dựa trên một điều kiện.
Cú pháp:
=SUMPRODUCT(IF(criteria_range=criteria, values_range1*values_range2))Từ tập dữ liệu dưới đây, chúng tôi cần tìm tổng doanh số bán hàng của ‘Oranges’. Để làm điều đó, chúng ta có thể sử dụng công thức dưới đây:
=SUMPRODUCT(IF(A2:A15=G3,C2:C15*D2:D15))Sau khi nhập công thức, bạn cần nhấn Ctrl+ Shift+ Enter đồng thời để áp dụng công thức này dưới dạng công thức mảng.
Hàm IF kiểm tra giá trị “Oranges” trong phạm vi A2: A15 và nếu được tìm thấy, nó sẽ nhân các giá trị tương ứng trong cột C và D. Sau đó, hàm Sumproduct tính tổng tất cả các giá trị của sản phẩm.
SUMPRODUCT và IF với nhiều điều kiện
Để tìm tổng doanh số bán Oranges từ miền Tây, chúng ta cần thêm một tiêu chí khác vào công thức:
=SUMPRODUCT(IF(A2:A15=G2,IF(B2:B15=G3,C2:C15*D2:D15)))Sau khi nhập công thức, hãy nhớ nhấn Ctrl+ Shift+ Enterđể áp dụng công thức đó dưới dạng công thức mảng.
Hàm IF đầu tiên trong công thức SUMPRODUCT tìm kiếm giá trị được nhập trong G2 (Cam) trong phạm vi A2: A15. Nếu giá trị được tìm thấy, hàm IF thứ hai sẽ kiểm tra các giá trị được nhập vào G3 (Tây) trong phạm vi B2: B15. Và nếu cả hai điều kiện đều được thỏa mãn, hàm SUMPRODUCT sẽ nhân các giá trị tương ứng trong C2: C15 và D2: D15. Trong trường hợp bất kỳ giá trị đã chỉ định nào không được tìm thấy trong phạm vi, nó sẽ trả về FALSE.
Kết quả là, chúng tôi nhận được mảng kết quả này – {FALSE; 760; FALSE; FALSE; FALSE; FALSE; 114; FALSE; FALSE; FALSE; 1311; FALSE; FALSE; 99. Ở đây, FALSE có nghĩa là 0. Cuối cùng, hàm SUMPRODUCT cộng các kết quả để trả về – 859.
SUMPRODUCT với các chức năng khác
Hàm SUMPRODUCT có thể được tích hợp với các hàm khác để xử lý chức năng mảng. Thay vì nhập các công thức dài và nhấn Ctrl+ Shift+ Entermỗi khi bạn muốn chạy công thức mảng, bạn có thể sử dụng chỉ cần SUMPRODUCT để thực hiện các phép tính trực tiếp trên mảng.
Ví dụ 1:
Ví dụ: giả sử bạn có một danh sách gồm 5 từ hoặc chuỗi văn bản khác nhau trong phạm vi A2: A6 và bạn muốn đếm tổng số ký tự của tất cả 5 chuỗi văn bản. Thông thường, bạn sẽ thêm một cột trợ giúp trong cột B và nhập một hàm LEN như = LEN (A2), Sau đó áp dụng công thức tương tự cho các ô B3, B4, B5 và B6. Cuối cùng, bạn sẽ sử dụng hàm SUM để thêm tất cả các kết quả đó như = SUM (A2: A6).
Tuy nhiên, với hàm SUMPRODUCT, bạn có thể tính tổng tất cả các ký tự bằng một công thức duy nhất mà không có cột trợ giúp bổ sung:
=SUMPRODUCT(LEN(A2:A6))
SUMPRODUCT với hàm MONTH
SUMPRODUCT có thể được sử dụng để tính tổng hoặc đếm các giá trị dựa trên tháng bằng cách sử dụng hàm MONTH.
Ví dụ: chúng tôi đã nhập một số tháng vào ô D2 và chúng tôi muốn đếm và tính tổng các giá trị cho tháng được chỉ định.
Để đếm số lần bán hàng trong tháng 3, chúng ta có thể sử dụng công thức dưới đây:
=SUMPRODUCT(--(MONTH(B2:B12)=E3))Ở đây, hàm MONTH kéo tháng từ mỗi ngày trong B2: B12 và kiểm tra nó với tháng được chỉ định trong E3. Nếu nó bằng nhau, nó trả về TRUE, FALSE, ngược lại – {TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE, TRUE}.
Sau đó, dấu gạch ngang kép hoặc dấu phủ định chuyển đổi mảng thành sau: {1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1}. Sau đó, SUMPRODUCT tổng hợp mảng để tạo ra số lần bán hàng tháng 3 – 4.
Trong trường hợp bạn muốn tìm tổng doanh thu của tháng 3, hãy nhập công thức dưới đây:
=SUMPRODUCT(--(MONTH(B2:B12)=E3),C2:C12)Hàm MONTH kéo tháng từ mỗi ngày trong B2: B12 và kiểm tra nó với tháng được chỉ định trong E3. Nếu nó bằng nhau, nó trả về TRUE, FALSE, ngược lại thì được chuyển đổi thành {1, 0, 0, 0, 0, 0, 0, 1, 0, 1,1}. Sau đó, hàm SUMPRODUCT nhân mảng kết quả với các giá trị tương ứng trong cột Doanh số (C2: C12) và cộng các giá trị sản phẩm để trả về tổng doanh số của tháng 3 – 2118.
SUMPRODUCT với hàm ISNUMBER
Nếu bạn muốn đếm số lần xuất hiện cho các từ cụ thể từ một dải ô, bạn có thể kết hợp hàm SUMPRODUCT với hàm ISNUMBER và FIND để tạo công thức.
Để đếm tất cả các lần xuất hiện của các từ được chỉ định trong ô D5, hãy sử dụng công thức sau:
=SUMPRODUCT(--(ISNUMBER(FIND(D5,A2:A16))))Trong công thức trên, hàm FIND tìm kiếm chuỗi văn bản được nhập vào ô D5 (Xerox) trong mỗi ô của phạm vi A2: A16. Nếu chuỗi văn bản được tìm thấy, hàm trả về vị trí bắt đầu của từ, nếu không, hàm trả về lỗi #VALUE! error.
Sau đó, hàm ISNUMBER trả về TRUE nếu hàm FIND tạo ra một số, ngược lại, nó trả về FALSE. Sau đó, phủ định kép chuyển đổi kết quả ISNUMBER thành này – {0, 1, 1, 0, 0, 1, 0, 1, 0, 0, 1, 0, 0, 0, 1}. Cuối cùng, hàm SUMPRODUCT tổng hợp mảng kết quả thành – 6.
SUMPRODUCT với chức năng LARGE / SMALL
Hàm LARGE và SMALL trả về các số lớn nhất và nhỏ nhất thứ n trong danh sách các giá trị khi được sắp xếp theo các giá trị theo thứ tự giảm dần hoặc tăng dần.
Ví dụ: nếu bạn muốn tính tổng 5 giá trị lớn nhất hàng đầu, hãy thử công thức dưới đây:
=SUMPRODUCT(LARGE(A1:A15,{1,2,3,4,5}))Trong công thức, hàm LARGE trả về 5 giá trị hàng đầu từ danh sách A1: A15. Thông thường, hàm LARGE trả về giá trị lớn nhất thứ n, nhưng chúng tôi đã đặt {1, 2, 3, 4, 5} trong dấu ngoặc kép. Vì vậy, nó trả về tất cả 5 số lớn nhất – {111, 95, 69, 52, 49}. Sau đó, công thức SUMPRODUCT cộng các số đó thành tổng – 377.
Để tính tổng 5 giá trị nhỏ nhất, hãy nhập công thức dưới đây:
=SUMPRODUCT(SMALL(A1:A15,{1,2,3,4,5}))Trong công thức, hàm SMALL trả về 5 số dưới cùng của phạm vi A1: A15. Thông thường, hàm SMALL trả về giá trị lớn nhất thứ n, nhưng chúng tôi đã đặt {1, 2, 3, 4, 5} trong dấu ngoặc kép. Vì vậy, nó trả về tất cả 5 số nhỏ nhất – {3, 5, 9, 10, 12}. Sau đó, công thức SUMPRODUCT cộng các số đó và xuất ra tổng của 5 số nhỏ nhất – 39.
So sánh Mảng với SUMPRODUCT
Hàm SUMPRODUCT không chỉ đếm, tổng, trung bình có điều kiện các ô với nhiều tiêu chí từ nhiều cột, nó còn có thể so sánh hai hoặc nhiều mảng.
Giả sử, bạn có bảng dưới đây và bạn muốn so sánh doanh số bán hàng của ngày hôm qua với doanh số bán hàng của ngày hôm nay và tính xem hôm nay có bao nhiêu sản phẩm có doanh số bán hàng nhiều hơn ngày hôm qua. Để làm được điều đó, thông thường bạn phải so sánh cột C với D và giữ nguyên số điểm. Tuy nhiên, với SUMPRODUCT, bạn có thể tự động hóa quy trình bằng một công thức duy nhất. Hãy xem làm thế nào chúng ta có thể làm điều đó:
Đây là công thức SUMPRODUCT mà bạn có thể sử dụng để so sánh cột C và D:
=SUMPRODUCT(--(D2:D12>C2:C12))Ở đây, chúng tôi muốn xem liệu doanh số bán hàng của ngày hôm nay có nhiều hơn doanh số bán hàng của ngày hôm qua hay không vì vậy chúng tôi đã thêm toán tử lớn hơn (>) giữa các đối số mảng.
Công thức kiểm tra từng giá trị của cột Bán hàng Hôm nay với giá trị tương ứng của cột Bán hàng Ngày hôm qua. Nếu một giá trị trong phạm vi D2: D12 lớn hơn các giá trị tương ứng trong C2: C12, nó trả về TRUE; nếu không, trả về FALSE. Sau đó, kép một ngôi (-) chuyển đổi các kết quả đó thành mảng này {1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0}. Sau đó, tất cả các giá trị đó được cộng lại và trả về dưới dạng tổng (3), là số lượng sản phẩm đã bán được hôm nay nhiều hơn ngày hôm qua.
Nhưng nếu bạn chỉ muốn xem doanh số của Peach cao hơn ngày hôm qua là bao nhiêu? Sau đó, bạn phải đáp ứng một điều kiện khác trong các đối số:
=SUMPRODUCT(--(A2:A12="Peach"),--(D2:D12>C2:C12))Đối --(A2:A12=”Peach”)số kiểm tra xem các giá trị ‘Peach’ có nằm trong phạm vi A2: A12 hay không và nếu nó nằm trong một ô, nó sẽ trả về 1; nếu không, trả về 0. Điều này sẽ tạo ra một mảng kết quả khác {0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 1}.
=SUMPRODUCT({0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 1}, {0, 1, 1, 0, 1, 0, 1, 1, 0, 1,1})
Sau đó, hàm Sumproduct nhân mảng đầu tiên với cặp tương ứng của chúng trong mảng thứ hai và cộng kết quả sản phẩm để tạo ra đầu ra, là 2. Như bạn có thể thấy, chỉ có 2 lần doanh số ngày hôm nay của Peach cao hơn hôm qua.
