Kiểm tra một trong hai giá trị văn bản (Microsoft Excel)
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”. Lưu ý rằng hàm FIND có phân biệt chữ hoa chữ thường, nghĩa là chỉ tìm thấy “apple” chứ không phải “Apple” hoặc “APPLE”. Nếu bạn cần thứ gì đó không phân biệt chữ hoa chữ thường, hãy thay FIND bằng SEARCH.
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 (9325) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: