Hàm Sumproduct Để Làm Gì

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 khuyên bảo này của Học Excel Online đã giải thích giải pháp thực hiện cơ bản với cải thiện của hàm SUMPRODUCT trong Excel. Quý khách hàng đang thấy một vài ba ví dụ công thức dùng để làm đối chiếu mảng, tính tổng gồm điều kiện với đếm số ô có không ít ĐK, tính mức độ 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: Hàm sumproduct để làm gì

Lúc chúng ta nghe cái brand name hàm SUMPRODUCT lần thứ nhất, nó có vẻ như là 1 phương pháp ăn hại – công thức tính tổng thông thường của những tích số. Nhưng tư tưởng này sẽ không cho biết thêm thậm chí còn là chỉ một trong những phần nhỏ dại tuấn kiệt của hàm SUMPRODUCT.

Thực ra, hàm SUMPRODUCT trong Excel là hàm có nhiều kỹ năng. Do khả năng quan trọng đặc biệt hoàn toàn có thể cách xử trí mảng một bí quyết sắc sảo cùng xuất sắc, nên hàm SUMPRODUCT trong Excel cực kỳ có ích, còn nếu không buộc phải, Lúc nói đến vấn đề đối chiếu tài liệu ở hai tuyệt nhiều dải ô và bài toán tính toán thù tài liệu có tương đối nhiều ĐK. Những ví dụ dưới đây vẫn cho mình thấy tổng thể tài năng của hàm SUMPRODUCT và sự hữu dụng của nó sẽ nhanh chóng trsinh sống phải rõ ràng thôi.


*

HÀM SUMPRODUCT TRONG EXCEL – CÚ PHÁPhường VÀ CÔNG DỤNG:

Về phương diện chuyên môn, hàm SUMPRODUCT vào Excel nhân những số lượng vào mảng khẳng định, rồi trả về tổng của những tích số đó.

Cú pháp của hàm SUMPRODUCT siêu đối kháng giản:

SUMPRODUCT(array1, , , …)

Trong đó, mảng 1, mảng 2, … là các dải ô thường xuyên tốt là những mảng có đựng yếu tắc bạn có nhu cầu nhân, rồi cùng lại.

Số mảng tối tphát âm là một trong. Trong trường đúng theo này, hàm SUMPRODUCT vào Excel chỉ dễ dàng và đơn giản tính tổng các nguyên tố mảng rồi trả về toàn bô.

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

Mặc mặc dù hàm SUMPRODUCT giải pháp xử lý mảng, nhưng mà nó không trải nghiệm phím tắt mảng (Ctrl + Shift + Enter). quý khách ngừng một phương pháp SUMPRODUCT Theo phong cách thường thì bẳng cách thừa nhận phím Enter.

Lưu ý:

Tất cả mảng vào phương pháp SUMPRODUCT phải bao gồm thuộc số hàng và số cột , còn nếu không thì bạn sẽ dìm lỗi #VALUE!.

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

Nếu mảng là phxay demo lô gic, thì nó đang trả về quý giá TRUE với FALSE. thường thì, bạn cần phải thay đổi các cực hiếm TRUE với FALSE này thành 1 với 0 bằng cách sử dụng toán thù tử 1-1 phân (–). Hãy nhìn qua ví dụ hàm SUMPRODUCT có không ít điều kiện để nắm rõ rộng.

Hàm SUMPRODUCT ko cung ứng ký kết từ bỏ đạ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:

Để bao gồm ánh nhìn bao quát về kiểu cách hàm SUMPRODUCT trong Excel chuyển động, hãy liếc qua ví dụ sau.

Giả sử chúng ta tất cả số lượng làm việc những ô A2:A4, và bạn muốn tính tổng. Nếu bạn đang giải một phnghiền toán thù sống trường, thì bạn sẽ nhân con số với cái giá tiền tài mỗi thành phầm, rồi cộng bọn 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 công thức SUMPRODUCT:

=SUMPRODUCT(A2:A4,B2:B4)

Ảnh chụp màn hình hiển thị tiếp sau đây cho biết bí quyết trên thực tế:

Đây là chi tiết các gì vẫn ra mắt về mặt toán học:

Công thức chọn số trước tiên trong mảng trước tiên rồi nhân nó cho số đầu tiên trong mảng đồ vật hai, rồi rước số lắp thêm nhì vào mảng thứ nhất nhân mang đến số thứ nhì trong mảng thứ nhì rồi cđọng liên tiếp như thế.

lúc sẽ nhân vớ các nguyên tố mảng, công thức và tính tổng các tích số rồi trả về tổng cộng.

Nói giải pháp không giống, phương pháp SUMPRODUCT trình diễn bí quyết toán học sau đây:

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

Hãy nghĩ tới sự việc bạn có thể tiết kiệm chi phí bao nhiều thời hạn ví như bảng của bạn không chứa 3 hàng tài liệu, nhưng là 3 trăm giỏi 3 nlẩn thẩn hàng!

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

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

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

thường thì vào Microsoft Excel, luôn có khá nhiều hơn một cách để hoàn thành quá trình. Nhưng Lúc nói đến vấn đề đối chiếu nhì giỏi các mảng, đặc biệt là có không ít điều kiện, còn nếu như không nói là nhất, hàm SUMPRODUCT vào Excel là hàm kết quả độc nhất vô nhị. À thì, hàm SUMPRODUCT tốt công thức mảng các được.

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

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

Đối với người bắt đầu bắt đầu, hãy chia bé dại cách làm dễ dàng rộng – cách làm đối chiếu những con số trong nhì cột sản phẩm theo mặt hàng, và nói đến chúng ta biết chu kỳ 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:

khi phương pháp SUMPRODUCT vào Excel chứa hai xuất xắc nhiều mảng, nó nhân những yếu tố của mảng, rồi cùng các tích số.

Có lẽ các bạn vẫn tồn tại lưu giữ, họ sẽ áp dụng cách làm tiếp sau đây để đếm mốc giới hạn lợi nhuận thực (cột C) thấp hơn doanh số dự loài kiến (cột B) đối 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à các phiên bạn dạng trước thì không có hàm IFS, một trong những bí quyết sử dụng phổ biến độc nhất vô nhị của hàm SUMPRODUCT chính là tính tổng tốt đếm tất cả ĐK các ô với tương đối nhiều tiêu chuẩn. Bắt đầu với Excel 2007, Microsoft giới thiệu một chuỗi hàm được quan trọng kiến tạo cho các trách nhiệm này – hàm SUMIFS, hàm COUNTIFS và hàm AVERAGEIFS.

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

Xem thêm: Auralog Tell Me More English Download Free Full Version, Tell Me More V 10 English

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

Giả sử chúng ta gồm chuỗi dữ liệu dưới đây, trong số ấy cột A liệt kê vùng miền, cột B – món hàng và cột C – doanh số cung cấp ra:

Điều bạn muốn tiến hành sẽ là đếm, tính tổng cùng tính trung bình cùng doanh số táo bị cắn dở xuất kho ngơi nghỉ Khu Vực phía Bắc.

Trong các phiên bạn dạng Excel ngay gần đây: Excel năm nhâm thìn, 2013, 2010 và 2007, chúng ta có thể thuận tiện chấm dứt trọng trách bằng cách áp dụng phương pháp SUMIFS, COUNTIFS cùng AVERAGEIFS. Nếu bạn đang search một hướng đi trở ngại hơn, xuất xắc nếu bạn vẫn áp dụng Excel 2003 tốt phiên phiên bản cũ rộng, thì các bạn vẫn đã đạt được kết quả ước muốn với hàm SUMPRODUCT trong Excel.

Để đếm doanh số táo Apple bán ra nghỉ ngơi Khu Vực phía Bắc:

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

hay

=SUMPRODUCT((A2:A12=”Khu Vực phía Bắc”)*(B2:B12=”táo”))

Để tính tổng doanh thu táo bị cắn bán ra sống khu vực phía Bắc:

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

hay

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

Để tính trung bình cộng doanh số apple đẩy ra làm việc Khu Vực phía Bắc:

Để tính vừa phải cùng, họ chỉ chia tổng được cho phép đếm như vậy này:

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

Để phương pháp SUMPRODUCT linc hoạt hơn, bạn có thể định rõ khoanh vùng cùng món mặt hàng ước muốn làm việc các ô riêng, rồi tsay mê chiếu các ô đó vào phương pháp 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 đã hiểu cách thức phương pháp SUMPRODUCT vào Excel đếm số ô với hiều ĐK. Nếu các bạn đang hiểu rõ cách đây, sẽ tương đối tiện lợi để bạn nắm vững quy dụng cụ tính tổng.

Để tôi kể bạn đừng quên bọn họ vẫn sử dụng phương pháp dưới đây để tính tổng lợi nhuận Táo ngơi nghỉ khu vực phía Bắc:

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

Công thức trực tiếp của công thức bên trên đó là 3 mảng dưới đây:

*

Trong mảng đầu tiên, 1 đại diện cho khu vực phía Bắc, với 0 thay mặt cho những khoanh vùng khác.

Trong mảng trang bị hai, 1 đại diện thay mặt đến Táo, với 0 đại diện thay mặt cho các món mặt hàng khác.

Mảng thiết bị tía cất thiết yếu những số lượng xuất hiện từ ô C2 cho C12.

Hãy hãy nhờ rằng nhân 0 thì luôn bởi 0, với nhân 1 thì bằng thiết yếu nó, chúng ta có mảng sau cuối cất doanh thu với hầu hết số lượng 0 – doanh thu chỉ lộ diện ví như nhị mảng đầu tiên bao gồm tiên phong hàng đầu nghỉ ngơi cùng một địa chỉ, ví dụ là cả hai ĐK khẳng định đều được đáp ứng; còn không thì đang hiển thị số 0:

Cộng những con số vào mảng bên trên mang đến tác dụng ước muốn – lợi nhuận Táo bán ra sinh hoạt Khu Vực phía Bắc.

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

Để công hay đếm bao gồm điều kiện những ô sử dụng hàm lôgic OR, hãy áp dụng lốt cùng (+) giữa những mảng.

Trong cách làm Excel SUMPRODUCT, cũng tương tự vào bí quyết mảng, vết cùng vào vai trò làm cho toán tử OR chỉ dẫn Excel trả về quý giá TRUE trường hợp bất kỳ ĐK làm sao trong biểu thức khẳng định được thỏa mãn nhu cầu.

lấy ví dụ, để đếm tổng lợi nhuận Táo và Chanh khô ko phân biệt vùng miền, hãy thực hiện bí quyết này:

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

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

Để tính tổng doanh số Táo với Ckhô hanh, hãy thêm 1 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 sau đây diễn giải bí quyết 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 nhiều trường phù hợp, hoàn toàn có thể bạn cần phải đếm xuất xắc tính tổng bao gồm điều kiện thực hiện cả hàm lôgic AND với OR. Thậm chí sinh hoạt phiên bạn dạng Excel tiên tiến nhất , chuỗi hàm tương quan đến hàm IF cũng cấp thiết có tác dụng được điều ấy.

trong số những chiến thuật khả thi chính là phối hợp hai xuất xắc những hàm SUMIFS+SUMIFS xuất xắc COUNTIFS+COUNTIFS.

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

Dấu hoa thị (*) được dùng nhỏng toán tử AND.

Dấu cùng (+) được sử dụng nlỗi toán tử OR.

Để khiến số đông trang bị dễ dàng rộng, hãy để ý những ví dụ dưới đây:

Để đếm tần số Táo và Ckhô giòn được chào bán sinh sống khu vực phía Bắc, hãy lập bí quyết 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”)))

Khi vừa thực hiện theo cú pháp hàm SUMPRODUCT, phương pháp sẽ sở hữu dạng nhỏng sau:

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

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

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

Để phương pháp trông gọn gàng hơn, bạn cũng có thể gõ các đổi thay vào những ô đơn lẻ – Khu vực sống ô F1 với Món mặt hàng nghỉ ngơi ô F2 cùng H2 – rồi tđê mê 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

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

Công thức SUMPRODUCT tính mức độ vừa phải tất cả trọng lượng tổng thể tất cả dạng nhỏng sau:

SUMPRODUCT(valuesweights) / SUM(weights)

Giả sử những quý giá nằm ở vị trí dải ô B2:B7 với trọng lượng sống dải ô C2:C7, phương pháp SUMPRODUCT tính trung bình gồm trọng lượng gồm dạng nhỏng sau:

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

Tôi tin tưởng rằng ngay hiện giờ, bạn sẽ không chạm chán khó khăn trong Việc phát âm quy phương pháp cách làm nữa.

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

ngay khi khi bạn phát âm bài viết này chỉ để sở hữu thêm ban bố cùng các bạn không thể ghi nhớ rõ các chi tiết nữa, hãy lưu giữ một điểm cốt yếu quan trọng đặc biệt – hàm SUMPRODUCT trong Excel cách xử trí mảng. Và vị hàm SUMPRODUCT có những kĩ năng của công thức mảng, cho nên nó có thể vươn lên là hàm sửa chữa thay thế dễ áp dụng mang lại cách làm mảng.

Ưu điểm là gì? Về mặt cơ phiên bản, các bạn có khả năng cai quản công thức một bí quyết dễ dãi nhưng mà không cần thiết phải nhấp Ctrl + Shift + Enter mỗi lần bạn nhập một bí quyết mảng bắt đầu tốt sửa đổi phương pháp mảng.

lấy ví dụ, chúng ta có thể áp dụng một bí quyết mảng dễ dàng để đếm số ký trường đoản cú vào dải ô xác định:

=SUM(LEN(range))

và biến chuyển nó thành công xuất sắc thức thông thường:

=SUMPRODUCT(LEN(range))

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

Để có thể vận dụng tốt Excel vào trong quá trình, họ không chỉ có nắm vững được các hàm Hơn nữa buộc phải thực hiện giỏi cả những quy định của Excel. Những hàm nâng cao góp vận dụng giỏi vào quá trình nhỏng SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những hiện tượng thường sử dụng là Data validation, Conditional formatting, Pivot table…

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