Contar las apariciones dentro de un rango de Excel

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.

Contar las apariciones dentro de un rango de Excel

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:

Cómo contar ocurrencias en Excel

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:

Contar las apariciones de un número o texto en un rango de Excel

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.

22 pensamientos en “Contar las apariciones dentro de un rango de Excel

  1. fernando

    como siempre excel total gracias por todos sus conocimientos. me han servido mucho gracias

  2. ANA

    Hola, esta es una nueva experiencia para mi y me gusta actualizarme continuamente.

  3. Luis

    Maestro, una vez más me sorprende con potentes herramientas Excel y explicadas de una forma clara. Muchas gracias, pues me ha significado de mucha ayuda, tal como las anteriores.

  4. JULIO CESAR

    Excelente tema, para ir actualizando nuestros conocimiento.
    Gracias

  5. cesar

    Gracias por compartir su experiencia, estaré al pendiente sobre los demas correos.

    Saludos

  6. Manuel

    Esta pagina es excelente…excel total es de gran ayuda…

  7. MARCOS

    MUCHAS GRACIAS POR TUS ENSEÑANZAS

  8. Rene

    Excelente.

  9. Pablo

    Excelente aporte Doctor, muchas gracias.

  10. Horacio Carmona

    Exelente, gracias por tanto apoyo!!

  11. Gustavo Velasquez

    Gracias, lo intentare aplicar a mi día cotidiano, es la única forma de dominar un nuevo conocimiento…

    Saludos

  12. Marco

    Gracias por compartir sus conocimientos, quiero decirle que devuelvo el karma al universo al digerir los ejemplos aqui expuestos y los comparto con mis compañeros de trabajo en sesiones semanales.

    Nuevamente gracias y atento y ansioso para la siguiente entrega

  13. Carol

    Recien empece a ver esta pagina, y me parece excelente!, la forma en representa los ejemplos. Estoy muy contenta por la oportunidad que me da para aprender cada dia mas.

  14. Yuniol Dice

    Exelente herramienta, Gracias

  15. nino

    Buenazo maestro gracias.

  16. carmen

    GRACIAS
    Por el envío del tema esta muy interesante, lo aplicare para mi trabajo

  17. Mauricio Jiménez

    Su página es impresionante, su manera de explicar los temas hacen que veamos al excel más a nuestro alcance, muchas gracias.

  18. Guillermo Padilla

    Excelentes conocimientos.
    Muchas Gracias!

  19. herme

    me parece excelente sus publicaciones y comentarios, gracias por compartirlos con nosotros.

  20. Percy Cansaya

    Muy buen aporte, gracias.

  21. Manuel Guajardo

    Genial, la forma de enseñar. Gracias

  22. RUBEN SANCHEZ

    Gracias por estos ejemplos de Excel que son excelentes y de gran ayuda!