rangos - ¿Cómo agrupar los elementos de Excel según las reglas personalizadas?

esquematizar en excel

Question

Tengo un conjunto de datos (consultas de búsqueda de herramientas del webmaster) que está en Excel con el siguiente encabezado:

Query | Impressions | Clicks | Date

Muestra la hoja de cálculo de google aquí .

Deseo agregar una columna adicional llamada Category y categorizar todas las consultas según las reglas personalizadas que buscarán una cadena en la columna A. Ejemplo:

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

Hasta ahora he intentado con una fórmula para hacer esto, pero no estoy seguro de que sea la manera más fácil. Además, si hay muchas reglas de categorización, la fórmula es muy larga e inmanejable.

=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")))

¿Puede sugerir una forma mejor de hacer esto donde puedo tener las reglas en una hoja en este formato?

Query_contains | Category_is

donde Query_contains sería la cadena que debe coincidir en la columna A de la hoja inicial, y Category sería el valor que debe rellenarse en la columna D.

Answer 1

Esta pequeña macro asume que sus datos están en la Hoja1 y sus reglas están en las reglas de la hoja de trabajo en las columnas A y 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 2

Ok, cambié tu hoja un poco ...

Suponiendo que todos sus datos estuvieran en las celdas A1: C9, entonces tenía la siguiente tabla en las celdas F1: G5

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

Ahora, en la celda D2, ingrese la siguiente fórmula:

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

E introdúzcalo como una fórmula de matriz. Significado, una vez que lo ingrese, presione CTRL + MAYÚS + INTRO .

A continuación, puede arrastrar la fórmula desde la celda D2 hacia abajo y le dará los resultados deseados (y puede, por supuesto, aumentar la lista en las columnas F y G según sea necesario).

Espero que este haga el truco !!

Answer 3

Y para una tercera opción, puede usar una fórmula personalizada.

Creé una tabla solo para categorías en una hoja separada, luego inserté el siguiente código en un módulo estándar.

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

Luego, en D2 (columna de categoría), inserte la siguiente fórmula (que luego puede arrastrarse hacia abajo)

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