☰

计算非连续工作表中的文本字符串

tag-icon • tag-icon microsoft-excel
计算非连续工作表中的文本字符串

在 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) 非常容易安装和使用：

  1. ALT-F11 打开 VBE 窗口
  2. ALT-I ALT-M 打开新模块
  3. 粘贴内容并关闭 VBE 窗口

如果您保存工作簿，UDF 将随之保存。如果您使用的是 2003 以后的 Excel 版本，则必须将文件另存为.xlsm而不是.xlsx

要删除 UDF：

  1. 调出如上所示的 VBE 窗口
  2. 清除代码
  3. 关闭 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

必须启用宏才能使其工作！

这将允许您添加/删除工作表而无需编辑公式。

相关内容