Cómo funciona la auditoría de fórmulas de Excel

La sección de auditoría de fórmulas de Excel permite, con distintas funcionalidades, detectar y corregir errores en un libro Excel. A pesar de que Excel nos ayuda a completar las fórmulas, pueden existir errores, especialmente si el libro es muy extenso y contiene mucha formulación y relaciones entre celdas. La auditoría de fórmulas son el conjunto de herramientas que Excel pone a nuestra disposición para detectar relaciones entre celdas y errores en las funciones y fórmulas.

Para empezar, en esta sección tenemos tres funcionalidades que nos permiten detectar relaciones entre celdas. Para usarlas sólo debemos situar el selector de celda en la celda que queremos evaluar y seleccionar una de las tres opciones del menú de Auditoría de Fórmulas que encontrarás en la sección de Fórmulas:

• Rastrear precedentes: Esta opción nos permite detectar todas aquellas celdas a las que hace referencia una fórmula. En este ejemplo tenemos la función SUMA en la celda A5 que suma el rango A1:A3, si situamos el selector de celda en la celda A5 y seleccionamos la opción rastrear precedentes se activarán los recuadros y flechas que indican las celdas y rango afectados por la fórmula.

• Rastrear dependientes: Permite, de forma gráfica, ver las celdas que se ven afectadas por la celda seleccionada. Siguiendo con el ejemplo anterior, en la celda A2 tenemos una referencia a la celda C2. Si situamos el selector de celda en la C2 y seleccionamos la opción rastrear dependientes se activarán los gráficos de dependencia.

• Quitar flechas: Permite limpiar la hoja de las flechas generadas por el rastreo de precedentes y dependientes. Podemos eliminar todas las flechas, las de dependencia o las precedentes en función de la opción seleccionada.

• Mostrar fórmulas: Siguiendo con las opciones de este menú tenemos una muy práctica, la de mostrar fórmulas que se activa seleccionando el botón fx con un triángulo. En este caso, en lugar de ver los resultados de las fórmulas en la hoja, lo que veremos son las fórmulas, algo muy práctico cuando tenemos que detectar errores o cuando queremos analizar la estructura de fórmulas. En nuestro ejemplo veríamos lo siguiente:

• Comprobación de errores: Esta opción de este menú es la que nos permite rastrear y comprobar errores. Antes de entrar en los detalles de la funcionalidad de esta opción tenemos que hablar de qué tipos de error nos podemos encontrar en Excel:
  • #¡VALOR!: Sucede cuando la fórmula tiene un valor inválido. Aquí la variedad de situaciones que nos podemos encontrar es muy amplia, pero un ejemplo sencillo es cuando intentamos sumar tres celdas, pero una de ellas tiene texto en lugar de números.
  • #¡DIV/0!: También muy habitual, cuando intentamos hacer una división y el denominador es cero, Excel devuelve este mensaje.
  • #¿NOMBRE?: Este error ocurre cuando no hemos escrito correctamente el nombre de la función o alguno de sus argumentos. Por ejemplo, si en lugar de =BUSCARV(…) ponemos =BUSCARB(…), tendremos este error.
  • #¡NUM!: Excel da este error cuando la fórmula o función contiene valores numéricos no válidos como, por ejemplo, cuando intentamos calcular la raíz cuadrada de un número negativo con la función RAIZ.
  • #¡N/A!: Este error de Excel lo veremos en fórmulas de búsqueda y referencia cuando Excel no encuentra la coincidencia que estamos pidiendo. Por ejemplo, si con la función BUSCARV queremos buscar las ventas de María en una matriz de datos de vendedores y no hay ninguna María en la matriz.
  • #¡NULO!: No es de los más habituales, pero este error de Excel se da cuando usamos un operador de rango incorrecto en una función o cuando usamos un operador de intersección entre dos matrices que no se entrecruzan.
  • #¡REF!: Este error de Excel salta cuando hacemos referencia a una celda o matriz que ya no existe. Por ejemplo. Si queremos sumar la casilla A1 de la Hoja 1 con la casilla A1 de la Hoja 2 y hemos eliminado la Hoja 2, la fórmula =Hoja1!A1+Hoja2!A2 dará el error #REF!.

Para ver cómo funciona la comprobación de errores hemos generado uno en el ejemplo anterior. En lugar de utilizar la fórmula suma, hacemos la suma por celdas en la C5 (A1+A2+A3) y en la celda A3 hemos puesto una letra en lugar de un número y tenemos el error #VALOR. Si seleccionamos la opción de comprobación de errores se abrirá el siguiente cuadro de diálogo:

La ayuda sobre este error se activa con el botón de la derecha y es un enlace al sitio oficial de Microsoft con ayuda sobre el tipo de error que se nos ha generado, en este caso sobre #VALOR.

Podemos usar la opción mostrar pasos de cálculo, que nos abre el evaluador de fórmulas al que también podemos acceder desde el menú de auditoría de fórmulas. Nos permite evaluar cómo está calculando Excel, paso a paso, la fórmula analizada. Es muy práctico para ver dónde no está calculando bien, especialmente en fórmulas con muchas dependencias. El cuadro de diálogo de esta opción es el siguiente:

Para comprobar los errores tenemos dos opciones, la opción de evaluar y las opciones de paso a paso para entrar y paso a paso para salir. La opción de Evaluar nos va dando el resultado de cada una de las referencias que se va subrayando en la fórmula, de forma que podemos entrar y salir de toda la fórmula con este botón.

Cuando Excel se encuentra una referencia a una celda se activan las dos opciones de paso a paso para entrar y paso a paso para salir. Cuando seleccionamos la opción de entrar, Excel investigará cómo se calcula el valor de la celda y lo mostrará en un recuadro debajo. Para seguir evaluando paso a paso debes seleccionar la opción "paso a paso para salir" para que se active la siguiente entrada. 

Con la opción de Ignorar error permites a Excel ignorar los errores. También puedes ir a modificar la fórmula o seleccionar "Opciones", que nos llevará al backstage, al apartado de opciones de la sección de fórmulas, que tiene este aspecto: 

Por último, las opciones de "Anterior y Siguiente" nos permiten navegar por los errores de la hoja hacia el anterior o el siguiente que Excel detecte. Es una opción muy práctica cuando estamos arrastrando el mismo error a lo largo de varias fórmulas.

También podemos acceder a la sección de gestión de errores mediante la opción que se habilita en una celda que los contiene. Pinchando sobre el icono de error se abrirá el desplegable para poder ejecutar las mismas acciones que hemos comentado hasta ahora como se muestra en la siguiente imagen.

Para finalizar con la sección de Auditoría de Fórmulas de Excel, tenemos la opción de Ventana de Inspección que nos permite tener una lista de fórmulas para acceder a ellas de forma rápida. Aquí podemos agregar las referencias que queramos con la opción “Agregar inspección”. Siguiendo con en ejemplo anterior hemos agregado a la ventana de inspección las dos fórmulas de la celda C2 y A5.