Hàm COUNTIF trong Excel: Hướng dẫn chi tiết và ví dụ thực tế

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).

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ố.

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.