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

413
SuziLmrdo

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. Существует ли такая вещь? Возвращаясь к сути моего первоначального вопроса, есть ли более простой способ написать формулу для подсчета экземпляров, состоящих только из целых слов, и поместить результат в ячейку?

1
[Это] (https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops#22542835), вероятно, то, что вы ищу cybernetic.nomad 5 лет назад 0
And I always thought there was a line between 2 and 3 that read "A woodchuck would chuck,". Apparently I do not know my mothergoose! Forward Ed 5 лет назад 0

3 ответа на вопрос

2
Scott Craner

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

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

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

enter image description here

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

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

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

enter image description here

Кажется, это вызывает проблему, если слово находится в начале предложения. Appleoddity 5 лет назад 0
@Appleoddity Нет, но есть проблема с подсчетом, если слово появляется дважды. Scott Craner 5 лет назад 0
Привет @ ScottCraner, я думаю, что я только что столкнулся с проблемой, которую вы упомянули выше. Если вы вставите всю строку в одну ячейку, ответ оценивается как «1». SuziLmrdo 5 лет назад 0
@SuziLmrdo см. Редактировать. Scott Craner 5 лет назад 0
@ScottCraner, the first formula is unable to count more that 4 occurrences. Second formula is working but few times it failed to count all occurrences. Just check it. ☺ Rajesh S 5 лет назад 0
,,Cont like try to add `wood` as last word with line 2 & 4, you found count remains 4. Rajesh S 5 лет назад 0
@RajeshS исправлено см редактировать. Scott Craner 5 лет назад 0
@ ScottCraner ,, хорошо сейчас работает ,, отлично 10 perfect Rajesh S 5 лет назад 0
0
Ron Rosenfeld

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

Механизм регулярных выражений 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")
Привет @ Рон, большое спасибо за твой ответ! Я установил ссылку на регулярные выражения VBScript 5.5 и вставил всю строку в одну ячейку (A1). Тем не менее, ответ оценивает, чтобы найти только один экземпляр. Есть что-то, чего мне не хватает? SuziLmrdo 5 лет назад 0
@SuziLmrdo It finds `4` here. Are certain the formula on the worksheet is pointing to the correct location? and has the correct regex included? Ron Rosenfeld 5 лет назад 0
что означает комментарий «конвертировать в позднюю привязку»? Все остальное было сделано - у меня есть набор ссылок, и я проверяю его, удаляя и проверяя, могу ли я измерять их как RegEx, Match, MatchExpression (да, библиотека показывает их все). Я попытался поместить макрос в модуль и рабочую книгу, я вижу и вызываю функцию. И чтобы действительно убедиться в том, что я идиот это защищу, я помещаю строку в A6 и буквально копирую / вставляю вашу формулу. Я не знаю, почему это не работает. SuziLmrdo 5 лет назад 0
The macro should be in a regular module. And, if you have set the reference, ignore the late-binding comment. Can you upload a copy (with sensitive information removed) of your workbook to some sharing site, and post a link? I will download it and see what the issue is. Ron Rosenfeld 5 лет назад 0
вот ссылка [ссылка] https://app.box.com/s/qu5kjx7ax6sl2p1su2jnp71bvlsj1qhn SuziLmrdo 5 лет назад 0
@SuziLmrdo If you look at the function carefully (use the Function Arguments dialog box), you will see there is an `Optional` third argument having to do with case-sensitivity. The default is Case-Sensitive. You did not set that. And, in your example, all was lower case. But in your worksheet, that is not so. So you need to set Case-Sensitive to False. eg.: `=ReCount(A6,"\bwood\b",FALSE)` Ron Rosenfeld 5 лет назад 0
thank you. Indeed, I didn't see that earlier. I am relatively new to VBA, and when I was looking at the code I kept trying to change this area of it: > ' Set Case Insensitivity. > objRegExp.ignoreCase = Not CaseSensitive. I am very much looking forward to learning more about code and appreciate your patience in helping me. SuziLmrdo 5 лет назад 0
@SuziLmrdo Посмотрите на [Как использовать регулярные выражения (Regex) в Microsoft Excel как внутри ячейки, так и в циклах] (https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex- в-Microsoft-Excel-оба-в-клетки-и-петли) Ron Rosenfeld 5 лет назад 0
-1
Rajesh S

enter image description here

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

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

Редакция:

enter image description here

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

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

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

  • При необходимости измените ссылки на ячейки в формуле.
Your formula is not doing what you think it is doing, even though you get the expected result with this data set. Try putting two or more instances of `wood` in any of the lines, and you will see that the total will not change. Ron Rosenfeld 5 лет назад 0
@ Раджеш, большое спасибо за ответ. К сожалению, в моем первоначальном вопросе каждая строка находится в отдельной ячейке, всего четыре строки, потому что я думаю, что в приведенной выше формуле подсчитывается количество ячеек с вхождением слова, а не отдельные экземпляры слова, независимо от того, сколько ячеек они разбросаны по всему. Например, когда я попытался вставить всю строку внутри ячейки E2, значение внутри F2 изменилось на 1. SuziLmrdo 5 лет назад 0
@RonRosenfeld, thanks for raising a good point, the same issue I've got with #Scott's formula. Second one is working sometimes but also getting Failed in most of the Cases and his first formula is exactly behaving like mine. Let me find the correction. ☺ Rajesh S 5 лет назад 0
@RajeshS, your answer and edit kind of miss the point of the question. The issue isn't finding "wood", it's not counting it when it's part of "woodchuck". fixer1234 5 лет назад 0
@fixer1234, I've picked this from OP,, "help me count whole-word-only instances (e.g. "wood" but not "woodchuck")", and I think others also worked on that only,,, since there formula also counting the WOOD, plz suggest if any other idea should have apply on this. ☺ Rajesh S 5 лет назад 0

Похожие вопросы