Descomposición de la fórmula de la celda de Excel
Recientemente me he visto en la necesidad de revisar varios libros Excel complejos en los que existían celdas con múltiples fórmulas SI anidadas, haciendo realmente complejo entender a simple vista la expresión. Aumentando la dificultad por el uso de referencias a celdas desplazadas. En libros pequeños, con una única hoja, posiblemente se pueda comprender lo que quería hacer el autor original solamente con un poco de paciencia. Pero cuando los libros son grandes viene bien algo de ayuda, como el add-in FormulaSpy de FormulaDesk que encontré hace poco para auditar las fórmulas en Excel. Un add-in con el que se puede explorar en detalle las fórmulas y realizar otras validaciones de forma automática.
En primer lugar, vamos a crear una hoja de cálculo con algunas expresiones en las que existen varias funciones SI anidadas junto a referencias a celdas desplazadas. Expresiones que solamente tienen la idea de mostrar la complejidad de la operación.
En la celda se puede ver una función SI.ERROR en la que existe tres SI anidados, cuya condición depende de los valores de celdas desplazadas (DESREF). Saber por qué en la celda D7 se obtiene el valor 5 y no otro puede llevar su tiempo. Algo que se puede solucionar con el uso de FormulaSpy.
El add-in FormulaSpy se puede descargar de la página web del fabricante en instalarlo en nuestro ordenador. Operación que se realiza fácilmente a través de un asistente, como es habitual en Windows. Una vez hecho esto aparecerá en una nueva pestaña con el nombre “FormulaDesk” en la cinta de Excel.
En esta nueva pestaña posiblemente la opción más interesante es “Formula Explorer”. Una opción con las que se nos mostrará una ventana como una descomposición de la fórmula que se ha ejecutado en la celda, incluyendo los valores intermedios.
De esta forma se puede entender con mayor facilidad el resultado de la celda. Pudiéndose ver a simple vista los resultados intermedios de todas las operaciones y los valores en los que se basan estos.
La información también se puede ver en forma de árbol, aunque personalmente no es mi vista favorita.
Una vista que sí me gusta es poder ver la expresión en un formato de texto similar a como se implementaría la operación en Python, R o Julia. Algo que me facilita mucho comprender lo que el usuario original quería conseguir al escribir la fórmula.
Finalmente, también se puede incluir información de las celdas de las que se obtienen los datos.
Una cosa que también me gusta de este add-in es la posibilidad de saber dónde se produce el error en una fórmula. Por ejemplo, si tenemos varios sumatorios y en uno de ellos hay una división por cero Excel solamente indica que hay una división por cero. Pero con el explorador del add-in se puede saber cuál de ellas es la que produce el error. Pudiendo solucionar el problema de una forma mucho más eficiente. Algo que se puede ver en la siguiente captura de pantalla.
Una cosa que suele ser molesta a la hora de abrir una hoja de cálculo es que los formatos no sean coherentes. Emplear diferentes fuentes de varios tamaños o estilos de una forma que parece aleatoria puede dar la falsa sensación de que el trabajo realizado se ha realizado de una forma poco cuidadosa. Lo que posiblemente no sea así. Para solucionar esto el add-in también cuenta con la opción “Style Explorer”, con la cual se pueden ver los estilos empleados y corregir estos para evitar que una celda destaque visualmente sin necesidad.
Finalmente, una opción que también me parece extremadamente útil para auditar las fórmulas en Excel es “Workbook Detective”. Una función con la que se pueden localizar automáticamente algunos de los errores más habituales como números almacenados como texto, fórmulas incoherentes o referencias a celdas vacías.
Algunos de estos errores, como las fórmulas inconsistentes, también las muestra visualmente Excel. Aunque el autor original del libro puede que indicase a Excel ignorar este error o, en el caso de libros grandes, es fácil que se no los observamos en una revisión visual.
Auditar las fórmulas en Excel es algo que puede parecer sencillo, pero no es así a medida que aumenta la complejidad de los libros. No saber de dónde vienen los datos o la existencia de fórmulas incoherentes puede hacer que sea realmente costoso comprender que hace la hoja de cálculo. Para ayudarnos en esta tarea podemos usar el add-in FormulaSpy, gracias al cual es más fácil comprender el funcionamiento de libros de Excel que no han sido creados por nosotros o aquellos que creados por nosotros hace tiempo.
Quizás la única pega que le pondría al complemento es que este no funcione en Excel para Mac, aunque este sea un problema habitual con los add-in.
Hace poco publiqué una entrada en la que trataba de un sesgo bien documentado: aferrarse…
En un entrada previa explicamos qué son el WOE y el IV y por qué…
Seguimos evolucionando el laboratorio de Analytics Lane y hoy lanzamos la versión 1.1, disponible en:…
“El interés compuesto es la octava maravilla del mundo. El que lo entiende lo gana…
Tienes los datos de ventas de tres productos en dos años distintos y quieres saber…
Imagina la situación. Tu equipo lleva tres años con un modelo en producción. No es…
This website uses cookies.