Hàm COUNTIF và cách đếm có điều kiện trong Excel

Thứ hai - 01/07/2024 01:55

Hàm COUNTIF trong Excel là gì? Cách dùng hàm đếm có điều kiện COUNTIF trong Excel như thế nào? Hãy cùng Quantrimang.com tìm hiểu nhé!

Microsoft Excel là phần mềm xử lý dữ liệu bảng tính nổi tiếng với rất nhiều hàm hữu ích. Nhờ chúng, bạn có thể tính toán nhanh hàng loạt dữ liệu trong bảng doanh số, lương thưởng hàng tháng và nhiều hơn thế nữa. Tất nhiên, để sử dụng hàm nào đó, bạn cần phải cung cấp dữ liệu cũng như điều kiện tương ứng.

Hàm COUNTIF là hàm đếm có điều kiện trong Excel. Bạn có thể dùng hàm COUNTIF để đếm ô trùng lặp, đếm dữ liệu. Dưới đây là thông tin chi tiết hơn về COUNTIF, cú pháp hàm COUNTIF và vài ví dụ minh họa để bạn hiểu cách sử dụng hàm Excel cơ bản này nhé.

Hàm COUNTIF dùng để làm gì?

COUNTIF là một hàm Excel để đếm các ô trong một phạm vi đáp ứng một điều kiện duy nhất. COUNTIF có thể được sử dụng để đếm các ô chứa ngày, số và văn bản. Tiêu chí được sử dụng trong COUNTIF hỗ trợ các toán tử logic (>, <, <>, =) và những ký tự đại diện (*,?) để đối sánh từng phần.

COUNTIF nằm trong nhóm 8 hàm trong Excel chia tiêu chí logic thành hai phần (phạm vi + điều kiện). Do đó, cú pháp được sử dụng để xây dựng tiêu chí khác nhau và COUNTIF yêu cầu một phạm vi ô nên bạn không thể sử dụng một mảng.

COUNTIF chỉ hỗ trợ một điều kiện duy nhất. Nếu bạn cần dùng nhiều điều kiện, hãy sử dụng hàm COUNTIFS. Nếu bạn cần thao tác các giá trị trong đối số phạm vi như một phần của kiểm tra logic, hãy xem hàm SUMPRODUCT và/hoặc FILTER.

Cú pháp của hàm COUNTIF trong Excel

Hàm COUNTIF trên Excel có cú pháp là =COUNTIF(range;criteria).

Trong đó range là phần vùng muốn đếm dữ liệu bắt buộc phải có. Có thể chứa số, mảng hoặc tham chiếu có chứa số. Các giá trị trống sẽ được bỏ qua. Criteria là phần điều kiện bắt buộc phải có để đếm các giá trị trong range, có thể là số, biểu thức, tham chiếu ô hoặc chuỗi văn bản.

Lưu ý với bạn đọc:

Hàm COUNTIF trả về kết quả sau khi sử dụng điều kiện với các chuỗi ký tự hơn 255 ký tự.
Đối số criteria cần để trong dấu ngoặc kép. Không phân biệt chữ thường hay chữ hoa.

Có thể sử dụng ký tự dấu chấm hỏi và dấu sao trong điều kiện criteria, trong đó 1 dấu hỏi là 1 ký tự và 1 dấu sao là 1 chuỗi ký tự. Tùy vào từng cài đặt trên máy mà dấu phân cách trong hàm là , hoặc ; để sử dụng.

Ví dụ về cách sử dụng hàm COUNTIF

Chúng ta sẽ tìm hiểu cách sử dụng hàm COUNTIF với bảng dữ liệu dưới đây và các ví dụ tìm kiếm dữ liệu khác nhau.

Bảng dữ liệu

1. Tìm kiếm số lượng tên hàng Xoài trong bảng

Chúng ta có công thức thực hiện là =COUNTIF(B2:B8,"xoài") rồi nhấn Enter để thực hiện hàm.

Tìm giá trị

Kết quả sẽ ra 1 giá trị tên Xoài trong bảng dữ liệu.

Kết quả tìm kiếm

2. Tìm số lượng hàng không phải là Chanh trong bảng

Chúng ta sử dụng điều kiện tên hàng không phải là Chanh là “<>chanh” rồi nhập công thức =COUNTIF(B2:B8,"<>chanh"). Kết quả sẽ ra 5 mặt hàng không có tên chanh trong bảng dữ liệu.

Tím giá trị không phải tên Chanh

3. Tìm số mặt hàng có số lượng bán >= 10 kg

Điều kiện sử dụng với nội dung là “>=10” tại cột số lượng bán với công thức hàm là =COUNTIF(C1:C8,">=10") và nhấn Enter.

Tìm số lượng bán

Kết quả sẽ ra 6 mặt hàng với số lượng bán >= 10 kg.

Số mặt hàng điều kiện kg bán

4. Tìm kiếm đơn hàng có tên Chanh sử dụng ký tự thay thế

Chúng ta có thể thay thế bằng ký tự * để tìm kiếm giá trị với công thức là =COUNTIF(B2:B8,"C*") và nhấn Enter.

Dùng ký tự * thay thế

Kết quả sẽ ra được như hình dưới đây.

Kết quả tìm giá trị *

5. Tìm kiếm mặt hàng có tên khác ô B2

Ở đây chúng ta sẽ tìm những mặt hàng có tên khác ô B2 là Chanh bằng cách dùng ký tự & trước ô tham chiếu, với cú pháp hàm =COUNTIF(B2:B8,"<>"&B2). Kết quả cũng ra số mặt hàng chính xác là 5.

Tìm giá trị khác ô điều kiện

Kết hợp hàm COUNTIF với những hàm khác trong Excel

Sử dụng hàm RANK kết hợp với hàm COUNTIF

Có thể sử dụng hàm RANK.EQ kết hợp với hàm COUNTIF để dừng việc bỏ qua các số, nhưng cũng sẽ bỏ qua việc trùng lặp thứ hạng.

Để hiểu điều này tốt hơn, hãy xem cách RANK.EQ hoạt động cùng với COUNTIF. Công thức có dạng như sau:

=RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1

Thực hiện công thức này sẽ giải quyết vấn đề bỏ qua số.

Giải quyết vấn đề bỏ qua số

Không có sự trùng lặp trong các cấp bậc trên. Nhưng, James Walker và Gillian Tillman, những người được cho là có cùng thứ hạng, giờ lại được xếp hạng khác nhau.

Do đó, sử dụng RANK.EQ với COUNTIF đã giải quyết được một nửa vấn đề, nhưng nó không tạo ra kết quả mong muốn.

Sử dụng hàm SUMPRODUCT với hàm COUNTIF

Để xếp hạng sinh viên trong danh sách bằng cách ấn định các thứ hạng giống nhau cho những tỷ lệ phần trăm tương đương mà không bỏ qua bất kỳ số nào, bạn có thể sử dụng hàm SUMPRODUCT với COUNTIF.

Hãy xem công thức bên dưới:

Sử dụng hàm SUMPRODUCT với COUNTIF

Công thức có vẻ phức tạp, nhưng đó là cách tốt nhất để xếp hạng các mục một cách chính xác. Bằng cách này, bạn có thể đạt được kết quả mong muốn, cho phép sự trùng lặp thứ hạng và không bỏ qua số.

Trong khi đưa ra kết quả cho học sinh của mình, bạn có thể sử dụng trực tiếp công thức SUMPRODUCT để thay thế cho hàm RANK. Để tính toán xếp hạng không trùng lặp, bạn có thể sử dụng hàm RANK.EQ một mình hoặc với hàm COUNTIF.

Sử dụng hàm RANK.EQ một mình hoặc với hàm COUNTIF

Thay đổi thứ tự của kết quả cuối cùng

Trên tab Data, nhấp vào nhóm Sort and Filter và chọn thứ tự tăng dần để sắp xếp thứ hạng.

Sắp xếp thứ hạng

So sánh kết quả trong 3 hàng cạnh nhau để hiểu rõ hơn cách thức hoạt động của từng phương pháp xếp hạng mục.

Những vấn đề thường gặp khi dùng hàm COUNTIF trong Excel

Vấn đề	Giải pháp
Trả về giá trị sai cho các chuỗi ký tự dài	Hàm COUNTIF trả về kết quả sai khi bạn dùng nó để khớp các chuỗi dài hơn 255 ký tự. Để khớp các chuỗi dàn hơn 255 ký tự, dùng hàm CONCATENATE hoặc toán tử `&`. Ví dụ: =COUNTIF(A2:A5,"chuỗi dài"&"chuỗi dài khác").
Không có giá trị được trả về	Đảm bảo đặt đối số tiêu chí trong ngoặc kép.
Một công thức COUNTIF nhận một lỗi #VALUE! khi tham chiếu tới worksheet khác	Lỗi này xảy ra khi công thức chứa hàm tham chiếu tới các hàm tham chiếu tới ô hoặc phạm vi trong một workbook đã đóng hoặc các ô đã được tính. Để tính năng này hoạt động, bạn phải mở workbook khác.

Sự khác biệt giữa hàm COUNTIF và COUNTIFS trong Excel

Cả COUNTIF và COUNTIFS đều có chung mục đích sử dụng. Cả hai đều được dùng để đếm số lượng ô trùng khớp với điều kiện. COUNTIF là một hàm đơn giản, phù hợp khi bạn chỉ cần thực hiện kiểm tra đơn giản. Trái lại, COUNTIFS cực kỳ hữu ích khi bạn cần kiểm tra dữ liệu theo nhiều điều kiện. Xem thêm cách dùng hàm COUNTIFS.

Nó có thể sao chép chức năng của COUNTIFS bằng nhiều hàm AND và OR trong COUNTIF, nhưng thật khó đọc và soạn nó. COUNTIFS cung cấp giải pháp đơn giản hơn, giúp người dùng phân tích dữ liệu thật nhanh với nhiều điều kiện mà không cần lồng nhiều cấp độ của hàm IF.

Điều quan trọng bạn cần nhớ là COUNTIFS sẽ kiểm tra tất cả điều kiện dựa trên tập hợp dữ liệu đó. Như vậy, nếu dữ liệu đó chỉ khớp với một trong số nhóm các điều kiện được cung cấp, bạn nên thêm nhiều lệnh COUNTIF.

Nhìn chung, cả COUNTIF và COUNTIFS đều là hai hàm Excel xuất sắc trong việc lấy dữ liệu cần thiết ra khỏi một nhóm dữ liệu lớn. Bạn chỉ cần dùng chúng thật hợp lý theo từng trường hợp.

Trên đây là cách sử dụng hàm COUNTIF với những ví dụ cụ thể để sử dụng hàm và kết hợp các ký tự như thế nào để tìm giá trị thỏa mãn điều kiện trong vùng dữ liệu. Lưu ý với dấu phân cách , hoặc ; sẽ tùy vào từng máy, nếu báo lỗi ở phần dấu bạn cần kiểm tra lại dấu phân cách.

Thường thì hàm COUNTIF sẽ dùng với những bảng dữ liệu thống kê, yêu cầu đếm số ô chứa giá trị thỏa mãn điều kiện cho sẵn. Cú pháp của hàm COUNTIF cũng khá đơn giản thôi, bạn chỉ nhìn qua một lần, xem các ví dụ bên trên của Quantrimang.com thì chắc chắn sẽ biết cách thực hiện.

Chúc các bạn thực hiện thành công!

Nguồn tin: Quantrimang.com: