CONTAR PALABRAS ESPECÍFICAS EN UNA CADENA DE TEXTO CON VBA

Hola a todos!

Esta temporada estoy bastante liado programando varios algoritmos de Inteligencia Artificial, y aunque es una programación bastante compleja, lo realmente complicado es poder dedicar tiempo a desarrollar estos códigos.

Pero aún así siempre tendré un momento para escribir nuevas publicaciones y compartirlas con todos vosotros.

En el post de hoy me gustaría mostrar un método para contar las veces que se repite una palabra específica en el una cadena de texto. Algo así como un “contar.si” pero con la diferencia que no estamos contando en un rango, sino en una variable string.

Partiendo de esta base, podemos hacerlo de varias formas, pero la de hoy en mi opinión sería la más eficiente, dado que evitamos utilizar bucles.

Para el ejemplo que os voy a poner empezaré desde el inicio, es decir, pasando los datos de un rango a una cadena de texto, esta sería la información de la hoja:

CONTAR PALABRAS ESPECIFICAS EN UNA CADENA DE TEXTO CON VBA

Se trata de la información sobre la presión arterial de varios pacientes de una clínica. Para el ejemplo utilizaremos la siguiente macro:

Sub CONTAR_STRING()
'Declaramos variable
Dim final As Long, i As Long
Dim sCadena As String, Contar As Long
'Trabajamos con la "Hoja1"
With Sheets("Hoja1")
final = Application.CountA(.Range("A:A"))
'Componemos cadena de texto
For i = 2 To final
ipalabra = ipalabra & "|" & .Cells(i, 2)
Next i
'Eliminamos espacio en blanco del inicio
sCadena = Trim(Mid(ipalabra, 2, Len(ipalabra)))
'Contamos el número de veces que se repite una palabra
Contar = UBound(Split(sCadena, "BAJA"))
End With
End Sub

sCadena será la variable a la que finalmente pasemos los datos de la cadena de texto, aquí podéis ver el contenido:

CONTAR PALABRAS ESPECIFICAS EN UNA CADENA DE TEXTO CON VBA1.jpg

Y ahora para contar las veces que se repite una palabra específica, por ejemplo “BAJA”, vamos a utilizar la siguiente línea de código:

Contar = UBound(Split(sCadena, "BAJA"))

y el resultado es que la palabra BAJA se repite 5 veces:

CONTAR PALABRAS ESPECIFICAS EN UNA CADENA DE TEXTO CON VBA2

Con una sola línea nos evitamos utilizar un loop y conseguimos mayor eficiencia en nuestros procesos.

Aunque este código es muy sencillo, este método de contar resulta muy interesante para grandes procesos o complejos algoritmos.

Y eso es todo, espero que os haya gustado!. En el archivo de prueba no hay botón de ejecutar, os dejo el código para que lo analicéis o probéis ejecutando la macro con “F8”.

Descarga el archivo de ejemplo pulsando en: CONTAR PALABRAS ESPECÍFICAS EN UNA CADENA DE TEXTO CON VBA

 

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Anuncios

ALGUNAS FÓRMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL

Hola a todos:

En muchas de las consultas que recibo, un tema recurrente es el de la confección de informes. Normalmente siempre preguntas y referencias a las tablas dinámicas (campos calculados, orden de etiquetas, etc…).

Y es que realmente la función de las tablas dinámicas es esa: mostrar información dinámica en la confección informes.

Sin embargo, una buen alternativa a las tablas dinámicas es hacer nuestro informe utilizando fórmulas y siempre con la ventaja de que no vamos a depender de las limitaciones típicas de las tablas dinámicas (colocación de información, límites, etc).

Por ello, en el post de hoy me gustaría trabajar con algunas funciones que nos van a permitir realizar interesantes informes y sin necesidad de usar tablas dinámicas.

Utilizaremos una de las bases de datos que uso habitualmente como ejemplo, una plantilla ficticia de unos grandes almacenes (si quereis saber cómo he generado los nombres, podeís visitar este post: COMBINAR LA FUNCIÓN HALLAR Y LA FUNCIÓN LARGO)

Imaginad que tenemos esta base de datos:

ALGUNAS FORMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL

Y necesitamos utilizar las fórmulas adecuadas para completar los datos de este informe (que ahora está vacío):

ALGUNAS FORMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL_1

Para los campos de Sexo, Idioma y Estudios necesitamos funciones que cuenten y a su vez que admitan varios criterios de condición.

Para el campo de la edad necesitamos funciones que puedan obtener promedios, máximos y mínimos.

He creado cuatro hojas con el mismo informe, en cada una trabajaremos una fórmula distinta para contar.

En la hoja INFORME_SUMAPRODUCTO utilizaremos la función SUMAPRODUCTO, la cual vamos a modificar para que cuente los datos que necesitamos. Por ejemplo el número de Hombres en la sección de Deportes:

Solo tenemos que seleccionar un rango de la columna “C” (SECCION) de la Hoja BBDD y que sea igual a la celda A3 de nuestro informe (Deportes) e introducimos un segundo criterio, seleccionando la columna E de la hoja BBDD (SEXO) y la celda B2 de nuestro informe (Hombre).

=SUMAPRODUCTO((BBDD!$C$2:$C$65000=A3)*1;(BBDD!$E$2:$E$65000=$B$2)*1)

Para el resto de campos va a ser igual, excepto en IDIOMA (OTROS), donde debemos especificar que el idioma debe ser diferente de INGLES, FRANCES Y ALEMAN:

=SUMAPRODUCTO((BBDD!$C$2:$C$65000=A3)*1;(BBDD!$F$2:$F$65000$E$2)*1;(BBDD!$F$2:$F$65000$F$2)*1;(BBDD!$F$2:$F$65000$D$2)*1)

Como podéis observar, vamos introduciendo las condiciones de forma anidada y vinculando con el idioma que queremos excluir del proceso de contar empleados.

En la hoja INFORME_CONTAR.SI.CONJUNTO utilizaremos la función CONTAR.SI.CONJUNTO, presente desde Excel 2007 y es una buena opción para contar los elementos de nuestro informe. Siguiendo el ejemplo anterior (número de Hombres en la sección de Deportes) sería así:

=CONTAR.SI.CONJUNTO(BBDD!$C$2:$C$65000;A3;BBDD!$E$2:$E$65000;$B$2)

En la sintaxis seleccionamos rango de la columna SECCIÓN de la hoja BBDD y que sea igual a A3 y también seleccionamos la columna SEXO y que sea igual a B2 (Hombre).

Para el resto de campos va a ser igual, excepto en el IDIOMA (OTROS) donde usaremos la misma fórmula pero indicando los idiomas que no queremos que se tengan en cuenta:

=CONTAR.SI.CONJUNTO(BBDD!$C$2:$C$65000;A3;BBDD!$F$2:$F$65000;”” & $E$2;BBDD!$F$2:$F$65000;”” & $F$2;BBDD!$F$2:$F$65000;”” & $D$2)

Efectivamente usamos las clausulas para indicar que cuente aquellos que sea distintos de INGLES, FRANCES Y ALEMAN.

Pues bien, con cualquiera de estas fórmulas podríais realizar el informe perfectamente. Pero me gustaría dejaros otras dos fórmulas (matriciales) para realizar el mismo proceso:

En la hoja INFORME_MATRICIAL utilizaremos una matricial muy sencilla que realiza prácticamente el mismo trabajo que SUMAPRODUCTO (pero en matricial).

Usamos el mismo ejemplo que el de las fórmulas anteriores: Número de hombres en la sección “Deportes”:

{=SUMA((BBDD!$C$2:$C$65000=A3)*(BBDD!$E$2:$E$65000=$B$2))}

Y el resultado será el mismo que en las fórmulas anteriores, es decir 7.

El resto de campos será igual pero cambiando los parámetros, excepto en IDIOMA (OTROS), aquí tendremos que ampliar la fórmulas con más criterios:

{=SUMA((BBDD!$C$2:$C$65000=A3)*(BBDD!$F$2:$F$65000$E$2)*(BBDD!$F$2:$F$65000$F$2)*(BBDD!$F$2:$F$65000$D$2))}

Indicando qué idioma no queremos contar. Esta matricial, me parece muy sencilla y a la vez efectiva, es una de mis favoritas 🙂

En la hoja INFORME_MATRICIAL_2 utilizaremos otra matricial pero en la que usaremos la función CONTAR.

Para el cálculo del número de hombres en la sección “Deportes”, sería así:

{=CONTAR(SI((BBDD!$C$2:$C$65000=A3);SI(BBDD!$E$2:$E$65000=$B$2;BBDD!$A$2:$A$65000)))}

Sin embargo, esta fórmula tiene una peculiaridad, solo va a contar si el rango sobre el que se cuenta es numérico, en nuestro ejemplo podríamos hacer referencia a la columna A o a la D, en nuestro caso, he marcado la A en rojo en la fórmula. Por ello no la podréis usar si vuestra base de datos no tiene una columna con datos numéricos, tipo edad, ID, etc.

Para el contar las celdas de IDIOMA (OTROS), tendremos que incluir los criterios en la función, así:

{=CONTAR(SI((BBDD!$C$2:$C$65000=A3);SI(BBDD!$F$2:$F$65000$E$2;SI(BBDD!$F$2:$F$65000$F$2;SI(BBDD!$F$2:$F$65000$D$2;BBDD!$A$2:$A$65000)))))}

Y el resultado es el mismo para todos los casos:

ALGUNAS FORMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL_2

Aunque no era el objeto de este post, os he incluido tres fórmulas más (matriciales) para calcular el promedio, el máximo y el mínimo de la edad por cada sección.

Para calcular el Promedio:

{=REDONDEAR(PROMEDIO(SI(BBDD!$C$2:$C$65000=A3;BBDD!$D$2:$D$65000));0)}

y usamos la función redondear para eliminar todos los decimales (podríamos usar entero).

Para el cálculo de Max:

{=REDONDEAR(MAX(SI(BBDD!$C$2:$C$65000=A3;BBDD!$D$2:$D$65000));0)}

Para el cálculo de Min:

{=REDONDEAR(MIN(SI(BBDD!$C$2:$C$65000=A3;BBDD!$D$2:$D$65000));0)}

El resultado es este (el mismo para las cuatro pestañas):

ALGUNAS FORMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL_3

Y las cuatro pestañas tienen que mostrar la misma información.

ALGUNAS FORMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL_4

Hemos utilizado fórmulas distintas para contar numero de empleados por sección o departamento, según sexo, idioma o estudios. Y también hemos calculado algunos estadísticos para enriquecer la información.

Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

Descarga el archivo de ejemplo pulsando enALGUNAS FÓRMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL

 
¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!