Hace un par de meses escribí un artículo sobre cómo determinar si un valor existe en un rango de Excel e incluí una macro para determinar la existencia de un valor. Hoy desarrollaremos una nueva función VBA que analice las palabras de cada celda para contar las apariciones.
El objetivo de esta nueva función VBA es contar las apariciones dentro de un rango de Excel analizando el valor de cada celda del rango especificado. Por ejemplo, si buscamos el texto “Bora” dentro de una celda que contiene el valor “Bora Bora”, la función devolverá como resultado el valor 2. Si no necesitas analizar cada palabra de un rango de celdas, entonces puedes utilizar la función CONTAR.SI para obtener el resultado deseado.
Contar apariciones con una función
Para contar fácilmente las apariciones de un número, o de un texto, en un rango de celdas podemos utilizar la función CONTAR.SI.
Con este método podremos contar rápidamente la cantidad de celdas que tienen un valor idéntico al valor buscado. Es importante mencionar que la función CONTAR.SI evaluará todo el contenido de la celda y no partes de ella y por tal motivo en el ejemplo anterior la única celda que cumple con la condición establecida es la celda A2.
Contar apariciones dentro de un texto
Una fórmula que se acerca un poco más a lo que necesitamos es la siguiente:
=(LARGO(A3) - LARGO(SUSTITUIR(A3,C1,""))) / LARGO(C1)
Esta fórmula buscará en el texto de la celda A3 el valor contenido indicado en la celda C1 y nos devolverá el número de apariciones. Es así como esta fórmula nos devuelve el valor 2 después de buscar el valor “Bora” dentro de la cadena de texto de la celda A3:
Para conocer un poco más sobre la elaboración de esta fórmula consulta el artículo Contar caracteres en Excel. La única desventaja de esta fórmula es que solo podemos buscar en una sola celda a la vez. Por esta razón debemos crear nuestra propia función VBA para poder indicar un rango de celdas sobre el cual se contarán las apariciones de la palabra indicada.
Función VBA para contar apariciones
Nuestra nueva función se llamará APARICIONES y recibirá como argumentos el rango donde se realizará la búsqueda así como el valor a buscar. El código de nuestra función es el siguiente:
Function APARICIONES(rango As Range, valor As Variant) As Integer
contador = 0
'Recorrer todas las celdas del rango
For Each celda In rango.Cells
posicion = 1
nuevoStr = celda.Value
'Mientra la posición encontrada por InStr sea diferente a cero
Do
posicion = InStr(posicion, nuevoStr, valor, vbTextCompare)
If (posicion = 0) Then
Exit Do
Else
'Aumentar contador de apariciones
contador = contador + 1
'Cadena de texto restante
nuevoStr = Mid(nuevoStr, posicion + Len(valor) + 1)
End If
Loop While posicion <> 0
Next
APARICIONES = contador
End Function
Con la instrucción For Each recorremos cada celda dentro del rango especificado y para cada celda obtenemos la cantidad de veces que se repite el valor buscado. La parte más importante de este código es el uso de la función InStr la cual nos devuelve la posición de una palabra dentro de un texto. Si la función InStr no encuentra la palabra entonces nos devuelve el valor cero.
De acuerdo a nuestros datos de ejemplo, al buscar la palabra “Bora” dentro del rango A1:A6 deberíamos de obtener como resultado el valor 3 ya que dicha palabra aparece una vez en la celda A2 y dos veces en la celda A3. Observa cómo nuestra función devuelve el resultado correcto:
De esta manera hemos desarrollado una nueva función VBA que puede contar las apariciones dentro de un rango inclusive analizando las palabras de cada celda. Descarga el libro de trabajo que contiene esta función y recuerda que el código lo encontrarás dentro del Módulo del proyecto VBA.