Contar palabras y caracteres con Excel

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: MS Excel

Conversión entre unidades con Excel

De tanto en tanto me enfrento al problema de convertir valores de una unidad a otra, como por ejemplo, de acres a hectáreas o de galones a litros.
La Internet nos ofrece un sinnúmero de sitios que nos asisten en hacer esta conversiones, como OnlineConversion.com o Online Unit Converter.
En Excel podemos realizar conversiones entre unidades con la función CONVERTIR (CONVERT). Esta función estará disponible sólo si tenemos instalado el complemento Analysis ToolPak.
El alcance de esta función está limitado a una lista de unidades y su uso no es muy flexible. Pero si desarrollamos un modelo en el cual necesitamos hacer operaciones entre unidades, esta función puede ser útil.

La sintaxis de CONVERTIR es obvia

CONVERTIR(número;de_unidad;a_unidad)

Para ver la lista de unidades podemos apelar a la ayuda en línea de la función. Ahí podemos ver listas ordenadas por categorías, como por ejemplo la de peso y masa



o la de temperatura



Y si alguna vez necesitamos saber cuántas cucharadas soperas hay en una pinta o en un galón, Excel vendrá a nuestro rescate



Para convertir 8 metros a pies usamos =CONVERT(C1,"m","ft")



En mi sistema, que no está montado en castellano, la función se llama CONVERT y el símbolo para la unidad pies es "ft". Como señalo más arriba, conviene chequear los símbolos de las unidades en la ayuda en línea, ya que ante cualquier imprecisión l función da un resultado #ERROR o #VALOR.

Technorati Tags: MS Excel

Calcular frecuencias y crear histogramas con Excel

En una nota anterior hemos mostrado como crear un histograma usando el Data Analyisis del Analysis ToolPak.
En esta nota veremos como hacerlo manualmente usando la función FRECUENCIA. Además veremos una fórmula para crear grupos de distribución uniformes a partir de los datos de la muestra.
Y como dijo Jack el Destripador, vamos por partes.
Supongamos esta tabla de notas de los alumnos de un colegio imaginario.



Digamos ahora que el director del colegio nos ha pedido analizar la distribución de las notas, dividiéndolas en cinco grupos de igual amplitud.
Vemos que la nota mínima obtenida es 50 lo que calculamos fácilmente con la fórmula

=MIN(A2:D18)

Lo mismo para la nota máxima, con =MAX(A2:D18)

Como nuestro director quiere cinco grupos de notas, estos serán:

de 0 a 60
de 61 a 70
de 71 a 80
de 81 a 90
de 91 a 100

Para nuestro uso en Excel tomamos sólo el extremo superior de intervalo, es decir



Para crear la columna con las frecuencias de cada grupo usamos la función FRECUENCIA. Esta función es matricial, es decir, la introducimos en el rango de celdas apretando simultáneamente Ctrl+Mayúsculas+Enter



Esta función tiene dos argumentos: Datos, en nuestro caso la tabla con las notas y Grupos, los grupos de distribución. Nótese que si bien el grupo es un intervalo, por ejemplo 0 - 60, sólo usamos la cota superior.
Para calcular el peso relativo de cada grupo usamos la fórmula

=G2/SUMA(G$2:G$8)



Ahora podemos representar la distribución fácilmente en un gráfico, que hará las delicias del director del colegio



También podemos calcular la distribución de las notas para cada asignatura por separado y mostrarlas en un mismo gráfico, lo que permitiría hacer una comparación



En lugar de crear dos columnas para cada asignatura, podemos calcular directamente la distribución relativa de los grupos de notas usando esta formula matricial

=FRECUENCIA(B2:B18,$F$2:$F$6)/CONTAR(B2:B18)



Sencillamente dividimos el resultado de FRECUENCIA para cada grupo por el total de elementos (notas) de la asignatura.

¿Cómo haríamos para calcular el intervalo de cada grupo en función de los datos de la tabla?
Lo haremos, como ya sospechan, con una fórmula matricial. Nuestras variables son la nota mínima, la máxima y la cantidad de grupos requeridos. Nuestra fórmula deberá calcular la cata superior de cada grupo.
Siguiendo con nuestro ejemplo, la nota mínima es 50, la máxima 100 (de hecho 99, pero el sentido común nos dice que debemos tomar el cuenta la máxima posible) y queremos 5 grupos divididos uniformemente.
Para facilitar la lectura de la fórmula he definido el rango de la tabla (A2:D18) en un nombre: "notas". La fórmula matricial que nos permitirá calcular los grupos es

={MIN(notas)+FILA(INDIRECTO("1:5"))*(MAX(notas)-MIN(notas)+1)/5}



Un detalle importante: seleccionamos todo el rango F2:F6 antes de introducir la fórmula. Luego escribimos la fórmula y pulsamos Ctrl+Mayúsculas+Enter simultáneamente.

¿Cómo funciona esta fórmula?

La expresión MIN(notas) genera una matriz de cinco líneas, todas con el valor 50 (la menor nota de la tabla);

FILA(INDIRECTO("1:5") genera una matriz de cinco valores del 1 al 5, siendo 5 el número de grupos que queremos exponer

MAX(notas)-MIN(notas)+1 genera una matriz de cinco líneas todas con el valor 50 (99-50+1)

La fórmula genera, entonces, una matriz con los valores 60, 70, 80, 90, 100 como resultado de:

50 + (1*50/5)
50 + (2*50/5)
50 + (3*50/5)
50 + (4*50/5)
50 + (5*50/5)

Si queremos generar 10 grupos, por ejemplo, usamos como variable 10 en lugar de 5

={MIN(notas)+FILA(INDIRECTO("1:10"))*(MAX(notas)-MIN(notas)+1)/10}

Technorati Tags: MS Excel