Formuła ogólna
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
Podsumowanie
Aby policzyć wszystkie słowa w komórce, możesz użyć formuły opartej na funkcjach DŁ i PODSTAW, z pomocą funkcji UTNIJ. W przedstawionym przykładzie formuła w komórce C5 skopiowana w dół to:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1
Formuła zwraca liczbę słów w komórce B5.
Wyjaśnienie
Excel nie ma dedykowanej funkcji do liczenia słów w komórce. Jednak przy odrobinie pomysłowości można utworzyć taką formułę za pomocą funkcji SUBSTITUTE i LEN z pomocą TRIM, jak pokazano na przykładzie. Na wysokim poziomie ta formuła używa funkcji DŁ do zliczenia liczby znaków w komórce, ze spacjami i bez, a następnie wykorzystuje tę różnicę do obliczenia liczby słów. To działa, ponieważ liczba słów jest równa liczbie spacji + 1, o ile między każdym słowem jest jedna spacja.
Pierwsza część formuły liczy znaki w komórce B5 po usunięciu dodatkowej spacji:
=LEN(TRIM(B5)) // normalize space, count characters
Wewnątrz LEN funkcja TRIM najpierw usuwa wszelkie dodatkowe spacje między wyrazami lub na początku lub na końcu tekstu. Jest to ważne, ponieważ wszelkie dodatkowe spacje zmniejszają liczbę słów. W tym przypadku nie ma dodatkowych znaków spacji, więc TRIM zwraca oryginalny tekst bezpośrednio do funkcji DŁ, która zwraca 30:
LEN("All Quiet on the Western Front") // returns 30
W tym momencie mamy:
=30-LEN(SUBSTITUTE(B5," ",""))+1
Następnie używamy funkcji SUBSTITUTE, aby usunąć wszystkie znaki spacji z tekstu:
SUBSTITUTE(B5," ","") // strip all space
Uwaga SUBSTITUTE jest skonfigurowany do wyszukiwania znaku spacji („”) i zastępowania go pustym ciągiem („”). Domyślnie SUBSTITUTE zastępuje wszystkie spacje. Wynik jest dostarczany bezpośrednio do funkcji LEN, która zwraca liczbę:
LEN("AllQuietontheWesternFront") // returns 25
LEN zwraca 25, czyli liczbę znaków pozostałych po usunięciu całej spacji. Możemy teraz uprościć formułę, aby:
=30-25+1 // returns 6
co jako wynik końcowy zwraca 6, liczbę słów w komórce B5.
Radzenie sobie z pustymi komórkami
Formuła w przykładzie zwróci 1, nawet jeśli komórka jest pusta lub zawiera tylko spację. Dzieje się tak, ponieważ bezwarunkowo dodajemy 1, po odliczeniu spacji między słowami. Aby ustrzec się przed tym problemem, możesz dostosować formułę, jak pokazano poniżej:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)
Zauważ, że zastąpiliśmy 1 tym wyrażeniem:
LEN(TRIM(B5))>0
Ten kod najpierw przycina B5, a następnie sprawdza długość. Jeśli B5 zawiera tekst, LEN zwraca liczbę dodatnią, a wyrażenie zwraca TRUE. Jeśli B5 jest pusty lub zawiera tylko spację, TRIM zwraca pusty ciąg („”) do LEN. W takim przypadku LEN zwraca zero (0), a wyrażenie zwraca FALSE. Sztuczka polega na tym, że PRAWDA i FAŁSZ obliczają odpowiednio 1 i zero, gdy są zaangażowane w jakąkolwiek operację matematyczną. W rezultacie wyrażenie dodaje 1 tylko wtedy, gdy w B5 znajduje się tekst. W przeciwnym razie dodaje zero (0). Tę logikę można również zapisać za pomocą instrukcji funkcji IF w następujący sposób:
IF(LEN(TRIM(B5))>0,1,0)
a wynik byłby taki sam. Powyższe wyrażenie jest po prostu bardziej zwarte.
