☰

Префиксы и суффиксы Excel

tag-icon • tag-icon microsoft-excel worksheet-function
Префиксы и суффиксы Excel

У меня есть столбец в Excel, содержащий список, разделенный запятыми:

Header
1, 61
61
1, 61, 161
5, 55

Я хотел бы извлечь эти данные, чтобы можно было подсчитать количество случаев появления каждого элемента и получить следующие результаты:

Count of Items
1    |    2
5    |    1
55   |    1
61   |    3
161  |    1

Я попробовал countif с "*", но это беспорядок, потому что в этом случае у меня есть префиксы или суффиксы (1,61,161)

ПОЖАЛУЙСТА ПОМОГИ!

решение1

Опция 1:

Я хотел бы предложить UDF (пользовательскую функцию), которая не только подсчитывает количество чисел, разделенных запятыми, но и количество текста.

введите описание изображения здесь

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

  • НажиматьАльт+Ф11чтобы получить редактор VB тогдаКопировать&Вставитьэтот код какМодуль.

    Option Explicit

Function CountOccurrence(SearchRange As Range, Phrase As String) As Long

Dim RE As Object, MC As Object
Dim sPat As String
Dim V As Variant
Dim I As Long, J As Long

V = SearchRange

Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.MultiLine = True
.ignorecase = True
.Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
End With

For I = 1 To UBound(V, 1)
If RE.test(V(I, 1)) Then J = J + 1
Next I

CountOccurrence = J

End Function

  • Введите Критерии в Диапазон H18:H26, затем введите эту Формулу в Ячейку I18и заполните ее.

=CountOccurrence($G$18:$G$24,H18)

Вариант 2:

Введите эту формулу в ячейку I18и заполните ее.

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

При необходимости отрегулируйте ссылки на ячейки.

решение2

  1. Сначала вам нужно разбить ваши числа по отдельным ячейкам:
    • выберите ваши данные
    • на вкладке «Данные» выберите «Разделить по столбцам»
    • выберите "с разделителями", далее
    • выберите данные, разделенные "запятой", закончите
  2. теперь вы можете использовать функцию СЧЁТЕСЛИ, например
    =COUNTIF($A$2:$C$5,E2)

введите описание изображения здесь

решение3

Похоже, у вас уже есть решение, но я добавлю решение не на VBA, которое обрабатывает динамические данные. Оно использует некоторые вспомогательные столбцы, которые вы можете предварительно заполнить в произвольном большом диапазоне. Если нет связанных данных, ячейки будут пустыми. Некоторые вспомогательные столбцы можно исключить; они включены для минимизации повторений, но все вспомогательные столбцы можно скрыть.

введите описание изображения здесь

Ваши данные находятся в столбце A. Столбец B определяет количество значений в каждой записи на основе количества запятых. Формула в B3:

=IF(ISBLANK(A3),"",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)

Столбец C — это просто кумулятивное количество компонентов из столбца B. C2 вводится как 0. Формула в C3:

=IF(ISBLANK(A3),"",SUM(B3:B$3))

Заполните столбцы B и C таким количеством строк, для которых у вас когда-либо будут данные. Вы всегда можете расширить эти столбцы, если это необходимо.

Столбец E нужен только для удобства. Он предоставляет индекс для проанализированных значений. Вы можете жестко закодировать 1и затем добавлять к нему 1 для каждой последующей строки. Без всякой причины я основывал его на номере строки ( -2в формуле ниже это для корректировки значений, чтобы начать с 1). Ячейки за пределами общего числа значений получают пустые. Моя формула в E3:

=IF(ROW()-2>MAX($C$2:$C$10),"",ROW()-2)

Столбец F нужен только для того, чтобы избежать повторения формулы. Он извлекает соответствующую запись столбца A, из которой будет проанализирован текущий компонент. Формула в F3:

=IFERROR(OFFSET($A$2,MATCH(E3-1,$C$2:$C$10,1),0),"")

Он находит соответствующую запись, сравнивая номер компонента в столбце E с накопленным количеством компонентов в столбце C.

Столбец G — это проанализированные значения компонентов, все в одном смежном столбце, с которым легко работать. Формула в G3:

=IFERROR(TRIM(MID(SUBSTITUTE(F3,",",REPT(" ",LEN(F3))),(E3-INDEX($C$2:$C$10,MATCH(E3-1,$C$2:$C$10,1))-1)*LEN(F3)+1,LEN(F3))),"")

Это определяет, какой элемент следует анализировать из записи столбца F, путем вычитания совокупного количества элементов последней «завершенной» входной записи из текущего номера элемента.

Столбцы E–G следует распространить на достаточное количество строк, чтобы охватить ожидаемое количество значений компонентов (по крайней мере, в несколько раз большее, чем количество строк данных). Обратите внимание, что все приведенные выше формулы, которые относятся к диапазону $C$2:$C$10, следует скорректировать, чтобы включить полный диапазон ваших данных.

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

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

При изменении данных просто обновите сводную таблицу и проверьте, выбраны ли в фильтре все новые значения элементов.

Связанный контент