我想计算指定字符串在整个工作簿中某一列出现的次数。我试过这个公式,=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:
这数学工作原理如下:
- 说有三个细绳单元格中字符数为 100
- 这代替删除所有三个实例,新长度为 82
- 减法结果为 18
- 除法结果为 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)