Introducción

Los escenarios, a grandes rasgos, permiten realizar una previsión de un conjunto de situaciones posibles sin alterar los datos originales de la hoja de cálculo. Cuando se emplean adecuadamente, permiten hacer comparaciones tomando como base los mismos cálculos, pero con distintos valores. Las auditorías son otra herramienta de Excel que permiten localizar de forma rápida aquel dato que afecta de forma negativa una fórmula o función, la forma en que se marca el error consiste en que Excel muestra una flecha directamente sobre el dato erróneo para una mayor rapidez en su localización.

Explicación

Administrador de escenarios

Utiliza un análisis de hipótesis para modificar valores de una serie de celdas y de esta forma ver cómo afectan estos cambios el resultado en una hoja de cálculo. Es decir, con la creación de escenarios podrás visualizar este comportamiento a partir de los valores de entrada. Si utilizas escenarios en una situación particular, debes de tomar en cuenta que éstos permiten un máximo de 32 variables, y eso posibilita que puedas crear tantos escenarios como sean necesarios.

Si necesitas crear un escenario para analizar distintas alternativas, esta herramienta la encuentras disponible en: ficha Datos > grupo Previsión > Análisis de hipótesis > Administrador de escenarios.


Para crear un escenario debes tomar en cuenta los siguientes pasos:

  1. Crear una hoja de cálculo que va a contener el escenario, en ella debes introducir la información, fórmulas y dejar en blanco los valores que van a constituir el escenario.
  2. Ir a la ficha Datos > Previsión > Análisis de hipótesis > Administrador de escenarios.
  3. En el cuadro de diálogo Administrador de escenarios debes hacer clic en Agregar.
  4. Se abre el cuadro de diálogo Agregar escenario, y debes escribir el nombre de tu escenario.
  5. En el campo Celdas cambiantes debes introducir las referencias de las celdas que deseas cambiar.
  6. En la sección Comentario, puedes incluir una breve descripción de tu escenario.
  7. En la sección Protección, selecciona la opción deseada.
  8. Haz clic en Aceptar para abrir el cuadro de diálogo Valores del escenario.
  9. En Valores del escenario, introduce los valores que deseas para las celdas cambiantes.
  10. Para crear el escenario, haz clic en Aceptar.
  11. Si deseas incluir otro escenario, debes hacer clic en el botón Agregar del cuadro de diálogo Valores del escenario.
  12. Cuando termines de crear los escenarios requeridos, debes hacer clic en Aceptar y en el botón Cerrar del cuadro de diálogo Administrador de escenarios.

El administrador de escenarios tiene los siguientes elementos:

Ejemplo: en una tienda departamental desea adquirir un refrigerador y esta tienda ofrece un plan de pago de 6, 12 o 18 meses.

  1. En una hoja nueva de Excel ingresa la siguiente información:
  2. Introduce en la celda B4 un plazo de 6 meses.
  3. Introduce en la celda B5 la función pago con los siguientes argumentos:
  4. =-PAGO(B3/12, B4, B2)

  5. Crea un escenario con el nombre de Refrigerador Plazos 6 meses y configúralo de la siguiente manera:

  6. Como se te mencionó anteriormente, en la sección Protección tienes dos opciones: Evitar cambios y Ocultar. La primera te ayuda a evitar que el escenario pueda ser modificado, y la segunda no te mostrará el escenario en la lista de los escenarios siempre y cuando la hoja de cálculo se encuentre protegida.

  7. Haz clic en Aceptar para incluir los valores del escenario que cambiarán, la celda sufrirá cambio en el plazo elegido de 6 meses.

  8. Haz clic en “Agregar” y repite los pasos 4 y 5 para continuar creando los siguientes dos escenarios que corresponden a los plazos 12 y 18.

  9. Ya has terminado con los tres escenarios respecto a los plazos otorgados para la compra del refrigerador, haz clic en “Aceptar” y observa como el “Administrador de escenarios” contiene ya los tres escenarios previamente creados:

  10. Ahora toca el turno de revisar que todos los escenarios creados te muestren la cantidad a pagar mensualmente en función del plazo elegido. Para ello, en el “Administrador de escenarios” en la sección “Escenario” elige el escenario “Refrigerador Plazo 12 meses” y posteriormente haz clic en el botón “Mostrar”.

  11. Observa que el resultado que te muestra corresponde al pago que efectuarás para un plazo de 12 meses.

    El Administrador de escenarios contiene otras opciones:

    Hacerlo por cualquiera de estas opciones te permite visualizar con más precisión la diferencia entre cada escenario y elegir el más conveniente.

  12. Elige la opción Resumen, en el cuadro de diálogo Resumen del escenario y verifica que la celda que aparece en el campo Celdas de resultado, sea donde se va a mostrar el resultado y haz clic en Aceptar.
  13. Excel te despliega todos los escenarios creados para así tener una visión más amplia y elegir el más conveniente de adquirir a un plazo específico:

Considera que la opción Combinar del cuadro de diálogo Administrador de escenarios, te ayuda a realizar una integración de los diversos escenarios creados y así elegir, de todas las posibilidades, la más conveniente.

Auditoría de fórmulas

Utiliza la herramienta Auditoría de fórmulas para analizar un conjunto de celdas y valores que son utilizados de forma directa en una fórmula o función. Esta herramienta permite localizar de una forma rápida los datos que afectan de forma negativa a una fórmula. En hojas de cálculo complejas es indispensable utilizar esta herramienta para rastrear qué celdas participan en el cálculo, o bien, qué ha provocado el error y, en otros casos, darle un seguimiento a la ejecución de una fórmula que está funcionando correctamente.

La herramienta auditoría de fórmulas la encuentras en la ficha Fórmulas > grupo Auditoría de fórmulas.

En este grupo dispones de distintas formas de realizar un seguimiento:

Ejemplo:

Para este ejemplo, vas a retomar el reto que realizaste en la lección 5 de este curso.

Realiza las siguientes operaciones:

  1. Utiliza la opción Rastrear precedentes en la celda H3 y observa cómo la flecha azul se incluye en la celda que afecta el valor de la celda (B2).

  2. Al utilizar la herramienta “Rastrear dependiente”, Excel te confirma a través de un cuadro de diálogo que no encuentra dependencias de celdas que tengan una relación con la celda G11.

  3. Utiliza el comando Mostrar fórmulas para desplegar todas las fórmulas contenidas en la hoja de cálculo activa.

  4. Utiliza la herramienta comprobación de errores para visualizar qué está ocasionando la inconsistencia en la fórmula de la celda H4, la fórmula fue cambiada a la siguiente manera:

  5. =$B$2-SUMA($F2:F$3)

  6. Observa cómo en el cuadro de diálogo Comprobación de errores tienes varias opciones y solo debes elegir una de ellas para corregir la inconsistencia encontrada, haz clic en el botón Reanudar y observa lo siguiente.

  7. Las opciones que tienes son las siguientes:


  8. Elije la opción Modificar en la barra de fórmulas, la celda H4 queda de la siguiente manera.

  9. =$B$2-SUMA($F$3:F4)

  10. Una vez hecho el paso 6, debes de hacer otro cambio en la misma celda H4 y así poder utilizar la herramienta Rastrear error.

  11. =$B$2-SUMA($F$3:F4)


    Observa que, con este cambio, la opción Rastrear error, muestra algunas flechas en azul en las celdas que intervienen en las fórmulas. Si haces nuevamente clic en la opción aparecen más flechas azules y así, por cada clic que des, irán apareciendo flechas azules enlazando resultados con las celdas precedentes hasta que ya no haya más celdas qué asociar.

    Para ir eliminando grupos de flechas precedentes, debes de utilizar el comando Quitar flechas y la opción Quitar un nivel de precedentes y, si definitivamente deseas eliminar todas las flechas mostradas, debes utilizar la opción Quitar todas las flechas.


  12. Utiliza la opción Evaluar fórmulas para que el asistente paso a paso te ayude a encontrar el error o inconsistencia de una forma más detallada, utiliza como ejemplo el error de la celda H4.
  13. En el primer cuadro de diálogo Evaluar fórmula, te aparece en subrayado el primer argumento de la fórmula y debes continuar con el asistente dando clic en la opción Evaluar.

  14. Observa que en el último cuadro de diálogo de Evaluar fórmula aparece el resultado que se muestra en la celda de la hoja de cálculo, y que finalmente esta opción te ayuda a analizar paso a paso los valores que va considerando cada argumento de la función o fórmula y, de este modo, identificar el error. Para terminar con el asistente sólo debes hacer clic en Cerrar.

    La opción Referencias circulares sólo aparecerá disponible cuando Excel detecte que en tu hoja de cálculo existe una de ellas.


    Una referencia circular es aquella que hace referencia a sí misma dentro de la fórmula o función. Esto es porque se produce un bucle o iteración en donde Excel posee un valor de iteraciones por defecto, lo cual impide que un cálculo se ejecute indefinidamente a consecuencia de este error.


  15. Utilizando nuevamente la celda H4 como ejemplo, ahora modifica la fórmula como sigue.

  16. Observa que te aparece el cuadro de diálogo en donde se te advierte de la referencia circular, al hacer clic en Aceptar para probar o quitar la referencia encontrada, el comando Referencia circular se habilita y de esta forma Excel muestra la celda en donde existe la referencia circular.

  17. Posteriormente, lo que toca hacer, es corregir los valores de la celda H4 para que no se siga provocando la referencia circular.


Las anteriores pantallas se obtuvieron directamente del software, para fines educativos.

Checkpoint

Asegúrate de:

 

 

Revisa la sección de recursos, en donde encontrarás contenido multimedia que te ayudará a reforzar tu aprendizaje.