Как посчитать слова в excel?

Есть одна опция, которую я всегда хотел бы иметь в Excel, — это подсчет количества слов в ячейке. Если вы работаете в MS Word, в строке состояния есть встроенная опция, показывающая, сколько слов находится на листе.

в word есть возможность подсчитывать слова, но нет в excel

Но когда дело касается Excel, такой возможности подсчета слов нет. Вы можете подсчитать количество ячеек, содержащих текст , но не содержащих слов.

Как вы знаете, в Excel есть функции , и с их помощью можно рассчитать практически что угодно. Вы можете создать формулу, которая будет подсчитывать слова в ячейке.

Сегодня в этой статье вы узнаете, как подсчитывать слова в Excel в одной ячейке, диапазоне ячеек или даже на всем листе. А еще я покажу вам, как посчитать определенное слово из ряда ячеек.

1. Подсчитайте слова в одной ячейке

Чтобы подсчитать слова в ячейке, необходимо объединить функцию ДЛСТР с функцией ПОДСТАВИТЬ . И формула будет такой (текст находится в ячейке A1): 

 =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
формула для подсчета слов в Excel с использованием замены len

Когда вы ссылаетесь на ячейку с помощью этой формулы, в результате она возвращает 7. И да, всего у вас в ячейке 7 слов.

Как это работает

Прежде чем мы перейдем к этой формуле, просто подумайте вот о чем. В обычном предложении, если у вас восемь слов, в этих словах обязательно будет 7 пробелов. ВЕРНО?

Это означает, что у вас всегда будет на одно слово больше, чем пробелов. Идея проста: если вы хотите посчитать слова, посчитайте пробелы и добавьте один. Чтобы понять эту формулу, нужно разделить ее на три части.

как работает формула для подсчета слов в Excel с использованием замены len

В первой части вы использовали функцию ДЛСТР для подсчета количества символов в ячейке A1. А во второй и третьей частях вы объединили ПОДСТАВКУ с ДЛСТР, чтобы удалить пробелы из ячейки , а затем посчитать символы. На данный момент у вас есть такое уравнение.

формула уравнения для подсчета слов в Excel

Общее количество символов с пробелами и общее количество символов без пробелов. И когда вы вычитаете два числа, вы получаете количество пробелов, и в конце вам нужно добавить один. В результате возвращается 7, что соответствует общему количеству слов в ячейке.

Важно : когда вы используете приведенную выше формулу, она возвращает 1, даже если ячейка пуста, поэтому лучше обернуть ее функцией ЕСЛИ, чтобы избежать этой проблемы. 

 =IF(ISBLANK(A2),0,LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)

подсчитывать слова в Excel, использовать, если ячейка пуста

Эта формула сначала проверит ячейку и вернет количество слов только в том случае, если в ячейке есть значение.

2. Используйте UDF

Помимо приведенных выше формул, я написал небольшой код для создания UDF . Этот код поможет вам создать специальную функцию, которая будет просто возвращать количество слов. Короче говоря, вам не нужно объединять функции.

 Function MyWordCount(rng As Range) As Integer MyWordCount = UBound(Split(rng.Value, " "), 1) + 1 End Function

Позвольте мне рассказать вам, как его использовать.

  1. Сначала введите этот код в редактор VBA.
    add udf code to vbe to count words in excel cell
  2. Затем вернитесь в свою электронную таблицу, введите «=MyWordCount(» и укажите ячейку, в которой у вас есть значение.

И он вернет количество слов.

udf для подсчета слов в Excel с использованием замены len

Связанный: Панель формул в Excel

3. Подсчитайте слова в диапазоне ячеек

Теперь перейдем к следующему уровню. И здесь вам придется считать слова из диапазона ячеек, а не из одной ячейки. Хорошей новостью является то, что вам просто нужно использовать ту же формулу (с простым изменением), которую вы использовали выше. И формула будет такая: 

 =SUMPRODUCT(LEN(A1:A11)-LEN(SUBSTITUTE(A1:A11," ",""))+1)

формула для подсчета слов в Excel из диапазона строк столбца

В приведенной выше формуле A1:A11 — это диапазон ячеек, и когда вы вводите формулу, в результате возвращается 77.

Как это работает

Эта формула работает так же, как и первый метод, но является лишь небольшим шагом вперед. Единственное отличие состоит в том, что вы обернули его в СУММПРОИЗВ и ссылаетесь на весь диапазон, а не только на одну ячейку.

как сумма произведений работает для подсчета слов в Excel

Знаете ли вы, что СУММПРОИЗВ может принимать таблицы? Поэтому, когда вы его используете, он возвращает массив, в котором для каждой ячейки указано количество слов. И в конце он складывает эти числа и сообщает вам количество слов в столбце.

4. Количество слов на всем листе

Этот код — один из полезных макрокодов, которые я использую в своей работе, и он может помочь вам посчитать все слова на листе.

 Sub Word_Count_Worksheet() Dim WordCnt As Long Dim rng As Range Dim S As String Dim N As Long For Each rng In ActiveSheet.UsedRange.Cells S = Application.WorksheetFunction.Trim(rng.Text) N = 0 If S <> vbNullString Then N = Len(S) – Len(Replace(S, " ", "")) + 1 End If WordCnt = WordCnt + N Next rng MsgBox "There are total " & Format(WordCnt, "#,##0") & " words in the active worksheet" End Sub

Когда вы запустите его, он отобразит окно сообщения с количеством слов, которые есть на активном листе.

Код vba для подсчета слов в Excel с помощью vba

Связанный: Что такое VBA в Excel

5. Подсчитайте определенное слово/текстовую строку из диапазона.

Здесь у вас другая ситуация. Предположим, вам нужно посчитать определенное слово в диапазоне ячеек или проверить, сколько раз значение появляется в столбце.

Давайте возьмем этот пример: ниже у вас есть диапазон из четырех ячеек, и из этого диапазона вам нужно подсчитать количество вхождений слова «Понедельник» .

Для этого используется формула: 

 =SUMPRODUCT((LEN(D6:D9)-LEN(SUBSTITUTE(D6:D9,"Monday","")))/LEN("Monday"))
подсчитывать слова в Excel с помощью формулы суммы произведения

И когда вы его вводите, обратный отсчет возобновляется до слова «Понедельник». Это 4.

Важно: он возвращает количество слов (частоту слов) из диапазона , а не количество ячеек, содержащих это слово. Понедельник там четыре раза в трёх камерах.

Как это работает

Чтобы понять эту функцию, опять же, нужно разделить ее на четыре части. В первой части функция ДЛСТР возвращает массив количества символов в ячейках.

используйте формулу суммы произведения для подсчета определенного слова из диапазона 1

Вторая часть возвращает массив количества символов ячеек, удаляя слово «Понедельник».

используйте формулу суммы произведения для подсчета определенного слова из диапазона 2

В третьей части функция LEN возвращает длину ключевого слова «Понедельник» в символах.

используйте формулу суммы произведения для подсчета определенного слова из диапазона 3

После этого вычитаем первую часть из второй части, затем делим на третью часть, получается массив с количеством слов «Понедельник» в каждой ячейке.

используйте формулу суммы произведения для подсчета определенного слова из диапазона 4

В четвертой части СУММПРОИЗВ возвращает сумму этого массива и количество «понедельников» в диапазоне.

Получить файл Excel

Скачать

Заключение

Всякий раз, когда вы вводите текст в ячейку или диапазон ячеек, вы можете использовать эти методы для управления количеством слов. Я бы хотел, чтобы однажды в будущем Excel получил эту возможность для подсчета слов. Но на данный момент у вас есть все эти замечательные методы.

Какой метод вам нравится больше всего? Обязательно поделитесь со мной своими мыслями в комментариях, мне будет интересно услышать ваше мнение. И, пожалуйста , не забудьте поделиться этим постом со своими друзьями, я уверен, им он понравится.

Список формул Excel

Связанные формулы