Bài hướng dẫn này của Học Excel Online sẽ lý giải cách sử dụng cơ bạn dạng và nâng cao của hàm SUMPRODUCT vào Excel. Bạn sẽ thấy một vài ví dụ như công thức dùng để so sánh mảng, tính tổng có điều kiện và đếm số ô có không ít điều kiện, tính vừa đủ cộng bao gồm trọng lượng và không dừng lại ở đó nữa. Bạn đang xem: Tính tổng của tích 2 cột trong excel
Khi bạn nghe cái thương hiệu hàm SUMPRODUCT thứ 1 tiên, nó có vẻ là 1 trong những công thức ăn hại – phương pháp tính tổng thông thường của những tích số. Cơ mà định nghĩa này không cho biết thậm chí là chỉ một phần nhỏ khả năng của hàm SUMPRODUCT.
Thực ra, hàm SUMPRODUCT vào Excel là hàm có tương đối nhiều tính năng. Do năng lực đặc biệt hoàn toàn có thể xử lý mảng một cách sắc sảo và thông minh, cần hàm SUMPRODUCT trong Excel khôn xiết hữu ích, còn nếu không bắt buộc, khi nói đến việc đối chiếu dữ liệu ở nhị hay những dải ô và việc đo lường và tính toán dữ 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ể khả năng của hàm SUMPRODUCT và sự có lợi của nó vẫn sớm trở nên rõ ràng 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 thường gặp, và những hàm thường dùng với SUMPRODUCT.
HÀM SUMPRODUCT vào EXCEL – CÚ PHÁP VÀ CÔNG DỤNG:
Về khía cạnh kỹ thuật, hàm SUMPRODUCT trong Excel nhân những con số vào 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 rất đơn giản:
SUMPRODUCT(array1,
Trong đó, mảng 1, mảng 2, … là những dải ô liên tiếp hay là những mảng gồm chứa thành phần bạn muốn nhân, rồi cùng lại.
Số mảng tối thiểu là 1. 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 thành phần mảng rồi trả về tổng số.
Số mảng buổi tối đa là 255 vào Excel 2016, Excel 2013, Excel 2010, và Excel 2007, và 30 trong những phiên bản trước của Excel.
Mặc cho dù hàm SUMPRODUCT xử trí mảng, mà lại nó ko yêu ước phím tắt mảng (Ctrl + Shift + Enter). Bạn chấm dứt một cách làm SUMPRODUCT theo cách thông thường bẳng giải pháp nhấn phím Enter.
Lưu ý:
Tất cả mảng trong phương pháp SUMPRODUCT phải tất cả cùng 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 kể câu lệnh mảng nào đựng giá trị phi số, chúng sẽ được xử lý như số 0.
Nếu mảng là phép demo lô gic, thì nó đang trả về quý hiếm TRUE với FALSE. Thông thường, bạn cần phải đổi những giá trị TRUE cùng FALSE này thành 1 với 0 bằng cách sử dụng toán tử đối kháng phân (–). Hãy coi qua ví dụ như hàm SUMPRODUCT có tương đối nhiều điều kiện để nắm rõ hơn.
CÁCH DÙNG CƠ BẢN CỦA HÀM SUMPRODUCT trong EXCEL:
Để gồm cái chú ý tổng quát về kiểu cách hàm SUMPRODUCT vào Excel hoạt động, hãy xem qua ví dụ như sau.
Giả sử chúng ta có số lượng ở các ô A2:A4, và bạn muốn tính tổng. Nếu bạn đang giải một phép toán làm việc trường, thì các bạn sẽ nhân số lượng với giá bán của mỗi sản phẩm, rồi cộng bọn chúng lại. Vào Microsoft Excel, chúng ta cũng có thể nhận được kết quả chỉ với một cách làm SUMPRODUCT:
=SUMPRODUCT(A2:A4,B2:B4)
Ảnh chụp màn hình hiển thị dưới đây cho thấy thêm công thức bên trên thực tế:
Đây là chi tiết những gì đang ra mắt về mặt toán học:
Công thức lựa chọn số đầu tiên trong mảng thứ nhất rồi nhân nó cho số thứ nhất trong mảng trang bị hai, rồi đem số thiết bị hai vào mảng trước tiên nhân cho số thứ hai trong mảng vật dụng hai rồi cứ liên tiếp như thế.
Khi đã nhân tất những thành phần mảng, bí quyết sẽ tính tổng các tích số rồi trả về tổng số.
Nói cách khác, công thức SUMPRODUCT trình diễn công thức toán học sau đây:
=A2*B2 + A3*B3 + A4*B4
Hãy nghĩ đến việc chúng ta cũng có thể tiết kiệm bao nhiều thời gian nếu bảng của khách hàng không chứa 3 mặt hàng dữ liệu, cơ mà là 3 trăm giỏi 3 ngàn hàng!
CÁCH SỬ DỤNG HÀM SUMPRODUCT vào EXCEL – VÍ DỤ CÔNG THỨC:
Nhân hai hay các dải ô cùng nhau rồi cộng những tích số là bí quyết dùng đơn giản và cụ thể nhất của hàm SUBTOTAL trong Excel, mà lại đây không phải là biện pháp dùng duy nhất. Vẻ đẹp nhất thực sự của hàm SUMPRODUCT vào Excel đó là nó có thể làm nhiều hơn nữa các nhân kiệt đã được nêu rõ. Sâu rộng trong bài bác hướng dẫn này, các bạn sẽ thấy vài ba công thức cho thấy thêm những biện pháp dùng thú vui và nâng cao hơn nhiều, vì thế hãy liên tục đọc bài viết nhé!
HÀM SUMPRODUCT CÓ NHIỀU ĐIỀU KIỆN
Thông thường xuyên trong Microsoft Excel, luôn có khá nhiều hơn một phương pháp để hoàn thành công việc. Tuy vậy khi kể đến việc đối chiếu hai hay những mảng, đặc biệt quan trọng là có khá nhiều điều kiện, còn nếu như không nói là duy nhất, hàm SUMPRODUCT vào Excel là hàm tác dụng nhất. À thì, hàm SUMPRODUCT hay phương pháp mảng đa số được.
Giả sử bạn có danh sách những món hàng ở cột A, doanh thu bán dự loài kiến ở cột B, và lợi nhuận bán thực làm việc cột C. Mục tiêu của doanh nghiệp là tìm kiếm xem tất cả bao nhiêu gồm bao nhiêu món bán thấp hơn dự kiến. Đối với trường hợp này, hãy sử dụng trong 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 mới bắt đầu, hãy chia nhỏ công thức đơn giản dễ dàng hơn – phương pháp so sánh các con số trong hai cột mặt hàng theo hàng, với nói cho bọn họ biết mốc giới hạn 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:
Khi cách làm SUMPRODUCT trong Excel chứa hai hay các mảng, nó nhân những thành phần của mảng, rồi cộng những tích số.
Có lẽ bạn vẫn còn đấy nhớ, họ đã áp dụng công thức sau đây để đếm số lần doanh thu thực (cột C) thấp hơn doanh số dự con 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à những phiên bản trước thì không tồn tại hàm IFS, giữa những cách cần sử dụng thông dụng độc nhất vô nhị của hàm SUMPRODUCT sẽ là tính tổng xuất xắc đếm bao gồm điều kiện những ô với rất nhiều tiêu chuẩn. Bước đầu với Excel 2007, Microsoft ra mắt một chuỗi hàm được quan trọng thiết kế mang đến những nhiệm vụ này – hàm SUMIFS, hàm COUNTIFS và hàm AVERAGEIFS.
Nhưng thậm chí là ở các phiên bạn dạng hiện đại của Excel, cách làm SUMPRODUCT trả toàn hoàn toàn có thể là một lựa chọn nỗ lực thế, ví dụ, tính tổng với đếm bao gồm điều kiện những ô bao gồm hàm lôgic OR. Dưới đây, bạn sẽ thấy vài ví dụ như công thức cho biết thêm khả năng này.
CÔNG THỨC SUMPRODUCT VỚI HÀM LÔGIC AND:Giả sử chúng ta có chuỗi dữ liệu dưới đây, trong số đó cột A liệt kê vùng miền, cột B – món hàng với cột C – doanh thu bán ra:
Điều bạn có nhu cầu thực hiện đó là đếm, tính tổng với tính vừa phải cộng lợi nhuận táo xuất kho ở khoanh vùng phía Bắc.
Trong những phiên bạn dạng Excel sát đây: Excel 2016, 2013, 2010 cùng 2007, chúng ta có thể dễ dàng chấm dứt nhiệm vụ bằng cách sử dụng cách làm SUMIFS, COUNTIFS cùng AVERAGEIFS. Nếu như khách hàng đang tìm một hướng đi trở ngại hơn, hay nếu bạn vẫn thực hiện Excel 2003 xuất xắc phiên bản cũ hơn, thì bạn vẫn đạt được công dụng mong mong mỏi với hàm SUMPRODUCT vào Excel.
Để đếm doanh số táo đẩy ra ở quanh vùng 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 số táo bán ra ở quanh vùng 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 mức độ vừa phải cộng doanh số táo bán ra ở khoanh vùng phía Bắc:
Để tính vừa đủ cộng, chúng ta chỉ phân tách 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”))
Để phương pháp SUMPRODUCT linh hoạt hơn, bạn cũng có thể định rõ khu vực và món hàng mong muốn ở những ô riêng, rồi tham chiếu các ô kia vào cách làm giống như hình ảnh chụp screen dưới đây:
CÔNG THỨC SUMPRODUCT DÙNG mang lại VIỆC TÍNH TỔNG CÓ ĐIỀU KIỆN HOẠT ĐỘNG NHƯ THẾ NÀOTừ ví dụ như trướ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 như khách hàng đã làm rõ cách đó, đã rất thuận tiện để bạn nắm vững quy điều khoản tính tổng.
Để tôi nhắc bạn nhớ rằng họ đã sử dụng công thức sau đây để tính tổng doanh thu Táo ở 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 thẳng của phương pháp trên đó là 3 mảng bên dưới đây:
Trong mảng đầu tiên, 1 thay mặt cho khoanh vùng phía Bắc, với 0 thay mặt cho các khu vực khác.
Xem thêm: 20+ Phần Mềm Hát Karaoke Và Thu Âm Trên Máy Tính Online, Offline Hay Nhất
Trong mảng thứ hai, 1 thay mặt cho Táo, với 0 thay mặt đại diện cho những món sản phẩm khác.
Mảng thứ ba chứa chính những con số mở ra từ ô C2 cho C12.
Hãy nhớ rằng nhân 0 thì luôn bằng 0, cùng nhân 1 thì bằng chính nó, họ có mảng ở đầu cuối chứa doanh số và những con số 0 – lợi nhuận chỉ lộ diện nếu nhì mảng đầu tiên có tiên phong hàng đầu ở và một vị trí, rõ ràng là cả nhị điều kiện khẳng định đều được đáp ứng; còn không thì sẽ hiển thị số 0:
Cộng những con số trong mảng trên cho hiệu quả mong mong muốn – doanh thu Táo xuất kho ở khu vực 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 gồm điều kiện các ô áp dụng hàm lôgic OR, hãy áp dụng dấu cùng (+) giữa những mảng.
Trong phương pháp Excel SUMPRODUCT, cũng giống như trong cách làm mảng, dấu cộng đóng vai trò làm toán tử OR lí giải Excel trả về quý giá TRUE nếu ngẫu nhiên điều kiện nào trong biểu thức xác định được đáp ứng.
Ví dụ, để đếm tổng doanh số Táo và Chanh không riêng biệt 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 đối chọi giản, phương pháp 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 lợi nhuận Táo với Chanh, hãy thêm 1 câu lệnh chứa dải ô Doanh số:
=SUMPRODUCT((B2:B12=”táo”)+(B2:B12=”chanh”), C2:C12)
Ảnh chụp màn hình dưới đâ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 & VÀ HÀM LÔGIC OR
Trong nhiều trường hợp, tất cả thể bạn cần phải đếm hay tính tổng có điều kiện sử dụng cả hàm lôgic & và OR. Thậm chí là ở phiên phiên bản Excel mới nhất , chuỗi hàm tương quan đến hàm IF cũng tất yêu làm được điều đó.
Một trong những giải pháp khả thi đó là kết hợp hai hay các hàm SUMIFS+SUMIFS tuyệt COUNTIFS+COUNTIFS.
Cách khác kia là sử dụng hàm SUMPRODUCT vào đó:
Dấu hoa thị (*) được sử dụng như toán tử AND.
Dấu cộng (+) được sử dụng như toán tử OR.
Để khiến cho mọi thứ đơn giản và dễ dàng hơn, hãy xem xét những ví dụ dưới đây:
Để đếm số lần táo bị cắn và Chanh được cung cấp ở khu vực phía Bắc, hãy lập công thức 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 sử dụng theo cú pháp hàm SUMPRODUCT, công thức sẽ sở hữu 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 cùng Chanh xuất kho ở khu vực vục phía Bắc, hãy dùng bí quyết trên rồi thêm dải ô lợi nhuận cùng 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)
Để bí quyết trông gọn hơn, bạn có thể gõ các biến vào những ô hiếm hoi – quanh vùng ở ô F1 cùng Món mặt hàng ở ô F2 với H2 – rồi tham 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Ở trong những ví dụ trước, họ đã bàn về công thức SUMPRODUCT dùng để làm tính trung bình tất cả điều kiện. Giải pháp dùng thông dụng khác của hàm SUMPRODUCT vào Excel đó là tính trung bình tất cả trọng lượng trong số ấy mỗi giá trị gồm một trọng lượng độc nhất định.
Công thức SUMPRODUCT tính trung bình có trọng lượng tổng quát tất cả dạng như sau:
SUMPRODUCT(values, weights) / SUM(weights)
Giả sử những giá trị nằm ở vị trí dải ô B2:B7 cùng trọng lượng sinh sống dải ô C2:C7, bí quyết SUMPRODUCT tính trung bình tất cả trọng lượng có dạng như sau:
=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)
Tôi có niềm tin rằng ngay bây giờ, các bạn sẽ không chạm mặt khó khăn trong bài toán hiểu quy luật bí quyết nữa.
HÀM SUMPRODUCT ĐƯỢC DÙNG LÀM HÀM vậy THẾ cho CÔNG THỨC MẢNGNgay cả khi chúng ta đọc nội dung bài viết này chỉ để sở hữu thêm thông tin và bạn không thể nhớ rõ các chi tiết nữa, hãy lưu giữ một điểm mấu chốt đặc trưng – hàm SUMPRODUCT trong Excel cách xử lý mảng. Và do hàm SUMPRODUCT tất cả các năng lực của bí quyết mảng, cần nó rất có thể trở thành hàm sửa chữa dễ áp dụng cho cách làm mảng.
Ưu điểm là gì? Về phương diện cơ bản, chúng ta có khả năng cai quản công thức một cách thuận tiện mà không cần phải nhấp Ctrl + Shift + Enter các lần bạn nhập một cách làm mảng new hay chỉnh sửa công thức mảng.
Ví dụ, bạn có thể sử dụng một bí quyết mảng đơn giản để đếm số ký kết tự vào dải ô xác định:
=SUM(LEN(range))
và vươn lên là nó thành công thức thông thường:
=SUMPRODUCT(LEN(range))
Để thực hành, bạn cũng có thể chọn những công thức mảng này vào Excel rồi nỗ lực viết lại áp dụng hàm SUMPRODUCT.
MOD và SUMPRODUCT vào Excel đếm ô chứa số lẻ
Ta đa số đã biết số lẻ là gần như số chia 2 dư 1. Trong trường hợp này, ta sẽ kết hợp 2 hàm là MOD với SUMPRODUCT để thực hiện đếm đầy đủ ô chứa số lẻ trong Excel.
Dữ liệu chủng loại của ta như sau:
Yêu cầu: Đếm mọi ô chứa số lẻ.
Công thức hàm SUMPRODUCT excel vào trường thích hợp này của ta như sau:
=SUMPRODUCT(–(MOD(A1:A15,2)=1))
Công thức (MOD(A1:A15,2)=1) sẽ trả về giá bán trị dạng mảng TRUE; FALSE tương ứng: TRUE nếu ô không phân chia hết cho 2 cùng ngược lại. — vươn lên là TRUE thành 1, FALSE thành 0. ở đầu cuối ta lồng vào SUMPRODUCT trả về công dụng là số ô chứa giá trị số lẻ.
Các ứng dụng thường gặp, và các hàm hay sử dụng với SUMPRODUCT.
Các hàm bình chọn logic: ISNUMBER/ISERROR + SEARCHHàm tìm kiếm giúp chúng ta tìm kiếm không phân biệt chữ Hoa/thường. Nếu bạn cần tra cứu chữ giống nhau thì bây giờ có hàm FIND.Tính tổng toàn bộ các sheet với SUMIFSTách chuỗi ra để đưa số liệu tính toán:Bài toán đề ra bạn bao gồm chuỗi cam kết tự: u, x, 3g, nhuộm. Và làm cố gắng nào để bóc tách ra theo các đơn vị tính tương ứng.Vận dụng kết hợp hàm TEXT để thay đổi các số, chữ, với số 0 thành số lượng tương ứng để tính toán.
Để có thể ứng dụng xuất sắc Excel vào vào công việc, bọn họ không chỉ nắm rõ được các hàm mà còn đề nghị sử dụng tốt cả các công cố gắng của Excel. Phần nhiều hàm cải thiện giúp áp dụng giỏi vào quá trình như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những cách thức thường áp dụng là Data validation, Conditional formatting, Pivot table…
Toàn cỗ những kiến thức này chúng ta đều rất có thể học được trong khóa huấn luyện Excel trường đoản cú cơ bạn dạng đến chuyên gia dành cho người đi làm
