Подсчет частоты разделенных запятыми тегов в Excel

Question

У меня есть таблица Excel со списком семинаров и классов, в которых учащиеся проходили обучение, которые (при сокращении) выглядят так:

(A)___ClassName________|(B)_________Tags____________|
Astrobiology           | astro, bio, sci            |
Extremophiles          | chem, bio, sci             |
Human Space Habitation | astro, bio, med, engi, sci |  etc.

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

__Tag__|_Frequency_|
astro  |     2     |
bio    |     3     |
sci    |     3     |
chem   |     1     |  etc.

Я пытался сделать это только с помощью функций, и я могу получить уникальный список строк тегов (например, "astro, bio, sci"), используя

{=INDEX(User1!Tags,MATCH(0,COUNTIF($A$1:A1,User1!Tags),0))}

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

Это возможно?

Answer 1

Я не совсем уверен, что это то, что вы спрашиваете, но я попробую. Сделайте разные столбцы рядом со столбцом B, чтобы каждый столбец назывался "astro" и "bio" .... И используйте эту формулу ниже каждого столбца = FIND($ C $ 1; astro) ex, если в тексте есть слово "astro", оно будет показывать число, если не ошибка, и в конце каждого столбца используйте = COUNT() покажет вам время, когда Astro распилен в столбце B.

Надеюсь, это помогло!

Answer 2

Скопируйте столбец тегов на новый лист, а затем:

1. Домой> Редактирование -Найдите и выберите, Замените [пробел] ('один' символ '), Заменить все, ОК, Закрыть.
2. Данные> Инструменты данных - Текст в столбцы, выберите Разграничить, Далее, выберите Запятая, Готово.
3. Вставьте две новые колонки, скажем, A & B.
4. Создайте свой список из 47 уникальных (сверху или вручную) - скажем, во вновь вставленном столбце A, начиная с Row1.
5. = COUNTIF (диапазон, A1) в B1 и, при необходимости, скопируйте его, где range - это массив, содержащий все отдельные записи тега (вероятно, начиная с C1), и определяется с фиксированными ссылками (т. Е. Со знаками $)
6. Выберите весь лист, скопируйте / вставьте специальные / значения и удалите столбцы C и вправо, если необходимо.
7. Сохранить.

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

Answer 3

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

Приведенный ниже код перебирает ячейку B2:B25 (это можно отредактировать или изменить на параметр или выбранные ячейки, но пока это кажется самым простым). Он использует пользовательский класс с именем KeyValue для агрегирования имени тега и количества вхождений. Это можно улучшить с помощью объекта Dictionary, но для этого требуются другие надстройки. Он отделяет запятые теги от каждой ячейки и подсчитывает частоту. Затем он выводит этот список в первые два столбца на втором листе.

Чтобы добавить код, вы должны сделать следующее. Сначала в Рабочей книге необходимо включить панель инструментов разработчика, а затем нажать кнопку Visual Basic . Затем добавьте новый модуль класса и назовите его KeyValue . Вставьте следующий код:

Public Key As String
Public Value As Integer

Public Sub Init(k As String, v As Integer)
    Key = k
    Value = v
End Sub

Затем на листе 1 добавьте следующий код:

Public Sub CountTags()

    Dim kv As KeyValue
    Dim count As Integer
    Dim tag As String
    Dim tags As New Collection
    Dim splitTags As Variant

    For Each Cell In Sheet1.Range("B2:B25")
        ' Split the comma separated list and process each tag
        splitTags = Split(Cell.Value, ", ")
        For tagIndex = LBound(splitTags) To UBound(splitTags)
            tag = splitTags(tagIndex)

            ' If tag is in collection get new count otherwise start at 1.
            If Contains(tags, tag) Then
                Set kv = tags(tag)
                count = kv.Value + 1
                tags.Remove tag
            Else
                count = 1
            End If

            ' Add tag to the collection with its count.
            Set kv = New KeyValue
            kv.Init tag, count
            tags.Add kv, tag
        Next
    Next Cell

    Dim rowIndex As Integer
    rowIndex = 1

    For Each pair In tags
        Set kv = pair
        Sheet2.Cells(rowIndex, 1) = kv.Key
        Sheet2.Cells(rowIndex, 2) = kv.Value
        rowIndex = rowIndex + 1
    Next pair

End Sub

Private Function Contains(col As Collection, Key As Variant) As Boolean
    Dim obj As Variant
    On Error GoTo err
    Contains = True
    Set obj = col(Key)
    Exit Function
err:
    Contains = False
End Function

Нажмите кнопку «Выполнить», чтобы подсчитать теги.