Herramientas para Auditoría de fórmulas

La barra de auditoría de fórmulas, es una excelente herramienta para controlar y auditar las fórmulas en las hojas. Esta herramienta nos permite realizar un seguimiento de las fórmulas y los datos, así como la localización y control de errores de cálculo que se produzcan en las hojas de nuestro libro.

26-Feb-2016

Auditoría de fórmulas

La barra de auditoría de fórmulas, es una excelente herramienta para controlar y auditar las fórmulas en las hojas. Esta herramienta nos permite realizar un seguimiento de las fórmulas y los datos, así como la localización y control de errores de cálculo que se produzcan en las hojas de nuestro libro.

En la ficha Fórmulas de la cinta de opciones de Excel encontraremos el grupo Auditoría de fórmulas.

Grupo de opciones para la auditoría de fórmulas

Con las herramientas que brinda podremos realizar una revisión sobre los cálculos realizados en la hoja activa.

Primero haremos una breve explicación de cada una de las posibles opciones que nos ofrece este grupo de herramientas y luego mostraremos un ejemplo práctico de la utilización del rastreo de fórmulas.

Rastrear precedentes.

Rastrear precedentes

Muestra flechas que indican las celdas que afectan al valor de la celda seleccionada actualmente. Para rastrear las celdas que proporcionan directamente valores a la fórmula de la celda activa, haremos clic de nuevo en el botón Rastrear precedentes.

Rastrear dependientes.

Rastrear dependientes

Muestra flechas que indican las celdas afectadas por el valor de la celda seleccionada actualmente. Para agregar niveles adicionales de dependientes indirectos, haremos clic de nuevo en el botón Rastrear dependientes.

Quitar flechas.

Quitar flechas de rastreo

Quita las flechas trazadas por rastrear precedentes o por rastrear dependientes.

Mostrar fórmulas.

Mostrar fórmulas

Muestra la fórmula en cada celda, en lugar del valor resultante.

Comprobación de errores.

Comprobación de errores

Busca errores comunes en las fórmulas, indicándonos donde se encuentra, ofreciéndonos ayuda sobre porque y como solucionar el error o la opción de modificar la fórmula para corregirlo.

Evaluar fórmula.

Evaluar fórmula

Muestra el cuadro de diálogo Evaluar fórmula, para depurar una fórmula, evaluando cada parte de la fórmula individualmente.

A medida que pulsemos el botón Evaluarsustituye la referencia subrayada por el valor correspondiente.

A medida que pulsemos el botón Paso a paso para entrarirá mostrando las fórmulas de las celdas precedentes.

Ventana inspección.

Ventana de inspección

Supervisa los valores de determinadas celdas al mismo tiempo que se realizan cambios en la hoja.

Los valores se muestran en una ventana separada, que se mantiene visible independientemente del área que se muestre del libro.

Ventana de Inspección (seguimiento de referencias)

Ejemplos sobre auditoría de fórmulas

Ejemplo de Comprobación de errores

Tenemos una hoja donde se calcula el importe de los trabajos de mantenimiento de una red local realizados para una empresa.

Para realizar la factura se han utilizado los valores del ‘Coste de Material’, ‘Importe Horas’ y ‘Desplazamiento’.

Ejemplo comprobación de errores

Como se puede observar el valor del IVA y Total de la factura, presentan errores de cálculo de tipo #VALOR.

Utilizaremos el botón ‘Comprobación de errores’, para que Excel nos indique donde se localiza el error y así actuar en consecuencia (en este caso resulta muy evidente donde está localizado).

Se abrirá el cuadro de dialogo indicándonos que se ha localizado un error en la fórmula introducida en la celda D19, indicándonos que uno de los valores utilizados para el cálculo no es correcto. Si pulsáramos el botón ‘Siguiente’, nos mostraría el siguiente error localizado en la celda D21.

Ejemplo comprobación de errores

Pulsaremos sobre el botón ‘Modificar en la barra de fórmulas’ para realizar la corrección de las referencia de la celda C19, que es la causante de este error.

Comprobación de errores

Después de realizar la corrección, pulsaremos el botón ‘Reanudar’ para que Excel vuelva a hacer una comprobación de los posibles errores de cálculo que aún queden en la hoja.

Podemos observar como al corregir el error de la celda D19 el siguiente error de la celda D21 también se ha corregido ya que es una celda dependiente del valor de la celda D19.

Comprobación de errores

Ejemplo de Evaluar fórmulas

Tomando la hoja del ejemplo anterior, vamos a evaluar la fórmula introducida en la celda D17.

Seleccionamos la celda D17 y pulsamos el botón ‘Evaluar fórmula’. Se abre el cuadro de diálogo  mostrándonos la fórmula escrita en la celda y dejando subrayada la primera referencia de la fórmula.

Ejemplo Evaluar fórmulas

Si pulsamos el botón ‘Evaluar’, se sustituirá la referencia por su valor. En nuestro caso el valor de la celda D16.

Ejemplo evaluar fórmulas

Si por el contrario, si hubiéramos pulsado el botón ‘Paso a paso para entrar’ nos mostraría, qué fórmula está escrita en la celda D16. Si la celda que esta subrayada no proviene de ninguna fórmula, simplemente nos mostraría el valor de la celda.

Ejemplo evaluar fórmulas

Ejemplo de Rastreo de celdas

En este ejemplo mostraremos el rastreo de celdas precedentes y dependientes en un libro de Excel de dos hojas de cálculo.

Precedentes son celdas que afectan al valor de la celda inspeccionada. Dependientes son las celdas afectadas por la celda seleccionada. Veamos esto con un ejemplo:

Ejemplo rastreo de celdas

En la hoja Costes tenemos una tabla de precios de transporte en función del peso del artículo.

Ejemplo rastreo de celdas

En la hoja Artículos tenemos la lista de los artículos con sus pesos.

Ejemplo rastreo de celdas

En la celda F2 tenemos de la hoja ‘Costes’ una lista desplegable con Validación de Datos, basada en la lista de artículos de la hoja ‘Artículos’.

Ejemplo rastreo de celdas

En la celda F3 ponemos la fórmula =BUSCARV(F2;tabla_articulos_pesos;2;0) que nos da el peso del artículo que aparece en la celda F2. Como matriz de búsqueda hemos utilizado un nombre de rango creado previamente que hace referencia a las columnas nombre y peso de la lista de artículos.

En la celda F4 introducimos la fórmula =BUSCARV(F3;tabla_pesos_coste;2;1) para determinar el coste del transporte de acuerdo al peso del artículo.

Seleccionamos A7 (o cualquier celda en el rango A2:A8) y accionamos la opción Rastrear dependientes.

Ejemplo rastreo de celdas

Excel traza una flecha que indica que la celda F4 es afectada por el valor de la celda A7.

Ejemplo rastreo de celdas

Ahora seleccionamos la celda F4 y pulsamos Rastrear Precedentes.

Ejemplo rastreo de celdas

Vemos que F3 es precedente de F4 (afecta su valor) y también todo el rango A2:B8 que además de la flecha aparece enmarcado con un borde de color azul.

¿Qué pasa cuando las celdas precedentes están en otra hoja? Ese es el caso de la fórmula en la celda F3 que es afectada por los valores de la tabla artículos en la hoja Artículos.

En ese caso Excel señala que se trata de una referencia remota poniendo una flecha y en su extremo el símbolo de una tabla.

Para ver las referencias a las celdas precedentes tenemos que apuntar con el ratón a la flecha (la figura del ratón cambia de una cruz a una flecha) y hacer un doble clic.

Ejemplo rastreo de celdas

Esto abrirá el cuadro de diálogo ‘Ir A’, donde podemos ver la lista de las celdas de la hoja ‘Artículo’ que afectan a la fórmula.

Podemos elegir una de las celdas de la lista y pulsar aceptar para ir al rango en cuestión.

Si deseamos quitar las flechas de rastreo, tanto precedentes como dependientes pulsaremos el botón ‘Quitar flechas’.