Как в excel посчитать количество слов в столбце
В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в ячейке или диапазоне с учетом и без учета регистра букв.
В Microsoft Excel есть несколько полезных функций, которые могут подсчитывать почти все: функция СЧЁТ для подсчета ячеек с числами, СЧЁТЗ для подсчета непустых ячеек, СЧЁТЕСЛИ и СЧЁТЕСЛИМН для условного подсчета ячеек и ДЛСТР для вычисления длины текстовой строки. Мы рассмотрим различные способы подсчета слов:
К сожалению, в Excel нет встроенного инструмента для подсчета количества слов. Но, комбинируя функции, вы можете создавать более сложные выражения для решения практически любой задачи. И мы будем использовать этот подход для подсчета слов в Excel.
Как посчитать общее количество слов в ячейке
Для подсчета слов в ячейке используйте следующую комбинацию функций ДЛСТР, ПОДСТАВИТЬ и СЖПРОБЕЛЫ:
Сюда вы подставляете адрес ячейки, в которой вы хотите посчитать слова.
Например, чтобы пересчитать слова в ячейке A2, используйте такое выражение:
Затем вы можете скопировать его вниз по столбцу, чтобы найти количество слов в других ячейках столбца A:
Как работает эта формула подсчета слов?
Во-первых, вы используете функцию ПОДСТАВИТЬ, удаляя этим все пробелы в тексте и заменив их пустой строкой (""), чтобы функция ДЛСТР возвратила количество символов без пробелов:
После этого вы вычитаете длину строки без пробелов из общей длины строки и добавляете 1 к окончательному количеству слов, поскольку количество слов всегда равно количеству пробелов плюс 1.
Кроме того, вы используете функцию СЖПРОБЕЛЫ, чтобы удалить лишние пробелы в тексте, если они есть. Иногда рабочий лист может содержать много невидимых на первый взгляд пробелов, например, два или более между словами или случайно набранные в начале или в конце текста (то есть начальные и конечные пробелы). И все они могут испортить результаты вашего подсчета слов. Поэтому удаляем все лишние пробелы, кроме обычных между словами.
Как видно на скриншоте выше, расчёт возвращает ноль для пустых ячеек и правильное количество слов для непустых.
Как посчитать конкретные слова в ячейке
Чтобы подсчитать, сколько раз появляется определенное слово, текст или подстрока, используйте следующий шаблон:
Например, давайте посчитаем количество появлений слова "напрасно" в A2:
Совет. Если вы планируете копировать формулу в несколько ячеек, обязательно используйте абсолютные и относительные ссылки, как это сделано в примере выше.
Рассмотрим пошагово, как подсчитывается количество вхождений определенного текста в ячейку
- Функция ПОДСТАВИТЬ удаляет указанное слово из исходного текста.
В этом примере мы удаляем слово, введенное в B1, из исходного текста, расположенного в A2:
ПОДСТАВИТЬ($A2;B$1;"") - Затем функция ДЛСТР вычисляет длину текстовой строки без указанного слова.
В этом примере ДЛСТР(ПОДСТАВИТЬ($A2;B$1;"")) возвращает длину текста в ячейке A2 после удаления всех символов, содержащихся во всех вхождениях слова «напрасно». - После этого полученное в п.2 число вычитается из общей длины исходного текста:
ДЛСТР($A2)-ДЛСТР(ПОДСТАВИТЬ($A2;B$1;"")) - Результатом этой операции является количество символов, содержащихся во всех вхождениях целевого слова, которое в этом примере равно 16 (2 вхождения слова «напрасно», по 8 символов в каждом).
- Наконец, посчитанное выше число делится на длину слова. Другими словами, вы делите количество символов, содержащихся во всех вхождениях целевого слова, на количество символов, содержащихся в одном вхождении этого слова. В этом примере 16 делится на 8, и в результате мы получаем 2.
Помимо подсчета количества определенных слов в ячейке, вы можете использовать эту формулу для подсчета вхождений любого текста (подстроки). Например, вы можете подсчитать, сколько раз появляется текст «Вынес»:
Как видите, часть слова здесь тоже была учтена при подсчёте.
Формула с учетом регистра для подсчета определенных слов в ячейке
Как вы, наверное, знаете, в функции Excel ПОДСТАВИТЬ учитывается регистр букв. Поэтому используемая нами формула подсчета слов по умолчанию чувствительна к регистру:
Вы можете в этом убедиться на скриншоте выше.
Формула без учета регистра для подсчета определенных слов в ячейке
Если вам нужно подсчитать вхождения данного слова как в верхнем, так и в нижнем регистре, используйте функцию СТРОЧН или ПРОПИСН внутри ПОДСТАВИТЬ, чтобы преобразовать исходный текст и тот текст, который вы хотите подсчитать, в один и тот же регистр.
Например, чтобы подсчитать количество вхождений слова из B2 в ячейке A3 без учета регистра, используйте:
Как показано на скриншоте ниже, выражение возвращает одно и то же количество слов независимо от того, как набрано слово:
Как сосчитать общее количество слов в диапазоне
Чтобы узнать, сколько слов содержит строка, столбец или диапазон, возьмите формулу, которая подсчитывает общее количество слов в ячейке, и вставьте ее в функцию СУММПРОИЗВ или СУММ:
=СУММПРОИЗВ(ДЛСТР(СЖПРОБЕЛЫ( диапазон ))-ДЛСТР(ПОДСТАВИТЬ( диапазон ;" ";""))+1)
=СУММ(ДЛСТР(СЖПРОБЕЛЫ( диапазон ))-ДЛСТР(ПОДСТАВИТЬ( диапазон ;" ";""))+1)
СУММПРОИЗВ - одна из немногих функций Excel, которые умеют обрабатывать массивы. Поэтому вы завершаете ввод обычным способом, нажимая клавишу Enter.
Чтобы функция СУММ могла вычислять массивы, ее следует использовать в формуле массива, которая завершается нажатием Ctrl + Shift + Enter вместо обычного ввода Enter.
Например, чтобы подсчитать все слова в столбце A2:A5, используйте один из следующих вариантов:
=СУММПРОИЗВ(ДЛСТР(СЖПРОБЕЛЫ(A2: A5))-ДЛСТР(ПОДСТАВИТЬ(A2: A5;" ";""))+1)
Как подсчитать конкретные слова в диапазоне
Если вы хотите подсчитать, сколько раз конкретное слово или текст появляется в строке, столбце или же в определённом диапазоне ячеек, используйте аналогичный подход — возьмите формулу для подсчета определенных слов в ячейке и объедините ее с функцией СУММ или СУММПРОИЗВ:
=СУММПРОИЗВ((ДЛСТР( диапазон )-ДЛСТР(ПОДСТАВИТЬ( диапазон ; слово ;"")))/ДЛСТР( слово))
=СУММ((ДЛСТР( диапазон )-ДЛСТР(ПОДСТАВИТЬ( диапазон ; слово ;"")))/ДЛСТР( слово))
Пожалуйста, не забудьте нажать Ctrl + Shift + Enter , чтобы правильно использовать функцию СУММ как формулу массива.
Например, чтобы подсчитать все вхождения слова, находящегося в C1, в столбце A2:A5, используйте это выражение:
Если не нужно учитывать регистр букв, добавьте функцию СТРОЧН, как делали ранее при подсчёте в отдельной ячейке:
Как сосчитать слова без использования формул.
Если нужно быстро пересчитать слова в ячейке или в диапазоне, то можно сделать это и без формул. Для этого служит инструмент «Count Words», который входит в надстройку Ultimate Suite for Excel.
Об этом замечательном инструменте я уже много рассказывал, и вот здесь он тоже может пригодиться.
Подробный обзор возможностей инструмента подсчёта слов и отдельных символов в ячейках вы можете посмотреть здесь на нашем сайте.
А сейчас на скриншоте ниже вы видите результаты его применения. Нужно выделить диапазон ячеек (или только одну из них), активировать опцию Count Words, выбрать – как вы хотите получить итоговый результат: в виде числа или формулой. После этого нажимаем кнопку Insert Results. Справа от выделенной области будет вставлен столбец с результатами.
На скриншоте выше вы видите, что результаты подсчета слов при помощи рассмотренных в этой статье формул и с использованием инструмента «Count Words» — одинаковы. Только времени во втором случае у нас уйдёт гораздо меньше.
Вот как можно сосчитать слова в Excel.
Если ни одно из решений, обсуждаемых в этом руководстве, для вас не подошло, пишите в комментариях. Постараюсь помочь.
Быть может, вас также заинтересует:
Как быстро извлечь число из текста в Excel - В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно…
Функция ПРАВСИМВ в Excel — примеры и советы. - В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. - В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…
Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов - ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим…
5 примеров с функцией ДЛСТР в Excel. - Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…
Как быстро сосчитать количество символов в ячейке Excel - В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Как преобразовать текст в число в Excel — 10 способов. - В этом руководстве показано множество различных способов преобразования текста в число в Excel: опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое. Иногда значения в ваших…
В этой статье я расскажу о некоторых приемах подсчета количества слова или символа в столбце или отдельной ячейке на листе Excel.
Подсчитайте количество экземпляров числа или слова в столбце с формулой
В этом разделе я представляю формулу для подсчета количества экземпляров числа или слова в столбце.
Выберите пустую ячейку и введите эту формулу = СЧЁТЕСЛИ (A1: A10; «Звезды» ), Нажмите Enter key, затем подсчитывает количество экземпляров слова «Звезды» в диапазоне столбцов. Смотрите скриншот:
В формуле A1: A10 - это диапазон столбцов, из которого вы хотите посчитать слово или число, а звезды - это слово, которое вы хотите подсчитать, вы можете изменить его на число, символ или другие слова по своему усмотрению.
Подсчитайте количество экземпляров символа или слова в ячейке с формулой
Если вы хотите подсчитать количество символа или слова в одной ячейке, вы можете попробовать следующие методы.
Подсчитайте определенный символ в одной ячейке с помощью формулы
Выберите пустую ячейку, введите эту формулу = LEN (F1) -LEN (ПОДСТАВИТЬ (F1; G1; "")) , Нажмите Enter, чтобы получить результат. Смотрите скриншот:
Функции: в этой формуле F1 - это ячейка, из которой вы хотите посчитать символы, а G1 - это символ, который вы хотите подсчитать, вы можете изменить их по своему усмотрению.
Подсчитайте определенное слово в одной ячейке с помощью формулы
Выберите пустую ячейку, введите эту формулу = (LEN (A14) -LEN (ПОДСТАВИТЬ (A14, B14, ""))) / LEN (B14) , Нажмите Enter ключ для получения результата. Смотрите скриншот:
Наконечник: в этой формуле A14 - это используемая вами ячейка, B14 - это слово, которое вы хотите посчитать, вы можете изменить их по своему усмотрению.
Подсчитайте конкретное слово в ячейке с помощью Kutools for Excel
Если вы не хотите запоминать формулы, вы можете подать заявку Kutools for ExcelАвтора Подсчитайте количество слова утилита для быстрого подсчета определенного слова в диапазоне.
Подсчитайте количество всех слов в диапазоне или ячейке с помощью Kutools for Excel
В некоторых случаях вам может потребоваться подсчитать количество всех слов в диапазоне или ячейке так же, как общее количество в документе Word. Очевидно, вы можете скопировать содержимое ячеек в документ Word, чтобы подсчитать общее количество слов, но здесь я представляю удобный инструмент добавления - Kutools for Excel вам, с его Подсчитать слова в диапазоне утилита для быстрого подсчета слов в диапазоне или ячейке.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите пустую ячейку, в которую вы поместите подсчитанный результат, нажмите Кутулс > Формула Помощник > Формула Помощник. Смотрите скриншот:
2. в Помощник по формулам диалог, проверьте Фильтр флажок, затем введите " считать "в текстовое поле, чтобы отфильтровать относительный счет по формуле, затем нажмите Подсчитать общее количество слов из Выберите формулу раздел, а затем выберите ячейки, в которых вы хотите подсчитать слова в Диапазон текстовое окно.
3. Нажмите Ok. И было подсчитано общее количество слов в выбранном диапазоне.
Как подсчитать количество определенных / конкретных слов в ячейке или диапазоне ячеек в Excel?
Предположим, вам нужно подсчитать, сколько раз конкретное слово появляется в ячейке или диапазоне ячеек, как этого добиться? В этом руководстве представлены два метода, которые помогут вам шаг за шагом решить эту проблему.
Easily count number of certain words in a cell or a range in Excel:
The Count times a word appears utility of Kutools for Excel can help you quickly count number of certian word in a cell or a range of cells in Excel. See screenshot:
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Подсчитайте количество определенных слов в ячейке с формулой
Вы можете комбинировать функции LEN и SUBSTITUTE для подсчета количества определенных слов в ячейке в Excel.
1. Выберите пустую ячейку, введите в нее приведенную ниже формулу и нажмите Enter ключ.
= (LEN (B2) -LEN (ПОДСТАВИТЬ (B2; D2; ""))) / LEN (D2)
Заметки:
1. В формуле B2 - это ячейка с текстовой строкой, внутри которой нужно посчитать определенное слово, D2 содержит определенное слово, которое вы будете считать. Измените ссылки на ячейки в зависимости от ваших ячеек.
Затем вы увидите, что общее количество определенного слова в определенной ячейке вычисляется и заполняется в выбранной ячейке.
Подсчитайте количество определенных слов в ячейке или диапазоне ячеек с помощью Kutools for Excel
Считайте, сколько раз появляется слово полезности Kutools for Excel помогает легко подсчитывать количество определенных слов в ячейке или диапазоне ячеек, не запоминая никаких формул.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите пустую ячейку для вывода результата, затем щелкните Кутулс > Формула Помощник > Формула Помощник.
2. в Помощник по формулам диалоговое окно, настройте следующим образом.
2.1). Выберите формулу коробка, найдите и выберите Подсчитайте количество слова;
Советы: Вы можете проверить Фильтр введите ключевое слово в текстовое поле, чтобы быстро отфильтровать нужную формулу.
2.2). Текст выберите ячейку или диапазон ячеек, в которых вы хотите подсчитать, сколько раз встречается слово;
2.3). Word поле укажите ячейку, содержащую конкретное слово, которое вы будете считать;
Советы: Вы можете напрямую ввести определенное слово в поле Word, как показано на скриншоте ниже.
Тогда вы получите результат в выбранной ячейке. Смотрите скриншот:
Внимание: Эта функция чувствительна к регистру.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Вы можете легко подсчитать количество слов в MS Word, но в Excel нет встроенного инструмента для подсчета количества слов на листе. Однако вы можете подсчитать количество слов в Excel следующими способами:
Подсчитайте количество слов по формуле
Вот две формулы для подсчета слов в одной ячейке и в ячейках диапазона.
Подсчитайте слова в одной ячейке
Пожалуйста, введите эту формулу =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1) в Панель формул, а затем нажмите Enter ключ.
Внимание: В формуле A2 - это ячейка, в которой вы будете подсчитывать количество слов.
Вы можете увидеть результат, как показано на скриншоте ниже:
Подсчет слов в диапазоне ячеек с помощью формулы массива
Если вы хотите подсчитать количество слов в диапазоне ячеек, введите формулу =SUM(IF(LEN(TRIM(A2:A3))=0,0,LEN(TRIM(A2:A3))-LEN(SUBSTITUTE(A2:A3," ",""))+1)) в строку формул, а затем нажмите Shift + Ctrl + Enter клавиши одновременно, чтобы получить результат. Смотрите скриншот:
Внимание: A2: A3 - это диапазон слов, которые вы будете считать.
Легко подсчитайте общее количество слов в ячейке или диапазоне ячеек в Excel:
Подсчитать общее количество слов полезности Kutools for Excel может помочь вам быстро подсчитать общее количество слов в ячейке или диапазоне ячеек в Excel, как показано в демонстрации ниже.
Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная трасса)
Подсчитайте количество слов с помощью функций, определяемых пользователем
Кроме того, вы можете подсчитать количество слов в ячейке с помощью функций, определяемых пользователем, сделайте следующее:
1. Нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. В winodw нажмите Вставить > Модули, затем скопируйте и вставьте ниже код VBA в модуль. Смотрите скриншот:
Код VBA: подсчитать количество слов в ячейке.
2. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений Winodw. Выберите пустую ячейку на листе, введите формулу « =intwordcount(A2) »В панель формул, а затем нажмите Enter ключ для получения результата. Смотрите скриншот:
Внимание: В формуле A2 - это ячейка, в которой вы будете подсчитывать количество слов.
Если вы хотите подсчитать количество слов в определенном диапазоне, примените следующий метод.
Подсчитать количество слов в указанном диапазоне с кодом VBA
Следующий код VBA может помочь вам быстро подсчитать количество слов в указанном диапазоне.
1. Нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. В winodw нажмите Вставить > Модули, затем скопируйте и вставьте ниже код VBA в модуль. Смотрите скриншот:
Код VBA: подсчитать количество слов в выбранном диапазоне.
3. нажмите F5 ключ для запуска кода. В всплывающем Kutools for Excel в диалоговом окне выберите диапазон, в котором вы будете подсчитывать количество слов, а затем щелкните OK кнопка. Смотрите скриншот:
Затем другой Kutools for Excel диалоговое окно всплывает, чтобы показать вам общее количество слов в выбранном диапазоне. Смотрите скриншот:
Подсчитайте количество слов с помощью Kutools for Excel (только щелчки)
Y ты можешь попробовать Подсчитать слова в диапазоне утилита Kutools for Excel, чтобы легко подсчитать количество слов в ячейке или диапазоне всего за несколько кликов.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите ячейку для возврата результата, затем щелкните Кутулс > Формула Помощник > Формула Помощник.
2. в Помощник по формулам диалоговое окно, настройте следующим образом.
- В Выберите формулу выберите Подсчитать общее количество слов;
Советы: Вы можете проверить Фильтр поле введите ключевое слово, чтобы быстро отфильтровать формулу по мере необходимости. - В Диапазон поле укажите ячейку или диапазон, в котором вы хотите подсчитать общее количество слов;
- Нажмите OK кнопка. Смотрите скриншот:
Тогда вы получите количество слов в указанной ячейке или диапазоне.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Легко подсчитать количество слов с помощью Kutools for Excel
Пользователи Microsoft Word знают, на сколько полезна возможность узнать количество слов в набранном тексте. Однако, пользуясь Excel, узнать количество слов в документе не возможно штатными средствами.
В этой статье мы разберем, как с помощью формул посчитать количество слов в одной или нескольких ячейках.
Как посчитать количество слов в ячейке Excel
Для подсчета количества слов в ячейке нам потребуются функции ДЛСТР и ПОДСТАВИТЬ . Формула для учета количества слов будет выглядеть так:
Используя эту формулу для любой ячейки, вы получите значение количества слов, находящихся в ней.
Как эта формула работает?
Прежде чем мы погрузимся в то, как работает формула, предлагаю поразмышлять.
Если мы составим обычное предложение из 8 слов, то их будут разделять 7 пробелов.
Это означает, что в любом предложении слов на один больше чем пробелов. То есть, для того, чтобы посчитать количество слов в предложении, нам нужно рассчитать количество пробелов и прибавить к этому числу один.
Соответственно, наша формула работает следующим образом:
- Функция ДЛСТР в первой части формулы подсчитывает количество символов в ячейке (с учетом пробелов)
- Во второй и третьей части формулы мы комбинируем функции ДЛСТР и ПОДСТАВИТЬ для подсчета количества символов в ячейке без пробелов
- Прибавляем к полученному значению число «один»
Если вы используете эту формулу для пустой ячейки, то результат вычисления будет равен единице.
Во избежание этого, я предлагаю использовать в дополнение две функции: ЕСЛИ и ЕПУСТО . Формула будет выглядеть так:
Эти две функции проверяют, есть ли текст в ячейке или она пустая. Если в ячейке нет текста, формула вернет значение «ноль».
Как посчитать количество слов в нескольких ячейках Excel
Теперь, перейдем на более сложный уровень.
Представим, что наша задача посчитать количество слов в нескольких ячейках.
Хорошая новость заключается в том, что мы будем использовать ту же формулу, что мы рассматривали на предыдущем примере, с небольшим дополнением:
=СУММПРОИЗВ(ДЛСТР(A1:A10)-ДЛСТР(ПОДСТАВИТЬ(A1:A10;» «;»»))+1)
В указанной выше формуле А1:А10 это диапазон ячеек в рамках которого мы хотим посчитать количество слов.
Как эта формула работает?
Эта формула работает по тому же принципу, что и для подсчета количества слов в одной ячейке. Разница лишь в использовании функции СУММПРОИЗВ . Она помогает нам вычислить количество слов во всем массиве данных.
Всякий раз, когда вы вводите текст в ячейку или диапазон ячеек, эти методы позволяют посчитать количество слов.
Я надеюсь, что в будущем Excel получит штатную возможность для подсчета слов.
Читайте также: