Funciones de Auditoría de Fórmulas
Las herramientas de auditoría de fórmulas nos son de gran utilidad en aquellos momentos donde nos vemos abrumados por la cantidad de cálculos en nuestras hojas. Cuando todo parece ir bien, realizamos cálculos, utilizamos estos resultados para obtener otros, y así hasta llegamos a la verdad que estábamos buscando dentro de nuestros datos. Sin embargo, ¿Qué haces si encuentras un error en tu última fórmula? Verificas sus argumentos, y compruebas ¿estás haciendo referencia a las celdas correctas? Estas celdas, ¿contienen a su vez los valores correctos?
Más pronto que tarde, puedes hallarte en un nudo yendo y viniendo entre tus fórmulas. Afortunadamente, tenemos algunas herramientas dentro de Excel que nos pueden ayudar a ordenarnos un poco, y que reciben el nombre de “Auditoría de fórmulas”, pero que para nuestro caso, podemos entenderlo como una forma de explorar y comprobar los procesos de cálculo que conducen a nuestros resultados.
Dependencias y Precedencias
En Excel, se establece una referencia cuando ponemos que el valor de una celda es igual al valor de otra, o cuando utilizamos como argumentos de nuestras fórmulas los valores de otras celdas. Esto nos permite ahorrar tiempo de transcripción de estos valores, a la vez que evita errores en los que podamos incurrir en este proceso, y mantiene nuestras hojas dinámicas y flexibles, ya que las fórmulas se actualizan inmediatamente para reflejar los resultados correctos con cada valor que se actualiza.
Ejemplos de referencias:
=A1
=SUMA(A1:A10)
=PROMEDIO(D4:D32)
Ahora, cuando creamos una referencia, estamos creando relaciones entre las celdas. Estas relaciones son de dependencia o de precedencia, pero estamos hablando del mismo par en distintos sentidos de la relación:
En esta fórmula sencilla:
El valor de J3 depende de J1 y J2. J1 y J2 son precedentes de J3.
- Una celda depende de otra cuando su resultado varía en función del valor de la celda a la que hace referencia
- Una celda es precedente de otra cuando su resultado es utilizado por la fórmula de otra celda
La idea central es que las celdas en cuestión están relacionadas, el nombre de esta relación dependerá de dónde nos paremos y hacia dónde miremos.
La forma de evidenciar estas relaciones es, ya sea consultando la barra de fórmulas y leyendo las referencias, o utilizando las herramientas de Fórmulas > Auditoría de Fórmulas > Rastrear precedentes o Rastrear dependientes.
Si nos posicionamos en una celda que contiene una fórmula, y hacemos click en alguna de ellas, dependiendo el tipo de relación existente, se dibujarán flechas sobre nuestra hoja de cálculo que muestran el sentido de la relación.
El ejemplo anterior es sencillo, y puede parecer redundante. Pero el rastreo de precedencia y dependencia es muy útil en libros complejos, donde tenemos relaciones a lo largo de gran cantidad de celdas e incluso entre distintas hojas. Mira el siguiente caso:
El valor de D16 depende de D7, y de un valor que se encuentra en otra hoja. La barra de fórmulas cuenta la misma historia:
=D7*Ingresos!E18
Si hacemos click en la flecha negra punteada, tendremos la opción de ir a visitar dicha referencia siguiendo un vínculo, sin tener que memorizarla, abrir la hoja en cuestión y buscarla allí. Por otro lado, el resultado de D16 es utilizado por las funciones de las celdas L16 y D23, que calculan los totales por filas (Costos de Distribución totales del año 3) y columnas (Beneficios Operativos del año 3). Vemos cómo comienza a ponerse más complejo. Puedes continuar explorando sucesivamente “aguas arriba” o “aguas abajo” hasta poder contar la historia completa de un resultado determinado.
Si quieres eliminar las flechas, basta con que presiones el botón Quitar flechas, debajo. También puedes quitar solo las precedentes o dependientes.
Mostrar Fórmulas
Otra opción interesante, es la que nos presenta el botón “Mostrar Fórmulas”. Al tocar sobre el mismo, todas las celdas que contienen algún tipo de fórmula o función cambian para revelarla:
Con esto, podremos ver rápidamente qué celdas contienen fórmulas y cuáles valores estáticos, y podremos verlas a todas al mismo tiempo para compararlas sin necesidad de ir una a una mirando la barra de fórmulas. Una opción más que interesante para explorar el libro de un vistazo si queremos confirmar el proceso de cálculo sin concentrarnos aún en los resultados.
Para volver a la vista tradicional, ocultando las fórmulas, basta con presionar en Mostrar fórmulas nuevamente.
Evaluar fórmula
Esta botón es de mis favoritos. Cuando anidamos fórmulas (es decir, cuando utilizamos fórmulas como argumentos de otras fórmulas sucesivamente), puede volverse complicado interpretar la secuencia de solución o predecir el resultado. Por suerte, podemos pedirle a Excel que nos cuente cada uno de los pasos que ha dado para resolver una fórmula. Al hacer click en Evualuar fórmula posicionados sobre una celda, y presionar sucesivamente Evaluar, Excel realizará un paso sencillo de cálculo, transformando referencias y utilizando los resultados anteriores en cada paso, hasta llegar al resultado final.
