Hàm SUMPRODUCT() trong Excel

Trong bài viết này freetuts sẽ hướng dẫn cách sử dụng hàm SUMPRODUCT trong Excel, giới thiệu một số ví dụ liên quan đến hàm.

Mình sẽ giới thiệu một số ví dụ về công thức để so sánh các mảng, tính tổng và đếm các ô có điều kiện, tính trung bình và hơn thế nữa với hàm SUMPRODUCT.

Khi nghe tên hàm SUMPRODUCT lần đầu tiên, có thể bạn sẽ liên tưởng đến hàm SUM trong Excel để tính tổng một cách đơn giản, tuy nhiên hàm này có nhiều công dụng và cách sử dụng phức tạp hơn hàm SUM rất nhiều, chúng ta hãy cùng tìm hiểu thật kỹ hàm này trong bài viết.

Thực tế SUMPRODUCT là một hàm có chức năng rất linh hoạt với nhiều công dụng, khả năng độc đáo của nó là để xử lý các mảng theo cách thông minh và cực kỳ nhanh chóng. SUMPRODUCT cực kỳ hữu ích và không thể thiếu khi so sánh dữ liệu trong hai hoặc nhiều phạm vi. Các ví dụ sau sẽ giúp bạn nắm rõ toàn bộ công dụng của hàm SUMPRODUCT một cách rõ ràng nhất.

Bài viết này được đăng tại [free tuts .net]

I. Cú pháp của Hàm SUMPRODUCT trong Excel

Hàm SUMPRODUCT trong Excel thuộc nhóm hàm xử lý dữ liệu mảng / hàm Math & Trig. Về mặt kỹ thuật thì hàm SUMPRODUCT sẽ nhân các số trong những mảng được chỉ định rồi trả về tổng của chúng.

Cú pháp của hàm SUMPRODUCT rất đơn giản và dễ hiểu:

SUMPRODUCT (array1, [array2], [array3],…)

Trong đó:

array1, array2, v.v. là các dải ô hoặc mảng liên tục có các phần tử bạn muốn nhân và sau đó cộng.

• Số mảng tối thiểu là 1: Trong trường hợp này thì hàm SUMPRODUCT chỉ cần cộng tất cả các phần tử của mảng và trả về tổng.
• Số mảng tối đa là 255 trong Excel 2016, Excel 2013, Excel 2010 và Excel 2007 và 30 trong các phiên bản Excel cũ hơn.

Mặc dù SUMPRODUCT hoạt động với mảng nhưng nó không yêu cầu sử dụng phím tắt mảng (Ctrl + Shift + Enter). Bạn chỉ cần nhập công thức SUMPRODUCT theo cách thông thường bằng cách nhấn phím Enter.

* Lưu ý:

• Tất cả các mảng trong công thức SUMPRODUCT phải có cùng số hàng và số cột, nếu không sẽ bị lỗi #VALUE!.
• Nếu bất kỳ đối số mảng nào chứa các giá trị không phải là số thì chúng sẽ được coi là số “0”.
• Nếu mảng là một phép kiểm tra logic thì nó sẽ trả về các giá trị TRUE và FALSE. Trong hầu hết các trường hợp, bạn nên chuyển đổi chúng thành 1 và 0 bằng cách sử dụng toán tử đơn phân kép (-).
• SUMPRODUCT không hỗ trợ các ký tự đại diện.

II. Cách sử dụng hàm SUMPRODUCT trong Excel

Để hiểu cách hoạt động cơ bản của hàm SUMPRODUCT trong Excel, chúng ta cùng xem xét ví dụ sau:

Giả sử bạn có số lượng sản phẩm (trong các ô A2: A4), giá (Price) ở các ô hàng bên cạnh (B2: B4) và bạn muốn tìm ra tổng số, nếu đang làm một bài kiểm tra toán thì bạn sẽ phải "nhân số lượng với giá của mỗi mặt hàng rồi cộng tổng".

Trong Microsoft Excel, bạn có thể nhận được kết quả nhanh chóng bằng một công thức SUMPRODUCT:

=SUMPRODUCT(A2:A4,B2:B4)

Ảnh chụp màn hình sau đây cho thấy nó đang hoạt động:

Đây là những gì đang diễn ra về mặt toán học:

Hàm SUMPRODUCT đã lấy giá trị ở mảng 1 nhân với mảng 2 trong hàng tương ứng sau đó cộng tổng của chúng.

Nói cách khác công thức SUMPRODUCT thực hiện các phép toán sau:

=A2*B2 + A3*B3 + A4*B4

Ta có thể thấy cách hoạt động của hàm SUMPRODUCT giúp chúng ta tiết kiệm được rất nhiều thời gian, đặc biệt là khi bảng của bạn không chỉ có 3 hàng dữ liệu như ví dụ trên mà có đến 300 hay 3000 hàng.

III. Ví dụ về hàm SUMPRODUCT trong Excel

“Nhân hai hoặc nhiều phạm vi với nhau sau đó tính tổng các tích” là cách sử dụng SUMPRODUCT đơn giản và rõ ràng nhất, nhưng đây chắc chắn không phải là cách duy nhất.

Hàm SUMPRODUCT trong Excel có thể làm được nhiều hơn thế, chúng ta cùng tiếp tục theo dõi một số ví dụ sử dụng hàm SUMPRODUCT nâng cao và thú vị hơn.

1. Thêm điều kiện / tiêu chí vào hàm SUMPRODUCT

Thông thường trong Microsoft Excel, có nhiều cách để hoàn thành cùng một tác vụ. Khi so sánh hai hoặc nhiều mảng, đặc biệt là với nhiều tiêu chí thì SUMPRODUCT là giải pháp hiệu quả nhất.

Giả sử bạn có danh sách các mặt hàng ở cột A, số liệu bán hàng theo kế hoạch ở cột B và doanh số bán hàng thực tế ở cột C. Mục tiêu của bạn là tìm ra bao nhiêu mặt hàng đã bán được ít hơn kế hoạch. Đối với điều này, hãy sử dụng một trong các biến thể sau của công thức SUMPRODUCT:

=SUMPRODUCT(--(C2:C10<B2:B10))

hoặc là

=SUMPRODUCT((C2:C10<B2:B10)*1)

Trong đó C2: C10 là doanh số bán hàng thực và B2: B10 là doanh số bán hàng theo kế hoạch.

Nhưng điều gì sẽ xảy ra nếu bạn có nhiều hơn một điều kiện? Giả sử bạn muốn đếm xem Apples có thành tích tệ hơn kế hoạch bao nhiêu lần. Giải pháp là thêm một tiêu chí nữa vào công thức SUMPRODUCT:

=SUMPRODUCT(--(C2:C10<B2:B10), --(A2:A10="apples"))

Hoặc, bạn có thể sử dụng cú pháp sau:

=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10="apples"))

Và bây giờ, chúng ta hãy dành một phút và hiểu những gì các công thức trên thực sự đang làm. Tôi tin rằng đó là một khoản đầu tư thời gian xứng đáng vì nhiều công thức SUMPRODUCT khác hoạt động với cùng một logic.

Cách hoạt động của hàm SUMPRODUCT trong Excel với một điều kiện

Đối với người mới bắt đầu, hãy chia nhỏ công thức đơn giản hơn để so sánh các số trong 2 cột theo từng hàng, cuối cùng cho ta biết cột C nhỏ hơn cột B bao nhiêu lần:

=SUMPRODUCT(--(C2:C10<B2:B10))

Nếu bạn chọn phần (C2: C10 <B2: B10) trong thanh công thức (Function bar) và nhấn F9 để xem các giá trị cơ bản, bạn sẽ thấy mảng sau:

Những gì chúng ta có ở đây là một mảng các giá trị Boolean TRUE và FALSE, trong đó TRUE có nghĩa là điều kiện cụ thể được đáp ứng (nghĩa là giá trị trong cột C nhỏ hơn giá trị trong cột B trong cùng một hàng) và FALSE có nghĩa là điều kiện không gặp.

Âm kép (-), về mặt kỹ thuật được gọi là toán tử đơn phân kép, buộc TRUE và FALSE thành các giá trị đơn và không: {0; 1; 0; 0; 1; 0; 1; 0; 0}.

Một cách khác để chuyển đổi các giá trị logic thành các giá trị số là bội mảng với 1:

=SUMPRODUCT((C2:C10<B2:B10)*1)

Dù bằng cách nào, vì chỉ có một mảng trong công thức SUMPRODUCT nên nó chỉ đơn giản là cộng 1 trong mảng kết quả sau đó chúng ta sẽ nhận được số lượng mong muốn.

Cách hoạt động của hàm SUMPRODUCT trong Excel với nhiều điều kiện

Khi một công thức SUMPRODUCT trong Excel chứa hai hoặc nhiều mảng, nó sẽ nhân các phần tử của tất cả các mảng sau đó cộng lại kết quả.

Bạn có thể nhớ lại mình đã sử dụng các công thức sau để tìm ra số lần bán hàng thực (cột C) ít hơn doanh số kế hoạch (cột B) cho Apples (cột A):

=SUMPRODUCT(--(C2:C10<B2:B10), --(A2:A10="apples"))

hoặc là

=SUMPRODUCT((C2:C10<B2:B10)*(A2:A10="apples"))

Sự khác biệt về công nghệ duy nhất giữa các công thức là phương pháp ép TRUE và FALSE thành 1 và 0 - bằng cách sử dụng phép toán nhân đôi hoặc phép nhân. Kết quả là mình nhận được hai mảng gồm 1 và 0:

Phép toán nhân được thực hiện bởi SUMPRODUCT sẽ nối chúng thành một mảng duy nhất. Vì nhân với 0 luôn cho kết quả = 0, 1 chỉ xuất hiện khi cả hai điều kiện được đáp ứng và do đó chỉ những hàng đó mới được tính:

2. Đếm có điều kiện / tổng / ô trung bình với nhiều tiêu chí

Một trong những cách sử dụng phổ biến nhất của hàm SUMPRODUCT trong Excel là tính tổng hoặc đếm các ô có nhiều tiêu / điều kiện.

Trong Excel 2003 và các phiên bản cũ hơn không có cái gọi là hàm IF, bắt đầu với Excel 2007 thì Microsoft đã giới thiệu một loạt các hàm được thiết kế đặc biệt cho các tác vụ như vậy: SUMIFS, COUNTIFS và AVERAGEIFS.

Trong các phiên bản Excel hiện đại thì công thức SUMPRODUCT vẫn có thể thay thế các hàm mình vừa giới thiệu bên trên, giúp tính tổng và đếm các ô có điều kiện bằng logic OR. Dưới đây là một vài ví dụ về công thức hàm SUMPRODUCT trong thực tế.

Ví dụ 1. Công thức SUMPRODUCT với logic AND

Giả sử bạn có tập dữ liệu sau, trong đó cột A liệt kê các khu vực, cột B là mặt hàng và cột C chứa số liệu bán hàng:

Điều bạn muốn là có được số lượng, tổng và trung bình doanh số bán táo (Apples) cho khu vực phía bắc (North).

Trong các phiên bản gần đây của Excel 2016, 2013, 2010 và 2007, bạn có thể dễ dàng thực hiện tác vụ này bằng cách sử dụng công thức SUMIF, COUNTIF và AVERAGEIF. Nếu không tìm kiếm những cách dễ dàng hoặc bạn vẫn đang sử dụng Excel 2003, hãy sử dụng hàm SUMPRODUCT để có kết quả tương tự.

Để tính doanh số bán táo (Apples) cho miền bắc (North):

=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"))

hoặc là

​=SUMPRODUCT((A2:A12="north")*(B2:B12="apples"))

Để tổng hợp táo bán cho miền bắc:

=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12)

hoặc là

=SUMPRODUCT((A2:A12="north")*(B2:B12="apples")*C2:C12)

Để tính giá trị Average (trung bình) doanh số bán táo cho miền bắc, chúng ta chỉ cần chia Sum cho Count như sau:

=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12) / SUMPRODUCT( --(A2:A12="north"), --(B2:B12="apples"))

Để tăng sự linh hoạt cho công thức SUMPRODUCT, bạn có thể chỉ định Region và Item vào các ô riêng biệt sau đó tham chiếu các ô đó trong công thức của bạn như được hiển thị trong ảnh chụp màn hình bên dưới:

Cách hoạt động của công thức SUMPRODUCT tính tổng có điều kiện

Từ ví dụ trước, bạn đã biết cách công thức SUMPRODUCT trong Excel đếm các ô có nhiều điều kiện. Nếu bạn hiểu điều đó thì sẽ rất dễ dàng hiểu được logic tổng.

Xin nhắc lại rằng mình đã sử dụng công thức sau để tính tổng doanh số bán táo (Apples) ở khu vực phía bắc :

=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12)

Kết quả trung gian của công thức trên là 3 mảng sau:

• Trong mảng thứ nhất, 1 là viết tắt của Bắc và 0 cho bất kỳ vùng nào khác.
• Trong mảng thứ hai, 1 là viết tắt của Táo và 0 cho bất kỳ mặt hàng nào khác.
• Mảng thứ ba chứa các số bán hàng chính xác như chúng xuất hiện trong các ô C2: C12.

Hãy nhớ rằng nhân với 0 luôn cho không và nhân với 1 cho cùng một số, chúng ta nhận được mảng cuối cùng bao gồm số bán hàng và số không - số bán hàng chỉ xuất hiện nếu hai mảng đầu tiên có 1 ở cùng vị trí, tức là cả hai đáp ứng các điều kiện cụ thể.

Cộng các con số trong mảng trên mang lại kết quả mong muốn - tổng doanh thu của táo ở khu vực phía bắc.

Ví dụ 2. Công thức SUMPRODUCT với logic OR

Để tính tổng hoặc đếm ô có điều kiện với logic OR hãy sử dụng ký hiệu dấu cộng (+) ở giữa các mảng.

Với công thức hàm SUMPRODUCT trong Excel thì biểu tượng dấu cộng hoạt động giống như toán tử OR, hướng dẫn Excel trả về TRUE nếu ANY (bất kỳ) điều kiện nào trong một biểu thức nhất định được đánh giá là TRUE.

Ví dụ: để tính tổng doanh số bán táo (Apples) và chanh (Lemons) bất kể khu vực nào, hãy sử dụng công thức sau:

=SUMPRODUCT((B2:B12="apples")+(B2:B12="lemons"))

Có thể diễn giải công thức như sau: Đếm ô nếu B2: B12 = "apples" HOẶC B2: B12 = "lemons".

Để tính tổng doanh số táo và chanh ta chỉ việc thêm một đối số khác có chứa phạm vi Sales:

=SUMPRODUCT((B2:B12="apples")+(B2:B12="lemons"), C2:C12)

Ảnh chụp màn hình sau cho thấy một công thức tương tự đang hoạt động:

Ví dụ 3. Công thức SUMPRODUCT với logic AND cũng như OR

Trong nhiều trường hợp, bạn có thể cần đếm hoặc tính tổng có điều kiện các ô với logic AND và logic OR tại một thời điểm. Ngay cả trong các phiên bản Excel mới nhất thì chuỗi hàm IF cũng không có khả năng đó.

Một trong những giải pháp khả thi là kết hợp hai hoặc nhiều hàm SUMIFS + SUMIFS hoặc COUNTIFS + COUNTIFS.

Một cách khác là sử dụng hàm SUMPRODUCT, trong đó:

• Dấu hoa thị (*) được sử dụng làm toán tử AND.
• Ký hiệu dấu cộng (+) được sử dụng làm toán tử OR.

Để làm cho mọi thứ dễ hiểu hơn, bạn hãy xem các ví dụ sau.

Để đếm số lần táo (Apples) và chanh (Lemons) đã được bán ở khu vực phía bắc (North), hãy tạo ra một công thức với logic sau:

=Count If ((Region="north") AND ((Item="Apples") OR (Item="Lemons")))

Khi áp dụng cú pháp SUMPRODUCT thích hợp, công thức có dạng sau:

=SUMPRODUCT((A2:A12="north")*((B2:B12="apples")+(B2:B12="lemons")))

Để tính tổng doanh số bán táo và chanh ở khu vực phía bắc (North), hãy lấy công thức trên và thêm mảng Sales với logic AND:

=SUMPRODUCT((A2:A12="north")*((B2:B12="apples")+(B2:B12="lemons"))*C2:C12)

Để làm cho các công thức nhỏ gọn hơn một chút, bạn có thể nhập biến vào các ô riêng biệt:

Trong ví dụ này mình đã nhập vùng (Region) tại ô F1 và Item trong F2 và H2.

3. Công thức SUMPRODUCT tính trung bình có trọng số

Ở một trong những ví dụ trước, chúng ta đã thảo luận về công thức SUMPRODUCT tính giá trị trung bình có điều kiện. Một cách sử dụng phổ biến khác của hàm SUMPRODUCT trong Excel là tính toán trung bình có trọng số, trong đó mỗi giá trị được gán một trọng số nhất định.

Công thức tính trung bình có trọng số SUMPRODUCT cơ bản như sau:

SUMPRODUCT(values, weights) / SUM(weights)

Trong đó values là giá trị còn weights là trọng số.

Giả sử rằng các giá trị nằm trong ô B2: B7 và trọng số nằm trong ô C2: C7, công thức SUMPRODUCT tính trung bình có trọng số sẽ giống như sau:

=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)

Mình tin rằng khi đọc đến phần này, bạn sẽ không gặp bất kỳ khó khăn nào để hiểu logic công thức.

IV. Tổng kết

Qua bài viết “Hàm SUMPRODUCT trong Excel”, mình hy vọng sẽ truyền tải được những cách sử dụng hàm giúp bạn tiết kiệm thời gian tính toán khi làm việc.

Đừng quên chia sẻ bài viết tới bạn bè và đồng nghiệp khi họ chưa biết cách sử dụng hàm SUMPRODUCT trong Excel nha.

Tham khảo: ablebits.com