Hướng dẫn sử dụng hàm COUNTIF cơ bản đếm có điều kiện trong Excel

Excel cung cấp một số hàm phục vụ việc đếm các loại ô khác nhau như đếm ô trống, ô không trống, ô chứa giá trị số hoặc những ô chứa kí tự cụ thể nào đó. Trong bài viết này, Học Excel Online cùng các bạn sẽ tập trung vào  hàm COUNTIF cơ bản, được sử dụng để đếm ô với điều kiện tự thiết lập thông qua các ví dụ dưới đây.

Cấu trúc hàm COUNTIF cơ bản

Hàm COUNTIF có cấu trúc như sau:

=COUNTIF(range, criteria)

Trong đó:

range: Vùng chứa các ô muốn đếm

criteria: điều kiện bạn muốn thiết lập dưới dạng một số, kí tự hoặc một logic sẽ chỉ định những giá trị nào sẽ được đếm

Ứng dụng hàm Countif trong các chuỗi ô

Để các bạn hiểu hơn về cách sử dụng Countif mình sẽ đi vào các ví dụ từ đơn thuần đến phức tạp hơn sau đây

Hàm COUNTIF cho văn bản và số (chính xác tuyệt đối)

Trên thực tế, chúng ta đã thảo luận về hàm COUNTIF đếm các giá trị chữ khớp với một điều kiện chính xác tuyệt đối vừa rồi. Hãy để tôi nhắc lại công thức của ô có chứa một chuỗi văn bản: = COUNTIF (C2: C15, “Roger Federer”). Vì vậy, bạn nhập:

• Vùng chọn đếm là tham số đầu tiên;
• Dấu phẩy như dấu phân cách;
• Một từ hoặc một số từ trong dấu ngoặc kép làm điều kiện.

Thay vì nhập lại từ, bạn có thể sử dụng tham chiếu đến bất kỳ ô nào chứa từ hoặc các từ đó và nhận được kết quả tương tự, ví dụ: = COUNTIF (C1: C9, C7).

Hàm COUNTIF dùng tương tự cho số. Như bạn thấy trong hình dưới đây, công thức = COUNTIF (D2: D9,5) đếm các ô có giá trị 5 ở Cột D.

Hàm COUNTIF với ký tự đại diện (khớp một phần)

Trong trường hợp tài liệu Excel của bạn gồm có một vài biến thể của ( các ) từ khóa mà bạn muốn tính, thì bạn hoàn toàn có thể sử dụng ký tự đại diện thay mặt để đếm toàn bộ các ô chứa một từ, cụm từ hoặc vần âm nhất định như một phần của nội dung ô .

Giả sử bạn có một danh sách các nhiệm vụ được giao cho những người khác nhau và bạn muốn biết số nhiệm vụ được giao cho Danny Brown. Bởi vì tên của Danny được viết bằng nhiều cách khác nhau, ta nhập “* Brown *” là điều kiện = COUNTIF (D2: D10, “*Brown *”).

Dấu hoa thị (*) được sử dụng thay thế các giá trị đầu và cuối, như được minh họa trong ví dụ trên. Nếu bạn cần phải khớp từng ký tự đơn, hãy nhập một dấu hỏi (?), như được trình bày dưới đây.

Đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định

Bạn hoàn toàn có thể sử dụng ký tự đại diện thay mặt, dấu sao ( * ) hoặc dấu chấm hỏi ( ? ), với tùy thuộc vào hiệu quả mà bạn muốn .
Nếu bạn muốn biết số ô khởi đầu hoặc kết thúc bằng văn bản nhất định bất kể có bao nhiêu ký tự khác trong ô, hãy sử dụng các công thức sau :

= COUNTIF (C2: C10, “Mr*”) – tính các ô bắt đầu bằng “Mr”.

= COUNTIF (C2: C10, “* ed”) – tính các ô kết thúc bằng chữ “ed”.

Hình ảnh dưới đây minh họa cho công thức thứ hai :

Nếu bạn đang tìm kiếm một số ít ô mở màn hoặc kết thúc bằng các ký tự nhất định và chứa 1 số ít ký tự nhất định, bạn sử dụng hàm COUNTIF với dấu hỏi ( ? ) tại điều kiện kèm theo :

= COUNTIF (D2: D9, “??own”) – đếm số ô kết thúc bằng chữ “own” và có chính xác 5 ký tự trong các ô từ D2 đến D9, bao gồm cả khoảng cách.

= COUNTIF (D2: D9, “Mr ??????”) – đếm số ô bắt đầu bằng chữ “Mr” và có chính xác 8 ký tự trong các ô từ D2 đến D9, bao gồm khoảng cách.

Mẹo. Để tìm số ô chứa dấu hỏi “?” hoặc dấu hoa thị “*”, hãy gõ dấu ngã (~) trước dấu ? hoặc * trong công thức. Ví dụ, = COUNTIF (D2: D9, “* ~? *”) để đếm tất cả các ô có chứa dấu chấm hỏi trong dải D2: D9.

Ứng dụng hàm COUNTIF cơ bản với chữ cái và số trùng khớp

Hãy tìm hiểu thêm ví dụ dưới đây :
[ iframe width = ” 700 ″ height = ” 689 ″ frameborder = ” 0 ″ scrolling = ” no ” src = ” https://onedrive.live.com/embed?resid=B21F9331F704AFCF%21115&authkey=%21AGBxOyCOXzaOtds&em=2&wdAllowInteractivity=False&AllowTyping=True&Item=’%C4%90%E1%BA%BFm%20ch%C3%ADnh%20x%C3%A1c’!A1%3AN32&wdHideGridlines=True&wdDownloadButton=True&wdInConfigurator=True ” ]
Tại đây, tất cả chúng ta sẽ sử dụng hàm COUNTIF để đếm những ô chứa giá trị thỏa mãn nhu cầu trùng khớp với điều kiện kèm theo. Cú pháp tương ứng trong ví dụ :

=COUNTIF($C$2:$C$31,$I$8) hoặc =COUNTIF($C$2:$C$31,”Nam”) – đếm số lượng ô chứa giá trị “Nam” trong vùng C2 đến C31

=COUNTIF($D$2:$D$31,I11) hoặc =COUNTIF($D$2:$D$31,18) – đếm số lượng ô chứa giá trị “18” trong vùng C2 đến C31.

Lưu ý: Criteria không phân biệt chữ hoa và chữ thường. Bởi vậy, Nam hay NAM, nam thì cũng như nhau thôi. Trong nhiều trường hợp, nó rất tiện vì bạn không phải xử lí chính xác dữ liệu, nhưng trong nhiều trường hợp, nó lại ảnh hưởng rất nhiều tới việc tìm kiếm. Vậy xử lí thế nào? Có rất nhiều cách. Ở đây, mình sẽ gợi ý một hướng đi, đó là thay vì đếm “Nam”, “NAM”, “nam”, hãy đếm TRUE và FALSE kết hợp cùng hàm EXACT. Cụ thể ra sao, các bạn hãy thử suy nghĩ và tự thực hành lời giải nhé.

Không hề khó nhớ phải không ? Giờ ta sẽ đến với những “ biến thể ” ở những ví dụ tiếp theo .

Sử dụng COUNTIF kết hợp những kí tự đại diện (wildcard)

Kí tự đại diện (Wildcard) được sử dụng để thay thế các kí tự khác trong ô. Có 3 kí tự đại diện là dấu sao (*), dấu hỏi (?), dấu ngã (~).

*Chú ý: Wildcard có thể thay thế mọi giá trị trong chuỗi kí tự, nhưng không được dùng để thay thế đối với định dạng số (Number). Nếu gặp trường hợp như vậy, bạn hãy chuyển toàn bộ về dạng TEXT bằng hàm TEXT, sau đó kiểm tra lại bằng hàm ISTEXT và cuối cùng COUNTIF kết hợp wildcard như bình thường.

Hãy cùng xem ví dụ :
[ iframe width = ” 700 ″ height = ” 689 ″ frameborder = ” 0 ″ scrolling = ” no ” src = ” https://onedrive.live.com/embed?resid=B21F9331F704AFCF%21115&authkey=%21AGBxOyCOXzaOtds&em=2&wdAllowInteractivity=False&AllowTyping=True&Item=’%C4%90%E1%BA%BFm%20v%E1%BB%9Bi%20k%C3%AD%20t%E1%BB%B1%20%C4%91%E1%BA%A1i%20di%E1%BB%87n’!A1%3AN32&wdHideGridlines=True&wdDownloadButton=True&wdInConfigurator=True ” ]

 Trong ví dụ này, ta sẽ đếm cho 2 trường hợp: những người có họ Lê; những người có tên Anh. 

Đầu tiên, ta sử dụng dấu (*) thay thế cho cụm kí tự. Đối với những người họ Lê, ta sử dụng cú pháp “Lê*” tại vùng điều kiện; hoặc $I$8 & “*” nếu tham chiếu tới ô I8. Nếu các bạn còn khúc mắc về các kí tự đại diện, xem thêm bài viết về Kí tự đại diện Wildcard ngay

Kết hợp hàm COUNTIF cơ bản, ta có thể  đếm được số lượng ô thỏa mãn điều kiện.

Bây giờ, hãy đến với ví dụ thứ 3

Sử dụng hàm COUNTIF đếm với các phép so sánh lớn nhỏ bằng

[ iframe width = ” 700 ″ height = ” 689 ″ frameborder = ” 0 ″ scrolling = ” no ” src = ” https://onedrive.live.com/embed?resid=B21F9331F704AFCF%21115&authkey=%21AGBxOyCOXzaOtds&em=2&wdAllowInteractivity=False&AllowTyping=True&Item=’%C4%90%E1%BA%BFm%20v%E1%BB%9Bi%20to%C3%A1n%20t%E1%BB%AD%20l%E1%BB%9Bn%20nh%E1%BB%8F%20b%E1%BA%B1ng’!A1%3AN32&wdHideGridlines=True&wdDownloadButton=True&wdInConfigurator=True ” ]

Đối với việc đếm sử dụng điều kiện là các phép so sánh lớn hơn (>), nhỏ hơn (điều kiện trực tiếp và điều kiện tham chiếu.

Với điều kiện trực tiếp ta đặt toàn bộ trong dấu nháy kép như sau: “>=a” với >= là phép so sánh, a là giá trị số được so sánh.

Với điều kiện tham chiếu ta đặt phép so sánh trong dấu nháy kép, kết hợp cùng kí tự &: “>=”& I8 với >= là phép so sánh, I8 là ô được tham chiếu tới.

Qua 3 ví dụ trên, chắc rằng các bạn đã mở màn hiểu rõ hơn về hàm COUNTIF rồi phải không ? Giờ hãy tìm hiểu và khám phá ứng dụng của hàm này một chút ít với ví dụ sau cuối nhé .

Ứng dụng hàm COUNTIF lọc giá trị trùng lặp

[ iframe width = ” 700 ″ height = ” 689 ″ frameborder = ” 0 ″ scrolling = ” no ” src = ” https://onedrive.live.com/embed?resid=B21F9331F704AFCF%21115&authkey=%21AGBxOyCOXzaOtds&em=2&wdAllowInteractivity=False&Item=’%C4%90%E1%BA%BFm%20tr%C3%B9ng’!A1%3AN32&wdHideGridlines=True&wdDownloadButton=True&wdInConfigurator=True ” ]
Ví dụ trên là một ứng dụng đơn thuần của hàm COUNTIF trong các hoạt động giải trí như lọc email người mua, lọc tên … bị trùng lặp. Có lẽ tới đây, các bạn đều đã nhận ra cách làm rồi phải không ? Hãy tính số lần Open của giá trị muốn tìm trong cột tổng và chuyển bài toán về lọc các giá trị trả về theo số tự nhiên .

Sử dụng COUNTIF kết hợp AND hoặc OR

Với cú pháp của hà Countif

=COUNTIF(range, criteria)

Rõ ràng là criteria ở đây là hàm AND, và điều kiện ta đã liệt kê ra đầy đủ rồi, phải không?

Ta hãy xem qua hàm AND một chút:

Vấn đề ở đây là :

1. Hàm AND (và cả OR) sẽ trả về giá trị là TRUE hoặc FALSE.
2. Logic so sánh trong hàm AND. Cái gì sẽ lớn hơn, hoặc nhỏ hơn?

Khi đưa AND hoặc OR vào COUNTIF, đối số range của bạn sẽ được so với criteria là TRUE hoặc FALSE (hoặc #VALUE!). Rõ ràng kết quả sẽ trả về 0 rồi.

Vậy ta khắc phục thế nào ?

Cách 1: Sử dụng hàm COUNTIFS thay thế

Hàm COUNTIFS cho phép đếm với nhiều điều kiện. Tại sao phải cố nhồi nhét vào COUNTIF trong khi có thể dùng hàm COUNTIFS.

Cách 2: Lập cột phụ

Hướng làm mà Học Excel Online gợi ý như sau: Lập một cột phụ dùng hàm And để trả về giá trị True hoặc False, sau đó sử dụng COUNTIF với cột phụ.

Cách 3: Sử dụng logic toán học

Logic ở đây là: Để tìm số những số thỏa mãn trong khoảng A và B, lấy số những số thỏa mãn khoảng A trừ đi số những số thỏa mãn khoảng B (khoảng B nằm trong khoảng A).

Lời kết

Đừng quên trên đây chỉ là những ví dụ cơ bản nhất của COUNTIF. Còn rất nhiều ứng dụng đang chờ các bạn tò mò, bởi vậy nên hãy rèn luyện liên tục nhé !
Chúc các bạn học tốt .