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 đó.
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.
Trong tất cả các hàm Excel, hàm đếm có điều kiện COUNTIFS và COUNTIF có lẽ thường dễ bị nhầm lẫn nhất vì chúng trông rất giống nhau và cả hai đều cùng có mục đích dùng để đếm các ô dựa trên các điều kiện đã được chỉ định.
Sự khác biệt duy nhất giữa hai hàm này là COUNTIF được thiết kế để đếm các ô có một điều kiện trong một phạm vi, trong khi COUNTIFS có thể đánh giá các điều kiện khác nhau trong cùng hoặc trong các phạm vi khác nhau.
Nhằm giúp bạn đọc phân biệt một cách chính xác và nắm được cách sử dụng hai hàm đếm có điều kiện COUNTIFS đúng trong Excel, trong bài viết này Unica sẽ giới thiệu và hướng dẫn bạn đọc cách sử dụng và lựa chọn công thức hiệu quả nhất cho từng nhiệm vụ từng bài toán cụ thể, đặc biệt là đối với hàm COUNTIFS.
1. Hàm COUNTIFS của Excel - cú pháp và cách sử dụng
Như đã đề cập, hàm COUNTIFS của Excel được dùng để đếm các ô trên nhiều phạm vi dựa trên một hoặc một số điều kiện. Hàm COUNTIFS có sẵn trong Excel 2016, 2013, Excel 2010 và Excel 2007, vì vậy bạn có thể sử dụng các ví dụ dưới đây trong bất kỳ phiên bản học Excel nào.
Cú pháp: =COUNTIFS[criteria_range1, criteria1, [criteria_range2, criteria2],…]
Trong đó:
- criteria_range1: Vùng chứa điều kiện thứ nhất, đây là phần bắt buộc phải có
- criteria1: Điều kiện thứ nhất [thuộc trong vùng điều kiện thứ nhất] gồm có các dạng số, ô tham chiếu, chuỗi văn bản, hàm Excel hoặc vùng Excel khác.
- criteria_range2: Vùng chứa điều kiện thứ hai[ yêu cầu vùng criteria_range2 phải có độ lớn bằng nhau]
- criteria2, ... Điều kiện thứ hai [thuộc trong vùng điều kiện thứ nhất]. Bạn có thể chỉ định tối đa 127 cặp phạm vi / điều kiện trong công thức của mình.
- Tương tự như vậy với các điều kiện khác cũng sẽ áp dụng như vậy.
Hình minh họa
Thực tế, bạn không cần phải nhớ cú pháp của hàm đếm có điều kiện COUNTIFS. Microsoft Excel sẽ hiển thị các đối số của hàm ngay khi bạn bắt đầu nhập; đối số bạn đang nhập vào lúc này được tô đậm.
Bạn chỉ cần nhớ bạn muốn sử dụng hàm COUNTIFS trong Excel là được.
LƯU Ý:
- Bạn có thể sử dụng hàm COUNTIFS trong Excel để đếm các ô trong một phạm vi với một điều kiện duy nhất cũng như trong nhiều phạm vi với nhiều điều kiện. Nếu sau này, chỉ những ô đáp ứng tất cả các điều kiện được chỉ định mới được tính.
- Criteria1 bạn có thể biểu diễn chúng giống như các ví dụ: 10, "0", C2:C7,"=0"]
Và số lượng là 2 ["Anh đào " và " Chanh "]:
Hình minh họa
Công thức 2. Công thức COUNTIFS có cùng tiêu chí
Khi bạn muốn đếm các mục có điều kiện giống hệt nhau, bạn vẫn cần cung cấp từng cặp điều kiện_range / điều kiện .
Ví dụ: đây là công thức đúng để đếm các mục có 0 cả trong cột B và cột C:
=COUNTIFS[$B$2:$B$7,"=0", $C$2:$C$7,"=0"]
Công thức COUNTIFS này trả về 1 vì chỉ " Nho " có giá trị "0" trong cả hai cột
Hình minh họa
2.2. Cách đếm ô có nhiều điều kiện khác nhau [logic OR]
Như bạn đã thấy trong các ví dụ trên, việc đếm các ô đáp ứng tất cả các điều kiện đã chỉ định là dễ dàng vì hàm đếm có điều kiện COUNTIFS được thiết kế để hoạt động theo cách này.
Nhưng nếu bạn muốn đếm các ô mà ít nhất một trong các điều kiện được chỉ định là TRUE , tức là dựa trên logic OR thì sao? Nhìn chung, có hai cách để thực hiện việc này - bằng cách thêm một số công thức COUNTIFS hoặc sử dụng công thức SUM COUNTIFS.
Công thức 1. Thêm hai hoặc nhiều công thức trong COUNTIFS
Ví dụ: để có được số lượng đơn đặt hàng " Đã hủy " và " Đang chờ xử lý" cho " Táo ", hãy sử dụng công thức sau:
=COUNTIFS[$A$2:$A$11, "Táo", $C$2:$C$11,"Đã hủy"] + COUNTIFS[$A$2:$A$11, "Táo", $C$2:$C$11,"Đang chờ xử lý"]
Hình minh họa
Công thức 2. SUMIFS với hằng số mảng
Trong các tình huống khi bạn phải đánh giá rất nhiều điều kiện, cách trên không phải là cách tốt nhất vì công thức của bạn sẽ quá dài.
Để thực hiện các phép tính tương tự trong một công thức nhỏ gọn hơn, hãy liệt kê tất cả các điều kiện của bạn trong một hằng số mảng và cung cấp mảng đó cho đối số điều kiện của hàm đếm có điều kiện COUNTIFS.
Ta sẽ dùng công thức sau:
-
=SUM[COUNTIFS[range,{"criteria1","criteria2","criteria3",…}]]
Trong bảng dưới đây, để đếm ‘Táo” có trạng thái " Đã hủy " hoặc " Đang chờ xử lý " hoặc " Nhập kho ", công thức sẽ như sau:
-
=SUM[COUNTIFS[$A$2:$A$11,"Táo",$C$2:$C$11,{"Đã hủy","Đang chờ xử lý","Nhập kho"}]]
Hình minh họa
2.3. Cách đếm số giữa 2 số được chỉ định
Công thức COUNTIFS để đếm các ô giữa hai số
Để tìm hiểu có bao nhiêu số từ 5 đến 10 [không bao gồm 5 và 10] được chứa trong các ô từ C2 đến C10, hãy sử dụng công thức này:
= COUNTIFS [C2: C10, "> 5", C2: C10, " = 5", B2: B10, "
>> Xem thêm: Hướng dẫn chi tiết cách sử dụng hàm DSUM và ví dụ
2.4. Cách sử dụng COUNTIFS với các ký tự đại diện
Trong các câu thức Excel, bạn có thể sử dụng các ký tự đại diện như sau:
- ? : được sử dụng để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định.
- * " Được sử dụng để đếm ô chứa một từ cụ thể hoặc một k1 tự trong ô.
Ví dụ bạn có danh sách các dự án trong cột A. Bạn muốn biết có bao nhiêu dự án đã ký kết, tức là cột B có chứa tên người. Hãy thêm một điều kiện thứ hai - End Date trong cột D.
=COUNTIFS[B2:B10,”*”,D2:D10,””&””]]
Lưu ý, bạn không thể sử dụng ký tự đại diện trong tiêu chí thứ 2 vì cột D chứa kiểu dữ liệu ngày tháng chứ không phải dạng text. Đó là lý do tại sao bạn sử dụng tiêu chí “”&”” để tìm ô chứa dữ liệu.
Hình minh họa
Trên đây là hàm đếm có điều kiện và các cách thông dụng nhất để bạn thực hiện sử dụng các hàm đếm đó trong Excel, từ đó, dễ dàng thống kê và phân tích số liệu một cách hiệu quả, cải thiện tối đa hiệu suất công việc. Ngoài ra bạn đọc quan tâm có thể tham khảo thêm các khoá học tin học văn phòng như học Word online, học Powerpoint cơ bản sau tại Unica và lựa chọn cho mình khóa học hay phù hợp nhất nhé! Cảm ơn các bạn đã đọc bài và chúc bạn thành công!
Tags: Excel