Một trong những hàm thống kê được sử dụng nhiều khi làm việc trong excel là hàm countifs. Hàm này dùng để đếm các ô, tính toán số lượng theo một điều kiện nhất định (ngày tháng, số, text hoặc ô chứa dữ liệu). Để nắm rõ hơn về hàm countifs, lệnh countifs và cách sử dụng hàm countifs trong excel 2010 bạn hãy tham khảo bài viết dưới đây để hiểu rõ hơn về hàm countifs nhé!
Hiểu rõ hơn về hàm countifs trong excel 2010
Hàm COUNTIFS dùng để đếm ô không thỏa mãn các điều kiện cho trước trong excel. Đây là một trong những hàm thống kê được sử dụng nhiều trong Excel, là hàm cao cấp của hàm COUNT chỉ đếm ô với 1 điều kiện cho trước mà thôi. Khi sử dụng hàm COUNTIFS, người dùng dễ dàng tìm ra ô kết quả thỏa mãn các điều kiện được đưa ra trong yêu cầu. Các điều kiện được đưa ra có thể là số, ngày tháng, text hay ô chứa dữ liệu Hàm COUNTIFS trong Excel 2010. Và hàm này dùng để đếm ô không thỏa mãn các điều kiện cho trước.
Công thức áp dụng và cách sử dụng hàm countifs trong excel 2010
Hàm COUNTIFS trong excel 2010 có công thức tính khái quát như sau:
=COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)
criteria_range1 – xác định điều kiện vùng chọn đầu tiên cần áp dụng điều kiện (criteria1), giá trị bắt buộc.
criteria1 – đặt điều kiện áp dụng đầu tiên cho vùng chọn, giá trị bắt buộc có thể là dạng số, ô tham chiếu, chuỗi văn bản, một mảng hoặc một hàm Excel khác. Tiêu chí này sẽ xác định những ô tính nào sẽ được thực hiện đếm và có thể biểu diễn là 10, “<=32”, A6, “sweets”.
[criteria_range2, criteria2]… – đây là cặp vùng chọn có các điều kiện bổ sung, tùy chọn. Bạn có thể nhập tối đa tới 127 cặp vùng chọn điều kiện trong công thức.
Bạn sẽ không cần phải thuộc công thức hàm COUNTIFS bởi trong Microsoft Excel sẽ hiển thị các đối số của hàm khi bạn bắt đầu gõ. Đối số sẽ được tô đậm khi bạn nhập hàm.
Lưu ý khi sử dụng hàm countifs trong excel 2010
- Mỗi vùng chọn bổ sung phải cùng số hàng và cột như vùng dữ liệu đầu tiên (đối số Criteria_range1) và vùng chọn không cần thiết phải liền kề nhau.
- Nếu điều kiện là một tham chiếu đến một ô trống thì hàm COUNTIFS sẽ xử lý coi ô trống như một giá trị bằng không.
- Hàm COUNTIFS có thể sử dụng để đếm các ô trong một phạm vi đơn lẻ trong nhiều vùng dữ liệu với nhiều điều kiện hoặc trong một điều kiện duy nhất. Trong trường hợp thứ hai, những ô tính nào mà đáp ứng được tất cả các điều kiện mới được đếm.
Cách đếm số trong một khoảng cụ thể sử dụng hàm countifs trong excel 2010
Công thức áp dụng: Sử dụng hàm COUNTIFS trong excel 2010 để đếm các ô trong khoảng số
Để tìm giữa 5 và 10 có bao nhiêu số (không bao gồm 5 và 10)chứa trong các ô C2 đến C10, áp dụng sử dụng công thức này: =COUNTIFS(C2:C10,”>5″,C2:C10,”<10″).
Nếu muốn đếm bao gồm cả số 5 và 10, sử dụng “lớn hơn hoặc bằng” hoặc “nhỏ hơn hoặc bằng”: =COUNTIFS(B2:B10,”>=5″,B2:B10,”<=10″)
Cách sử dụng lệnh countifs trong excel với nhiều tiêu chí
Cách đếm ô tính với nhiều tiêu chí (AND logic):
Công thức 1. Áp dụng công thức COUNTIFS với các tiêu chí khác nhau
Giả sử bạn có một danh sách các loại quả như trong hình bên dưới. Bạn muốn biết giá trị số hàng tồn kho (giá trị cột B lớn hơn 0) nhưng chưa bán được (giá trị cột C bằng 0).
Để tính được số hàng tồn kho chúng ta sử dụng công thức này:
= COUNTIFS ($B$2: $B$7, “> 0”, $C$2: $C$7, “= 0”)
Kết quả cho ra là 2 (“Cherries” và “Lemons”):
Công thức 2. Sử dụng công thức hàm COUNTIFS trong excel 2010 với các tiêu chí giống nhau
Khi bạn muốn đếm các mục có các tiêu chí giống nhau, bạn vẫn phải xếp mỗi cặp criteria range / criteria một cách riêng lẻ.
Ví dụ: Giả sử bạn muốn đếm các mục chứa 0 cả trong cột B và cột C:
= COUNTIFS ($B$2: $B$7, “= 0”, $C$2: $C$7, “= 0”)
Kết quả trả về 1 vì chỉ có “Grapes” có giá trị “0” trong cả hai cột.
Sử dụng một công thức đơn giản hơn với một criteria range duy nhất như COUNTIFS(B2: C7, “= 0”) sẽ đưa ra một kết quả khác trên – tổng số các ô tính trong khoảng $B$2:$C$7 chứa 0 (trong ví dụ này là 4).
Công thức đếm số ô tính với nhiều tiêu chí( or logic)
Giả sử bạn muốn tính các đơn đặt hàng đang trong trạng thái “Cancelled” và “Pending“. Bạn chỉ cần viết 2 công thức hàm COUNTIF bình thường nó sẽ đưa ra kết quả:
Công thức như sau: =COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)
Trong trường hợp mỗi hàm chứa nhiều hơn một điều kiện thì sử dụng hàm COUNTIFS thay vì sử dụng hàm COUNTIF.
Ví dụ: Giả sử muốn đếm số đơn đặt hàng trong trạng thái“Cancelled” và “Pending” đối với “Apples”.
Áp dụng công thức sau: =COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,”Cancelled”)+COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,”Pending”)
Các ký tự đại diện khi sử dụng COUNTIFS
Bạn có thể sử dụng các ký tự đại diện trong các công thức excel:
- Dấu hỏi (?) – khớp với bất kỳ ký tự đơn trong các tiêu chí. Sử dụng nó để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự đại diện nhất định.
- Dấu hoa thị (*) – khớp với bất kỳ dãy ký tự nào, bạn dùng nó để đếm ô chứa một từ cụ thể hoặc một k1 tự trong ô.
Lưu ý. Nếu bạn muốn đếm các ô chứa dấu hỏi hoặc dấu hoa thị, gõ dấu ngã (~) trước dấu hoa thị hoặc dấu chấm hỏi. Giả sử Trong cột A bạn chứa danh sách các dự án. Bạn muốn biết trong số đó có bao nhiêu dự án đã ký kết, tức là cột B có chứa tên người. Hãy thêm một điều kiện thứ hai – End date trong cột D. Công thức áp dụng là =COUNTIFS(B2:B10,”*”,D2:D10,”<>”&””)). Ngoài ra, bạn không thể sử dụng ký tự đại diện trong tiêu chí thứ 2 vì cột D chứa kiểu dữ liệu ngày tháng chứ không phải dạng text. Đây cũng chính là lý do tại sao bạn sử dụng tiêu chí “<>”&”” để tìm ô chứa dữ liệu.
Lời kết
Hy vọng những công thức hướng dẫn sử dụng hàm countifs trong excel 2010 trong bài viết trên sẽ giúp các bạn áp dụng được vào trong công việc hàng ngày của mình. Cảm ơn các bạn đã theo dõi bài viết “ hướng dẫn sử dụng hàm countifs trong excel của chúng tôi”
