Hàm đếm có điều kiện countif
Excel cung ứng một số hàm ship hàng việc đếm những loại ô không giống nhau như đếm ô trống, ô ko trống, ô đựng giá trị số hoặc hầu hết ô cất kí tự ví dụ nào đó. Trong bài viết này, Học Excel Online cùng các bạn sẽ tập trung vào hàm COUNTIF cơ bản, được thực hiện để đếm ô với đk tự tùy chỉnh thiết lập thông qua những ví dụ dưới đây. Bạn đang xem: Hàm đếm có điều kiện countif
Cấu trúc hàm COUNTIF cơ bản
Hàm COUNTIF có cấu tạo như sau:
=COUNTIF(range, criteria)
Trong đó:
range: Vùng chứa những ô hy vọng đếm
criteria: điều kiện bạn muốn thiết lập bên dưới dạng một số, kí từ bỏ hoặc một lô ghích sẽ chỉ định phần đa giá trị nào sẽ được đếm
Ứng dụng hàm Countif trong số chuỗi ô
Để các bạn hiểu hơn về phong thái sử dụng Countif mình đã đi vào những ví dụ từ dễ dàng và đơn giản đến phức tạp hơn sau đây
Hàm COUNTIF mang lại văn bạn dạng và số (chính xác tuyệt đối)
Trên thực tế, họ đã bàn bạc về hàm COUNTIF đếm những giá trị chữ khớp với cùng một điều kiện đúng chuẩn tuyệt đối vừa rồi. Hãy nhằm tôi kể lại bí quyết của ô có chứa một chuỗi văn bản: = COUNTIF (C2: C15, “Roger Federer”). Do vậy, các bạn nhập:
Vùng chọn đếm là thông số đầu tiên;Dấu phẩy như vết phân cách;Một từ hoặc một vài từ trong lốt ngoặc kép làm cho điều kiện.Thay vì nhập lại từ, bạn cũng có thể sử dụng tham chiếu đến bất kỳ ô nào chứa từ hoặc những từ đó và nhận được công dụng tương tự, ví dụ: = COUNTIF (C1: C9, C7).
Hàm COUNTIF dùng tựa như cho số. Như các bạn thấy trong hình dưới đây, công thức = COUNTIF (D2: D9,5) đếm các ô có giá trị 5 sinh sống Cột D.
Hàm COUNTIF với ký kết tự đại diện thay mặt (khớp một phần)
Trong trường hợp tài liệu Excel của bạn bao gồm một vài vươn lên là thể của (các) keyword mà bạn muốn tính, thì chúng ta có thể sử dụng ký tự đại diện thay mặt để đếm toàn bộ các ô đựng một từ, cụm từ hoặc vần âm nhất định như một phần của nội dung ô.
Giả sử chúng ta có một danh sách những nhiệm vụ được giao cho tất cả những người khác nhau và bạn muốn biết số trách nhiệm được giao mang đến Danny Brown. Chính vì tên của Danny được viết bằng rất nhiều cách thức khác nhau, ta nhập “* Brown *” là đk = COUNTIF (D2: D10, “*Brown *”).
Dấu hoa thị (*) được sử dụng sửa chữa các giá trị đầu cùng cuối, như được minh họa trong lấy một ví dụ trên. Nếu bạn phải khớp từng ký tự đơn, hãy nhập một dấu hỏi (?), như được trình bày dưới đây.
Đếm những ô bước đầu hoặc dứt bằng những ký tự tuyệt nhất định
Bạn rất có thể sử dụng ký tự đại diện, vết sao (*) hoặc có thể dấu chấm hỏi (?), cùng với tùy thuộc vào công dụng mà chúng ta muốn.
Nếu bạn muốn biết số ô ban đầu hoặc hoàn thành bằng văn bản nhất định bất cứ có từng nào ký tự khác trong ô, hãy sử dụng các công thức sau:
= COUNTIF (C2: C10, “Mr*”) – tính các ô bước đầu bằng “Mr”.
= COUNTIF (C2: C10, “* ed”) – tính các ô xong xuôi bằng chữ “ed”.
Hình hình ảnh dưới trên đây minh họa cho phương pháp thứ hai:
Nếu nhiều người đang tìm kiếm một vài ô bước đầu hoặc dứt bằng các ký tự khăng khăng và chứa một số ký tự tốt nhất định, bạn sử dụng hàm COUNTIF với lốt hỏi (?) tại điều kiện:
= COUNTIF (D2: D9, “??own”) – đếm số ô xong bằng chữ “own” với có chính xác 5 ký tự trong số ô trường đoản cú D2 cho D9, bao gồm cả khoảng chừng cách.
= COUNTIF (D2: D9, “Mr ??????”) – đếm số ô bước đầu bằng chữ “Mr” cùng có đúng mực 8 ký tự trong những ô từ bỏ D2 mang đến D9, bao gồm khoảng cách.
Mẹo. Để tìm kiếm số ô cất dấu hỏi “?” hoặc giấu hoa thị “*”, hãy gõ dấu bửa (~) trước vết ? hoặc * vào công thức. Ví dụ, = COUNTIF (D2: D9, “* ~? *”) nhằm đếm toàn bộ các ô gồm chứa vệt chấm hỏi vào dải D2: D9.
Ứng dụng hàm COUNTIF cơ phiên bản với vần âm và số trùng khớp
Hãy xem thêm ví dụ bên dưới đây:
Tại đây, bọn họ sẽ thực hiện hàm COUNTIF để đếm rất nhiều ô chứa giá trị vừa lòng trùng khớp cùng với điều kiện. Cú pháp tương xứng trong ví dụ:
=COUNTIF($C$2:$C$31,$I$8) hoặc =COUNTIF($C$2:$C$31,”Nam”) – đếm con số ô chứa giá trị “Nam” trong vùng C2 đến C31
=COUNTIF($D$2:$D$31,I11) hoặc =COUNTIF($D$2:$D$31,18) – đếm con số ô cất giá trị “18” trong vùng C2 mang đến C31.
Xem thêm: Tổng Hợp Tranh Tĩnh Vật Chì Lọ Hoa Và Quả, Cách Vẽ Tranh Tĩnh Vật Lọ Hoa Và Quả
Lưu ý: Criteria không riêng biệt chữ hoa với chữ thường. Vì chưng vậy, Nam hay NAM, nam giới thì tương tự như nhau thôi. Trong vô số nhiều trường hợp, nó cực kỳ tiện vày bạn chưa hẳn xử lí đúng chuẩn dữ liệu, nhưng trong vô số trường hợp, nó lại tác động rất những tới việc tìm kiếm. Vậy xử lí rứa nào? có rất nhiều cách. Ở đây, mình sẽ nhắc nhở một hướng đi, đó là thay bởi đếm “Nam”, “NAM”, “nam”, hãy đếm TRUE cùng FALSE phối kết hợp cùng hàm EXACT. Ví dụ ra sao, chúng ta hãy thử suy nghĩ và từ thực hành giải thuật nhé.
Không hề khó khăn nhớ buộc phải không? tiếng ta sẽ đến với các “biến thể” ở rất nhiều ví dụ tiếp theo.
Sử dụng COUNTIF kết hợp những kí tự thay mặt đại diện (wildcard)
Kí tự thay mặt đại diện (Wildcard) được thực hiện để sửa chữa các kí tự khác trong ô. Gồm 3 kí tự thay mặt là vết sao (*), vệt hỏi (?), dấu vấp ngã (~).
*Chú ý: Wildcard hoàn toàn có thể thay thế phần lớn giá trị trong chuỗi kí tự, mà lại không được dùng để làm thay thế so với định dạng số (Number). Nếu chạm chán trường hợp như vậy, các bạn hãy chuyển cục bộ về dạng TEXT bằng hàm TEXT, tiếp nối kiểm tra lại bởi hàm ISTEXT và sau cuối COUNTIF kết hợp wildcard như bình thường.
Hãy thuộc xem ví dụ:
Trong ví dụ như này, ta vẫn đếm mang đến 2 trường hợp: những người dân có họ Lê; những người có tên Anh.
Kết vừa lòng hàm COUNTIF cơ bản, ta hoàn toàn có thể đếm được số lượng ô thỏa mãn điều kiện.
Bây giờ, hãy cho với ví dụ thiết bị 3
Sử dụng hàm COUNTIF đếm với những phép đối chiếu lớn nhỏ tuổi bằng
Đối với việc đếm sử dụng đk là các phép so sánh to hơn (>), nhỏ tuổi hơn (=a” với >= là phép so sánh, a là quý hiếm số được so sánh.
Với điều kiện tham chiếu ta đặt phép đối chiếu trong dấu nháy kép, phối hợp cùng kí từ bỏ &: “>=”& I8 với >= là phép so sánh, I8 là ô được tham chiếu tới.
Qua 3 lấy một ví dụ trên, kiên cố hẳn chúng ta đã ban đầu hiểu rõ rộng về hàm COUNTIF rồi buộc phải không? giờ đồng hồ hãy mày mò ứng dụng của hàm này một chút ít với ví dụ ở đầu cuối nhé.
Ứng dụng hàm COUNTIF lọc giá trị trùng lặp
Ví dụ trên là một trong những ứng dụng dễ dàng của hàm COUNTIF vào các chuyển động như lọc email khách hàng, thanh lọc tên… bị trùng lặp. Có lẽ tới đây, chúng ta đều đã nhận ra giải pháp làm rồi đề xuất không? Hãy tính số lần xuất hiện của giá trị ý muốn tìm trong cột tổng và chuyển việc về lọc những giá trị trả về theo số từ nhiên.
Sử dụng COUNTIF kết hợp AND hoặc OR
Với cú pháp của hà Countif
=COUNTIF(range, criteria)
Rõ ràng là criteria ở đó là hàm AND, và đk ta đang liệt kê ra khá đầy đủ rồi, đề nghị không?
Ta hãy xem qua hàm AND một chút:
Vấn đề tại đây là:
Hàm AND (và cả OR) sẽ trả về cực hiếm là TRUE hoặc FALSE.Logic đối chiếu trong hàm AND. Cái gì sẽ lớn hơn, hoặc nhỏ dại hơn?Khi đưa AND hoặc OR vào COUNTIF, đối số range của các bạn sẽ được so với criteria là TRUE hoặc FALSE (hoặc #VALUE!). Rõ ràng hiệu quả sẽ trả về 0 rồi.
Vậy ta tự khắc phục gắng nào?
Cách 1: sử dụng hàm COUNTIFS nắm thế
Hàm COUNTIFS chất nhận được đếm với tương đối nhiều điều kiện. Tại sao phải rứa nhồi nhét vào COUNTIF trong khi hoàn toàn có thể dùng hàm COUNTIFS.
Cách 2: Lập cột phụ
Hướng làm nhưng Học Excel Online gợi ý như sau: Lập một cột phụ dùng hàm and để trả về giá bán trị True hoặc False, tiếp đến sử dụng COUNTIF với cột phụ.
Cách 3: Sử dụng xúc tích và ngắn gọn toán học
Logic ở chỗ này là: Để tra cứu số đông đảo số vừa lòng trong khoảng A và B, đem số gần như số vừa lòng khoảng A trừ đi số phần lớn số thỏa mãn khoảng B (khoảng B nằm trong tầm A).Video gợi ý Cách thực hiện hàm countif vào excel
Lời kết
Đừng quên trên trên đây chỉ là đều ví dụ cơ bản nhất của COUNTIF. Còn không hề ít ứng dụng đã chờ các bạn khám phá, bởi vậy nên hãy luyện tập tiếp tục nhé!