Nauczyliśmy się już liczyć komórki zawierające określony tekst za pomocą funkcji LICZ.JEŻELI. W tym artykule dowiemy się, jak liczyć, ile razy słowo pojawia się w zakresie Excela. Innymi słowy, policzymy, ile razy słowo wystąpiło w zakresie Excela.
Formuła ogólna
=PRODUKTSUMA(DŁUG(zakres)-DŁ(ZAMIANA(zakres;słowo;")))/DŁ(słowo)
Zasięg: Zakres, w którym próbujesz policzyć określone słowo.
Słowo: Słowo, które chcesz policzyć.
Weźmy przykład i zrozummy, jak to działa.
Przykład: Policz „szczęśliwe” słowo w zakresie Excela.
Tutaj mamy kilka zdań w różnych komórkach. Musimy policzyć wystąpienia słowa „szczęśliwy” w tym zakresie.
Napisz tę formułę w komórce D2.
=PROD.SUMA(DŁ(A2:A4)-DŁ(ZAMIENNIK(A2:A4,D1"")))/DŁ(D1)
Korzystając z tej funkcji, program Excel zlicza, ile razy dane słowo „szczęśliwy” pojawia się w zakresie A2:A4. Ta formuła programu Excel zwraca liczbę jako 4.
Jak to działa?
Chodzi o to, aby uzyskać sumę liczby znaków danego słowa w zakresie, a następnie podzielić przez długość słowa. Na przykład, jeśli Happy występuje 4 razy w zasięgu, jego całkowita długość wynosi 20 (4*5) w zasięgu. Jeśli podzielimy 20 przez 5, otrzymamy 4. Jest to liczba słów w zakresie.
Zburzmy go od środka.
LEN(A2:A4): zwraca liczbę znaków w każdej komórce jako tablicę {49;27;34}.
Następny LEN(SUBSTITUTE(A2:A4,D1"")): Funkcja substytucji zamienia słowo w D1 na „” w każdej komórce z zakresu A2:A4. Następnie funkcja Len zwraca liczbę znaków z tego podstawionego zdania w tablicy {39;22;29}.
LEN(D1): zwraca długość słowa w D1, która wynosi 5 (szczęśliwe).
Teraz formuła jest uproszczona do SUMPRODUCT({49;27;34} - {39;22;29})/5. Po odjęciu tablic SUMPRODUCT ma PRODUKT SUMA({10;5;5})/5. Funkcja dodaje tablicę i otrzymujemy 20/5. Co daje nam nasz wynik 4.
Liczenie bez uwzględniania wielkości liter
Ponieważ SUBSTITUTE rozróżnia wielkość liter, powyższa formuła zignoruje każde słowo, które nie ma tej samej wielkości, tj. „Szczęśliwy”. Aby powyższa formuła ignorowała wielkość liter, musimy zmienić wielkość liter każdego słowa na wielkość liter szukanego słowa. Tak to się robi.
=PROD.SUMA(DŁ(A2:A4)-DŁ(ZAMIENNIK(GÓRNE(A2:A4);GÓRNE(D1);"")))/DŁ(D1)
Teraz sprawa nie ma już znaczenia. Ta funkcja zlicza każde słowo w D1 niezależnie od wielkości liter.
Możliwe błędy:
Liczona będzie część wyrazowa innego wyrazu: w tym przykładzie, gdybyśmy mieli słowo „szczęście” (na przykład wiem, że nie ma takiego słowa), również zostałoby ono policzone. Aby tego uniknąć, możesz otoczyć słowa znakami „ ” &D1& „ ”. Ale kiedy słowo pojawia się jako pierwsze lub ostatnie w zdaniu, to również się nie powiedzie.
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność: Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja WYSZUKAJ.PIONOWO w programie Excel: Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel: Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel: To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.