La Biblia en Excel

viernes, junio 29, 2007

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:

10 comments:

carlos 04 julio, 2007 01:24  

Hola, en primer debo de felicitarte por tan estupenda pagina de ayuda a los usuarios en el manjo del excel.
Mi pregunta es sobre darle un color a una respuesta o a un resultado.
Si decimos que la diferencia entre A2 y B2 me da por resultado en la celda C2 una cantidad menor a 10 y quiero que se marque con rojo, pero si es amayor que 10 y menor que 20 sea azul, si es mayor que veinte y menor que 40 sea verde y asi sucesivamente.
Otra digamos que tengo 24 distritos cuya poblacion varia entre 1000 y 57000, si yo quiero diferenciar por ejemplo todos distritos donde la poblacion es menor a 10000 con color rojo y asi sucevimanete de diez en diez voy cambiando el color.

Gracias por tu apoyo.

Jorge L. Dunkelman 05 julio, 2007 20:12  

Hola y gracias por las felicitaciones.
En relación a tu consulta, puedes leer mis notas sobre Formato Condicional.

Anónimo,  13 julio, 2007 06:12  

Hola Jorge:

Te agradezco que tomes de tu tiempo para ayudar a trabajar mejor con Excel ... tengo la siguiente situación: columna A tengo valores incrementadose en uno en la columna B tengo valores que son similares entre si, por ejemplo el uno se repite en varias celdas: la cosa es que necesito buscar dentro del rango que ser repite el uno el valor minimo de la columna A y el valor maximo de la misma columna.
Gracias
JOG

Jorge L. Dunkelman 13 julio, 2007 08:57  

Hola JOG
Te sugiero que consultes en los distintos foros de Excel, como Exceluciones, donde aparecen cada tanto preguntas similares a la tuya.

Jorge L. Dunkelman 13 julio, 2007 13:30  

Hola nuevamente JOG
me quedé pensando en tu problema y la solución me planteó algunos problemas interesantes que publicaré en la próxima entrada del blog.
Mientras tanto te comento que existen, por lo menos dos soluciones. Una con columnas auxiliares, que comentaré en la entrada, y otra con una fórmula matricial.
Suponiendo que los números repetidos están en el rango B2:B16, la fórmula matricial
={K.ESIMO.MENOR((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1));CONTAR(SI((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1))=0;1))+1)}
te dará el mínimo de los números repetidos.
Luego para ubicarlo en la columna de los valores únicos usamos una fórmula que combina DIRECCION y COINCIDIR.
Todo esto estará explicado en la entrada.

Jorge L. Dunkelman 13 julio, 2007 22:13  

Hola JOG
acabo de leer nuevamente tu consulta y me doy cuenta que no había interpretado tu pregunta.
Acabo de publicar una entrada sobre el tema.
La respuesta a tu consulta aparece al final de la nota.

Anónimo,  17 septiembre, 2008 19:47  

Solo quiero entregar mi felicitación por tu página. a todos mis cercanos que tenga duda de excel se la recomiendo.

Gustavo A. G. 06 mayo, 2016 17:33  

Hola, me interesa la Biblia en formato en Excel pero doy click a la liga y no me da resultado favorable, qué puedo hacer?
Gracias por todo
Saludos cordiales

Jorge Dunkelman 07 mayo, 2016 07:46  

Hola Gustavo, acabo de actualizar el vínculo al archivo.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP