Посчитать в excel сколько раз встречается слово в
В этой статье я хочу рассказать, как можно подсчитать количество повторений какого-либо значения в таблице или в ячейке. Начнем по порядку. Имеется таблица:
И необходимо подсчитать количество повторений каждого наименования:
Самый простой способ - создать сводную таблицу, поместив в область строк и в область значений данные столбца А. Сводная сделает все сама. Подробнее про создание и использование сводных таблиц можно узнать в этой статье с видеоуроком: Общие сведения о сводных таблицах
Но если по каким-то причинам сводная не Ваш вариант - в Excel имеется функция СЧЁТЕСЛИ (COUNTIF) , при помощи которой все это можно сделать тоже буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ( $A$2:$A$30 ; A2 )
=COUNTIF( $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")
Подсчитать числа, которые больше нуля: =СЧЁТЕСЛИ( $A$2:$A$30 ;">0")
Подсчитать количество непустых ячеек: =СЧЁТЕСЛИ( $A$2:$A$30 ;"<>")
Как видно из второго рисунка - там наименования не повторяются, хотя в таблице они все записаны вперемешку. Я не буду заострять на этом внимание - я уже описывал это в статье Как получить список уникальных(не повторяющихся) значений? и при необходимости можно воспользоваться любым описанным в ней методом.
Если необходимо подсчитать количество повторений на основании нескольких условий(значений), то начиная с 2007 Excel это легко можно сделать при помощи функции СЧЁТЕСЛИМН(COUNTIFS). Синтаксис функции почти такой же, как у СЧЁТЕСЛИ(COUNTIF), только условий и диапазонов больше:
=СЧЁТЕСЛИМН( $A$2:$A$30 ; A2 ; $B$2:$B$30 ; B2 )
предполагается, что условия записаны в столбце В
По сути идет просто перечисление:
=СЧЁТЕСЛИМН(Диапазон_условий1;Условие1; Диапазон_условий2;Условие2; Диапазон_условий3;Условие3; и т.д.)
Так же не могу не написать про небольшую особенность функции СЧЁТЕСЛИ (а так же СЧЁТЕСЛИМН , СУММЕСЛИ , СУММЕСЛИМН и им подобных) - данные функции всегда стремятся преобразовать все значения аргументов к типам(в отличии от той же ВПР , которая к типам относится очень бережно и ничего не преобразует). Что это значит. Если у нас в ячейке записано число 23 - оно будет воспринято как число. Если тоже число будет записано как текст - "23" , то функция преобразует его сначала в число, а потом уже будет работать с ним. Т.е. и 23 и "23" у нас будут считаться одинаковым значением. С одной стороны это хорошо, но иногда такое поведение может сыграть злую шутку. Например, у Вас в ячейках расположены некие номера счетов, длина которых более 15-ти символов и могут иметь ведущие нули:
000 34889913131323455
00 34889913131323455
000 34889913131323477
как видно, первые два числа почти одинаковые, но у первого числа три ведущих нуля спереди, а второго - два. И это разные счета. А третий счет вообще отличается на последние цифры. Но СЧЁТЕСЛИ после преобразования все три этих значения будет считать как число 348899131313234 00 и если записать функцию так: =СЧЁТЕСЛИ( $A$1:$A$3 ; A1 ) , то она вернет значение 3. Особо обращаю внимание на тот факт, что все числа после 15-го знака будут преобразованы в нули. Эти особенности всегда необходимо учитывать при использовании данных функций, чтобы не попасть в неловкую ситуацию, когда подсчет будет некорректным.
Еще один вариант подсчета значений. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке( $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. Что нам и требовалось.
Но тут есть и более каверзная ситуация - когда у нас диапазон ячеек, в каждой из которых наше слово может встречаться более одного раза. И подсчитать надо ВСЕ повторения. Диапазон для подсчета повторений у нас будет в ячейках A1:A10 . Слово для подсчета повторений запишем в ячейку B1 (там будет все тоже слово " банан "):
Базируясь на формуле выше можно написать такую:
=СУММПРОИЗВ((ДЛСТР( A1:A10 )-ДЛСТР(ПОДСТАВИТЬ( A1:A10 ; B1 ;"")))/ДЛСТР( B1 ))
И простая функция пользователя, которая так же подсчитывает повторения внутри ячейки:
Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len(sTxt) - Len(Replace(sTxt, sCntWord, ""))) / Len(sCntWord) End Function
Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Ctrl + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .
Синтаксис функции:
=GetRepeat( $D$1 ; D3 )
sTxt - текст, в котором подсчитываем кол-во вхождения.
sCntWord - текст для подсчета. Может быть символом или словом.
Пример Подсчета повторений.xls (70,5 KiB, 12 804 скачиваний)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Предположим, вам нужно узнать количество уникальных значений в диапазоне, который содержит повторяющиеся значения. Например, если столбец содержит:
Значения 5, 6, 7 и 6, результатом являются три уникальных значения : 5, 6 и 7.
Значения "Брэнли", "Дойл", "Дойл", "Дойл" — это два уникальных значения: "Андрей" и "Дойл".
Существует несколько способов подсчета уникальных значений среди дубликатов.
В диалоговом окне Расширенный фильтр можно извлечь уникальные значения из столбца данных и ввести их в новое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.
Выберем диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
Убедитесь, что диапазон ячеек имеет заголовок столбца.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Появится диалоговое окно Расширенный фильтр.
В поле Копировать в введите ссылку на ячейку.
Вы также можете нажать кнопку Свернуть , чтобы временно скрыть диалоговое окно, выбрать ячейку на этом сайте и нажать кнопку Развернуть .
Выберите поле Уникальные записи и нажмите кнопку ОК.
Уникальные значения из выбранного диапазона копируется в новое место, начиная с ячейки, указанной в поле Копировать в.
В пустой ячейке под последней ячейкой диапазона введите функцию СТРОКИ. Используйте диапазон уникальных значений, скопированные в качестве аргумента, исключая заголовок столбца. Например, если диапазон уникальных значений — B2:B45, введите =СТРОКИ(B2:B45).
Для этой задачи используйте сочетание функций ЕСЛИ,СУММ,ЧАСТОТА,НАЙТИИ LEN:
Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.
Сложить итог с помощью функции СУММ.
Подсчет количества уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА игнорирует текст и нулевые значения. Для первого вхождения определенного значения эта функция возвращает число, равное количеству его вхождений. Для каждого вхождения с одинаковым значением после первого функция возвращает ноль.
Возвращает положение текстового значения в диапазоне с помощью функции MATCH. Возвращаемая величина затем используется в качестве аргумента функции ЧАСТОТА для оценки соответствующих текстовых значений.
Находите пустые ячейки с помощью функции LEN. Пустые ячейки имеют длину 0.
Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Чтобы оценить функцию пошаговую проверку, выйдите из ячейки, содержащей формулу, а затем на вкладке Формулы в группе Зависимости формул нажмите кнопку Вы оцениваете формулу.
Функция ЧАСТОТА вычисляет частоту ветвей значений в диапазоне значений и возвращает вертикальный массив чисел. Например, с помощью частоты можно подсчитать количество результатов тестирования, которые попадают в диапазоны оценок. Так как эта функция возвращает массив, она должна быть введена как формула массива.
Функция ПОИСК ПОИСК ПО ищет указанный элемент в диапазоне ячеек, а затем возвращает его относительную позицию в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =MATCH(25;A1:A3;0) возвращает число 2, поскольку 25 является вторым элементом в диапазоне.
Функция LEN возвращает количество символов в текстовой строке.
Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, СУММ(A1:A5) суммирует все числа, содержащиеся в ячейках A1–A5.
Функция ЕСЛИ возвращает одно значение, если условие, которое вы указываете, возвращает значение ИСТИНА, и другое, если условие возвращает значение ЛОЖЬ.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Аннотация
Эта статья содержит и описывает формулы, которые вычисляют следующее:
- Количество случаев появления текстовой строки в диапазоне ячеек.
- Количество случаев появления символа в одной ячейке.
- Количество случаев появления символа в диапазоне ячеек.
- Количество слов (или текстовых строк), разделенных символом в ячейке.
Дополнительная информация
Формула для подсчета количества вхождений текстовой строки в диапазоне
=SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")
Где диапазон — диапазон ячеек, а "text" заменяется конкретной текстовой строкой, которую вы хотите посчитать.
Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массива в Excel для Windows, нажмите CTRL+SHIFT+ENTER. Чтобы ввести формулу в качестве массива в Excel для Macintosh, нажмите COMMAND+RETURN.
Формула должна быть разделена на длину строки текста, поскольку сумма длины символа диапазона уменьшается кратно каждому вхождению текстовой строки. Эта формула может заменить все более поздние формулы в этой статье, кроме формулы для подсчета количества слов в ячейке.
Пример 1. Подсчет количества вхождений текстовой строки в диапазоне
Запустите Excel, а затем откройте новую книгу
Введите следующие сведения на Листе 1:
Значение ячейки A8 составляет 4, потому что текст "яблоко" появляется четыре раза в диапазоне.
Формула для подсчета количества происшествий одного символа в одной ячейке
=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,"a",""))
Где cell_ref ссылка на ячейку, и "а" заменяется символом, который вы хотите посчитать.
Эта формула не должна быть введена в качестве массивной формулы.
Пример 2. Подсчет количества появлений символа в одной ячейке.
Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество появлений символа "p" в A7. Введите следующую формулу в ячейку A9:
Значение ячейки A9 составляет 3, потому что символ "p" появляется три раза в A7.
Формула для подсчета количества появлений одного символа в диапазоне
=SUM(LEN(range)-LEN(SUBSTITUTE(range,"a","")))
Где range - это диапазон нужной ячейки, а "а" заменяется символом, который вы хотите сосчитать.
Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.
Пример 3. Подсчет количества появлений символа в диапазоне
Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество символа "p" в A2:A7. В ячейку А10 введите следующую формулу:
Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.
Значение ячейки A10 составляет 11, потому что символ "p" появляется 11 раз в A2:A7.
Формула для подсчета количества слов, разделенных символом в ячейке
=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,""))+1)
Где cell_ref – это ссылка на ячейку, а char – это символ, разделяющий слова.
В приведенной выше формуле нет пробелов; несколько строк используются только для того, чтобы вписать формулу в этот документ. Не добавляйте пробелы при вводе формулы в ячейку. Эта формула не должна быть введена в качестве массивной формулы.
Пример 4. Подсчет количества слов, разделенных пространством в ячейке
Чтобы подсчитать количество слов в ячейке, где слова разделены пробелом, выполните следующие действия:
Запустите Excel, а затем откройте новую книгу
Введите следующие сведения на Листе 1:
Формула в ячейке A2 возвращает значение 4, чтобы отразить, что строка содержит четыре слова, разделенных пробелами. Если слова разделены несколькими пробелами или если слова начинаются или заканчиваются пробелами, это не имеет значения. Функция TRIM удаляет дополнительные пробелы и пробелы в начале и в конце в тексте ячейки.
В Excel можно также использовать макрос для подсчета количества случаев появления определенного символа в ячейке или диапазоне ячеек.
Ссылки
Дополнительные сведения о подсчете количества символов см. в следующей статье базы знаний Майкрософт:
89794 Как использовать Visual Basic для приложений для подсчета случаев появления символа в выделении в Excel
Подсчет ячеек в Excel, используя функции СЧЕТ и СЧЕТЕСЛИ
и удалять дублирующие Формула содержит функцию заказ, могут возникнуть. ___________ . короткую и элегантную чём сейчас? Функция СЧЁТЕСЛИМН проверяетАдминистраторГрачев подсчитывает ячейки, значенияНапример, чтобы подсчитать количество диапазоне, то можно пустые или заполненныеПервые ячейки остались видны,В ячейке G5 в данными написать слово данные, но и =СЧЁТЕСЛИ(). Эту функцию определенные проблемы дляНужна формула, которая формулу массива:Michael_S соответствие обоим условиям.245225 000 в которых начинаются ячеек, содержащих текст,
воспользоваться статистической функцией ячейки, содержащие только а последующие повторы пишем такую формулу.Excel. «Нет». работать с ними так же можно фирмы. Ниже рассмотрим посчитает все sweetНе забудьте ввести ее
: Вопрос действительно общий,megavlom4Нагайчук с буквы который начинается с
СЧЕТЗ числовые значения, а не видны. При
=ЕСЛИ(СЧЁТЕСЛИ(A$5:A$10;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1) Копируем поУстановим формулу вВ столбце А – посчитать дубли использовать при поиске
изменении данных в столбцу. Получился счетчик столбце D, чтобы устанавливаем фильтр. Как перед удалением, обозначить одинаковых значений в форматирования.
СЧЕТЕСЛИ()
P.S. для подсчета т.е. нажать после него - это одинаковые цифровые значения28010Формулаили с буквыН содержащие текст, числовые их содержимое должно
первом столбце меняются повторов. автоматически писались слова. установить и работать
дубли словами, числами, диапазоне ячеек. ВЧтобы избежать дублированных заказов, количество sweet использую ввода формулы не примерно глава книги в разных диапазонах,(данные3
ОписаниеК(без учета регистра), значения, дату, время, отвечать определенным критериям. и пустые ячейки,Изменим данные в столбце Формула такая. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"Да";"Нет") с фильтром, смотрите знаками, найти повторяющиеся функции первым аргументом можно использовать условное
такую формулу =COUNTIF(B1:B400, Enter, а сочетание об Excel. одного диапазона сИТРезультат: можно воспользоваться следующей
а также логические В этом уроке в зависимости от А для проверки.Копируем формулу по
в статье «Фильтр строки, состоящие из указан просматриваемый диапазон форматирование, которое поможет "sweet"). Но как Ctrl+Shift+Enter.megavlom данными другого).Искал на29546=СЧЁТЕСЛИ(A2:A7,"Грачев")
Функции Excel для подсчета формулой: значения ИСТИНА или мы подробно разберем того, где находятся Получилось так. столбцу. Получится так. в Excel». В нескольких ячеек, т.д. данных. Во втором
быстро найти одинаковые теперь узнать, сколькоТехнически, эта формула пробегает: Здравствуйте.Формулы,подсказанные Вами работают,но
форуме по аналогии-не4Количество вхождений фамилии Грачев данных очень полезныЕсли необходимо подсчитать количество ЛОЖЬ. две основные функции дубли.Ещё один способ подсчетаОбратите внимание ячейке B2 пишем Как выделить цветом аргументе мы указываем значения в столбце из этих sweet по всем ячейкам файл имеет большой получается.Продажи (3)
и могут пригодиться ячеек, которые содержатРешить обратную задачу, т.е. Excel для подсчетаПятый способ. дублей описан в, что такое выделение слово «Нет». одинаковые значения в что мы ищем. Excel. есть apple ? массива и вычисляет объём.Возможен ли другойMichael_S316343 практически в любой ровно четыре символа, подсчитать количество пустых данных –Как найти повторяющиеся строки
статье "Как удалить
Подсчет количества вхождений значения
дублей, выделяет словомНажимаем два раза левой Excel условным форматированием, Первый аргумент уПример дневного журнала заказовЗаранее спасибо за для каждого элемента способ решения(кроме макросов): Вроде не первый3
Как подсчитать, сколько раз символ / слово появляется в ячейке?
В этой статье я расскажу о том, как подсчитать, сколько раз конкретный символ или слово появляется в ячейке на листе. Например, A2 содержит текст «Kutools for Outlook и Kutools for word», и теперь я хочу знать номер буквы «o» в этой ячейке. В Excel мы можем применять формулы для решения этой задачи.
Подсчитайте, сколько раз в формулах встречается символ или слово
Чтобы подсчитать количество вхождений слова или символа в ячейку, вы можете использовать формулу, которая сочетает в себе функции ЗАМЕЩЕНИЕ и ДЛИТЕЛЬНОСТЬ Пожалуйста, сделайте следующее:
Введите приведенную ниже формулу в пустую ячейку и нажмите Enter чтобы узнать, сколько раз персонаж появляется, см. снимок экрана:
Ноты:
1. В приведенной выше формуле A2 текстовая ячейка, которую вы хотите посчитать, слово «удаление»- указанный символ, количество вхождений которого вы хотите подсчитать.
2. С помощью приведенной выше формулы вы также можете подсчитать количество определенных символов, отображаемых в ячейке, вам просто нужно изменить слово "удаление"к персонажу, как вы хотите, см. следующий снимок экрана:
3. Эта формула чувствительна к регистру.
4. Чтобы подсчитать количество определенных слов или символов без учета регистра, примените следующую формулу:
Подсчитайте, сколько раз встречается символ или слово с помощью простой функции
Если у вас есть Kutools for Excel, вы можете использовать его Подсчитайте количество слова of Часто используемые формулы для подсчета времени появления определенного символа или слова.
Примечание: Чтобы применить Подсчитайте количество слова функции, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените функции.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Щелкните ячейку, в которой вы хотите вывести результат.
2. Затем нажмите Кутулс > Формула Помощник > Статистический > Подсчитайте количество слова, см. снимок экрана:
3. И в выскочившем Помощник по формулам диалоговом окне выберите ячейку, в которой вы хотите выделить определенное слово или символ из Текст поле, а затем введите слово или символ, который вы хотите посчитать в поле Word текстовое поле, см. снимок экрана:
4. Затем нажмите OK чтобы закрыть диалог, и вы получите нужный результат.
Читайте также: