Khi bạn nghe tên hàm SUMPRODUCT lần thứ nhất tiên, nó bao gồm vẻ giống hệt như một bí quyết vô dụng tiến hành một tổng số thường thì của vận động sản phẩm. Tuy vậy với cách hướng dẫn sau đây thì thực sự hàm SUMPRODUCT không thể vô dụng một chút nào nhé.
Bạn đang xem: Excel tính tổng có điều kiện
1. Hàm SUMPRODUCT trong Excel
Hàm SUMPRODUCT vào học Excellà hàm tính tích các phạm vi hoặc mảng cùng với nhau và trả về tổng thể sản phẩm. Điều này nghe có vẻ như khá nhàm chán, dẫu vậy hàm nàycó những tác dụng cực kỳ linh hoạt hoàn toàn có thể được thực hiện để đếm và tính tổng hoạt bát hơn. Trong thực tế, hàm SUMPRODUCT được dùng khá nhiều và rất là hữu ích.
Sơ lược về hàm SUMPRODUCT
2. Cách làm tính của hàm SUMPRODUCT
Về khía cạnh kỹ thuật, hàm này vào Excel sẽ nhân các số trong những mảng đã hướng dẫn và chỉ định và trả về tổng của các sản phẩm đó.
Cú pháp của hàm SUMPRODUCT trong Excel rất đơn giản và dễ dàng và dễ hiểu:
= SUMPRODUCT (array1,
Trong đó:- array1: là mảng đầu tiên bắt phải có nếu muốn nhân những thành phần của nó rồi cộng tổng lại.
-
- con số mảng về tối thiểu là 1. Vào trường đúng theo này, bí quyết SUMPRODUCT chỉ đơn giản và dễ dàng là cộng tất cả các bộ phận mảng và trả về tổng.
- con số mảng về tối đa là 255 vào Excel 2016, Excel 2013, Excel 2010 cùng Excel 2007 cùng 30 trong những phiên bản Excel trước đó.
- mặc dù SUMPRODUCT vận động với các mảng, dẫu vậy nó không yêu cầu thực hiện phím tắt mảng ( Ctrl + Shift + Enter).. Bạn tuyên chiến đối đầu một phương pháp SUMPRODUCT theo phong cách thông thường bằng cách nhấn phím Enter.
Lưu ý:
+ toàn bộ các mảng trong công thức SUMPRODUCT phải bao gồm cùng số lượng hàng với cột, trường hợp không, bạn sẽ nhận được #VALUE! lỗi.
+ Nếu bất kỳ đối số mảng làm sao chứa những giá trị không phải là số, bọn chúng sẽ được xem như là số không.
+ trường hợp một mảng là 1 trong bài đánh giá logic, nó sẽ dẫn đến các giá trị TRUE cùng FALSE. Trong số đông các ngôi trường hợp, các bạn cần thay đổi chúng thành 1 với 0 bằng cách sử dụng toán tử đối kháng nguyên kép (-).
+ Tổng thích hợp không hỗ trợ các ký kết tự đại diện.
3. Lấy ví dụ như cách áp dụng hàm SUMPRODUCT
Giả sử bạn có số lượng trong những ô A2: A4, giá trong những ô B2: B4 và bạn có nhu cầu tìm đọc tổng số. Nếu bạn đang làm bài kiểm tra toán nghỉ ngơi trường, bạn sẽ tính tích con số với giá cho mỗi mục, tiếp đến cộng các tổng phụ. Trong Microsoft Excel, chúng ta có thể nhận hiệu quả bằng một công thức SUMPRODUCT duy nhất:=SUMPRODUCT(A2:A4,B2:B4)
3. Cách áp dụng hàm SUMPRODUCT
Hàm sumproduct tính tổng nhiều đk sẽ được thực hiện qua các ví dụ ví dụ dưới đây:
+ Trường thích hợp 1: giải pháp dùnghàm SUMPRODUCT trong Excel với một tiêu chí duy nhất
Ví dụ: Tập dữ liệu dưới đây chứa 6 tên trong những hàng và 12 tháng trong số cột. Hãy tưởng tượng sếp của bạn muốn bạn cộng tất cả các số cho tháng Sáu. Ban đầu công thức của bạn bằng cách chọn phạm vi dữ liệu (không có tên và tháng) cùng nhân nó với mảng chứa các tháng bằng 6.
Các cách thực hiệnhàm tính tổng có điều kiện trong Excel.
Bước 1: Nhập công thức: = SUMPRODUCT(array1,
Trong đó:
Mảng 1 của phương pháp hướng dẫn Excel cộng toàn bộ các số trong sàng lọc C9: N14. Array2 bình chọn từng số mon xem nó có bằng 6. Hiệu quả của việc này là TRUE (1) hoặc FALSE (0). Chúng ta có thể thấy công dụng của sản phẩm này trong hàng 16. Chỉ với cột H kết quả là TRUE, vị vậy tác dụng là 1. Để nhân Array1 cùng với Array2 tức là nhân các số thực với 0 hoặc 1. Trong ví dụ này, bí quyết nhân toàn bộ các số vào cột C bởi 0 và tất cả các số từ cột H bằng 1. Toàn bộ các giá trị được nhân cùng với 0 cùng với không. Những số duy nhất còn lại được nhân cùng với 1, trong trường hòa hợp này là tháng 6. Việc thực hiện hàmsumproduct có điều kiện giúp bạn nhanh lẹ thực hiện tại phép tính và đến ra tác dụng nhanh và đúng chuẩn nhất.
+ Trường phù hợp 2: thực hiện hàm SUMPRODUCT làm tiêu chí cho các cột
- Hãy liên tiếp ví dụ trên bằng phương pháp thêm các tiêu chí khác. Sếp của bạn hiện thời muốn biết doanh số bán sản phẩm trong quý ii là bao nhiêu, vày vậy từ thời điểm tháng thứ 4 trở lên và bao gồm cả tháng 6. Bao gồm nghĩa là công ty chúng tôi có một phạm vi dữ liệu to hơn để thêm vào. Làm núm nào để chúng ta xử lý nó?
Mô tả:
Công thức áp dụng là:
=SUMPRODUCT(( C9:N14) * (C8:N*>=4) * (C8:N8 = 4) và nhỏ tuổi hơn hoặc bởi 6 (Trường hợp 2: sử dụng hàm đểlàm tiêu chí cho các cột
Để đã có được điều đó, hãy thêm tháng giới hạn thấp rộng trong ô J4 với giới hạn cao hơn trong ô J5. Bây giờ thay đổi cách làm để phản chiếu điều này. Cầm vì tìm hiểu thêm số 4, hãy viết ô J4. Cố số 6 bởi ô J5.
Bạn sẽ tiến hành công thức ở đầu cuối sẽ là:
=SUMPRODUCT ((C9:N14) * (C8:N8>= J14) * (C8:N8Trường hợp 3: sử dụng hàm đểlàm tiêu chú mang đến cột và hàng
Như vậy, để tính một tiêu chí bổ sung trong các hàng, hãy thêm 1 mảng khác vào công thức. Chỉ định rằng những tên phải bởi “Lisa” . Như họ vừa search hiểu, chúng ta cũng có thể muốn tìm hiểu thêm một ô tham số thay do mã hóa nó. Vị vậy, viết Lisa vào ô M4. Hiện nay thêm một mảng chọn các ô B9: B14 bằng với ô thông số của bạn, trong trường đúng theo này là M4. Cột P cho biết hàng nào đáp ứng điều khiếu nại này, là hàng 11.
Công thức thực hiện là:
=SUMPRODUCT ((C9:N14) * (C8:N8 >=J4) * (C8:N Khi thao tác với tài liệu trong Excel, những hàm tính tổng gồm điều kiện không chỉ có gói gọn gàng trong 2 hàm thịnh hành SUMIF, SUMIFS nhưng Excel còn cung cấp một số các hàm tính tổng khác có thể chấp nhận được bạn triển khai tính tổng cùng với điều kiện phức tạp hơn. Trong bài viết này, emcanbaove.edu.vn vẫn tổng hợp các hàm Excel bạn cũng có thể sử dụng để tính tổng có điều kiện từ đơn giản và dễ dàng đến phức tạp. Mục lục Một trong những điểm mạnh của Excel chính là thực hiện những phép tính theo một hoặc nhiều đk bạn mong muốn cho một vùng tài liệu lớn vô cùng cấp tốc chóng. Mặc dù nhiên, để chọn lọc và triển khai đúng những hàm đòi hỏi kỹ năng bốn duy cũng như sự hiểu biết tuyệt nhất định của người tiêu dùng về các hàm với cách kết hợp các hàm. Excel gồm sẵn các hàm để bạn có thể: Hàm SUMIF trả về quý hiếm tổng của các ô vừa lòng một điều kiện cho trước. SUMIF(range, criteria, range: là thông số bắt buộc, là vùng ô mà bạn có nhu cầu đánh giá chỉ theo điều kiện để tính tổng. criteria: là tham số bắt buộc, là đk để bạn reviews dải ô phía trước. sum_range: là tham số tùy chọn. Đây là những ô thực tế được mang ra tính tổng nếu các ô tương ứng tại đoạn range vừa lòng điều kiện. Nếu bạn không điền sum_range, Excel đang tính những ô thỏa mãn trong vùng range. Bạn áp dụng hàm SUMIF nhằm tính tổng doanh thu của tất cả các thành phầm thuộc phân các loại Phụ kiện trong list dưới đây. Bạn có cách làm =SUMIF(A3:A222,”Phụ kiện”,C3:C222) Trong đó, A3:A222 là vùng điều kiện; Phụ kiện là đk lọc, vì đấy là văn bản nên chúng ta nhớ để vào dấu ngoặc kép với C3:C222 là vùng tính tổng theo điều kiện.Tổng quan tiền về các hàm tính tổng có điều kiện trong Excel
Các hàm tính tổng có điều kiện trong Excel
Hàm SUMIF
Hàm SUMIF tương đối đơn giản và dễ dàng và chỉ hoàn toàn có thể tính tổng đương nhiên một điều kiện cho trước. Để không ngừng mở rộng điều kiện, chúng ta có thể kết hợp với các hàm không giống ở thông số criteria.
Hàm SUMIFS
Nếu hàm SUMIF chỉ cho phép bạn chế tác một đk cho tổng thì hàm SUMIFS trả về quý hiếm tổng của những ô thỏa mãn nhiều đk cho trước.
Cú phápSUMIFS(sum_range, criteria_range1, criteria1,
Sum_range: là tham số bắt buộc. Là vùng nên tính tổng trong bảng dữ liệu, giá trị trống và quý hiếm text được quăng quật qua.
Criteria_range1: là tham số bắt buộc, là phạm vi cần phải kiểm tra bằng đk criteria1.
Criteria1: là tham số bắt buộc, là tiêu chí khẳng định ô như thế nào trong Criteria_range1 sẽ tiến hành tính tổng
Criteria_range2,criteria2,…: Các phạm vi bổ sung và các tiêu chí links với chúng. Chúng ta cũng có thể nhập tối đa 127 cặp phạm vi/tiêu chí.
Xem chi tiết: Hàm SUMIFS trong Excel Để sản xuất Lập Dashboard: trả lời Từ A Đến Z
Một vào những để ý quan trọng khi sử dụng hàm SUMIFS đó là các bạn phải điền chính xác thứ tự điều kiện cho vùng đối số điều kiện. Excel đang chỉ tính tổng các giá trị thỏa mãn toàn cục các đk được nhập vào mà không hẳn tổng các giá trị vừa lòng một trong số điều kiện đó. Đó hoàn toàn có thể xem là một ưu thế cũng có thể là một giảm bớt của hàm SUMIFS. Như trong ảnh dưới đây, hàm SUMIFS và tính tổng những giá trị lệch giá thỏa mãn trực thuộc phân một số loại Phụ khiếu nại VÀ là Đèn xếp.
Hàm SUMPRODUCT
Hàm SUMPRODUCT trả về tổng của tích các dải ô hoặc mảng tương ứng.
Cú phápSUMPRODUCT(array1,
array1: là tham số bắt buộc. Đối số mảng trước tiên mà bạn muốn nhân những thành phần của nó rồi cùng tổng.
Xem chi tiết: Hàm SUMPRODUCT trong Excel Tính Tổng các Tích rất Hiệu Quả
Lưu ý: Khi bạn có nhu cầu sử dụng một mảng đối số như 1 điều kiện, sử dụng dấu hoa thị (*) cùng với mảng đối số đó. Ví dụ khi chúng ta nhập *(Table1
Hàm SUBTOTAL
Hàm SUBTOTAL là hàm trả về tổng phụ của một chuỗi giá bán trị. Tổng phụ có nghĩa là tổng của một số trong những giá trị thỏa mãn nhu cầu một đk chung làm sao đó.
Cú phápSUBTOTAL(function_num,ref1,
Function_num: là con số khẳng định hàm thực hiện phép tính.
Ref1, Ref2, …: là một hoặc nhiều ô, hoặc hàng ô để tính tổng phụ.
Xem đưa ra tiết: tất Tần Tật Về Hàm SUBTOTAL vào Excel
Lời kết về các hàm tính tổng có đk trong Excel
Trong thực tế, các bạn cần phụ thuộc vào dữ liệu và điều kiện tiến hành tính tổng để chọn lựa hàm hoặc phối kết hợp các hàm đến phù hợp.
Xem thêm: Tổng Hợp Những Câu Chúc Mừng Sinh Nhật Hay Và Ý Nghĩa Cho Mọi Đối Tượng
4 hàm tính tổng có điều kiện trong Excel phổ cập nhất:
Hàm SUMIFHàm SUMIFSHàm SUMPRODUCTHàm SUBTOTALViệc nắm rõ kiến thức nền về tổng thể các hàm tính tổng gồm điều kiện sẽ giúp đỡ bạn tứ duy, sắp tới xếp những hàm cấp tốc và chính xác hơn. Hy vọng rằng với nội dung bài viết này, chúng ta cũng có thể bổ sung, không ngừng mở rộng kiến thức tương tự như kỹ năng để tăng hiệu quả làm việc.
Ngoài ra, nếu công việc của bạn nối liền với Tin học văn phòng công sở nhưng vì quá bận rộn nên bạn không tồn tại thời gian tham gia các lớp học trực tiếp tại chính giữa thì bạn cũng có thể tham khảo khóa Luyện thi MOS online những môn Excel, Word, Power
Point, và Excel AZ từ cơ bạn dạng đến cải thiện và ứng dụng của Trung vai trung phong tin học văn phòng công sở MOS nhé!
Thông Tin Liên Hệ
Trung vai trung phong tin học văn phòng MOS – Viện Đào sản xuất và hỗ trợ tư vấn doanh nghiệp– Đại học Ngoại Thương