Cách sử dụng hàm đếm COUNT và hàm đếm có điều kiện COUNTIF, COUNTIFS trong excel là một trong những kỹ năng cần thiết khi bạn sử dụng phần mềm này. Trong bài viết lần này, excel.net.vn sẽ cùng các bạn tìm hiểu thêm về các hàm đếm hữu ích đó.
Bạn đang xem: Đếm có điều kiện trong excel
1. Hàm đếm có một điều kiện COUNTIF
Để có cái nhìn trực tiếp hơn về hàm này, chúng ta hãy cùng xem ví dụ sau đây.
Chúng ta có một bảng thống kê kết quả các trận đấu của Đội tuyển Việt Nam trong AFF Cup. Câu hỏi đưa ra là hãy đếm số trận Thắng, trận Hòa và trận Thua của đội tuyển.
Ở đây ta có thể sử dụng hàm COUNTIF để đếm.
Công thức ở đây là:
=COUNTIF(mảng dữ liệu cần đếm, điều kiện được đưa ra)
Hãy cùng phân tích công thức với câu hỏi ở trong bài toán trên. Ở đây mảng dữ liệu cần đếm cho câu hỏi của bài toán chính là kết quả của các trận đấu (Thắng/Hòa hoặc Thua). Vì vậy nó chính là cột Kết quả, nằm ở mảng G3:G10.
Điều kiện được đưa ra ở đây là số trận Thắng vì vậy nó chính là “Thắng” hoặc chính là dữ liệu của ô B14.
Kết hợp lại ta có:
=COUNTIF(G3:G10,B14)
Làm tương tự với hai dòng ở dưới (Hòa và Thua). Bạn sẽ nhận được kết quả sau:
2. Hàm đếm có nhiều điều kiện COUNTIFS
Cách dùng hàm COUNTIFS nhiều điều kiện không khác với hàm COUNTIF có một điều kiện là mấy, khác biệt là nó tăng thêm nhiều mảng và nhiều điều kiện.
Chúng ta cùng nhìn vào ví dụ dưới đây. Trừ đếm số trận thắng, trận hòa và trận thua của Đội tuyển Việt Nam ra, chúng ta còn muốn cho thêm điều kiện đó là chỉ tính các trận ở Bảng A.
Như vậy điều kiện và mảng cần xét ở đây từ 1 đã tăng lên thành 2, chúng ta không thể dùng hàm COUNTIF như cũ mà phải dùng COUNTIFS.
Công thức của hàm COUNTIFS như sau:
=COUNTIFS(mảng cần đếm số 1, điều kiện để đếm của mảng 1, mảng cần đếm số 2, điều kiện để đếm của mảng 2,…)
Phân tích công thức và câu hỏi của bài toán ta thấy, mảng cần đếm số 1 và điều kiện để đếm của mảng 1 giống với ví dụ trong phần 1 là G3:G10 và B14.
Cái cần đếm tiếp theo chính là số “Bảng A” của cột Vòng. Vây mảng cần đếm số 2 chính là B3:B10 còn điều kiện để đếm của mảng đó là “Bảng A”.
Từ đó, ta có công thức sau:
= COUNTIFS(G3:G10, B14, B3:B10, “Bảng A”)
Và đây là kết quả bạn sẽ nhận được:
Cảm ơn các bạn đã theo dõi bài viết về hàm đếm có điều kiện của excel.net.vn. Chúc các bạn có thể thực hiện thành công.
Khi làm việc với một lượng dữ liệu lớn, bạn sẽ rất khó khăn cũng như mất rất nhiều thời gian để có thể thống kê hết tất cả phần dữ liệu đó. Hàm Countif chính là một giải pháp giúp cho bạn hoàn thành quá trình làm báo cáo hay thống kê số liệu một cách nhanh chóng nhất. Cùng chúng tôi tìm hiểu ngay về hàm Countif là gì cũng như cách sử dụng chúng hiệu quả nhất nhé!
Hàm COUNTIF là gì?
Hàm COUNTIF là hàm đếm có điều kiện được sử dụng trong Excel và Google Sheet. Có thể dùng hàm Countif để đếm ô trùng lặp hay đếm dữ liệu. Thường thì hàm Countif sẽ dùng đối với những bảng dữ liệu thống kê để đếm số ô chứa giá trị thỏa mãn điều kiện cho trước.
Công thức hàm đếm có điều kiện
Công thức hàm Countif: =COUNTIF(range,criteria)
Trong đó:
Range: Là vùng cần đếm dữ liệu bắt buộc phải có.Criteria: là điều kiện để đếm dữ liệu trong Range, cần để trong dấu ngoặc kép, có thể là kiểu văn bản, kiểu số, biểu thức hay tham chiếu ô.
Pad Chưa?
Cách sử dụng hàm Countif excel/google sheet trong các chuỗi ô
Hàm COUNTIF dùng cho văn bản và số (chính xác tuyệt đối)
Đếm theo giá trị văn bản:
Công thức: =COUNTIF(A2:A9,"Táo")
Mô tả: Công thức trên đếm số sản phẩm “Táo” trong vùng dữ liệu A2:A9. Kết quả là 3
Đếm theo giá trị số:
Công thức: =COUNTIF(B2:B9,65000)
Mô tả: Công thức trên đếm số lần giá “65000” xuất hiện trong vùng dữ liệu B2:B9. Kết quả là 3.
Hàm đếm có điều kiện sử dụng cho ký tự đại diện (khớp một phần)
Nếu bảng tính Excel của bạn chứa nhiều biến thể của từ khóa cần tính số lượng, bạn có thể sử dụng ký tự đại diện để đếm số lượng tất cả các ô chứa một từ, cụm từ hoặc chữ cái nhất định nào đó là một phần của nội dung ô.
Ví dụ trong trường hợp có rất nhiều loại xoài trong bảng và bạn muốn đếm số lần xuất hiện của tất cả xoài thì bạn nhập công thức: =COUNTIF(B3:B10,“*xoài*”)
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.
Đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định
Ký tự đại diện, dấu sao (*) hay dấu chấm hỏi (?) được sử dụng tùy thuộc vào kết quả mà người dùng muốn.
Để biết số ô bắt đầu hoặc kết thúc bằng một văn bản nhất định nào đó mà không cần biết có bao nhiêu ký tự khác trong ô, bạn có thể sử dụng các công thức sau:
Tính các ô bắt đầu bằng chữ “Mr”: = COUNTIF (C2: C10, “Mr*”)Tính các ô kết thúc bằng chữ “ed”: = COUNTIF (C2: C10, “*ed”)
Để đếm số ô bắt đầu hoặc kết thúc bằng các ký tự nhất định và chứa một số lượng ký tự nhất định, bạn sử dụng dấu hỏi (?) tại điều kiện hàm COUNTIF:
= COUNTIF (B2: B9, “??anh”) – đếm số ô kết thúc bằng chữ “anh” và có chính xác 5 ký tự trong các ô từ B2 đến B9, bao gồm cả khoảng cách.
= COUNTIF (B2: B9, “Miss ??????”) – đếm số ô bắt đầu bằng chữ “Miss” và có chính xác 9 ký tự trong các ô từ B2 đến B9, bao gồm cả khoảng cách.
Lưu ý: Nếu bạn muốn đếm số ô chứa dấu hỏi “?” hoặc dấu sao “*”, hãy thêm dấu ngã (~) vào trước dấu ? hoặc * trong công thức hàm Countif.
Ví dụ = COUNTIF (B2: B9, “* ~? *”) để đếm tất cả các ô có chứa dấu chấm hỏi trong vùng dữ liệu B2: B9.
Cách sử dụng hàm COUNTIF cơ bản với chữ cái và số trùng khớp
Sử dụng COUNTIF kết hợp những ký tự đại diện (wildcard)
Ký tự đại diện (Wildcard) là ký tự được sử dụng để thay thế các ký tự khác trong ô. Có 3 ký tự wildcard là dấu sao (*), dấu hỏi (?) và dấu ngã (~).
Ví dụ muốn đếm số người mang họ Lê và số người có tên Anh ở bảng sau ta nhập công thức:
=COUNTIF($B$2:$B$31,$I$8&”*”) - đếm số người họ Lê
=COUNTIF($B$2:$B$31,”*”&$I$11) - đếm số người tên Anh
Lưu ý: Wildcard có thể thay thế mọi giá trị trong chuỗi ký tự, nhưng không thể thay thế đối với định dạng số (Number). Do đó, nếu muốn sử dụng wildcard trong trường hợp này, bạn cần phải chuyển toàn bộ về dạng TEXT bằng hàm TEXT trước khi áp dụng công thức.
Xem thêm: Top 8 phần mềm quản lý vận chuyển logistics tốt nhất hiện nay
Cách Sử dụng hàm COUNTIF đếm với các phép so sánh lớn nhỏ bằng
Bạn có thể sử dụng điều kiện trực tiếp hoặc điều kiện tham chiếu trong hàm Countif đếm các phép so sánh lớn hơn (>), nhỏ hơn (
Với điều kiện trực tiếp, ta đặt toàn bộ điều kiện trong dấu nháy kép như sau:
Điều kiện | Công thức ví dụ | Diễn giải |
Lớn hơn | =COUNTIF(B2:B10,”>6″) | Đếm ô có giá trị lớn hơn 6 trong dải ô B2:B10 |
Bé hơn | =COUNTIF(B2:B10,” | Đếm ô có giá trị bé hơn 6 trong dải ô B2:B10 |
Bằng | =COUNTIF(B2:B10,”=6″) | Đếm ô có giá trị bằng 6 trong dải ô B2:B10 |
Khác | =COUNTIF(B2:B10,”6″) | Đếm ô có giá trị khác 6 trong dải ô B2:B10 |
Bằng hoặc lớn hơn | =COUNTIF(B2:B8,”>=6″) | Đếm ô có giá trị lớn hơn hoặc bằng 6 trong dải ô B2:B10 |
Bằng hoặc bé hơn | =COUNTIF(B2:B8,” | Đếm ô có giá trị bé hơn hoặc bằng 6 trong dải ô B2:B10 |
Với điều kiện tham số, ta đặt phép so sánh trong dấu nháy kép, kết hợp cùng ký tự & đặt trước ô tham chiếu:
Ví dụ đếm các ô trong dải ô D2:D9 có giá trị lớn hơn giá trị trong ô D3 ta nhập công thức: =COUNTIF(D2:D9,”>”&D3)
Cách sử dụng hàm COUNTIF lọc giá trị trùng lặp
Hàm countif được ứng dụng để lọc các thông tin trùng lặp trong bảng dữ liệu, thường áp dụng trong bảng data khách hàng như lọc email, lọc tên trùng lặp.
Ví dụ để tính tần số lặp lại của mỗi dữ liệu trong dải ô A2:A9, ta nhập công thức: =COUNTIF($A$2:$A$9,A2) (dấu “$” dùng để cố định hàng và cột để không bị thay đổi khi thực hiện sao chép công thức)