Jeff có một trang tính với khá nhiều văn bản trong đó, trong cột C. Anh ấy muốn đếm số lần xuất hiện của một chuỗi cụ thể trong cột C, nhưng chỉ cho các hàng lẻ (1, 3, 5, v.v.) trong dữ liệu. Chuỗi mà anh ta đang tìm kiếm có thể không phải là toàn bộ nội dung ô và nó có thể xảy ra nhiều lần trong một ô cụ thể. (Nếu nó xảy ra 2 hoặc 3 lần trong một ô, nó sẽ được tính là 2 hoặc 3 lần xuất hiện.) Anh ấy tự hỏi liệu có cách công thức nào để thực hiện điều này mà không cần dùng đến macro.

Một cách để tiếp cận điều này là sử dụng cột trợ giúp. Ví dụ: giả sử bạn có thể sử dụng cột D làm cột trợ giúp và ô đầu tiên chứa dữ liệu là ô C2. (Có lẽ ô C1 có tiêu đề cột trong đó.) Bạn có thể sử dụng công thức sau trong ô D2:

=IF(MOD(ROW(),2)=1,(LEN(C2)-LEN(SUBSTITUTE(C2,"my text","")))/LEN("my text"),"")

Tất cả những gì bạn cần làm là thay thế chuỗi tìm kiếm (“văn bản của tôi”) bằng bất cứ thứ gì bạn đang tìm kiếm. Hàm LEN được sử dụng hai lần, trước tiên để tìm độ dài của bất kỳ thứ gì có trong ô C2 và sau đó để trừ đi độ dài của văn bản với tất cả các trường hợp của “văn bản của tôi” bị xóa. Giá trị này sau đó được chia cho độ dài của những gì bạn đang tìm kiếm, dẫn đến có bao nhiêu trường hợp của văn bản tìm kiếm trong ô. Lưu ý rằng hàm IF đảm bảo rằng một giá trị số chỉ được trả về nếu hàng đó là một hàng được đánh số lẻ.

Bạn có thể sao chép công thức này xuống bao nhiêu hàng nếu cần, sau đó tính tổng cột. Kết quả là số lần chuỗi xuất hiện trong các hàng được đánh số lẻ.

Nếu bố cục trang tính của bạn không cho phép bạn sử dụng cột trợ giúp, thì bạn có thể dựa vào công thức hoạt động trên mảng dữ liệu.

Đây là một trong những thủ thuật:

=SUMPRODUCT((LEN(C:C)-LEN(SUBSTITUTE(C:C,"my text","")))/LEN("my text")*ISODD(ROW(C:C)))

Công thức này về cơ bản hoạt động tương tự như công thức trước, ngoại trừ hàm SUMPRODUCT thực hiện tính toán nội bộ cho mỗi ô trong cột C. Bạn nên nhận ra rằng vì công thức kiểm tra tất cả cột C, điều đó có nghĩa là nếu văn bản tìm kiếm của bạn (“văn bản của tôi “) xảy ra trong bất kỳ ô không phải dữ liệu nào trong cột (chẳng hạn như tiêu đề cột), sau đó nó cũng sẽ được bao gồm trong tổng số được trả về.

Nếu bạn quyết định sử dụng macro, bạn có thể dễ dàng tạo một hàm do người dùng xác định để kiểm tra phạm vi ô và xác định số lượng. Sau đây là một ví dụ về loại macro bạn có thể sử dụng:

Function CountInst(rSource As Range, sSearch As String, bCaseInsensitive As Boolean) As Integer     Dim c As Range     Dim iCount As Integer     Dim sTemp1 As String     Dim sTemp2 As String

sTemp2 = sSearch     If bCaseInsensitive Then sTemp2 = LCase(sTemp2)

iCount = 0

For Each c In rSource         If c.Row Mod 2 = 1 Then             sTemp1 = c.Text             If bCaseInsensitive Then sTemp1 = LCase(sTemp1)



iCount = iCount + (Len(sTemp1) - _               Len(Replace(sTemp1, sTemp2, ""))) / Len(sTemp2)

End If     Next c

CountInst = iCount End Function

Để sử dụng điều này, tất cả những gì bạn cần làm là cung cấp một phạm vi, những gì bạn muốn tìm kiếm và liệu bạn có muốn đối sánh không phân biệt chữ hoa chữ thường hay không. Ví dụ: nếu bạn muốn tìm kiếm “văn bản của tôi” và trường hợp không thành vấn đề, bạn sẽ sử dụng như sau:

=CountInst(C2:C99,"my text",TRUE)

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

Mẹo này (1514) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.