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

cấu tạo hàm COUNTIF căn bản

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

Bạn Đang Xem: Hướng dẫn sử dụng hàm COUNTIF căn bản đếm có điều kiện trong

Trong đó:

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

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

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

Để Anh chị hiểu hơn về cách sử dụng Countif mình sẽ đi &o các ví dụ từ đơn giả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 đã tranh biện xung đột 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 gần đây. 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”). chính vì như 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 dữ liệu Excel của bạn gồm 1 &i biến thể của (các) từ khoá mà bạn mong muốn tính, thì bạn có thể sử dụng ký tự đại diện để đếm tất cả các ô chứa một từ, cụm từ hoặc chữ cái 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 rất thích 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 phải phải khớp từng ký tự đơn, hãy nhập một dấu hỏi (?), như được biểu thị dưới đây.

Đếm các ô khai mạc hoặc chấm dứt bằng các ký tự nhất định

Bạn có thể sử dụng ký tự đại diện, dấu sao (*) hoặc dấu chấm hỏi (?), với tùy thuộc &o kết quả mà bạn có nhu cầu muốn.

Nếu bạn muốn biết số ô khai mạc hoặc chấm dứt bằng văn bản nhất định bất kể có bao lăm 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 ô khai mạc bằng “Mr”.

= COUNTIF (C2: C10, “* ed”) – tính các ô chấm dứt bằng chữ “ed”.

Bức Ả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ố ô khởi đầu hoặc chấm dứt bằng các ký tự nhất định và chứa một số 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:

Xem Thêm : Lợn cưới – áo mới [Truyện cười dân gian Việt Nam]

= 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, gồm có cả khoảng cách.

= COUNTIF (D2: D9, “Mr ??????”) – đếm số ô mở đầu bằng chữ “Mr” và có chính xác 8 ký tự trong các ô từ D2 đến D9, gồm có 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ăn bản với chữ cái và số trùng khớp

Hãy tham khảo thêm ví dụ dưới đây:

[iframe width=”300″ 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, chúng ta sẽ sử dụng hàm COUNTIF để đếm những ô chứa giá trị thỏa mãn trùng khớp với điều kiện. 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. chính chính vì như thế, 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 phía đ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, Anh chị em 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 gỡ 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=”200″ 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 Anh chị em còn khúc mắc về các kí tự đại diện, đọc thêm bài viết về Kí tự đại diện Wildcard ngay

Kết hợp hàm COUNTIF căn 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=”100″ 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ỏ thêm hơn (<) hoặc bằng (=), có hai kiểu đặt điều kiện thường được sử dụng luân phiên nhau đó là đ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.

Xem Thêm : Top 10 Bài văn phân tích bài thơ “Vội &ng” của Xuân Diệu hay nhất

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 hẳn 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 ứng dụng của hàm này một chút với ví dụ cuối cùng nhé.

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

[iframe width=”300″ 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 giản của hàm COUNTIF trong các hoạt động sinh hoạt như lọc email khách hàng, 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 xuất hiện 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

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ỏ nhiều hơn?

Khi đưa AND hoặc OR &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 &o COUNTIF Bên cạnh đó 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 khám phá, bởi bởi vì hãy luyện tập thường xuyên nhé!

Chúc các bạn học tốt.