Hàm Countifs trong Excel được phân loại là hàm thống kê, được sử dụng để đếm số ô đáp ứng một hoặc nhiều tiêu chí cụ thể. Bài viết sau đây Ben Computer sẽ giới thiệu cho bạn chi tiết cách sử dụng cũng như cú pháp hàm Countifs trong Excel. Bạn đọc cùng tham khảo tiếp bài viết dưới đây.
Hàm Countif và hàm Countifs trong Excel
Hàm Countifs trong Excel được sử dụng để đếm ô thỏa mãn các điều kiện cho trước. Đây là hàm Excel cao cấp của hàm Counif, chỉ đếm ô với 1 điều kiện cho trước.
Điểm khác biệt lớn nhất giữa hàm Countif và hàm Countifs trong Excel là hàm Countif được sử dụng để đếm các ô dựa trên một điều kiện, tiêu chí duy nhất trong phạm vi ô. Ngược lại, hàm Countifs có thể đánh giá các tiêu chí khác nhau trong cùng hoặc các phạm vi khác nhau.
Cú pháp hàm Countifs trong Excel
Hàm Countifs trong Excel có cú pháp như dưới đây:
=COUNTIFS[criteria_range1, criteria1, [criteria_range2, criteria2]…]
Trong đó hàm COUNTIFS sử dụng các tham số sau:
– Criteria_range1 [tham số bắt buộc]: là vùng chọn đầu tiên cần thống kê.
– Criteria1 [tham số bắt buộc]: các điều kiện được kiểm tra dựa trên các giá trị. Tham số này có thể ở dạng số, biểu thức, tham chiếu ô hoặc văn bản.
– Criteria_range2, criteria2, … [tham số tùy chọn]: là các vặp vùng chọn và điều kiện bổ sung. Hàm Countifs cho phép tham số này tối đa lên đến 127 cặp phạm vi / tiêu chí.
Các tiêu chí, điều kiện đã cho có thể là:
- Là giá trị số, có thể là số nguyên, số thập phân, ngày tháng hoặc các giá trị logic.
- Chuỗi văn bản, có thể bao gồm cả các ký tự đại diện chẳng hạn như dấu hỏi chấm, dấu hoa thị, … .
Để tìm hiểu chi tiết cách sử dụng hàm Countifs Excel như thế nào, bạn đọc cùng tham khảo một số ví dụ dưới đây:
Ví dụ 1
Cho bảng dữ liệu như dưới đây:
Nhiệm vụ của bạn là đếm các mặt hàng trong kho [giá trị trong cột C lớn hơn 0] nhưng chưa được bán [giá trị cột D bằng 0].
Trong ví dụ này, Brown Bread là mặt hàng duy nhất chưa được bán. Hàm Countifs sẽ trả về kết quả như dưới đây:
Ví dụ 2
Trong ví dụ này nhiệm vụ của bạn là tìm số học sinh đạt trên 85 điểm trong cả 2 kỳ. Trong đó số điểm của học sinh trong kỳ 1 được lưu trong cột B và kỳ 2 là trong cột C. Công thức hàm Countifs để đếm số học sinh đạt trên 85 điểm trong cả 2 kỳ có dạng như dưới đây:
Kết quả hàm Countifs trả về có dạng như sau:
Lưu ý khi sử dụng hàm Countifs Excel
– Hàm Countifs có sẵn trên Excel 2000 và các phiên bản mới hơn.
– Hàm Countifs trả về lỗi #VALUE! nếu:
- Các mảng criteria_range đã cho độ dài không bằng nhau.
- Các tham số điều kiện đã cho là các chuỗi văn bản dài hơn 255 ký tự.
– Nếu điều kiện là tham chiếu đến ô trống thì hàm Countifs sẽ coi là 0.
– Chúng ta có thể sử dụng các ký tự đại diện như dấu hoa thị hay dấu hỏi chấm. Trong đó ký tự dấu ? để thay thế cho ký tự nào đó và ký tự dấu * thay thế cho cả một chuỗi ký tự.
Lưu ý quan trọng: Để đếm các ô chứa dấu hỏi hoặc dấu hoa thị, chúng ta sẽ nhập dấu ngã [~] trước dấu hoa thị hoặc dấu hỏi chấm [?].
– Các vùng chọn bổ sung cần phải cùng số hàng, cột với vùng criteria_range1. Các vùng này có thể tách rời.
Trên đây là tất tần tật cú pháp, cách sử dụng hàm Countifs trong Excel. Hy vọng sau bài viết trên đây sẽ giúp bạn làm chủ hàm Excel này. Ngoài ra bạn đọc có thể tham khảo thêm một số bài viết đã có trên Ben Computer để hiểu hơn về các hàm trong Excel nhé.
- Hàm Right trong Excel
- Hàm Index và match
- Hàm tách tên trong Excel
Hàm COUNTIFS trong Excel là 1 hàm thống kê được sử dụng rất nhiều khi làm việc với Excel. Nếu bạn đã đọc qua các bài viết trước của mình về các hàm COUNT thì bạn cũng đã biết, hàm COUNT là hàm dùng để đếm các ô chứa số còn hàm COUNTIF dùng để đếm các ô theo 1 điều kiện. Và trong bài viết này bạn sẽ được học thêm hàm COUNTIFS dùng để đếm theo 2 hay nhiều điều kiện.
Bài viết này mình sẽ hướng dẫn bạn cách dùng hàm COUNTIFS trong Excel để bạn biết rõ hơn về cú pháp, cách dùng hàm COUNTIFS thông qua các ví dụ minh họa. Mình sẽ cố gắng đưa ra nhiều ví dụ với các kiểu dữ liệu khác nhau [số, ngày tháng, text và ô chứa dữ liệu] để bạn dễ dàng nắm bắt được cách sử dụng hàm này. Bây giờ chúng ta đi vào tìm hiểu chi tiết hơn nhé.
Hàm COUNTIFS là hàm cơ bản nên bạn có thể sử dụng nó trong hầu hết các phiên bản Excel như Excel 2016, Excel 2013, Excel 2010, Excel 2007 và cả Excel cho MAC, iPhone, Android. Trong bài viết này mình sẽ sử dụng Excel 2016 để làm ví dụ, nếu bạn đang dùng phiên bản Excel khác thì cũng làm tương tự.
=COUNTIFS[criteria_range1, criteria1, [criteria_range2, criteria2],…]
Chú ý: Tùy theo cài đặt của từng máy tính mà bạn dùng dấu phân cách là dấu phẩy [,] hoặc chấm phẩy [;] nhé.
- criteria_range1: Vùng chọn đầu tiên cần thống kê. Đây là giá trị bắt buộc
- criteria1: Điều kiện áp dụng cho vùng chọn criteria_range1. Giá trị này có thể là một số, tham chiếu ô, biểu thức hoặc văn bản. Đây là giá trị bắt buộc.
- [criteria_range2, criteria2]: Các cặp vùng chọn và điều kiện bổ sung. Hàm COUNTIFS cho phép tối đa 127 cặp vùng chọn và điều kiện. Đây là giá trị tùy chọn.
- Mỗi vùng chọn bổ sung [criteria_range2, criteria_range3,…] phải có cùng số hàng và cột với vùng chọn 1 [criteria_range1] và các vùng chọn không nhất thiết phải liền kề nhau.
- Điều kiện áp dụng của mỗi vùng chọn sẽ được áp dụng cho một ô mỗi lần.
- Nếu điều kiện của vùng chọn tham chiếu đến 1 ô trống thì hàm COUNTIFS coi ô trống là giá trị 0.
- Trong hàm COUNTIFS, bạn có thể dùng các ký tự đại diện như dấu hỏi [?] thay cho bất kỳ ký tự đơn nào và dấu sao [*] thay cho bất kỳ chuỗi ký tự nào trong điều kiện. Khi bạn cần tìm 1 dấu chấm hỏi [?] hay dấu sao [*] thực thì hãy gõ dấu ngã [~] ở trước ký tự đó.
Tham khảo thêm
Để thực hành các ví dụ về hàm COUNTIFS chúng ta sẽ thực hành với bảng số liệu như sau
Để sử dụng công thức hàm COUNTIFS với nhiều điều kiện thì yêu cầu đặt ra là: Thống kê số lượng nhà cung cấp không bán được sản phẩm Samsung A5 nào.
Để giải bài toán này chúng ta sẽ có 2 điều kiện:
Điều kiện 1: Tìm số lượng nhà cung cấp bán sản phẩm Samsung A5
Dựa vào bảng đã cho thì ta sẽ có vùng chọn là C4:C13 và điều kiện là “Samsung A5“
Điều kiện 2: Tìm số lượng nhà cung cấp không bán được sản phẩm nào. Tức là số lượng bán bằng 0
Dựa vào bảng ta có vùng chọn là F4:F13 và điều kiện là “0“
Dựa vào công thức hàm COUNTIFS và những phân tích ở trên ta sẽ có công thức tính như sau:
=COUNTIFS[C4:C13,”Samsung A5″,F4:F13,”0″]
Kết quả thống kê bằng 1. Bạn hoàn toàn có thể đếm bằng mắt thường để kiểm tra lại kết quả.
Trong nhiều trường hợp bạn sẽ phải kết hợp nhiều tiêu chí, khi đó công thức của bạn sẽ rất dài. Để công thức ngắn gọn hơn bạn hãy liệt kê tất cả các tiêu chí trong mảng, sau đó đưa mảng đó vào điều kiện của hàm COUNTIFS. Và để đếm tổng thì chỉ việc kết hợp hàm COUNTIFS với hàm SUM với công thức như sau:
=SUM[COUNTIFS[criteria_range,{“criteria1″,”criteria2″,”criteria3”,…}]]
Áp dụng vào trong bảng tính ở trên, chúng ta sẽ đếm tổng số các sản phẩm không bán được và chưa thống kê được với công thức như sau:
=SUM[COUNTIFS[F4:F13,{“0″,”~?”}]]
Và đây là kết quả
Như đã nói trong phần lý thuyết ở đầu bài, trong bảng tính Excel sẽ có lúc có những ô chứ ký tự đơn hoặc chuỗi ký tự. Và để thống kê các ô như vậy với hàm COUNTIFS bạn sẽ sử dụng các ký tự đại diện để đếm.
- Dấu hỏi chấm [?] – đại diện cho bất kỳ ký tự đơn nào, sử dụng nó để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định.
- Dấu sao [*] – đại diện cho bất kỳ chuỗi ký tự nào, bạn dùng nó để đếm ô chứa một từ cụ thể hoặc một ký tự trong ô.
- Nếu muốn đếm các ô có chứa dấu hỏi chấm [?] hoặc dấu sao [*] thì bạn chỉ việc thêm dấu ngã [~] ở trước dấu hỏi chấm hoặc dấu sao.
Ví dụ chúng ta có bảng với các ký tự, chuỗi ký tự và số như hình dưới. Dựa vào đó chúng ta sẽ thống kê số lượng ô chứa ký tự đơn, ô chứa chuỗi ký tự và tìm dấu hỏi chấm, dấu sao thực.
Trên đây là các ví dụ rất cụ thể về cách dùng hàm COUNTIFS. Hi vọng rằng nó sẽ giúp bạn hiểu được cách sử dụng hàm COUNTIFS như nào để áp dụng vào công việc tính toán.
Trên đây bạn đã được học lý thuyết và xem qua 1 số ví dụ về hàm COUNTIFS. Để có thể sử dụng tốt hàm COUNTIFS bạn cần phải thực hành với bài tập về hàm này. Hãy tải file bài tập đính kèm ở dưới đây về để thực hành nhé.
Download bài tập về hàm COUNTIFS trong Excel
Lời kết
Trên đây là toàn bộ lý thuyết cùng với 1 số ví dụ về hàm COUNTIFS mình đã trình bày khá chi tiết. Hi vọng bạn sẽ hiểu được những gì mình muốn truyền tải và bạn có thể vận dụng vào để sử dụng hàm COUNTIFS 1 cách thành thạo. Nếu gặp khó khăn gì hãy để lại bình luận trong khung bình luận bên dưới để mình giải đáp cho nhé.
Nhớ theo dõi blog Nguyễn Hùng để cập nhật thêm cách sử dụng các hàm Excel khác cùng với những thủ thuật Excel mới. Chúc bạn thành công!