Traducir el blog

Diseño de la calculadora Fibonacci

Trilogía para calcular la sucesión de Fibonacci

  1. Cálculo iterativo de Fibonacci
  2. Cómo calcular números Fibonacci con VBA
  3. Calculando números grandes Fibonacci. Artículo dividido en 2 fragmentos:
    1. Calculadora de números grandes Fibonacci
    2. Diseño de la calculadora Fibonacci

Este es el segundo fragmento del tercer artículo sobre números Fibonacci, donde explicaré cómo he diseñado la calculadora de números grandes Fibonacci, que ningún tipo de datos de Excel o de VBA puede soportar.

En esta imagen se muestra el Fib(4902) de 1025 cifras, calculado con 114 fragmentos de 9 cifras cada uno. También informa de los tiempos de cálculo, de transformación en texto y de carga en la hoja.


Leyendo este artículo sabrás responder a estas preguntas:

0️⃣0️⃣ Cómo declarar variables
0️⃣1️⃣ Cómo borrar datos de salida
0️⃣2️⃣ Cómo validar datos de entrada
0️⃣3️⃣ Cómo calcular números grandes
0️⃣4️⃣ Cómo establecer límites al cálculo
0️⃣5️⃣ Cómo conocer el progreso del cálculo
0️⃣6️⃣ Cómo detener, continuar o abortar el cálculo
0️⃣7️⃣ Cómo medir tiempos de cálculo
0️⃣8️⃣ Cómo copiar datos en rangos de celdas
0️⃣9️⃣ Cómo visualizar números grandes
1️⃣0️⃣ Cómo imprimir datos de salida
1️⃣1️⃣ Cómo proteger datos de salida
1️⃣2️⃣ Cómo guardar datos de salida


Números grandes (Big numbers)

Esta calculadora se basa en fragmentar o trocear los números grandes en números pequeños de 9 cifras, sumar los números de 9 cifras, acarrear el desbordamiento de la décima cifra al número fragmentado superior, y así sucesivamente hasta completar el total de cifras del número grande.

Se verá mejor con un ejemplo de cálculo de números Fibonacci:

El número mas grande que admite el tipo Decimal en VBA es de 29 cifras:

  • 79228162514264337593543950335 = Límite mayor del tipo Decimal.

El mayor número Fibonacci que admite el tipo Decimal es el siguiente, de 29 cifras:

  • 50095301248058391139327916261 = Fib(139)

El siguiente número Fibonacci también tiene 29 cifras pero es mayor que el límite del tipo Decimal:

  • 81055900096023504197206408605 = Fib(140)

Por lo que el Fib(140) no cabe en un número del tipo Decimal y menos caben los de más de 29 cifras.

El problema es que no se puede usar el tipo Decimal para sumar números Fibonacci de más de 29 cifras como el siguiente, que es de 30 cifras:

  • 131151201344081895336534324866 = Fib(141)

¿Cómo podemos sumar números de más de 29 cifras?

La solución es fragmentar los números grandes en un tipo de datos con el que se pueda sumar.

He elegido el tipo Long cuyo límite superior es: 2147483647, usando sólo 9 cifras, y la décima cifra se usa para acarrear el desbordamiento de la suma de las 9 cifras.

Veamos la suma de 4 fragmentos de 9 cifras, que permiten sumar números de hasta 36 cifras.

Primero se suman las 9 cifras del fragmento 1, luego las 9 cifras del fragmento 2 añadiendo un 1 si la suma de los fragmentos 1 llegaba a la décima cifra, y así sucesivamente hasta completar la suma de los 4 fragmentos:

        4                 3                 2                 1         = Fragmentos de 9 cifras sumados independientemente

000000050 095301248 058391139 327916261 = Fib(139) de 29 cifras en tipo Decimal.

000000081 055900096 023504197 206408605 = Fib(140) de 29 cifras > tipo Decimal.

000000131 151201344 081895336 534324866 = Fib(141) = Fib(140) + Fib(139) de 30 cifras.

La numeración de fragmentos siempre se hace de derecha a izquierda. En cada uno de los 4 fragmentos se han añadido ceros por la izquierda hasta completar sus 9 cifras.

Este caso es muy simple, pues ninguno de los 4 fragmentos de 9 cifras produce acarreo de la décima cifra.

En cuantos más fragmentos se divida un número, mayores serán los números que se puedan sumar para calcular la sucesión de Fibonacci. Esta calculadora permite dividir un número grande en hasta 3641 fragmentos, con los que se puede llegar a calcular el Fib(156790) de 32767 cifras.

Si quieres profundizar sobre el cálculo con números grandes (big numbers), lee este artículo en inglés:


Descarga la calculadora de Fibonacci

Para seguir las explicaciones es mejor descargar el archivo de ejemplo, con la hoja protegida sin contraseña, desde cualquiera de estos enlaces:

Abre el archivo y habilita la edición y las macros para poder hacer los cálculos y comprender su diseño funcional.

Abre el Editor VBA con la combinación de teclas: Alt + F11

Funciona escribiendo el número de Fibonacci a calcular en la celda E1 y haciendo clic en la imagen con la espiral de Fibonacci. Hacer clic en la papelera para borrar los números.


Diseño de la calculadora de Fibonacci

La calculadora de números grandes de Fibonacci está diseñada en lenguaje VBA, dentro del módulo MóduloFibonacci, con las siguientes macros:

  • CalcularFibonacci: Ejecuta al hacer clic en la foto con la espiral de Fibonacci.
  • CalculaSucesiónFibonacci: Calcula la sucesión de Fibonacci hasta un número máximo.
  • TransformarFibonacci: Transforma los números Fibonacci en texto.
  • CargarFibonacci: Carga los textos Fibonacci en un rango de celdas.
  • GuardarTiempos: Guarda los tiempos de cálculo, transformación y carga en un mensaje.
  • BorrarFibonacci: Borra el resultado anterior.
  • ProtegerHojaActiva: Protege o desprotege la hoja activa.

Todas las macros incluyen comentarios de las instrucciones, primero para recordar qué hacen, y luego para que puedan ser modificadas por quien venga detrás.

Esta calculadora de números grandes de Fibonacci incluye las siguientes funcionalidades:


0️⃣0️⃣ Cómo declarar variables

La instrucción Option Explicit se usa a nivel de módulo para forzar la declaración explícita de todas las variables de ese módulo.

La constante CTE_FRAG define el máximo número permitido dentro de un fragmento del tipo Long.

Lista de constantes y variables del módulo:

Las variables empleadas son del tipo Private, al alcance del módulo, con lo que incluso se pueden analizar después de finalizar la ejecución de las macros.


0️⃣1️⃣ Cómo borrar datos de salida

Antes de hacer cualquier cálculo, limpia automáticamente los datos anteriores de salida, para que no interfieran con los nuevos datos. También se pueden borrar manualmente los datos haciendo clic en la papelera.


0️⃣2️⃣ Cómo validar datos de entrada

En la celda E1 se valida el número máximo X para el que se calculará la sucesión de Fibonacci, siendo un entero entre 1 y 156790, debido al límite máximo de 32767 cifras guardadas como texto en una celda.

Parte de la macro CalcularFibonacci:


0️⃣3️⃣ Cómo calcular números grandes

En la celda E1 se introduce el número de Fibonacci a calcular y se hace clic en la espiral de Fibonacci (también se puede ejecutar con el método abreviado del teclado: Control + Mayúsculas + C), que ejecuta esta parte de la macro CalcularFibonacci:

El calculo de la variable iMaxFrag es una estimación, suponiendo que cada 4,78 números Fibonacci se incrementa una cifra su valor.

La división por 9 es debida a que los fragmentos son variables del tipo Long de 9 cifras. Se añade un 1 por seguridad de que cabrán todas las cifras en el número de fragmentos.

Con esta estimación las pruebas han resultado satisfactorias hasta ahora. Si se detectan casos de errores en la segmentación habrá que ajustar ese valor. Quien quiera puede mejorar la fórmula del cálculo del número de fragmentos...

La función CalculaSucesiónFibonacci realiza el cálculo:

El cálculo se hace con una matriz redimensionada como Fib(0 To FibMax, 1 To iMaxFrag) con datos del tipo Long con los números Fibonacci siendo guardados como fragmentos de 9 cifras significativas. 

La primera dimensión es para los números de Fibonacci desde el 0 al máximo a calcular (FibMax). La segunda dimensión es para los fragmentos de números del tipo Long de 9 cifras significativas.

Las variables del tipo Long son enteros largos, siendo su mayor valor positivo: 2.147.483.647, por lo que la décima cifra por la izquierda sólo puede valer 0, 1 y 2, y para el caso de 2 el resto de cifras no forman un número completo de 9 cifras, por lo que hay que prescindir de números iguales o mayores que 2.000.000.000 en los cálculos fragmentados.

El cálculo con números del tipo Long se limitará hasta el valor 1.999.999.999 pero únicamente se almacenarán números de 9 cifras, del 0 al 999.999.999, siendo la cifra de la izquierda usada para saber si hay desbordamiento en la suma de los dos fragmentos de los anteriores números Fibonacci.

Como ejemplo vamos a calcular el primer número Fibonacci que necesita 2 fragmentos de 9 cifras, que es el Fib(45).

Abriendo en el Editor VBA la Ventana Locales, los dos números anteriores son Fib(43) y Fib(44), que son de 9 cifras, por lo que caben en un tipo Long delimitado a 9 cifras. Pero su suma hace que Fib(45) sea de 10 cifras, por lo que no cabe en un tipo Long delimitado a 9 cifras.

El cálculo del número Fib(45) se hace así:

  1. Se suman los fragmentos de orden inferior (es la segunda dimensión de la matriz): Fib(43,1) + Fib(44,1) resultando un número de 10 cifras.
  2. Se guardan las 9 cifras de la derecha del resultado de la suma en el primer fragmento Fib(45,1)
  3. Se guarda el valor 1 de la décima cifra en la variable de desbordamiento (iDesb) para poder sumarlo al segundo fragmento Fib(45,2).
  4. Se repite este proceso para los fragmentos de orden superior.

Cuando la suma de dos fragmentos de 9 cifras sobrepasa las 9 cifras, se produce desbordamiento que hay que acarrearlo en la suma de fragmentos de orden superior.

¡Este es el truco usado para poder sumar números Fibonacci de muchas cifras!

Mi modesta tableta, con Windows 10 y con 2 GB de RAM, calcula en menos de 8 minutos hasta el Fib(151660) de 31695 cifras con 3522 fragmentos de 9 cifras. Números de Fibonacci más grandes generan errores de memoria en mi tableta.

¿Cuál es el mayor número Fibonacci que puede calcular tu máquina?


0️⃣4️⃣ Cómo establecer límites al cálculo

Los límites en el cálculo son debidos a la imposibilidad de guardar infinitas cifras en una celda con formato Texto.

En el apartado 0️⃣2️⃣ Cómo validar datos de entrada ya se ha establecido el número Fib(156790)  como el número máximo de cálculo de la sucesión de Fibonacci, debido al límite máximo de 32767 cifras guardadas como texto en una celda.

El proceso de guardar los números Fibonacci, fragmentados en números del tipo Long de 9 cifras, en variables del tipo Texto, lo hace la macro: TransformarFibonacci

El número Fib(4901) contiene 1024 cifras, que es el máximo número de caracteres visualizados en una celda. Hasta ese número se visualizan todos los números del Fib(0) al Fibonacci calculado.

A partir del Fib(4902) solamente se visualiza el último número Fibonacci calculado.

Dos bucles recorren la matriz Fib(), con los números fragmentados, para construir la matriz sFib(1 To lFib - lNum, 1 To 2) del tipo String.

La primera dimensión son los valores X guardados como texto, por ejemplo X=45 se guarda en sFib(46,1) y Fib(45) se guarda en sFib(46,2) que es la segunda dimensión de la matriz.

Con la función String() se añaden ceros "0" por la izquierda hasta completar las 9 cifras de cada fragmento del número del tipo Long.


0️⃣5️⃣ Cómo conocer el progreso del cálculo

Instrucciones en 3 macros distintas del estado del proceso:

En la barra de estado se informa, cada 1000 sumas, tanto del progreso del cálculo como de la transformación de los números fragmentados en texto.


0️⃣6️⃣ Cómo detener, continuar o abortar el cálculo

El cálculo arranca al hacer clic en la imagen con la espiral de Fibonacci. Si se hace clic de nuevo en esa imagen, se para el cálculo y se pregunta:

¿Seguir calculando?

Respondiendo No, continua el cálculo.

Respondiendo , se aborta el cálculo y se informa con el mensaje de la izquierda que no se ha conseguido obtener el número Fibonacci buscado, sino un número inferior debido a abortar el cálculo.

Para ejecutar otros eventos del sistema se ha colocado estratégicamente en las macros la instrucción: DoEvents, que permite volver a lanzar la macro CalcularFibonacci tantas veces como se haga clic en la imagen con la espiral de Fibonacci:


0️⃣7️⃣ Cómo medir tiempos de cálculo

Durante el proceso de cálculo se guarda el Timer al inicio del proceso. Al finalizar el proceso, con un mensaje se informan los tiempos de cálculo, de transformación de los fragmentos de números en texto y de carga de los números en formato texto en las celdas de la hoja.

El ejemplo es para el Fib(99999) que ha sido calculado en unos 214 segundos, menos de 4 minutos en mi tableta.

¡Seguro que en tu máquina cuesta menos calcularlo que en la mía!

La macro que va montando el mensaje final con los tiempos es la siguiente:


0️⃣8️⃣ Cómo copiar datos en rangos de celdas

Habiendo declarado una matriz del tipo Texto con la instrucción:

Private sFib() As String

Y redimensionada como matriz de dos dimensiones, siendo la primera dimensión el número de Fibonacci calculados y con la segunda dimensión se guarda X y Fib(X):

ReDim sFib(1 To lFib, 1 To 2) As String

Prácticamente una única instrucción copia los números, convertidos en texto dentro de la matriz sFib, y los pega en las celdas de las columnas A (valor de X) y B (valor de Fib(X)):

Range("A2:B" & lFila) = sFib

Siendo lFila el número de fila que corresponde al último número de Fibonacci a pegar en el rango de celdas y sFib la matriz del tipo String con los números convertidos en texto.

Igualar un rango de una hoja de cálculo con una matriz VBA de dos dimensiones es muchísimo más rápido que hacer lo mismo con un bucle que recorra la matriz para copiar sus valores y pegarlos individualmente en cada celda. Microsoft no lo explica bien en este enlace: Uso de matrices (VBA) | Microsoft Docs. En este enlace está mejor explicado: Arrays And Ranges In VBA (cpearson.com)

La macro CargarFibonacci realiza todo el proceso, creando una tabla con los valores en formato texto de X y Fib(X), siendo la principal instrucción la que iguala el rango con la matriz sFib:


0️⃣9️⃣ Cómo visualizar números grandes

Microsoft dice que el límite de caracteres visualizados en una celda es de 1024, aunque en la celda se puedan guardar textos de hasta 32767 caracteres.

Al ajustar el texto en las celdas he comprobado que,  con el ancho de columna establecido, se puede visualizar hasta el Fib(14605) de 3052 cifras. Se pueden visualizar números mayores si se aumenta el ancho de la columna B.

Se pueden visualizar números más grandes en la barra de fórmulas:

En cualquier caso, siempre se pueden copiar las celdas y pegarlas en el bloc de notas.


1️⃣0️⃣ Cómo imprimir datos de salida

Para imprimir desde el menú: Archivo > Imprimir, con lo que se abre la presentación previa de las páginas a imprimir:

Cuando se calculan números mayores que Fib(4901), sólo se puede imprimir un único número Fibonacci y la impresión será parcial para números con muchas cifras, como se ha comentado en el apartado anterior explicando cómo visualizar números grandes.


1️⃣1️⃣ Cómo proteger datos de salida

La macro para proteger los datos de salida es ProtegerHojaActiva:

La protección permite modificar únicamente la celda E1 con el número máximo de Fibonacci a calcular.

No se puede crear una tabla o borrar un rango dentro de una hoja protegida, por lo que en esos casos se debe quitar la protección de la hoja previamente.


1️⃣2️⃣ Cómo guardar datos de salida

Antes de guardar la plantilla se debe decidir si se quieren guardar los números de Fibonacci calculados.

Si no se quieren guardar se debe hacer clic en la imagen de la papelera para borrar la tabla de datos antes de guardar el archivo.

Para guardar se hace clic en el icono con el diskette 💾 o desde el menú: Archivo > Guardar o Guardar como 


Conclusiones

Este artículo debe servir de ejemplo de técnicas y algoritmos de cálculo de números grandes, aunque lo fundamental es que el cálculo con números grandes no se debe hacer en VBA y/o en Excel. Es más conveniente usar librerías específicas de números grandes, por ejemplo en el lenguaje R con la clase bigz: Large Sized Integer Values.

Importa mucho elegir qué tipo de dato va a ser el menor fragmento de un número grande. En este ejemplo se ha elegido el tipo Long. En el artículo anterior se comentó que para otros algoritmos se había elegido el tipo String o el tipo Byte. Sería interesante probar con otros tipos de datos VBA, como Int, Double e incluso Decimal, para saber si mejora la eficacia del algoritmo y bajan los tiempos de cálculo.

Pero eso se lo dejo como ejercicio a los lectores de este modesto blog de Excel...

Calculadora de números grandes Fibonacci

Trilogía para calcular la sucesión de Fibonacci

  1. Cálculo iterativo de Fibonacci
  2. Cómo calcular números Fibonacci con VBA
  3. Calculando números grandes Fibonacci. Artículo dividido en 2 fragmentos:
    1. Calculadora de números grandes Fibonacci
    2. Diseño de la calculadora Fibonacci

Este es el tercer artículo sobre números Fibonacci, donde veremos cómo calcular números grandes, que ningún tipo de datos de Excel o de VBA puede soportar.

Como el artículo es demasiado extenso, lo he dividido en 2 fragmentos. El primer fragmento es este mismo artículo. En el segundo fragmento explicaré cómo he diseñado esta calculadora de la sucesión de Fibonacci para números grandes...

La división de un problema complejo en fragmentos es una solución elegante y eficaz, como veremos en este artículo fragmentado en dos partes...

Divide y vencerás (DYV) es uno de los principales métodos a seguir para diseñar algoritmos complejos. El método está basado en la resolución recursiva de un problema dividiéndolo en dos o más subproblemas de igual o similar tipo. El proceso continúa hasta que éstos llegan a ser lo suficientemente sencillos como para que se resuelvan directamente. Al final, las soluciones a cada uno de los subproblemas se combinan para dar una solución al problema original.

En esta técnica se basan los algoritmos de ordenamiento, de multiplicación de números grandes, de análisis sintácticos y muchos más.

Para calcular números grandes Fibonacci he usado esta misma técnica, dividiendo los números grandes en fragmentos de números más pequeños, que se puedan sumar, y así poder vencer los límites de los tipos de datos en VBA.


Límites de los tipos numéricos

En los dos primeros artículos hemos comprobado que el límite es de 15 cifras significativas, para un número dentro de una celda de Excel, y que en VBA el límite es de 29 cifras significativas, para el subtipo Decimal del tipo Variant.

Ahora definiremos tipos de datos personalizados con matrices de números fragmentados, para alcanzar más de 29 cifras significativas con lo que, por ejemplo, podremos calcular el número de Fibonacci:

Fibonacci(518) - Wolfram|Alpha (wolframalpha.com) = 

805587367387474993557712643417250666635155463347703764181824844653814375958479581952978891769754733107167209

Que ni siquiera cabe en una línea de texto, de unos 80 caracteres, pues contiene 108 cifras significativas.

En esta imagen se muestra la sucesión de Fibonacci desde Fib(492) hasta Fib(518), todos ellos números de más de 100 cifras significativas.


La pregunta es: ¿Cómo puedo hacer cálculos con números tan grandes?

¡Si no hay ningún tipo de dato numérico, ni en Excel ni en VBA, que admita tantas cifras!


Cómo sobrepasar los límites de cálculo

Lo que me encanta de los ordenadores es que obedecen sin rechistar a cualquier orden que se les dicte mediante un programa.

No como los humanos que, aún dándoles la receta de cocina, no saben cocinar... (me pongo yo mismo como ejemplo)

Siempre que se respeten las reglas del lenguaje de programación, se pueden obtener resultados de salida que a priori son imposibles.

Para demostrarlo vamos a saltarnos las barreras de los límites de los tipos de datos numéricos definidos por el lenguaje VBA.

¿Te imaginas hacer cálculos con números grandes antes de la invención del ordenador?

¿Te imaginas a mi nieto aprendiendo a calcular con un ábaco?

Enlace a unos cuantos artículos sobre cálculos manuales, antes de que se inventara el cálculo automático:

Calculadora humana - Wikipedia, la enciclopedia libre

Cómo los científicos realizaban cálculos complejos antes de la invención de las calculadoras (labrujulaverde.com)

Los inicios de los cálculos computacionales – The Smoke Sellers

Instrumentos de cálculo en la historia - Ciencia (redestrategia.com)


Descarga la calculadora de Fibonacci

Descarga esta calculadora de Fibonacci para números grandes, con la hoja protegida sin contraseña, desde cualquiera de estos enlaces:

Abre el archivo y habilita la edición y las macros para poder hacer cálculos con números grandes de la sucesión de Fibonacci.


Características de la calculadora

Para calcular un número de Fibonacci se escribe en la celda E1 y se hace clic en la imagen con la espiral de Fibonacci. Haciendo clic en la papelera se borran los números.

Características de la calculadora:

  1. Calcula hasta el número Fib(156790), debido al límite máximo de 32767 cifras guardadas como texto en una celda.
  2. Visualiza un único número si es mayor que Fib(4901), debido al límite máximo de 1024 caracteres visualizados en una celda.
  3. Visualiza todos los números si el Fibonacci máximo a calcular es el número Fib(4901).
  4. Permite arrancar, pausar y abortar el cálculo.
  5. Muestra el progreso del cálculo en la barra de estado. 
  6. Mide los tiempos de cálculo, de transformación y de carga en la hoja.
  7. Avisa si se produce un error de memoria o de desbordamiento.
  8. Borra datos del cálculo manual y automáticamente.
  9. Imprime los números calculados. A partir del Fib(14605) es mejor usar copiar y pegar, pues se imprimen parcialmente.
  10. Protege la hoja de cálculo sin contraseña.


Vídeo: Números grandes Fibonacci

En este vídeo presento la plantilla que has descargado y que permite calcular números grandes de la sucesión de Fibonacci, de mucho más que 29 cifras significativas:


Otros algoritmos Fibonacci

El 10 de diciembre publiqué el segundo artículo de esta trilogía, y avisé de su publicación en este enlace a LinkedIn, cuando ya había programado la parte del algoritmo que calcula números grandes de Fibonacci, usando el método divide y vencerás con las matrices de tipo Long fragmentadas, que explicaré en el segundo fragmento de este artículo.

Un comentario de D. Tello, Ph.D. me puso sobre aviso de que Rick Rothstein, excelente Microsoft MVP en Excel, había escrito un código para Fibonacci usando VBA parecido al mío. La sorpresa fue que el mismísimo Rick Rothstein me contestó:

Hace un tiempo publiqué una calculadora de Fibonacci "infinita" que un tal Eric W aceleró ** drásticamente ** usando mi algoritmo, pero intercambiando las manipulaciones de cadenas con manipulaciones de arrays binarias.

Y adjuntaba el algoritmo que Eric W había publicado, con matrices del tipo Byte, en este foro: MrExcel.com - Fibonacci - Eric W

como mejora y en contestación al algoritmo de Rick Rothstein con cadenas del tipo String, de su comentario en el foro: MrExcel.com - Fibonacci - Rick Rothstein

Probando en mi maravillosa tableta (con 2 GB de RAM) a calcular el Fib(99999):

  • Con el algoritmo de Eric W tarda unos 489 segundos, más de 8 minutos.
  • Con mi algoritmo, incluido en la descarga, tarda unos 204 segundos, menos de 3,5 minutos.
  • ¡La mitad de tiempo del algoritmo de Eric W! ¡Y eso que mi calculadora incluye otras funcionalidades, además del cálculo propiamente dicho!

Me gustaría conocer los tiempos de cálculo en otras máquinas...

Y para eso necesito tus comentarios con tus resultados...


Diseño de la calculadora Fibonacci

Dejo pendiente el segundo fragmento de este artículo, con el tutorial de cómo se ha diseñado esta calculadora Fibonacci, que pronto publicaré...

Cómo calcular números Fibonacci con VBA

Trilogía para calcular la sucesión de Fibonacci

  1. Cálculo iterativo de Fibonacci
  2. Cómo calcular números Fibonacci con VBA
  3. Calculando números grandes Fibonacci. Artículo dividido en 2 fragmentos:
    1. Calculadora de números grandes Fibonacci
    2. Diseño de la calculadora Fibonacci

Lo importante para resolver problemas de cálculo es usar los tipos de datos adecuados dentro de un algoritmo, y eso es lo que voy a explicar en este artículo.

Como ejemplo de cálculo vamos a calcular la sucesión de Fibonacci, que es infinita, por lo que el cálculo se ha limitado hasta el elemento o hijo: F(139) con 29 cifras significativas.

Con este cálculo se demostrará que se puede traspasar el límite de 15 cifras significativas de las celdas de Excel, programando macros en lenguaje VBA con números de tipo Decimal.

El resultado del algoritmo de cálculo es el siguiente:


Aclaración: En esta imagen se han agrupado los primeros 140 números de Fibonacci en varias columnas, para que quepan todos en un pantallazo. En la plantilla, que se puede descargar más abajo, los números de Fibonacci se calculan uno en cada fila.

Fíjate en la indicación que hay a partir del elemento F(74), avisando que son números almacenados como textos, por si se desea convertirlos en números.

¡Ni se te ocurra! ¡Pues perderás la precisión de los números!


Límites numéricos

El límite de 15 dígitos significativos para números en celdas existe en todas las versiones de Excel conocidas, como se explica en estas dos páginas:

Microsoft explica en esos dos artículos cómo guardar números grandes en Excel: ¡como texto dentro de las celdas!

¡Es tan frustrante que ni siquiera se puede guardar el número de una tarjeta de crédito de 16 cifras en una celda! ¡Hay que guardar los 16 dígitos como texto!

Por ejemplo, si queremos guardar el número de nuestra tarjeta de crédito:

  • Se debe escribir una comilla simple (') antes del número. Por ejemplo: '1234567812345678
  • O dar formato de Texto a la celda:
    • En esos dos casos el número se guarda como texto.
    • Es adecuado para números de tarjetas de crédito.
    • Pero el problema es que es muy difícil, casi imposible, hacer cálculos con números guardados como textos.
    • Aunque el aviso de la celda lo sugiera, ¡ni se nos ocurra convertirlo en número!, pues ocurrirá lo que explico a continuación.
  • Si escribimos el número sin la comilla simple: 1234567812345678
    • Se trata como un número y la última cifra se pone a cero: 1234567812345670
    • ¡Lo que no es de desear!
    • ¡Ya no es el número de nuestra tarjeta de crédito!
    • ¡No se pueden guardar más de 15 cifras significativas de un número!

En el ejemplo de esta captura se introduce el mismo número de 16 cifras (1234567812345678) en las 3 celdas:

  • En A1 se introduce el número de 16 cifras como texto. ¡Correcto! ¡Pero no es un número!
  • En C1 se convierte automáticamente en un número de coma flotante. ¡Error!
  • En E1, con formato numérico tipo 0, se pone a 0 la última cifra. ¡Error!

Este comportamiento se produce solo si la celda tiene el formato Número y el número especificado supera los 15 dígitos. Para las celdas con formato de texto, se pueden escribir hasta 32767 caracteres, aunque Excel solamente muestra los primeros 1024 caracteres en la hoja de cálculo.

O sea, que podemos guardar y visualizar números de hasta 1024 cifras en una celda con formato Texto, lo que es suficiente para el desafío de este artículo: escribir 29 cifras en una celda.


Números de más de 15 cifras

Lo siguiente lo he leído en esta página en inglés: Significant figures - Wikipedia

La mayoría de las calculadoras y programas de computadora pueden manejar el número π como 3,141592653589793 con 16 dígitos decimales, que se usa comúnmente en computadoras, y en la NASA para "los cálculos de mayor precisión del JPL para la navegación interplanetaria".

Pero Excel no llega ni siquiera a guardar números de 16 decimales. Aunque Excel siga la especificación internacional IEEE 754 para almacenar y calcular números de punto flotante, sólo almacena 15 dígitos significativos en un número y cambia los dígitos después del decimoquinto lugar por ceros, por lo que ni la NASA ni SpaceX pueden usar Excel para sus viajes interplanetarios...

¡A no ser que usen el lenguaje VBA para hacer cálculos con más de 15 cifras significativas!

Brian Sutter es el diseñador de la trayectoria de la misión Lucy de la NASA, que explorará los satélites de Júpiter, para la que programa macros en Excel con el tipo Decimal de datos.

¡Y eso es lo que también voy a hacer ahora para calcular los números de Fibonacci!


Calcular Fibonacci con VBA

En estos dos artículos se exponen algoritmos con macros en lenguaje VBA para calcular la sucesión de Fibonacci:

VBA: Números de Fibonacci en Excel | EXCEL FORO: Un blog de Excel

PROGRAMAR EL ALGORITMO DE SUCESIÓN DE FIBONACCI CON VBA EN EXCEL - Excel Signum

El primero está escrito por Ismael Romero, Microsoft MVP Excel desde 2014, que cuenta con uno de los mejores blogs que conozco sobre Excel.

El segundo está escrito por Segundo Miguéns Romeu, Microsoft MVP Office Apps and Services desde 2017, centrado principalmente en Excel, programación y Power Query.

Tengo que reconocer que sus blogs son muy instructivos, ¡pero sus cálculos de la sucesión de Fibonacci con VBA pueden mejorar!

Ismael usa variables del tipo Long en sus cálculos, por lo que el mismo avisa en su artículo que:

"Habrá que tener precaución con el límite de precisión de Excel (15 dígitos), ya que a partir del elemento 75, la precisión de Excel nos devolverá valores incompletos o no válidos."

Segundo usa variables del tipo Variant pero no las inicializa, por lo que un tipo Integer se promociona a un tipo Long y un tipo Long se promociona a un tipo Double, pero no se promociona automáticamente al tipo Decimal, ¡que es el que nos interesa usar para cálculos con números de hasta 29 cifras significativas!


Tipos de datos VBA

El tipo Double tiene como valor máximo: 1,79769313486232E308, pero con 15 dígitos significativos.

El tipo Decimal admite números de hasta: 79228162514264337593543950335 con 29 cifras, pero solo puede usarse como un subtipo de un tipo Variant. No se puede declarar directamente una variable de tipo Decimal. Sin embargo, se puede crear un tipo Variant cuyo subtipo sea del tipo Decimal con la función CDec.

Microsoft hace un resumen de todos los tipos de datos VBA en esta página:

Resumen de tipo de datos | Microsoft Docs

En la siguiente tabla he resumido todos los tipos de datos VBA, por lo que es conveniente colgarla de la cabecera de la cama, para saber programar macros sólidas y robustas:

Contiene enlaces a cada tipo de datos en español, lo que puede hacer que su traducción no sea exacta y se deba: Leer en inglés...


Algoritmo VBA Fibonacci

El algoritmo de la macro SucesiónFibonacci usa la función CDec, que convierte un tipo Variant en un tipo Decimal, para poder calcular el elemento F(139) con 29 cifras significativas:

Lo importante de esta macro es la definición de una variable Array Fib() del tipo Variant que, cuando se definen sus dos primeros elementos, se convierten explícitamente en tipo Decimal con la función CDec:

Fib(0) = CDec(0)

Fib(1) = CDec(1)

Los demás números Fibonacci se calculan mediante suma de los anteriores:

Fib(i) = Fib(i - 1) + Fib(i - 2)

La suma de números de tipo Decimal convierte implícitamente el resultado en tipo Decimal.

Los números de más de 15 cifras se guardan en las celdas convertidos a texto con la función:

CStr(Fib(i))

Esta es la hoja de cálculo 'VBA Fibonacci', vacía antes de hacer el cálculo:

En la celda E1 se escribe el valor máximo de X que se quiere calcular, que no puede ser mayor que 150, aunque sólo calcula hasta Fib(139) pues el tipo Decimal llega hasta 29 cifras significativas.

Haciendo clic en la espiral de Fibonacci comienza el cálculo.

Haciendo clic en la papelera se borra la tabla de números Fibonacci.

Haciendo clic en mi foto se abre la página de mi blog. 

En la hoja 'VBA Fib Simple' también se calcula la sucesión de Fibonacci, pero con un algoritmo mucho más simple e igual de efectivo, que conviene que estudies...


Descarga del algoritmo

El archivo de ejemplo, con la primera hoja protegida sin contraseña, se puede descargar desde cualquiera de estos enlaces:

Abre el archivo y habilita la edición y las macros para poder hacer los cálculos.


Vídeo: Cómo calcular números Fibonacci con VBA

En este vídeo explico como programar macros VBA con las que calcular números de hasta 29 cifras significativas:


Otros algoritmos de cálculo

Mientras me documentaba descubrí que se me había adelantado Benjamín Martín-Palanco con estos algoritmos:

Nube de datos: Sucesión de Fibonacci en Excel y VBA

Nube de datos: Sucesión de Fibonacci en R 

Ahora falta conseguir mas de 139 elementos Fibonacci, con Excel y VBA, como se hace con R, que lo consigue gracias a la clase bigz: Large Sized Integer Values.

¿Cómo conseguir F(140) y sucesivos números de Fibonacci en VBA de Excel?

La respuesta a esta pregunta la dejo para otro artículo.

Cálculo iterativo de Fibonacci

Trilogía para calcular la sucesión de Fibonacci

  1. Cálculo iterativo de Fibonacci
  2. Cómo calcular números Fibonacci con VBA
  3. Calculando números grandes Fibonacci. Artículo dividido en 2 fragmentos:
    1. Calculadora de números grandes Fibonacci
    2. Diseño de la calculadora Fibonacci

En la siguiente imagen aparecen los 17 primeros números de Fibonacci, calculados según la ecuación:

Definiendo: F(0) = 0 y F(1) = 1, se cumple que para n>1: F(n) = F(n-1) + F(n-2)


Imprecisión de los cálculos

La sucesión de Fibonacci es una serie infinita de números naturales.

¡Lástima que Excel no contemple números con precisión infinita!

Ni siquiera se salva el número π. Como mucho, se obtienen sus primeras 15 cifras decimales en una celda de Excel.

Al escribir en una celda la fórmula: =PI() produce: 3,14159265358979 con 15 dígitos significativos.

Este año 2021 se ha conseguido calcular el número π con 62.831.853.071.796 decimales, según esta publicación:

Pi-Challenge: World record attempt by UAS Grisons

Lo mismo ocurre con el número e calculado con la fórmula: =EXP(1) que produce: 2,71828182845905 siendo la última cifra redondeada incorrectamente: 2,71828182845904...

Todos los cálculos que hagamos en las celdas de Excel serán, como mucho, con números con un máximo de 15 cifras significativas.

De esta limitación no se salva la sucesión de Fibonacci, que no se puede calcular en las celdas de Excel para hijos mayores de 73.

F(73) = 806515533049393 se puede calcular por ser de 15 cifras significativas.

F(74) = 1304969544928657 no se puede calcular en una celda por ser de 16 cifras significativas.

En un próximo artículo explicaré cómo calcular números de Fibonacci hasta el elemento F(139) con 29 cifras significativas, usando el tipo de número Decimal del lenguaje VBA.

Si sabes cómo hacerlo con macros, ¡coméntalo, por favor!

De momento voy a hablar de cómo obtener números de Fibonacci con el cálculo iterativo de Excel.


Descarga Fibonacci con cálculo iterativo

El archivo sin macros y con fórmulas recalculadas iterativamente se puede descargar desde cualquiera de estos enlaces:

Abrir el archivo y habilitar la edición para analizar las fórmulas.


Desafío Fibonacci con cálculo iterativo

David Bueno Vallejo, Doctor Ingeniero en Informática y Profesor Titular en la Universidad de Málaga, escribió un comentario en este artículo de mi blog:

Test de Excel con cálculo iterativo (Comentario #2)

"Me encanta el calculo iterativo. Acabo de proponer un desafío que espero que los miembros de este blog puedan resolver"

Y enlazaba a un vídeo de su canal de YouTube:

¿Te gusta EXCEL y lo usas bien? Te invito a mi: Desafío Fibonacci

Le contesté en otro comentario:

Test de Excel con cálculo iterativo (Comentario #3)

"Hola David, me encantan los retos. Cuando publiques la solución al desafío Fibonacci te contaré la solución que acabo de diseñar para la serie de Fibonacci del 0 al 72. Como evidencia de mi propia solución te digo que la fórmula de la celda F9 es: =SI(E1>0;SI(E9="72";F6;F6+F11);1)

A partir de ahora te sigo en tu canal."

David ya dio su solución al desafío en el siguiente vídeo:

Calculo iterativo en Excel. Ejemplo de Fibonacci y otras funciones.


En el siguiente vídeo propuse mi solución al desafío:

Desafío Fibonacci Iterativo en Excel


Cálculo iterativo de Fibonacci

Si has descargado la plantilla de ejemplo, y has visto el vídeo, ya sabrás que, para ejecutar el cálculo iterativo, debes presionar la tecla F9, que calcula todo el libro, o las teclas Mayúsc + F9, que calculan la hoja activa. La presión de esas teclas realiza una iteración o, si se mantienen pulsadas, se itera continuadamente hasta soltar las teclas.

Ahora me toca a mí dar mi solución, ¡bueno no! ¡van a ser 3 soluciones!

1ª) Es la respuesta al desafío de David.

2ª) Es una solución más simple con la que calcular los números de Fibonacci.

3ª) Es la solución correcta y más elaborada, con control del recálculo iterativo y con control del rango máximo de la Sucesión de Fibonacci con 15 dígitos significativos.

Lo que no voy a hacer es explicar las soluciones dadas en la plantilla de ejemplo pues ya las he explicado suficientemente en el vídeo, por lo que únicamente voy a insistir en que esta solución sólo alcanza al número F(73) con 15 cifras significativas.


Datos sobre números Fibonacci

En los siguientes enlaces encontrarás más información sobre los números de Fibonacci:

Sucesión de Fibonacci - Wikipedia, la enciclopedia libre

fibonacci(n) - Wolfram|Alpha (wolframalpha.com)

Fibonacci Number -- from Wolfram MathWorld

The Online Encyclopedia of Integer Sequences - A000045 - OEIS

Fibonacci Sequence - A000045 - OEIS


Otros algoritmos de cálculo de Fibonacci

En las siguientes páginas encontrarás ejemplos de cálculo de números de Fibonacci con diferentes algoritmos:

VBA: Números de Fibonacci en Excel | EXCEL FORO: Un blog de Excel

PROGRAMAR EL ALGORITMO DE SUCESIÓN DE FIBONACCI CON VBA EN EXCEL - Excel Signum

PROGRAMAR EL ALGORITMO DE SUCESIÓN DE FIBONACCI CON PYTHON Y EXPORTAR A EXCEL - Excel Signum

PROGRAMAR EL ALGORITMO DE SUCESIÓN DE FIBONACCI CON TYPESCRIPT - Excel Signum

PROGRAMAR EL ALGORITMO DE SUCESIÓN DE FIBONACCI CON POWER QUERY - Excel Signum

Anímate a programar algoritmos siguiendo estos ejemplos y ganarás experiencia en todo tipo de cálculos.

Te espero en mi próximo artículo sobre el cálculo de la sucesión de Fibonacci en VBA, en el que calcularé hasta el elemento F(139) con 29 cifras significativas.

¡¡¡ Te reto a conseguirlo !!!


Corolario 

Por cierto, ¡gané el reto de David y me premió con un regalo!

David Bueno Vallejo en Twitter:

"Excelente respuesta al desafío empezando los cálculos de #Fibonacci desde 0. Gracias por aceptar el reto. Te envío mis dos cursos de @udemy_es gratis."    

He ganado estos dos excelentes cursos: 

Muchas gracias al profesor de la Universidad de Málaga y de la UNED:

David Bueno Vallejo | Doctor Ingeniero en Informática | Udemy

Estoy esperando con ansias tu próximo reto en Excel.

Nueva cabecera del blog #ExcelPedroWave

Mi nueva cabecera

En 10 años prácticamente no ha cambiado la cabecera de este blog de Excel, por lo que he decidido darle un nuevo aspecto, añadiendo un mapamundi de relojes junto a mi foto y, ante todo, he cambiado el título.

¿Qué te parece la nueva cabecera?



Mi nueva imagen

Mi nueva imagen contiene dos imágenes:

  • La foto de Pedro dentro de un archivo Excel, con círculos grandes como olas, y círculos pequeños como burbujas, para referirme a la palabra inglesa Wave:


Mi nuevo título

He cambiado el título del blog, con un nuevo logotipo para dejar atrás referencias al género, porque este blog va dirigido a todos los que les gusta Excel o quieren aprender más trucos, tanto chicas como chicos, por lo que he quitado del título la palabra: Guys (en español: tipos, tíos, chicos, individuos).

También he cambiado el título en inglés, pues la mayoría de mis seguidores hablan español, por un título con mi propia etiqueta:

Anterior título:      Pedro Wave for Excel Guys

Nuevo título:        #ExcelPedroWave

que sirve tanto en inglés como en español, y donde se hace referencia a:

  • # (símbolo almohadilla o numeral)
  • Excel (mi herramienta principal)
  • Pedro (mi nombre)
  • Wave (mi ola, mi onda, mi saludo)


Mi nueva etiqueta

El símbolo # (almohadilla) le viene como anillo al dedo al título del blog pues representa muchos de los datos que se manejan en Excel:

  • Las celdas de Excel como dos segmentos de recta similares, paralelos y casi verticales, cruzados por otros dos similares, paralelos y horizontales.
  • Los formatos de celdas personalizadas usan el signo de número (#) si se desean mostrar solo los dígitos significativos de un número. 
  • Con funciones de matriz desbordadas es posible hacer referencia a todo el rango de desbordamiento, colocando el operador de rango desbordado (#) después del rango. Sólo aplicable en Excel para Microsoft 365.
  • El signo de número y cardinal. El signo # significa No. o Nº.
  • Se parece al tablero del juego tres en línea. Excel también es un tablero, dashboard en inglés.
  • En ajedrez sirve para indicar mate, por ejemplo:
    • Mate pastor: 1 e4 e5 2 Ac4 Ac5 3 Dh5 Cc6 4 Dxf7#
  • Se asemeja al símbolo musical de sostenido ().
  • Precede a códigos de colores en formato hexadecimal. Rojo: #FF0000 (RGB: 255,0,0)
  • Es una tecla del teléfono que activa funciones especiales o códigos abreviados.
  • Introduce comentarios en algunos lenguajes de programación (como Perl, Python y PHP).
  • Reemplaza al signo de sostenido en el nombre del lenguaje de programación C# (Sharp significa sostenido en inglés).
  • En los sistemas operativos tipo Unix, el símbolo # se usa para representar al superusuario o administrador del sistema.
  • En Twitter sirve para indicar en qué hashtag se encuentra un tuit. También se usan hashtags en Facebook, en LinkedIn y en otras redes.

Todas estas son buenas razones para cambiar el título de este blog con la etiqueta de mi propio hashtag:  #ExcelPedroWave


Mis motivos del cambio

Un motivo es que el nombre del blog no había cambiado en 11 años y ya tocaba cambiarlo.

El otro motivo son las noticias de cambio de nombre de las compañías tecnológicas, que están poniendo de moda poner "metas" a sus nombres. Aunque pensándolo bien, las empresas siempre han puesto objetivos y metas, ¿verdad?

Entre las empresas que se ponen como "meta" que nos comuniquemos como avatares dentro de un metaverso están:

Pero el metaverso ya lleva mucho tiempo inventado en 2D, y los que vendrán seguro que no serán como cuando nuestros avatares se movían libremente por Second Life hace tantos años. ¡Qué tiempos aquellos! ¡Cuando nuestro avatar volaba de isla en isla para conocer gente de todo el mundo!

Algún día no hará falta tener avatares distintos para meternos en cada red social, con meternos con nuestro avatar personal en un único mundo virtual podremos movernos por toda la red, sin salir de ella ni para ir al servicio...

¿Cuánto tardaremos en ver a nuestro avatar usar Excel dentro del mundo 3D virtual?


Mis Redes Sociales

Desde hace un tiempo #ExcelPedroWave es el hashtag que uso en las Redes Sociales (RRSS) para identificar mis publicaciones, por lo que es el que voy a usar a partir de ahora para identificar y etiquetar el blog. Puedes encontrarme en las redes con ese hashtag:

También puedes encontrar estos enlaces dentro del menú: MIS REDES



Mis metas

Mis metas SMART siguen siendo las mismas que los objetivos que me puse cuando publiqué el primer artículo del blog:

  • eSpecíficas: mejorar el conocimiento de Excel y sus herramientas Power.
  • Medibles: escribir un articulo en el blog por cada reto que voy superando.
  • Asignables: el autor y los visitantes del blog aprenderán cada día un poco más de Excel y Power.
  • Realistas: en casi todos los artículos se puede descargar una plantilla de ejemplo.
  • Temporales: todos los artículos se pueden visitar en cualquier momento y lugar y, en un virtual futuro, puede que dentro de un metaverso bloguero.

Mis metas son seguir superando nuevos retos en Excel y compartirlos con mis seguidores, para sacarle todo el poder a esta excelente herramienta multiusos, tan usada y a la vez tan incomprendida, para así poder mejorar y ampliar nuestros conocimientos de Excel, descargando multitud de ejemplos del blog.


Mi vídeo de presentación

En este vídeo explico cómo he hecho la nueva cabecera del blog:

En este vídeo hago una presentación de la nueva cabecera del blog explicando que, al mismo tiempo que he hecho ese cambio, he aprovechado para hacer limpieza de algunos elementos del blog, como actualizar el menú superior y la barra lateral, y añadir el Traductor de Google con el que se puede traducir el blog a 108 idiomas.

¿Por qué no pruebas a traducir el blog a tu propio idioma?


Muchas gracias por visitar y seguir mi blog #ExcelPedroWave

Mi lista de blogs