Formuła ogólna
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
Podsumowanie
Jeśli chcesz policzyć, ile razy określone słowo (lub dowolny podciąg) pojawia się w komórce, możesz użyć formuły używającej PODSTAW i DŁ. W ogólnej postaci powyższego wzoru „tekst” oznacza komórkę zawierającą tekst, a „słowo” oznacza liczone słowo lub podciąg. W przykładzie używamy tej formuły:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Wyjaśnienie
B4 to komórka, w której liczymy słowa, a C4 zawiera podciąg (słowo lub dowolny podciąg), który liczysz.
SUBSTITUTE usuwa podciąg z oryginalnego tekstu, a LEN oblicza długość tekstu bez podłańcucha. Liczba ta jest następnie odejmowana od długości tekstu oryginalnego. Wynikiem jest liczba znaków, które zostały usunięte przez SUBSTITUTE.
Na koniec liczba usuwanych znaków jest dzielona przez długość podciągu. Tak więc, jeśli podciąg ma 5 znaków i brakuje 10 znaków po usunięciu go z oryginalnego tekstu, wiemy, że podciąg pojawił się dwukrotnie w oryginalnym tekście.
Sprawa manipulacyjna
SUBSTITUTE jest funkcją rozróżniającą wielkość liter, więc podczas wykonywania podstawiania dopasowuje wielkość liter. Jeśli chcesz policzyć zarówno duże, jak i małe wystąpienia słowa lub podłańcucha, użyj funkcji UPPER wewnątrz SUBSTITUTE, aby przekonwertować tekst na wielkie litery przed wykonaniem podstawienia:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Ponieważ ta formuła konwertuje podciąg i tekst na wielkie litery przed wykonaniem podstawienia, w każdym przypadku będzie działać równie dobrze z tekstem.
Rozwiązywanie problemów
Liczenie słów w programie Excel jest trudne, ponieważ program Excel nie obsługuje wyrażeń regularnych. W rezultacie trudno jest kierować reklamy na słowa, które chcesz dokładnie policzyć, ignorując podciągi i inne częściowe dopasowania (np. Znajdź „lis”, ale nie „lisy”). Interpunkcja i różnice w wielkości liter sprawiają, że ten problem jest dość trudny.
Jednym ze sposobów obejścia tego problemu jest użycie innej formuły w kolumnie pomocniczej w celu „normalizacji tekstu” jako pierwszego kroku. Następnie użyj wzoru na tej stronie, aby policzyć słowa zawinięte w spacje, aby uzyskać dokładną liczbę (tj. Możesz wyszukać słowo „lis” w znormalizowanym tekście.
Uwaga: to podejście jest tak dobre, jak znormalizowany tekst, który jesteś w stanie utworzyć i może być konieczne wielokrotne dostosowywanie formuły normalizacji, aby uzyskać wymagany wynik.
