Eu tenho uma planilha do Excel com uma lista de seminários e aulas que os alunos fizeram (quando abreviada):
(A)___ClassName________|(B)_________Tags____________|
Astrobiology | astro, bio, sci |
Extremophiles | chem, bio, sci |
Human Space Habitation | astro, bio, med, engi, sci | etc.
Eu gostaria de poder extrair tags individuais e obter uma contagem associada para cada tag em outra planilha, de tal forma que para o acima:
__Tag__|_Frequency_|
astro | 2 |
bio | 3 |
sci | 3 |
chem | 1 | etc.
Eu tenho tentado fazer isso usando apenas funções, e posso obter uma lista única de tags strings (por exemplo, "astro, bio, sci") usando
{=INDEX(User1!Tags,MATCH(0,COUNTIF($A$1:A1,User1!Tags),0))}
mas não consegui extrair as tags por conta própria. Eu gostaria de manter o arquivo "aviso de macro" - menos, se possível, mas eu sou novo para o excel, por isso, se eu estou indo sobre isso da maneira errada, por favor me avise!
Isso é possível?
Eu não estou completamente certo de que é o que você está pedindo, mas vou tentar. Faça colunas diferentes perto da coluna B com cada coluna chamada "astro" "bio" .... E use esta fórmula abaixo de cada coluna = FIND ($ C $ 1; astro) ex se a palavra "astro" estiver no texto mostrará um número se não for um erro e no final de cada coluna use o = COUNT () mostrará os tempos em que o astro é visto na coluna B.
Espero que isso tenha ajudado !!
Copie sua coluna de tags para uma nova planilha e, em seguida:
C1 ) e é definido com referências fixas por toda parte (ou seja, sinais '$'). O primeiro passo pode não ser necessário, mas destina-se a remover espaços que, de outra forma, interfeririam na contagem.
Então, fiquei intrigado com essa pergunta e quis descobrir como resolvê-la com uma macro. Eu sei que você disse que gostaria de evitar uma macro, mas não acredito que isso possa ser feito apenas com uma função.
O código abaixo faz um loop na célula B2: B25 (isso pode ser editado ou alterado para um parâmetro ou para as células selecionadas, mas isso parece mais fácil por enquanto). Ele usa uma classe personalizada chamada KeyValue para agregar o nome da tag e o número de ocorrências. Isso pode ser melhorado com o uso de um objeto Dictionary, mas isso requer outros suplementos. Ele separa as tags delimitadas por vírgula de cada célula e conta a frequência. Em seguida, ele exibe essa lista para as duas primeiras colunas na segunda planilha.
Para adicionar o código, você deve fazer o seguinte. Primeiro, na pasta de trabalho, você precisa ativar a barra de ferramentas do desenvolvedor e, a partir daí, clicar no botão Visual Basic . Em seguida, adicione um novo módulo de classe e nomeie-o como KeyValue . Cole o seguinte código:
Public Key As String
Public Value As Integer
Public Sub Init(k As String, v As Integer)
Key = k
Value = v
End Sub
Em seguida, na Planilha1, adicione o seguinte código:
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
Clique no botão Executar para que ele conte as tags.