Hướng dẫn cách đếm theo điều kiện bao gồm phân biệt chữ hoa, chữ thường, điều kiện chính xác hoặc chính xác một phần

Trong bài viết này Gitiho.com sẽ hướng dẫn bạn cách sử dụng các hàm trong Excel để đếm theo điều kiện, phân biệt chữ hoa chữ thường và bao gồm đếm theo điều kiện chính xác và đếm chính xác một phần.

Cách đếm các ô theo điều kiện khớp toàn phần.

Để thực hiện điều này, trong Excel ta sử dụng hàm COUNTIF. Tất cả những gì bạn cần phải làm là cung cấp chuỗi điều kiện cần đếm.

Đây là công thức Excel để đếm các ô có văn bản xác định cụ thể:

COUNTIF (Range, "Text")

Hãy xét ví dụ dưới đây. Giải sử bạn có một danh sách các ID tại vùng A2:A10 và bạn muốn đếm số ô có một ID cụ thể là "AA-01". Đưa điều kiện cần đếm vào công thức ta có kết quả công thức như sau:

= COUNTIF (A2: A10, "AA-01")

Hoặc ta có thể thiết lập điều kiện tìm kiếm tại một ô cụ thể như D1 chẳng hạn, khi đó D1 là điều kiện đếm, ta có công thức như sau:

= COUNTIF (A2: A10, D1)

Ghi chú. Hàm COUNTIF trong Excel không phân biệt chữ hoa chữ thường. Để xử lý các ký tự viết hoa và viết thường khác nhau, hãy sử dụng công thức phân biệt chữ hoa chữ thường này.

Cách đếm các ô có điều kiện khớp một phần

Trong phần trên chúng ta đã thực hiện sử dụng hàm COUNTIF để đếm với điều kiện khớp toàn phần. Trong trường hợp ở điều kiện đếm có ký tự khác nhau trong mỗi ô, ví dụ như một khoảng trống phía trước hoặc phía sau, hoặc một ký tự khác thì khi này điều kiện đếm sẽ không chính xác và ô đó sẽ không được tính.

Để tìm số ô chứa văn bản nhất định như một phần nội dung của chúng, hãy sử dụng các ký tự đại diện trong tiêu chí của bạn, cụ thể là dấu hoa thị (*) đại diện cho bất kỳ chuỗi hoặc ký tự nào. Tùy thuộc vào mục tiêu của bạn, một công thức có thể giống như một trong những công thức sau.

Đếm các ô theo điều kiện với điểm chung là điều kiện đếm ở đầu:

COUNTIF (range, "Text *")

Đếm các ô theo điều kiện với điểm chung là điều kiện đếm ở bất kỳ vị nào:

COUNTIF (range, "* Text*")

Ví dụ: để tìm có bao nhiêu ô trong phạm vi A2: A10 bắt đầu bằng "AA", hãy sử dụng công thức sau:

= COUNTIF (A2: A10, "AA *")

Để có được số lượng ô chứa "AA" ở bất kỳ vị trí nào, hãy sử dụng vị trí sau:

= COUNTIF (A2: A10, "* AA *")

Để làm cho các công thức năng động hơn, hãy thay thế các chuỗi được mã hóa cứng bằng các tham chiếu ô.

Đếm các ô theo điều kiện với điểm chung là điều kiện đếm ở đầu:

= COUNTIF (A2: A10, D1 & "*")

Đếm các ô theo điều kiện với điểm chung là điều kiện đếm ở bất kỳ vị nào:

= COUNTIF (A2: A10, "*" & D1 & "*")

Ảnh chụp màn hình bên dưới cho thấy kết quả:

Đếm các ô theo điều kiện có phân biệt chữ hoa chữ thường

Trong trường hợp bạn cần phân biệt các ký tự viết hoa và viết thường, hàm COUNTIF sẽ không hoạt động. Tùy thuộc vào việc bạn đang tìm kiếm đối sánh chính xác hay từng phần, bạn sẽ phải xây dựng một công thức khác nhau.

Công thức đếm theo điều kiện phân biệt chữ hoa chữ thường với điều kiện đối sánh chính xác

Để đếm số lượng ô có văn bản nhất định nhận dạng chữ hoa, ta sẽ sử dụng kết hợp các hàm SUMPRODUCT và EXACT:

SUMPRODUCT (-- EXACT ("Text", range))

Công thức này hoạt động như thế nào:

·         EXACT so sánh từng ô trong phạm vi với văn bản mẫu và trả về một mảng giá trị TRUE và FALSE, TRUE đại diện cho các kết quả phù hợp chính xác và FALSE cho tất cả các ô khác. Dấu gạch nối đôi (được gọi là dấu đơn phân kép) buộc TRUE và FALSE thành 1 và 0.

·         SUMPRODUCT tính tổng tất cả các phần tử của mảng. Tổng đó là số của 1, là số trận đấu.

Ví dụ: để lấy số ô trong A2: A10 chứa văn bản trong D1 và xử lý chữ hoa và chữ thường dưới dạng các ký tự khác nhau, hãy sử dụng công thức sau:

= SUMPRODUCT (-- EXACT (D1, A2: A10))

Công thức đếm theo điều kiện phân biệt chữ hoa chữ thường với điều kiện khớp một phần

Để xây dựng một công thức phân biệt chữ hoa chữ thường có thể tìm thấy một chuỗi văn bản quan tâm ở bất kỳ đâu trong một ô, ta đang sử dụng 3 hàm khác nhau:

SUMPRODUCT (-- (ISNUMBER (FIND ("Text", Range))))

Công thức này hoạt động như thế nào:

·         Hàm FIND phân biệt chữ hoa chữ thường tìm kiếm văn bản đích trong mỗi ô của phạm vi. Nếu thành công, hàm sẽ trả về vị trí của ký tự đầu tiên, nếu không thì là lỗi #VALUE!error. Để rõ ràng, chúng ta không cần biết vị trí chính xác, bất kỳ số nào (ngược lại với lỗi) có nghĩa là ô chứa văn bản đích.

·         Hàm ISNUMBER xử lý mảng số và lỗi do FIND trả về và chuyển các số thành TRUE và bất kỳ thứ gì khác thành FALSE. Một đơn vị kép (--) ép buộc các giá trị logic thành giá trị đơn vị và số không.

·         SUMPRODUCT tính tổng mảng 1 và 0 và trả về số lượng ô có chứa văn bản được chỉ định như một phần nội dung của chúng.

Để kiểm tra công thức trên dữ liệu đời thực, hãy tìm xem có bao nhiêu ô trong A2: A10 chứa đầu vào chuỗi con trong D1:

= SUMPRODUCT (-- (ISNUMBER (FIND (D1, A2: A10))))

Và điều này trả về số lượng 3 (ô A2, A3 và A6):

Cách đếm các ô đã lọc với văn bản cụ thể

Để đếm các mục hiển thị trong danh sách đã lọc, bạn sẽ cần sử dụng kết hợp 4 hoặc nhiều hàm tùy thuộc vào việc bạn muốn đối sánh chính xác hay từng phần. Để làm cho các ví dụ dễ theo dõi hơn, trước tiên chúng ta hãy xem nhanh dữ liệu nguồn.

Giả sử, bạn có một bảng với ID đơn hàng ở cột B và Số lượng ở cột C như thể hiện trong hình dưới đây. Hiện tại, bạn chỉ quan tâm đến số lượng lớn hơn 1 và bạn đã lọc bảng của mình cho phù hợp. Câu hỏi đặt ra là - làm cách nào để bạn đếm các ô đã lọc với một id cụ thể?

Công thức đếm các ô đã lọc với văn bản cụ thể (đối sánh chính xác)

Để đếm các ô đã lọc có nội dung khớp chính xác với chuỗi văn bản mẫu, hãy sử dụng một trong các công thức sau:

= SUMPRODUCT (SUBTOTAL (103, INDIRECT ("A" & ROW (A2: A10))), - (B2: B10 = F1))

= SUMPRODUCT (SUBTOTAL (103, OFFSET (A2: A10, ROW (A2: A10) - MIN (ROW (A2: A10)),, 1)), - (B2: B10 = F1))

Trong đó F1 là văn bản mẫu và B2: B10 là các ô cần đếm.

Cách các công thức này hoạt động:

Ở cốt lõi của cả hai công thức, bạn thực hiện 2 kiểm tra:

1.    Xác định hàng hiển thị và hàng ẩn. Đối với điều này, bạn sử dụng hàm SUBTOTAL với đối số function_num được đặt thành 103. Để cung cấp tất cả các tham chiếu ô riêng lẻ cho SUBTOTAL, hãy sử dụng INDIRECT (trong công thức đầu tiên) hoặc kết hợp OFFSET, ROW và MIN (trong công thức thứ hai) . Vì chúng tôi nhắm đến việc xác định các hàng hiển thị và ẩn, nên việc tham chiếu cột nào không thực sự quan trọng (A trong ví dụ của chúng tôi). Kết quả của phép toán này là một mảng gồm các số 1 và số 0 trong đó các số đó đại diện cho các hàng hiển thị và số không - các hàng ẩn.

2.    Tìm các ô chứa văn bản đã cho. Đối với điều này, hãy so sánh văn bản mẫu (F1) với phạm vi ô (B2: B10). Kết quả của phép toán này là một mảng các giá trị TRUE và FALSE, được ép buộc thành 1 và 0 với sự trợ giúp của toán tử đơn phân kép.

Cuối cùng, hàm SUMPRODUCT nhân các phần tử của hai mảng ở cùng vị trí, rồi tính tổng mảng kết quả. Bởi vì nhân với 0 sẽ cho không, chỉ những ô có 1 trong cả hai mảng mới có 1 trong mảng cuối cùng. Tổng của 1 là số ô được lọc có chứa văn bản được chỉ định.

Công thức đếm các ô đã lọc với văn bản cụ thể (đối sánh một phần)

Để đếm các ô đã lọc có chứa văn bản nhất định như một phần của nội dung ô, hãy sửa đổi các công thức trên theo cách sau. Thay vì so sánh văn bản mẫu với phạm vi ô, hãy tìm kiếm văn bản đích bằng cách sử dụng ISNUMBER và FIND như được giải thích trong một trong các ví dụ trước:

= SUMPRODUCT (SUBTOTAL (103, INDIRECT ("A" & ROW (A2: A10))), -- (ISNUMBER (FIND (F1, B2: B10))))

= SUMPRODUCT (SUBTOTAL (103, OFFSET (A2: A10, ROW (A2: A10) - MIN (ROW (A2: A10)),, 1)), -- (ISNUMBER (FIND (F1, B2: B10))) )

Kết quả là, các công thức sẽ định vị một chuỗi văn bản nhất định ở bất kỳ vị trí nào trong ô:

Ghi chú. Hàm SUBTOTAL với 103 trong đối số function_num, xác định tất cả các ô ẩn, được lọc ra và ẩn theo cách thủ công. Do đó, các công thức trên chỉ tính các ô có thể nhìn thấy bất kể các ô không nhìn thấy được ẩn như thế nào. Để chỉ loại trừ các ô đã lọc ra nhưng bao gồm các ô được ẩn theo cách thủ công, hãy sử dụng 3 cho function_num.

Đó là cách đếm số ô có văn bản nhất định trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!