CÁCH SỬ DỤNG SUMPRODUCT TRONG EXCEL

Bài giải đáp này của Học Excel Online đã giải thích cách sử dụng cơ phiên bản và cải thiện của hàm SUMPRODUCT trong Excel. Quý Khách đã thấy một vài ví dụ công thức dùng làm so sánh mảng, tính tổng tất cả ĐK cùng đếm số ô có rất nhiều điều kiện, tính trung bình cộng tất cả trọng lượng cùng không những thế nữa.

Bạn đang xem: Cách sử dụng sumproduct trong excel

lúc bạn nghe cái tên hàm SUMPRODUCT lần trước tiên, nó có vẻ như là 1 công thức bất lợi – phương pháp tính tổng thường thì của những tích số. Nhưng khái niệm này không cho biết thêm thậm chí còn là chỉ một phần nhỏ dại anh tài của hàm SUMPRODUCT.

Thực ra, hàm SUMPRODUCT trong Excel là hàm có rất nhiều kĩ năng. Do kỹ năng quan trọng đặc biệt hoàn toàn có thể cách xử lý mảng một biện pháp tinh tế và sắc sảo cùng xuất sắc, buộc phải hàm SUMPRODUCT trong Excel hết sức có lợi, còn nếu không buộc phải, khi nói tới Việc đối chiếu dữ liệu ngơi nghỉ hai hay các dải ô với vấn đề tính toán thù dữ liệu có không ít điều kiện. Những ví dụ tiếp sau đây vẫn cho chính mình thấy toàn bộ năng lực của hàm SUMPRODUCT cùng sự hữu dụng của chính nó đang mau chóng trlàm việc bắt buộc ví dụ thôi.


Xem nhanh


CÁCH SỬ DỤNG HÀM SUMPRODUCT TRONG EXCEL – VÍ DỤ CÔNG THỨC:HÀM SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆNĐẾM/TÍNH TỔNG/TÍNH TRUNG BÌNH CÓ ĐIỀU KIỆN CÁC Ô VỚI NHIỀU TIÊU CHUẨNCác áp dụng hay gặp mặt, cùng những hàm hay được dùng cùng với SUMPRODUCT.

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 trong mảng xác đị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à những dải ô liên tục tốt là các mảng có cất nguyên tố bạn muốn nhân, rồi cộng lại.


*

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

Số mảng buổi tối nhiều là 255 trong Excel năm 2016, Excel 2013, Excel 2010, cùng Excel 2007, và 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, nhưng mà nó không yên cầu phím tắt mảng (Ctrl + Shift + Enter). quý khách hàng chấm dứt một phương pháp SUMPRODUCT Theo phong cách thường thì bẳng giải pháp dấn phím Enter.

Lưu ý:

Tất cả mảng trong cách làm SUMPRODUCT phải tất cả thuộc số sản phẩm cùng số cột , còn nếu như không thì các bạn sẽ nhấn lỗi #VALUE!.

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

Nếu mảng là phnghiền test lô gic, thì nó vẫn trả về cực hiếm TRUE với FALSE. Thông thường, bạn phải thay đổi các giá trị TRUE cùng FALSE này thành 1 và 0 bằng phương pháp thực hiện toán tử đơn phân (–). Hãy liếc qua ví dụ hàm SUMPRODUCT có khá nhiều điều kiện nhằm nắm vững rộng.

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

Để tất cả ánh nhìn tổng thể về phong thái hàm SUMPRODUCT trong Excel vận động, hãy nhìn qua ví dụ sau.

Giả sử bạn gồm con số ngơi nghỉ các ô A2:A4, với bạn muốn tính tổng. Nếu bạn đang giải một phép tân oán nghỉ ngơi trường, thì các bạn sẽ nhân con số với giá tiền tài từng sản phẩm, rồi cùng chúng lại. Trong Microsoft Excel, chúng ta cũng có thể nhận được công dụng chỉ cách một bí quyết SUMPRODUCT:

=SUMPRODUCT(A2:A4,B2:B4)

Hình ảnh chụp screen sau đây cho thấy thêm cách làm trên thực tế:

Đây là cụ thể rất nhiều gì đã ra mắt về khía cạnh toán thù học:

Công thức chọn số đầu tiên trong mảng đầu tiên rồi nhân nó mang lại số thứ nhất trong mảng đồ vật nhị, rồi đem số thiết bị nhì vào mảng trước tiên nhân cho số thứ hai trong mảng trang bị hai rồi cứ đọng thường xuyên như vậy.

khi đang nhân vớ các yếu tắc mảng, phương pháp sẽ tính tổng các tích số rồi trả về tổng thể.

Nói bí quyết không giống, công thức SUMPRODUCT biểu diễn phương pháp toán học tập sau đây:

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

Hãy nghĩ về tới việc bạn có thể tiết kiệm chi phí bao nhiều thời gian ví như bảng của chúng ta không chứa 3 mặt hàng tài liệu, nhưng là 3 trăm tuyệt 3 ndở người 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 những tích số là giải pháp dùng dễ dàng và đơn giản và cụ thể tuyệt nhất của hàm SUBTOTAL vào Excel, tuy vậy đây chưa hẳn là cách dùng tốt nhất. Vẻ đẹp nhất thực sự của hàm SUMPRODUCT vào Excel sẽ là nó hoàn toàn có thể làm nhiều hơn những hào kiệt đã được nêu rõ. Sâu rộng trong bài bác khuyên bảo này, các bạn sẽ thấy vài ba cách làm cho biết thêm những phương pháp dùng thú vị cùng cải thiện hơn những, chính vì vậy hãy thường xuyên đọc bài viết nhé!

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

Đôi khi vào Microsoft Excel, luôn có khá nhiều rộng một cách để kết thúc công việc. Nhưng lúc kể đến vấn đề so sánh nhì hay những mảng, nhất là có nhiều ĐK, còn nếu không nói là tuyệt nhất, hàm SUMPRODUCT vào Excel là hàm công dụng độc nhất. À thì, hàm SUMPRODUCT tốt cách làm mảng đều được.

Giả sử chúng ta tất cả danh sách những món sản phẩm sinh sống cột A, doanh số phân phối dự con kiến sinh sống cột B, và doanh số cung cấp thực sinh sống cột C. Mục tiêu của chúng ta là tìm kiếm xem bao gồm từng nào tất cả từng nào món bán thấp hơn dự con kiến. Đối với ngôi trường đúng theo này, hãy thực hiện một trong số những đổi mới 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 những người new bước đầu, hãy chia nhỏ tuổi cách làm dễ dàng và đơn giản hơn – bí quyết đối chiếu các số lượng vào nhì cột hàng theo mặt hàng, và nói cho bọn họ biết tần số cột C thấp 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ông thức SUMPRODUCT vào Excel chứa nhì tuyệt những mảng, nó nhân các yếu tố của mảng, rồi cùng những tích số.

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

Nhưng thậm chí sinh sống những phiên bản hiện đại của Excel, công thức SUMPRODUCT hoàn toàn có thể là 1 trong chọn lựa thay thế, ví dụ, tính tổng với đếm có điều kiện các ô gồm hàm lôgic OR. Dưới đây, bạn sẽ thấy vài ví dụ cách làm cho biết năng lực này.

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

Giả sử các bạn có chuỗi dữ liệu tiếp sau đâ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 bán ra:

Điều bạn có nhu cầu triển khai sẽ là đếm, tính tổng với tính vừa phải cùng doanh số táo Apple bán ra sống Quanh Vùng phía Bắc.

Trong các phiên bản Excel ngay sát đây: Excel 2016, 2013, 2010 cùng 2007, chúng ta có thể thuận tiện dứt trách nhiệm bằng phương pháp sử dụng phương pháp SUMIFS, COUNTIFS với AVERAGEIFS. Nếu bạn đang tra cứu 1 phía đi khó khăn hơn, tuyệt nếu bạn vẫn áp dụng Excel 2003 giỏi phiên phiên bản cũ hơn, thì bạn vẫn có được kết quả ước muốn với hàm SUMPRODUCT vào Excel.

Để đếm doanh số hãng apple xuất kho sinh hoạt 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 Apple xuất kho sinh hoạt 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=”Quanh Vùng phía Bắc”)*(B2:B12=”táo”)*C2:C12)

Để tính trung bình cộng lợi nhuận hãng apple xuất kho làm việc khu vực phía Bắc:

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

=SUMPRODUCT(–(A2:A12=”Khu Vực 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 linc hoạt rộng, chúng ta có thể định rõ khu vực cùng món hàng mong ước nghỉ ngơi những ô riêng rẽ, rồi tsay mê chiếu những ô đó vào bí quyết giống như hình họa chụp màn hình 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ách làm SUMPRODUCT vào Excel đếm số ô cùng rất hiều ĐK. Nếu các bạn vẫn nắm rõ cách đây, sẽ tương đối dễ dãi nhằm các bạn nắm vững quy công cụ tính tổng.

Để tôi đề cập các bạn nhớ rằng chúng ta đang sử dụng phương pháp sau đây để tính tổng doanh số Táo ở khoanh vùng phía Bắc:

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

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

*

Trong mảng đầu tiên, 1 đại diện mang lại Khu Vực phía Bắc, cùng 0 đại diện thay mặt cho những khoanh vùng không giống.

Xem thêm: Cách Backup Tin Nhắn Iphone, Sao Lưu Tin Nhắn Trên Iphone, Backup Sms Iphone

Trong mảng sản phẩm công nghệ hai, 1 thay mặt cho Táo, và 0 đại diện cho các món sản phẩm khác.

Mảng thiết bị tía chứa thiết yếu những con số xuất hiện trường đoản cú ô C2 mang đến C12.

Hãy nhớ là nhân 0 thì luôn bởi 0, và nhân 1 thì bằng bao gồm nó, bọn họ tất cả mảng sau cuối cất doanh thu cùng đều số lượng 0 – lợi nhuận chỉ lộ diện trường hợp nhì mảng thứ nhất có hàng đầu làm việc và một địa chỉ, ví dụ là cả nhị điều kiện khẳng định số đông được đáp ứng; còn không thì đã hiển thị số 0:

Cộng các con số vào mảng trên mang đến công dụng mong muốn – lợi nhuận Táo bán ra 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 bao gồm điều kiện các ô áp dụng hàm lôgic OR, hãy sử dụng lốt cùng (+) thân những mảng.

Trong phương pháp Excel SUMPRODUCT, cũng như vào phương pháp mảng, vết cộng đóng vai trò làm cho toán tử OR khuyên bảo Excel trả về quý giá TRUE ví như ngẫu nhiên ĐK nào vào biểu thức xác định được thỏa mãn nhu cầu.

lấy một ví dụ, để đếm tổng doanh thu Táo với Chanh hao ko minh bạch vùng miền, hãy áp 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 tất cả nghĩa là: Hãy đếm số ô giả dụ dải ô B2:B12=”táo” OR B2:B12=”chanh”.

Để tính tổng lợi nhuận Táo và Chanh hao, 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ị 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ợp, rất có thể bạn cần phải đếm tuyệt tính tổng gồm điều kiện thực hiện cả hàm lôgic AND cùng OR. Thậm chí ngơi nghỉ phiên bạn dạng Excel mới nhất , chuỗi hàm liên quan đến hàm IF cũng quan yếu làm được điều này.

trong những giải pháp khả thi chính là phối kết hợp nhị hay nhiều hàm SUMIFS+SUMIFS tốt COUNTIFS+COUNTIFS.

Cách không giống đó là thực hiện hàm SUMPRODUCT vào đó:

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

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

Để khiến hồ hết thiết bị đơn giản và dễ dàng hơn, hãy để mắt tới những ví dụ dưới đây:

Để đếm mốc giới hạn Táo cùng Ckhô hanh được bán sinh hoạt Khu Vực phía Bắc, hãy lập cách làm 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, công thức sẽ sở hữu dạng nlỗi 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 hao đẩy ra làm việc quần thể vục phía Bắc, hãy dùng công thức bên trên rồi thêm dải ô Doanh số với 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õ các đổi mới vào những ô hiếm hoi – Khu vực ngơi nghỉ ô F1 cùng Món hàng làm việc ô F2 với H2 – rồi tmê man chiếu các ô 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 mức độ vừa phải tất cả ĐK. Cách dùng thông dụng không giống của hàm SUMPRODUCT vào Excel chính là tính vừa đủ có trọng lượng trong các số ấy từng quý giá bao gồm một trọng lượng nhất mực.

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

SUMPRODUCT(valuesweights) / SUM(weights)

Giả sử những quý giá nằm ở dải ô B2:B7 với trọng lượng ngơi nghỉ dải ô C2:C7, bí quyết SUMPRODUCT tính vừa phải có trọng lượng tất cả dạng nlỗi sau:

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

Tôi tin rằng ngay hiện giờ, các bạn sẽ ko gặp mặt trở ngại trong câu hỏi gọi quy pháp luật 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

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

Ưu điểm là gì? Về khía cạnh cơ bản, các bạn có công dụng cai quản phương pháp một phương pháp thuận tiện nhưng không nhất thiết phải nhấp Ctrl + Shift + Enter những lần các bạn nhập một phương pháp mảng new hay chỉnh sửa công thức mảng.

lấy ví dụ, chúng ta có thể thực hiện một bí quyết mảng dễ dàng và đơn giản nhằm đếm số ký trường đoản cú 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, chúng ta có thể chọn những cách làm mảng này trong Excel rồi nỗ lực viết lại thực hiện hàm SUMPRODUCT.

MOD cùng SUMPRODUCT trong Excel đếm ô chứa số lẻ

Ta các đã biết số lẻ là hầu như số phân tách 2 dư 1. Trong ngôi trường phù hợp này, ta sẽ phối kết hợp 2 hàm là MOD với SUMPRODUCT để triển khai đếm hồ hết ô cất số lẻ trong Excel.

Dữ liệu mẫu mã của ta nhỏng sau:

*

Yêu cầu: Đếm hồ hết ô đựng số lẻ.

Công thức hàm SUMPRODUCT excel vào trường đúng theo này của ta nlỗi sau:

=SUMPRODUCT(–(MOD(A1:A15,2)=1))

Công thức (MOD(A1:A15,2)=1) vẫn trả về giá chỉ trị dạng mảng TRUE; FALSE tương ứng: TRUE giả dụ ô không phân tách hết đến 2 cùng ngược trở lại.  đổi thay TRUE thành 1, FALSE thành 0. Cuối thuộc ta lồng vào SUMPRODUCT trả về công dụng là số ô đựng cực hiếm số lẻ.

*

Các vận dụng thường xuyên chạm mặt, và các hàm thường được sử dụng với SUMPRODUCT.

Các hàm bình chọn logic: ISNUMBER/ISERROR + SEARCHHàm SEARCH giúp chúng ta kiếm tìm kiếm không biệt lập chữ Hoa/hay. Nếu bạn cần tìm kiếm chữ như thể nhau thì hôm nay tất cả hàm FIND.Tính tổng tất cả các sheet với SUMIFSTách chuỗi ra để lấy số liệu tính toán:Bài toán đưa ra bạn có chuỗi ký kết tự: u, x, 3g, nhuộm. Và làm cho thế làm sao để bóc tách ra theo những đơn vị chức năng tính tương ứng.Vận dụng phối kết hợp hàm TEXT để biến hóa các số, chữ, và số 0 thành số lượng tương xứng nhằm tính tân oán.

*

Chuyển chuỗi cam kết tự biểu thức cộng trừ thành hiệu quả tính toán:Bước 1: Chúng ta đang tra cứu thay thế cam kết từ “-” thành “+-“.Bước 2: Chúng ta vẫn search sửa chữa ký kết từ bỏ “+” cùng với hàm REPT(” “,99) nhằm cyếu thay thế sửa chữa 99 cam kết tự ” “.Bước 4: Khác với hàm TRIM, chúng ta đã dùng hàm TEXT dìm dạng ký trường đoản cú.Cách 5: Với hàm SUMPRODUCT kết hợp vệt -- (dùng để đưa chữ thành số), sau đó cùng dồn lại cùng nhau.Kết quả:=SUMPRODUCT(--TEXT(MID(SUBSTITUTE(SUBSTITUTE(A2,"-","+-"),"+",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A2)))*99-98,99),"0.00;-0.00;0;"))

*

Đếm độc nhất với SUMPRODUCT:

*

Tính khoảng thời gian đi muộn, về mau chóng, giờ đồng hồ làm cho của nhân viên với hàm SUMPRODUCT.Ta quy ước tiếng đi muộn vào ô BM1, là 8:00, nếu đi sau giờ kia tính là muộn.SUMPRODUCT từ bây giờ lấy giờ checkin, trừ cho giờ giải pháp.Để quy ra số phút, ta buộc phải nhân cho 24 giờ đồng hồ x 60 phút ít. Vì thời hạn trong Excel là 1 trong những số lượng thập phân tự 0 -> 1.Ứng dụng SUMPRODUCT trong bài toán chnóng công, tính công cho nhân viên.Quý Khách rất có thể lập bảng cam kết hiệu, và quy thay đổi ký hiệu kia sang ngày công tương ứng. Rồi từ bỏ kia chúng ta trọn vẹn có thể tính ngày công.

Để rất có thể áp dụng giỏi Excel vào trong quá trình, họ không chỉ nắm rõ được những hàm ngoại giả phải thực hiện tốt cả những mức sử dụng của Excel. Những hàm nâng cấp giúp vận dụng tốt vào các bước như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những luật thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn cỗ mọi kỹ năng này các bạn đa số rất có thể học tập được vào khóa đào tạo và huấn luyện Excel tự cơ bản cho chuyên gia dành cho người đi làm