La Biblia en Excel

John Walkenbach publicó en su blog un cuaderno Excel que contiene toda la Biblia. Cada libro aparece en una hoja y cada versículo en una celda. Lo interesante en este uso de Excel es no sólo mostrar sus posibilidades casi ilimitadas, sino también el uso de funciones de texto y funciones matriciales.
Partiendo de la base que una buena parte de mis lectores no dominan el inglés, decidí hacer una adaptación al español.

La Biblia Excel en Español que publico aquí, comprende solamente los capítulos 1 al 9 del libro del Génesis.

Al igual que Walkenbach, empecé por buscar un archivo texto de la Biblia para importarlo a Excel. Después de descargar un archivo un tanto problemático (cada porción de texto tiene un formato distinto), logré importarlo a un cuaderno Excel usando algunas macros ad-hoc.

A diferencia de la versión en inglés, mi versión sólo contiene los primeros 9 capítulos del Génesis, cada capítulo en una hoja y cada versículo en una celda.

La primer hoja es el índice de dónde se puede acceder a las distintas hojas. Además contiene una serie de estadísticas por capítulo: número de versículos, número de letras, letras por versículo, número de palabras, palabras por versículo y concordancia (número de veces que una determinada palabra aparece en un capítulo).




Para elaborar las estadísticas usamos las siguientes fórmulas:

Versículos: =CONTARA(INDIRECTO("'"&B10&"'!A:A")). Usamos indirecto para crear una referencia a la hoja indicada usando el valor que aparece en la celda de la columna B de la fila correspondiente. En lugar de INIDRECTO podríamos usar una referencia explícita

Letras (Caracteres): ={SUMA(LARGO(INDIRECTO("'"&B10&"'!B1:B"&C10)))}. Esta es una fórmula matricial. LARGO suma el total de caracteres en cada celda del rango señalado por INDIRECTO, y SUMA en su forma matricial suma todos los valores obtenidos por LARGO.

Palabras: esta es la fórmula más elaborada
=CONTARA(INDIRECTO("'"&B10&"'!B1:B"&C10))+SUMAPRODUCTO(LARGO(ESPACIOS(INDIRECTO("'"&B10&"'!B1:B"&C10)))-LARGO(SUSTITUIR(ESPACIOS(INDIRECTO("'"&B10&"'!B1:B"&C10));" ";""))).

La primer función LARGO calcula la cantidad de caracteres que hay en la celda. La segunda función LARGO calcula la cantidad de caracteres después de haber sustraído los espacios entre las palabras.
La diferencia entre ambos resultados es la cantidad de palabras menos 1 (ya que no hay espacio antes de la primer palabra). Así que tenemos que agregar una unidad por cada versículo en el capítulo, lo que hacemos con la función CONTARA.

Finalmente tenemos la Concordancia, que lo que hace es contar cuantas veces aparece una palabra determinada en cada versículo. La fórmula es matricial:

=SUMA(LARGO(MAYUSC(INDIRECTO("'"&B10&"'!B1:B"&C10)))-LARGO(SUSTITUIR(MAYUSC(INDIRECTO("'"&B10&"'!B1:B"&C10));MAYUSC($H$7);"")))/LARGO($H$7)

La técnica es similar a la usada en la fórmula anterior. La primer función LARGO da el número de caracteres que hay en la celda. La segunda función LARGO calcula el número de caracteres después de haber extraído con SUSTITUIR la palabra que buscamos (celda $H$7). El resultado lo dividimos por la cantidad de caracteres de la palabra buscada, que calculamos con la última función LARGO en la fórmula.

Como se puede apreciar, Excel es muy flexible y ofrece muchas posibilidades en todo lo que respecta a cálculos vinculados con textos.



Technorati Tags: MS Excel

Números a letras en Excel – versión para México

En la nota Función números a letras en Excel en español - versión corregida había publicado la versión corregida de la función, publicada originariamente en la nota Como convertir números en palabras en Excel en Español.
Esta función está orientada a usos financieros. Es decir, no sólo traduce los números a su expresión en palabras, sino que también agrega el nombre de la moneda.
Uno de mis lectores, Javier Reséndiz, ha adaptado la función a las necesidades de México. Según sus palabras:

Entre otras cosas necesitaba convertir las cifras a texto y esa una de las
razones por la que te escribo. En las facturas se acostumbra escribir,
entre paréntesis, la cantidad con letra (para evitar remiendos o
añadiduras fraudulentas en la cifra), los decimales se escriben con número
y seguidos por "/100", y al final todo se remata con "M.N." (moneda
nacional), eso cuando menos en México, no lo sé de cierto en otras partes.

Así las cosas, necesitaba que una cifra dada, $125.11, p. eje, quedara
escrita así:
(CIENTO VEINTICINCO PESOS 11/100 M.N.)

Aquí pueden descargar el código de la función adaptada para México por gentileza de Javier.


Technorati Tags: MS Excel

Números aleatorios únicos con Excel

En mi nota sobre Cómo generar números aleatorios con Excel mostraba cómo generar una serie de números aleatorios únicos, es decir, sin repeticiones. Esta técnica se basaba en un generar intencionalmente una referencia circular.
Hay, por supuesto, otras posibilidades que detallo en este archivo




1 – "Unique Numbers": Microsoft propone un macro para generar números aleatorios únicos. Esta macro siempre pone los resultados a partir de la celda A3 (en el original desde la celda A1, pero la he modificado para poder poner el comando en el encabezamiento).

2 – "RndNum": no recuerdo de que foro tome esta macro.

3 – "UDF" (user defined function): tomada del sitio de Ozgrid. Esta es una función volátil, es decir, cada vez que se produce algún cambio en la hoja, Excel recalcula la función. El resultado aparece como texto en al celda que contiene la función.

4 – "MRAND": mi favorito. Esta función forma parte del complemento desarrollado por Laurent Longre que ya he mencionado en mi nota sobre la función INDIRECTO con cuadernos Excel cerrados. Esta función tiene muchas ventajas. Al ponerla como función matricial, nos permite definir una matriz (x filas X y columnas) de números aleatorios no repetidos. Además podemos definir si la función debe ser volátil o no. La sintaxis es:
{=MRAND(máximo, inicio, cantidad, volátil o estática)}.

Este archivo contiene los ejemplos y los códigos de las macros.




Technorati Tags: MS Excel