Подсчет количества слов в ячейке excel

Количество слов в ячейке в Excel

Рассмотрим простой способ подсчета количества слов в ячейке в Excel.

Стандартной формулы подсчета слов в ячейке в Excel нет, поэтому итоговая формула будет состоять из нескольких формул.
Итоговая формула выглядит следующим образом:

=ЕСЛИ(ЕПУСТО( A2 );0;ДЛСТР(СЖПРОБЕЛЫ( A2 ))-ДЛСТР(ПОДСТАВИТЬ( A2 ;» «;»»))+1)

Краткое описание используемых функций (с подробным описанием можно ознакомиться в конце статьи):

• СЖПРОБЕЛЫ — удаляет из текста лишние пробелы;
• ПОДСТАВИТЬ — производит замену старого текста на новый в текстовой строке;
• ДЛСТР — рассчитывает количество знаков в строке;
• ЕПУСТО — проверяет является ли ячейка пустой.

Алгоритм подсчета

Алгоритм строится на том факте, что количество слов в любой фразе всегда на 1 больше, чем количество пробелов.
Однако фраза может начинаться с пробела или по ошибке могут встречать двойные пробелы, поэтому в первую очередь удалим лишние проблемы из исходной фразы с помощью функции СЖПРОБЕЛЫ и подсчитаем длину фразы функцией ДЛСТР:

Теперь рассчитаем длину фразы без учета пробелов, для этого воспользуемся функций ПОДСТАВИТЬ, которая удалит символ пробела (если быть точнее, то заменит » » на «»):

Рассчитываем разницу между двумя данными значениями, прибавляем 1 и получаем итоговое количество слов в ячейке:

=ДЛСТР(СЖПРОБЕЛЫ( A2 ))-ДЛСТР(ПОДСТАВИТЬ( A2 ;» «;»»))+1

Однако в случае, если ячейка пустая, то данная формула вернет значение 1, поэтому добавим в функцию проверку на непустую ячейку (если ячейка пустая, то вернется значение 0):

=ЕСЛИ(ЕПУСТО( A2 );0;ДЛСТР(СЖПРОБЕЛЫ( A2 ))-ДЛСТР(ПОДСТАВИТЬ( A2 ;» «;»»))+1)

Описание используемых функций

Функция СЖПРОБЕЛЫ:

СЖПРОБЕЛЫ(текст)
Удаляет из текста лишние пробелы (кроме одиночных пробелов между словами).

• Текст(обязательный аргумент) — текст, из которого удаляются лишние пробелы.

Функция ПОДСТАВИТЬ:

ПОДСТАВИТЬ(текст; стар_текст; нов_текст; [номер_вхождения])
Заменяет новым текстом старый текст в текстовой строке.

• Текст(обязательный аргумент) — текст, в котором происходит подстановка;
• Старый текст(обязательный аргумент) — заменяемый текст;
• Новый текст(обязательный аргумент) — текст, на который меняется старый текст;
• Номер вхождения(необязательный аргумент) — номер вхождения заменяемого фрагмента старый текст.

Функция ДЛСТР:

ДЛСТР(текст)
Возвращает количество знаков в текстовой строке.

• Текст(обязательный аргумент) — измеряемый текст.

Функция ЕПУСТО:

ЕПУСТО(значение)
Проверяет, ссылается ли данная ссылка на пустую ячейку, и возвращает значение ИСТИНА или ЛОЖЬ.

• Значение(обязательный аргумент) — ссылка на ячейку.

Как посчитать количество слов в Excel

Пользователи Microsoft Word знают, на сколько полезна возможность узнать количество слов в набранном тексте. Однако, пользуясь Excel, узнать количество слов в документе не возможно штатными средствами.

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

Как посчитать количество слов в ячейке Excel

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

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;” “;””))+1

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

Как эта формула работает?

Прежде чем мы погрузимся в то, как работает формула, предлагаю поразмышлять.

Если мы составим обычное предложение из 8 слов, то их будут разделять 7 пробелов.

Это означает, что в любом предложении слов на один больше чем пробелов. То есть, для того, чтобы посчитать количество слов в предложении, нам нужно рассчитать количество пробелов и прибавить к этому числу один.

Соответственно, наша формула работает следующим образом:

1. Функция ДЛСТР в первой части формулы подсчитывает количество символов в ячейке (с учетом пробелов)
2. Во второй и третьей части формулы мы комбинируем функции ДЛСТР и ПОДСТАВИТЬ для подсчета количества символов в ячейке без пробелов
3. Прибавляем к полученному значению число “один”

Если вы используете эту формулу для пустой ячейки, то результат вычисления будет равен единице.

Во избежание этого, я предлагаю использовать в дополнение две функции: ЕСЛИ и ЕПУСТО . Формула будет выглядеть так:

=ЕСЛИ(ЕПУСТО(A1);0;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;” “;””))+1)

Эти две функции проверяют, есть ли текст в ячейке или она пустая. Если в ячейке нет текста, формула вернет значение “ноль”.

Как посчитать количество слов в нескольких ячейках Excel

Теперь, перейдем на более сложный уровень.

Представим, что наша задача посчитать количество слов в нескольких ячейках.

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

=СУММПРОИЗВ(ДЛСТР(A1:A10)-ДЛСТР(ПОДСТАВИТЬ(A1:A10;” “;””))+1)

В указанной выше формуле А1:А10 это диапазон ячеек в рамках которого мы хотим посчитать количество слов.

Как эта формула работает?

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

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

Я надеюсь, что в будущем Excel получит штатную возможность для подсчета слов.

Уверен, эти приемы помогут вам стать лучше в Excel.

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

Есть одна штука, которую я бы хотел иметь в Excel: подсчет слов в ячейке.

Если вы работаете в MS Word, в строке состояния есть встроенная опция, которая показывает, сколько слов на листе.

В Word есть опция для подсчета слов, но не в Excel. Вы можете посчитать количество ячеек, в которых есть текст, но не фактические слова в них.

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

Четыре разных способа посчитать слова в Excel

Сегодня в этой статье вы научитесь считать слова в Excel в ячейке или диапазоне ячеек или даже во всей таблице.

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

1. Формула для подсчета слов в ячейке

Сочетание функций ДЛСТР с ПОДСТАВИТЬ

Формула такая(текст в ячейке A1):

= ДЛСТР(A1) — ДЛСТР (ПОДСТАВИТЬ (A1; » «; «»)) + 1

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

И да, у вас есть 5 слов в ячейке.

Как формула работает?

Прежде чем перейти к этой формуле, просто подумайте. В обычном предложении, если у вас восемь слов, у вас определенно будет 7 пробелов в этих словах. Правильно? Это означает, что у вас всегда будет на одно слово больше, чем пробелов.

Идея проста: если вы хотите посчитать слова, подсчитайте пробелы и добавьте единицу.

Теперь, чтобы понять эту формулу, вам нужно разделить ее на три части.

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

Наше уравнение выглядит так:

= 20 — 16 +1

• 20 — общее количество символов с пробелами
• 16 — символы без пробелов

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

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

= ЕСЛИ(ЕПУСТО (A1);0; ДЛСТР(A1) — ДЛСТР(ПОДСТАВИТЬ(A1; » «; «»)) + 1)

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

Пользовательская функция

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

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

Давайте я расскажу вам, как ее использовать.

• Прежде всего, введите этот код в редакторе VBA.

• Затем вернитесь на свой рабочий лист и введите «= MyWordCount(» и сошлитесь на ячейку, в которой у вас есть значение.

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

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

Теперь давайте перейдем на следующий уровень. Здесь вам нужно будет посчитать слова уже в диапазоне ячеек вместо одной ячейки.

Хорошая новость! Можно использовать ту же формулу (добавив небольшое изменение), которую мы использовали выше.

Вот эта формула:

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

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

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

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

3. Подсчет количества слов во всей таблицы с кодом VBA

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

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 «Всего » & Format(WordCnt, «#,##0″) & » слов на активном листе» End Sub

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

4. Подсчет определенного слова/текстовой строки в диапазоне

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

Разберем на примере.

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

Вот формула для этого:

= СУММПРОИЗВ ((ДЛСТР (A1:A4) — ДЛСТР (ПОДСТАВИТЬ (A1:A4; «понедельник»; «»)) / ДЛСТР(«понедельник»))

И когда вы введете ее, она возвратит количество понедельников. Ответ — 4.

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

Понедельник встречается четыре раза в трех ячейках.

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

Чтобы понять эту функцию, вам снова нужно разделить ее на четыре части.

В первой части функция ДЛСТР (LEN) возвращает массив количества символов в ячейках.

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

В третьей части функция ДЛСТР (LEN) возвращает длину символов слова «понедельник».

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

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

Заключение

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

Я мечтаю, что когда-нибудь в будущем в Excel появится эта опция. Ну а пока будем пользоваться этими замечательными методами.

Я надеюсь, что статья была полезной для вас. Какой метод вам понравился больше всех?

Не забудьте поделиться своими мнениями со мной в разделе комментариев, для меня это важно. И, пожалуйста, не забудьте поделиться со своими друзьями, я уверен, что они это оценят.

Как посчитать количество символов в ячейке excel

Видео: как … подсчитать количество символов в строке Excel

Рассмотрим, как посчитать количество символов в тексте Excel, количество слов, букв, цифр, конкретных букв, т.д. В Excel нет специальной кнопки подсчета количества символов в ячейке, как в Word.

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

Сначала уточним, что символы в Excel — это и буквы, и цифры, и запятая, и точка, и др. знак, и пустое место между словами, числами, кавычки, т.д. По-простому – сколько места занимает текст.

Первый вариант. Как посчитать количество символов в ячейке Excel.

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

Находится функция «ДЛСТР» на закладке «Формулы» в разделе «Библиотека функций». Нажимаем на кнопку «Текстовые». В появившемся списке выбираем функцию «ДЛСТР».

В ячейке В49 устанавливаем функцию «ДЛСТР». Диалоговое окно функции заполнили так.В строке «Текст» диалогового окна функции указываем адрес ячейки с текстом, в котором нужно посчитать символы. Нажимаем «ОК».

Получилась такая формула в ячейке А49. =ДЛСТР(A49) Копируем формулу по столбцу. Какими способами быстро скопировать формулу по столбцу, смотрите в статье «Копирование в Excel». Получилось так.

Видео: Как посчитать количество текстовых повторов в таблице Excel, функция СЧЕТЕСЛИ

Обратите внимание. Excel посчитал и кавычки, это, тоже, символы, как и тире, пробелы. В Excel есть возможность вставить весь текст из Word в одну ячейку. Обычным способом текст из Word вставляется в несколько ячеек, что нарушет всю таблицу.

Второй вариант. Посчитать количество цифр в ячейке Excel.

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

Третий вариант. Посчитать количество символов в нескольких ячейках Excel.

Нам нужно посчитать количество символов в нескольких ячейках сразу, узнать их сумму. Первый способ. Устанавливаем функцию «ДЛСТР», как в первом варианте. Затем, функцией «Автосумма» складываем эти значения отдельных ячеек.

Второй способ. Совмещаем функцию «ДЛСТР» с функцией «СУММ». Получится такая формула.

Четвертый вариант. Как посчитать количество конкретных букв в Excel.

Первый способ. Нам нужно посчитать количество буквы «с» в словах. В ячейку В66 пишем такую формулу.

Копируем формулу по столбцу. Получится так. Кстати, эта формула поможет быстро проверить тесты учеников. : )

Второй способ. Нам нужно посчитать в словах количество сразу двух букв – «с» и «о». В ячейке С66 напишем такую формулу.

Копируем формулу по столбцу.

Этой же формулой можно посчитать количество цифр. Только, в формуле, вместо букв, в кавычках пишем нужную цифру.

Пятый вариант. Нам нужно посчитать во всех ячейках букву «с», т.е. сложить букву «с» во всех ячейках. Для этого не нужно считать буквы во всех ячейках отдельно. Достаточно написать одну формулу. В ячейке E66 напишем такую формулу.

В тексте в ячейке Excel можно посчитать не только символы, но и слова.

(Пока оценок нет)

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

При работе с таблицами Excel в некоторых случаях пользователю нужно определить количество символов в определенной ячейке. Если содержимое ячейки незначительно, то это без особых усилий можно сделать вручную. Но как быть, если символов достаточно много или, скажем, содержимое ячейки меняется в зависимости от какого-либо фактора или действия в таблице.

На самом деле выполнить поставленную задачу достаточно просто, ведь в Эксель предусмотрена функция под названием “ДЛСТР”, которая применяется именно в таких случаях.

Количество символов в одной ячейке

Ниже приведен алгоритм действий для использования функции “ДЛСТР”:

1. Становимся в ту ячейку, куда планируем вставить функцию, после чего щелкаем по кнопке “Вставить функцию” (с левой стороны от строки формул).
2. Откроется окно Мастера функций. Кликнув по текущей категории выбираем пункт “Текстовые”.
3. В предложенном перечне операторов выбираем “ДЛСТР” и жмем кнопку OK.
4. На экране отобразится окно аргументов функции. На самом деле у данного оператора всего одно поле для заполнения, в котором нужно указать координаты ячейки, количество символов в которой нужно посчитать. Причем указать можно только адрес конкретной ячейки, ссылки на несколько ячеек или диапазон элементов оператором не поддерживаются. Прописать координаты в поле аргумента можно вручную. Также выбрать нужную ячейку можно, щелкнув левой кнопкой мыши по ней, предварительно кликнув по области ввода информации. После того, как значение аргумента заполнено, кликаем OK.
5. Результат в виде количества символов в выбранной ячейке (в аргументах функции) сразу же отобразится на экране. А готовая формула функции отобразится, соответственно, в строке формул.

Количество знаков в столбце или строке

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

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

Ранее мы упоминали, что в качестве аргумента функции “ДЛСТР” можно указать только адрес одной конкретной ячейки. Поэтому, чтобы определить суммарное количество знаков во всех ячейках столбца нужно воспользоваться одним из способов подсчета суммы значений. Подробнее ознакомиться со всеми методами можно в нашей статье – “Как посчитать в Экселе сумму столбца”.

Давайте к примеру, воспользуемся одним из способов:

1. Выделяем ячейки столбца. Далее нажимаем кнопку “Сумма” в разделе инструментов “Редактирование” (вкладка “Главная”).
2. Результат в виде суммарного количества символов во всех ячейках столбца отобразится в ячейке сразу под выделенным диапазоном.

Определение количества знаков в разбросанных ячейках

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

В этом случае нас опять выручит суммирование значений ячеек. Вот, что нужно сделать:

1. 1. Отмечаем ячейку, куда планируем вставить формулу. Далее ставим знак “равно” (“=”) и через знак плюса добавляем функции “ДЛСТР” для каждой ячейки, которая должна участвовать в финальном расчете. Например, нам нужно определить итоговое количество знаков в ячейках A2, A5, A10, B3, B10, C8 и C12.
      Следовательно, финальная формула должна выглядеть следующим образом:
      =ДЛСТР(A2)+ДЛСТР(A5)+ДЛСТР(A10)+ДЛСТР(B3)+ДЛСТР(B10)+ДЛСТР(C8)+ДЛСТР(C12).
   2. После того, как формула набрана, нажимаем клавишу Enter и получаем готовый результат в выбранной ячейке.

Заключение

Некоторые пользователи, столкнувшись с такой задачей, как подсчет количества знаков в определенной ячейке или диапазоне ячеек в Excel тратят немало усилий на выполнение данной процедуры вручную, не догадываясь о наличии специально предназначенной для этого функции ДЛСТР, освоение которой займет пару минут, а результатом станет безошибочный результат и экономия одного из самых главных ресурсов – времени.