A veces tenemos que buscar errores en un libro de Excel o entender como funciona. Con la auditoría de fórmulas de Excel será muy sencillo.
Si tenemos un libro sencillo, esto no será muy difícil. Pero cuando el libro empieza a ser relativamente grande, con muchas fórmulas interdependientes o, aún peor, no lo hemos hecho nosotros y, por tanto, no tenemos ni idea de como está construido, la cosa puede ser realmente complicada.
Herramientas de auditoría de fórmulas Excel
Excel pone a nuestra disposición una serie de herramientas que nos permiten facilitar la tarea de revisar las fórmulas, las interrelaciones entre celdas y fórmulas e, incluso, entender el funcionamiento de algunas fórmulas que cuando las miramos por primera vez, nos dan ganas de hacer cualquier cosa menos intentar saber como funciona.
Las herramientas de que disponemos son las siguientes:
- Rastrear precedentes
- Rastrear dependientes
- Mostrar fórmulas
- Comprobación de errores
- Evaluar fórmula
Rastrear precedentes
La primera de las herramientas de auditoría de Excel con la que contamos es la de Rastrear precedentes. Con esta opción podemos ver a que celdas hace referencia la fórmula de la celda en que estemos situados. Esto se identifica por medio de unas flechas que, partiendo de dicha celda, van hasta las celdas de que depende.
Veamos esto con un ejemplo sencillo. En un libro nuevo con dos hojas, introduzcamos valores numéricos en las celdas A2 y B2 de la Hoja1 y en la celda B2 de la Hoja2. En la celda B5 introduzcamos la fórmula =B1+B2+Hoja2!B1 que será la celda de la que rastrearemos precedentes.
Si nos situamos en B5 y hacemos click en Fórmulas, Auditoría de fórmulas, Rastrear precedentes, veremos que nos aparece lo siguiente:
Vemos que hay una flecha azul que entra en la celda B5 desde la celda A2, otra flecha azul entrando en la celda B5 desde la celda B2 y otra flecha discontinua que apunta a otra hoja. Las celdas precedentes de la misma hoja quedan perfectamente identificadas.
Ahora, si hacemos doble click sobre la flecha discontinua, veremos que nos aparece en menú Ir a y dentro de éste la referencia de la otra hoja, en este caso [AuditoríaDeFórmulas.xlsm]Hoja2!$B$1, que es la otra celda que interviene en nuestra fórmula.
De esta forma, podríamos trazar que hace la fórmula o donde se origina un posible error.
Rastrear dependientes
El funcionamiento de Rastrear dependientes es similar al descrito para Rastrear precedentes solo que en este caso, lo que se mostrará son las celdas que dependen de la seleccionada.
En nuestro caso, si seleccionamos la celda A1 de la hoja A1, veremos que aparece una flecha azul que va desde dicha celda A1 hasta la celda B5 que es donde tenemos la fórmula que utiliza A1 para obtener el resultado.
Si ahora hacemos la prueba desde la celda B2 de la Hoja2, al igual que antes, veremos una flecha discontinua que parte de dicha celda y apunta a una imagen de una hoja.
Y de la misma forma, si hacemos doble click sobre la flecha discontinua, nos aparece el menú Ir a indicando que, en este caso, la celda dependiente es [AuditoríaDeFórmulas.xlsm]Hoja1!$B$5.
Mostrar fórmulas
La opción Mostrar fórmulas es otra de las utilidades de auditoría de fórmulas de Excel que nos permite cambiar la visualización del contenido de las celdas entre el resultado, que es lo que normalmente vemos, y las fórmulas. Esto facilita el seguimiento de dependientes y precedentes para ir trazando una fórmula ya que vemos que celdas contienen simplemente valores y cuales fórmulas que tal vez nos interese seguir.
Vamos a modificar ligeramente nuestra hoja añadiendo en C2 una fórmula que sea =1/D2 y en D2 un nuevo valor. La fórmula de la celda B5 también la vamos a cambiar para que sume, además, el resultado de la celda C2. Nos quedará dicha fórmula como =A2+B2+C2+Hoja2!B1.
La Hoja1 queda, por tanto así:
Si hacemos click en la opción Mostrar fórmulas de Auditoría de fórmulas, lo que veremos será lo siguiente:
Donde podemos ver claramente que A2, B2 y D2 contienen valores y C2 y B5 fórmulas.
Combinar Mostrar fórmulas con Rastrear precedente
Si combinamos la opción de Mostrar fórmulas con la de Rastrear precedentes y vamos haciendo click aguas arriba de la formula, es decir, primero estando situados en la celda B5 y después en la C2, que es otra fórmula (las celdas de valores, lógicamente, no tienen precedentes), vemos que el mapa de como se obtiene B5 queda bastante claro al visualizar varios niveles de precedentes:
Podemos ver que B5 se construye desde A2, B2 y C2 y que, esta última a su vez, se construye desde D2.
Comprobación de errores
Dentro del menú de Auditoría de fórmulas de Excel, disponemos también de la opción Comprobación de errores. Con esta herramienta podemos trazar un error hasta su origen.
Vamos a provocar un error para ver como funciona esta búsqueda del origen de error. Para ello, introduzcamos un cero en la celda D2. Esto provoca un error de división por 0 en la celda C2 y se trasmite a la celda B5 ya que, para su calculo, se suma C2.
Vemos que tanto la celda C2 como la B5 muestran el error #¡DIV/0!.
Si nos situamos sobre la celda B5 y hacemos click en Comprobación de errores, nos aparecerá la siguiente ventana:
Vemos que nos informa de que hay un error en dicha celda B5, nos enseña la fórmula de dicha celda y nos especifica que es un error de división entre 0.
Si ahora hacemos click sobre el botón Seguimiento de error, veremos que nos indica con unas flechas las celdas precedentes. El color de las flechas, además, nos muestra que la relación conla celda C2 es la que está provocando el error en la B5 y que la C2, a su vez, tiene a la D2 como precedente.
A este mismo resultado hubiésemos llegado haciendo click en la opción Rastrear error que se muestra al desplegar las opciones disponibles en Comprobación de errores.
Llegados a este punto, lo mejor es que experimentes con las opciones que te permite el rastreo de errores hasta que te sientas cómodo en su manejo.
Como puedes ver, también existe una opción para rastrear las referencias circulares. Está nos mostrará un listado de aquellas celdas cuyos cálculos dependan entre ellas mismas y, por tanto, generen una indefinición.
Hay que tener en cuenta, en este caso, que como al detectar una referencia circular Excel detiene los cálculos para las celdas afectadas, las celdas afectadas por referencias circulares se van mostrando por grupos y hasta no resolver una, no mostrará otra.
Evaluar fórmulas
La última opción disponible en el menú de Auditoría de fórmulas de Excel es la de Evaluar fórmulas.
Hay veces que tenemos que crear fórmulas relativamente complejas, que van insertando como argumentos otras fórmulas cuyos resultados son después usados como parámetros de otras.
Otras veces, recibimos libros con fórmulas hechas por otros, que necesitamos (o nos apetece) entender como están construidas y como funcionan.
Con la opción de Evaluar fórmula podremos ver, paso a paso, como se calcula la fórmula sobre la que estemos situados y nos permitirá así, entender su comportamiento y localizar en que momento y parámetro se puede estar produciendo un error.
Para usarla, sitúate sobre la celda en que esté la fórmula que quieras analizar y haz click en Evaluar fórmula. Te aparecerá una ventana donde podrás ver la fórmula completa.
En el ejemplo puedes ver la evaluación de la fórmula =SUMAPRODUCTO(–((B$5:C$5)=»Agregar»);(B$3:C$3);(B10:C10))+SUMAPRODUCTO(–((B$5:C$5)=»Aggregate»);(B$3:C$3);(B10:C10)).
No es una fórmula muy compleja cuando la escribes y sabes lo que quieres. Pero si te la ponen delante «en frío», puede asustar un poco.
En la imagen del ejemplo, puedes ver que hay una parte de la fórmula a evaluar subrayada. Eso indica que esa parte, es la que se evaluará en primer lugar. Si haces click en Evaluar, la expresión de la fórmula es sustituida por el resultado de dicha evaluación y pasa a estar subrayada la siguiente parte de la fórmula a evaluar, por el orden en que Excel hace dicha evaluación. Con clicks sucesivos en Evaluar iremos avanzando hasta alcanzar el resultado final y entender así como se llega al mismo.
Cuando la evaluación de la fórmula haya llegado al final, el botón de evaluar pasará a llamarse Reiniciar, por si quisieras repetir los pasos de nuevo en el caso de que algo no hubiese quedado claro del todo.
Conclusiones
Hemos visto en este artículo las herramientas de que disponemos para poder realizar una auditoría de fórmulas de Excel.
Estas herramientas son realmente fáciles de utilizar y no te llevará mucho tiempo familiarizarte con ellas para sacarles todo el partido.
Son especialmente necesarias y útiles si quieres sacar de Excel algo mas de partido que usarlo como una simple calculadora sencilla con un pobre editor de texto incorporado, así que, ¡no pierdas el tiempo y ponte ahora mismo a practicar con ellas!
Puedes consultar otras cosas útiles en el foro específico de consejos y utilidades donde periódicamente, y cuando el tiempo disponible me lo permite, voy poniendo pequeñas píldoras que creo pueden resultar interesantes.
Si te ha interesado el artículo o si te has quedado con alguna duda, tienes a tu disposición de la sección de comentarios un poco mas abajo y pásate por el foro para dejar tu consulta.
¡Hasta la próxima!
