☰

如何使用 Microsoft Excel 计算土拨鼠吃了多少木头？

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 regex grep
如何使用 Microsoft Excel 计算土拨鼠吃了多少木头？

总结Excel 中是否有类似的东西grep可以帮助我计算全字实例（例如“wood”而不是“woodchuck”）（以及它们的变体）？

鹅妈妈有一首诗：

How much wood could a woodchuck chuck 
If a woodchuck could chuck wood? 
As much wood as a woodchuck could chuck, 
If a woodchuck could chuck wood.

这是一个开始，但仍然没有确切的数字。

所以我找到了Exceljet.net 上非常有用的帖子建议使用 SUBSTITUTE、LEN 和 SUMPRODUCT 的组合。

如果您想象上述文本包含在单元格 A1：A4 中，我的第一个公式如下所示：

=SUMPRODUCT((LEN(A1:A4)-LEN(SUBSTITUTE((UPPER(A1:A4)),UPPER("木材"),"")))/LEN("木材"))

注意：我的公式中包含了“上”，因为她发给我的诗看起来更像这样

hOw MucH WOod cOuld a WOodCHucK CHUCk 
IF a wOoDchUCk couLD cHuck wood? 
aS muCH wOOD as a WOOdCHuCk coUlD ChuCK, 
iF a woODChUck coulD CHuCK WoOD.

但不管怎样，这个结果都是8，这是错误的。

我手动数了一下，只有 4 块木头和 4 只土拨鼠。我不想数土拨鼠，只想数它们会扔的木头（如果它们确实能扔木头的话）。

所以现在我的公式如下所示：

=SUMPRODUCT((LEN(A1:A4)-LEN(SUBSTITUTE((UPPER(A1:A4)),UPPER("木材"),"")))/LEN("木材"))-(SUMPRODUCT((LEN(A1:A4)-LEN(SUBSTITUTE((UPPER(A1:A4)),UPPER("土拨鼠"),"")))/LEN("土拨鼠")))

从技术上讲，这确实有效！但事实是，我并不是真的想计算鹅妈妈诗中“木头”这个词出现的次数，我需要计算许多不同单词的实例数，包括可能被转置、拼写错误或其中有随机空格或字符的单词。我基本上需要 Excel 中的 grep 之类的东西。有这样的东西吗？回到我最初问题的范围，有没有更简单的方法来编写一个公式来计算全词实例并将结果放在单元格中？

答案1

工作表功能困难且复杂，因为在处理空格和各种标点符号时很难确定单词边界。

VBA 正则表达式引擎有一个简单的标记\b，可以检测单词边界，单词边界定义为word字符与non-word字符或行的开始或结束相交的位置。单词字符是位于[A-Za-z0-9_]

VBA 函数：

Option Explicit
'Set Reference to Microsoft VBScript Regular Expressions 5.5
'   or convert to Late Binding

Function ReCount(str As String, Pattern As String, _
    Optional CaseSensitive As Boolean = True) As Long

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches   As MatchCollection

    ' Create a regular expression object.
   Set objRegExp = New RegExp

   'Set the pattern by using the Pattern property.
   objRegExp.Pattern = Pattern

   ' Set Case Insensitivity.
   objRegExp.ignoreCase = Not CaseSensitive

   'Set global applicability.
   objRegExp.Global = True

   'Test whether the String can be compared.
    If (objRegExp.Test(str) = True) Then

   'Get the matches.
        Set colMatches = objRegExp.Execute(str)   ' Execute search.
        ReCount = colMatches.Count
    Else
        ReCount = 0
    End If
End Function

用法：

=ReCount(A6,"\bwood\b")

答案2

这适用于此实例，您可能需要为其他标点符号添加其他替代品。

=SUMPRODUCT(--ISNUMBER(SEARCH(" "& "wood" & " "," " & SUBSTITUTE(SUBSTITUTE(A1:A4,".",""),"?","") & " ")))

现在，它会查找由空格包围的完整单词，这就是我们需要删除标点符号的原因。由于 SEARCH 不区分大小写，因此我们不需要 UPPER。

在此处输入图片描述

无论找到该单词的次数有多少，上述操作每个单元格仅计算 1 个。

这将计算所有：

=SUMPRODUCT((LEN(" " & SUBSTITUTE(SUBSTITUTE(UPPER(A1:A4),".", " "),"?"," ") & " ")-LEN(SUBSTITUTE(" " & SUBSTITUTE(SUBSTITUTE(UPPER(A1:A4),"."," "),"?"," ") & " ",UPPER(" wood "),"")))/LEN(" wood "))

在此处输入图片描述

答案3

在此处输入图片描述

这个简单的公式将计算木头在数据范围内。

=COUNTIF(E53:E56,"*wood*")

編輯：

在此处输入图片描述

发生木头也可以使用辅助列进行计数。

  • 在帮助栏中写入木头所有可能的格式。
  • 写下此公式并填写。
  • 最后对计数求和。

=SUMPRODUCT(LEN($E$74:$E$77)-LEN(SUBSTITUTE($E$74:$E$77,F74,"")))/LEN(F74)

  • 根据需要调整公式中的单元格引用。

相关内容