excel - Contar y resaltar palabras clave dentro de frases

vba excel-vba

Question

Tengo una hoja de Excel con dos columnas. La primera columna es la frase clave y la segunda son los mensajes. La frase clave puede aparecer en la columna de mensajes. Necesito saber cuántas veces se ha producido una frase clave en la columna de mensajes. sugiera alguna forma buena y fácil de encontrar.

La frase clave es una columna y los mensajes son la segunda columna. La columna de mensajes es una combinación (concatenación) de 1 o más de 1 frases clave. Necesito saber cuántas frases clave contiene cada mensaje.

Answer 1

Es posible que pueda recopilar un recuento eficiente con un subprocedimiento de módulo que realiza todas las matemáticas en matrices de memoria memory y devuelve los recuentos a la hoja de trabajo.

He usado algunas palabras clave y frases estándar de Lorem Ipsum para crear los datos de muestra anteriores.

Toque Alt + F11 y cuando se abra el VBE, use inmediatamente los menús desplegables para Insertar ► Módulo ( Alt + I , M ). Pegue lo siguiente en la nueva hoja de códigos del módulo titulada algo así como Libro1 - Módulo1 (Código) .

Option Explicit

Sub count_strings_inside_strings()
    Dim rw As Long, lr As Long
    Dim k As Long, p As Long, vKEYs As Variant, vPHRASEs As Variant, vCOUNTs As Variant

    ReDim vKEYs(0)
    ReDim vPHRASEs(0)

    With Worksheets("Sheet1")   ''<~~ set to the correct worksheet name/
        ''populate the vKEYs array
        For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            vKEYs(UBound(vKEYs)) = LCase(.Cells(rw, 1).Value2)
            ReDim Preserve vKEYs(UBound(vKEYs) + 1)
        Next rw
        ReDim Preserve vKEYs(UBound(vKEYs) - 1)

        ''populate the vPHRASEs array
        For rw = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
            vPHRASEs(UBound(vPHRASEs)) = LCase(.Cells(rw, 2).Value2)
            ReDim Preserve vPHRASEs(UBound(vPHRASEs) + 1)
        Next rw
        ReDim Preserve vPHRASEs(UBound(vPHRASEs) - 1)
        ReDim vCOUNTs(0 To UBound(vPHRASEs))

        ''perform the counts
        For p = LBound(vPHRASEs) To UBound(vPHRASEs)
            For k = LBound(vKEYs) To UBound(vKEYs)
                vCOUNTs(p) = CInt(vCOUNTs(p)) + _
                    (Len(vPHRASEs(p)) - Len(Replace(vPHRASEs(p), vKEYs(k), vbNullString))) / Len(vKEYs(k))
            Next k
        Next p

        ''return the counts to the worksheet
        .Cells(2, 3).Resize(UBound(vCOUNTs) + 1, 1) = Application.Transpose(vCOUNTs)

        ''run the helper procedure to Blue|Bold all of the found keywords within the phrases
        Call key_in_phrase_helper(vKEYs, .Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp)))

    End With
End Sub

Sub key_in_phrase_helper(vKYs As Variant, rPHRSs As Range)
    Dim p As Long, r As Long, v As Long

    With rPHRSs
        For r = 1 To rPHRSs.Rows.Count
            .Cells(r, 1) = .Cells(r, 1).Value2
            For v = LBound(vKYs) To UBound(vKYs)
                p = 0
                Do While CBool(InStr(p + 1, .Cells(r, 1).Value2, vKYs(v), vbTextCompare))
                    p = InStr(p + 1, .Cells(r, 1).Value2, vKYs(v), vbTextCompare)
                    Debug.Print vKYs(v)
                    With .Cells(r, 1).Characters(Start:=p, Length:=Len(vKYs(v))).Font
                        .Bold = True
                        .ColorIndex = 5
                    End With
                Loop
            Next v
        Next r
    End With
End Sub

Es posible que deba cambiar el nombre de la hoja de trabajo para procesarla en la ^5ª línea de código. También he incluido una rutina auxiliar que identifica las palabras clave dentro de las frases con una fuente Blue | Bold. Comente o elimine la línea Call key_in_phrase_helper(...) en la parte inferior del primer subprocedimiento si no se desea.

Toque Alt + Q para volver a su hoja de trabajo. Toque Alt + F8 para abrir el cuadro de diálogo Macros y ejecutar el subprocedimiento. Si sus datos se parecen a los datos de muestra que he reunido, entonces debería tener resultados similares.

¹ _{Estos son algunos métodos avanzados, pero creo que también son la mejor manera de abordar su problema. Si tiene preguntas específicas que su propia investigación no explica adecuadamente, intentaré abordarlas en la sección Comentarios. El libro de trabajo de muestra que creé para crear esta solución puede estar disponible a pedido.}

Answer 2

Puede usar esta fórmula COUNTIF(B:B;"*"&A2&"*") partir de la segunda fila.