Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel подсчет слов в ячейке

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

Читать еще:  Таймер в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Волатильные функции excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как подсчитать количество повторений

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

И необходимо подсчитать количество повторений каждого наименования:

Как ни странно, но сделать это весьма просто: в Excel имеется функция — СЧЁТЕСЛИ, при помощи которой все это сделать можно буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ( $A$2:$A$30 ; A2 )
Диапазон ( $A$2:$A$30 ) — указываются ячейки диапазона, в которых записаны значения, количество которых необходимо подсчитать. Главная особенность: данный аргумент может быть исключительно ссылкой на ячейку или диапазон ячеек. Недопустимо указывать произвольный массив значений.
Критерий ( A2 ) — указывается ссылка на ячейку или непосредственно значение для подсчета. Т.е. можно указать и так: =СЧЁТЕСЛИ( $A$2:$A$30 ;»Яблоко») . Помимо этого можно применять символы подстановки: ? и *. Т.е. указав в качестве Критерия «*банан*» можно подсчитать количество ячеек, в которых встречается слово «банан» (банановый, банан, бананы, банановый сок, сто бананов, три банана и орех и т.п.). А указав «банан*» — значения, начинающиеся на «банан» (бананы, банановый сок, банановая роща и т.п.). «?» — заменяет лишь один символ, т.е. указав «бан?н» можно подсчитать строки и со значением «банан» и со значением «банон» и т.д. Если в качестве критерия указать =СЧЁТЕСЛИ( $A$2:$A$30 ;»*») , то будут подсчитаны все текстовые значения. Числовые значения при этом игнорируются. Данные подстановочные символы (* и ?) не получится применить к числовым значениям — исключительно к тексту. Т.е. если если указать в качестве критерия «12*», то числа 1234, 123, 120 и т.п. не будут подсчитаны. Для подсчета числовых значений следует применять операторы сравнения: =СЧЁТЕСЛИ( $A$2:$A$30 ;»>12″)

Читать еще:  Пустое значение в excel

Подсчитать числа, которые больше нуля: =СЧЁТЕСЛИ( $A$2:$A$30 ;»>0″)
Подсчитать количество непустых ячеек: =СЧЁТЕСЛИ( $A$2:$A$30 ;»<>«)

Как видно из второго рисунка — там наименования не повторяются, хотя в таблице они все записаны вперемешку. Я не буду заострять на этом внимание — я уже описывал это в статье Как получить список уникальных(не повторяющихся) значений? и при необходимости можно воспользоваться любым описанным в ней методом.

Но. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке( $D$1 ):
Дыня Киви Груша Яблоко Дыня Груша Груша Арбуз Яблоко Банан Яблоко Яблоко Банан Яблоко Яблоко Дыня Дыня Киви Банан Дыня Арбуз Дыня Киви Яблоко Дыня Груша Яблоко Киви Арбуз
Здесь СЧЁТЕСЛИ точно не поможет. Но в Excel полно других функций и все можно сделать так же достаточно просто:
=(ДЛСТР( $D$1 )-ДЛСТР(ПОДСТАВИТЬ( $D$1 ; D3 ;»»)))/ДЛСТР( D3 )
ДЛСТР — подсчитывает количество символов в указанной ячейке/строке( $D$1 , D3 )
ПОДСТАВИТЬ (текст; старый_текст; новый_текст) — заменяет в указанном тексте заданный символ на любое другое заданное значение. По умолчанию заменяет все повторы указанного символа. Именно это и положено в основу алгоритма. На примере значения Банан( D3 ) пошаговый разбор формулы:

  • при помощи функции ДЛСТР получаем количество символов в строке с исходным текстом( $D$1 ) =(170-ДЛСТР(ПОДСТАВИТЬ( $D$1 ; D3 ;»»)))/ДЛСТР( D3 ) ;
  • при помощи функции ПОДСТАВИТЬ заменяем в строке с исходным текстом( $D$1 ) все значения Банан( D3 ) на пусто и при помощи ДЛСТР получаем количество символов строки после этой замены =(170-155)/ДЛСТР( D3 ) ;
  • вычитаем из общего количества символов количество символов в строке после замены и делим результат на количество символов в критерии =(170-155)/5 .

Получаем число 3. Что нам и требовалось.

И простая функция пользователя, которая так же подсчитывает повторения внутри ячейки:

Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len(sTxt) — Len(Replace(sTxt, sCntWord, «»))) / Len(sCntWord) End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(InsertModule) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Ctrl + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .
Синтаксис функции:
=GetRepeat( $D$1 ; D3 )
sTxt — текст, в котором подсчитываем кол-во вхождения.
sCntWord — текст для подсчета. Может быть символом или словом.

Tips_All_Count_Duplicate.xls (39,0 KiB, 9 457 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Добрый день.
Можете ли вы мне помочь?

Нужно посчитать количество повторений по столбцу рейс только в одной строке.
Рейс. Объем. Колич
повтор
1аб. 2 2
1ав. 3. 1
1 аб. 0.3. 2 (2нужно посчитать как 0)
2аг. 9. 2
2аг. 3. 2 (2нужно посчитать как 0)
2ав. 0.4. 1

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

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

Ссылка на основную публикацию
Adblock
detector