E16_UD14

Vista previa del material en texto

UNIDAD - AUDITORÍA DE FÓRMULAS 
 
Auditoría de fórmulas, cómo calcula Excel 
Cuando trabajamos con hojas de cálculo de Excel, habitualmente los cálculos se realizan de una 
manera automática. 
 
De esta forma, cuando ponemos en una celda una función y pulsamos enter se obtiene el 
resultado de una manera automática. O si en una tabla varío un campo, en este por ejemplo en 
lugar de 1 pongo 1,5, vemos que recalculan el resto de campos de manera automática. 
 
Este automatismo lo podemos modificar. Vamos a la pestaña “fórmulas”, en el botón “opciones 
para el cálculo”, tenemos un pequeño botón para “calcular ahora”. 
 
Recalcula todo el libro en este momento, todas las hojas del libro. 
En cambio, el botón “calcular hoja” va a recalcular únicamente la hoja. 
 
Estas dos opciones únicamente se utilizan si tenemos el cálculo automático desactivado. En caso 
de tenerlo activado no es necesario porque se van a recalcular de manera automática. 
Tenemos las opciones para el cálculo. 
 
Tenemos en este caso seleccionado “automático”. Podríamos seleccionar “automático excepto 
en las tablas de datos” o manual, de forma que habría que hacerlo a través de los dos botones 
que hemos visto anteriormente. 
Podemos acceder también a estas opciones a través del menú archivo, opciones y fórmulas, y 
nos salen las opciones de cálculo. 
 
Las mismas que hemos visto hace un momento. Aquí en manual tenemos la opción de marcar 
“volver a calcular libro antes de guardarlo”. También permite crear un cálculo iterativo, pero 
que consume muchos recursos y por defecto no viene habilitado. 
 
Auditoría de fórmulas, mostrar fórmulas 
Cuando trabajamos con hojas de cálculo en Excel, no vemos habitualmente las fórmulas que hay 
en la hoja. Por ejemplo, si nos situamos en la D4, en la barra de fórmulas si la vemos la fórmula, 
pero en la celda sólo vemos el resultado. 
 
Hay una manera para que Excel nos muestre todas las funciones o fórmulas de la hoja. 
Vamos a la pestaña “fórmulas”, y dentro del grupo “auditoría de fórmulas”, tenemos el botón 
“mostrar fórmulas”. 
 
Si lo pulsamos, observamos que en lugar de los resultados ahora nos muestra las fórmulas en 
cada celda. 
 
Si estos resultados hubiesen tenido un formato, también lo quitaría y sólo me mostraría la 
fórmula, porque lo que está mostrando es el contenido real. 
Para quitar esta visualización, volvemos al botón de mostrar fórmulas y lo pulsamos. De esta 
forma tenemos de nuevo nuestra hoja de cálculo con los resultados de las fórmulas. 
 
Autocorrección de fórmulas 
En ocasiones cuando trabajamos con Excel nos aparece un mensaje de error como es en este 
ejemplo. 
 
En él, estamos intentando calcular el promedio de los beneficios que obtengo de mis productos 
y al utilizar nuestra función nos aparece en la celda de resultado el mensaje de error # 
¿NOMBRE? 
En estos casos, tenemos una herramienta en Excel que nos va a permitir rastrear y corregir esos 
errores. 
Si vamos a la pestaña de fórmulas, auditoría de fórmulas, tenemos el botón de comprobación 
de errores. 
 
Nos deja comprobarlos y rastrearlos. 
En este caso vamos a comprobar errores. 
 
Nos aparece una venta que indica que hay un error en la celda B16, que hay un error de nombre 
no válido, que contiene un texto no reconocido. 
Si pulsamos en ayuda sobre este error, nos lleva al navegador y a la ayuda de Excel. 
 
 
 
 
Le damos a reanudar y le decimos “mostrar pasos de cálculo”, nos hace una evaluación y nos 
señala el argumento que es incorrecto, que vemos es el nombre. 
 
Nos hemos equivocado al poner el nombre de la función. Si pulsamos en evaluar, nos dice dónde 
está el error, que es el nombre de la función. 
 
Pulsamos Cerrar 
 
Nos permitiría omitir el error y modificar en la barra de fórmulas. 
 Si hubiese más errores, nos dejaría pasar al siguiente o al anterior. 
Si pulsamos en “modificar en la barra de fórmulas” nos lleva allí, para rectificar ese error. 
Quitaríamos esa “s” y pulsamos “reanudar”. 
Comprueba que no hay más celdas con errores y nos da un mensaje que ha finalizado la 
comprobación de errores en la hoja. 
Esta es la forma que tenemos para corregir los errores en Excel. 
 
Rastrear precedentes y dependientes 
En este apartado vamos a ver cómo podemos en una hoja de cálculo rastrear los precedentes y 
los dependientes de una fórmula. 
Por ejemplo, si seleccionamos beneficio, sabemos que depende del precio kilo y del porcentaje 
que estamos aplicando, y además cuando tenemos ese beneficio, de él va a depender el precio 
neto y el promedio de beneficios. 
 
Vamos a ver cómo podemos rastrear esos precedentes y antecedentes. 
En la pestaña “fórmulas”, dentro de auditoria de fórmulas, tenemos dos opciones: rastrear 
precedentes que nos va a mostrar unas fechas que señalan las celdas que afectan al valor que 
estamos rastreando. Y los dependientes nos van a dar unas flechas que indican las celdas que 
están afectadas por la celda que tenemos seleccionada. 
 
Al pulsar precedentes, nos está marcando que el valor señalado está afectado por los valores de 
la celda C2 y la celda B6. 
 
Si rastreamos los dependientes, nos indica que el precio neto depende de este valor y también 
el promedio de beneficios. 
 
Si queremos eliminar flechas, tenemos la opción “quitar flechas”, que si la pulsamos 
directamente nos quita todas o bien podemos desplegar y quitar precedentes o dependientes. 
 
 En nuestro caso, quitamos todas las flechas. 
 
Rastrear errores 
En ocasiones puede aparecernos un mensaje de error, por un problema con los valores. 
 
Ese mensaje es #¡VALOR!. Vamos a ver cómo podemos rastrear esos errores. 
Si vamos a una celda con error y le decimos que nos lo rastree desde “auditoria de fórmulas”, 
desplegando el botón de los errores, vamos a rastrear error. 
 
Nos inserta unas flechas que nos indican que el error en este caso viene porque estamos 
intentando calcular un valor que viene de un precio por un texto, que no lo reconoce, por eso 
nos da un error de valor. 
 
Quitamos las flechas y nos vamos a otra celda. Rastreamos de nuevo, y en este caso nos indica 
que tenemos un error que proviene de esta otra celda, que a su vez proviene de la celda C2 y de 
la celda C9. 
 
En este caso sólo tenemos que cambiar el texto de “porcentaje” por un valor, en este caso, 25%. 
Ya nos desaparece el error, quitamos las flechas y tenemos nuestra tabla sin errores. 
 
La ventana de Inspección 
 
Vamos a ver una última herramienta de Auditoria de Fórmulas, que es la “ventana de 
inspección”. Nos permite monitorear o inspeccionar determinadas celdas, sin necesidad de 
tener que tenerlas en la pantalla o en la hoja. Permite hacer un seguimiento de los valores o de 
las fórmulas que se encuentren en esas celdas. 
 
Tenemos en nuestro ejemplo un presupuesto personal, con la parte de ingresos en la zona 
superior, la parte de gastos en la zona inferior, los totales y en la parte de abajo un balance 
anual. En este caso tenemos pocos datos, pero es muy habitual tener gran cantidad de datos. 
Y vez que cambiamos algo en la zona superior, no queremos tener que estar desplazándonos a 
la zona inferior para ver cómo afecta a nuestro balance anual. Esto podemos cubrirlo con la 
ventana de inspección. 
En la pestaña de fórmulas, auditoría de fórmulas, pulsamos el botón ventana de inspección. 
 
Nos aparece la ventana y si la arrastramos podemos ponerla sobre la barra de fórmulas o 
podemos dejarla flotando sobre la hoja. 
Nosotros lo que queremos auditar es la celda de balance anual, para tenerla siempre a la vista, 
aunque me mueve por la hoja. 
Pulsamos en agregar inspección, y nos pide la celda cuyos datos quiero inspeccionar. Nos vamos 
a nuestra celda, seleccionamos y agregamos. 
 
 
 
Ya nos aparece en nuestra ventana en una línea con todos sus valores, no sólo el valor en sí, 
también la celda a la quecorresponde, la hoja, el libro y la fórmula que contiene. 
Si ahora nos vamos a nuestra tabla y en derechos y en lugar de 845 pongo 965, vemos que ya 
nos varía el valor en la ventana de inspección sin necesidad de movernos en la hoja. 
 
Es muy cómodo cuando tenemos varios datos, disponer de ella en la zona superior, porque los 
tengo a la vista siempre. Por ejemplo, aquí ponemos 550, vuelve a modificarnos el valor del 
balance anual, que podemos verlo sin necesidad de movernos a la parte inferior de la tabla. 
Si la tenemos flotante, aunque nos movemos por la hoja o por otras hojas, siempre estará visible. 
¿Cómo quitamos una inspección? Simplemente la selecciono y le damos a eliminar inspección.