Хотите узнать количество слов в Excel? Вы не поверите, но в Excel нет встроенного счетчика слов.
Но не волнуйтесь.
Крутой набор функций Excel (или немного VBA, если вам нравится) может легко сделать это за вас.
В этом уроке я покажу несколько способов подсчета слов в Excel с помощью простых формул. И в конце также будет рассмотрен метод создания настраиваемой формулы с использованием VBA, которая быстро даст вам количество слов любого текста в любой ячейке.
Формула для подсчета слов в Excel
Прежде чем я дам вам точную формулу, давайте быстро рассмотрим логику подсчета слов.
Предположим, у меня есть предложение, показанное ниже, для которого я хочу подсчитать количество слов.
Хотя Excel не может подсчитать количество слов, он может подсчитать количество пробелов в предложении.
Итак, чтобы получить количество слов, мы можем посчитать эти пробелы вместо слов и добавить 1 к общей сумме (поскольку количество пробелов будет на единицу меньше количества слов).
Теперь может быть две возможности:
- Между каждым словом есть один пробел
- Между словами есть несколько пробелов.
Итак, давайте посмотрим, как подсчитать общее количество слов в каждом случае.
Пример 1 — Когда между словами есть один пробел
Допустим, у меня в ячейке A1 есть следующий текст: Выпустите кошку из мешка.
Чтобы подсчитать количество слов, я бы использовал формулу:
= LEN (A1) -LEN (ПОДСТАВИТЬ (A1; ""; "")) + 1
В результате будет возвращено «7».
Вот как работает эта формула:
- LEN (A1) — Эта часть формулы возвращает 26, то есть общее количество символов в тексте в ячейке A1. Он включает в себя текстовые символы, а также символы пробела.
- ПОДСТАВИТЬ (A1, »«, »») — эта часть формулы удаляет все пробелы из текста. Таким образом, результат в этом случае будет Все прояснить.
- LEN (ПОДСТАВИТЬ (A1; «»,“”) — Эта часть формулы подсчитывает общее количество символов в тексте без пробелов. Таким образом, результат будет 20.
- LEN (A1) -LEN (ЗАМЕНА (A1, ”“,“”)) — Это вычтет длину текста без пробелов из длины текста с пробелами. В приведенном выше примере это будет 26-20, что равно 6.
- = LEN (A1) -LEN (ПОДСТАВИТЬ (A1; ”“,“”))+1 — Мы добавляем 1 к общему результату, так как общее количество пробелов на единицу меньше общего количества слов. Например, в двух словах есть один пробел, а в трех словах — два пробела.
Теперь это хорошо работает, если у вас есть только один пробел между словами. Но это не сработает, если между словами будет больше одного пробела.
В этом случае используйте формулу в следующем примере.
Пример 2: когда между словами несколько пробелов
Допустим, у вас есть такой текст: Выпустите кошку из мешка.
В этом случае между словами есть несколько пробелов.
Чтобы получить количество слов, нам сначала нужно удалить все лишние пробелы (так, чтобы между двумя словами был только один пробел), а затем подсчитать общее количество пробелов.
Вот формула, которая даст нам нужное количество слов:
= LEN (ОБРЕЗАТЬ (A1)) - LEN (ПОДСТАВИТЬ (A1; ""; "")) + 1
Это аналогичная формула, использованная в приведенном выше примере, с небольшим изменением — здесь мы также использовали функцию TRIM.
Функция Excel TRIM удаляет все начальные, конечные и лишние пробелы (кроме одиночных пробелов между словами).
Остальная часть формулы работает так же (как объяснено в Примере 1).
Примечание. Если между словами нет пробелов, это считается одним словом.
Использование настраиваемой функции VBA для подсчета слов в Excel
Хотя приведенные выше формулы отлично работают, если вам нужно часто рассчитывать количество слов, вы можете использовать VBA для создания настраиваемой функции (также называемой функцией, определяемой пользователем).
Преимущество использования настраиваемой функции заключается в том, что вы можете создать ее один раз, а затем использовать как любую другую обычную функцию Excel. Поэтому вместо создания длинной сложной формулы, как мы делали в двух приведенных выше примерах, у вас есть простая формула, которая берет ссылку на ячейку и мгновенно дает вам количество слов.
Вот код, который создаст эту настраиваемую функцию для получения количества слов в Excel.
Функция WordCount (CellRef As Range) Dim TextStrng As String Dim Result () As String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End Function
После создания вы можете использовать функцию WordCount так же, как любую другую обычную функцию Excel.
В приведенном выше коде для пользовательской функции я использовал функцию TRIM рабочего листа, чтобы удалить любые начальные, конечные и двойные пробелы между словами. Это гарантирует, что все три ячейки дают одинаковый результат, поскольку учитываются только слова, а не двойные пробелы.
Как работает эта формула:
В приведенном выше коде VBA сначала используется функция TRIM, чтобы удалить все начальные, конечные и двойные пробелы из текстовой строки в указанной ячейке.
После очистки строки он использует функцию SPLIT в VBA для разделения текстовой строки на основе разделителя, который мы указали как пробел. Таким образом, каждое слово разделяется и сохраняется как отдельный элемент в переменной Result.
Затем мы используем функцию UBOUND, чтобы подсчитать общее количество элементов, которые были сохранены в переменных Result. Поскольку база VBA равна 0, нам нужно добавить 1, чтобы получить общее количество слов.
Это означает, что Результат (0) сохраняет первое слово, Результат (1) сохраняет второе слово и так далее. Поскольку этот подсчет начинается с 0, нам нужно добавить 1, чтобы получить реальное количество слов.
Куда положить этот код?
При создании настраиваемой функции вам необходимо поместить код в редактор VB книги (который является задней частью книги, где вы можете писать код для автоматизации задач и создания настраиваемых функций).
Ниже приведены инструкции по размещению кода функции GetNumeric в книге.
- Перейдите на вкладку Разработчик.
- Выберите вариант Visual Basic. Это откроет редактор VB в бэкэнде.
- На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
- Перейдите во вкладку «Вставить» и нажмите «Модуль». Это вставит объект модуля для вашей книги.
- Скопируйте и вставьте код в окно модуля.
После того, как вы скопировали код в окно кода, вы можете вернуться к своему рабочему листу и использовать эту функцию, как любую другую обычную функцию Excel.
Просто введите = Слово и он покажет вам формулу в списке.
Он принимает один аргумент, который является ссылкой на ячейку, и мгновенно дает вам количество слов в ней.
