Mejorando el rendimiento de nuestra hoja de cálculo.

Hoy hablaremos de cómo mejorar (o intentarlo al menos) el rendimiento de nuestras hojas de Excel... y es que en ocasiones comprobamos cómo nuestros equipos se colapsan, perdiendo información y mucho tiempo.
Lo primero que debemos tener presente y saber es la diferencia entre los métodos de cálculo disponibles, en especial, entre Automático y Manual, configurable desde la ficha Fórmulas > grupo Cálculo > botón Opciones para el cálculo:

Optando por la opción 'Manual' conseguimos que Excel vuelva a calcular todos los libros abiertos sólo cuando se solicita al presionar F9 ó Ctrl + Alt + F9 o bien al guardar un libro.
Esta posibilidad es aconsejable cuando trabajando con aquellos de nuestros libros de trabajo que 'se toman su tiempo' para volver a calcularse, evitando así un retraso y tiempos de espera innecesarios

Si optásemos por el 'Cálculo automático', que tenemos por defecto!!, Excel calcularía automáticamente todos los libros abiertos en cualquier cambio en nuestras hojas, y por supuesto al abrir el libro.

Una cuestión muy importante que no podemos olvidar es el equipo de trabajo.. una limitación importante son las características técnicas de nuestro PC, en cuanto a velocidad del procesador, número de ellos y memoria RAM son las principales, aunque versión del Sistema Operativo, del paquete Office y si es de 32 ó 64 bits.
Algo no muy conocido es que desde la versión Excel 2007 podemos configurar en Excel el número de procesadores a emplear, bastará entrar en la ficha Archivo > Opciones de Excel > Avanzadas > sección Fórmulas:

Por supuesto, cuanto más tengamos de todo, mejor y de manera más óptima recalcularemos nuestros Libros.

Salvados los primeros problemas de configuración (optamos por la forma de cálculo Manual) y conociendo las limitaciones de nuestros PC, pasamos a abordar pequeñas mejoras que agilizan los cálculos:

1- Ejercicio de síntesis: evitaremos, siempre sea posible, duplicar cálculos, referencias. Esto requiere un ejercicio mental previo.
2- Uso de las funciones/fórmulas adecuadas: Sabiendo que las fórmulas matriciales son extremadamente pesadas y lentas; algo menos las funciones personalizadas en VBA (nuestras UDF); las siguientes en el escalafón son las funciones tipo SUMAR.SI o SUMAR.SI.CONJUNTO y sus hermanas CONTAR.SI, PROMEDIO.SI, etc... Así procuraremos evitar las matriciales (cuando sea posible) y reemplazarlas por funciones personalizadas, que aunque también más lentas que las estándar, bien construidas, favorecen los procesos y la rapidez de cálculo; una alternativa más podrían ser las funciones de Base de datos: funciones BD (ejemplo).
Evitar trabajar con matriciales, apoyándonos en columnas/filas auxiliares.
En lugar de matriciales, emplear la función SUMAPRODUCTO que trabaja algo más rápido.
3- Evitar funciones 'volátiles': las funciones volátiles serían aquellas que siempre se recalculan, tras cada nueva actualización o cambio en la hoja. Abusar de estas funciones ralentiza cada nuevo cálculo.
Algunas de las funciones estándar de Excel evidentemente volátiles: ALEATORIO(), AHORA(), HOY(), o también DESREF(), CELDA(), INDIRECTO() ó INFO() entre otras.
4- Uso adecuado de las funciones de búsqueda (COINCIDIR, BUSCARV, BUSCAR): Las funciones de búsqueda, con frecuencia, ralentizan en exceso el cálculo significativamente.
Emplear el argumento de Coincidencia exacta, acelera el tiempo de cálculo de la función, siendo proporcional al número de celdas escaneadas antes de encontrar la coincidencia.
Es preferible (y recomendable) emplear la combinación INDICE y COINCIDIR en vez de BUSCAR o BUSCARV !!
En particular para estas funciones de búsqueda es más óptimo una ordenación previa de los rangos de búsqueda
5- Minimizar el tamaño de los rangos de trabajo que emplearemos en nuestros fórmulas o funciones: A mayor tamaño del rango más lentitud añadida.
6- Evitar los vínculos externos: Es bien sabido que un Libro de trabajo con vínculos a un segundo libro hace lento los cálculos y actualizaciones.
El uso de menos libros de trabajo más grandes es, generalmente, pero no siempre, mejor que el uso de muchos libros de trabajo más pequeños.
7-Tratar de no trabajar con un gran número de hojas vinculadas dentro de un mismo Libro: y es que aunque emplear muchas hojas en nuestros libros hacen que sean más fáciles de usar, por lo general harán más lento el calculo. Esto es, las referencias dentro de un mismo libro lastran el proceso de cálculo.
8- Minimizar Rango usado: Para ahorrar memoria y reducir el tamaño del archivo, Excel intenta almacenar información sobre la zona usada en cada hoja de cálculo que se utilizó. Esto se llama Rango usado.
A veces, acciones de edición y formato amplían el rango usado de manera significativa, lo que podría causar problemas de rendimiento y en el tamaño del archivo.
9- Siendo muy útiles, los Formatos condicionales, alargan los tiempos de cálculo.
10- De igual modo ocurre con la Validación de datos.
11- Uso de los Nombres definidos: Sin duda una de las características más eficaces en Excel, pero requiere de tiempo adicional de cálculo.
El empleo de nombres con referencia a otras hojas de cálculo, agrega un nivel adicional de complejidad para el proceso de cálculo. Además, debemos intentar evitar los nombres que hacen referencia a otros nombres (nombres anidados).
12- Macros: Ciertamente incluir programación en nuestros Libros aporta un retraso en el proceso de cálculo... que puede ser elevado en determinadas ocasiones.

En conclusión, todo aquello que aporta potencia y funcionalidad a nuestras hojas de cálculo requiere de un consumo de memoria que hace retrasar nuestros cálculos... por suerte, los equipos de trabajo con los que se disponen hoy en día, son lo suficientemente potentes para salvar la mayoría de las situaciones, solo saltando en momentos muy concretos con un GRAN volumen de información.