Estoy seguro de que en alguna ocasión has oído hablar del concepto de 'función volátil'. No es nada novedoso, pues existe desde el principio de los tiempos de Excel.
Cuando estás creando una plantilla con gran cantidad de datos puedes sentirte tentado a utilizar funciones como INDIRECTO, DESREF y HOY. Estas tres funciones (y alguna otra más) son un tanto especiales y debes utilizarlas con cuidado, pues se tratan de funciones volátiles y podrían afectar a la velocidad de cálculo de la hoja haciendo de la experiencia de uso de Excel, un auténtico infierno.
Una de mis recomendaciones es intentar evitar este tipo de funciones, sobre todo cuando su número es elevado (te ofreceré alternativas).
¿Quieres conocer las funciones volátiles y saber por qué debes tenerlas presentes?
Cómo se calculan las fórmulas?
Antes de hablar de funciones volátiles es una buena idea conocer cómo se calculan las fórmulas de Excel.
Imagina una gran hoja de cálculo con cientos, miles e incluso millones de fórmulas. La mayoría de estas fórmulas harán referencia a celdas y muchas de ellas contendrán más fórmulas que a su vez hacen referencia a otras celdas.
Si la celda A2 hace referencia a la celda A1 se dice que A2 depende directamente de A1. Cualquier cambio que hagas en A1 se verá reflejado automáticamente en A2 (la modificación 'fluye' desde A1 a A2.
Cuando recalculas el libro, A2 se recalcula justo después de haberlo hecho A1. A esto se le llama 'cadena de dependencia'.
Las hojas de cálculo grandes pueden tener cadenas de dependencia muy largas y complejas compuestas por cientos de miles de celdas en otras hojas u otros libros. Para que todo funcione correctamente y cada celda muestre el valor exacto, Excel utiliza lo que se conoce como 'árboles de dependencia'.
¿Qué son los árboles de dependencia?
Un árbol de dependencia es como un diagrama de flujo que muestra cómo se interconectan todas las celdas dependientes.
Gracias a estos árboles de dependencia, cuando cambias el valor de una celda, Excel conoce qué celdas dependen de ella y así puede recalcular de forma inteligente solo las celdas afectadas.
Esto significa que ante un cambio en la hoja, Excel no recalcula a ciegas todo el libro, sino únicamente la parte dependiente de la celda donde se hace el cambio.
Te pongo un ejemplo. Imagina una celda dependiente de varias (no depende, sino es dependiente de). Supongamos que haces una modificación en esta celda. Gracias al cálculo inteligente, Excel solo recalcula el valor de la celda modificada. No tiene que recalcular todo el libro. Otra cosa sería que modificaras, por ejemplo la celda B8 de la imagen anterior. En ese caso Excel recalcularía la propia celda B8 y la celda D10 porque se encuentra en su cadena de dependencia.
Si la cadena de dependencia es lo suficientemente larga es probable que aparezca la palabra 'Calculando...' en la barra de estado mientras Excel recorre todas las celdas a recalcular.
La volatilidad ignora los árboles y las cadenas de dependencia
Aquí viene lo importante: Excel cuenta con algunas funciones volátiles que se recalculan automáticamente cada vez que se realiza alguna acción, incluso si la celda modificada se encuentra fuera de cualquier cadena de dependencia.
Esto significa que si estás trabajando con una plantilla muy grande que contiene funciones volátiles (y si estas tienen muchas fórmulas que dependen de ellas), cada vez que hagas un cambio en la hoja, se recalculará la función volátil y en consecuencia, las fórmulas que dependen de ella.
Esta es la razón por la que debes vigilar las fórmulas dependientes de funciones volátiles porque esa 'volatilidad' ignora los árboles y cadenas de dependencia que puedan existir en el libro.
Algunas de las acciones que desencadenan el cálculo de las funciones volátiles son:
- Eliminar o insertar una fila o columna.
- Realizar ciertas acciones con Autofiltro.
- Hacer doble clic en los separadores de los encabezados de filas o columnas para redimensionarlas.
- Insertar, editar o eliminar un nombre definido.
- Renombrar una hoja.
- Cambiar el orden de una hoja desde las pestañas inferiores.
- Ocultar y mostrar filas (columnas no).
¿Qué funciones son volátiles?
La respuesta a la pregunta del millón es:
- AHORA
- HOY
- ALEATORIO, ALEATORIO.ENTRE y MATRIZALEAT
- DESREF
- INDIRECTO
- INFO (dependiendo del argumento introducido)
- CELDA (dependiendo del argumento introducido)
Si eres un Exceler medio o avanzado estoy seguro de que has utilizado algunas de ellas en tus plantillas.
Cuándo comienzan a ser importantes las funciones volátiles
La mayoría de las plantillas donde se utilizan estas funciones suelen ser tan pequeñas que quizá no notes los recálculos relacionados con la volatilidad. En estos casos no tienes por qué prestarlas atención. Sin embargo en hojas de cálculo grandes que en las que el rendimiento disminuye al introducir datos es donde debes prestar atención.
El cálculo manual es un arma de doble filo
A menudo los libros de Excel se vuelven tan lentos que muchas personas deciden cambiar la configuración del cálculo a 'Manual' (en la ficha Fórmulas) mientras hacen las modificaciones y luego vuelven a activar el cálculo automático cuando terminan.
Atención: esta práctica es muy peligrosa, pues hay muchas posibilidades de no volver a activar el cálculo automático y estés trabajando con datos obsoletos. Pero esto no es todo: cuando abres dos libros (uno con el cálculo manual y el otro con cálculo automático), ambos tendrán el modo de cálculo del primer libro abierto. He visto muchos casos de estos y te aseguro que es muy frustrante.
¿Cómo evitar las funciones volátiles?
Una de mis frases favoritas es "En Excel siempre hay varias formas de hacer la misma tarea". Y en esta ocasión también es así. Las funciones DESREF e INDIRECTO se pueden sustituir por otras funciones no volátiles como INDICE y COINCIDIR.
Para sustituir la función HOY puedes utilizar VBA para crear una función personalizada que no cambie.
En caso de que no encuentres alternativas a las funciones volátiles te sugiero que plantees la consulta en el foro de la web.
Conclusión
Realmente pocas hojas de cálculo son lo suficientemente complejas como para fijarse en si las funciones utilizadas en ellas son volátiles o no. Normalmente (bueno, según la velocidad del equipo) son necesarias un par de decenas de miles de fórmulas con funciones volátiles para notar la ralentización que provocan.
Si te encuentras trabajando en un archivo con estas características te recomiendo evitar funciones como HOY o INDIRECTO, pues son las culpables de la lentitud en el cálculo.
