Contar palabras y caracteres con Excel

lunes, septiembre 15, 2008

La mujer de mi buen amigo Abner es maestra de inglés en el colegio secundario de la zona. Como tal tiene la dura tarea de corregir exámenes y trabajos que preparan sus alumnos. Una las tareas que suele encomendar a sus discípulos es preparar una composición que no exceda un determinado número de palabras.
Abner me preguntó si había alguna manera de contar las palabras de cada trabajo en forma automática. Mi sugerencia fue que los alumnos presentaran los trabajos en archivos de Word (lo que me parece que hacen de todas maneras). Luego todo lo que hay que hacer es abrir el archivo y usar el menú Herramientas-Contar palabras



Había cierto aire de decepción en la mirada de Abner, que es un fanático de Excel. Si por él fuera también el café del desayuno lo prepararía con Excel.
-Ah!, pensé que se podría hacer con Excel, dijo cerrando la frase con un suspiro.

Personalmente soy enemigo de estas exageraciones pero me quedé pensando si, efectivamente, se podría hacer con Excel. Me acordé entonces de mi nota sobre la Biblia en Excel. Allí justamente había usado Excel para contar palabras por versículos y también encontrar con qué frecuencia cierta palabra aparecería en el texto.

Decidí adaptar ese modelo a las necesidades de Bárbara, la mujer de Abner. En esta nota presento el resultado que tal vez le resulte útil a alguno de mis lectores. Y de no ser así, por lo menos puede contribuir a mostrar algunas técnicas interesantes en Excel.

Este modelo cuenta con dos hojas, Contador donde usamos fórmulas para contar caracteres, palabras y frecuencia de una determinada palabra



y la hoja Capítulos donde vamos poniendo los capítulos (o parágrafos o trabajos de los alumnos) en celdas de la columna B. En las celdas de la columna A ponemos el nombre del capitulo o el número de parágrafo o el nombre del alumno



Veamos ahora que fórmulas usamos. Para contar la cantidad de caracteres, incluyendo los espacios, usamos la función LARGO



Sencillo, no? Para contar los caracteres, sin tomar en cuenta los espacios tenemos que usar una fórmula más elaborada



Lo que hace la fórmula =LARGO(SUSTITUIR(ESPACIOS(Capitulos!B2)," ","")) es sustituir los espacios en blanco , que señalamos en la fórmula con " ", por ausencia de espacios que señalamos con "" (comillas sin espacios entre ellas). Usamos la función ESPACIOS para quitar todos los espacios que no se encuentren entre palabra y palabra.

Las cosas se complican un poco más cuando queremos contar la cantidad de palabras. Para hacer esto partimos de la base que toda combinación de caracteres que se encuentre entre dos espacios es una palabra. Una vez establecido esto usamos la fórmula



=LARGO(ESPACIOS(Capitulos!B2))-D8+(LARGO(Capitulos!B2)>=1)

La expresión LARGO(ESPACIOS(Capitulos!B2)) es similar a la fórmula de la celda C8, sólo que hemos agregado la función ESPACIOS como hemos explicado más arriba. De esta expresión restamos el resultado de D8, que es la cantidad de caracteres del parágrafo sin tomar en cuenta los espacios entres las palabras. Esta diferencia es el número de palabras en el parágrafo menos una.
No podemos agregar sencillamente 1 a esta fórmula ya que existe la posibilidad que la celda de la referencia está vacía. Por lo tanto agregamos la expresión

(LARGO(Capitulos!B2)>=1)

Esta es una expresión lógica cuyo resultado puedes ser 1 (VERDADERO) o 0 (FALSO). Es decir, si la celda contiene texto y por lo tanto el largo es por lo menos 1, la expresión da como resultado 1 que es agregado al resultado de la fórmula.

Ahora necesitamos una fórmula para calcular la frecuencia con que una palabra se repite en el texto. La idea es que en la celda C4 ponemos la palabra que queremos evaluar, en la celdas de la columna F veremos la frecuencia de la palabra en cada parágrafo y en la celda C5 veremos el total.



En la primera etapa del desarrollo del modelo usé la misma fórmula que en el modelo de la Biblia, pero no en forma matricial

=(LARGO(ESPACIOS(MAYUSC(Capitulos!B3)))-LARGO(SUSTITUIR(ESPACIOS(MAYUSC(Capitulos!B3)),MAYUSC(Contador!$C$4),"")))/LARGO($C$4)

Me eximo de explicar esta fórmula por la sencilla razón que es errónea (mea culpa!, mea culpa!).
Esta fórmula funciona bien a condición que la palabra buscada no coincida con alguna o algunas sílabas de otras palabras. Por ejemplo, al buscar la frecuencia de la palabra "el", el resultado para el parágrafo 1 es 4 veces. Sin embargo "el" sólo aparece 2 veces



Después de darle vuelta al asunto, decidí escribir una función definida por el usuario (UDF) que resultó ser de lo más sencilla



Al aplicar esta fórmula en el modelo vemos resultados totalmente distintos



El código se basa en crear una matriz (array) con las palabras de la celda usando la función Split de Visual Basic. Luego usamos InsStr para comparar cada una de las palabras de la matriz con la palabra buscada. En caso de coincidencia incrementamos el valor de la variable Counter en 1.

Basándonos en la misma idea podemos escribir otra función UDF para realizar el recuento de palabras



El archivo con el modelo y las funciones puede descargarse aqui.


Technorati Tags:

19 comments:

homero 16 septiembre, 2008 02:14  

Sobre la fórmula errónea... no se podría corregir este problema buscando " el" en lugar de "el"?

Saludos!

Anónimo,  16 septiembre, 2008 16:16  

Me parece excelente, no se si me pueden enviar el archivo al mail
rq2370@hotmail.com

Jorge L. Dunkelman 18 septiembre, 2008 16:48  

El problema es que " el" coincide con " ellos", por ejemplo, o cualquier palabra que empiece con "el".
Si usáramos "el ", coincidiría con toda palabra que termine en "el".
Podríamos usar " el ", pero no contaríamos la palabra si esta es seguida de algún signo de puntuación (por ejemplo " el, ".

Anónimo,  11 marzo, 2009 10:33  

Hola Jorge,

¿habría alguna fórmula con matrices que permita saber (en este caso) cuántas palabras distintas se han utilizado? No me refiero al número, sino a las palabras en sí.

Saludos.

Jorge L. Dunkelman 11 marzo, 2009 22:19  

Posiblemente si, pero por qué con fórmulas matriciales? Me parece que con una UDF (función definida por el usuario) sería más fácil.

Lucio Arango 13 agosto, 2010 18:47  

excelente tutorial, Jorge tienes algun post dnde se hable como crear funciones definidas por el usuario, ahorita estoy empecenzando en macros no conosco mucho, gracias

Jorge L. Dunkelman 13 agosto, 2010 20:37  

No, no tengo ningún post específico sobre el tema. Pero si haces una búsqueda en mi blog con la palabra UDF, verás varios posts donde toco el tema.

Anónimo,  19 octubre, 2010 18:01  

He estado leindo atentamente y me voy a animar a probarlo, pero tengo una pregunta. Esposible a partir de esta macro, crear una variacion en ella, que nos permita contar las veces que se repiten varias palabras en una misma celda? yo tengo tres palabras en concreto que quiero ver cuantas veces se repite dentro de la misma celda.

Gracias por vuestra ayuda de antemano.

Jorge L. Dunkelman 20 octubre, 2010 13:39  

Es lo que hace la función. En tu caso el ragno de la función incluye una única celda.

Anónimo,  20 octubre, 2010 19:22  

Muchas gracias por tu contestacion.
Me podrias enviar la funcion a mi email para poder contar las veces que dos o tres palabras se repiten dentro de una misma celda? yo escribo varias palabras, con espacios, y quiero que me cuente las veces que se repiten por ejemplo dos de ellas en particular.
la funcion en esta web se lee algo mal y al aplicarla me da siempre error.
Mi email es oscar@olc.es

Jorge L. Dunkelman 20 octubre, 2010 21:38  

Lo lamento pero no he guardado una copia del archivo. Puedes escribir las rutinas como aparece en la entrada.

Javier 20 enero, 2012 15:51  

Esta bueno el articulo, pero yo quisiera saber si tengo una manera de calcular los caracteres totales de una base de datos (no es parrafo) sin tener que calcularlo celda a celda, ya que la cantidad de las mismas es grandisima.
Gracias

Javier

Jorge L. Dunkelman 20 enero, 2012 19:30  

Suponiendo que la base de datos está en una hoja de Excel, podrías usar esta fórmula matricial (entrarlo apretando Ctrl+Mayúsculas+Enter simultáneamente)

=SUMA(LARGO(rango de las celdas))

Dado que se trata de una fórmula matricial, el cálculo puede llevar bastante tiempo.

Anónimo,  07 agosto, 2012 06:55  

Me envias el archivo estimado amigo y te felicito una vez mas por el blog
smaylodon@gmail.com

Jorge L. Dunkelman 07 agosto, 2012 17:10  

Acabo de agregar un enlace para descargar el archivo.

Unknown 08 enero, 2014 01:28  

Hola Jorge revise atentamente tu post, e incluso lo use en un archivo que estoy creando, aunque cuando lo uso para buscar palabras como Logistics Production entre algunas celdas me eh dado cuenta que aunque solo hay una palabra me sale el numero 2 o 3,.. el 2 cuando le agrego un punto "Logistics." y el 3 realmente no se a que se deba, puesto que no hay ninguna palabra parecida, me puedes ayudar? Gracias de antemano.

Violeta Alv.

Jorge Dunkelman 08 enero, 2014 07:27  

Violeta, ¿estás usando la fórmula o la UDF?

Unknown 08 enero, 2014 21:33  

Hola! :D

Estoy usando la UDF con un contador "resultado= resultado +1" ,.. habia pensado que podia ser que no le puse tambien lo otro para contar caracteres con/sin espacios.

Saludos
Violeta Alv.

Jorge Dunkelman 09 enero, 2014 07:42  

He probado la función con tu ejemplo (buscar palabras con punto al funal y sin él) y veo que funciona sin problemas.
Puedes mandarme el cuaderno con el ejmeplo para que vea si tienes algún error en el código (no me queda claro qué es "lo otro" cuando dices "había pensado que podía ser que no le puse también lo otro...")

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP