Подсчитать количество вхождений символа в строку Excel

Обновлено: 20.11.2024

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

Подсчет символов или слов максимально полезен с точки зрения дизайна; Сколько символов будет на визитной карточке, сколько слов будет в брошюре, сколько символов поместится в зависимости от размера объекта, шрифта, дизайна. Когда все это подсчитано и отсортировано, открывается хорошая перспектива!

Что касается конкретных встроенных символов, вы можете искать определенные продукты по коду продукта, текстовому символу, обозначающему характеристику, и т. д. Какой бы важной или бессмысленной ни была ваша причина, мы тем не менее узнаем, как считать символы в Excel, так что давайте начните считать!

Оглавление

Подсчитать количество символов

Мы начнем с подсчета количества символов в Excel. В качестве примера мы будем использовать внутренние сообщения в поздравительных открытках. В зависимости от размера и дизайна поздравительной открытки вам нужно будет посмотреть, какое сообщение может поместиться где. Следовательно, будет полезно посмотреть, сколько символов содержит сообщение. Ниже приведен пример листа:

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

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

Количество символов в ячейке можно легко подсчитать с помощью функции ДЛСТР. Функция ДЛСТР возвращает количество символов в текстовой строке. Посмотрим, что из этого выйдет:

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

А вот как выглядит примененная формула:

Функция ДЛСТР использовалась с одним аргументом; ссылка на ячейку для подсчета символов в тексте ячейки. LEN возвращает 76 как количество символов в C4. Это количество включает пробелы. Если вы хотите, чтобы ваш счет исключал пробелы, мы вам поможем. Прочитайте следующий сегмент.

Подсчет количества символов в ячейке без пробелов

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

Функция ПОДСТАВИТЬ заменяет символы пробела (" ") в C4 пустым текстом (""). Таким образом, текст, который будет подсчитан функцией ДЛСТР, будет Пусть этот день рождения будет началом года, наполненного счастливыми воспоминаниями. Функция ДЛСТР возвращает подсчитанные символы в качестве результата.

Подсчитать количество символов в диапазоне

Если нам нужно подсчитать количество символов в диапазоне, мы можем следовать чему-то вроде сложения отдельных сумм, например:

Но пока мы на этом, давайте подробнее изучим потенциал функции LEN, не так ли? Теперь мы покажем вам, как подсчитать количество символов в диапазоне, используя функции ДЛСТР и СУММПРОИЗВ, а также функции ДЛСТР и СУММ вместе.

LEN с СУММПРОИЗВ

Здесь мы объединим функции ДЛСТР и СУММПРОИЗВ, чтобы получить общее количество символов в диапазоне. Функция СУММПРОИЗВ вычисляет произведения диапазонов, а затем сумму произведений. Кроме того, мы используем функцию СУММПРОИЗВ для суммирования количества функций ДЛСТР, поскольку ДЛСТР не вычисляет массивы самостоятельно. У нас есть 2 формулы ниже; один считает символы в диапазоне, включая пробелы, другой исключает пробелы:

С пробелами:

Функция СУММПРОИЗВ помогает функции ДЛСТР работать как формула массива. Работа функции ДЛСТР заключается в вычислении общего количества символов в ячейках C4:C6, которое мы суммируем с помощью функции СУММПРОИЗВ.

Без пробелов:

Прежде чем функции ДЛСТР и СУММПРОИЗВ начнут выполнять свою работу, функция ПОДСТАВИТЬ используется для замены пробелов из C4:C6 пустыми символами. Текстовые строки в C4:C6 будут считаться без пробелов.

Затем функция ДЛСТР продолжает считать символы, а функция СУММПРОИЗВ суммирует значения и возвращает 203 в качестве результата.

ДЛСТР с СУММ

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

С пробелами:

После добавления формулы нажмите клавиши Ctrl + Shift + Enter, и вы увидите фигурные скобки, заключающие формулу в скобки. Это превращает его в формулу массива, чтобы функция ДЛСТР могла считать символы в C4:C6 как массив с помощью функции СУММ.

Без пробелов:

Чтобы исключить пробелы из подсчета, мы вложили функцию ПОДСТАВИТЬ, чтобы заменить все пробелы пустым текстом. Это оставит только буквы, цифры и символы для подсчета. Функция ДЛСТР подсчитывает символы каждой из ячеек в C4:C6, результаты добавляются функцией СУММ. В конце формула завершается нажатием клавиш Ctrl + Shift + Enter, чтобы работать как формула массива, и вот результаты:

Если вам интересно, что произойдет, если формулу не преобразовать в формулу массива, попробуйте посмотреть результат, не добавляя фигурные скобки. Формула вернет только количество одной ячейки; ячейка, к которой примыкает результат, несмотря на диапазон, присутствующий в формуле. Фигурные скобки превращают его в формулу массива, чтобы он мог принимать указанный диапазон.

Подсчет вхождений определенного символа

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

Подсчет вхождений определенного символа в ячейку

Например, мы ищем продукты в наборе данных, которые отмечены звездочкой (*). Отмеченные продукты будут отображаться в результатах как 1, а неотмеченные — как 0. Мы будем использовать функцию ДЛСТР для подсчета и функцию ПОДСТАВИТЬ для замены текста. Давайте поместим это в действие Excel. Формула будет такой:

Используя функцию ПОДСТАВИТЬ, мы заменяем звездочку в C3 пустым текстом и подсчитываем оставшиеся символы с помощью функции ДЛСТР. В результате получается 7 символов.

Все коды в нашем примере состоят из 7 символов, и ячейка, не содержащая звездочки, будет вычисляться как 7-7=0.

Все продукты, которые в результате формулы получают 1, являются отмеченными продуктами, а 0 – неотмеченными продуктами.

Подсчет вхождений определенного символа в диапазоне

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

Мы можем использовать приведенную выше формулу в нашем примере, чтобы узнать, сколько продуктов было отмечено звездочкой. В формуле все звездочки в диапазоне C3:C18 заменены пустым текстом с помощью функции ПОДСТАВИТЬ. Функция LEN подсчитывает все символы в C3:C18 (исключая звездочки), что дает 16 произведений, умноженных на 7-значный код, что равняется 112 символам.

В части функции LEN(C3:C18)-LEN(SUBSTITUTE(C3:C18,"*","")) число 112 вычитается из общего количества символов C3:C18. Если мы быстро проверим общее количество на листе, оно составит 122 символа. 122-112=10 — результат формулы.

Но при чем здесь функция СУММПРОИЗВ? СУММПРОИЗВ — это функция, которая упорядочивает результаты диапазона в формуле, а также работает для того, чтобы функция ДЛСТР работала как формула массива.

Поэтому в нашем наборе данных есть 10 отмеченных продуктов.

Обратный отсчет до характерного закрытия. Сегодня функция LEN стала тем другом, на которого мы можем рассчитывать (фактически считать с ним). Попробуйте использовать некоторые из этих методов в следующий раз, когда столкнетесь с чем-то столь же дотошным, как подсчет символов; никто не должен делать это в одиночку. И чтобы вы никогда не были так одиноки в своих приключениях с Excel, мы вернемся с еще одним аспектом для изучения.

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

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

Загрузите учебную тетрадь

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

5 методов подсчета вхождений символов в строку в Excel

1. Найдите общее количество вхождений символов в строку в Excel, используя функцию СУММПРОИЗВ и ДЛСТР

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

Шаги:

Здесь функция ДЛСТР возвращает количество символов в текстовой строке, а функция СУММПРОИЗВ возвращает сумму произведений соответствующего диапазона.

А вот общее количество символов в указанном диапазоне.

Примечание:

Функция ДЛСТР в Excel подсчитывает все символы в каждой ячейке, включая буквы, цифры, пробелы, символы, а также все пробелы, знаки препинания и т. д.

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

Шаги:

Разбивка формулы:

➤ ДЛСТР(B5)

Здесь функция ДЛСТР подсчитывает символы в ячейке B5.

➤ ПОДСТАВИТЬ(B5","a","")

Функция ПОДСТАВИТЬ заменяет все символы «а» пробелом («»).

➤ ДЛСТР(ПОДСТАВИТЬ(B5","a",""))

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

➤ ДЛСТР(B5)-ДЛСТР(ПОДСТАВИТЬ(B5,"a",""))

Наконец, эта формула вычитает две ранее рассчитанные длины и дает общее количество указанного нами символа «a».

В конце концов, вот количество ожидаемых символов:

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

В отличие от предыдущего метода, вам может потребоваться подсчитывать символы независимо от их чувствительности к регистру. В этом случае вы можете добавить функцию ВЕРХНЯЯ или НИЖНЯЯ к предыдущей формуле. Теперь мы будем искать как «А», так и «а» в нашем наборе данных названий книг. Вот шаги:

Шаги:

Здесь функция ПРОПИСНЫЕ преобразует текстовую строку во все буквы верхнего регистра. Здесь все «а» будут преобразованы в «А», и вы получите соответствующий счет. И остальная часть формулы работает так же, как описано в предыдущем примере.

Наконец, если формула введена правильно, вы получите следующий результат.

4. Подсчитайте количество вхождений одного символа в диапазоне в Excel

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

Шаги:

Здесь функция СУММ складывает все числа в диапазоне ячеек. Остальная часть формулы работает, как и раньше, и, наконец, возвращает общее количество символов «а» во всем диапазоне.

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

5. Подсчитайте количество вхождений строки символов (текста или подстроки) в диапазоне

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

Шаги:

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

Наконец, вот общее количество «зеленых» в указанном диапазоне.

Примечание:

Вышеупомянутая формула должна быть введена как формула массива. Чтобы ввести формулу в виде массива в Excel для Windows, нажмите клавиши CTRL+SHIFT+ВВОД, чтобы ввести формулу в виде массива.

Заключение

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

Счетчик с учетом регистра

Эта формула подсчитывает появление строчной буквы i в ячейке A2. Чтобы подсчитать заглавные буквы, просто введите букву в верхнем регистре.

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

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

(Если хотите, можете пропустить эту часть и просто скопировать/вставить приведенную выше формулу и изменить ссылки на ячейки, содержащие текст.)

Принцип этой формулы заключается в том, что вы подсчитываете количество символов в ячейке, а затем выполняете второй подсчет символов в ячейке после удаления символа, который хотите подсчитать, с помощью функции ПОДСТАВИТЬ; затем вы вычитаете второе число из первого, чтобы получить окончательный счет.

LEN(A2) подсчитывает количество символов в ячейке A2.

SUBSTITUTE(A2,"i","") заменяет каждый экземпляр буквы i ничем, пустым пространством "", и это эффективно удаляет i из текста. Как только это будет сделано, нам нужно подсчитать, сколько символов осталось после удаления всех i символов.

ДЛСТР(ПОДСТАВИТЬ(A2,"i","")) ДЛСТР(), которая окружает функцию ПОДСТАВИТЬ, выполняет второй подсчет.

После того, как оба подсчета выполнены, вы просто вычитаете количество без i из количества всех символов в ячейке, и вот как мы получаем окончательную формулу:

ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2,"i",""))

Счетчик без учета регистра

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

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

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

Это работает точно так же, как предыдущая формула с добавлением функции LOWER().

НИЖНИЙ(A3) эта функция переводит весь текст в нижний регистр. Это позволяет функции ПОДСТАВИТЬ() работать независимо от текста в нижнем/верхнем регистре.

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

Больше примеров

Считать верхний регистр

Считать в нижнем регистре

Подсчет пробелов

Примечания

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

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

Формула для подсчета вхождений слова в ячейке или диапазоне в Excel
Учебное пособие: Формула для подсчета количества вхождений слова в одной ячейке или во всем диапазоне в Excel.

Функция ПОДСТАВИТЬ() — изменение определенного текста в ячейке в Excel
Учебное пособие: изменение или замена текста в ячейке другим текстом — вы можете заменить один символ, число.

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

Формула для удаления первого или последнего слова из ячейки в Excel
Учебник : Формула Excel для удаления первого или последнего слова из ячейки. Вы можете скопировать и вставить fo.

Заглавная первая буква каждого слова в ячейке — функция PROPER
Учебник: В Excel вы можете использовать функцию, чтобы сделать первую букву каждого слова в ячейке заглавной. .

Подсчитайте количество ячеек, которые начинаются или заканчиваются определенным текстом в Excel
Учебник: Как подсчитать ячейки, которые соответствуют тексту в начале или конце строки в Excel. Если вы с.

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
200+ руководств по Excel

Вопрос: как подсчитать, сколько раз текстовая строка встречается в значении ячейки в Excel?

Ответ:

Формула в ячейке C6 подсчитывает, сколько раз заданная текстовая строка встречается в значении ячейки.

Формула в C6:

Пояснение формулы в ячейке C6

Шаг 1. Замените данную текстовую строку ничем

SUBSTITUTE("AA BB CC AA CC BB CC AA", "AA", "")

и возвращает "BB CC CC BB CC".

Шаг 2. Подсчет символов текстовой строки

LEN("ББ СС СС BB СС ")

Шаг 3. Подсчет символов текстовой строки в ячейке C2

LEN("AA BB CC AA CC BB CC AA")

Шаг 4. Вычтите исходную длину символов из новой длины символов текстовой строки

Шаг 5. Разделите на длину символа строки поиска

(ДЛСТР(C2)-ДЛСТР(ПОДСТАВИТЬ(С2, С4, "")))/ДЛСТР(C4)

и возвращает 3 в ячейке C6.

Получить файл Excel *.xlsx

2. Подсчет текстовой строки в диапазоне (с учетом регистра)

Вопрос:

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

Ответ:

Формула массива в ячейке B11:

Как создать формулу массива

Копировать формулу массива (Ctrl + C).
Выберите ячейку B11.
Вставьте формулу массива (Ctrl + V) в строку формул.
Нажмите и удерживайте клавиши Ctrl + Shift.
Нажмите клавишу ВВОД.
Отпустить все клавиши

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