Недавно коллега спросил, как подсчитать число вхождений подстроки (определенного символа) в текст, размещенный в одной ячейке? Я давно знал, что в Excel нет такой функции. И вот вчера написал небольшую пользовательскую функцию КолСимв. [1] Вот ее код:
Function КолСимв(строка As String, символ As String) As Integer
Application.Volatile True
Dim TestPos As Integer
КолСимв = 0
TestPos = 1
Do While InStr(TestPos, строка, символ) > 0
КолСимв = КолСимв + 1
TestPos = InStr(TestPos, строка, символ) + Len(символ)
Loop
End Function
Мастер функции КолСимв предлагает выбрать, «строку» – в чем ищем, и «символ» – что ищем (рис. 1). При этом «символ» может содержать и несколько символов (подстроку).
Рис. 1. Мастер функции КолСимв, подсчитывающей число вхождений символа (подстроки) в текст
Скачать заметку в формате Word, пример в формате архива zip (внутри архива вы найдете Excel файл с макросом; политика провайдера не позволяет мне загрузить файл Excel, содержащий код VBA)
Ниже я расскажу подробнее о функции и о том, как работает код VBA.
Гордый собою, я решил обратиться к Интернету, чтобы сравнить свое детище с другими идеями. И… наткнулся на решение без привлечения VBA кода!
Вот что пишет Пол Мак-Федрис в книге «Формулы и функции в Microsoft Office Excel 2007». В разделе, посвященном текстовым функциям, в примечаниях к описанию функции ПОДСТАВИТЬ:
Одним из неожиданных способов использования функции ПОДСТАВИТЬ() является подсчет количества вхождений определенного символа в заданной строке. Идея состоит в том, что при удалении определенного символа из строки разница в длине между исходной строкой и полученной строкой в точности равна количеству вхождений данного символа в исходной строке. Например, строка «издержки» состоит из 8 символов. Если удалить из строки все символы «и», то длина оставшейся строки будет 6 символов. Разница между длиной исходной и длиной полученной строки составляет 2 – именно столько раз символ «и» встречался в исходной строке. Чтобы выполнить такую операцию в формуле, необходимо использовать функцию ДЛСТР() и вычесть длину строки после удаления символа из длины исходной строки:
=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"и";""))
Джон Уокенбах в книге «Excel 2007. Библия пользователя» развивает эту идею, приводя формулу, способную подсчитать число вхождений подстрок в текст:
Формула представленная ниже подсчитывает количество вхождении подстроки, состоящей из нескольких символов (но, и один не возбраняется, прим. мое) в заданную текстовую строку. Пусть искомая подстрока находится в ячейке B1, а просматриваемый текст — в ячейке A2:
=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;B1;"")))/ДЛСТР(B1)
Для любознательных и начинающих VBA-программистов (коим, в сущности, являюсь и я) поясню, как работает код пользовательской функции КолСимв.
Function КолСимв(строка As String, символ As String) As Integer
Функция передает из Excel в VBA две текстовые строки: строка и символ. Значение функции является целым.
Dim TestPos As Integer
КолСимв = 0
TestPos = 1
Задается переменная TestPos – позиция, с которой будет искаться символ (подстрока) в строке, а также начальные значения самой функции КолСимв = 0 и переменной TestPos = 1.
Do While InStr(TestPos, строка, символ) > 0
КолСимв = КолСимв + 1
TestPos = InStr(TestPos, строка, символ) + Len(символ)
Loop
Цикл, выполняется до тех пор, пока значение InStr больше нуля. Функция VBA InStr – аналог Excel`евской НАЙТИ. Они обе возвращают позицию в строке, относительно начальной, с которой начинается искомый символ. Значение InStr будет больше нуля, пока символ будет входить в строку. Как только символ не будет найден в строке, функция InStr выдаст значение ошибки, и цикл прекратится.
КолСимв = КолСимв + 1
После каждого попадания внутрь цикла значение КолСимв будет увеличиваться на единицу; если ни одного символа не будет найдено в строке, КолСимв так и останется равным нулю.
TestPos = InStr(TestPos, строка, символ) + Len(символ)
Также внутри цикла происходит увеличение начальной позиции поиска для следующего цикла. Напомню, что InStr возвращает номер позиции. Затем это значение TestPos увеличивается на длину символа (единицу, если мы ищем вхождение единичного символа). В следующем цикле символ будет искаться с позиции сразу после окончания вхождения всего предыдущего символа в строку (рис. 2а).
Рис. 2. Принципы поиска символа «апа» в строке: (а) начальная позиция следующего поиска отстоит от начальной позиции предыдущего найденного вхождения на длину символа; (б) начальная позиция следующего поиска отстоит от начальной позиции предыдущего найденного вхождения на единицу
Представленный код функции КолСимв найдет 5 вхождений символа «апа» в строку «апапапапапапапапапапап». То есть, следующее вхождение будет искаться только после окончания предыдущего вхождения.
Если строку кода
TestPos = InStr(TestPos, строка, символ) + Len(символ)
заменить на
TestPos = InStr(TestPos, строка, символ) + 1
то функция КолСимв найдет 10 вхождений символа «апа» в строку «апапапапапапапапапапап», как показано на рис. 2б.
Обратите также внимание, что функция КолСимв чувствительна к языку (см. рис. 1): русский символ «а» в англоязычной строке «afff» не находится…
[1] Если вы никогда не создавали пользовательских функций, вам сюда. Не пугайтесь, это совсем несложно! 🙂
=(ДЛСТР(A169)-ДЛСТР(ПОДСТАВИТЬ(A169;
"@";"")))/ДЛСТР("@")Спасибо за идею с использованием функции «Подставить». Я давно программирую на VBA, и тут же написал по этой идее функцию
Function КоличествоВхождений(Str1, substr1)
КоличествоВхождений = (Len(Str1) — Len(Replace(Str1, substr1,
""))) / Len(substr1)End Function
Мне как раз такая функция понадобилась, а цикл писать лень было.
Function КоличествоВхождений(Str1, substr1)
КоличествоВхождений = UBound(Split(Str1, substr1)) — 1
End Function
Добрый день!
Однако функция не работает, если подряд идут более 3 одинаковых символов (считает неправильно)
В
КоличествоВхождений = UBound(Split(Str1, substr1)) — 1
— 1 лишнее