Microsoft Excel cung cấp nhiều hàm hỗ trợ đếm các loại ô khác nhau, bao gồm ô trống, ô chứa số, ngày tháng, giá trị văn bản, hoặc chứa ký tự/từ cụ thể. Bài viết này từ Thủ Thuật sẽ tập trung vào hàm COUNTIF, giúp bạn đếm số ô thỏa mãn một điều kiện nhất định. Chúng ta sẽ tìm hiểu cú pháp, cách sử dụng, ví dụ minh họa, và một số lưu ý khi sử dụng hàm này với nhiều điều kiện và các ô đặc biệt.
Hàm COUNTIF: Cú pháp và cách sử dụng
Hàm COUNTIF dùng để đếm số ô trong một phạm vi đáp ứng một tiêu chí hoặc điều kiện cụ thể. Ví dụ, bạn có thể dùng COUNTIF để xác định số ô chứa số lớn hơn hoặc nhỏ hơn một giá trị nào đó, hoặc đếm số ô chứa một từ cụ thể hay bắt đầu bằng một hoặc nhiều chữ cái nào đó.
Cú pháp của hàm COUNTIF rất đơn giản:
COUNTIF(range, criteria)Hàm này có hai đối số bắt buộc:
range: Phạm vi ô cần đếm. Bạn có thể nhập phạm vi theo cách thông thường trong Excel, ví dụ:A1:A20.criteria: Điều kiện để đếm ô. Có thể là số, chuỗi văn bản, tham chiếu ô, hoặc biểu thức. Ví dụ:"10",A2,">=10","văn bản".
Minh họa đối số hàm COUNTIF
Minh họa ví dụ hàm COUNTIF
Ví dụ đơn giản: Danh sách các tay vợt hàng đầu trong 14 năm. Công thức =COUNTIF(C2:C15,"Roger Federer") sẽ đếm số lần tên “Roger Federer” xuất hiện trong danh sách.
Ví dụ đếm tên Roger Federer
Lưu ý: Điều kiện không phân biệt chữ hoa chữ thường. "roger federer" cũng cho kết quả tương tự.
Ví dụ về hàm COUNTIF
Cú pháp của COUNTIF đơn giản nhưng cho phép nhiều biến thể của điều kiện, bao gồm ký tự đại diện, giá trị của ô khác, và cả các hàm Excel khác. Điều này làm cho COUNTIF trở thành một hàm mạnh mẽ, hữu ích trong nhiều trường hợp.
Đếm văn bản và số (khớp chính xác)
Ví dụ trên đã minh họa cách đếm giá trị văn bản khớp chính xác. Công thức =COUNTIF(C2:C15,"Roger Federer") đếm số ô chứa chuỗi “Roger Federer”. Bạn cần nhập:
- Phạm vi cần đếm là đối số đầu tiên.
- Dấu phẩy phân cách các đối số.
- Từ hoặc cụm từ cần đếm nằm trong dấu ngoặc kép.
Bạn cũng có thể dùng tham chiếu ô chứa từ/cụm từ thay vì nhập trực tiếp: =COUNTIF(C1:C9,C7).
COUNTIF hoạt động tương tự với số. Công thức =COUNTIF(D2:D9,5) đếm số ô có giá trị 5 trong cột D.
Ví dụ đếm số 5
Sử dụng ký tự đại diện (khớp một phần)
Nếu dữ liệu chứa nhiều biến thể của từ khóa cần đếm, bạn có thể sử dụng ký tự đại diện. Ví dụ, đếm số nhiệm vụ được giao cho “Danny Brown” khi tên được viết theo nhiều cách khác nhau: =COUNTIF(D2:D10,"*Brown*").
Ví dụ đếm tên Brown
Dấu sao (*) thay thế cho bất kỳ chuỗi ký tự nào ở đầu hoặc cuối. Để khớp từng ký tự, sử dụng dấu hỏi (?).
Đếm ô bắt đầu hoặc kết thúc bằng ký tự cụ thể
Sử dụng dấu sao (*) hoặc dấu hỏi (?) để đếm ô bắt đầu hoặc kết thúc bằng ký tự cụ thể.
=COUNTIF(C2:C10,"Mr*"): Đếm ô bắt đầu bằng “Mr”.=COUNTIF(C2:C10,"*ed"): Đếm ô kết thúc bằng “ed”.
Ví dụ đếm ô kết thúc bằng "ed"
Để tìm ô bắt đầu hoặc kết thúc bằng ký tự cụ thể và chứa một số ký tự nhất định, sử dụng dấu hỏi (?):
=COUNTIF(D2:D9,"??own"): Đếm ô kết thúc bằng “own” và có 5 ký tự (bao gồm cả khoảng trắng).=COUNTIF(D2:D9,"Mr?????"): Đếm ô bắt đầu bằng “Mr” và có 8 ký tự (bao gồm cả khoảng trắng).
Mẹo: Để tìm ô chứa ? hoặc *, thêm dấu ngã (~) trước ký tự đó. Ví dụ: =COUNTIF(D2:D9,"*~?*") đếm ô chứa dấu hỏi.
Đếm ô trống và không trống
Đếm ô không trống:
Công thức =COUNTIF(range,"<>"") đếm tất cả các ô không trống, bao gồm cả số, ngày tháng, và văn bản.
Ví dụ đếm ô không trống
Đếm ô trống:
Công thức =COUNTIF(range,"") đếm tất cả các ô trống, bao gồm cả ô chứa công thức trả về giá trị trống. Hàm COUNTBLANK(range) cũng có chức năng tương tự.
COUNTIF với điều kiện lớn hơn, nhỏ hơn hoặc bằng
Để đếm ô có giá trị lớn hơn, nhỏ hơn, hoặc bằng một số, sử dụng dấu so sánh trong điều kiện (luôn đặt trong dấu ngoặc kép).
| Điều kiện | Công thức ví dụ | Diễn giải |
|---|---|---|
| Lớn hơn | =COUNTIF(A2:A10,">5") | Đếm ô có giá trị lớn hơn 5 |
| Nhỏ hơn | =COUNTIF(A2:A10,"<5") | Đếm ô có giá trị nhỏ hơn 5 |
| Bằng | =COUNTIF(A2:A10,"=5") | Đếm ô có giá trị bằng 5 |
| Khác | =COUNTIF(A2:A10,"<>5") | Đếm ô có giá trị khác 5 |
| Lớn hơn hoặc bằng | =COUNTIF(C2:C8,">=5") | Đếm ô có giá trị lớn hơn hoặc bằng 5 |
| Nhỏ hơn hoặc bằng | =COUNTIF(C2:C8,"<=5") | Đếm ô có giá trị nhỏ hơn hoặc bằng 5 |
Bạn có thể thay thế số bằng tham chiếu ô. Lưu ý: Khi dùng tham chiếu ô, đặt dấu so sánh trong dấu ngoặc kép và dùng dấu & để nối chuỗi. Ví dụ: =COUNTIF(D2:D9,">"&D3).
Ví dụ COUNTIF với điều kiện so sánh
Sử dụng COUNTIF với ngày tháng
COUNTIF hoạt động với ngày tháng tương tự như với số.
| Điều kiện | Công thức ví dụ | Diễn giải |
|---|---|---|
| Đếm ngày cụ thể | =COUNTIF(B2:B10,"6/1/2014") | Đếm số ô có ngày 6/1/2014 |
| Đếm ngày lớn hơn hoặc bằng | =COUNTIF(B2:B10,">=6/1/2014") | Đếm số ô có ngày lớn hơn hoặc bằng 6/1/2014 |
| Đếm ngày lớn hơn hoặc bằng (tham chiếu ô) | =COUNTIF(B2:B10,">="&B2-7) | Đếm số ô có ngày lớn hơn hoặc bằng ngày trong ô B2 trừ đi 7 ngày |
Bạn cũng có thể kết hợp COUNTIF với các hàm ngày giờ như TODAY() để đếm ô dựa trên ngày hiện tại.
Ví dụ COUNTIF với ngày tháng
Hàm COUNTIF với nhiều điều kiện
COUNTIF với hai điều kiện về số
Ví dụ: đếm số trong khoảng từ 5 đến 15 (không bao gồm 15): =COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,">=15")
Ví dụ COUNTIF với hai điều kiện về số
COUNTIF với nhiều điều kiện cho giá trị văn bản
Ví dụ: đếm số lượng “Lemonade” và các loại “juice”: =COUNTIF(B2:B13,"Lemonade")+COUNTIF(B2:B13,"*juice")
Ví dụ COUNTIF với nhiều điều kiện cho giá trị văn bản
Sử dụng COUNTIF để tìm giá trị lặp và giá trị duy nhất
Tìm và đếm giá trị lặp trong một cột
Ví dụ: =COUNTIF(B2:B10,B2)>1 trả về TRUE/FALSE nếu ô B2 lặp lại. Hàm =COUNTIF(D2:D10,TRUE) đếm số lần lặp.
Ví dụ tìm giá trị lặp trong cột
Đếm số lần lặp giữa hai cột
Ví dụ: đếm số tên xuất hiện trong cả hai cột B và C: =SUMPRODUCT((COUNTIF(B2:B1000,C2:C1000)>0)*(C2:C1000<>""))
Ví dụ đếm số lần lặp giữa hai cột
Đếm giá trị lặp và giá trị duy nhất trong một hàng
Ví dụ: đếm số lần lặp trong hàng: =SUMPRODUCT((COUNTIF(A2:I2,A2:I2)>1)*(A2:I2<>""))
Ví dụ đếm giá trị lặp trong hàng
Kết luận: Hàm COUNTIF là công cụ mạnh mẽ trong Excel, giúp bạn dễ dàng đếm số ô thỏa mãn điều kiện cụ thể. Hy vọng bài viết này cung cấp cho bạn kiến thức cần thiết để sử dụng hàm COUNTIF hiệu quả trong công việc. Tham khảo thêm các bài viết khác trên Thủ Thuật để nâng cao kỹ năng Excel của bạn.
Discussion about this post