☰

在 Excel 中计算逗号分隔标签的频率

tag-icon • tag-icon microsoft-excel worksheet-function string-manipulation
在 Excel 中计算逗号分隔标签的频率

我有一个 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))}

但我无法成功提取标签本身。如果可能的话，我希望文件不包含“宏警告”，但我对 Excel 还不熟悉，所以如果我操作错误，请告诉我！

这可能吗？

答案1

我不完全确定这就是您要问的，但我会尝试一下。在 B 列附近创建不同的列，每列分别命名为“astro”“bio”...并在每列下方使用此公式 =FIND($C$1;astro) 例如，如果文本中包含单词“astro”，则显示一个数字，否则显示错误，在每列末尾使用 =COUNT()，它将显示在 B 列中锯切 astro 的次数。

希望这有帮助！

答案2

然后将您的标签列复制到新表：

  1. 主页>编辑-查找和选择、替换[空格]（“一个”字符）、全部替换、确定、关闭。
  2. 数据 > 数据工具 - 文本到列，选择分隔，下一步，检查逗号，完成。
  3. 插入两个新列，例如 A 和 B。
  4. 创建 47 个唯一值的列表（从上面或“手动”） - 比如在新插入的 ColumnA 中，从 Row1 开始。
  5. =COUNTIF(范围,A1) 在 B1 中并按要求抄下，其中范围是包含所有单独标签条目的数组（可能从 开始C1），并且在整个过程中使用固定引用定义（即“$”符号）。
  6. 根据需要选择整个工作表，复制/粘贴特殊/值并删除 C 列及右侧列。
  7. 节省。

第一步可能不是必需的，但其目的是删除可能干扰计数的空格。

答案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

然后在Sheet1上添加以下代码：

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

单击“运行”按钮来计算标签数量。

相关内容