Bạn là nhân viên văn phòng hay bạn mong muốn học thêm kiến thức về tính toán trong Excel. Mỗi ngày bạn phải xử lý với khối lượng lớn dữ liệu với nhiều cú pháp hàm khiến cho bạn không nhớ nổi mình nên xử lý vấn đề đấy như thế nào. Bạn có đang tìm cách tổng hợp và thống kê số lần xuất hiện của một giá trị bất kỳ trong dữ liệu của bạn. Hãy cùng Blog Phần Mềm tìm hiểu hàm COUNTIF trong Excel, việc này sẽ giúp ích cho bạn tổng hợp và thống kê dữ liệu rất nhanh đó.
Nội dung bài viết
Hàm COUNTIF trong Excel 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 sẽ được sử dụng để đếm các ô chứa ngày, số và văn bản. Tiêu chí được 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.
Xem thêm: 3 cách dùng hàm MIN, MAX trong Excel có thể bạn chưa biết
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). Vì vậy, cú pháp được sử dụng để tạo ra tiêu chí không giống nhau và COUNTIF yêu cầu một phạm vi ô nên bạn không thể dùng một mảng.
COUNTIF chỉ giúp đỡ và hỗ trợ một điều kiện độc nhất. Nếu như bạn cần sử dụng nhiều điều kiện, hãy sử dụng hàm COUNTIFS. Nếu như bạn cần thực hành các bước các giá trị trong đối số phạm vi như một phần của kiểm duyệt logic, hãy coi 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 mong 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 thành quả trong range, có thể là số, biểu thức, tham chiếu ô hoặc chuỗi văn bản.
Chú ý với bạn đọc:
- Hàm COUNTIF trả về mục đích một khi 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 số đó 1 dấu hỏi là 1 ký tự và 1 dấu sao là 1 chuỗi ký tự. Tùy thuộc theo 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.
Cách dùng hàm COUNTIF trong Excel và ví dụ
Mời bạn tìm biết cách dùng hàm COUNTIF qua các ví dụ tìm kiếm dữ liệu không giống nhau.
Ví dụ 1: Dùng hàm COUNTIF tìm kiếm số lượng tên hàng Dưa hấu có trong bảng sau
Cú pháp như sau: =COUNTIF(B2:B8,”dưa hấu”) rồi nhấn Enter để thực hiện hàm.
Và kết quả sẽ đếm ra 2 giá trị có tên là Dưa hấu.
VD 2: Vẫn với bảng trên, chúng ta sẽ đếm số lượng mặt hàng lớn hơn 6 kg
Công thức như sau: =COUNTIF(C2:C8,”>6″) rồi nhấn Enter để thực hiện hàm.
Kết quả là sẽ đếm ra được là có 3 mặt hàng có khối lượng lớn 6kg.
Ngoài ra, đối với các trường hợp sau:
Đếm số lượng nhỏ hơn 6kg, chỉnh sửa đối số criteria là “<6”. Đếm số lượng lớn hơn hoặc bằng 6kg, chỉnh sửa đối số criteria là “>=6”. Đếm số lượng nhỏ hơn hoặc bằng 6kg, thay đổi đối số criteria là “<=6”.
VD 3: Sử dụng hàm COUNTIF tìm số lượng hàng không phải là Dưa hấu trong bảng
Công thức như sau: =COUNTIF(B2:B8,”<>Dưa hấu”) rồi nhấn Enter để thực hiện hàm. Trong số đó “<>” là ký tự khác
Kết quả đếm được là có 5 sản phẩm không phải dưa hấu.
VD 4: Dùng hàm COUNTIF tìm kiếm đơn hàng có tên Sầu riêng sử dụng ký tự thay thế
Để đếm mặt hàng bằng việc dùng ký tự thay thế thì ở đối số criteria, bạn nhập chữ cái đầu của tên mặt hàng và theo sau đấy là ký tự hoa *.
*Lưu ý: Cách này chỉ có thể ứng dụng khi các sản phẩm có chữ cái đầu không giống nhau.
Công thức như sau: =COUNTIF(B2:B8,”S*”) rồi nhấn Enter để thực thi hàm.
Và đây là kết quả.
VD 5: Tìm kiếm mặt hàng có tên khác ô B3
Ở đây chúng ta sẽ tìm những sản phẩm có tên khác ô B3 là Xoài bằng cách sử dụng ký tự “<>” và “&” trước ô tham chiếu, với cú pháp hàm =COUNTIF(B2:B8,”<>”&B3).
Kết quả sẽ ra số sản phẩm chuẩn xác là 6.
Sử dụng hàm COUNTIF kết hợp AND hoặc OR
Với cú pháp của hàm Countif
=COUNTIF(range, criteria)
Rõ ràng là criteria ở đây chính là hàm AND, và điều kiện ta đã liệt kê ra rất đầy đủ rồi, phải không?
Ta hãy xem qua hàm AND một chút:
Vấn đề ở đây là:
Hàm thủ thuật excel AND (và cả OR) sẽ trả về giá trị là TRUE hoặc FALSE. Logic so sánh trong hàm AND. Cái gì sẽ lớn hơn, hoặc nhỏ hơn?
Logic ở đây là: Để tìm số những số thỏa mãn trong khoảng A và B, thu thập 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).
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!). 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ó khả năng 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 đấy sử dụng COUNTIF với cột phụ.
Cách 3: Sử dụng logic toán học
Dùng hàm RANK liên kết 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ố, tuy nhiên cũng sẽ bỏ qua việc trùng lặp xếp hạng.
Để hiểu điều này hiệu quả hơn, hãy xem cách RANK.EQ công việc cùng với COUNTIF. Công thức có dạng như sau:
Xem thêm: Hướng dẫn 4 cách đánh số trang trong Excel nhanh, đơn giản
=RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1Thực hiện công thức này sẽ xử lý 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 đánh giá là có cùng xếp hạng, giờ lại được xếp hạng khác nhau.
Do đó, sử dụng RANK.EQ với COUNTIF đã xử lý được một nửa vấn đề, tuy nhiên 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 học 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ệ 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 coi công thức bên dưới:
Công thức có vẻ khó hiểu, tuy nhiên đấy 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 việc này, bạn sẽ có được kết quả mong muốn, cho phép sự trùng lặp xếp hạng và không bỏ qua số.
Trong khi đưa ra thứ hạng cho học sinh của mình, bạn có thể 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 có sự trùng lặp, bạn có thể 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.
So sánh kết quả trong 3 hàng cạnh nhau để hiểu rõ hơn cách thức công việc của từng phương pháp thứ hạng mục.
Tổng kết
Đừng bao giờ quên trên đây chỉ là những ví dụ căn bản nhất của hàm COUNTIF trong Excel. Còn rất nhiều ứng dụng đang chờ các bạn khám phá, bởi vậy nên hãy luyện tập thường xuyên nhé! Cảm ơn các bạn đã theo dõi bài viết của blogphanmem.vn
Chúc các bạn học tốt.
Nguồn: Tổng hợp
