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

Bộ phận cấu thành của hàm COUNTIF

Chúng ta xem xét cấu trúc của COUNTIF:

Nội dung như sau:

range: Phạm vi ô cần được tính toán

criteria: là điều kiện mong muốn được đặt ra dưới hình thức con số, kí tự hoặc một logic nhất định để xác định giá trị cần được tính đến

Cách áp dụng hàm Countif trên các dãy ô

Dưới đây là một số ví dụ cụ thể giúp bạn nắm bắt cách dùng hàm Countif, từ dễ đến khó.

Hàm COUNTIF dùng cho chữ và số (khớp hoàn toàn)

Chúng ta vừa bàn luận về cách hàm COUNTIF đếm giá trị văn bản khớp hoàn toàn với tiêu chuẩn. Hãy xem lại công thức cho ô chứa văn bản cụ thể: = COUNTIF (C2: C15, “Roger Federer”). Như vậy, bạn cần nhập:

  • Làm tham số đầu tiên, hãy chọn phạm vi cần tính;
  • Sử dụng dấu phẩy như bộ tách;
  • Chọn một hoặc nhiều từ đặt trong dấu ngoặc nháy để làm tiêu chuẩn.

Bạn có thể sử dụng một ô tham chiếu chứa từ hoặc cụm từ đó thay vì nhập trực tiếp, để nhận kết quả tương tự, như ví dụ sau: = COUNTIF (C1: C9, C7).

Việc sử dụng hàm COUNTIF đối với con số cũng tương tự. Trong hình dưới, công thức = COUNTIF (D2: D9,5) dùng để đếm những ô có giá trị là 5 trong Cột D.

Hàm COUNTIF cùng kí tự đặc biệt để khớp một phần giá trị

Trong các tình huống bạn muốn tính các biến thể của từ khoá cần tìm trong dữ liệu Excel, kí tự đặc biệt có thể được dùng để đếm số lượng các ô có chứa từ, cụm từ, hoặc ký tự cụ thể nào đó.

Với giả định danh sách các công việc được chỉ định cho khá nhiều người và bạn cần tìm hiểu bao nhiêu công việc được giao cho Danny Brown. Dùng điều kiện nhập vào như “* Brown *” trong công thức = COUNTIF (D2: D10, “*Brown *”) do tên Danny xuất hiện dưới nhiều hình thức khác nhau.

Dấu hoa thị (*) thay thế cho các giá trị không xác định ở hai đầu, như trình bày trong ví dụ. Trong trường hợp bạn muốn khớp từng ký tự riêng lẻ, hãy dùng dấu hỏi (?).

Tính toán những ô có chứa ký tự khởi đầu hoặc kết thúc nhất định

Bạn có thể ứng dụng kí tự wild card như dấu hoa thị (*) hoặc dấu chấm hỏi (?) tùy thuộc vào mục đích của bạn.

Để đếm số lượng ô có chứa văn bản bắt đầu hoặc kết thúc bằng một dãy chữ cụ thể mà không quan trọng số lượng các ký tự khác có mặt trong ô, hãy sử dụng nhu liệu sau:

= COUNTIF (C2: C10, “Mr*”) – tính toán những ô có nội dung bắt đầu với “Mr”.

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

Hình dưới minh họa cho ví dụ thứ hai:

Trong trường hợp bạn cần đếm những ô có chứa ký tự cụ thể và bắt đầu hoặc kết thúc với những ký tự đặc biệt, dùng dấu hỏi (?) trong điều kiện hàm COUNTIF:

= COUNTIF (D2: D9, “??own”) – công thức này đếm số ô có chứa từ “own” ở phía cuối và tồn tại đủ 5 ký tự trong dãy từ D2 đến D9, kể cả khoảng trắng.

= COUNTIF (D2: D9, “Mr ??????”) – công thức này dùng để đếm nh

Đoạn “Mr” chứa tổng cộng 8 ký tự, bao gồm cả khoảng trắng, trong phạm vi ô từ D2 tới D9.

Bí kíp: Để tìm kiếm các ô có chứa dấu chấm hỏi “?” hoặc dấu asterisk “*”, bạn nên nhập dấu ngã (~) trước ? hoặc *. Ví dụ, hãy sử dụng công thức = COUNTIF (D2: D9, “* ~? *”) để đếm số ô có dấu hỏi trong khu vực D2: D9.

Áp dụng hàm COUNTIF cơ bản cho các ký tự và số lượng tương ứng

Xem xét ví dụ sau đâ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”]

Ta sẽ dùng hàm COUNTIF để đếm số ô thỏa yêu cầu. Cú pháp sử dụng trong ví dụ là:

=COUNTIF($C$2:$C$31,$I$8) hay =COUNTIF($C$2:$C$31,”Nam”) – đếm số ô chứa giá trị “Nam” trong vùng từ C2 tới C31.

=COUNTIF($D$2:$D$31,I11) hoặc =COUNTIF($D$2:$D$31,18) – đếm số ô chứa giá trị “18” từ C2 tới C31.

Lưu ý: Criteria không làm phân biệt chữ in hoa hay chữ in thường. Vì vậy, “Nam” hoặc “NAM”, “nam” đều được chấp nhận như nhau. Điều này thỉnh thoảng hữu ích vì bạn không cần quan tâm tới việc nhập liệu chính xác, nhưng đôi khi nó lại ảnh hưởng đến việc tìm kiếm thông tin. Bạn muốn xử lý thế nào? Có nhiều phương pháp khác nhau. Tại đây, tôi gợi ý sử dụng kết hợp giữa TRUEFALSE với hàm EXACT để tiếp cận. Hãy tự bạn thử nghĩ và vận dụng xem sao nhé.

Phải chăng không khó để nhớ? Bây giờ, chúng ta hãy tiếp tục tìm hiểu các biến thể thông qua ví dụ sau.

Dùng COUNTIF kết hợp với kí tự đại diện (wildcard)

Kí tự đại diện (Wildcard) đưa vào để thay thế cho các kí tự khác trong ô. Có ba loại kí tự đại diện là dấu sao (*), dấu hỏi (?) và dấu ngã (~).

*Lưu ý: Wildcard có thể dùng để thay thế cho bất kỳ giá trị trong chuỗi kí tự nào nhưng không áp dụng cho định dạng số (Number). Trong trường hợp như vậy, hãy đổi toàn bộ về dạng TEXT sử dụng hàm TEXT, rồi kiểm tra lại với hàm ISTEXT và cuối cùng tiến hành COUNTIF kết hợp với wildcard như bình thường.

Để hiểu rõ hơn, bạn có thể tham khảo ví dụ sau:

[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 trường hợp này, chúng ta sẽ tính cho hai tình huống: những người mang họ và những người có tên là Anh.

Để đếm những người họ , chúng ta sẽ sử dụng cú pháp “Lê*” trong điều kiện hoặc $I$8 & “*” nếu muốn tham chiếu tới ô I8. Để biết thêm về kí tự đại diện, bạn đọc thêm bài viết về Kí tự đại diện Wildcard.

Khi áp dụng hàm COUNTIF cơ bản, ta có thể tìm ra số lượng ô đáp ứng được điều kiện.

Xin mời bạn xem qua ví dụ thứ ba ngay sau đây.

Sử dụng hàm COUNTIF để đếm số lượng với các toán tử so sánh >=, <=, >

[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”]

Khi thực hiện việc đếm sử dụng…

Chúng ta có thể đặt điều kiện dưới dạng các phép toán >, <, hoặc =, và có hai phương pháp phổ biến để thực hiện, bao gồm điều kiện trực tiếp và điều kiện tham chiếu.

Trong trường hợp điều kiện trực tiếp, người ta đặt cả điều kiện vào các dấu ngoặc kép tương tự như “>=a”, ở đây >= là toán tử so sánh và a là giá trị cần so sánh.

Đối với điều kiện tham chiếu, phép so sánh được bao trong dấu ngoặc kép và kết hợp với biển tham chiếu: “>=”&I8, trong đó >= đại diện cho toán tử so sánh, và I8 là ô tham chiếu.

Qua những minh họa trên, hy vọng quý vị đã có cái nhìn tổng quan hơn về hàm COUNTIF. Bây giờ, chúng ta hãy xem xét tính năng của hàm này qua một ví dụ cuối cùng.

Ứng dụng hàm COUNTIF để lọc các giá trị lặp lại

[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”]

Ứng dụng này của hàm COUNTIF cho phép giúp đơn giản hóa việc lọc ra các email hoặc tên khách hàng bị trùng lặp. Chắc chắn bạn đã nắm được cách tiếp cận: tính toán số lần một giá trị xuất hiện trong một cột dữ liệu và sau đó lọc ra theo các con số nguyên.

Áp dụng hàm COUNTIF kết hợp với AND hoặc OR

Trong cú pháp của hàm Countif,

Rõ ràng, ở đây ta nói về hàm AND, và tất cả các điều kiện đã được liệt kê một cách cụ thể, phải không?

Xét hàm AND một cách chi tiết:

Ra vấn đề nảy sinh:

  1. Hàm AND cũng như OR sẽ cho kết quả là TRUE hoặc FALSE.
  2. Nguyên lý so sánh của hàm AND là gì? Điều gì sẽ được đánh giá là lớn hơn hay nhỏ hơn?

Việc tích hợp AND hoặc OR vào COUNTIF sẽ khiến cho tham số range được so sánh với criteriaTRUE hoặc FALSE (hoặc #VALUE!). Và chắc chắn, điều này sẽ khiến kết quả trở về 0.

Làm thế nào để giải quyết vấn đề này?

Cách 1: Thay thế bằng hàm COUNTIFS

Hàm COUNTIFS hỗ trợ đếm với nhiều điều kiện riêng biệt. Không cần ép buộc sử dụng COUNTIF khi bạn có thể lựa chọn sử dụng hàm COUNTIFS.

Cách 2: Tạo cột phụ

Siêu Marketing đưa ra gợi ý như sau: Tạo một cột phụ sử dụng hàm And để cho ra kết quả True hoặc False, sau đó áp dụng COUNTIF với cột phụ đó.

Cách 3: Dùng toán học logic

Phương pháp này dùng logic toán: Để tìm số giá trị nằm giữa hai giới hạn A và B, ta lấy số lượng thỏa mãn A trừ đi số lượng thỏa mãn B (với giả định B thuộc A).

Kết luận

Những điểm mà chúng tôi đã trình bày chỉ mới là những ứng dụng cơ bản của COUNTIF. Còn vô số ứng dụng khác đang chờ bạn khám phá, vì thế hãy không ngừng thực hành nhé!

Chúc mọi người học tập hiệu quả.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop