đếm các ô có văn bản cụ thể (khớp chính xác và một phần)

Tuần trước chúng tôi đã xem xét Cách đếm ô với văn bản trong Excel, có nghĩa là tất cả các ô với bất kỳ văn bản. Khi phân tích khối thông tin lớn, bạn cũng có thể muốn biết có bao nhiêu ô chứa văn bản cụ thể. Hướng dẫn này giải thích làm thế nào để làm điều đó một cách đơn giản.

Cách đếm ô với văn bản cụ thể trong Excel

Microsoft Excel có một chức năng đặc biệt để đếm các ô có điều kiện, Hàm COUNTIF. Tất cả bạn phải làm là cung cấp chuỗi văn bản đích trong tiêu chí tranh luận.

Đây là một công thức Excel chung để đếm số lượng ô chứa văn bản cụ thể:

COUNTIF (phạm vi, “bản văn“)

Ví dụ sau đây cho thấy nó trong hành động. Giả sử, bạn có một danh sách ID vật phẩm trong A2: A10 và bạn muốn đếm số lượng ô với một id cụ thể, nói “AA-01”. Nhập chuỗi này vào đối số thứ hai và bạn sẽ nhận được công thức đơn giản này:

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

Để cho phép người dùng của bạn đếm các ô với bất kỳ văn bản đã cho nào mà không cần sửa đổi công thức, hãy nhập văn bản vào một ô được xác định trước, nói D1 và cung cấp tham chiếu ô:

=COUNTIF(A2:A10, D1)

Cách đếm ô với văn bản nhất định (khớp một phần)

Công thức được thảo luận trong ví dụ trước khớp chính xác với tiêu chí. Nếu có ít nhất một ký tự khác nhau trong một ô, ví dụ cuối cùng sẽ có thêm một khoảng trắng, đó sẽ không phải là một kết hợp chính xác và một ô như vậy sẽ không được tính.

Để tìm số lượng ô 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ự. Tùy thuộc vào mục tiêu của bạn, một công thức có thể trông giống như một trong những điều sau đây. Đếm các ô có chứa văn bản cụ thể tạirất bắt đầu

:COUNTIF (phạm vi, “bản văn

* “) Đếm các ô có chứa văn bản nhất định trongbất kỳ vị trí nào

:COUNTIF (phạm vi, “*bản văn

* “)

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

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 này:

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

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

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

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

Để đếm các ô bắt đầu bằng văn bản nhất định:

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

Để đếm các ô có văn bản nhất định ở bất cứ đâu trong đó:

Công thức đếm các ô chứa một chuỗi văn bản đã cho

Đếm các ô có chứa văn bản cụ thể (phân biệt chữ hoa chữ thường)

Trong trường hợp khi bạn cần phân biệt các ký tự chữ hoa và chữ 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 một kết hợp chính xác hay một phần, bạn sẽ phải xây dựng một công thức khác nhau.

Công thức phân biệt chữ hoa chữ thường để đếm các ô có văn bản cụ thể (khớp chính xác)

Để đếm số lượng ô có văn bản nhất định nhận dạng trường hợp văn bản, chúng tôi sẽ sử dụng kết hợp các hàm SUMPRODVEL và EXACT:TÓM TẮT (- CHÍNH XÁC (“bản văn “,phạm vi

))

• Cách thức hoạt động của công thức này: Chính xác so sánh từng ô trong phạm vi so với văn bản mẫu và trả về một mảng các giá trị TRUE và FALSE, TRUE biểu thị các kết quả khớp chính xác và FALSE cho tất cả các ô khác. Dấu gạch nối kép (được gọi làđôi đơn
• ) ép TRUE và FALSE thành 1 và 0. TÓM TẮT

tổng hợp tất cả các yếu tố của mảng. Tổng đó là số 1, là số lượng trận đấu.

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

Công thức phân biệt chữ hoa chữ thường để lấy số lượng ô có văn bản cụ thể

Công thức phân biệt chữ hoa chữ thường để đếm các ô có văn bản cụ thể (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 cứ đâu trong một ô, chúng tôi đang sử dụng 3 hàm khác nhau:TÓM TẮT (- (ISNUMBER (TÌM (“bản văn “,phạm vi

))))

• Cách thức hoạt động của công thức này: Các trường hợp nhạy cảm Hàm TÌM
• 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à #VALUE! lỗi. Để rõ ràng, chúng ta không cần biết vị trí chính xác, bất kỳ số nào (trái ngược với lỗi) có nghĩa là ô chứa văn bản đích.
• Hàm ISNUMBER xử lý mảng các số và lỗi được FIND trả về và chuyển đổi các số thành TRUE và bất cứ thứ gì khác thành FALSE. Một unary kép (-) ép các giá trị logic thành các giá trị và số không.

TÓM TẮT tổng hợp mảng 1 và 0 và trả về số lượng ô chứa văn bản đã chỉ định làm một phần nội dung của chúng.

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

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

Công thức phân biệt chữ hoa chữ thường để đếm các ô chứa văn bản nhất định ở bất cứ đâu trong đó

Cách đếm các ô được lọc với văn bản cụ thể Để đếm mục có thể nhìn thấy

trong danh sách được lọc, bạn sẽ cần sử dụng kết hợp 4 chức năng trở lên tùy thuộc vào việc bạn muốn khớp chính xác hay một phần. Để làm cho các ví dụ dễ theo dõi hơn, trước tiên hãy xem xét dữ liệu nguồn. Giả sử, bạn có một bảng với ID đơn hàng trong cột B và Định lượng

Cách đếm các ô được lọc với văn bản nhất định

Công thức đếm các ô được lọc với văn bản cụ thể (khớp chính xác)

=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))

Để đếm chính xác cá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:

Công thức đếm các ô được lọc với văn bản cụ thể

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

1. Tại cốt lõi của cả hai công thức, bạn thực hiện 2 kiểm tra: Xác định các hàng nhìn thấy và ẩn. Đối với điều này, bạn sử dụng ĐĂNG KÝ chức năng với hàm_num đối số đượ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 một trong hai GIÁN TIẾP (trong công thức đầu tiên) hoặc kết hợpBÙ LẠI
2. , ROW và MIN (trong công thức thứ hai). Vì chúng tôi nhắm đến việc xác định vị trí các hàng có thể nhìn thấy và ẩn, nên việc tham chiếu cột nào không quan trọng (A trong ví dụ của chúng tôi). Kết quả của hoạt động này là một mảng 1 và 0 trong đó các mảng đại diện cho các hàng và số không nhìn thấy – các hàng ẩn.

Tìm các ô chứa văn bản đã cho. Đối với điều này, so sánh văn bản mẫu (F1) với phạm vi ô (B2: B10). Kết quả của hoạt động 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 nguyên kép.

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

Công thức đếm các ô được lọc với văn bản cụ thể (khớp một phần)

=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))))

Để đếm cá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à TÌM như được giải thích trong một trong các ví dụ trước:

Công thức đếm các ô được lọc với một chuỗi văn bản nhất định ở bất kỳ vị trí nào Ghi chú. Hàm SUBTOTAL với 103 trong hàm_num đối số, xác định tất cả các ô ẩn, được lọc và ẩn thủ công. Kết quả là, các công thức trên chỉ được tính tế bào nhìn thấy bất kể các tế bào vô hình đã bị ẩn như thế nào. Để loại trừ chỉ các ô được lọc nhưng bao gồm các ô được ẩn thủ công, hãy sử dụng 3 chohàm_num

Đó là cách đếm số lượng ô 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 dịch vụ kế toán tại Hà Nội vào tuần tới!