Precedentes y Dependientes
Cuando trabajamos con fórmulas por lo común se cometen errores durante el cálculo o bien al indicar las referencias a las celdas que intervienen en el cálculo. Excel nos ofrece las herramientas necesarias para auditar las fórmulas, evaluarlas y encontrar la referencia exacta en la cual se produce el error. Estas herramientas nos simplifican la corrección de los errores para que nuestras fórmulas siempre produzcan el resultado adecuado.
La auditoría de fórmulas nos permite no sólo rastrear errores, sino también rastrear dependientes y precedentes, así como efectuar una inspección o evaluación de las mismas. Las flechas de color rojo muestran las celdas que generan errores. Si una celda de otra hoja de cálculo o de otro libro hace referencia a la celda seleccionada, se mostrará una flecha de color negro que señala de la celda seleccionada a un icono de hoja de cálculo. El otro libro debe estar abierto para que Microsoft Excel pueda rastrear las dependencias.
Para realizar la auditoria de fórmulas en la ficha Fórmulas, en el grupo
Auditoría de fórmulas utilice alguno de los siguientes comandos de acuerdo a lo
que requiera:
Rastrear precedentes. Indica con flechas cuáles son las celdas que
intervienen en el cálculo de la fórmula que se encuentra en la celda actual. En caso de no haber fórmula en la celda activa se mostrará un
Rastrear dependientes. Indica con flechas cuáles son las celdas que
utilizan la celda activa para el cálculo de alguna fórmula. En caso de no
utilizar la celda actual en una fórmula se notificará.
Quitar flechas. Permite quitar todas flechas activadas, o bien hacerlo por niveles.
NOTA: Las flechas de seguimiento son flechas que muestran la relación
entre la celda activa y sus celdas relacionadas. Las flechas de seguimiento
son azules cuando se señalan desde una celda que proporciona datos a otra
celda, y rojas si una celda contiene un valor de error, como #DIV/0!.
Mostrar fórmulas. Muestra la fórmula correspondiente en cada celda en lugar del valor resultante
Comprobación de errores. Busca errores comunes en las celdas.
Rastrear error. Esta herramienta sólo se utiliza cuando la celda activa contiene un error, y muestra o rastrea desde donde se origina. Marca las celdas que intervienen en el error con flechas.
Evaluar fórmula. Permite depurar una fórmula visualizando paso a paso su cálculo.
AL CAMBIAR LA HOJA DESAPARECEN LAS FLECHAS DE RASTREO
Si se cambia la fórmula de la ruta de rastreo, se insertan o se eliminan columnas o filas, o si se eliminan o mueven celdas, desaparecerán todas las flechas de rastreo. Para restaurar las flechas de rastreo después de hacer cualquiera de estos cambios, deberán utilizarse otra vez los comandos de auditoría en la hoja de cálculo.
MICROSOFT EXCEL EMITE UN PITIDO AL RASTREAR…
Si Microsoft Excel emite un pitido al hacer clic en Rastrear dependientes o en
Rastrear precedentes en el grupo Auditoría de fórmulas, significa que Excel ha
buscado en todos los niveles de la fórmula o que está tratando de rastrear un elemento que no puede rastrearse. Los siguientes elementos de la hoja de cálculo, a los que pueden hacer referencia las fórmulas, no pueden rastrearse mediante las herramientas de auditoría:
Las referencias a cuadros de texto, o imágenes en una hoja de cálculo.
Informes de tabla dinámica
Las referencias a constantes con nombre
Las fórmulas ubicadas en otro libro que hacen referencia a la celda activa si se cierra el otro libro
Comprobación y Rastreo de fórmulas
Microsoft Excel tiene varias herramientas que le ayudarán a encontrar y corregir los problemas con las fórmulas
HERRAMIENTA DE COMPROBACIÓN DE ERRORES EN FÓRMULAS
Al igual que un corrector gramatical, Excel emplea algunas reglas para comprobar si hay problemas en las fórmulas. Estas reglas no garantizan que la hoja de cálculo no tenga ningún problema, pero ayudan en gran medida a encontrar los errores más comunes. Se pueden activar y desactivar individualmente.
Los problemas se pueden revisar de dos formas: de uno en uno, como con el corrector ortográfico, o inmediatamente sobre la hoja de cálculo mientras trabaja. Cuando se detecta un problema, aparece un triángulo en la esquina superior izquierda de la celda. Ambos métodos presentan las mismas opciones.
Celda con un problema de fórmula
Puede solucionar el problema utilizando las opciones que aparecen u omitirlo. Si lo omite, ya no volverá a aparecer en las comprobaciones de errores subsiguientes. Sin embargo, todos los errores que haya omitido se pueden restablecer para que vuelvan a mostrarse.
REGLAS Y ERRORES QUE SE DETECTAN
Evalúa valor de error. La fórmula no utiliza la sintaxis, los argumentos o los
tipos de datos que se esperaba. Entre los valores de error se encuentran #DIV/0!, #N/A, #¿NOMBRE?, #¡NULO!, #¡NUM!, #¡REF! y #¡VALOR!. Cada valor de error tiene distintas causas y se resuelve de diferente modo.
NOTA: Si introduce un valor de error directamente en una celda, no se
marcará como problema.
Año con dos dígitos en fecha de texto: La celda contiene una fecha de texto
en la que el siglo se puede interpretar incorrectamente si se utiliza en fórmulas. Por ejemplo, la fecha de la fórmula =AÑO("1/1/31") podría ser 1931 ó 2031. Utilice esta regla para comprobar fechas de texto ambiguas.
Número almacenado como texto: La celda contiene números guardados
como texto. Suelen proceder de datos importados de otros orígenes. Los números guardados como texto pueden ocasionar cambios inesperados en la forma de ordenar; es preferible convertirlos a números
Para utilizar todas las herramientas anteriores, es necesario que esté situado en una celda que contenga una fórmula, de lo contrario Excel le notificará que no pueden ser utilizadas.
CORREGIR UN VALOR DE ERROR
Si una fórmula no puede evaluar correctamente un resultado, Excel muestra un valor de error, como #####, #¡DIV/0!, #N/A, #¿NOMBRE?, #¡NULO!, #¡NÚM!, #¡REF! y #¡VALOR!. Cada tipo de error tiene diversas causas y soluciones diferentes.
Valor de
Error
Descripción
##### Excel muestra este error cuando el ancho de una columna no es suficiente para mostrar todos los caracteres de una celda o cuando una celda incluye valores negativos en la fecha o la hora.
Por ejemplo, una fórmula que resta a una fecha del pasado una fecha del futuro, como =15/06/2008-01/07/2008. Esto genera un valor negativo en la fecha.
#¡DIV/0! Excel muestra este error cuando un número se divide por cero (0) o por una celda que no contiene ningún valor.
#N/A Excel muestra este error cuando un valor no está disponible para una función o una fórmula.
#¿NOMBRE? Este error aparece cuando Excel no reconoce el texto de una fórmula. Por ejemplo, el nombre de un intervalo o de una función puede estar mal
escrito.
#¡NULO! Excel muestra este error cuando se especifica una intersección de dos áreas que no forman intersección (no se cruzan). El operador de intersección es un carácter de espacio que separa referencias en una fórmula.
Por ejemplo, las áreas A1:A2 y C3:C5 no forman intersección, de modo que al escribir la fórmula =SUMA(A1:A2 C3:C5) se devuelve el error #¡NULO!.
#¡NÚM! Excel muestra este error cuando una fórmula o función contiene valores numéricos no válidos.
#¡REF! Excel muestra este error cuando una referencia de celda no es válida. Por ejemplo, cuando se eliminan celdas a las que otras fórmulas hacían referencia o se pegan celdas movidas sobre otras a las cuales se hacía referencia en otras fórmulas.
#¡VALOR! Excel puede mostrar este error si la fórmula incluye celdas que contienen tipos de datos diferentes. Si se habilita la comprobación errores, la información en pantalla muestra el mensaje "Un valor utilizado en la fórmula es de un tipo de datos erróneo". Por lo general, para resolver este problema, se pueden realizar pequeñas modificaciones en la fórmula.
Mostrar Fórmulas
Entre las herramientas de auditoría de fórmulas podemos encontrar una opción llamada Mostrar fórmulas cuya finalidad es mostrar la fórmula en cada
celda en lugar del valor resultante.
Esto facilita la identificación de cuáles son las funciones y/o fórmulas que se están empleando en la hoja y así poder realizar los ajustes pertinentes para aplicar otras fórmulas o en su caso corregir aquellas celdas que tiene algún error.
Ventana de Inspección
INSPECCIONAR UNA FÓRMULA Y SU RESULTADO A TRAVÉS DE LA VENTANA INSPECCIÓN
Cuando las celdas no están visibles en una hoja de cálculo, puede ver las celdas y sus fórmulas en la barra de herramientas ventana Inspección. La ventana Inspección es útil para revisar, controlar o confirmar el cálculo de fórmulas y los resultados en hojas de cálculo grandes. Con el uso de la ventana Inspección, no necesitará desplazarse repetidamente ni ir a las distintas partes de su hoja de cálculo.
Esta barra de herramientas se puede mover o acoplar como cualquier otra barra de herramientas. Por ejemplo, puede acoplarla en la parte inferior de la ventana. La barra de herramientas realiza un seguimiento de las siguientes propiedades de una celda: libro, hoja, nombre, celda, valor y fórmula.
AGREGAR CELDAS A LA VENTANA INSPECCIÓN
1. Seleccione las celdas que desee inspeccionar.
2. En el grupo Auditoría de fórmulas de la ficha Fórmulas, haga clic en
Ventana Inspección.
3. Haga clic en Agregar inspección . 4. Haga clic en Agregar.
5. Mueva la barra de herramientas Ventana Inspección a la parte superior, inferior, izquierda o derecha de la ventana.
6. Para cambiar el ancho de una columna, arrastre el borde derecho del encabezado de la columna.
7. Para mostrar la celda a la que hace referencia una entrada en la barra de herramientas Ventana Inspección, haga doble clic en la entrada.
NOTA: Las celdas que tienen referencias externas a otros libros solo se
muestran en la barra de herramientas ventana Inspección cuando el otro libro está abierto.
Quitar celdas de la ventana Inspección
1. En el grupo Auditoría de fórmulas de la pestaña Fórmulas, haga clic en
Ventana Inspección.
2. Seleccione las celdas que desee quitar.
Para seleccionar varias celdas, presione CTRL y haga clic en las celdas. 3. Haga clic en Eliminar inspección .
Evaluar Fórmulas
EVALUAR UNA FÓRMULA ANIDADA PASO A PASO
A veces resulta difícil comprender cómo se calcula el resultado final de una fórmula anidada porque hay diversos cálculos intermedios y pruebas lógicas. Sin embargo, mediante el cuadro de diálogo Evaluar fórmula, puede ver las diferentes partes de una fórmula anidada evaluados en el orden en que la fórmula se calcula. Por ejemplo, la fórmula =SI(PROMEDIO(F2:F5)>50;SUMA(G2:G5);0) es más fácil de comprender cuando puede ver los siguientes resultados intermedios:
Pasos que se muestran en el cuadro de diálogo
Descripción =SI(PROMEDIO(F2:F5)>50;
SUMA(G2:G5);0)
Se muestra inicialmente la fórmula anidada. La función PROMEDIO y la función SUMA están anidadas dentro de la función SI.
=SI(40>50;SUMA(G2:G5);0) El rango de celdas F2:F5 contiene los valores 55, 35, 45 y 25 y, por lo tanto, el resultado de la función
PROMEDIO(F2:F5) es 40.
=SI(Falso;SUMA(G2:G5);0) Puesto que 40 no es mayor que 50, la expresión en el primer argumento de la función SI (el argumento de la prueba lógica) es Falso.
0 La función SI devuelve el valor del tercer argumento (el argumento falso de valor de SI). La función SUMA no se evalúa porque es el segundo argumento de la función SI (argumento verdadero
de valor de SI) y se devuelve sólo cuando la
expresión es Verdadera.
1. Seleccione la celda con una fórmula que desee evaluar. Sólo se puede evaluar una celda a la vez.
2. En el grupo Auditoría de fórmulas de la pestaña Fórmulas, haga clic en la opción Evaluar fórmula.
3. Haga clic en Evaluar para examinar el valor de la referencia subrayada. El resultado de la evaluación se muestra en cursiva.
Si la parte subrayada de la fórmula es una referencia a otra fórmula, haga clic en Paso a paso para entrar para mostrar la otra fórmula en el cuadro Evaluación. Haga clic en Paso a paso para salir para volver a la celda y fórmula anteriores.
NOTA: El botón Paso a paso para entrar no está disponible para una
referencia la segunda vez que ésta aparece en la fórmula, ni si la fórmula hace referencia a una celda de un libro distinto.
4. Continúe hasta que se hayan evaluado todas las partes de la fórmula.
5. Para ver de nuevo la evaluación, haga clic en Reiniciar. Para finalizar la evaluación, haga clic en Cerrar.
OBSERVACIONES:
Algunas partes de fórmulas que usan las funciones SI y ELEGIR no se evalúan. En estos casos, se muestra #N/A en el cuadro Evaluación.
Si una referencia está en blanco, aparece el valor cero (0) en el cuadro Evaluación.
Las siguientes funciones se vuelven a calcular cada vez que se modifica la hoja de cálculo y puede provocar que el cuadro de diálogo Evaluar fórmula presente resultados distintos de los que aparecen en la celda: ALEATORIO, ÁREAS, ÍNDICE, DESPLAZAMIENTO, CELDA, INDIRECTO, FILAS, COLUMNAS, AHORA, HOY, ALEATORIO ENTRE.
Laboratorio 1: AUDITORÍA DE FÓRMULAS
Ejercicio 1. Encontrar Errores en los Cálculos y Corregirlos
En este ejercicio encontrará errores en fórmulas y utilizando las herramientas de auditoria de fórmulas las corregirá.
Tareas Guía para completar la tarea
1. Abra el archivo de Excel llamado Errores. 2. Rastrea los precedentes de la celda H21. Seleccione la celda H21.
En la ficha fórmulas en el grupo Auditoria de fórmulas haga clic en rastrear precedentes.
Una flecha color roja indicará todas las celdas que preceden la fórmula.
3. Rastre el error de la celda H21.
Seleccione la celda H21.
Despliegue la etiqueta inteligente de Error que está a la izquierda de la celda y haga clic en Seguimiento de error.
La celda activa se moverá hacia donde se ubica el error (H7) y mostrará los precedentes al error.
4. Evalué la fórmula y corrija el error que se muestra.
Seleccione la celda H7 en caso de ser necesario.
En la ficha fórmulas en el grupo Auditoria de fórmulas haga clic en Evaluar fórmula. El cuadro de diálogo Evaluar fórmula aparecerá. Haga clic en Evaluar, se mostrará en lugar de G7 el valor de la celda Haga clic de nuevo en Evaluar se mostrará en lugar de H4 el valor de
la celda. Haga clic nuevamente en evaluar y observe que aparece el resultado #¡VALOR!
NOTA: No realice operación alguna
El error se debe a que se está multiplicando una celda con valor numérico (G7) por una celda que contiene un texto (H4).
La fórmula correcta debe ser: G7 * H2.
Debido a que H2 está fuera de la tabla y todas las fórmulas hacen referencia a ella, debe de utilizar una referencia absoluta, es decir $H$2. La fórmula entonces debe ser G7 * $H$2.
Haga clic en Cerrar.
5. Verifique que la primera fórmula
contenga la
referencia absoluta y cópiela a las celdas adyacentes.
Seleccione la celda H5.
En la barra de fórmulas verifique que la fórmula esté escrita como sigue: G2*H2.
Corrija la fórmula para que quede como se indica. Haga clic en la barra de fórmula y para volver absoluta la referencia H2 haga clic en ella y presione la tecla F4. Una vez que la formula este escrita como sigue G5*$H$2 presiona [ENTER].
Seleccione la celda H5 y cópiela de forma adyacente al rango H6:H19.
Verifique que los errores del rango H5:H19 se hayan corregido. 6. Seleccione la celda H21 y rastree sus precedentes y el error y corríjalo. Copie la fórmula corregida a la celda I21. Seleccione la celda H21.
En la ficha fórmulas en el grupo Auditoria de fórmulas haga clic en rastrear precedentes.
Una flecha color roja indicará todas las celdas que preceden la fórmula.
Despliegue la etiqueta inteligente de Error que está a la izquierda de la celda y haga clic en Rastrear error.
Un mensaje le indicará que el Rastreador ha encontrado una flecha o una referencia circular.
En la ficha fórmulas en el grupo Auditoria de fórmulas haga clic en rastrear precedentes, cuando la flecha roja indique las celdas que intervienen en el cálculo verifique que la celda H21 no esté dentro de ellas.
Vaya a la barra de fórmulas y corrija la fórmula de tal manera que se muestre como sigue: =PROMEDIO(H5:H19).
Verifique que el error se haya corregido. Copie la fórmula a la celda I21.
Verifique que el error se haya corregido.
7. Guarde el libro
creado con el
nombre Mod6 Lab1.
Haga clic en la ficha Archivo, despliegue y de clic en Guardar Como… El cuadro de diálogo Guardar como… aparecerá.
Vaya a la carpeta de Prácticas que creo anteriormente en Mis Documentos.
En el cuadro Nombre de archivo escriba Mod6 Lab1.
En el cuadro Guardar como tipo verifique que diga Libro de Excel. De clic en el botón Guardar.