如何根据自定义规则对 Excel 项目进行分组

Question

我有一组数据（网站管理员工具搜索查询），这些数据在excel中带有以下标题：

Query | Impressions | Clicks | Date

在这里采样谷歌电子表格。

我想添加一个名为 Category 的额外列，并根据自定义规则对所有查询进行分类，这些规则将在 A 列上搜索字符串。前任：

if A2 contains the string 'laptop' then write 'laptop' on the category next to it

到目前为止，我已经尝试了一个公式来做到这一点，但我不确定这是最简单的方法。此外，如果有很多分类规则，公式会变得非常长且难以管理。

=IF(ISNUMBER(SEARCH("laptop",A2)),"laptop",
   IF(ISNUMBER(SEARCH("notebook",A2)),"laptop",
   IF(ISNUMBER(SEARCH("iphone",A2)),"phone",
   IF(ISNUMBER(SEARCH("galaxy s",A2)),"phone",
"other")))

您能否建议一种更好的方法，我可以将规则以这种格式放在一张纸中：

Query_contains | Category_is

其中Query_contains是需要在初始工作表的 A 列中匹配的字符串，Category是需要填充到 D 列中的值。

Answer 1

好的，我稍微改变了你的工作表....

假设您的所有数据都在单元格 A1：C9 中，那么您在单元格 F1：G5 中有下表

Search_For:    Category:
laptop         Laptop
iphone         Phone
galaxy         Phone
notebook       Laptop

现在，在单元格D2中，输入以下公式：

=IFERROR(INDEX(G$2:G$5,MATCH(TRUE,ISNUMBER(SEARCH(F$2:F$5,A2)),0)),"other")

并将其输入为数组公式含义，输入后，按 按Ctrl + SHIFT + ENTER。

然后，您可以将公式从单元格D2向下拖动，它应该会给您所需的结果（当然，您可以根据需要增加F&G列中的列表）。

希望这能解决问题！！

Answer 2

这个小宏假设您的数据在 Sheet1 中，并且您的规则位于 A 列和 B 列的工作表规则中：

Sub catagorize()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim N1 As Long, N2 As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("rules")
    N1 = s1.Cells(Rows.Count, "A").End(xlUp).Row
    N2 = s2.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To N1
        v = s1.Cells(i, 1).Value
        For j = 1 To N2
            If InStr(1, v, s2.Cells(j, 1).Value) > 0 Then
                s1.Cells(i, "D").Value = s2.Cells(j, "B").Value
            End If
        Next j
    Next i
End Sub

Answer 3

对于第三个选项，您可以使用自定义公式。

我在单独的工作表上为类别创建了一个表，然后在标准模块中插入了以下代码。

Option Explicit
Function CategoryLookup(s_Query As String, Keyword_tbl As Range)
    Dim rngKeywords As Range
    Dim s_foundCategory As String
    Dim b_CategoryExists As Boolean
    b_CategoryExists = False
    For Each rngKeywords In Keyword_tbl
        If InStr(s_Query, rngKeywords.Value) <> 0 Then
            s_foundCategory = rngKeywords.Offset(0, 1).Value
            b_CategoryExists = True
            Exit For
        End If
    Next rngKeywords
    If b_CategoryExists = True Then
        CategoryLookup = s_foundCategory
    Else
        CategoryLookup = "Other"
    End If
End Function

然后在D2（您的类别列）中插入以下公式（然后可以向下拖动）

=CategoryLookup(A2,categories!$A$2:$A$5)