Hàm sumproduct trong excel 2007

-

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

Bạn đang xem: Hàm sumproduct trong excel 2007

*


*

Mình sẽ ra mắt một số lấy một ví dụ về cách làm để so sánh các mảng, tính tổng với đếm các ô bao gồm điều kiện, tính mức độ vừa phải và hơn thế nữa với hàm SUMPRODUCT.

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

Thực tế SUMPRODUCT là 1 hàm có tính năng rất linh hoạt với tương đối nhiều công dụng, khả năng lạ mắt của nó là nhằm xử lý những 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ể không có khi đối chiếu dữ liệu trong nhị hoặc nhiều phạm vi. Những ví dụ sau sẽ giúp bạn nắm vững toàn bộ tính năng của hàm SUMPRODUCT một cách rõ ràng nhất.

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

Hàm SUMPRODUCT trong Excel thuộc team hàm xử lý dữ liệu mảng / hàm Math & Trig. Về mặt kỹ thuật thì hàm SUMPRODUCT vẫn nhân các số một 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 dễ dàng và đơn giản và dễ hiểu:

SUMPRODUCT (array1, , ,…)Trong đó:

array1, array2, v.v. Là những dải ô hoặc mảng liên tiếp có những phần tử bạn có nhu cầu nhân và tiếp đến cộng.

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

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

* lưu lại ý:

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

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

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

Giả sử chúng ta có số số lượng hàng hóa (trong các ô A2: A4), giá (Price) ở các ô hàng cạnh bên (B2: B4) và bạn có nhu cầu tìm ra tổng số, giả dụ đang làm một bài xích kiểm tra toán thì các 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, chúng ta có thể nhận được tác dụng nhanh chóng bằng một cách làm SUMPRODUCT:

=SUMPRODUCT(A2:A4,B2:B4)Ảnh chụp màn hình hiển thị sau đây cho biết nó đang hoạt động:

Đây là tất cả những gì đang diễn ra về phương diện toán học:

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

Nói biện pháp khác công thức SUMPRODUCT triển khai các phép toán sau:

=A2*B2 + A3*B3 + A4*B4Ta hoàn toàn có thể thấy cách buổi giao lưu của hàm SUMPRODUCT giúp họ tiết kiệm được rất nhiều thời gian, nhất là khi bảng của doanh nghiệp không chỉ tất cả 3 hàng tài liệu như lấy ví dụ như trên mà có đến 300 xuất xắc 3000 hàng.

III. Lấy ví dụ về hàm SUMPRODUCT trong Excel

“Nhân nhì hoặc các phạm vi với nhau tiếp nối tính tổng các tích” là cách thực hiện SUMPRODUCT đơn giản và ví dụ nhất, nhưng lại đây chắc hẳn rằng không cần là giải pháp duy nhất.

Hàm SUMPRODUCT trong Excel có thể làm được không ít hơn thế, họ cùng liên tiếp theo dõi một trong những ví dụ sử dụng hàm SUMPRODUCT nâng cấp và thú vui hơn.

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

Thông thường trong Microsoft Excel, có nhiều cách để hoàn thành và một tác vụ. Khi đối chiếu hai hoặc các mảng, nhất là với nhiều tiêu chuẩn thì SUMPRODUCT là chiến thuật hiệu quả nhất.

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

=SUMPRODUCT(--(C2:C10hoặc là

=SUMPRODUCT((C2:C10Trong đó C2: C10 là doanh số bán sản phẩm thực cùng 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ó khá nhiều hơn một điều kiện? trả sử bạn muốn đếm coi Apples gồm thành tích tệ rộng kế hoạch bao nhiêu lần. Chiến thuật là thêm một tiêu chuẩn nữa vào cách làm SUMPRODUCT:

=SUMPRODUCT(--(C2:C10Hoặc, bạn cũng có thể sử dụng cú pháp sau:

=SUMPRODUCT((C2:C10

Và bây giờ, chúng ta hãy dành riêng một phút với hiểu mọi gì những công thức bên trên thực sự đang làm. Tôi có niềm tin rằng đó là 1 trong khoản đầu tư chi tiêu thời gian xứng danh vì nhiều bí quyết SUMPRODUCT khác chuyển động với và một logic.

Cách buổi giao lưu của hàm SUMPRODUCT vào Excel với cùng một điều kiện

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

=SUMPRODUCT(--(C2:C10Nếu bạn chọn phần (C2: C10 đều gì chúng ta có ở đây là một mảng các giá trị Boolean TRUE với FALSE, trong những số ấy TRUE tức là điều kiện rõ ràng được đáp ứng (nghĩa là quý hiếm trong cột C bé dại hơn giá trị trong cột B trong và một hàng) và FALSE tức là điều kiện không gặp.

Âm kép (-), về khía cạnh kỹ thuật được điện thoại tư vấn là toán tử 1-1 phân kép, buộc TRUE cùng FALSE thành các giá trị đối kháng và không: 0; 1; 0; 0; 1; 0; 1; 0; 0.

Một phương pháp khác để chuyển đổi các giá bán trị súc tích thành những giá trị số là bội mảng cùng với 1:

=SUMPRODUCT((C2:C10Dù bằng cách nào, vị chỉ có một mảng trong phương pháp SUMPRODUCT vì thế nó chỉ đơn giản là cộng một 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 rất nhiều điều kiện

Khi một bí quyết SUMPRODUCT vào Excel đựng hai hoặc nhiều mảng, nó vẫn nhân các bộ phận của tất cả các mảng kế tiếp cộng lại kết quả.

Xem thêm: Các Biệt Danh Cho Người Yêu, Đặt Biệt Danh Hay Cho Người Yêu, Bạn Trai, Gái

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 sản phẩm thực (cột C) ít hơn doanh số chiến lược (cột B) cho Apples (cột A):

=SUMPRODUCT(--(C2:C10hoặc là

=SUMPRODUCT((C2:C10Sự biệt lập về công nghệ duy duy nhất giữa những công thức là cách thức ép TRUE cùng 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. Hiệu quả là mình nhận được hai mảng bao gồm một và 0:

Phép toán nhân được triển khai bởi SUMPRODUCT vẫn nối chúng thành một mảng duy nhất. Bởi vì nhân với 0 luôn cho hiệu quả = 0, 1 chỉ xuất hiện thêm khi cả hai đk được đáp ứng và cho nên vì thế chỉ hầu hết hàng đó mới được tính:

2. Đếm có điều kiện / tổng / ô trung bình với tương đố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ó rất nhiều tiêu / điều kiện.

Trong Excel 2003 và các phiên bạn dạng cũ hơn không tồn tại cái điện thoại tư vấn là hàm IF, ban đầu với Excel 2007 thì Microsoft đã trình làng một loạt các hàm có phong cách thiết kế đặc biệt cho các tác vụ như vậy: SUMIFS, COUNTIFS và AVERAGEIFS.

Trong những phiên bản Excel tiến bộ thì bí quyết SUMPRODUCT vẫn có thể thay thế các hàm mình vừa ra mắt bên trên, giúp tính tổng với đếm các ô có đk bằng ngắn gọn xúc tích OR. Dưới đó là một vài lấy ví dụ như về phương pháp hàm SUMPRODUCT trong thực tế.

Ví dụ 1. Bí quyết SUMPRODUCT với lô ghích AND

Giả sử các bạn có tập tài liệu sau, trong số ấy cột A liệt kê những khu vực, cột B là sản phẩm và cột C đựng số liệu cung cấp hàng:

Điều bạn muốn là giành được số lượng, tổng cùng trung bình doanh thu bán táo bị cắn dở (Apples) cho khoanh vùng phía bắc (North).

Trong những phiên bản gần phía trên của Excel 2016, 2013, 2010 cùng 2007, chúng ta cũng có thể dễ dàng triển khai tác vụ này bằng cách sử dụng phương pháp SUMIF, COUNTIF với AVERAGEIF. Nếu không tìm kiếm kiếm phần lớn cách dễ ợt hoặc chúng ta vẫn đang thực hiện Excel 2003, hãy thực hiện hàm SUMPRODUCT để có kết quả tương tự.

Để tính lợi nhuận bán táo khuyết (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 thích hợp táo bán ra 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 quý giá Average (trung bình) doanh số bán táo apple cho miền bắc, bọn họ chỉ phải chia Sum mang lại Count như sau:

=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12) / SUMPRODUCT( --(A2:A12="north"), --(B2:B12="apples"))Để tăng sự hoạt bát cho bí quyết SUMPRODUCT, chúng ta cũng có thể chỉ định Region và item vào các ô đơn nhất sau đó tham chiếu các ô đó trong công thức của công ty như được hiển thị trong hình ảnh chụp screen bên dưới:

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

Từ lấy ví dụ trước, bạn đã hiểu phương pháp công thức SUMPRODUCT trong Excel đếm những ô có rất nhiều điều kiện. Nếu khách hàng hiểu điều ấy thì đã rất dễ dàng hiểu được logic tổng.

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

=SUMPRODUCT(--(A2:A12="north"), --(B2:B12="apples"), C2:C12)Kết trái trung gian của bí quyết trên là 3 mảng sau:

Trong mảng vật dụng nhất, một là viết tắt của Bắc cùng 0 cho ngẫu nhiên vùng như thế nào khác.Trong mảng sản phẩm công nghệ hai, 1 là viết tắt của apple 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 mở ra trong những ô C2: C12.Hãy đừng quên nhân cùng với 0 luôn cho không và nhân với cùng 1 cho và một số, họ nhận được mảng cuối cùng bao gồm số bán hàng và số ko - số bán hàng chỉ xuất hiện thêm nếu hai mảng đầu tiên có 1 ở thuộc vị trí, tức là cả hai đáp ứng các điều kiện cụ thể.

Cộng các con số vào mảng trên có lại công dụng mong mong muốn - tổng lợi nhuận của táo apple ở khoanh vùng phía bắc.

Ví dụ 2. Phương pháp SUMPRODUCT với ngắn gọn xúc tích OR

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

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

Ví dụ: để tính tổng lợi nhuận bán táo bị cắn dở (Apples) và chanh (Lemons) bất kể khu vực nào, hãy áp dụng công thức sau:

=SUMPRODUCT((B2:B12="apples")+(B2:B12="lemons"))Có thể diễn giải cách làm như sau: Đếm ô trường hợp B2: B12 = "apples" HOẶC B2: B12 = "lemons".

Để tính tổng lợi nhuận táo với 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 screen sau cho thấy thêm một công thức tương tự đang hoạt động:

Ví dụ 3. Cách làm SUMPRODUCT với lô ghích AND cũng tương tự OR

Trong nhiều trường hợp, chúng ta cũng có thể cần đếm hoặc tính tổng bao gồm điều kiện những ô với logic AND và logic OR tại 1 thời điểm. Ngay cả trong số phiên bạn dạng Excel tiên tiến nhất thì chuỗi hàm IF cũng không có tác dụng đó.

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

Một bí quyết khác là sử dụng hàm SUMPRODUCT, vào đó:

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

Để khiến cho mọi thứ dễ nắm bắt hơn, các bạn hãy xem những ví dụ sau.

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

=Count If ((Region="north") and ((Item="Apples") OR (Item="Lemons")))Khi vận dụng cú pháp SUMPRODUCT ưng ý hợp, công thức tất cả dạng sau:

=SUMPRODUCT((A2:A12="north")*((B2:B12="apples")+(B2:B12="lemons")))Để tính tổng doanh số bán táo apple và chanh ở khu vực phía bắc (North), hãy lấy cách làm trên cùng thêm mảng Sales với xúc tích AND:

=SUMPRODUCT((A2:A12="north")*((B2:B12="apples")+(B2:B12="lemons"))*C2:C12)Để làm cho những công thức nhỏ tuổi gọn hơn một chút, bạn có thể nhập trở nên vào những ô riêng rẽ biệt:

Trong lấy một ví dụ này tôi đã nhập vùng (Region) tại ô F1 và thành tích trong F2 với H2.

3. Cách làm SUMPRODUCT tính trung bình có trọng số

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

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

SUMPRODUCT(values, weights) / SUM(weights)Trong kia values là giá trị còn weights là trọng số.

Giả sử rằng các giá trị phía trong ô B2: B7 với trọng số bên trong ô C2: C7, công thức SUMPRODUCT tính trung bình có trọng số sẽ y hệt như sau:

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

Mình tin rằng khi đọc mang lại phần này, các bạn sẽ không gặp ngẫu nhiên khó khăn nào nhằm hiểu súc tích công thức.

IV. Tổng kết

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

Đừng quên phân chia sẻ nội dung bài viết tới anh em và người cùng cơ quan khi họ không biết cách thực hiện hàm SUMPRODUCT vào Excel nha.