如何计算整个工作簿中某个字符串出现的次数?

如何计算整个工作簿中某个字符串出现的次数?

我想计算指定字符串在整个工作簿中某一列出现的次数。我试过这个公式,=COUNTIF(sheet1:sheetn!C2:C150; "*string*")但它会#VALUE!报错。

我的公式有什么问题?我该如何得到这个计数?

答案1

以下是等效的虚拟专用网络代码来计算细胞至少包含一个细绳

Sub StringCounter()
    Dim r As Range, s As String, i As Long
    s = "*string*"
    For Each sh In Sheets
        Set r = sh.Range("C2:C150")
        i = i + Application.WorksheetFunction.CountIf(r, s)
    Next sh
    MsgBox i
End Sub

编辑#1:

这是计算的代码琴弦数量 它将计算多个细绳在单个单元格内:

Sub StringCounter_II_The_Sequel()
Dim r As Range, s As String, i As Long
Dim rr As Range, L As Long
s = "string"
L = Len(s)
For Each sh In Sheets
    Set r = sh.Range("C2:C150")
        For Each rr In r
            txt = rr.Text
            If InStr(txt, s) > 0 Then
                i = i + (Len(txt) - Len(Replace(txt, s, ""))) / L
            End If
        Next rr
Next sh
MsgBox i
End Sub

编辑#2:

数学工作原理如下:

  1. 说有三个细绳单元格中字符数为 100
  2. 代替删除所有三个实例,新长度为 82
  3. 减法结果为 18
  4. 除法结果为 3(因为单词细绳长度为 6 个字符)

答案2

您需要为每张表定义每个范围,例如

COUNTIF(Sheet1!C2:C150,"*string*")+COUNTIF(Sheet2!C2:C150,"*string*") ...

如果你想更聪明一点,你可以把每张表放在 A 列,然后在 B 列中使用

=COUNTIF(INDIRECT("Sheet"&A1&"!A1:A5"),"*string*")

或者任何适合您的命名方案的内容。然后将其拖到每个计数下方并求和。

答案3

您可以在 VBA 中编写一个非常简单的用户定义函数来执行此操作。将以下代码粘贴到新模块中(有关如何执行此操作的说明,请参阅这个帖子):

Public Function WBCountString(SearchFor As String, InRange As Range) As Long
Dim wbcs As Long, rng As Range, addr As String
For Each s In Worksheets
    'Limit search area to specified range.
    addr = InRange.Address
    Set rng = s.Range(addr)
    'Keep a running tally of the number of instances of the word in the specified range on each sheet.
    wbcs = wbcs + Application.WorksheetFunction.CountIf(rng, "*" & SearchFor & "*")
Next s
WBCountString = wbcs
End Function

将此代码添加到模块后,即可使用工作表中的函数。例如,要计算所有工作表 C 列中的实例数potato,可以使用以下语法。

=WBCountString("potato",C:C)

相关内容