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 gợi ý này của Học Excel Online đang phân tích và lý giải giải pháp thực hiện cơ bạn dạng cùng cải thiện của hàm SUMPRODUCT vào Excel. Quý khách hàng sẽ thấy một vài ba ví dụ phương pháp dùng để đối chiếu mảng, tính tổng tất cả điều kiện cùng đếm số ô có nhiều điều kiện, tính trung bình cùng bao gồm trọng lượng và không dừng lại ở đó nữa.

Bạn đang xem: Hướng dẫn sử dụng hàm sumproduct

khi các bạn nghe cái brand name hàm SUMPRODUCT lần thứ nhất, nó có vẻ như là một trong những bí quyết vô ích – cách làm tính tổng thông thường của các tích số. Nhưng tư tưởng này sẽ không cho biết thậm chí là chỉ một phần nhỏ tuổi công dụng của hàm SUMPRODUCT.

Thực ra, hàm SUMPRODUCT trong Excel là hàm có tương đối nhiều nhân kiệt. Do khả năng đặc trưng rất có thể giải pháp xử lý mảng một phương pháp tinh tế với hoàn hảo, nên hàm SUMPRODUCT trong Excel cực kỳ bổ ích, còn nếu như không cần, Lúc kể tới Việc so sánh tài liệu sinh sống nhị tuyệt nhiều dải ô cùng câu hỏi tính tân oán tài liệu có không ít điều kiện. Những ví dụ sau đây sẽ cho chính mình thấy tổng thể tài năng của hàm SUMPRODUCT với sự có lợi của chính nó đang sớm trnghỉ ngơi bắt buộc rõ ràng thôi.


*

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

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

Cú pháp của hàm SUMPRODUCT hết sức đối chọi giản:

SUMPRODUCT(array1, , , …)

Trong số đó, mảng 1, mảng 2, … là những dải ô liên tiếp xuất xắc là những mảng gồm cất thành phần bạn có nhu cầu nhân, rồi cộng lại.

Số mảng về tối tđọc là một. Trong ngôi trường thích hợp này, hàm SUMPRODUCT trong Excel chỉ đơn giản dễ dàng tính tổng những yếu tắc mảng rồi trả về tổng số.

Số mảng về tối đa là 255 trong Excel năm nhâm thìn, Excel 2013, Excel 2010, với 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 cách xử lý mảng, tuy vậy nó ko đề xuất phím tắt mảng (Ctrl + Shift + Enter). Quý khách hàng xong một công thức SUMPRODUCT theo cách thường thì bẳng phương pháp dấn phím Enter.

Lưu ý:

Tất cả mảng trong công thức SUMPRODUCT đề xuất có cùng số hàng và số cột , nếu như không thì các bạn sẽ dấn lỗi #VALUE!.

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

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

Hàm SUMPRODUCT không cung ứng ký kết từ bỏ đại diện.

<Đă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:

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

Giả sử chúng ta có con số nghỉ ngơi những ô A2:A4, với 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 sinh hoạt trường, thì các bạn sẽ nhân con số với mức giá tiền của từng sản phẩm, rồi cộng chúng lại. Trong Microsoft Excel, chúng ta có thể nhận thấy tác dụng chỉ cách một phương pháp SUMPRODUCT:

=SUMPRODUCT(A2:A4,B2:B4)

Ảnh chụp màn hình sau đây cho biết công thức trên thực tế:

Đây là cụ thể hồ hết gì đã ra mắt về khía cạnh tân oán học:

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

lúc vẫn nhân vớ các yếu tố mảng, cách làm công thêm tổng các tích số rồi trả về tổng số.

Nói cách khác, cách làm SUMPRODUCT màn biểu diễn cách làm tân oán học tập sau đây:

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

Hãy nghĩ về đến việc bạn có thể tiết kiệm chi phí bao nhiều thời gian nếu bảng của công ty ko đựng 3 hàng tài liệu, nhưng là 3 trăm tuyệt 3 nngớ ngẩn hàng!

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

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

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

thường thì trong Microsoft Excel, luôn có khá nhiều rộng một phương pháp để xong xuôi quá trình. Nhưng Khi nói đến việc đối chiếu hai tuyệt các mảng, đặc biệt là có rất nhiều ĐK, nếu không nói là duy nhất, hàm SUMPRODUCT trong Excel là hàm kết quả độc nhất. À thì, hàm SUMPRODUCT tuyệt cách làm mảng phần lớn được.

Giả sử bạn bao gồm list những món mặt hàng làm việc cột A, doanh thu chào bán dự loài kiến ngơi nghỉ cột B, cùng doanh thu chào bán thực nghỉ ngơi cột C. Mục tiêu của chúng ta là kiếm tìm xem có từng nào có bao nhiêu món buôn bán ít hơn dự kiến. Đối với trường vừa lòng này, hãy áp dụng một trong những phát triển thành phân của phương pháp 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 bắt đầu bước đầu, hãy phân tách nhỏ dại bí quyết đơn giản và dễ dàng rộng – bí quyết đối chiếu các con số vào nhì cột mặt hàng theo sản phẩm, với nói cho họ 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 bí quyết SUMPRODUCT vào Excel cất hai giỏi nhiều mảng, nó nhân những nhân tố của mảng, rồi cộng các tích số.

Có lẽ bạn vẫn còn đấy nhớ, chúng ta đang sử dụng cách làm tiếp sau đây nhằm đếm số lần lợi nhuận thực (cột C) ít hơn doanh thu dự 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ới 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 biện pháp cần sử dụng thường dùng duy nhất của hàm SUMPRODUCT chính là tính tổng tuyệt đếm có ĐK các ô với rất nhiều tiêu chuẩn chỉnh. Bắt đầu với Excel 2007, Microsoft trình làng một chuỗi hàm được quan trọng đặc biệt kiến thiết mang đến phần lớn nhiệm vụ này – hàm SUMIFS, hàm COUNTIFS và hàm AVERAGEIFS.

Nhưng thậm chí còn sinh hoạt các phiên phiên bản tân tiến của Excel, phương pháp SUMPRODUCT trọn vẹn có thể là 1 trong những tuyển lựa sửa chữa thay thế, ví dụ, tính tổng với đếm bao gồm điều kiện các ô bao gồm hàm lôgic OR. Dưới đây, các bạn sẽ thấy vài ví dụ công thức cho thấy kĩ năng này.

Xem thêm: Hướng Dẫn Dynasty Warriors 8 Xtreme Legends Từ A Đến Z

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

Giả sử bạn tất cả chuỗi dữ liệu dưới đây, trong các số ấy cột A liệt kê vùng miền, cột B – món sản phẩm với cột C – doanh thu chào bán ra:

Điều bạn muốn tiến hành đó là đếm, tính tổng và tính vừa phải cộng doanh số táo bị cắn dở xuất kho sinh sống Quanh Vùng phía Bắc.

Trong các phiên phiên bản Excel ngay sát đây: Excel 2016, 2013, 2010 và 2007, bạn có thể dễ ợt chấm dứt trọng trách bằng phương pháp thực hiện phương pháp SUMIFS, COUNTIFS cùng AVERAGEIFS. Nếu ai đang tìm kiếm một hướng đi trở ngại rộng, xuất xắc nếu khách hàng vẫn sử dụng Excel 2003 xuất xắc phiên bản cũ hơn, thì chúng ta vẫn có được hiệu quả ước muốn với hàm SUMPRODUCT trong Excel.

Để đếm lợi nhuận táo Apple đẩy ra sống Khu Vực phía Bắc:

=SUMPRODUCT(–(A2:A12=”Quanh Vùng 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 đẩy ra ngơi nghỉ khu vực phía Bắc:

=SUMPRODUCT(–(A2:A12=”khoanh 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 mức độ vừa phải cùng doanh thu hãng apple xuất kho ở khoanh vùng phía Bắc:

Để tính mức độ vừa phải cùng, chúng ta chỉ chia tổng có thể chấp nhận được đếm như thế này:

=SUMPRODUCT(–(A2:A12=”khoanh 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ách làm SUMPRODUCT linch hoạt rộng, chúng ta cũng có thể xác định rõ Quanh Vùng và món sản phẩm mong muốn làm việc những ô riêng biệt, rồi tham mê chiếu những ô kia vào phương pháp hệt như hình ảnh chụp screen 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, các bạn đã biết cách công thức SUMPRODUCT trong Excel đếm số ô cùng với hiều điều kiện. Nếu các bạn đang nắm rõ cách đó, sẽ tương đối dễ ợt để chúng ta nắm rõ quy luật tính tổng.

Để tôi nói chúng ta nhớ là bọn họ sẽ sử dụng cách làm sau đây để tính tổng lợi nhuận Táo sinh sống Khu Vực phía Bắc:

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

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

*

Trong mảng thứ nhất, 1 đại diện thay mặt cho khu vực phía Bắc, với 0 thay mặt cho các Khu Vực không giống.

Trong mảng thiết bị hai, 1 đại diện mang đến Táo, với 0 đại diện thay mặt cho các món sản phẩm khác.

Mảng thứ ba đựng bao gồm những con số xuất hiện từ ô C2 cho C12.

Hãy nhớ rằng nhân 0 thì luôn bằng 0, và nhân 1 thì bởi bao gồm nó, chúng ta gồm mảng ở đầu cuối chứa lợi nhuận và phần lớn số lượng 0 – lợi nhuận chỉ xuất hiện ví như hai mảng trước tiên gồm hàng đầu ngơi nghỉ và một địa chỉ, rõ ràng là cả hai ĐK khẳng định hồ hết được đáp ứng; còn ko thì đã hiển thị số 0:

Cộng các con số vào mảng trên đến kết quả mong muốn – doanh thu Táo xuất kho sinh hoạt khoanh vùng phía Bắc.

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

Để công xuất xắc đếm tất cả điều kiện các ô áp dụng hàm lôgic OR, hãy thực hiện lốt cộng (+) thân những mảng.

Trong bí quyết Excel SUMPRODUCT, cũng như vào cách làm mảng, vết cộng vào vai trò có tác dụng tân oán tử OR lý giải Excel trả về cực hiếm TRUE nếu bất kỳ ĐK nào vào biểu thức xác minh được đáp ứng nhu cầu.

lấy ví dụ như, nhằm đếm tổng doanh số Táo cùng Ckhô hanh ko minh bạch vùng miền, hãy thực hiện phương pháp này:

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

Nói đơn giản và dễ dàng, bí quyết bên trên tất cả 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 và Ckhô cứng, hãy thêm 1 câu lệnh cất dải ô Doanh số:

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

Ảnh chụp màn hình sau đây diễn giải công thức 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 trường vừa lòng, rất có thể bạn phải đếm tốt tính tổng bao gồm ĐK áp dụng cả hàm lôgic AND cùng OR. Thậm chí sinh sống phiên bạn dạng Excel tiên tiến nhất , chuỗi hàm tương quan mang lại hàm IF cũng thiết yếu có tác dụng được điều ấy.

trong những phương án khả thi đó là phối hợp hai xuất xắc những hàm SUMIFS+SUMIFS tốt COUNTIFS+COUNTIFS.

Cách không giống sẽ là sử dụng hàm SUMPRODUCT vào đó:

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

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

Để khiến đa số thiết bị dễ dàng rộng, hãy cẩn thận các ví dụ bên dưới đây:

Để đếm mốc giới hạn Táo cùng Ckhô giòn được bán làm việc 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 áp dụng theo cú pháp hàm SUMPRODUCT, cách làm sẽ có được dạng nhỏng sau:

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

Để tính tổng lợi nhuận Táo và Ckhô cứng xuất kho ở khu vực 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 rất hàm lôgic AND:

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

Để bí quyết trông gọn rộng, bạn có thể gõ những trở thành vào các ô riêng lẻ – Khu vực ngơi nghỉ ô F1 với Món hàng sống ô F2 và 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 Một trong những ví dụ trước, họ đã bàn về phương pháp SUMPRODUCT dùng để làm tính trung bình bao gồm điều kiện. 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 mức độ vừa phải bao gồm trọng lượng trong số ấy từng quý hiếm có một trọng lượng nhất định.

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

SUMPRODUCT(valuesweights) / SUM(weights)

Giả sử các quý hiếm nằm ở dải ô B2:B7 với trọng lượng ở dải ô C2:C7, công thức SUMPRODUCT tính trung bình có trọng lượng bao gồm dạng nlỗi sau:

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

Tôi tin rằng ngay hiện nay, các bạn sẽ ko gặp mặt trở ngại vào câu hỏi gọi quy điều khoản công thức nữa.

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

ngay khi khi chúng ta hiểu bài viết này chỉ để sở hữu thêm ban bố với các bạn không thể ghi nhớ rõ những chi tiết nữa, hãy lưu giữ một điểm chính yếu quan trọng đặc biệt – hàm SUMPRODUCT trong Excel cách xử lý mảng. Và bởi hàm SUMPRODUCT bao gồm các kỹ năng của công thức mảng, nên nó có thể biến chuyển hàm sửa chữa dễ sử dụng mang lại cách làm mảng.

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

lấy một ví dụ, chúng ta cũng có thể sử dụng một công thức mảng dễ dàng và đơn giản nhằm đếm số cam kết trường đoản cú trong dải ô xác định:

=SUM(LEN(range))

với trở thành 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ó thể chọn các công thức mảng này trong Excel rồi cố gắng viết lại áp dụng hàm SUMPRODUCT.

Để rất có thể ứng dụng tốt Excel vào trong quá trình, chúng ta không những nắm vững được những hàm mà hơn nữa nên áp dụng giỏi cả các hiện tượng của Excel. Những hàm nâng cao góp áp dụng xuất sắc vào quá trình nhỏng SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những phương pháp thường xuyên áp dụng là Data validation, Conditional formatting, Pivot table…

Toàn cỗ phần nhiều kiến thức và kỹ năng này các bạn phần nhiều rất có thể học tập được trong khóa huấn luyện và đào tạo Excel từ cơ bản mang đến chuyên gia dành cho tất cả những người đi làm