Как я могу подсчитать, сколько дерева сурка забрасывает с помощью Microsoft Excel?

Question

TL; DR Есть ли что-то вроде grep в Excel, которое может помочь мне подсчитать экземпляры, содержащие только целые слова (например, "дерево", но не "сурок") (и, возможно, их варианты)?

Так вот, это стихотворение, мать гусыня:

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, моя первая формула выглядела так:

= СУММПРОИЗВ ((LEN (А1: А4)-LEN (ПОДСТАВИТЬ ((ВЕРХНИЙ (А1: А4)), ВЕРХНИЙ ("дерево"), "")))/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 сурка. Я не хочу считать сурков, просто дерево, которое они будут бросать (если бы они могли бросить дерево).

Теперь моя текущая формула выглядит так:

= СУММПРОИЗВ ((LEN (А1: А4)-LEN (ПОДСТАВИТЬ ((ВЕРХНИЙ (А1: А4)), ВЕРХНИЙ ("дерево"), "")))/LEN ("дерево"))- (SUMPRODUCT ((LEN (А1: А4)-LEN (ПОДСТАВИТЬ ((ВЕРХНИЙ (А1: А4)), ВЕРХНИЙ ("сурка"), "")))/LEN ("сурка")))

И это технически работает! но правда в том, что я на самом деле не пытаюсь подсчитать количество вхождений слова "дерево" в стихотворении "Мать гусиная", мне нужно подсчитать количество экземпляров множества разных слов, в том числе тех, которые могут быть транспонированы, написаны с ошибками или иметь случайные пробелы или символы в них. Мне в принципе нужно что-то вроде grep внутри Excel. Существует ли такая вещь? Возвращаясь к сути моего первоначального вопроса, есть ли более простой способ написать формулу для подсчета экземпляров, состоящих только из целых слов, и поместить результат в ячейку?

Answer 1

Это работает для этого экземпляра, вам может понадобиться добавить другие ЗАМЕНЫ для других знаков препинания.

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

Теперь мы ищем полное слово, окруженное пробелами, поэтому нам нужно убрать пунктуацию. Поскольку поиск не учитывает регистр, нам не нужен верхний регистр.

---

Выше будет учитываться только 1 на ячейку, независимо от того, сколько раз слово найдено.

Это будет считать все:

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

Answer 2

Сложно и сложно с функциями листа, из-за сложности определения границ слов при работе с пробелами, а также с различными пунктуациями.

Движок регулярных выражений 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")

Answer 3

Эта простая формула будет считать Вуд в диапазоне данных.

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

Редакция:

Появление Вуда может быть подсчитано также с помощью вспомогательной колонны.

• В Helper Column напишите Wood во всех возможных форматах.
• Напишите эту формулу и заполните.
• Наконец, сумма графа.

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

• При необходимости измените ссылки на ячейки в формуле.