Formuła ogólna
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Podsumowanie
Aby policzyć, ile razy określone słowo (lub dowolny podciąg) pojawia się w zakresie komórek, możesz użyć formuły opartej na funkcjach SUBSTITUTE, LEN i SUMPRODUCT. W pokazanym przykładzie formuła w C11 to:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Uwaga: Formuła na tej stronie uwzględnia zawartość słowa w zakresie. Na przykład, jeśli komórka zawiera dwa wystąpienia słowa, wniesie 2 do całkowitej liczby. Jeśli chcesz tylko policzyć komórki zawierające określone słowo, zobacz tę prostą formułę opartą na funkcji LICZ.JEŻELI.
Wyjaśnienie
W ogólnej wersji formuły rng reprezentuje zakres do sprawdzenia, a txt to słowo lub podciąg do zliczenia.
W pokazanym przykładzie B5: B8 to zakres do sprawdzenia, a C2 zawiera tekst (słowo lub podciąg) do zliczenia.
Dla każdej komórki w zakresie 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.
Następnie liczba usuwanych znaków jest dzielona przez długość podciągu. Tak więc, jeśli podciąg lub słowo ma 5 znaków, a po usunięciu z oryginalnego tekstu brakuje 10 znaków, wiemy, że podciąg / słowo pojawiło się dwukrotnie w oryginalnym tekście.
Ponieważ powyższe obliczenia są opakowane w funkcję SUMPRODUCT, wynikiem jest tablica zawierająca liczbę dla każdej komórki w zakresie. Liczby te reprezentują liczbę wystąpień podciągu w każdej komórce. W tym przykładzie tablica wygląda następująco: (1; 1; 0; 1)
Na koniec funkcja SUMPRODUCT sumuje wszystkie elementy w tablicy, aby uzyskać łączną liczbę wystąpień podłańcucha w zakresie komórek.
Ignorowanie wielkości liter
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:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
