Nếu bạn chưa biết cách sử dụng hàm này như thế nào thì mời bạn cùng Sforum xem cách ứng dụng hàm đếm có điều kiện qua các ví dụ cụ thể trong bài viết dưới đây nhé!
Hàm COUNTIF là gì?
Hàm COUNTIF là hàm đếm có điều kiện trong Excel. Bạn có thể dùng để đếm ô trùng lặp, đếm dữ liệu. Thường thì hàm đếm số lượng có điều kiện này sẽ dùng với những bảng dữ liệu thống kê, yêu cầu đếm số ô chứa giá trị thỏa mãn điều kiện cho sẵn.Bạn đang xem: Hàm đếm ký tự có điều kiện trong excel
Công thức hàm đếm có điều kiện
Hàm COUNTIF trong Excel có công thức là =COUNTIF(range;criteria). Trong đó: Range: Là vùng cần đếm dữ liệu. Criteria: Điều kiện cần đếm. Đối số criteria cần để trong dấu ngoặc kép. Không phân biệt chữ thường hay chữ hoa.Cách sử dụng hàm COUNTIF trong Excel và ví dụ
Mời bạn tìm hiểu cách sử dụng hàm COUNTIF qua các ví dụ tìm kiếm dữ liệu khác nhau.Ví dụ 1: Dùng hàm COUNTIF tìm kiếm số lượng tên hàng Dưa hấu có trong bảng sau
Công thức như sau: =COUNTIF(B2:B8,"dưa hấu") rồi nhấn Enter để thực hiện hàm.
Và kết quả sẽ đếm ra 2 giá trị có tên là Dưa hấu.
Ví dụ 2: Vẫn với bảng trên, chúng ta sẽ đếm số lượng mặt hàng lớn hơn 6 kg
Công thức như sau: =COUNTIF(C2:C8,">6") rồi nhấn Enter để thực hiện hàm.
Kết quả là sẽ đếm ra được là có 3 mặt hàng có khối lượng lớn 6kg.
Ngoài ra, đối với các trường hợp sau: Đếm số lượng nhỏ hơn 6kg, thay đổi đối số criteria là " Đếm số lượng lớn hơn hoặc bằng 6kg, thay đổi đối số criteria là ">=6". Đếm số lượng nhỏ hơn hoặc bằng 6kg, thay đổi đối số criteria là "Ví dụ 3: Dùng hàm COUNTIF tìm số lượng hàng không phải là Dưa hấu trong bảng
Công thức như sau: =COUNTIF(B2:B8,"Dưa hấu") rồi nhấn Enter để thực hiện hàm. Trong đó "" là ký tự khác
Kết quả đếm được là có 5 mặt hàng không phải dưa hấu.
Ví dụ 4: Dùng hàm COUNTIF tìm kiếm đơn hàng có tên Sầu riêng sử dụng ký tự thay thế
Để đếm mặt hàng bằng cách sử dụng ký tự thay thế thì ở đối số criteria, bạn điền chữ cái đầu của tên mặt hàng và theo sau đó là ký tự hoa *.
*Lưu ý: Cách này chỉ có thể áp dụng khi các mặt hàng có chữ cái đầu khác nhau.
Công thức như sau: =COUNTIF(B2:B8,"S*") rồi nhấn Enter để thực hiện hàm.
Và đây là kết quả.
Ví dụ 5: Tìm kiếm mặt hàng có tên khác ô B3
Ở đây chúng ta sẽ tìm những mặt hàng có tên khác ô B3 là Xoài bằng cách dùng ký tự "" và "&" trước ô tham chiếu, với cú pháp hàm =COUNTIF(B2:B8,""&B3).
Kết quả sẽ ra số mặt hàng chính xác là 6.
Kết luận cách dùng COUNTIF - Hàm đếm có điều kiện trong Excel
Trên đây là bài viết hướng dẫn bạn cách sử dụng hàm COUNTIF - Hàm đếm số lượng có điều kiện trong Excel. Hy vọng những thông tin này giúp ích cho bạn. Ngoài ra bạn cũng có thể tham khảo thêm nhiều thủ thuật Excel khác tại Sforum.
Trong tất cả các hàm Excel, hàm đếm có điều kiện COUNTIFS và COUNTIF có lẽ thường dễ bị nhầm lẫn nhất vì chúng trông rất giống nhau và cả hai đều cùng có mục đíchdùng để đếm các ô dựa trên các điều kiệnđã được chỉ định.
Sự khác biệt duy nhất giữa hai hàmnàylà COUNTIF được thiết kế để đếm các ô có một điều kiện trong một phạm vi, trong khi COUNTIFS có thể đánh giá các điều kiện khác nhau trong cùng hoặc trong các phạm vi khác nhau.
Nhằm giúp bạn đọc phân biệt một cách chính xác và nắm được cách sử dụng hai hàm đếm số lượngcó điều kiện COUNTIFSđúng trong Excel, trong bài viết này docongtuong.edu.vn sẽ giới thiệu và hướng dẫn bạn đọc cách sử dụng và lựa chọn công thức hiệu quả nhất cho từng nhiệm vụ từng bài toán cụ thể, đặc biệt là đối với hàm
COUNTIFS.
1. Hàm COUNTIFS của Excel - cú pháp và cách sử dụng
Như đã đề cập, hàm COUNTIFtrong
Excel được dùng để đếm các ô trên nhiều phạm vi dựa trên một hoặc một số điều kiện. Hàm COUNTIFS có sẵn trong Excel 2016, 2013, Excel 2010 và Excel 2007, vì vậy bạn có thể sử dụng các ví dụ dưới đây trong bất kỳ phiên bản
Excelnào.
Công thức countif: =COUNTIFS(criteria_range1, criteria1,
Trong đó:
- criteria_range1: Vùng chứa điều kiện thứ nhất, đây là phần bắt buộc phải có
- criteria1: Điều kiện thứ nhất (thuộc trong vùng điều kiện thứ nhất) gồm có các dạng số, ô tham chiếu, chuỗi văn bản, hàm Excel hoặc vùng Excel khác.
- criteria_range2: Vùng chứa điều kiện thứ hai( yêu cầu vùng criteria_range2 phải có độ lớn bằng nhau)
-criteria2, ... Điều kiện thứ hai (thuộc trong vùng điều kiện thứ nhất). Bạn có thể chỉ định tối đa 127 cặp phạm vi / điều kiệntrong công thức của mình.
- Tương tự như vậy với các điều kiện khác cũng sẽ áp dụng như vậy.
Hình minh họa
Thực tế, bạn không cần phải nhớ cú pháp của hàm đếm có điều kiệnCOUNTIFS. Microsoft Excel sẽ hiển thị các đối số của hàm ngay khi bạn bắt đầu nhập; đối số bạn đang nhập vào lúc này được tô đậm.
Bạn chỉ cần nhớ bạn muốn sử dụng hàm COUNTIFS trong
Excel là được.
LƯU Ý:
- Bạn có thể sử dụng hàm COUNTIFS trong Excel để đếm các ô trong một phạm vi với một điều kiện duy nhất cũng như trong nhiều phạm vi với nhiều điều kiện. Nếu sau này, chỉ những ô đáp ứng tất cả các điều kiện được chỉ định mới được tính.
- Criteria1bạn có thể biểu diễn chúng giống như các ví dụ: 10, "- Mỗi phạm vi bổ sung phải có cùng số hàng và cột như phạm vi đầu tiên.Cả phạm vi tiếp giáp và không liền kề đều được cho phép.
- Nếu điều kiệnlà một tham chiếu đến một ô trống , hàm COUNTIFS coi nó là giá trị 0 (0).
- Bạn có thể sử dụng các ký tự đại diệntrong điều kiện - dấu hoa thị (*) và dấu hỏi (?).
2. Cách sử dụng hàm đếm có điều kiện COUNTIFS với nhiều điều kiện
Dưới đây bạn sẽ tìm thấy một số ví dụ về công thức minh họa cách sử dụng các hàm COUNTIFS trong Excel để đánh giá nhiều điều kiện.
Thông qua những ví dụ được minh họa dưới đây bạn sẽ hình dung ra được cách sử dụng những hàm này trong các trường hợp cụ thể tương ứng để có thể áp dụng hàm
COUNTIFS trong Excel để đánh giá nhiều điều kiện khác nhau
2.1. Cách đếm các ô có nhiều điều kiện
Công thức 1. Công thức COUNTIFS với nhiều điều kiện khác nhauVí dụbạn có một danh sách sản phẩm như hiển thị trong ảnh chụp màn hình bên dưới. Bạn muốn có được một số lượng các mặt hàng trong kho (giá trị trong cột B lớn hơn 0) nhưng chưa được bán (giá trị là cột C bằng 0).
Nhiệm vụ có thể được thực hiện bằng cách sử dụng công thức này:
=COUNTIFS(B2:B7,">0", C2:C7,"=0")
Và số lượng là 2 ("Anh đào " và " Chanh "):
Hình minh họa
Công thức 2. Công thức COUNTIFS có cùng tiêu chíKhi bạn muốn đếm các mục có điều kiệngiống hệt nhau, bạn vẫn cần cung cấp từng cặp điều kiện_range / điều kiện.
Ví dụ: đây là công thức đúng để đếm các mục có 0 cả trong cột B và cột C:
=COUNTIFS($B$2:$B$7,"=0", $C$2:$C$7,"=0")
Công thức COUNTIFS này trả về 1 vì chỉ " Nho " có giá trị "0" trong cả hai cột
Hình minh họa
2.2. Cách đếm ô có nhiều điều kiện khác nhau (logic OR)
Như bạn đã thấy trong các ví dụ trên, việc đếm các ô đáp ứng tất cả các điều kiện đã chỉ định là dễ dàng vì hàm COUNTIF nhiều điều kiệnđược thiết kế để hoạt động theo cách này.
Nhưng nếu bạn muốn đếm các ô mà ít nhất một trong các điều kiện được chỉ định là TRUE , tức là dựa trên logic OR thì sao? Nhìn chung, có hai cách để thực hiện việc này - bằng cách thêm một số công thức COUNTIFS hoặc sử dụng công thức SUM COUNTIFS.
Công thức 1. Thêm hai hoặc nhiều công thức trong COUNTIFSVí dụ: để có được số lượng đơn đặt hàng " Đã hủy " và " Đang chờ xử lý" cho " Táo ", hãy sử dụng công thức sau:
=COUNTIFS($A$2:$A$11, "Táo", $C$2:$C$11,"Đã hủy") + COUNTIFS($A$2:$A$11, "Táo", $C$2:$C$11,"Đang chờ xử lý")
Hình minh họa
Công thức 2. SUMIFS với hằng số mảngTrong các tình huống khi bạn phải đánh giá rất nhiều điều kiện,cách trên không phải là cách tốt nhất vì công thức của bạn sẽ quá dài.
Để 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, hãy liệt kê tất cả các điều kiệncủa bạn trong một hằng số mảng và cung cấp mảng đó cho đối số điều kiệncủa hàm đếm có điều kiệnCOUNTIFS.
Ta sẽ dùng công thức sau:
=SUM(COUNTIFS(range,{"criteria1","criteria2","criteria3",…}))
Trong bảng dưới đây, để đếm ‘Táo” có trạng thái " Đã hủy " hoặc " Đang chờ xử lý " hoặc " Nhập kho ", công thức sẽ như sau:
=SUM(COUNTIFS($A$2:$A$11,"Táo",$C$2:$C$11,{"Đã hủy","Đang chờ xử lý","Nhập kho"}))
Hình minh họa
2.3. Cách đếm số trong khoảng cụ thể
Công thức 1:Hàm COUNTIFS đếm các ô trong khoảng số
Công thứcCOUNTIFS2 điều kiện sử dụng để đếm các ô giữa hai số
Để tìm hiểu có bao nhiêu số từ 5 đến 10 (không bao gồm 5 và 10) được chứa trong các ô từ C2 đến C10, hãy sử dụng công thức này:
= COUNTIFS (C2: C10, "> 5", C2: C10, "
Hình minh họa
Để đếm các số 5 và 10 trong số đếm, hãy sử dụng công thức "lớn hơn hoặc bằng" và "nhỏ hơn hoặc bằng":
= COUNTIFS (B2: B10, "> = 5", B2: B10, "
Công thức 2: Công thức COUNTIF để đếm số giữa X và Y
Bạn có thể áp dụng 2 công thức sau:
=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″)– đếm có bao nhiêu số lớn hơn 5 nhỏ hơn 10 trong vùng dữ liệu C2:C10.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″)– công thức đếm 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.
Trong đó:
- Công thức đầu tiên dùng để đế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ứ 2 loại bỏ những cố lớn hơn giá trị giới hạn trên (Ví dụ là 10)
Do vậy kết quả nhận được sẽ nằm trong khoảng giá trị nhận được trên.
Cách sử dụng tham chiếu ô trong công thức COUNTIFS
Ví dụ: Cho bảng dữ liệu sau, hãy tính các đơn hàng "Apples" với số tiền >200$.
Bạn áp dụng công thức như sau:=COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)
Trong đó:
-criteria_range1trong A2: A11
-criteria_range2trong B2: B11.
Lưu ý sử dụng giá trị tuyệt đối trongcriteriavàcriteria_rangeđể công thức vẫn đúng khi sao chép sang các ô tính khác.
Hình ảnh minh họa
2.4. Cách sử dụng COUNTIFS với các ký tự đại diện
Trong các câu thức Excel, bạn có thể sử dụng các ký tự đại diện như sau:
- ? : được sử dụng để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định.
- * " Được sử dụng để đếm ô chứa một từ cụ thể hoặc một k1 tự trong ô.
Ví dụ bạn có danh sách các dự án trong cột A. Bạn muốn biết 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.
=COUNTIFS(B2:B10,”*”,D2:D10,””&””))
Lưu ý, 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. Đó là lý do tại sao bạn sử dụng tiêu chí “”&”” để tìm ô chứa dữ liệu.
Hình minh họa
3.COUNTIFS và COUNTIF với nhiều tiêu chí ngày tháng
Ví dụ 1: Đếm số ngày trong một khoảng thời gian cụ thể
Với ví dụ này, bạn có thể sử dụng 2 hàm COUNTIFSnhiều điều kiện và COUNTIF.
Hình minh họa
Ví dụ 3: Đếm ngày với nhiều điều kiện dựa trên ngày hiện tại
Với trường hợp này, bạn có thể sử dụng hàm COUNTIF kết hợp với hàm TODAY.
Xem thêm: Top 13+ Bakugan Phần 3 Tập 1 Mới Nhất 2022, Chiến Binh Bakugan Phần 3 Tập 1
Ví dụ: Dùng hàm COUNTIF để xác định có bao nhiêu sản phẩm đã được mua nhưng chưa giao hàng:
=COUNTIFS(C2:C9,””&TODAY())
Hình minh họa
Trên đây là hàm đếm có điều kiện và các cách thông dụng nhất để bạn thực hiện sử dụng các hàm đếm đótrong Excel, từ đó dễ dàng thống kê và phân tích số liệu một cách hiệu quả, cải thiện tối đa hiệu suất công việc.
Ngoài kiến thức về hàm đếm có điều kiện trong Excelcác bạn có thể tham khảo các khóahọc Excel online tại docongtuong.edu.vn với cực kỳ nhiều ưu đãi bạn nhé