![计算非连续工作表中的文本字符串](https://linux22.com/image/1480295/%E8%AE%A1%E7%AE%97%E9%9D%9E%E8%BF%9E%E7%BB%AD%E5%B7%A5%E4%BD%9C%E8%A1%A8%E4%B8%AD%E7%9A%84%E6%96%87%E6%9C%AC%E5%AD%97%E7%AC%A6%E4%B8%B2.png)
在 Excel 2003 中,我有一个关于计算文本字符串的问题男人在名为 001R 至 900R 的工作表中,所有工作表的同一单元格 C8 中
我得到了以下答案
=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT(001&":"&900))&"R"&"'!C8"),"*Man*"))
当所有工作表都存在时,此方法非常有效。但是,当使用上述公式时,如果并非所有工作表都存在,则会出现错误,因为我的工作表名称为
001R
002R
003S
004R
101R
102S
103R
210R
211R
305R
306S
307R
401R
402R
403S
404R
以此类推,大约有 300 张纸,缺失的纸号将在稍后数据可用时添加。
有没有办法使用涵盖所有数字的公式来包含现在和可能的未来的工作表名称,以避免每次添加新工作表时编辑公式?
@Gary's Student 的回答很有帮助。但我想计算包含许多项目的下拉列表中的文本,并计算每个项目的出现次数。使用您的技术,我应该为每个项目创建一个 UDF 模块吗?或者有办法做到这一点吗?
另外,我有一些表格的编号是 S 而不是 R。有没有办法计算这些表格的编号?我的意思是有没有办法让
shName = Format(i, "000") & "R"
和
If InStr(1, cel, "Man") as variables entered in =SpecialSum(C8).
答案1
以下用户定义函数将检查您的工作表块。如果工作表存在,则检查感兴趣的单元格以查看它是否包含字符串男人。如果字符串存在,则计数增加:
Public Function SpecialSum(rin As Range) As Long
Dim addy As String, i As Long, shName As String
Dim cel As String
Application.Volatile
addy = rin.Address
For i = 1 To 900
shName = Format(i, "000") & "R"
On Error Resume Next
cel = Sheets(shName).Range(addy).Text
If Err.Number = 0 Then
If InStr(1, cel, "Man") > 0 Then SpecialSum = SpecialSum + 1
Else
Err.Number = 0
End If
On Error GoTo 0
Next i
End Function
用户定义函数 (UDF) 非常容易安装和使用:
- ALT-F11 打开 VBE 窗口
- ALT-I ALT-M 打开新模块
- 粘贴内容并关闭 VBE 窗口
如果您保存工作簿,UDF 将随之保存。如果您使用的是 2003 以后的 Excel 版本,则必须将文件另存为.xlsm而不是.xlsx
要删除 UDF:
- 调出如上所示的 VBE 窗口
- 清除代码
- 关闭 VBE 窗口
要从 Excel 使用 UDF:
=SpecialSum(C8)
在哪里C8是感兴趣的单元格。要了解有关宏的更多信息,请参阅:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
和
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
有关 UDF 的详细信息,请参阅:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
必须启用宏才能使其工作!
这将允许您添加/删除工作表而无需编辑公式。