Herramientas de auditoría de fórmulas en Excel
Como todos sabemos, MS Excel se usa principalmente y es popular por su función, fórmulas y macros. Pero, ¿qué pasa si tenemos algún problema al escribir la fórmula o no podemos obtener el resultado deseado en una celda porque no hemos formulado la función correctamente? Es por eso que MS Excel proporciona muchas herramientas integradas para la auditoría de fórmulas y la solución de problemas.
Las herramientas que podemos utilizar para la auditoría y la resolución de problemas de fórmulas en Excel son:
- Trazar precedentes
- Rastrear dependientes
- Eliminar flechas
- Mostrar fórmulas
- Comprobación de errores
- Evaluar fórmula
Ejemplos de herramientas de auditoría en Excel
Aprenderemos sobre cada una de las herramientas de auditoría anteriores una por una usando algunos ejemplos en Excel.
Puede descargar esta plantilla de Excel de herramientas de auditoría aquí - Plantilla de Excel de herramientas de auditoría# 1 - Trazar precedentes
Suponga que tenemos la siguiente fórmula en la celda D2 para calcular el interés de una cuenta FD en un banco.
Ahora, si queremos verificar los precedentes de la fórmula, podemos presionar F2 para entrar en el modo de edición después de seleccionar la celda requerida, de modo que las celdas precedentes estén bordeadas con varios colores y en el mismo color, se escribe la referencia de celda.
Podemos ver que A2 está escrito con color azul en la celda de fórmula y con el mismo color, la celda A2 está bordeada.
Del mismo modo,
La celda B2 tiene un color rojo.
La celda C2 tiene un color púrpura.
Esta forma es buena, pero tenemos una forma más conveniente de verificar los precedentes de la celda de fórmula.
Para rastrear precedentes, podemos usar el comando 'Rastrear precedentes' en el grupo 'Auditoría de fórmulas' en la pestaña 'Fórmulas' .
Solo tenemos que seleccionar la celda de fórmula y luego hacer clic en el comando 'Rastrear precedentes' . Entonces puede ver una flecha como se muestra a continuación.
Podemos ver que las celdas precedentes están resaltadas con puntos azules.
# 2 - Eliminar flechas
Para eliminar estas flechas, podemos usar el comando 'Eliminar flechas' en el grupo 'Auditoría de fórmulas' en la pestaña 'Fórmulas' .
# 3 - Rastrear dependientes
Este comando se utiliza para rastrear la celda que depende de la celda seleccionada.
Usemos este comando usando un ejemplo.
Supongamos que tenemos 4 cantidades que podemos invertir. Queremos saber cuánto interés podemos ganar si invertimos.
Podemos ver que en la imagen de arriba, hemos aplicado una fórmula para calcular el interés con el Monto 1 y el porcentaje de interés especificado y la duración en el año.
Copiaremos la fórmula y la pegaremos en las celdas adyacentes para la cantidad 2, la cantidad 3 y la cantidad 4. Se puede notar que hemos usado una referencia de celda absoluta para las celdas G2 e I2 ya que no queremos cambiar estas referencias mientras copiar y pegar.
Ahora, si queremos verificar si las celdas dependen de la celda G2. Luego usaremos el comando 'Rastrear dependientes' disponible en el grupo 'Auditoría de fórmulas' en la pestaña 'Fórmulas' .
Seleccione la celda G2 y haga clic en el comando 'Rastrear dependientes' .
En la imagen de arriba, podemos ver las líneas de flecha donde las flechas indican qué celdas dependen de las celdas.
Ahora eliminaremos las líneas de flecha usando el comando 'Eliminar flechas' .
# 4 - Mostrar fórmulas
Podemos usar este comando para mostrar fórmulas escritas en la hoja de Excel. La tecla de método abreviado para este comando es 'Ctrl + ~' .
Vea la imagen de abajo donde podemos ver las fórmulas en la celda.
Podemos ver que en lugar de los resultados de la fórmula, podemos ver la fórmula. Para los importes, el formato de moneda no es visible.
Para desactivar este modo, presione 'Ctrl + ~' nuevamente o podemos hacer clic en el comando 'Mostrar fórmulas' .
# 5 - Comprobación de errores
Este comando se usa para verificar el error en la fórmula o función especificada.
Tomemos un ejemplo para entender esto.
Vea la imagen de abajo donde tenemos un error en la función aplicada para el resultado.
Ahora, para solucionar este error, usaremos el comando 'Comprobación de errores' .
Los pasos serían:
Seleccione la celda donde está escrita la fórmula o función y luego haga clic en 'Comprobación de errores'.
Cuando hacemos clic en el comando, obtenemos el siguiente cuadro de diálogo titulado 'Comprobación de errores' .
En el cuadro de diálogo anterior, se puede ver que hay un error de nombre no válido. La fórmula contiene texto no reconocido.
Si estamos usando la función o construimos la fórmula por primera vez, entonces podemos hacer clic en el botón 'Ayuda sobre este error' que abrirá la página de ayuda para la función en el navegador donde podemos ver toda la información relacionada en línea y entender la causa y encontrar todas las posibles soluciones.
Al hacer clic en este botón ahora, encontraremos la siguiente página.
En esta página, conocemos el error que este error se produce cuando
- La fórmula se refiere a un nombre que no se ha definido. Significa que el nombre de la función o el rango con nombre no se ha definido anteriormente.
- La fórmula tiene un error tipográfico en el nombre definido. Significa que hay algún error de escritura.
Si hemos usado la función anteriormente y conocemos la función, entonces podemos hacer clic en el botón 'Mostrar pasos de cálculo' para verificar cómo la evaluación de la función da como resultado un error.
Si hacemos clic en este botón, se muestran los siguientes pasos:
- El siguiente cuadro de diálogo se muestra cuando hacemos clic en el botón 'Mostrar pasos de cálculo' .
- Después de hacer clic en el botón "Evaluar" , la expresión subrayada, es decir, "IIF" se evalúa y proporciona la siguiente información como se muestra en el cuadro de diálogo.
Como podemos ver en la imagen de arriba, la expresión 'IIF' evaluada como un error que es '#NAME?'. Ahora la siguiente expresión o referencia, es decir, B2 se subrayó. Si hacemos clic en el botón 'Step In' , también podemos verificar los detalles internos de un paso y salir presionando el botón 'Step Out' .
- Ahora haremos clic en el botón 'Evaluar' para comprobar el resultado de la expresión subrayada. Después de hacer clic, obtenemos el siguiente resultado.
- Después de hacer clic en el botón 'Evaluar' , obtenemos el resultado de la función aplicada.
- Obtuvimos un error como resultado y mientras analizamos la función paso a paso, llegamos a saber que hay algún error en 'IIF'. Para esto, podemos usar el comando 'Insertar función' en el grupo 'Biblioteca de funciones' debajo de ' Pestaña de fórmulas.
Cuando escribimos el 'si' , obtuvimos una función similar en la lista, debemos elegir la función adecuada.
Después de seleccionar la función 'Si' , obtenemos el siguiente cuadro de diálogo con cuadros de texto para el argumento y llenaremos todos los detalles.
Después de hacer clic en 'Aceptar' , obtenemos el resultado en la celda. Copiaremos la función para todos los estudiantes.
Cosas para recordar
- Si activamos el comando 'Mostrar fórmulas', las fechas también se muestran en formato numérico.
- Mientras evaluamos la fórmula, también podemos usar F9 como atajo en Excel.
