24/06/2020 - View : 708
Hàm đếm có điều kiện COUNTIF và COUNTIFS trong excel được thực hiện như thế nào ? các điều kiện được đưa ra bằng cách nào hãy cùng xosothantai tìm hiểu chi tiết.
Công thức hàm Countifs : = COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)
Trong đó : criteria_range1 là phạm vi đầu tiên áp dụng điều kiện đầu tiên (criteria1), bắt buộc.
criteria1 ( tiêu chí 1 ) – đặt điều kiện cho 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, bắt buộc. Tiêu chí này xác định những ô tính nào sẽ được đếm và có thể biểu diễn là 10, “<=32”, A6, “sweets”.
Hàm có điều kiện trong Excel
[criteria_range2, criteria2]… : Là các điều kiện bổ sung, tùy chọn này có thể thêm tới 127 điều kiện.
Thực tế, bạn cũng không cần phải thuộc lòng cú pháp của hàm COUNTIFS , bởi Microsoft Excel sẽ hiển thị các đối của của hàm khi bạn bắt đầu gõ.
Lưu ý: Hàm COUNTIFS không dùng được ở các phiên bản Microsoft Office thấp hơn 2007
Cách sử 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 sản phẩm như sau : Để kiểm tra 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
Để thực hiện cách đếm này, công thức đưa ra sẽ là
= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”) =>> Kết quả nhận được là 2 (“Cherries” và “Lemons”):
( Lấy vùng dữ liệu chứa số hàng tồn kho với điều kiện là “>0”, tiếp theo lấy vùng điều kiện số hàng đã bán Sold với điều kiện “=0” )
Cách sử dụng công thức COUNTIFS với các tiêu chí giống nhau
Trường hợp bạn muốn đếm nhưng mục có tiêu chí giống nhau thì bạn vẫn cần cũng cấp những cặp criteria_range / criteria một cách riêng lẻ.
Ví dụ : Để đếm các giá trị bằng 0 ở cả hai cột B và C ta sẽ có công thức như sau = COUNTIFS ($B$2: $B$7, “= 0”, $C$2: $C$7, “= 0”)
Khi đó hàm sẽ tìm kiếm ở cả hai vùng với hai điều kiện tương đương và cho kết quả là 1 bởi chỉ có Grapes đáp ứng đủ điều kiện có giá trị bằng “0” ở cả hai cột.
Nếu chỉ sử dụng công thức đơn giản với một criteria_range duy nhất : Theo công thức : COUNTIFS(B2: C7, “= 0”) Thì kết quả trả lại sẽ khác : Ở đây kết quả trả về là 4
Cách đếm có điều kiện với nhiều tiêu chí
Như vậy việc đếm các ô tính đáp ứng được tất cả các tiêu chí chỉ định rất dễ dàng bởi hàm COUNTIFS “sinh ra” để hoạt động theo cách này.
Nhưng trường hợp bận lại cấn đếm các ô có ít nhất một trong các tiêu chí được chỉ định là TRUE nghĩa là dựa theo phép tính logic OR thì cách thích hiện sẽ được tiến hành như sau :
Có 2 cách :
Cách 1 : Cách thêm hai hoặc nhiều công thức COUNTIF hoặc COUNITFS
Ví dụ ta có một bảng như trên. Hiện tại bạn đang muốn tính các đơn đặt hàng với trạng thái là “Cancelled” và “Pending“, Khi đó bạn chỉ có thể viết 2 công thức COUNTIF bình thường sau đó nhận kết quả : Công thức được thực hiện sẽ là : =COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)
Nếu như bạn muốn sử dụng hàm đếm có điều kiện với nhiều hơn một điều kiện thì sử dụng COUNTIFS thay vì COUNTIF.
Ví dụ cụ thể với bảng trên, để đếm số đơn đặt hàng “Cancelled” và “Pending” đối với “Apples”, thì ta sẽ xử 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ách 2: Công thức SUM COUNTIFS với hằng mảng
Nếu như bạn muốn thiết lập nhiều tiêu chí, thì cách trên lại không phải cách tối ưu bởi vì công thức có vẻ dài dòng. Để thực hiện các phép tính tương tự trong một công thức nhỏ gọn hơn, thì cách đơn giản là liệt kê tất cả những tiêu chí của bạn trong mảng và cung cấp mảng đó cho criteria của hàm COUNTIFS. Để đếm được tổng ta có thể kết hợp các hàm COUNTIFS bên trong hàm SUM như sau:
=SUM (COUNTIFS (range, {“criteria1”, “criteria2”, “criteria3”, …}))
Trong bảng mẫu ở trên, nếu bạn muốn đếm các đơn đặt hàng có trạng thái “Cancelled“, “Pending” hoặc “In transit“, Công thức thực hiện như sau :
=SUM(COUNTIFS($C$2:$C$11,{“Cancelled”,”Pending”,”In Transit”}))
Tương tự với cách làm này, bạn có thể đếm các ô dựa trên hai hoặc nhiều hơn các cặp criteria_range/criteria.
Để biết số lượng đơn đặt hàng “Apples” trong trạng thái “Cancelled”, “Pending”, “In transit”, sử dụng công thức này:
=SUM(COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,{“Cancelled”,”Pending”,”In transit”}))
Công thức 1. Hàm COUNTIFS đếm các ô trong khoảng số
Cách đếm trong một khoảng cụ thể
Để đếm được có bao nhiêu số giữa 5 và 10 ( không bao gồm 5 và 10 ) có trong các ô C2 đến C10. Ta sẽ sử dụng công thức sau
=COUNTIFS(C2:C10,”>5″,C2:C10,”<10″)
Trường hợp đếm cả hai số nút, tức là đếm cả 5 và 10 thì ta có thể sử dụng dấu ” >=” để làm điều kiện :
=COUNTIFS(B2:B10,”>=5″,B2:B10,”<=10″)
Cách thực hiện thứ 2 Công thức COUNTIF để đếm số giữa X và Y
Chúng ta có thể sử dụng công thức COUNTIF lồng ghép với một hàm khác khác để có kết quả tương tự. Công thức đầu tiên đếm những số lớn hơn giá trị giới hạn dưới (ví dụ là 5). Công thức thứ hai loại bỏ những số lớn hơn giá trị giới hạn trên (trong trường hợp này là 10). Kết quả bạn muốn tìm nằm trong khoảng hai giá trị nhận được trên.
=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″) – Đây là cách đếm loại trừ, ta sẽ đếm xem có bao nhiêu ô có giá trị lớn hơn 5, sau đó laijd dếm xem có bao nhiêu ô trong vùng dữ liệu có giá trị lớn hơn 10 ở trong vùng dự liệu C2:C10.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – Còn đây là công thức để đếm xem có bao nhiêu số trong đoạn từ 5 đến 10 thuộc vùng dữ iệu C2:C10, bao gồm cả 5 và 10.
Trên đây là những cách sử dụng hàm đếm có điều kiện trong Excel cơ bản. Chúc các bạn thành ông.
Mơ thấy con ghẹ báo điềm gì? Chiêm bao thấy con ghẹ dự báo tốt lành hay xui xẻo? Mơ thấy con ghẹ đánh con gì hiệu quả ăn chắc phần thắng?
Nhận định, thông tin tỷ lệ, ý kiến chuyên gia, dự đoán bóng đá Lecce vs AS Roma vào lúc 23h00 ngày 1/4, giải VĐQG Italia.
Lịch thi đấu
Kết quả bóng đá
BXH Bóng đá
