11 Ví Dụ Hay Về Hàm Sumproduct

3 CÁCH SỬ DỤNG HÀM SUMPRODUCT TRONG EXCEL – VÍ DỤ CÔNG THỨC:3.1 HÀM SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆN3.2 ĐẾM/TÍNH TỔNG/TÍNH TRUNG BÌNH CÓ ĐIỀU KIỆN CÁC Ô VỚI NHIỀU TIÊU CHUẨN

Bài giải đáp này giải thích biện pháp áp dụng cơ bản cùng nâng cấp của hàm SUMPRODUCT trong Excel. quý khách hàng đã thấy một vài ví dụ cách làm dùng làm đối chiếu mảng, tính tổng gồm điều kiện cùng đếm số ô có nhiều điều kiện, tính vừa phải cùng tất cả trọng lượng và chưa dừng lại ở đó nữa.

Bạn đang xem: 11 ví dụ hay về hàm sumproduct

Khi chúng ta nghe cái tên hàm SUMPRODUCT lần đầu tiên, nó có vẻ là một bí quyết bất lợi – bí quyết tính tổng thông thường của các tích số. Nhưng khái niệm này không cho thấy thậm chí là là chỉ một trong những phần nhỏ tuổi nhân kiệt của hàm SUMPRODUCT.

Thực ra, hàm SUMPRODUCT là hàm có khá nhiều bản lĩnh. Do khả năng đặc biệt quan trọng có thể cách xử lý mảng một cách sắc sảo cùng xuất sắc, bắt buộc hàm SUMPRODUCT cực kỳ hữu dụng, nếu không buộc phải, Lúc kể tới bài toán so sánh tài liệu sinh sống hai giỏi những dải ô và vấn đề tính toán dữ liệu có không ít ĐK. Những ví dụ dưới đây đã cho bạn thấy toàn thể khả năng của hàm SUMPRODUCT và sự hữu ích của chính nó sẽ mau chóng trngơi nghỉ đề xuất rõ ràng thôi.


*

HÀM SUMPRODUCT TRONG EXCEL – CÚ PHÁP.. VÀ CÔNG DỤNG:

Về khía cạnh kỹ thuật, hàm SUMPRODUCT trong Excel nhân các con số vào mảng xác định, rồi trả về tổng của những tích số kia.

Cú pháp của hàm SUMPRODUCT vô cùng solo giản:

SUMPRODUCT(array1, , , …)

Trong số đó, mảng 1, mảng 2, … là các dải ô liên tục xuất xắc là các mảng bao gồm đựng nguyên tố bạn muốn nhân, rồi cộng lại.

Số mảng về tối tphát âm là 1 trong những. Trong ngôi trường phù hợp này, hàm SUMPRODUCT chỉ đơn giản dễ dàng tính tổng các nguyên tố mảng rồi trả về tổng cộng.

Số mảng buổi tối đa là 255 trong Excel 2016, Excel 2013, Excel 2010, và Excel 2007, cùng 30 trong các phiên phiên bản trước của Excel.

Mặc cho dù hàm SUMPRODUCT giải pháp xử lý mảng, dẫu vậy nó không tận hưởng phím tắt mảng (Ctrl + Shift + Enter). quý khách hàng ngừng một cách làm SUMPRODUCT theo cách thường thì bẳng bí quyết dìm phím Enter.

Lưu ý:

Tất cả mảng vào cách làm SUMPRODUCT buộc phải tất cả thuộc số hàng với số cột , nếu không thì các bạn sẽ nhận lỗi #VALUE!.

Nếu bất cứ câu lệnh mảng nào chứa cực hiếm phi số, chúng sẽ được xử trí như số 0.

Nếu mảng là phnghiền thử lô gic, thì nó vẫn trả về quý hiếm TRUE cùng FALSE. Thông thường, bạn cần phải đổi những quý hiếm TRUE cùng FALSE này thành 1 với 0 bằng cách thực hiện toán thù tử solo phân (–). Hãy liếc qua ví dụ hàm SUMPRODUCT có rất nhiều ĐK để nắm vững rộng.

Hàm SUMPRODUCT không cung ứng cam kết tự đại diện thay mặt.

<Đăng ký>: Khóa học Excel nâng cấp Online

CÁCH DÙNG CƠ BẢN CỦA HÀM SUMPRODUCT TRONG EXCEL:

Để gồm tầm nhìn bao quát về phong thái hàm SUMPRODUCT hoạt động, hãy liếc qua ví dụ sau.

Giả sử các bạn tất cả con số sinh hoạt những ô A2:A4, và bạn có nhu cầu tính tổng. Nếu nhiều người đang giải một phép toán sống ngôi trường, thì bạn sẽ nhân con số với mức giá tài chánh từng thành phầm, rồi cùng chúng lại. Trong Microsoft Excel, chúng ta cũng có thể cảm nhận kết quả chưa đến một phương pháp SUMPRODUCT:

=SUMPRODUCT(A2:A4,B2:B4)

Ảnh chụp màn hình hiển thị dưới đây cho biết công thức trên thực tế:

Đây là chi tiết hầu như gì vẫn diễn ra về phương diện toán thù học:

Công thức chọn số thứ nhất trong mảng trước tiên rồi nhân nó mang đến số thứ nhất vào mảng trang bị hai, rồi đem số vật dụng nhị vào mảng thứ nhất nhân đến số đồ vật hai trong mảng vật dụng nhì rồi cứ tiếp tục như thế.

Khi vẫn nhân tất các yếu tắc mảng, bí quyết sẽ tính tổng những tích số rồi trả về toàn bô.

Nói giải pháp không giống, cách làm SUMPRODUCT trình diễn công thức toán thù học sau đây:

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

Hãy nghĩ về đến việc bạn cũng có thể tiết kiệm ngân sách và chi phí bao nhiều thời gian ví như bảng của người tiêu dùng ko chứa 3 sản phẩm tài liệu, nhưng là 3 trăm giỏi 3 nngu hàng!

CÁCH SỬ DỤNG HÀM SUMPRODUCT TRONG EXCEL – VÍ DỤ CÔNG THỨC:

Nhân nhị tốt nhiều dải ô với nhau rồi cùng những tích số là biện pháp cần sử dụng dễ dàng và đơn giản và cụ thể nhất của hàm SUBTOTAL trong Excel, tuy nhiên trên đây chưa hẳn là phương pháp sử dụng tốt nhất. Vẻ đẹp mắt thực thụ của hàm SUMPRODUCT trong Excel đó là nó hoàn toàn có thể làm nhiều hơn thế nữa những nhân tài đã được nêu rõ. Sâu rộng trong bài xích lí giải này, bạn sẽ thấy vài ba bí quyết cho thấy các cách cần sử dụng độc đáo cùng nâng cao rộng nhiều, chính vì thế hãy liên tục phát âm nội dung bài viết nhé!

HÀM SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆN

Đôi khi vào Microsoft Excel, luôn luôn có khá nhiều rộng một cách để xong xuôi quá trình. Nhưng lúc nói tới câu hỏi so sánh nhị tốt những mảng, nhất là có tương đối nhiều điều kiện, nếu không nói là độc nhất vô nhị, hàm SUMPRODUCT là hàm hiệu quả độc nhất. À thì, hàm SUMPRODUCT tuyệt phương pháp mảng đa số được.

Giả sử bạn tất cả list những món hàng sinh hoạt cột A, doanh số buôn bán dự loài kiến ngơi nghỉ cột B, với doanh số phân phối thực sinh hoạt cột C. Mục tiêu của khách hàng là search xem gồm từng nào tất cả từng nào món cung cấp ít hơn dự loài kiến. Đối với ngôi trường hợp này, hãy áp dụng một Một trong những thay đổi phân của bí quyết SUMPRODUCT:

=SUMPRODUCT(–(C2:C10CÁCH CÔNG THỨC SUMPRODUCT CÓ MỘT ĐIỀU KIỆN HOẠT ĐỘNG:

Đối với những người mới bắt đầu, hãy phân chia nhỏ dại công thức dễ dàng và đơn giản hơn – bí quyết so sánh những con số trong nhì cột hàng theo mặt hàng, và nói đến chúng ta biết tần số cột C ít hơn cột B:

=SUMPRODUCT(–(C2:C10CÁCH CÔNG THỨC SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆN HOẠT ĐỘNG:

lúc cách làm SUMPRODUCT vào Excel đựng hai hay nhiều mảng, nó nhân những nguyên tố của mảng, rồi cộng những tích số.

Có lẽ các bạn vẫn còn lưu giữ, họ vẫn áp dụng bí quyết sau đây để đếm chu kỳ lợi nhuận thực (cột C) ít hơn doanh thu dự con kiến (cột B) so với Táo (cột A):

=SUMPRODUCT(–(C2:C10ĐẾM/TÍNH TỔNG/TÍNH TRUNG BÌNH CÓ ĐIỀU KIỆN CÁC Ô VỚI NHIỀU TIÊU CHUẨN

Trong Excel 2003 và những phiên phiên bản trước thì không tồn tại hàm IFS, một trong số những bí quyết dùng phổ cập tốt nhất của hàm SUMPRODUCT chính là tính tổng tuyệt đếm gồm điều kiện các ô với khá nhiều tiêu chuẩn. Bắt đầu với Excel 2007, Microsoft trình làng một chuỗi hàm được đặc biệt quan trọng kiến tạo mang đến các trách nhiệm này – hàm SUMIFS, hàm COUNTIFS với hàm AVERAGEIFS.

Nhưng thậm chí là nghỉ ngơi các phiên bạn dạng văn minh của Excel, phương pháp SUMPRODUCT trọn vẹn hoàn toàn có thể là một tuyển lựa sửa chữa, ví dụ, tính tổng và đếm có ĐK các ô gồm hàm lôgic OR. Dưới phía trên, các bạn sẽ thấy vài ba ví dụ cách làm cho thấy tài năng này.

Xem thêm: Mua Thảm Cỏ Nhân Tạo Sân Vườn Hà Nội, Cỏ Nhân Tạo Giá Rẻ Nhất Tại Hà Nội

CÔNG THỨC SUMPRODUCT VỚI HÀM LÔGIC AND:

Giả sử bạn gồm chuỗi tài liệu sau đây, trong những số ấy cột A liệt kê vùng miền, cột B – món mặt hàng với cột C – lợi nhuận chào bán ra:

Điều bạn muốn thực hiện chính là đếm, tính tổng với tính trung bình cộng doanh số táo bị cắn dở xuất kho làm việc Khu Vực phía Bắc.

Trong những phiên bản Excel ngay gần đây: Excel năm nhâm thìn, 2013, 2010 cùng 2007, bạn cũng có thể thuận tiện hoàn thành trách nhiệm bằng cách thực hiện phương pháp SUMIFS, COUNTIFS cùng AVERAGEIFS. Nếu nhiều người đang tìm 1 hướng đi trở ngại rộng, tốt nếu bạn vẫn áp dụng Excel 2003 tuyệt phiên bạn dạng cũ rộng, thì chúng ta vẫn giành được tác dụng mong ước cùng với hàm SUMPRODUCT.

Để đếm doanh số apple đẩy ra sống Quanh Vùng phía Bắc:

=SUMPRODUCT(–(A2:A12=”khoanh vùng phía Bắc”), –(B2:B12=”táo”))

hay

=SUMPRODUCT((A2:A12=”khoanh vùng phía Bắc”)*(B2:B12=”táo”))

Để tính tổng doanh số táo khuyết đẩy ra sinh sống Khu Vực phía Bắc:

=SUMPRODUCT(–(A2:A12=”Quanh Vùng phía Bắc”), –(B2:B12=”táo”), C2:C12)

hay

=SUMPRODUCT((A2:A12=”Quanh Vùng phía Bắc”)*(B2:B12=”táo”)*C2:C12)

Để tính vừa phải cộng doanh thu táo khuyết đẩy ra sinh sống Khu Vực phía Bắc:

Để tính vừa phải cộng, bọn họ chỉ phân chia tổng chất nhận được đếm như vậy này:

=SUMPRODUCT(–(A2:A12=”Quanh Vùng phía Bắc”), –(B2:B12=”táo”), C2:C12) / SUMPRODUCT( –(A2:A12=”khu vực phía Bắc”), –(B2:B12=”táo”))

Để công thức SUMPRODUCT linh hoạt hơn, bạn có thể định rõ Quanh Vùng và món mặt hàng ước muốn nghỉ ngơi những ô riêng biệt, rồi tmê mệt chiếu những ô đó vào công thức hệt như hình ảnh chụp màn hình hiển thị bên dưới đây:

CÔNG THỨC SUMPRODUCT DÙNG CHO VIỆC TÍNH TỔNG CÓ ĐIỀU KIỆN HOẠT ĐỘNG NHƯ THẾ NÀO

Từ ví dụ trước, chúng ta đã biết cách công thức SUMPRODUCT vào Excel đếm số ô cùng rất hiều ĐK. Nếu các bạn đang hiểu rõ từ thời điểm cách đây, sẽ rất dễ dàng nhằm chúng ta nắm vững quy luật tính tổng.

Để tôi nhắc chúng ta nhớ là bọn họ vẫn dùng bí quyết dưới đây nhằm tính tổng doanh số Táo nghỉ ngơi khoanh vùng phía Bắc:

=SUMPRODUCT(–(A2:A12=”Quanh Vùng phía Bắc”), –(B2:B12=”táo”), C2:C12)

Công thức thẳng của phương pháp bên trên chính là 3 mảng bên dưới đây:

*

Trong mảng thứ nhất, 1 đại diện mang lại Quanh Vùng phía Bắc, cùng 0 đại diện cho những Quanh Vùng khác.

Trong mảng thiết bị nhì, 1 thay mặt đến Táo, và 0 thay mặt cho các món mặt hàng khác.

Mảng thứ cha đựng thiết yếu những con số lộ diện tự ô C2 mang đến C12.

Hãy hãy nhớ là nhân 0 thì luôn bằng 0, với nhân 1 thì bằng chính nó, họ bao gồm mảng cuối cùng cất lợi nhuận với gần như số lượng 0 – lợi nhuận chỉ xuất hiện thêm trường hợp nhì mảng đầu tiên bao gồm tiên phong hàng đầu sinh sống và một địa chỉ, ví dụ là cả nhì ĐK xác minh phần lớn được đáp ứng; còn ko thì đã hiển thị số 0:

Cộng những số lượng trong mảng bên trên đến tác dụng ước muốn – doanh số Táo bán ra sinh sống Khu Vực phía Bắc.

VÍ DỤ 2. CÔNG THỨC SUMPRODUCT VỚI HÀM LÔGIC OR

Để công tốt đếm tất cả điều kiện những ô sử dụng hàm lôgic OR, hãy thực hiện lốt cộng (+) giữa những mảng.

Trong công thức Excel SUMPRODUCT, cũng giống như trong cách làm mảng, vệt cộng vào vai trò làm cho toán tử OR giải đáp Excel trả về quý hiếm TRUE nếu như ngẫu nhiên ĐK làm sao vào biểu thức xác minh được thỏa mãn nhu cầu.

lấy ví dụ như, để đếm tổng doanh thu Táo và Ckhô cứng không phân minh vùng miền, hãy sử dụng công thức này:

=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”))

Nói đơn giản, phương pháp bên trên bao gồm nghĩa là: Hãy đếm số ô nếu dải ô B2:B12=”táo” OR B2:B12=”chanh”.

Để tính tổng doanh thu Táo cùng Ckhô hanh, hãy thêm một câu lệnh đựng dải ô Doanh số:

=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”), C2:C12)

Hình ảnh chụp màn hình hiển thị tiếp sau đây diễn giải phương pháp tương tự:

VÍ DỤ 3. CÔNG THỨC SUMPRODUCT CÙNG VỚI HÀM LÔGIC AND VÀ HÀM LÔGIC OR

Trong những ngôi trường hòa hợp, có thể bạn cần phải đếm tốt tính tổng tất cả điều kiện sử dụng cả hàm lôgic AND với OR. Thậm chí làm việc phiên phiên bản Excel tiên tiến nhất , chuỗi hàm tương quan đến hàm IF cũng không thể làm cho được điều đó.

giữa những chiến thuật khả thi chính là phối kết hợp nhị tuyệt các hàm SUMIFS+SUMIFS giỏi COUNTIFS+COUNTIFS.

Cách khác sẽ là thực hiện hàm SUMPRODUCT trong đó:

Dấu hoa thị (*) được sử dụng nhỏng tân oán tử AND.

Dấu cộng (+) được dùng như toán tử OR.

Để khiến đông đảo vật dụng dễ dàng rộng, hãy chú ý những ví dụ bên dưới đây:

Để đếm mốc giới hạn Táo cùng Ckhô nóng được buôn bán nghỉ ngơi Khu Vực phía Bắc, hãy lập bí quyết cùng với hàm lôgic sau:

=Count If ((Vùng miền=”miền Bắc”) AND ((Món hàng=”Táo”) OR (Món hàng=”Chanh”)))

Lúc vừa sử dụng theo cú pháp hàm SUMPRODUCT, công thức sẽ có được dạng như sau:

=SUMPRODUCT((A2:A12=”miền Bắc”)*((B2:B12=”táo”)+(B2:B12=”chanh”)))

Để tính tổng doanh số Táo với Chanh đẩy ra sinh sống khu vục phía Bắc, hãy dùng phương pháp trên rồi thêm dải ô Doanh số cùng với hàm lôgic AND:

=SUMPRODUCT((A2:A12=”miền Bắc”)*((B2:B12=”táo”)+(B2:B12=”chanh”))*C2:C12)

Để công thức trông gọn rộng, chúng ta cũng có thể gõ các biến chuyển vào các ô lẻ tẻ – Khu vực sinh hoạt ô F1 cùng Món mặt hàng sinh hoạt ô F2 và H2 – rồi tyêu thích chiếu những ô này vào công thức:

CÔNG THỨC SUMPRODUCT DÙNG ĐỂ TÍNH TRUNG BÌNH CÓ TRỌNG LƯỢNG

Tại một giữa những ví dụ trước, họ vẫn bàn về bí quyết SUMPRODUCT dùng để tính vừa đủ có ĐK. Cách cần sử dụng phổ biến không giống của hàm SUMPRODUCT trong Excel sẽ là tính vừa phải bao gồm trọng lượng trong các số ấy từng quý hiếm bao gồm một trọng lượng nhất định.

Công thức SUMPRODUCT tính vừa đủ bao gồm trọng lượng tổng thể bao gồm dạng nlỗi sau:

SUMPRODUCT(valuesweights) / SUM(weights)

Giả sử những giá trị nằm ở dải ô B2:B7 và trọng lượng sinh sống dải ô C2:C7, cách làm SUMPRODUCT tính mức độ vừa phải gồm trọng lượng gồm dạng nlỗi sau:

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

Tôi tin rằng ngay lập tức hiện giờ, bạn sẽ không gặp trở ngại vào vấn đề phát âm quy giải pháp phương pháp nữa.

HÀM SUMPRODUCT ĐƯỢC DÙNG LÀM HÀM THAY THẾ CHO CÔNG THỨC MẢNG

mặc khi khi chúng ta phát âm nội dung bài viết này chỉ để có thêm thông tin và chúng ta không hề lưu giữ rõ những cụ thể nữa, hãy lưu giữ một điểm cốt lõi quan trọng đặc biệt – hàm SUMPRODUCT vào Excel cách xử trí mảng. Và vị hàm SUMPRODUCT tất cả các năng lực của phương pháp mảng, vì thế nó hoàn toàn có thể biến hóa hàm sửa chữa thay thế dễ thực hiện mang lại cách làm mảng.

Ưu điểm là gì? Về phương diện cơ bản, bạn có công dụng thống trị công thức một phương pháp dễ dãi nhưng không cần phải nhấp Ctrl + Shift + Enter những lần chúng ta nhập một cách làm mảng mới tốt sửa đổi công thức mảng.

ví dụ như, bạn cũng có thể áp dụng một công thức mảng đơn giản nhằm đếm số cam kết từ bỏ trong dải ô xác định:

=SUM(LEN(range))

và biến chuyển nó thành công thức thông thường:

=SUMPRODUCT(LEN(range))

Để thực hành thực tế, chúng ta cũng có thể lựa chọn những cách làm mảng này vào Excel rồi cố gắng viết lại áp dụng hàm SUMPRODUCT.

Để hoàn toàn có thể vận dụng giỏi Excel vào trong các bước, bọn họ không chỉ có nắm rõ được những hàm hơn nữa nên áp dụng tốt cả những cơ chế của Excel. Những hàm cải thiện giúp vận dụng giỏi vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những mức sử dụng thường xuyên áp dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn các có thể học được vào khóa huấn luyện và đào tạo Excel tự cơ bản mang lại chuyên gia dành cho tất cả những người đi làm