Zliczanie częstotliwości znaczników rozdzielanych przecinkami w programie Excel

Mam arkusz kalkulacyjny programu Excel z listą seminariów i zajęć, które uczniowie wzięli, i które (po skróceniu) wyglądają następująco:

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

Chciałbym móc wyodrębnić z tego poszczególne tagi i uzyskać powiązaną liczbę dla każdego tagu na innym arkuszu, na przykład dla powyższego:

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

Próbowałem to zrobić tylko za pomocą funkcji i mogę uzyskać unikalną listę ciągów znaków (np. „Astro, bio, sci”) za pomocą

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

ale nie udało mi się samodzielnie wyodrębnić tagów. Chciałbym zachować plik „ostrzeżenie o makrze” - jeśli to możliwe - bez nowych, ale jestem nowy, aby się wyróżniać, więc jeśli zamierzam to zrobić w niewłaściwy sposób, proszę dać mi znać!

czy to możliwe?

Nie jestem do końca pewien, czy o to pytasz, ale spróbuję. Utwórz różne kolumny w pobliżu kolumny B z każdą kolumną o nazwie „astro” „bio” .... I użyj tej formuły pod każdą kolumną = ZNAJDŹ ($ C $ 1; astro) np. Jeśli słowo „astro” znajduje się w tekście wyświetli liczbę, jeśli nie jest błędem, a na końcu każdej kolumny użyj = COUNT () pokaże, ile razy astro jest piłowane w kolumnie B.

Mam nadzieję, że to pomogło !!

Skopiuj kolumnę tagów do nowego arkusza, a następnie:

1. Strona główna> Edycja - Znajdź i wybierz, Zamień [spacja] (znak „jeden”), Zamień wszystko, OK, Zamknij.
2. Dane> Narzędzia danych - Tekst do kolumn, wybierz Rozdzielany, Dalej, zaznacz Przecinek, Zakończ.
3. Wstaw dwie nowe kolumny, powiedz A i B.
4. Utwórz listę 47 unikatów (z góry lub „ręcznie”) - powiedzmy w nowo wstawionej kolumnie A, zaczynając od wiersza 1.
5. = LICZ.JEŻELI ( zakres , A1) w B1 i skopiuj zgodnie z wymaganiami, gdzie zakres to tablica zawierająca wszystkie indywidualne wpisy znaczników (prawdopodobnie zaczynające się od C1) i jest zdefiniowany za pomocą stałych odniesień w całym tekście (tj. Znaki „$”).
6. Wybierz cały arkusz, Kopiuj / Wklej specjalnie / Wartości i usuń kolumny C i po prawej stronie, zgodnie z wymaganiami.
7. Zapisać.

Pierwszy krok może nie być wymagany, ale ma na celu usunięcie spacji, które w przeciwnym razie zakłóciłyby liczenie.

Zaintrygowało mnie to pytanie i chciałem dowiedzieć się, jak rozwiązać to za pomocą makra. Wiem, że powiedziałeś, że chcesz uniknąć makra, ale nie sądzę, że można tego dokonać za pomocą samej funkcji.

Poniższy kod zapętla się nad komórką B2: B25 (można to zmienić lub zmienić na parametr lub wybrane komórki, ale na razie wydawało się to najłatwiejsze). Używa niestandardowej klasy o nazwie KeyValue do agregacji nazwy znacznika i liczby wystąpień. Można to poprawić za pomocą obiektu Dictionary, ale wymaga to innych dodatków. Oddziela tagi rozdzielane przecinkami od każdej komórki i zlicza częstotliwość. Następnie wysyła tę listę do pierwszych dwóch kolumn w drugim arkuszu.

Aby dodać kod, wykonaj następujące czynności. Najpierw w skoroszycie należy włączyć pasek narzędzi dla programistów, a następnie kliknąć Visual Basicprzycisk. Następnie dodaj nowy moduł klasy i nazwij go KeyValue. Wklej następujący kod:

Public Key As String
Public Value As Integer

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

Następnie w arkuszu 1 dodaj następujący kod:

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

Kliknij przycisk Uruchom, aby policzyć tagi.