Общая формула
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
Резюме
Чтобы подсчитать общее количество слов в ячейке, вы можете использовать формулу, основанную на функциях LEN и SUBSTITUTE с помощью функции TRIM. В показанном примере формула в ячейке C5, скопированная вниз, имеет следующий вид:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1
Формула возвращает количество слов в ячейке B5.
Объяснение
В Excel нет специальной функции для подсчета слов в ячейке. Однако проявив немного изобретательности, вы можете создать такую формулу с помощью функций ПОДСТАВИТЬ и ДЛСТР с помощью функции СРЕЗКА, как показано в примере. На высоком уровне эта формула использует функцию LEN для подсчета количества символов в ячейке с пробелами и без них, а затем использует разницу для подсчета количества слов. Это работает, потому что количество слов равно количеству пробелов + 1, если между каждым словом есть один пробел.
Первая часть формулы считает символы в ячейке B5 после удаления лишнего пробела:
=LEN(TRIM(B5)) // normalize space, count characters
Внутри LEN функция TRIM сначала удаляет лишние пробелы между словами, а также в начале или конце текста. Это важно, так как любые лишние пробелы приведут к потере количества слов. В этом случае лишних пробелов нет, поэтому TRIM возвращает исходный текст непосредственно в функцию LEN, которая возвращает 30:
LEN("All Quiet on the Western Front") // returns 30
На данный момент у нас есть:
=30-LEN(SUBSTITUTE(B5," ",""))+1
Затем мы используем функцию ЗАМЕНА, чтобы удалить все пробелы из текста:
SUBSTITUTE(B5," ","") // strip all space
Обратите внимание, что SUBSTITUTE настроен на поиск символа пробела ("") и замену его пустой строкой (""). По умолчанию SUBSTITUTE заменяет все пробелы. Результат передается непосредственно в функцию LEN, которая возвращает счетчик:
LEN("AllQuietontheWesternFront") // returns 25
LEN возвращает 25 - количество символов, оставшихся после удаления всего пространства. Теперь мы можем упростить формулу до:
=30-25+1 // returns 6
который возвращает 6 в качестве окончательного результата, количество слов в ячейке B5.
Работа с пустыми ячейками
Формула в примере вернет 1, даже если ячейка пуста или содержит только пробел. Это происходит потому, что мы добавляем 1 безоговорочно после подсчета пробелов между словами. Чтобы избежать этой проблемы, вы можете адаптировать формулу, как показано ниже:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)
Обратите внимание, что мы заменили 1 этим выражением:
LEN(TRIM(B5))>0
Этот код сначала обрезает B5, а затем проверяет длину. Если B5 содержит текст, LEN возвращает положительное число, а выражение возвращает TRUE. Если B5 пуст или содержит только пробел, TRIM возвращает пустую строку ("") в LEN. В этом случае LEN возвращает ноль (0), а выражение возвращает FALSE. Хитрость в том, что ИСТИНА и ЛОЖЬ дают оценку 1 и ноль соответственно, когда участвуют в какой-либо математической операции. В результате выражение добавляет 1 только при наличии текста в B5. В противном случае он добавляет ноль (0). Эту логику можно также записать с помощью оператора функции IF следующим образом:
IF(LEN(TRIM(B5))>0,1,0)
и результат будет таким же. Выражение выше просто более компактно.
