EA;m...Ẩn bớt
Hãy nói rằng bạn phải tính tổng các giá trị với nhiều hơn nữa một điều kiện, chẳng hạn như tổng lệch giá sản phẩm trong một khu vực cụ thể. Đây là một trong những trường hợp xuất sắc cho việc thực hiện hàm Sumifs trong công thức.
Bạn đang xem: Hàm tính tổng theo điều kiện
Hãy xem ví dụ này mà cửa hàng chúng tôi có hai điều kiện: shop chúng tôi muốn tổng doanh số bán thịt (từ cột C) ở quanh vùng phía nam (từ cột a).
Đây là 1 công thức bạn có thể sử dụng để acomplish này:
= SUMIFS (D2: D11, A2: A11, "miền Nam", C2: C11, "thịt")
Kết trái là quý giá 14.719.
Hãy coi xét ngặt nghèo hơn sinh hoạt từng phần của công thức.
=SUMIFS là cách làm số học. Nó đo lường và thống kê số, trong trường hòa hợp này nằm trong cột D. Bước đầu tiên là xác định vị trí của những số:
=SUMIFS(D2:D11,
Nói bí quyết khác, bạn muốn công thức tính tổng các số vào cột kia nếu phù hợp với các điều kiện. Phạm vi ô sẽ là đối số đầu tiên trong phương pháp này — đoạn đầu tiên của tài liệu mà hàm đó yêu mong là đầu vào.
Tiếp theo, bạn có nhu cầu tìm dữ liệu thỏa mãn nhu cầu hai điều kiện, vày vậy bạn nhập điều kiện thứ nhất của mình bằng cách chỉ định các chất trí của tài liệu (A2: A11) cùng cũng là nội dung nào — vốn là "miền Nam". Thông tin dấu phẩy giữa những đối số riêng biệt biệt:
=SUMIFS(D2:D11,A2:A11,”Miền Nam”,
Dấu ngoặc kép bao quanh "miền Nam" xác minh rằng tài liệu văn phiên bản này.
Cuối cùng, bạn nhập trở thành số cho đk thứ nhì – phạm vi ô (C2:C11) chứa từ “thịt,” cùng với từ đó (nằm trong vệt ngoặc kép) nhằm Excel hoàn toàn có thể tìm dữ liệu phù hợp. Xong công thức với vệt đóng ngoặc ) , rồi nhấn Enter. Kết quả, một lần nữa, là 14.719.
=SUMIFS(D2:D11,A2:A11,”Miền Nam”,C2:C11,”Thịt”)
Khi chúng ta nhập hàm SUMIFS trong Excel, nếu như bạn không nhớ các đối số, hỗ trợ đã sẵn sàng chuẩn bị ở bàn tay. Sau khi bạn nhập = Sumifs (, cách làm Auto
Complete xuất hiện dưới công thức, cùng với danh sách những đối số theo đồ vật tự cân xứng của họ.
Xem hình hình ảnh của bí quyết Auto
Complete và danh sách những đối số, trong lấy một ví dụ của bọn chúng sum_rangelà D2: D11, cột của các số mà bạn có nhu cầu tính tổng; criteria_range1là A2. A11, cột tài liệu nơi criteria1 "miền Nam" nằm.
Khi chúng ta nhập, phần còn sót lại của trở thành số sẽ mở ra trong bí quyết Auto
Complete (không hiện ở đây); criteria_range2 là C2:C11, cột tài liệu nơi criteria2 “Thịt” có trong đó.
Nếu bạn nhấn vào Sumifs trong bí quyết Auto
Complete, một bài viết sẽ mở ra để giúp đỡ bạn có thêm trợ giúp.
Bạn thử đi
Nếu bạn muốn thử nghiệm cùng với hàm SUMIFS, đó là một số tài liệu mẫu với công thức thực hiện hàm.
Bạn có thể làm câu hỏi với tài liệu mẫu và cách làm ngay tại đây, trong sổ làm việc Excel giành riêng cho web này. Biến đổi các quý hiếm và công thức, hoặc thêm những giá trị và công thức của riêng rẽ bạn với xem thẳng kết quả thay đổi như nạm nào.
Sao chép toàn bộ các ô trong bảng dưới và dính vào ô A1 trong trang tính new trong Excel. Bạn cũng có thể muốn điều chỉnh độ rộng lớn cột giúp xem công thức tốt hơn
| Khu vực | Nhân viên chào bán hàng | Loại | Doanh số |
| Miền Nam | Ito | Nước giải khát | 3571 |
| Miền Tây | Lannin | Sản phẩm bơ sữa | 3338 |
| Miền Đông | Makovec | Nước giải khát | 5122 |
| Miền Bắc | Makovec | Sản phẩm bơ sữa | 6239 |
| Miền Nam | Jordan | Nông sản | 8677 |
| Miền Nam | Lannin | Thịt | 450 |
| Miền Nam | Lannin | Thịt | 7673 |
| Miền Đông | Makovec | Nông sản | 664 |
| Miền Bắc | Lannin | Nông sản | 1500 |
| Miền Nam | Jordan | Thịt | 6596 |
| Công thức |
| Mô tả | Kết quả |
| " = SUMIFS (D2: D11, A2: A11,"Miền Nam", C2: C11, "thịt") | Tổng doanh thu thịt trong | = SUMIFS (D2: D11, A2: A11,"Miền Nam", C2: C11, "thịt") |
Lưu ý: Trang này được dịch auto nên có thể chứa những lỗi về ngữ pháp hoặc ngôn từ không bao gồm xác. Mục đích của chúng tôi là khiến nội dung này trở yêu cầu hữu ích với bạn. Cho cửa hàng chúng tôi biết thông tin này còn có hữu ích tuyệt không? tiếp sau đây là nội dung bài viết bằng tiếng Anh để bạn tham khảo..
Khi bạn nghe thương hiệu hàm SUMPRODUCT trước tiên tiên, nó gồm vẻ giống hệt như một công thức vô dụng triển khai một tổng số thường thì của hoạt động sản phẩm. Nhưng với cách hướng dẫn sau đây thì thực thụ hàm sumproduct tính tổng nhiều điều kiệnkhông hề vô dụng 1 chút nào nhé.
Khái niệm hàm sumproduct là gì?
Sumproduct là hàm gì? Hàm sumproduct tính tổng các điều kiện là một hàm giám sát và đo lường trong các bảng tính trong học Excel. Nó triển khai phép nhân giữa các bộ phận tương ứng của nhị hoặc những mảng và kế tiếp tính tổng của các kết quả nhân đó.
Hàm SUMPRODUCT thường xuyên được áp dụng để tính tổng những giá trị trong số dãy số hoặc để thực hiện các phép tính tương tự như như nhân ma trận vào bảng tính Excel
Ý nghĩa hàm sumproduct vào excel
Hàm sumproduct tính tổng các điều kiệntrong Excel có ý nghĩa sâu sắc quan trọng trong việc triển khai các giám sát phức tạp. Thay thể, ý nghĩa sâu sắc của hàm SUMPRODUCT là:
1. Tính tổng các giá trị nhân
Hàm sumproduct tính tổng những điều kiện được cho phép tính tổng các tác dụng nhân của các phần tử tương ứng trong các mảng khác nhau. Công dụng này để giúp đỡ bạn tính tổng những giá trị nhân vào một hàng số hoặc trong số phép tính tương tự như như nhân ma trận.
2. Áp dụng các tiêu chuẩn lọc dữ liệu
Hàm tính tổng những điều kiệnsumproduct vào Excel có thể được áp dụng để tinh lọc các tiêu chí lọc dữ liệu và tính tổng những giá trị tương xứng với những điều kiện được chỉ định. Bằng phương pháp sử dụng những biểu thức ngắn gọn xúc tích như "", "=", "=", "", chúng ta có thể lọc dữ liệu và tính tổng những giá trị thỏa mãn nhu cầu các tiêu chuẩn mình để ra.
Hàm SUMPRODUCT trong Excel có thể được áp dụng để chọn lọc các tiêu chí lọc dữ liệu và tính tổng những giá trị phù hợp với những điều kiện được chỉ định
3. đo lường và thống kê trên những phạm vi ko liên tục
Một ưu điểm của hàm sumproduct tính tổng nhiều điều kiện là chất nhận được bạn thực hiện thống kê giám sát trên các phạm vi không thường xuyên trong một bảng tính. Chúng ta cũng có thể chỉ định những phạm vi không thường xuyên và hàm SUMPRODUCT sẽ tính tổng các giá trị tương ứng trong số phạm vi mà chúng ta khoanh vùng.
4. Kết phù hợp với các hàm khác thuộc SUMPRODUCT
Không chỉ dùng đơn độc mà hàm tổng có điều kiện SUMPRODUCT hoàn toàn có thể kết phù hợp với các hàm khác trong Excel như IF, SUMIF, COUNTIF và những hàm không giống để thực hiện các thống kê giám sát phức tạp. Bằng cách kết hợp các hàm này, chúng ta cũng có thể tạo ra những công thức mạnh khỏe để phân tích tài liệu và triển khai các đo lường đa dạng.
Kết hợp
SUMPRODUCT với các hàm khác
Tóm lại, hàm cộng có rất nhiều điều kiện trong excelsumproduct là một trong những công cụ đặc biệt quan trọng trong Excel để tính toán, lọc dữ liệu và phân tích dữ liệu. Hàm này còn có tính linh động cao, chất nhận được người dùng sử dụng riêng lẻ hoặc kết hợp với một số hàm khác nhằm đạt được kết quả mình mong mỏi muốn.
Công thức tính của hàm SUMPRODUCT
Về khía cạnh kỹ thuật, hàm sumproduct tính tổng nhiều điều kiện trong Excel đang nhân những số trong số 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 có điều kiện trong Excel rất dễ dàng và dễ dàng hiểu:
= SUMPRODUCT (array1,
Trong đó:- array1: là mảng thứ nhất bắt buộc phải có nếu muốn nhân những thành phần của chính nó rồi cộng tổng lại.
-
- số lượng mảng tối thiểu là 1. Vào trường hợp này, cách làm SUMPRODUCT chỉ dễ dàng là cộng tất cả các phần tử mảng cùng trả về tổng.
- con số mảng về tối đa là 255 vào Excel 2016, Excel 2013, Excel 2010 và Excel 2007 cùng 30 trong số phiên phiên bản Excel trước đó.
- mặc dù hàm tổng nhiều đk SUMPRODUCT chuyển động với các mảng, tuy thế nó không yêu cầu sử dụng phím tắt mảng ( Ctrl + Shift + Enter).. Bạn đối đầu và cạnh tranh một bí quyết SUMPRODUCT theo cách thông thường bằng phương pháp nhấn phím Enter.
Dùng hàm
SUMPRODUCT
Lưu ý:
+ toàn bộ các mảng trong cách làm SUMPRODUCT phải bao gồm cùng con số hàng với cột, nếu như không, các bạn sẽ nhận được #VALUE! lỗi.
+ Nếu bất kỳ đối số mảng nào chứa các giá trị chưa phải là số, bọn chúng sẽ được coi là số không.
+ nếu một mảng là một bài kiểm tra logic, nó sẽ dẫn đến những giá trị TRUE với FALSE. Trong số đông các ngôi trường hợp, chúng ta cần chuyển đổi chúng thành 1 và 0 bằng cách sử dụng toán tử solo nguyên kép (-).
+ Tổng phù hợp không cung cấp các ký kết tự đại diện.
Ví dụ cách sử dụng hàm SUMPRODUCT
Giả sử các bạn có số lượng trong số ô C2:C8, giá trong các ô D2:D8 và bạn muốn tìm đọc tổng số. Nếu như khách hàng đang làm bài kiểm tra toán sinh hoạt trường, bạn sẽ tính tích số lượng với giá cho mỗi mục, sau đó cộng những tổng phụ. Trong Microsoft Excel, chúng ta cũng có thể nhận hiệu quả bằng một công thức SUMPRODUCT duy nhất:=SUMPRODUCT(C2:C8,D2:D8)
Ví dụ về hàm
SUMPRODUCT
Cách thực hiện hàm SUMPRODUCT
Hiện tại tất cả 3 cách thực hiện hàm sumproduct nhằm tính tổng các mã giống nhau, tính tổng theo điều kiện và tính tổng theo điều kiện có kết hợp với OR (hoặc). Từng cách cụ thể như sau:
1. Hàm SUMPRODUCT tính tổng các mã như thể nhau
Bạn rất có thể sử dụnghàm sumproduct excel tính tổng nhiều điều kiện để tính tổng các giá trị vào một hàng nếu điều kiện của chúng thỏa mãn.
Ví dụ, bạn có nhu cầu tính tổng những giá trị trong một dãy A1:A11 nếu bọn chúng giống nhau với mức giá trị trong ô B3, chúng ta có thể sử dụng công thức sau:
=SUMPRODUCT((A1:A11=B3)*(A1:A11))
2. Hàm SUMPRODUCT tính tổng theo điều kiện
Hàm tính tổng có đk trong excel
SUMPRODUCT cũng có thể được áp dụng để tính tổng những giá trị vào một hàng theo một điều kiện.
Ví dụ, bạn có nhu cầu tính tổng những giá trị trong một hàng C2:C7 trường hợp chúng to hơn 6, bạn có thể sử dụng công thức sau:
=SUMPRODUCT((C2:C7>6)*(C2:C7))
Dùng hàm SUMPRODUCT để tính tổng theo điều kiện
3. Hàm SUMPRODUCT tính tổng theo điều kiện có kết hợp với OR (hoặc)
Ngoài thực hiện một điều kiện để tính tổng các giá trị vào một dãy thì bạn có thể kết vừa lòng nhiều điều kiện với nhau. Thời gian này, thay vì dùng hàm SUMPRODUCT tính tổng theo 1 điều kiện thì các bạn sẽ dùng hàm SUMPRODUCT phối kết hợp cùng OR theo ví dụ bên dưới đây:
Tính tổng những giá trị vào một hàng A1:A10 ví như chúng lớn hơn 6 hoặc nhỏ dại hơn 5, chúng ta có thể sử dụng bí quyết sau:
=SUMPRODUCT((C2:C7>6)+(C2:C7Tính tổng nhiều đk với
SUMPRODUCT
Lưu ý: trong số công thức hàm sumproduct tính tổng nhiều điều kiện bên trên, bọn họ sử dụng toán tử "*" để thực hiện phép nhân cùng "+" để tiến hành phép cộng lô ghích OR. Chúng ta cũng có thể điều chỉnh những phạm vi và đk trong phương pháp sao cho cân xứng với mong ước của mình.
Cách sử dụng hàm SUMPRODUCT nâng cao
Hàm SUMPRODUCT rất thiêng hoạt, chúng ta có thể sử dụng hàm này kết phù hợp với một số hàm khác để thực hiện những phép tính phức tạp hơn. Để khiến cho bạn hiểu hơn về kiểu cách dùng hàm sumproduct trong excel, ở chỗ này, cửa hàng chúng tôi sẽ mang một vài ví dụ cải thiện như sau:
1. Đếm số thành phầm thỏa mãn đk so sánh với 1 số chũm thể
Thông thường, bạn sẽ sử dụng hàm đếm có đk COUNTIF để thực hiện đếm số thành phầm theo 1 điều kiện cụ thể. Tuy nhiên thay bởi dùng COUNTIF, bạn có thể dùng phương pháp sumproduct có điều kiện sau để triển khai việc này:
= SUMPRODUCT(-(X:Y>Z))
Trong đó:
- Biểu thức (X:Y>Z) để tiến hành so sánh từng phần tử của mảng với cái giá trị Z
- sau thời điểm so sánh xong từng phần tử trong mảng, hàm Sumproduct hôm nay sẽ biến chuyển dạng: SUMPRODUCT(- FALSE, FALSE, TRUE, TRUE, FALSE)
Lý vì chưng đặt vệt “-” trước mảng với mục tiêu là nhằm biến những giá trị TRUE/ FALSE về dạng số nguyên -1 và 0 thì công dụng mới hoàn toàn có thể chính xác. Hoặc bạn có thể sử dụng 2 lốt “-” để những giá trị TRUE/ FALSE trả về 1 cùng 0.
Ví dụ: Đếm số thành phầm từ ô C2 mang lại C7 vừa lòng điều kiện to hơn 150
=SUMPRODUCT(--(C2:C7>150))= 3
Đếm số thành phầm thỏa mãn điều kiện so sánh với cùng một số gắng thể
2. Đếm số thành phầm khi đối chiếu 2 mảng với nhau
Khi đếm số thành phầm khi cùng đối chiếu hai mảng với nhau thì chúng ta không thể sử dụng hàm COUNTIF. Cầm cố vào đó, bạn nên dùng hàm sumproduct tính tổng nhiều đk theo công thức:
=SUMPRODUCT(-(A:B>C:D))
Trong đó:
- Biểu thức (A:B>C:D) sẽ thực hiện so sánh từng thành phần của cả nhì mảng cùng với nhau. Chũm thể, các bạn sẽ so sánh A với C, B với D
- khi so sánh dứt các phần tử của cả 2 mảng, SUMPRODUCT sẽ thay đổi dạng: SUMPRODUCT(- TRUE, FALSE, FALSE, FALSE, TRUE)
Lý vì đặt lốt “-” trước mảng để mang hàm về dạng SUMPRODUCT(-1, 0, 0, 0, -1), tác dụng bằng -2. Nếu như bạn sử dụng thêm 1 dấu nữa thì hiệu quả trả về đã là 2.
Ví dụ: =SUMPRODUCT(-(D2:D7>C2:C7))=2
Đếm số sản phẩm khi so sánh 2 mảng cùng với nhau
3. Sử dụng hàm SUMPRODUCT nhiều đk để rứa cho hàm COUNTIF
Tính tổng bao gồm 2 đk trong excel là phép toán phổ biến. Để giúp cho bạn hiểu rõ hơn về hàm tính tổng vào excel có đk này, cửa hàng chúng tôi sẽ đem một ví dụ ví dụ về tính tổng theo đk trong excel như sau:
=SUMPRODUCT((A4:A10=”táo”)*(C4:C10>100))=2
Trong đó:
- A4:A10=”táo”: Lọc tổng thể giá trị là “táo”
- C4:C10>100: Lọc các giá trị to hơn 100
- (A4:A10=”táo”)*(C4:C10>100): kết hợp lọc phối hợp các cực hiếm là “táo” và to hơn 100
Một số lỗi khi sử dụng hàm SUMPRODUCT
Khi áp dụng hàm SUMPRODUCT, bạn sẽ thường gặp mặt một vài ba lỗi như sau:
1. Lỗi #VALUE!
Lỗi này thường xảy ra khi các phạm vi hoặc mảng không tồn tại cùng kích thước. Để né lỗi này khi dùng hàm tính tổng theo điều kiện, bạn cần bảo đảm an toàn rằng những phạm vi hoặc mảng bạn sử dụng trong hàm SUMPRODUCT tất cả cùng kích thước.
Lỗi #VALUE! thường xảy ra khi những phạm vi hoặc mảng không có cùng kích thước
2. Lỗi #DIV/0!
Lỗi #DIV/0! xảy ra khi bạn chia một số trong những cho 0 trong hàm sumproduct tính tổng những điều kiện. Bạn phải kiểm tra lại những phép tính trong hàm SUMPRODUCT để bảo đảm an toàn không gồm chia mang đến 0 xảy ra.
3. Lỗi #NAME?
Lỗi này xảy ra khi Excel không nhận ra tên hàm SUMPRODUCT do bạn viết sai bao gồm tả hoặc thiếu hụt chữ trong từ “SUMPRODUCT”. Bí quyết khắc phục lỗi này là bạn phải nhập đúng cú pháp với viết đúng đắn tên hàm SUMPRODUCT.
Lỗi #NAME? xẩy ra khi Excel không nhận thấy tên hàm SUMPRODUCT do bạn viết sai thiết yếu tả hoặc thiếu hụt chữ trong từ “SUMPRODUCT”
4. Lỗi #REF!
Lỗi #REF! xảy ra khi những phạm vi hoặc mảng bạn sử dụng trong hàm SUMPRODUCT vẫn bị đổi khác hoặc xóa bỏ. Hãy chất vấn lại những phạm vi và mảng vào hàm SUMPRODUCT để bảo đảm an toàn chúng không bị biến hóa hoặc xóa khỏi so với dịp ban đầu.
5. Lỗi #NUM!
Nguyên nhân dẫn tới Lỗi #NUM! là khi những giá trị trong số phạm vi hoặc mảng bạn áp dụng trong hàm SUMPRODUCT không hợp lệ. Biện pháp tránh lỗi này là các bạn cần bảo đảm an toàn các giá trị bạn thực hiện trong hàm SUMPRODUCT đúng định dạng và tương xứng với yêu cầu của công thức.
6. Lỗi #N/A
Lỗi #N/A xẩy ra khi không tìm thấy giá bán trị cân xứng trong những phạm vi hoặc mảng được áp dụng trong hàm SUMPRODUCT. Bí quyết khắc phục là bạn cần kiểm tra lại các giá trị và đk trong hàm SUMPRODUCT để bảo đảm an toàn tìm thấy quý giá phù hợp.
Lỗi #N/A xẩy ra khi không tìm kiếm thấy giá chỉ trị cân xứng trong các phạm vi hoặc mảng được sử dụng trong hàm SUMPRODUCT
Tóm lại, khi gặp gỡ lỗi trong những lúc dùng hàm SUMPRODUCT, bạn cần kiểm tra lại cách làm và những phạm vi, mảng, quý hiếm được thực hiện trong hàm SUMPRODUCT. Đồng thời, hãy bảo đảm an toàn rằng các đối tượng người tiêu dùng được tham chiếu vào hàm SUMPRODUCT ko bị biến đổi hoặc xóa bỏ.
Xem thêm: Máy Phun Sơn Cầm Tay Đài Loan Giá Tốt Tháng 5, 2023, Súng Phun Sơn Khí Nén Cầm Tay Kingtony Ki
Ngoài những bước mà wu.edu.vn đã chia sẻ, nếu như bạn đọc ý muốn tìm hiểu cụ thể hơn nữa về cách sử dụng hàm sumproduct tính tổng nhiều đk trong Excel thì hoàn toàn có thể tham khảo đoạn phim bài giảng sau:
Hướng dẫn cách sử dụng hàm SUMPRODUCT trong Excel
Như vậy, wu.edu.vn đang hướng dẫn chúng ta đọcvề hàm hàm SUMPRODUCT vàcách tính tổng có điều kiện trong Excelđúng và nhanh chóng, hy vọng nội dung bài viết này để giúp đỡ ích được nhiều cho bạn trong thừa trình làm việc sớm hoàn thành công việc của mình. Dường như bạn đọc nhiệt tình tới chứng chỉ IC3 giúp đỡ bạn trong quá trình cũng như thăng tiến vào sự nghiệp, hãy xem thêm nhé. Mày mò ngay cuốn sách Excel từ cơ phiên bản đến nâng cao giúp đỡ bạn nắm chắc kiến thức Excel.