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)