Chris muốn đếm các ô chứa giá trị văn bản A hoặc giá trị văn bản B, ở bất kỳ vị trí nào trong văn bản của ô. Nếu ô chứa cả A và B, cô ấy muốn đếm nó, nhưng chỉ một lần. Ví dụ: Chris có ba ô chứa “hạt táo”, “cây táo” và “hạt đào” và cô ấy muốn biết số ô chứa “quả táo” hoặc “hạt giống”. (Câu trả lời thích hợp nên được trả lại là 3.)

Có nhiều cách để tiếp cận điều này. Khi xem xét các giải pháp, tôi chỉ kiểm tra những giải pháp tránh các câu trả lời trung gian, chiếm các cột bổ sung. Giải pháp đầu tiên liên quan đến việc sử dụng hàm COUNTIF theo cách này:

=COUNTIF(A1:A9,"apple")+COUNTIF(A1:A9,"seed")

-COUNTIF(A1:A9,"seedapple")-COUNTIF(A1:A9,"appleseed")

Công thức đếm tất cả các ô có chứa “apple” hoặc “seed”

và sau đó trừ tất cả các ô có chứa “hạt giống” theo sau là “quả táo”

(cả hai từ đều nằm trong ô) hoặc “apple” theo sau là “seed” (các từ giống nhau theo thứ tự ngược lại).

Một giải pháp khác, giải pháp này ngắn hơn một chút, dựa vào các hàm COUNTA và FIND, như được hiển thị ở đây:

=COUNTA(A1:A9)-SUMPRODUCT(--(ISERROR(FIND("apple",A1:A9)))

* --ISERROR(FIND("seed",A1:A9)))

Công thức đếm các ô chứa giá trị và sau đó trừ tất cả các ô không chứa “quả táo” hoặc “hạt giống”.

Bạn cũng có thể, nếu muốn, sử dụng một trong các hàm cơ sở dữ liệu của Excel.

Miễn là bạn có một tiêu đề cột cho các cụm từ ban đầu của mình, điều này không khó thực hiện và nó dẫn đến công thức ngắn nhất. Tất cả những gì bạn cần làm là thiết lập một bảng tiêu chí tương ứng. Ví dụ: giả sử dữ liệu của bạn ở A1: A9 và ô đầu tiên trong cột chứa tiêu đề chẳng hạn như “Cụm từ của tôi”. Trong một cột khác, bạn nên đặt cùng một tiêu đề và sau đó, trong hai ô ngay dưới nó, đặt hai công thức sau:

apple seed

Tiêu chí chỉ định rằng bạn muốn đối sánh bất kỳ ô nào có chứa “quả táo” hoặc “hạt giống” trong ô. Với thiết lập này (tôi giả sử bạn đã đặt bảng tiêu chí trong D1: D3), bạn có thể sử dụng công thức sau:

=DCOUNTA(A1:A9,1,D1:D3)

Tất nhiên, bạn cũng có thể sử dụng công thức mảng (được nhập bằng cách nhấn Ctrl + Shift + Enter) để có câu trả lời. Sau đây là một trong những công thức như vậy dựa vào các cụm từ đang được kiểm tra để có trong A1: A9:

=SUM(--((ISNUMBER(FIND("apple",A1:A9))+ISNUMBER(FIND("seed",A1:A9)))>0))

Nếu bạn hướng nhiều hơn đến việc làm việc với macro, bạn có thể tạo một hàm do người dùng xác định để trả về số lượng cho bạn. Sau đây là một ví dụ về một cái sẽ hoạt động:

Function FindTwoStrings(rng As Range, s1 As String, _   s2 As String) As Integer     Application.Volatile     If TypeName(rng) <> "Range" Then Exit Function     Dim cell As Range     For Each cell In rng.Cells         If (InStr(1, UCase(cell.Value), UCase(s1), _           vbTextCompare) > 0) Or (InStr(1, UCase(cell.Value), _           UCase(s2), vbTextCompare) > 0) Then _           FindTwoStrings = FindTwoStrings + 1     Next cell End Function

Để sử dụng hàm, bạn có thể sử dụng công thức này trong ô:

=FindTwoStrings(A1:A9,"apple","seed")

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (9326) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: