Análisis de datos de Excel: auditoría de fórmulas
Es posible que desee verificar la precisión de las fórmulas o encontrar la fuente de un error. Los comandos de Auditoría de fórmulas de Excel le proporcionan una manera fácil de encontrar
- Qué celdas contribuyen al cálculo de una fórmula en la celda activa.
- Qué fórmulas se refieren a la celda activa.
Estos hallazgos se muestran gráficamente mediante líneas de flechas que facilitan la visualización. Puede mostrar todas las fórmulas en la hoja de trabajo activa con un solo comando. Si sus fórmulas se refieren a celdas en un libro de trabajo diferente, abra también ese libro. Excel no puede ir a una celda de un libro que no está abierto.
Configuración de las opciones de visualización
Debe comprobar si las opciones de visualización de los libros de trabajo que está utilizando están configuradas correctamente.
- Hacer clic FILE > Options.
- En el cuadro de diálogo Opciones de Excel, haga clic en Avanzado.
- En Mostrar opciones para el libro de trabajo:
- Seleccione el libro de trabajo.
- Compruebe que en Para objetos, mostrar, Todo esté seleccionado.
- Repita este paso para todos los libros de trabajo que está auditando.
Rastreo de precedentes
Las celdas precedentes son aquellas celdas a las que se hace referencia mediante una fórmula en la celda activa.
En el siguiente ejemplo, la celda activa es C2. En C2, tienes la fórmula=B2*C4.
B2 y C4 son celdas precedentes para C2.
Para rastrear los precedentes de la celda C2,
- Haga clic en la celda C2.
- Haga clic en la pestaña Fórmulas.
- Haga clic en Rastrear precedentes en el grupo Auditoría de fórmulas.
Se mostrarán dos flechas, una de B2 a C2 y otra de C4 a C2, trazando los precedentes.
Tenga en cuenta que para rastrear los precedentes de una celda, la celda debe tener una fórmula con referencias válidas. De lo contrario, recibirá un mensaje de error.
- Haga clic en una celda que no contenga una fórmula o haga clic en una celda vacía.
- Haga clic en Rastrear precedentes en el grupo Auditoría de fórmulas.
Recibirá un mensaje.
Eliminando flechas
Haga clic en Eliminar flechas en el grupo Auditoría de fórmulas.
Todas las flechas de la hoja de trabajo desaparecerán.
Rastreo de dependientes
Las celdas dependientes contienen fórmulas que hacen referencia a otras celdas. Eso significa que si la celda activa contribuye a una fórmula en otra celda, la otra celda es una celda dependiente de la celda activa.
En el siguiente ejemplo, C2 tiene la fórmula =B2*C4. Por lo tanto, C2 es una celda dependiente de las celdas B2 y C4.
Para rastrear a los dependientes de la celda B2,
- Haga clic en la celda B2.
- Haga clic en la pestaña Fórmulas.
- Haga clic en Rastrear dependientes en el grupo Auditoría de fórmulas.
Aparece una flecha de B2 a C2, mostrando que C2 depende de B2.
Para rastrear a los dependientes de la celda C4 -
- Haga clic en la celda C4.
- Haga clic en la pestaña Fórmula> Rastrear dependientes en el grupo Auditoría de fórmulas.
Aparece otra flecha de C4 a C2, mostrando que C2 también depende de C4.
Hacer clic Remove Arrowsen el grupo Auditoría de fórmulas. Todas las flechas de la hoja de trabajo desaparecerán.
Note- Para rastrear dependientes de una celda, la celda debe estar referenciada por una fórmula en otra celda. De lo contrario, recibirá un mensaje de error.
- Haga clic en la celda B6 no está referenciada por ninguna fórmula o haga clic en una celda vacía.
- Haga clic en Rastrear dependientes en el grupo Auditoría de fórmulas. Recibirá un mensaje.
Trabajar con fórmulas
Has entendido el concepto de Precedentes y Dependientes. Ahora, considere una hoja de trabajo con varias fórmulas.
- Haga clic en una celda debajo de Categoría de aprobación en la tabla Resultados del examen.
- Haga clic en Rastrear precedentes. La celda a su izquierda (Marcas) y el rango E4: F8 se asignarán como precedentes.
- Repita para todas las celdas bajo Categoría de aprobación en la tabla Resultados del examen.
Haga clic en una celda debajo de Categoría de aprobación en la tabla Calificaciones de los estudiantes.
Haga clic en Rastrear dependientes. Todas las celdas bajo Categoría de aprobación en la tabla Resultados del examen se asignarán como dependientes.
Mostrar fórmulas
La hoja de trabajo siguiente contiene el resumen de las ventas de los vendedores en las regiones Este, Norte, Sur y Oeste.
Haga clic en la pestaña FÓRMULAS en la cinta.
Haga clic en Mostrar fórmulas en el grupo Auditoría de fórmulas. Aparecerán las fórmulas en la hoja de trabajo, para que sepa qué celdas contienen fórmulas y cuáles son las fórmulas.
Haga clic en una celda debajo TotalSales.
Haga clic en Rastrear precedentes. Aparece un icono de hoja de trabajo al final de la flecha. El icono de la hoja de trabajo indica que los precedentes están en una hoja de trabajo diferente.
Haz doble clic en la flecha. UNAGo TO Aparece un cuadro de diálogo que muestra los precedentes.
Como puede observar, hay cuatro precedentes, en cuatro hojas de trabajo diferentes.
- Haga clic en una referencia de uno de los precedentes.
- La referencia aparece en el cuadro Referencia.
- Haga clic en Aceptar. Aparece la hoja de trabajo que contiene ese precedente.
Evaluar una fórmula
Para averiguar paso a paso cómo funciona una fórmula compleja en una celda, puede usar el comando Evaluar fórmula.
Considere la fórmula NPV (año medio) en la celda C14. La formula es
=SQRT (1 + C2)*C10
- Haga clic en la celda C14.
- Haga clic en la pestaña FÓRMULAS en la cinta.
- Haga clic en Evaluar fórmula en el grupo Auditoría de fórmulas. Aparece el cuadro de diálogo Evaluar fórmula.
En el Evaluate Formulacuadro de diálogo, la fórmula se muestra en el cuadro bajo Evaluación. Haciendo clic en elEvaluatevarias veces, la fórmula se evalúa paso a paso. La expresión con un subrayado siempre se ejecutará a continuación.
Aquí, C2 está subrayado en la fórmula. Entonces, se evalúa en el siguiente paso. Hacer clicEvaluate.
La celda C2 tiene valor 0.2. Por tanto, C2 se evaluará como 0,2.1+0.2está subrayado y lo muestra como el siguiente paso. Hacer clicEvaluate.
1 + 0,2 se evaluará como 1,2. SQRT(1.2)está subrayado mostrándolo como el siguiente paso. Hacer clicEvaluate.
SQRT (1.2) se evaluará como 1.09544511501033. C10está subrayado mostrándolo como el siguiente paso. Hacer clicEvaluate.
C10 se evaluará como 4976.8518518515.
1.09544511501033 * 4976.8518518515 está subrayado y lo muestra como el siguiente paso. Hacer clicEvaluate.
1.09544511501033 * 4976.8518518515 se evaluará como 5,451.87.
No hay más expresiones para evaluar y esta es la respuesta. losEvaluate el botón se cambiará a Restart botón, que indica la finalización de la evaluación.
Comprobación de errores
Es una buena práctica hacer una verificación de errores una vez que su hoja de trabajo y / o libro de trabajo esté listo con los cálculos.
Considere los siguientes cálculos simples.
El cálculo en la celda ha dado como resultado el error # DIV / 0 !.
Haga clic en la celda C5.
Haga clic en la pestaña FÓRMULAS en la cinta.
Haga clic en la flecha junto a Comprobación de errores en el grupo Auditoría de fórmulas. En la lista desplegable, encontrará queCircular References está desactivado, lo que indica que su hoja de trabajo no tiene referencias circulares.
Seleccione Trace Error de la lista desplegable.
Las celdas necesarias para calcular la celda activa se indican mediante flechas azules.
- Haga clic en Eliminar flechas.
- Haga clic en la flecha junto a Comprobación de errores.
- Seleccione Comprobación de errores en la lista desplegable.
los Error Checking aparece el cuadro de diálogo.
Observe lo siguiente:
Si hace clic en Help on this error, Se mostrará la ayuda de Excel sobre el error.
Si hace clic en Show Calculation Steps, Aparece el cuadro de diálogo Evaluar fórmula.
Si hace clic en Ignore Error, el cuadro de diálogo Comprobación de errores se cierra y si hace clic en Error Checking comando de nuevo, ignora este error.
Si hace clic en Edit in Formula Bar, se le dirigirá a la fórmula en la barra de fórmulas, para que pueda editar la fórmula en la celda.