Посчитать в excel сколько раз встречается слово в
Добрый день, уважаемый читатель!
Эту статью я хочу посвятить повторам тех значений, которые встречаются в вашей таблице, то есть мы научимся как подсчитывать повторения в Excel. Данная возможность будет полезной при подсчёте одинаковых значений в необходимом диапазоне, это поможет когда с большого массива вам нужно сгруппировать, например, сколько сотрудников сделало чеков, сколько раз работали с тем или иным поставщиком и многое другое.
Для начала рассмотрим, как выглядят столбики с данными, повторения в которых, мы собственно и будем считать. К примеру, возьмём список сотрудников, которые совершают продажи. Теперь можно посчитать, сколько раз сотрудник производил продажи, то есть попросту считаем сколько повторений его фамилии в столбике. Это возможно произвести несколькими способами:
Используя функцию СЧЁТЕСЛИ
В Excel произвести такой подсчёт чрезвычайно просто, достаточно задействовать функцию СЧЁТЕСЛИ и она за несколько секундок всё за вас сделает. В нашем случае формула будет следующего вида:
=СЧЁТЕСЛИ($B$2:$B$11;B15) В первом аргументе «диапазон» $B$2:$B$11, указываем тот диапазон ячеек, в котором и будет производиться подсчёт повторяющихся данных. Важно! Указывать случайный диапазон данных недопустимо. Его особенностью является то, что он может быть только диапазоном ячеек или ссылкой на определённую ячейку.
Вторым аргументом «критерий» ставим указание на ячейку, по которому будет производиться подсчёт аналогичных данных. Если он один вы можете его прописать вручную как текстовое слово и вместо адреса ячейки «B15» указать «Нагаев А.В.», результат будет аналогичным, но только в одном конкретном случае, возможность автоматизации таблицы тогда значительно сократится.
Дополнительная информация! Кроме прямого указания поиска данных, функция СЧЁТЕСЛИ умеет работать с символами подстановки. Таких знаков используют двух видов «?» и «*», применять их возможно только при работе с текстовыми символами. Знак «*» позволяет заменить абсолютно любое количество значений, а Знак «?» производит замену только одного символа.
Для работы с числовыми значениями необходимо применять знаки операторов сравнения: «>», «<», «<>» и «=». К примеру, для подсчёта числовых значений больше «нуля» прописывайте «>0», а для подсчёта непустых ячеек нужен указать «<>».
Используем функцию СЧЁТЕСЛИМН
Когда у вас возникает необходимость подсчитывать повторения в Excel, но уже по нескольким критериям, тогда нужно работать с функцией СЧЁТЕСЛИМН, которая легко и просто сможет это выполнить.
В моём примере я добавлю категорию продаж по городам и используя формулы соберу необходимые мне повторы:
=СЧЁТЕСЛИМН($B$2:$B$11;B14;$C$2:$C$11;C14) Замечу, что орфография функции абсолютно аналогична предыдущей функции СЧЁТЕСЛИ, разница состоит только в их количестве. В нашем примере их два, но функция умеет работать и со 127 диапазонами.
Работаем с функцией ДЛСТР
Теперь давайте рассмотрим ситуацию, когда не всё так просто и упорядоченно, когда информация сбита в одну ячейку, к примеру, «Нагаев Гаврош Каропачёв Козубенко Нагаев Гаврош Козубенко Нагаев Нагаев». В этом случае статистические функции нам не помогут, необходимо считать символы и сверять повторы значений с указанным эталоном. Для этих целей есть много иных полезных функций, используя которые это можно провернуть достаточно просто:
=(ДЛСТР($B$2) -ДЛСТР(ПОДСТАВИТЬ($B$2;B5;"")))/ДЛСТР(B5) Итак, используя функцию ДЛСТР, мы считаем, сколько же символов содержится в ячейке «$B$2» и «B5», результат будет «71». А потом с помощью функции ПОДСТАВИТЬ производим замену текущего значения на «пусто», получаем результат «47». Следующим действием отнимаем от общего количества символов наш остаток «71-47=24» и делим на количество символов в одном значении «24/6=4», как результат получаем сколько раз в строке, встречается необходимый результат… Ответ: 4. (Это результат рассматривая только первую строку поиска).
С помощью функций VBA
Последний рассматриваемый вариант, это подсчёт количества повторений с помощью, созданной в VBA функции. Функции я не писал, а просто предлагаю вам найденный ранее вариант, чтобы упростить вашу работу.
Для начала вам нужно запустить редактор макросов VBA и вставить новый модуль с помощью команд «Insert» — «Module». В созданное окно модуля вы вставляете код функции:
Когда вы работаете с листом Excel, вы когда-нибудь пытались подсчитать, сколько раз слово появляется в диапазоне ячеек? Подсчет их по одной займет много времени, если нужно подсчитать несколько ячеек, сегодня я расскажу о некоторых хороших приемах, которые помогут вам решить эту задачу в Excel.
Подсчитайте, сколько раз конкретное слово появляется в диапазоне с помощью формулы
Следующая формула поможет вам подсчитать, сколько раз конкретное слово появляется в ячейке или диапазоне. Пожалуйста, сделайте следующее:
1. Щелкните ячейку, в которой вы хотите получить результат, например C2, а затем скопируйте и вставьте приведенную ниже формулу, затем нажмите Enter ключ для получения результата, см. снимок экрана:
=SUMPRODUCT((LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,"KTE","")))/LEN("KTE"))
Ноты:
1. В приведенной выше формуле A2: A5 это данные, из которых вы хотите посчитать конкретное слово, и КТЭ это слово, которое вы хотите посчитать. Вы можете менять их по своему усмотрению.
2. Эта формула чувствительна к регистру. Если вы хотите узнать количество раз, когда определенное слово появляется без учета регистра, примените следующую формулу:
=SUMPRODUCT((LEN(A2:A5)-LEN(SUBSTITUTE((UPPER(A2:A5)),UPPER("KTE"),"")))/LEN("KTE"))
Подсчитайте, сколько раз конкретное слово появляется в диапазоне с помощью полезной функции
С помощью Подсчитайте количество слова особенность Kutools for Excel, вы можете быстро рассчитать количество определенного текста или слова в диапазоне ячеек, не запоминая никаких формул.
Примечание: Чтобы применить это Подсчитайте количество слова, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.После установки Kutools for Excel, пожалуйста, сделайте так:
1. Щелкните ячейку, в которой вы хотите разместить результат. А затем нажмите Kutools > Формула Помощник > Формула Помощник, см. снимок экрана:
2. В Формула Помощник диалоговом окне выполните следующие действия:
- Выберите Статистический из файла Тип формулы раскрывающийся список;
- Затем выберите Подсчитайте количество слова из Выберите формулу список;
- В правой Ввод аргументов раздел, выберите список ячеек, которые вы хотите подсчитать, сколько раз появляется определенное слово из Текст поле, а затем введите конкретное слово, которое вы хотите подсчитать, в поле Word пунктом.
3, Затем нажмите Ok кнопку, и вы увидите, сколько раз конкретное слово появляется в диапазоне ячеек, см. screesnhot:
| Раздел функций | Текстовые |
| Название на английском | LEN |
| Волатильность | Не волатильная |
| Похожие функции | СЧЁТЗ |
Что делает функция ДЛСТР?
Эта функция подсчитывает количество символов, включая пробелы и цифры, в фрагменте текста.
Есть символ, который функция ДЛСТР не учитывает. Это символ апострофа (‘), когда он в начале ячейки. Если в начале ячейки идут два символа апострофа подряд, второй уже учитывается.
Почему так происходит? Дело в том, что апостроф в начале ячейки автоматически воспринимается Excel как служебный символ, переводящий формат ячейки в текстовый.
Синтаксис
Аргумент у функции один, он обязательный. В нем можно указывать как одну ячейку, так и другие вычисления с помощью формул. При использовании в функциях массива аргументом может быть диапазон ячеек.
Форматирование
Если входящее значение не в текстовом формате, оно конвертируется в текстовый формат.
Ячейки в формате даты воспринимаются как числа, поэтому содержат обычно 5 знаков (если это не даты, близкие к 1900 году).
Процентный формат также конвертируется в натуральное число, например, 2500% = 25 (2 символа, см. таблицу примеров выше).
Примеры применения ДЛСТР
Функция часто используется как вспомогательная в составных формулах, в комбинации с другими текстовыми функциями.
При этом эти комбинации позволяют решать самые необычные задачи.
Пример 1 — посчитать слова
Сколько слов в ячейке? Обычно на 1 больше, чем пробелов между ними. Если пробел один, значит слов — два, если два — то слов три, и так далее.
Если есть вероятность, что пробелов больше 1 между словами, или они есть в начале и конце ячейки, их можно устранить функцией СЖПРОБЕЛЫ.
Пробелы в строке можно посчитать, измерив длину строки в символах:
- с ними,
- без них,
- и вычислив разницу.
Как получить строку без пробелов? Тут поможет функция ПОДСТАВИТЬ.
Так будет выглядеть формула для ячейки A1, учитывающая все эти нюансы. СЖПРОБЕЛЫ удаляет лишние пробелы, ПОДСТАВИТЬ удаляет их, а ДЛСТР измеряет длину строк:
Пример 2 — посчитать встречаемость фрагмента в тексте
Есть ли в тексте определенный символ? И если есть, то сколько их?
Рассмотрим пару примеров формул.
Есть ли в ячейке лишние пробелы
Формула ниже проверит, есть ли в ячейке лишние пробелы. Это удобно, когда нужно не просто удалить их, а узнать, были ли они вообще:
Формула очевидна и довольно проста. Если длина ячейки после удаления лишних пробелов функцией СЖПРОБЕЛЫ равна исходной — значит, их в ней нет, а если наоборот, не равна — есть.
Обратите внимание, что, несмотря на то, что в самой постановке вопроса звучит условие ЕСЛИ, функция ЕСЛИ тут не нужна. Если равенство верное, Excel сам вставит в ячейку результат ИСТИНА, и ЛОЖЬ, если наоборот.
Сколько раз встречается символ или слово в тексте
По некой аналогии с предыдущим примером, мы можем производить не булевое (ДА-НЕТ, ИСТИНА-ЛОЖЬ) сравнение длин строки перед и после удаления символа или фрагмента, а вычислить разницу.
Если мы ищем один символ, это позволит сразу понять его встречаемость.
Для удаления произвольного символа из строки нам понадобится функция ПОДСТАВИТЬ.
Подсчет встречаемости буквы в ячейке
Если же текстовый фрагмент длиной 2 и более символов — нам потребуется еще и поделить полученный результат на его длину:
В данной формуле исходный текст в ячейке A1, а подсчитываемый фрагмент — в ячейке B1.
Обратите внимание на 5 и 6 строки — поскольку функция «ПОДСТАВИТЬ» чувствительна к регистру, из текста ею не было удалено первое вхождение. Если нужно учитывать вхождения в любом регистре, могут понадобиться функции СТРОЧН или ПРОПИСН.
Подсчитываемый текстовый фрагмент может быть любой длины.
Еще момент — функция может быть непригодна для подсчета коротких слов, т.к. одно слово может быть частью другого.
Пример 3 — извлечь или удалить последнее слово
Зная количество пробелов в тексте, можно заменить последний из них на редкий символ, чтобы потом вычислить его позицию.
С этим нам, как и в предыдущем примере, поможет функция ПОДСТАВИТЬ — она позволяет заменить не только все определенные текстовые фрагменты в строке, но и определенное по порядку вхождение (первое, второе и т.д.).
Что это нам дает? Последний пробел — не что иное, как символ, отделяющий последнее слово от остальной части строки. И по его позиции далее уже нехитрыми манипуляциями удалить или извлечь его.
Подробно пример рассмотрен здесь: Удалить последнее слово в Excel
Пример 4 — удалить первые N символов / первое слово
Нехитрая комбинация функций ПРАВСИМВ, ПОИСК и ДЛСТР позволяет удалить из ячейки первое слово.
- вычисляем позицию первого пробела функцией ПОИСК,
- вычитаем из длины строки (ДЛСТР) это число
- Извлекаем функцией ПРАВСИМВ полученную разницу из исходной строки
Подробнее пример рассмотрен в статье про функцию ПРАВСИМВ.
Если же количество символов уже известно заранее, формула еще проще — нужны только ДЛСТР и ПРАВСИМВ:
Пример 5 — замена последнего символа в ячейке
Как бы ни звучало очевидно, позиция последнего символа в строке равна ее длине в символах. А поэтому, зная длину строки, можно заменить последний символ в ней, взяв на 1 символ меньше с помощью ЛЕВСИМВ, и конкатенации с нужным текстом через амперсанд:
Или с помощью функции ЗАМЕНИТЬ, которая на вход возьмет длину строки как позицию заменяемого символа:
Пример 6 — в формуле массива
Как посчитать разом все символы во всех ячейках столбца или диапазона?
Здесь поможет формула массива на основе комбинации функции ДЛСТР и функции СУММ. Первая создаст массив значений длины каждой из ячеек диапазона, а вторая просуммирует эти значения:
Функция ДЛСТР в формуле массива
Аналогичным способом функциями МАКС и МИН можно посчитать длины самой длинной и самой короткой строк в диапазоне.
Допустим у нас есть таблица регистра составленных заказов клиентов. Необходимо узнать с какого города поступило наибольшее количество заказов, а с какого – наименьшее. Для решения данной задачи будем использовать формулу с поисковыми и вычислительными функциями.
Поиск наиболее повторяющегося значения в Excel
Чтобы наглядно продемонстрировать работу формулы для примера воспользуемся такой схематической таблицей регистра заказов от клиентов:
Теперь выполним простой анализ наиболее часто и редко повторяющихся значений таблицы в столбце «Город». Для этого:
- Сначала находим наиболее часто повторяющиеся названия городов. В ячейку E2 введите следующую формулу:
- Обязательно после ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как ее нужно выполнить в массиве.
- Для вычисления наиболее редко повторяющегося названия города вводим весьма похожую формулу:
Результат поиска названий самых популярных и самых редких городов клиентов в регистре заказов, отображен на рисунке:
Если таблица содержит одинаковое количество двух самых часто повторяемых городов или два самых редко повторяющихся города в одном и том же столбце, тогда будет отображаться первый из них.
Принцип действия поиска популярных по повторению значений:
Если посмотреть на синтаксис формул то можно легко заметить, что они отличаются только одним из названием функций: =МАКС() и =МИН(). Все остальные аргументы формулы – идентичны. Функция =СЧЕТЕСЛИ() подсчитывает, сколько раз каждое название города повторяется в диапазоне ячеек C2:C16. Таким образом в памяти создается условный массив значений.
Функция МАКС или МИН выбирает из условного массива наибольшее или наименьшее значение. Функция =ПОИСКПОЗ() возвращает номер позиции на которой в столбце C название города соответственного наибольшему или наименьшему количеству повторений. Полученное значение будет передано в качестве аргумента для функции =ИНДЕКС(), которая возвращает конечный результат в ячейку.
Читайте также: