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