Table of Contents
Khi bạn nghe tên SUMPRODUCT lần đầu tiên, có thể bạn sẽ nghĩ đó chỉ là 1 công thức để thực hiện phép tính tổng bình thường của các sản phẩm. Nhưng đó chỉ là một phần nhỏ mà những gì hàm SUMPRODUCT có thể làm được. Vậy SUMPRODUCT là hàm gì và cách sử dụng hàm này như thế nào? GhienCongNghe sẽ giải đáp cho bạn trong bài viết dưới đây.
Hàm SUMPRODUCT trong Excel – cú pháp và cách sử dụng
Hàm SUMPRODUCT trong Excel nhân các số trong các mảng được chỉ định và trả về tổng của các sản phẩm đó.
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 đó thêm vào.
Số mảng tối thiểu là 1. Trong trường hợp này, công thức 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). Chỉ cần thực hiện công thức SUMPRODUCT theo cách thông thường bằng cách nhấn phím Enter.
Ghi chú:
- 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 bạn nhận được lỗi #VALUE! lỗi.
- Nếu bất kỳ đối số mảng nào chứa các giá trị không phải là số, chúng sẽ được coi là số không.
- Nếu một mảng là một phép kiểm tra lôgic, thì nó cho kết quả là các giá trị TRUE và FALSE. Trong hầu hết các trường hợp, bạn cầ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 (-). Vui lòng xem ví dụ SUMPRODUCT với nhiều tiêu chí để biết thêm chi tiết.
- SUMPRODUCT không hỗ trợ các ký tự đại diện.
Cách sử dụng cơ bản của SUMPRODUCT trong Excel
Để hiểu chung về cách hoạt động của hàm SUMPRODUCT trong Excel, hãy xem xét ví dụ sau.
Giả sử bạn có số lượng trong các ô A2: A4, giá trong các ô B2: B4 và bạn muốn tìm ra tổng số. Nếu bạn đang làm một bài kiểm tra toán ở trường, bạn sẽ nhân số lượng với giá của mỗi mặt hàng, sau đó cộng tổng phụ. Trong Microsoft Excel, bạn có thể nhận được kết quả bằng một công thức SUMPRODUCT:
=SUMPRODUCT(A2:A4,B2:B4)
Các ả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:
Công thức lấy 1 st số trong 1 st mảng và nhân lên nó bằng 1 st số trong 2 nd mảng, sau đó lấy 2 nd số trong 1 st mảng và nhân lên nó bằng 2 nd số trong 2 nd mảng , và như thế.
Khi tất cả các phần tử của mảng được nhân lên, công thức sẽ cộng các tích và trả về tổng.
Nói cách khác, công thức SUMPRODUCT của chúng tôi thực hiện các phép toán sau:
=A2*B2 + A3*B3 + A4*B4
Chỉ cần nghĩ rằng nó có thể giúp bạn tiết kiệm bao nhiêu thời gian nếu bảng của bạn không chứa 3 hàng dữ liệu mà là 3 trăm hoặc 3 nghìn hàng!
Cách sử dụng SUMPRODUCT trong Excel – ví dụ về công thức
Nhân hai hoặc nhiều phạm vi với nhau và sau đó tính tổng các sản phẩm là cách sử dụng SUBTOTAL đơn giản và rõ ràng nhất trong Excel, mặc dù không phải là cách duy nhất. Và hàm SUMPRODUCT trong Excel có thể làm được nhiều hơn mục đích đã nêu của nó.
SUMPRODUCT với nhiều tiêu chí
Thông thường trong Microsoft Excel, có nhiều cách để hoàn thành cùng một tác vụ. Nhưng khi so sánh hai hoặc nhiều mảng, đặc biệt với nhiều tiêu chí, SUMPRODUCT là giải pháp hiệu quả nhất, nếu không phải là duy 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 có 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”))
Cách hoạt động của công thức SUMPRODUCT với một điều kiện
Đối với người mới bắt đầu, hãy chia nhỏ một công thức đơn giản hơn để so sánh các số trong 2 cột theo từng hàng và cho chúng 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 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 chỉ định đượ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)
Cách hoạt động của công thức SUMPRODUCT 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ả.
Như bạn có thể nhớ, chúng tôi đã sử dụng các công thức sau để tìm ra số lần doanh số bán hàng thực (cột C) ít hơn doanh số kế hoạch (cột B) cho Táo (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à, chúng tôi nhận được hai mảng gồm các số một và số không:
Phép toán nhân do SUMPRODUCT thực hiện sẽ nối chúng thành một mảng duy nhất. Và vì nhân với 0 luôn cho không, 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:
Đếm có điều kiện / tổng / ô trung bình với nhiều tiêu chí
Trong Excel 2003 và các phiên bản cũ hơn không có cái gọi là hàm IF, một trong những cách sử dụng phổ biến nhất của hàm SUMPRODUCT là tính tổng hoặc đếm ô có nhiều tiêu chí có điều kiện. Bắt đầu với Excel 2007, 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.
Nhưng ngay cả trong các phiên bản Excel hiện đại, công thức SUMPRODUCT vẫn có thể được sử dụng thay thế. Xem ví dụ dưới đây để minh họa cho việc tính tổng và đếm các ô có điều kiện bằng logic OR.
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 – mặt hàng và cột C – số liệu bán hàng:
Những gì bạn muốn là có được số lượng, tổng và trung bình doanh số bán apples cho khu vực phía Bắc.
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 SUMIFS , COUNTIFS và AVERAGEIFS. Nếu bạn không tìm kiếm những cách dễ dàng hoặc nếu bạn vẫn đang sử dụng Excel 2003 trở lên, bạn có thể nhận được kết quả mong muốn với SUMPRODUCT.
Để tính doanh số bán Apples cho khu vực phía Bắc:
=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 hàng cho 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ị trung bình, chúng ta chỉ cần chia Tổng cho Đếm như sau:
=SUMPRODUCT(–(A2:A12=”north”), –(B2:B12=”apples”), C2:C12) / SUMPRODUCT( –(A2:A12=”north”), –(B2:B12=”apples”))
Để thêm tính linh hoạt hơn cho các công thức SUMPRODUCT, bạn có thể chỉ định Vùng và Mục mong muốn trong 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 cho 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 đó, bạn sẽ rất dễ dàng hiểu được logic tổng.
Sử dụng công thức sau để tính tổng doanh số bán 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 đại diện cho miền Bắc và 0 cho bất kỳ khu vực nào khác.
- Trong mảng thứ 2, 1 là viết tắt của Apples và 0 cho bất kỳ mặt hàng nào khác.
- Mảng thứ 3 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ể; bằng 0 nếu không:
Cộng các con số trong mảng trên mang lại kết quả như mong muốn – tổng doanh số về apples 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.
Trong công thức SUMPRODUCT của Excel, cũng như trong công thức mảng , 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 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 apples và 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 dịch sang tiếng Anh đơn giản, công thức đọc như sau:
=SUMPRODUCT((B2:B12=”apples”)+(B2:B12=”lemons”))
Để tính tổng doanh số bán apples và lemons, hãy thêm một đối số khác có chứa phạm vi Doanh số:
=SUMPRODUCT((B2:B12=”apples”)+(B2:B12=”lemons”), C2:C12)
Ảnh chụp màn hình sau đây 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 ô có 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, chuỗi hàm IF 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 Excel 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, hãy xem xét các ví dụ sau.
Để đếm bao nhiêu lần apples và lemons đã được bán, 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 apples và lemons ở khu vực phía Bắc, hãy lấy công thức trên và thêm mảng Doanh số 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 các biến trong các ô riêng biệt – Vùng trong F1 và Mục trong F2 và H2 – và tham khảo các ô đó trong công thức của bạn:
Công thức SUMPRODUCT cho trung bình có trọng số
Trong một trong những ví dụ trước, chúng ta đã thảo luận về công thức SUMPRODUCT cho giá trị trung bình có điều kiện . Một cách sử dụng phổ biến khác của 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 trung bình có trọng số SUMPRODUCT chung như sau:
SUMPRODUCT(values, weights) / SUM(weights)
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 trung bình có trọng số sẽ giống như sau:
=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)
SUMPRODUCT thay thế cho công thức mảng
Về cơ bản, khi sử dụng hàm SUMPRODUCT bạn sẽ có thể quản lý công thức của mình một cách dễ dàng mà không cần phải nhấn (Ctrl + Shift + Enter) mỗi khi bạn nhập mới hoặc chỉnh sửa công thức mảng hiện có.
Ví dụ, chúng ta có thể lấy một công thức mảng đơn giản đếm tất cả các ký tự trong một phạm vi nhất định:
{=SUM(LEN(range))}
và biến nó thành một công thức thông thường:
=SUMPRODUCT(LEN(range))
Để thực hành, bạn có thể sử dụng các công thức mảng Excel này và thử viết lại sau đó sử dụng hàm SUMPRODUCT. Ngoài ra, bạn có thể tải xuống sổ làm việc mẫu Excel SUMPRODUCT và thiết kế ngược các công thức để hiểu rõ hơn.
Excel SUMPRODUCT – ví dụ về công thức nâng cao
Bây giờ bạn đã biết cú pháp và logic của hàm SUMPRODUCT trong Excel, bạn có thể muốn tìm hiểu các công thức phức tạp hơn và mạnh mẽ hơn trong đó hàm SUMPRODUCT được sử dụng liên kết với các hàm Excel khác.
- SUMPRODUCT cho hàm VLOOKUP phân biệt chữ hoa chữ thường – cách thực hiện tra cứu phân biệt chữ hoa chữ thường và trả về các số phù hợp. Đối với tra cứu phân biệt chữ hoa chữ thường hoạt động cho tất cả các kiểu dữ liệu, hãy sử dụng công thức MATCH / INDEX này .
- Tra cứu hai chiều trong Excel – Công thức SUMPRODUCT để tra cứu một giá trị tại giao điểm của một hàng và cột nhất định.
- Cách đếm ký tự trong Excel – công thức để đếm tổng số hoặc chỉ các ký tự cụ thể trong một phạm vi.
SUMPRODUCT để đếm từ trong Excel – công thức để lấy tổng số từ trong một phạm vi xác định hoặc chỉ đếm các từ cụ thể. - Cách đếm các giá trị riêng biệt – tìm hiểu có bao nhiêu giá trị khác nhau xuất hiện trong một phạm vi (duy nhất cộng với số lần xuất hiện trùng lặp đầu tiên).
- Đếm các giá trị trùng lặp và duy nhất – Công thức SUMPRODUCT / COUNTIF để đếm các giá trị trùng lặp và duy nhất trong một cột hoặc giữa 2 cột.
- Cách tính tổng dữ liệu theo tháng – Công thức SUMPRODUCT / MONTH để nhận tổng giá trị của một tháng nhất định.
- Chỉ trích xuất các số từ chuỗi chữ và số – SUMPRODUCT kết hợp với 7 hàm khác trích xuất tất cả các số từ chuỗi số-văn bản-số.
Xem thêm:
Trên đây GhienCongNghe đã giới thiệu cho bạn SUMPRODUCT là hàm gì và cách sử dụng trong các trường hợp khác nhau. Hy vọng những thông tin trên sẽ bổ ích với bạn và đừng quên Like, Share và Comment ý kiến của bạn ở phía dưới nhé.
Theo ablebits.com
