Я изо всех сил пытался подсчитать, сколько раз несколько различных текстовых строк появляются в ячейке.
В столбце A меня есть текстовая строка с несколькими типами файлов, и я пытаюсь подсчитать, сколько файлов каждого типа встречается в этой строке.
Подсчитывает ли какая-либо формула количество раз, когда текстовая строка встречается в одной ячейке?
ожидаемый результат
Подсчитывает ли какая-либо формула количество раз, когда текстовая строка встречается в одной ячейке?
Не напрямую, но вы можете рассчитать это:
=(LEN(A1) - LEN(SUBSTITUTE(A1,"doc",""))) / LEN("doc")
Изменение формулы для поиска только целых слов усложнит стандартные формулы Excel.
Здесь я использую бесплатную надстройку Regex Find/Replace .
С этим вы можете использовать эту формулу:
=(LEN(A1)-LEN(RegExReplace(A1,"(\W?)doc(?!\w)","$1")))/LEN("doc")
Как это устроено:
(?!\w) является негативным взглядом, рассматривает только doc если за ним не следует буква(\W?) - так как эта надстройка не поддерживает внешний вид, здесь я использую групповую конструкцию, ища не-букву перед doc(примечание: мне нравится эта надстройка и никак не связанная с ней)
Для вашей конкретной ситуации решение по формуле является относительно простым.
Введите следующую формулу в B2 и ctrl-enter/copy-paste/fill-down/auto-fill в оставшуюся часть столбца таблицы:
=(LEN($A2&",")-LEN(SUBSTITUTE($A2&",","/doc,","")))/LEN("/doc,")
Повторите эту формулу для других столбцов, изменив doc на тип файла столбца. Тем не менее, для no. of text столбца, вам нужно использовать plain вместо этого на text
Объяснение:
Добавление запятой в конец строки в колонке A гарантирует , что подстрока мы ищем всегда ограничена с / префиксом и ,
В этом случае количество вхождений подстроки представляет собой просто разницу между длиной полной строки и полной строки с удаленными разделителями подстрок, разделенными на длину разделенной подстроки.
Также может быть построена немного более сложная формула, которая извлекает тип файла для подсчета из самого заголовка, поэтому для всей таблицы требуется только одна формула:
Введите следующую формулу в B2 и ctrl-enter/copy-paste/fill-down & right/auto-fill в остальной части таблицы:
=(LEN($A2)+1-LEN(SUBSTITUTE($A2&",","/"&MID(B$1,8,LEN(B$1))&",","")))/(LEN(MID(B$1,8,LEN(B$1)))+2)
Обратите внимание, что заголовок столбца H должен быть изменен на no. of plain для этой формулы для работы в соответствии с вашими требованиями.
Объяснение:
Эта формула работает практически так же, как и предыдущая.
Единственное существенное отличие состоит в том, что вместо жесткого кодирования типа файла для извлечения его из заголовка используется MID(B$1,8,LEN(B$1)) . По сути, это эквивалент функции MID() с двумя аргументами, которая извлекает подстроку из 8-го символа до конца строки. (LEN(B$1) используется вместо произвольно большого числа, так как это приводит к "чистому" решению без шансов.)
Другое, незначительное отличие в этой формуле - это небольшое упрощение, достигаемое жестким кодированием длин разделителей.