Trong nội dung bài viết này, học Excel Online sẽ phân tích và lý giải sự khác hoàn toàn giữa các hàm SUMIF cùng SUMIFS trong excel theo cú pháp và cách thực hiện của chúng, cạnh bên đó, cũng cung ứng một số lấy một ví dụ về phương pháp để tính tổng các giá trị có tương đối nhiều điều khiếu nại đồng thời xẩy ra (AND) hoặc tất cả không đồng thời xẩy ra (OR) vào phiên bản Excel 2013, 2010, 2007, 2003 và trước nữa.
Bạn đang xem: Hướng dẫn sử dụng hàm sumifs trong excel 2010
Sử dụng SUMIFS và SUMIF trong Excel – một số trong những điều bắt buộc nhớ:Làm núm nào để thực hiện SUMIFS vào Excel – ví dụ công thức
Sử dụng hàm SUMIF có không ít điều kiện ORExcel SUMIFS có tương đối nhiều điều khiếu nại ORSử dụng hàm SUM trong số công thức mảng
Hàm SUMIF – cú pháp và bí quyết sử dụng:
Hàm SUMIF được sử dụng để tính tổng tất cả điều kiện, dựa trên một điều kiện. Chúng tôi đã bàn luận về cú pháp của nó một cách cụ thể trong nội dung bài viết trước, vày vậy, hiện giờ tôi hãy cho chính mình một bản tóm tắt nhanh.SUMIF (range, điều kiện,
Như các bạn thấy, cú pháp của hàm Excel SUMIF chỉ cho phép một điều kiện. Tuy nhiên, bên trên, shop chúng tôi có nói rằng Excel SUMIF có thể được áp dụng để tính tổng các giá trị với nhiều điều kiện. Làm nạm nào mà rất có thể được? Thực tế, bạn phải thêm các hiệu quả của vài hàm SUMIF với sử dụng các công thức SUMIF với những điều kiện mảng, như trong lấy ví dụ tiếp theo.
Hàm SUMIFS – cú pháp và bí quyết sử dụng:
Bạn sử dụng hàm SUMIFS vào Excel để tìm một tính tổng có mức giá trị các giá trị dựa vào nhiều điều kiện. Hàm SUMIFS trong excel vẫn được reviews trong Excel 2007, vị vậy chúng ta cũng có thể sử dụng nó trong tất cả các phiên bạn dạng của Excel 2013, 2010 và 2007.
So cùng với SUMIF, thì cú pháp SUMIFS phức hợp hơn một chút:
SUMIFS (sum_range, criteria_range1, criteria1,
3 đối số trước tiên là bắt buộc, những range được bổ sung và các điều kiện tương quan tới bọn chúng thì được tùy chọn.
sum_range – một hoặc các ô tính tổng, mang tính chất bắt buộc. Đây rất có thể là một ô duy nhất, một dải ô hoặc dải gồm tên. Chỉ có các ô chỉ cất số mới được xem tổng; Còn quý hiếm ô trống và quý giá văn bạn dạng thì có khả năng sẽ bị bỏ qua.criteria_range1 – range trước tiên được đánh giá theo những điều khiếu nại liên quan, mang ý nghĩa bắt buộc.criteria1 – điều kiện thứ nhất phải được đáp ứng, là đối số cần phải có. Bạn có thể cung cấp các điều kiện dưới dạng một số, biểu thức logic, tham chiếu ô, văn bạn dạng hoặc một hàm Excel khác. Ví dụ bạn cũng có thể sử dụng những điều khiếu nại như 10, “> = 10”, A1, “cherries” hoặc TODAY ().criteria_range2, criteria2, … – đấy là các dải được cấp dưỡng và đk liên quan tới các dải này, do các bạn tùy chọn. Bạn cũng có thể sử dụng buổi tối đa 127 dải / đk trong phương pháp SUMIFS.
Chú ý: Hàm SUMIFS trong excel chuyển động với biểu thức logic với AND, nghĩa là mỗi ô trong đối số sum_range chỉ được xem tổng nếu toàn bộ các điều kiện được hướng dẫn và chỉ định là đúng cho ô đó.
Và bây giờ, chúng ta hãy xem hàm SUMIFS hoạt động với nhì điều kiện. Giả sử bạn có một bảng liệt kê các lô sản phẩm trái cây từ những nhà cung cấp khác nhau. Bạn có tên quả vào cột A, tên trong phòng cung cung cấp trong cột B và con số trong cột C. Bạn muốn tìm ra số tiền liên quan đến quả và nhà cung cấp, ví dụ: toàn bộ táo (apples) được cung ứng bởi Pete.
Khi ai đang tiếp thu một cái mới mẻ, các bạn nên bước đầu với phần nhiều điều 1-1 giản. Vị vậy, nhằm bắt đầu, hãy xác minh tất cả những đối số cho cách làm SUMIFS của chúng tôi:
sum_range – C2: C9criteria_range1 – A2: A9criteria1 – “apples”criteria_range2 – B2: B9criteria2 – “Pete”Bây giờ tập hòa hợp các thông số kỹ thuật trên, và bạn sẽ nhận được cách làm SUMIFS sau:
= SUMIFS (C2: C9, A2: A9, "táo", B2: B9, "Pete")
Để khiến cho việc sửa đổi công thức đơn giản và dễ dàng hơn, bạn cũng có thể thay thế các tiêu chuẩn văn bản “apples” với “Pete” bằng những tham chiếu ô. Trong trường thích hợp này, các bạn sẽ không phải biến đổi công thức để thống kê giám sát lượng hoa quả khác xuất phát điểm từ một nhà cung ứng khác nhau:
= SUMIFS (C2: C9, A2: A9, F1, B2: B9, F2)
Mời chúng ta theo dõi 2 videos sau đây và hãy nhớ là download tư liệu kèm theo dưới để làm rõ hơn về hàm SUMIFS nhé!
Video nâng cấp về 2 hàm SUMIF cùng SUMIFS sẽ giúp chúng ta sử dụng các ký tự dấu sao và hỏi chấm nhằm tính tổng nâng cao:
Sử dụng SUMIFS với SUMIF vào Excel – một số trong những điều bắt buộc nhớ:
Vì mục tiêu của lí giải này là bao hàm tất cả các cách rất có thể có, nhằm tính tổng những giá trị thỏa một trong những điều kiện, bọn họ sẽ đàm đạo các lấy một ví dụ công thức đối với tất cả hai hàm – SUMIFS với SUMIF với tương đối nhiều điều kiện. Để thực hiện chúng một cách chính xác, bạn phải hiểu rõ nhị hàm này còn có điểm gì chung và phương thức chúng không giống nhau như nạm nào.
Mặc mặc dù nét tầm thường thì khá ví dụ – tương đồng về điểm đến lựa chọn cho kết quả cuối thuộc và những tham số – trong khi sự khác hoàn toàn dù ko rõ rang dẫu vậy vẫn rất phải thiết.
1. Trình tự của những đối số
Trong những hàm Excel SUMIF và SUMIFS, thiết bị tự những đối số là không giống nhau. Gắng thể, sum_range là tham số đầu tiên trong SUMIFS, tuy nhiên lại đứng vị trí thứ 3 trong công thức SUMIF.
Khi bước đầu học, bạn sẽ cảm thấy có vẻ như như Microsoft đã cầm ý làm phức tạp hóa cho những người học và người tiêu dùng nó. Mặc dù nhiên, khi xem xét kỹ hơn, các bạn sẽ thấy tại sao thực chất ẩn dưới nó. Vấn đề là sum_range là tùy lựa chọn trong SUMIF. Nếu bạn bỏ qua nó, không tồn tại vấn đề, SUMIF cách làm của bạn sẽ tính tổng các giá trị vào range (tham số đầu tiên).
Trong SUMIFS, sum_range là rất đặc trưng và là bắt buộc, với đó là tại sao tại sao nó đến trước. Rất có thể những bạn của Microsoft nghĩ về rằng sau khoản thời gian thêm những dải/điều kiện chăm chú thứ 10 hoặc 100, thì gồm ai đó rất có thể quên xác định dải để tính tổng:)
Tóm lại, nếu như khách hàng đang xào nấu và chỉnh sửa những hàm này, hãy đảm bảo bạn đặt các thông số theo sản phẩm công nghệ tự đúng.
2. Size của đối số sum_range và criteria_range
Trong hàm SUMIF, đối số sum_range không độc nhất thiết phải gồm cùng size với đối số range, miễn là các bạn có ô bên trên bên trái. Vào hàm SUMIFS, từng criteria_range cần chứa cùng một số trong những hàng và cột như thông số sum_range.
Ví dụ, cách làm = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại kết quả đúng bởi Excel chỉ coi ô phía bên trên bên trái trong đối số sum_range (C2 trong ví dụ này là đúng) với sau đó bao gồm số cột và hàng hệt như kích thước của đối số range.
Công thức SUMIFS: = SUMIFS (C2: C9, A2: A9, "apples", B2: B10, "Pete") sẽ báo lỗi # VALUE! vì criter_range2 (B2: B10) không khớp với criteria_range1 (A2: A9) và sum_range (C2: C9).
Có vẻ như bọn họ đã tiếp cận tạm chấm dứt lý thuyết rồi, nên mục sau bọn họ sẽ đưa sang làm việc thực hành (chính là những ví dụ công thức:)
Đăng ký ngay: Học Excel dành cho tất cả những người đi làm
Làm nắm nào để sử dụng SUMIFS trong Excel – lấy ví dụ như công thức
Vừa nãy, chúng ta đã đàm đạo về một phương pháp SUMIFS đơn giản dễ dàng với hai điều kiện văn bản. Với phương pháp tương từ bỏ như thế, chúng ta có thể sử dụng Excel SUMIFS với tương đối nhiều điều khiếu nại thể hiện bởi số, ngày, biểu thức logic, và những hàm Excel khác.
Ví dụ 1. Bí quyết SUMIFS với toán tử so sánh
Trong bảng hỗ trợ trái cây bên dưới đây, trả sử, bạn muốn tính tổng toàn bộ các lượng hàng đã có Mike cung ứng với số lượng. Tự 200 trở lên. Để làm cho điều này, bạn áp dụng toán tử so sánh “lớn rộng hoặc bằng” (> =) trong số điều khiếu nại 2 với nhận được cách làm SUMIFS sau:
= SUMIFS (C2: C9, B2: B9, “Mike”, C2: C9, “> = 200”)
Lưu ý: Hãy xem xét rằng trong các công thức SUMIFS, những biểu thức lôgic với các toán tử đối chiếu phải luôn luôn luôn được đặt trong vết nháy kép (“”).
Chúng tôi sẽ đề cập chi tiết tất cả các toán tử so sánh rất có thể khi luận bàn về hàm Excel SUMIF, và các chúng cũng có thể sử dụng trong điều kiện SUMIFS. Ví dụ: Trả về cực hiếm tổng của tất cả các giá chỉ trị trong những ô C2: C9 mà to hơn hoặc bằng 200 và nhỏ dại hơn hoặc bởi 300.
= SUMIFS (C2: C9, C2: C9, “> = 200”, C2: C9, “Ví dụ 2. Sử dụng công thức SUMIFS với ngày Trong ngôi trường hợp bạn muốn tính tổng các giá trị với rất nhiều điều kiện dựa trên ngày hiện tại tại, hãy sử dụng hàm TODAY () trong điều kiện của hàm SUMIFS của bạn, như được trình bày bên dưới. Công thức dưới đây tính tổng mức vốn trong cột D ví như ngày khớp ứng trong cột C rơi vào tình thế khoảng thời gian 7 ngày vừa qua, có bao gồm ngày hôm nay: Khi phân tích report và tài liệu khác, chúng ta thường rất cần phải tính tổng các giá trị tương ứng với ô trống hoặc không trống. Tính tổng giá bán trị trong số ô C2:C10 nếu các ô tương xứng với nó trong cột A cùng B là ô hoàn toàn trống. Tính tổng các giá trị trong số ô C2:C10 (có cùng các điều kiện) như bí quyết ở trên, mà lại có bao gồm các chuỗi trống. Tính tổng giá chỉ trị trong những ô C2:C10 nếu những ô khớp ứng với nó trong cột A cùng B không là ô trống, có bao gồm các ô với chuỗi trống. =SUM((C2:C10) * (LEN(A2:A10)>0)*(LEN(B2:B10)>0)) Tính tổng giá chỉ trị trong các ô C2:C10 nếu những ô tương ứng với nó vào cột A với B không là ô trống, cùng không bao hàm các ô cùng với chuỗi trống.
Và bây giờ, hãy cùng xem cách bạn có thể sử dụng cách làm SUMIFS với đk “trống” với “không trống” trên dữ liệu thực như vậy nào:Điều kiện Mô tả Công thưc ví dụ Những ô trống “=” Tính tổng các giá trị mà gồm ô trống tương xứng (hoàn toàn không cất dũ liệu – không công thức, cùng chuỗi có 0 kí tự) =SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”) “” Tính tổng các giá trị khớp ứng với những ô white “nhận định trực quan”, bao hàm các giá trị chứa các chuỗi trống rỗng được trả về bởi một vài hàm Excel không giống (ví dụ: ô tất cả công thức như = “”). =SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”) Những ô ko trống “” Tính tổng các giá trị nhưng mà có những giá trị khớp ứng là những ô ko trống, cùng có bao hàm chuỗi tất cả chiều dài bởi 0 =SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”) SUM-SUMIFhay
SUM / LENTính tổng những giá trị nhưng có các giá trị khớp ứng là các ô không trống, với không bao hàm chuỗi có chiều dài bằng 0 =SUM(C2:C10) – SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)
Giả sử chúng ta có ngày mua hàng trong cột B, ngày giao hàng trong cột C và số lượng trong cột D. Làm cố nào để chúng ta tính được tổng số sản phẩm chưa được giao? Nghĩa là bạn muốn biết tổng các giá trị tương xứng với các ô không rỗng trong cột B và những ô rỗng trong cột C.
Giải pháp là sử dụng công thức SUMIFS cùng với 2 điều kiện:
= SUMIFS (D2: D10, B2: B10, “”, C2: C10, “=”)
Ví dụ 4: bí quyết viết điều kiện hàm SUMIFS
Đề bài: Cho bảng dữ liệu tại vùng F2:H10, tính các tác dụng tại vùng B4:D7 theo các điều kiện tương ứng tại cột A (Mã) và chiếc 3 (Ngày)Bước 1: Phân tích đề bài
Đề bài bác yêu mong tính tổng theo 2 điều kiện là Ngày với Mã, do đó ta không thể thực hiện hàm SUMIF nhưng phải áp dụng hàm SUMIFS (ở đây chúng ta bỏ qua các hàm khác cơ mà chỉ xét tính ứng dụng của hàm SUMIF/SUMIFS)Điều kiện cần tính chưa hẳn cố định mà tùy biến, viết 1 hàm SUMIFS tại B4 rồi copy hàm đó lịch sự các ô khác để tínhĐiều khiếu nại Mã và Ngày có thể thay đổi trong công thức.
Bước 2: Xây dựng công thức SUMIFSCấu trúc hàm SUMIFS(sum_range, Criteria_range1, Criteria1, …)
Sum_range: là cột con số trong vùng bảng dữ liệu F2:H10Criteria_range1: là cột Mã vào vùng bảng tài liệu F2:H10Criteria1: là điều kiện về Mã (để tương ứng cùng với Criteria_range1), với ô B4 là điều kiện tại ô A4Criteria_range2: là cột Ngày vào vùng bảng dữ liệu F2:H10Criteria2: là điều khiếu nại về Ngày (tương ứng với Criteria_range2), với ô B4 là điều khiếu nại tại ô B3Do đó công thức tại ô B4 rất có thể viết như sau:
Khi làm cho báo cáo, các bước tính toán và đối chiếu số liệu là một kỹ năng cơ bản để có thể lập ra một bản báo cáo chính xác, đầy đủ thông tin. Trong bài viết này, họ sẽ thuộc nhau khám phá cách áp dụng hàm SUMIFStrong Excel để tính toán hiệu quả của báo cáo.
Kết hòa hợp hàm SUMIFS và hàm COUNTIFS trong report tổng hợp
Hàm SUMIFS
Hàm SUMIFS và hàm COUNTIFS là nhị hàm tính toán cơ phiên bản của Excel. Nhị hàm này thuộc đội hàm toán học và thống kê, vì chưng vậy chúng rất hữu ích trong câu hỏi tổng hợp, thống kê số liệu trong vấn đề làm báo cáo.
Hàm SUMIFS là 1 hàm cách tân và phát triển từ hàm SUM. Nếu hàm SUM dùng để tính tổng những số liệu nhất mực trong tệp tin dữ liệu, thì hàm SUMIF được dùng để tính tổng những dữ liệu theo một điều kiện nhất định. Vậy nên, đối với những bản báo cáo tổng hợp, cần đo lường và thống kê với nhiều đk kèm theo, bọn họ sẽ bắt buộc dùng mang đến hàm SUMIFS.
Hàm COUNTIFS
Bên cạnh đó, cùng với những report cần tổng hòa hợp số lượng, thì hàm đếm ô COUNTIFS sẽ là 1 trong những công cầm rất hữu ích. Hàm COUNTIFS có chức năng đếm các ô dữ liệu theo khá nhiều điều kiện. Bởi thế với những bản báo cáo phải tổng hợp tác dụng theo nhiều điều kiện của yêu cầu, hàm COUNTIFS hoàn toàn có thể đếm rất dễ dãi và bao gồm xác.
Với cách áp dụng hàm COUNTIFS trong việc giám sát và đo lường để giao hàng cho báo cáo tổng hợp, bọn họ sẽ cùng tò mò trong bài viết lần sau nhé.
Ví dụ hàm SUMIFS trong báo cáo tổng hợp
Với hàm SUMIFS trong report tổng hợp, chúng ta sẽ cùng nhau làm một ví dụ rõ ràng để thấy được giải pháp hàm SUMIFS được ứng dụng như vậy nào.
Cú pháp hàm SUMIFS
Công thức hàm SUMIFS như sau:
=SUMIFS(sum_range, criteria_range1, criteria1,...)
Trong đó:- sum_range là tham số chỉ vùng tài liệu cần tính tổng- criteria_range1 là tham số chỉ vùng điều kiện- criteria1 là tham số chỉ điều kiện, áp dụng cho vùng điều kiện tương ứng của nó
Trong cú pháp hàm SUMIFS, bọn họ sẽ có khá nhiều điều kiện, các bạn cần ghi không thiếu tất cả những vùng đk kèm đk tương ứng kia vào công thức.
Ứng dụng hàm SUMIFS trong báo cáo
Chúng ta có các bảng dữ liệu như sau
Trong ví dụ này, chúng ta sẽ cùng thực hành sử dụng hàm SUMIFS để tính tổng theo một vài điều kiện nhất quyết của bạn dạng báo cáo tổng hợp. Trong yêu cầu của bản báo cáo tổng hợp này bọn họ sẽ thấy tất cả 3 điều kiện, chính là tính tổng theo tên từng khách hàng, theo tháng và theo năm.
Vì bọn họ có 3 điều kiện cần theo khi tính tổng, vậy nên họ sẽ sử dụng hàm tính tổng nhiều đk là hàm SUMIFS. Chúng ta sẽ nhập cách làm hàm SUMIFS vào ô trước tiên cần tính tổng. Ô đầu tiên họ cần tính tổng cộng lượng của khách hàng là doanh nghiệp A, vào tháng 4 của năm 2020.
Đầu tiên chúng ta cần lựa chọn vùng tính tổng, chính là cột con số ở sheet số liệu. Bạn cũng có thể chọn tới các ô có chứa tài liệu hoặc có thể chọn toàn cục cột đó, như vậy nếu khách hàng thêm số liệu vào, công dụng cũng sẽ tự động hóa cập nhật theo.
Tiếp theo là những điều kiện nhưng mà bạn quan trọng lập để hàm SUMIFS có thể tính toán theo các điều kiện đó, sinh hoạt ví dụ này chúng ta có 3 đk là tên khách hàng hàng, tháng cùng năm. Các bạn có thểt sắp đến xếp điều kiện nào trước cũng được. Ở trên đây mình vẫn nhập cách làm của 3 điều kiện này như sau:
Trong cách làm hàm SUMIFS trên, chúng ta đã tất cả đủ các tham số cần thiết là vùng tính tổng, các vùng điều kiện và điều kiện kèm theo như yêu cầu của phiên bản báo cáo. Với những vùng đk và đk này, chúng ta cần linh hoạt đổi khác theo đúng vùng điều kiện cũng giống như điều kiện trong bản báo cáo của bản thân nhé.
Xem thêm: Đề Thi Sát Hạch Bằng Lái Xe Hạng A1 Online 2022, Đề Thi Thử Bằng Lái Xe Máy A1 Online 2022
Kết trái sau khi các bạn đã áp dụng đúng các điều kiện với vùng điều kiện, hàm SUMIFS sẽ tính tổng con số hàng hoá của đúng các điều kiện là tên khách hàng, mon mua, năm cài đặt và trả về hiệu quả sau
Để kiểm tra lại hiệu quả trên đang đúng hay chưa, các chúng ta cũng có thể lọc file tài liệu gốc và cần sử dụng hàm tính tổng SUM để chất vấn xem tổng con số mà bạn cần tính tất cả đúng không
Tổng kết
Như vậy qua bài viết trên, họ đã cùng nhau tìm hiểu về hàm SUMIFS, trong những hàm liên tiếp được thực hiện trong các report tổng hợp. Cùng với hàm SUMIFS tính tổng dựa trên nhiều đk đi kèm, bạn có thể dễ dàng lọc những điều kiện bạn thích và tính toán số liệu báo cáo một cách hối hả và thiết yếu xác. Chúc các bạn thực hành thành công xuất sắc và ứng dụng kết quả trong công việc.
Ngay bây giờ hãy thuộc luyện tậpthêm vớibộ bài tập Excel có đáp áncủa tmec.edu.vn nhằm nằm lòngtin học văn phòng nhé!