Hàm sumproduct dùng để làm gì

     

Dùng Excel bạn không nên bỏ qua hàm SUMPRODUCT. Để gọi SUMPRODUCT là hàm gì cùng dịp như thế nào áp dụng hàm này hãy quan sát và theo dõi nội dung bài viết dưới đây của web5s.com.vn nhé.

Bạn đang xem: Hàm sumproduct dùng để làm gì


Ngô Tuấn

khi các bạn nghe tên SUMPRODUCT lần thứ nhất, rất có thể các bạn sẽ suy nghĩ kia chỉ là 1 trong công thức để thực hiện phxay tính tổng thông thường của những thành phầm. Nhưng đó chỉ cần một phần nhỏ dại mà lại đông đảo gì hàm SUMPRODUCT rất có thể có tác dụng được. Vậy SUMPRODUCT là hàm gì với giải pháp thực hiện hàm này như vậy nào? web5s.com.vn sẽ đáp án cho chính mình vào nội dung bài viết sau đây.

*


Nội dung

Hàm SUMPRODUCT trong Excel – cú pháp và cách sử dụngCách sử dụng SUMPRODUCT vào Excel – ví dụ về công thứcĐếm bao gồm điều kiện / tổng / ô trung bình với tương đối nhiều tiêu chí

Hàm SUMPRODUCT vào Excel – cú pháp cùng phương pháp sử dụng

Hàm SUMPRODUCT vào Excel nhân các số trong số mảng được chỉ định và hướng dẫn cùng trả về tổng của các thành phầm đó.

Cú pháp của hàm SUMPRODUCT hết sức dễ dàng và đơn giản với dễ dàng hiểu:

SUMPRODUCT (array1, , ,…)

Trong đó array1, array2, v.v. là những dải ô hoặc mảng tiếp tục có những bộ phận bạn có nhu cầu nhân với tiếp đến tiếp tế.

Số mảng buổi tối tphát âm là 1 trong những. Trong ngôi trường đúng theo này, phương pháp SUMPRODUCT chỉ cần cộng toàn bộ những bộ phận của mảng và trả về tổng.

Số mảng tối nhiều là 255 trong Excel 2016, Excel 2013, Excel 2010 và Excel 2007 cùng 30 trong những phiên phiên bản Excel cũ hơn.

Mặc cho dù SUMPRODUCT hoạt động cùng với mảng, mà lại nó không trải nghiệm sử dụng phím tắt mảng (Ctrl + Shift + Enter). Chỉ cần triển khai cách làm SUMPRODUCT theo cách thông thường bằng phương pháp thừa nhận phím Enter.

Ghi chú:

Tất cả những mảng vào công thức SUMPRODUCT bắt buộc tất cả cùng số mặt hàng và số cột, còn nếu như không bạn nhận ra lỗi #VALUE! lỗi.Nếu ngẫu nhiên đối số mảng như thế nào chứa các quý giá không phải là số, chúng sẽ tiến hành xem như là số không.Nếu một mảng là một trong phép khám nghiệm lôgic, thì nó cho kết quả là các giá trị TRUEFALSE. Trong hầu như những ngôi trường thích hợp, bạn phải biến đổi bọn chúng thành 1 cùng 0 bằng phương pháp áp dụng toán thù tử đơn phân kép (-). Vui lòng xem ví dụ SUMPRODUCT với tương đối nhiều tiêu chí để hiểu biết thêm cụ thể.SUMPRODUCT không hỗ trợ các ký kết trường đoản cú thay mặt.

Cách sử dụng cơ bạn dạng của SUMPRODUCT trong Excel

Để phát âm phổ biến về phong thái hoạt động của hàm SUMPRODUCT trong Excel, hãy xem xét ví dụ sau.

Giả sử bạn có con số trong số ô A2: A4, giá chỉ trong các ô B2: B4 với bạn có nhu cầu đưa ra toàn bô. Nếu nhiều người đang có tác dụng một bài xích bình chọn toán thù sống ngôi trường, các bạn sẽ nhân số lượng với giá của từng sản phẩm, sau đó cùng tổng phụ. Trong Microsoft Excel, chúng ta có thể nhận thấy hiệu quả bởi một cách làm SUMPRODUCT:

=SUMPRODUCT(A2:A4,B2:B4)

Các ảnh chụp màn hình hiển thị dưới đây cho thấy nó đang hoạt động:

*

Đây là tất cả những gì đã diễn ra về khía cạnh toán học:

Công thức đem 1 st số vào 1 st mảng và nhân lên nó bởi 1 st số vào 2 nd mảng, sau đó mang 2 nd số vào 1 st mảng cùng nhân lên nó bằng 2 nd số vào 2 nd mảng , với như thế. lúc toàn bộ các phần tử của mảng được nhân lên, phương pháp vẫn cùng những tích với trả về tổng. Nói biện pháp khác, phương pháp SUMPRODUCT của Cửa Hàng chúng tôi tiến hành các phxay tân oán sau:

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

Chỉ cần cho rằng nó rất có thể giúp đỡ bạn tiết kiệm ngân sách từng nào thời hạn ví như bảng của người tiêu dùng không chứa 3 sản phẩm dữ liệu mà lại là 3 trăm hoặc 3 ngàn hàng!

Cách thực hiện SUMPRODUCT trong Excel – ví dụ về công thức

Nhân nhì hoặc các phạm vi với nhau và tiếp đến tính tổng các sản phẩm là phương pháp sử dụng SUBTOTAL dễ dàng và cụ thể độc nhất vào Excel, mặc dù chưa hẳn là biện pháp nhất. Và hàm SUMPRODUCT trong Excel rất có thể có tác dụng được không ít rộng mục đích đang nêu của chính nó.

SUMPRODUCT với rất nhiều tiêu chí

thường thì vào Microsoft Excel, có khá nhiều cách để hoàn thành cùng một tác vụ. Nhưng khi đối chiếu hai hoặc nhiều mảng, đặc trưng với khá nhiều tiêu chí, SUMPRODUCT là giải pháp tác dụng tuyệt nhất, nếu không phải là nhất,

Giả sử các bạn có list các mặt hàng làm việc cột A, số liệu bán hàng theo chiến lược ngơi nghỉ cột B và lợi nhuận bán hàng thực tế sinh sống cột C. Mục tiêu của chúng ta là tìm ra gồm bao nhiêu mặt hàng đang bán tốt thấp hơn kế hoạch. Đối với vấn đề này, hãy áp dụng một trong những thay đổi thể sau của công thức SUMPRODUCT:

=SUMPRODUCT(–(C2:C10

*

Nhưng điều gì đã xẩy ra nếu bạn có khá nhiều hơn một điều kiện? Giả sử, bạn muốn đếm xem apples có các thành tích tệ hơn planer từng nào lần. Giải pháp là thêm 1 tiêu chuẩn nữa vào bí quyết SUMPRODUCT:

=SUMPRODUCT(–(C2:C10

*

Cách buổi giao lưu của công thức SUMPRODUCT với một điều kiện

Đối với những người new bắt đầu, hãy phân chia nhỏ một cách làm đơn giản hơn để so sánh những số vào 2 cột theo từng hàng với mang đến bọn họ biết cột C nhỏ tuổi hơn cột B từng nào lần:

=SUMPRODUCT(–(C2:C10

*

Những gì bọn họ bao gồm sống đó là một mảng các quý hiếm Boolean TRUEFALSE, trong số ấy TRUE Có nghĩa là điều kiện được chỉ định và hướng dẫn được đáp ứng nhu cầu (tức thị cực hiếm trong cột C nhỏ rộng cực hiếm vào cột B vào cùng một hàng) với FALSE Tức là ĐK không chạm chán.

Âm kép (-), về mặt kỹ thuật được Call là toán thù tử 1-1 phân kép , buộc TRUE với FALSE thành các cực hiếm đơn và không: 0; 1; 0; 0; 1; 0; 1; 0; 0.

Một phương pháp không giống để đổi khác những quý giá súc tích thành những quý hiếm số là bội mảng với 1:

=SUMPRODUCT((C2:C10

*

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

khi một bí quyết SUMPRODUCT trong Excel cất nhì hoặc nhiều mảng, nó đang nhân các thành phần của tất cả các mảng, kế tiếp cùng lại tác dụng.

Như chúng ta cũng có thể ghi nhớ, Cửa Hàng chúng tôi sẽ sử dụng các cách làm sau để tìm ra tần số lợi nhuận bán hàng thực (cột C) ít hơn lợi nhuận kế hoạch (cột B) cho Táo (cột A):

=SUMPRODUCT(–(C2:C10

*

Phép toán nhân vì SUMPRODUCT tiến hành vẫn nối chúng thành một mảng độc nhất vô nhị. Và do nhân với 0 luôn mang lại ko, 1 chỉ mở ra Lúc cả hai ĐK được thỏa mãn nhu cầu cùng vì thế, chỉ hầu như mặt hàng kia mới được tính:

*

Đếm gồm điều kiện / tổng / ô vừa đủ với khá nhiều tiêu chí

Trong Excel 2003 với những phiên bản cũ rộng không có chiếc điện thoại tư vấn là hàm IF, một trong số những phương pháp áp dụng thịnh hành độc nhất của hàm SUMPRODUCT là tính tổng hoặc đếm ô có rất nhiều tiêu chuẩn có ĐK. Bắt đầu với Excel 2007, Microsoft vẫn trình làng hàng loạt các hàm có thiết kế quan trọng cho các tác vụ điều này – SUMIFS, COUNTIFS và AVERAGEIFS.

Nhưng trong cả trong những phiên bản Excel tân tiến, cách làm SUMPRODUCT vẫn hoàn toàn có thể được thực hiện sửa chữa thay thế. Xem ví dụ dưới đây nhằm minch họa mang lại câu hỏi tính tổng và đếm những ô bao gồm ĐK bằng xúc tích và ngắn gọn OR.

lấy ví dụ như 1. Công thức SUMPRODUCT với xúc tích AND

Giả sử các bạn tất cả tập tài liệu sau, trong đó cột A liệt kê những khoanh vùng, cột B – món đồ và cột C – số liệu buôn bán hàng:

*

Những gì bạn có nhu cầu là đạt được con số, tổng và trung bình doanh thu buôn bán apples mang đến khoanh vùng phía Bắc.

Xem thêm: Nghiên Cứu Tại Bàn ( Desk Research Là Gì ? Định Nghĩa, Ví Dụ, Giải Thích

Trong các phiên phiên bản gần đây của Excel năm nhâm thìn, 2013, 2010 với 2007, chúng ta có thể dễ dàng tiến hành tác vụ này bằng cách sử dụng cách làm SUMIFS , COUNTIFS cùng AVERAGEIFS. Nếu bạn không tìm kiếm những phương pháp dễ dãi hoặc nếu như khách hàng vẫn sẽ áp dụng Excel 2003 trlàm việc lên, bạn cũng có thể nhận thấy công dụng mong muốn với SUMPRODUCT.

Để tính doanh thu cung cấp Apples đến khoanh vùng 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 sản phẩm 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 quý giá vừa đủ, bọn họ chỉ việc phân tách Tổng đến Đếm như sau:

=SUMPRODUCT(–(A2:A12=”north”), –(B2:B12=”apples”), C2:C12) / SUMPRODUCT( –(A2:A12=”north”), –(B2:B12=”apples”))

Để thêm tính linch hoạt rộng cho các cách làm SUMPRODUCT, chúng ta cũng có thể chỉ định Vùng và Mục mong muốn trong các ô riêng lẻ, kế tiếp ttê mê chiếu các ô đó trong bí quyết của bạn nlỗi được hiển thị trong hình họa chụp màn hình hiển thị mặt dưới:

*

Cách hoạt động vui chơi của bí quyết SUMPRODUCT đến tổng có điều kiện

Từ ví dụ trước , các bạn đã hiểu cách thức công thức SUMPRODUCT trong Excel đếm những ô có rất nhiều ĐK. Nếu các bạn phát âm điều đó, các bạn sẽ rất dễ dãi hiểu được xúc tích và ngắn gọn tổng.

Sử dụng bí quyết sau nhằm tính tổng doanh thu cung cấp apples ngơi nghỉ Quanh Vùng phía Bắc :

=SUMPRODUCT(–(A2:A12=”north”), –(B2:B12=”apples”), C2:C12)

Kết quả trung gian của bí quyết bên trên là 3 mảng sau:

*

Trong mảng trước tiên, 1 đại diện thay mặt đến miền Bắc cùng 0 đến bất kỳ khu vực nào không giống.Trong mảng thứ hai, 1 là viết tắt của Apples và 0 mang lại bất kỳ món đồ nào không giống.Mảng thứ 3 cất các số bán hàng chính xác nlỗi chúng xuất hiện trong số ô C2: C12.

Hãy hãy nhờ rằng nhân với 0 luôn đến ko và nhân với một mang đến cùng một trong những, chúng ta cảm nhận mảng cuối cùng bao gồm số bán hàng với số không – số bán sản phẩm chỉ xuất hiện thêm nếu như nhì mảng đầu tiên có một làm việc cùng địa điểm, Tức là cả nhị thỏa mãn nhu cầu những ĐK thế thể; bởi 0 ví như không:

*

Cộng các con số vào mảng bên trên đem lại kết quả suôn sẻ – tổng lợi nhuận về apples Quanh Vùng phía Bắc.

lấy ví dụ 2. Công thức SUMPRODUCT cùng với ngắn gọn xúc tích OR

Để tính tổng hoặc đếm ô có ĐK với xúc tích và ngắn gọn OR, hãy sử dụng ký kết hiệu dấu cộng (+) trung tâm các mảng.

Trong phương pháp SUMPRODUCT của Excel, cũng tương tự trong bí quyết mảng , biểu tượng vết cộng vận động hệt như tân oán tử OR hướng dẫn Excel trả về TRUE nếu như BẤT KỲ ĐK nào vào một biểu thức nhất định được Đánh Giá là TRUE.

Ví dụ: để tính tổng lợi nhuận chào bán apples với lemons bất kể Khu Vực làm sao, hãy thực hiện cách làm sau:

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

Được dịch lịch sự giờ Anh dễ dàng và đơn giản, bí quyết phát âm nlỗi sau:

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

Để tính tổng doanh thu chào bán apples và lemons, hãy thêm 1 đối số không giống bao gồm chứa phạm vi Doanh số:

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

Ảnh chụp screen sau đây cho thấy một công thức tương tự như vẫn hoạt động:

*

lấy ví dụ như 3. Công thức SUMPRODUCT cùng với xúc tích AND cũng tương tự OR

Trong các ngôi trường đúng theo, chúng ta cũng có thể nên đếm hoặc tính tổng bao gồm điều kiện các ô bao gồm súc tích AND với xúc tích OR tại một thời điểm. ngay khi trong các phiên bạn dạng Excel tiên tiến nhất, chuỗi hàm IF ko có khả năng đó.

giữa những phương án khả thi là phối kết hợp hai hoặc nhiều hàm SUMIFS + SUMIFS hoặc COUNTIFS + COUNTIFS.

Một cách không giống là sử dụng hàm SUMPRODUCT trong Excel vào đó:

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

Để khiến cho phần đa lắp thêm dễ dàng nắm bắt hơn, hãy xem xét các ví dụ sau.

Để đếm từng nào lần apples với lemons đã có cung cấp, tạo nên một cách làm cùng với ngắn gọn xúc tích sau:

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

Lúc áp dụng cú pháp SUMPRODUCT phù hợp, bí quyết gồm dạng sau:

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

Để tính tổng doanh số bán apples cùng lemons sinh sống khu vực phía Bắc, hãy đem công thức trên với thêm mảng Doanh số cùng với xúc tích và ngắn gọn AND:

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

Để làm cho những bí quyết nhỏ gọn gàng hơn một ít, chúng ta có thể nhập những đổi thay trong các ô lẻ tẻ – Vùng trong F1 và Mục trong F2 cùng H2 – cùng tham khảo những ô đó vào cách làm của bạn:

*

Công thức SUMPRODUCT đến vừa đủ bao gồm trọng số

Trong một trong số những ví dụ trước, chúng ta đang trao đổi về cách làm SUMPRODUCT đến giá chỉ trị vừa phải bao gồm điều kiện . Một bí quyết thực hiện thịnh hành không giống của SUMPRODUCT trong Excel là tính tân oán mức độ vừa phải tất cả trọng số trong đó mỗi giá trị được gán một trọng số nhất định.

Công thức mức độ vừa phải bao gồm trọng số SUMPRODUCT tầm thường như sau:

SUMPRODUCT(values, weights) / SUM(weights)

Giả sử rằng các cực hiếm phía trong ô B2: B7 và trọng số nằm trong ô C2: C7, phương pháp SUMPRODUCT vừa phải bao gồm trọng số đang y hệt như sau:

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

*

SUMPRODUCT thay thế sửa chữa đến cách làm mảng

Về cơ bản, lúc thực hiện hàm SUMPRODUCT bạn sẽ hoàn toàn có thể làm chủ công thức của bản thân một phương pháp dễ dãi mà lại không nhất thiết phải dìm (Ctrl + Shift + Enter) từng khi bạn nhập mới hoặc sửa đổi công thức mảng hiện nay tất cả.

lấy ví dụ như, bạn cũng có thể lấy một bí quyết mảng dễ dàng và đơn giản đếm tất cả những ký kết từ bỏ trong một phạm vi nhất định:

=SUM(LEN(range))

cùng trở thành nó thành một cách làm thông thường:

=SUMPRODUCT(LEN(range))

*

Để thực hành thực tế, bạn cũng có thể thực hiện các công thức mảng Excel này với demo viết lại tiếp nối sử dụng hàm SUMPRODUCT. Bên cạnh đó, bạn cũng có thể tải xuống sổ làm việc mẫu mã Excel SUMPRODUCT và xây cất ngược những công thức để làm rõ rộng.

Excel SUMPRODUCT – ví dụ về cách làm nâng cao

Bây giờ bạn đã biết cú pháp và súc tích của hàm SUMPRODUCT vào Excel, bạn cũng có thể mong khám phá các cách làm phức tạp rộng với trẻ trung và tràn đầy năng lượng rộng trong các số đó hàm SUMPRODUCT được thực hiện link với các hàm Excel không giống.

SUMPRODUCT cho hàm VLOOKUP rành mạch chữ hoa chữ thường – cách triển khai tra cứu giúp phân biệt chữ hoa chữ thường cùng trả về những số cân xứng. Đối với tra cứu giúp rành mạch chữ hoa chữ thường xuyên chuyển động mang lại toàn bộ những đẳng cấp dữ liệu, hãy sử dụng bí quyết MATCH / INDEX này .Tra cứu vớt hai phía trong Excel – Công thức SUMPRODUCT để tra cứu giúp một cực hiếm trên giao điểm của một mặt hàng cùng cột nhất định.Cách đếm ký kết từ bỏ vào Excel – phương pháp nhằm đếm tổng cộng hoặc chỉ các ký từ rõ ràng trong một phạm vi. SUMPRODUCT nhằm đếm trường đoản cú vào Excel – công thức để đưa tổng cộng trường đoản cú vào một phạm vi xác minh hoặc chỉ đếm những tự rõ ràng.Cách đếm các giá trị riêng biệt biệt – tìm hiểu bao gồm bao nhiêu quý giá không giống nhau xuất hiện trong một phạm vi (duy nhất cộng với mốc giới hạn mở ra giống nhau đầu tiên).Đếm những cực hiếm giống nhau và duy nhất – Công thức SUMPRODUCT / COUNTIF nhằm đếm những quý hiếm giống nhau với độc nhất trong một cột hoặc thân 2 cột.Cách tính tổng dữ liệu theo tháng – Công thức SUMPRODUCT / MONTH nhằm nhấn tổng mức của một tháng cố định.Chỉ trích xuất các số từ chuỗi chữ cùng số – SUMPRODUCT kết phù hợp với 7 hàm không giống trích xuất toàn bộ những số từ chuỗi số-vnạp năng lượng bản-số.

Xem thêm: Phân Loại Thư Trong Gmail - Cách Để Sắp Xếp Gmail Theo Người Gửi (Kèm Ảnh)

Trên phía trên web5s.com.vn vẫn giới thiệu cho chính mình SUMPRODUCT là hàm gì và phương pháp sử dụng trong những ngôi trường phù hợp không giống nhau. Hy vọng hồ hết thông tin bên trên vẫn bổ ích cùng với bạn và hãy nhớ là Like, Share cùng Comment chủ ý của người tiêu dùng sinh hoạt phía dưới nhé.