Post on 28-Oct-2018
326
EJERCICIO 30 DE EXCEL
ANÁLISIS DE DATOS Excel 2016
Escenarios
Excel puede crear y guardar conjuntos de variables que producen resultados
diferentes.
Los escenarios son útiles en el análisis de datos, ya que se podría ver, por
ejemplo como afectarían distintas condiciones (tasa de interés, plazo de
devolución, etc.) en un préstamo. Se pueden definir escenarios diferentes, cambiar
de uno a otro para realizar análisis y guardarlos con el modelo.
Se define un escenario como un conjunto de variables llamadas celdas
cambiantes que se guardan con un nombre y se aplican a una hoja de cálculo para
ver los distintos resultados producidos.
Un escenario está compuesto por dos partes principales:
1. Celdas cambiantes. Son las celdas en las cuales se van a introducir datos
(que se van a modificar) y no deben contener fórmulas.
2. Celdas resultantes. Son las celdas donde se ve el resultado. Deben ser
celdas que contengan fórmulas o que dependan de las celdas cambiantes.
Un escenario puede dar como resultado:
• Resumen. Muestra los resultados (celdas cambiantes y las celdas
resultantes) en una hoja de cálculo.
• Tablas dinámicas. Da el resultado en una tabla dinámica.
• Mostrar. Muestra las variaciones dentro de la hoja de cálculo activa.
327
Creación de un escenario
Para ver el funcionamiento de esta opción realizaremos un sencillo ejemplo:
1. Cree la siguiente hoja de cálculo y utilice la fórmula =B1+B1*B2 para
calcular el Precio final.
A continuación crearemos tres escenarios llamados Mínimo, Medio y Máximo
que cambiarán el Margen de beneficios con los valores 10%, 50% y 90%
respectivamente.
2. Desplace el cursor a la celda B2 y seleccione la ficha Datos, en el
grupo Herramientas de datos, haga clic en Análisis Y si y, después,
en Administrador de escenarios.
328
3. Aparecerá el siguiente cuadro de diálogo llamado Administrador de
escenarios:
En donde:
• Aparece una lista con todos los escenarios creados (los ocultos no
aparecen, si se activó la protección).
• Celdas cambiantes. Las celdas en las que van a introducir datos.
• Comentarios. Explicación de lo que hace un escenario; si no se
introduce nada, Excel de forma automática, coloca el nombre que
figure como usuario y la fecha en que fue creado o modificado.
• Agregar. Agrega un escenario.
• Eliminar. Elimina el escenario sobre el que se está posicionado.
329
• Modificar. Sirve para realizar cambios en un escenario, siempre
que la protección no esté activada.
• Combinar. Combina los escenarios de las hojas de cálculo
seleccionadas en cualquier libro de trabajo abierto.
• Resumen. Crea un resumen o una tabla dinámica en una hoja en
blanco con los escenarios definidos.
4. Pulse el botón Agregar para crear el primer escenario. En su pantalla
aparecerá otro nuevo cuadro de diálogo llamado Agregar
escenario.
En donde:
A. Se escribe el nombre del escenario.
A
B
C
D
330
B. Celdas cambiantes. Se establecen las celdas cambiantes para ese
escenario.
C. Comentarios. Explicación o seguimiento del escenario.
D. Protección. Evitar que se produzcan modificaciones en los
escenarios.
5. Teclee Mínimo en el interior del cuadro Nombre del escenario.
En el interior del cuadro Celdas cambiantes tenemos que introducir
la referencia de la celda o celdas que deseamos cambiar.
6. Observe que en el cuadro Celdas cambiantes se indique la celda B2,
en caso contrario modifíquelo.
En el cuadro Comentarios aparece el nombre del usuario y la fecha de
creación del escenario. En el caso que lo considere necesario puede
cambiarlo.
7. Pulse el botón Aceptar.
Aparecerá un nuevo cuadro de diálogo mostrando el valor actual de la
celda cambiante y preguntando qué nuevo valor se desea
introducir en la misma.
Cuando introduzca un valor podrá pulsar el botón Agregar para
continuar creando otros escenarios o bien pulsar el botón Aceptar
para terminar y regresar al cuadro de diálogo Administrador de
escenarios.
331
8. Teclee 10% y pulse el botón Agregar para crear otro escenarios.
9. Teclee Medio como nombre de escenario y pulse el botón Aceptar.
10. Introduzca el valor 50% y pulse el botón Agregar para crear el último
escenario.
11. Teclee Máximo como nombre del escenario y pulse el botón Aceptar.
12. Introduzca el valor 90% y pulse el botón Aceptar para terminar y
regresar al cuadro de diálogo Administrador de escenarios.
332
Proteger los escenarios
Los cuadros de diálogo Agregar y Modificar escenario, contienen dos
opciones de protección:
• Evitar cambios.
• Ocultar.
Si se selecciona Evitar cambios, el escenario que se defina no podrá ser
editado.
Sin embargo, esto no impide editar directamente en la hoja los valores de
las celdas cambiantes (a menos que las propias celdas estén bloqueadas). La casilla
Ocultar, evita la presentación en la lista de escenarios.
Mostrar los escenarios
Para ver el resultado de los escenarios directamente sobre la hoja de cálculo,
simplemente se posiciona sobre el escenario que se desee ver el resultado y se
pulsa Mostrar, automáticamente se ven los resultados sobre la hoja de cálculo.
Modificar escenarios
Si deseamos modificar algún escenario tenemos que realizar los siguientes
pasos:
1. Seleccione el escenario Máximo.
333
2. Pulse el botón Modificar. A continuación aparecerá el cuadro de diálogo
Modificar escenario.
En esta pantalla podemos modificar el nombre del escenario, la celda cambiante o
el comentario, pero si nuestra intención es cambiar el valor del escenario tenemos
que pulsar el botón Aceptar.
En nuestro caso cambiaremos el valor.
3. Pulse el botón Aceptar.
4. Teclee 100%.
5. Pulse el botón Aceptar para finalizar el cambio.
334
Eliminar escenarios
Para eliminar escenarios que no se necesiten, efectúe los siguientes pasos:
1. Elija el escenario a eliminar.
2. Pulse el botón Eliminar.
Combinar escenarios
Utilice la opción cambiar cuando necesite copiar al libro de trabajo activo
escenarios de otros libros de trabajo, teniendo en cuenta que deben estar abiertos
todos los libros.
Los pasos para realizar la combinación son:
➢ Abra todos los libros de trabajo que contienen los escenarios a combinar.
➢ Active el libro de trabajo donde desea realizar la combinación.
➢ Seleccione la ficha “Datos”, en el grupo “Herramientas de datos”, haga
clic en “Análisis Y si” y, después, en “Administrador de escenarios”.
➢ Pulse el botón Combinar. Aparecerá el cuadro Combinar escenarios.
335
➢ Seleccione de la lista desplegable el libro de trabajo que contiene los
escenarios para combinar.
➢ Elija también la hoja que posee los escenarios para combinar. Observe
que en la parte inferior de la ventana aparece un mensaje del número de
escenarios existentes en la hoja seleccionada.
➢ Pulse el botón Aceptar. Excel regresará al cuadro de diálogo
Administrador de escenarios con los escenarios combinados en su
interior.
➢ Pulse Cerrar para finalizar.
Resumen de los escenarios
Se puede ver el resultado en un resumen, en el que se incluyen todos los
escenarios creados, con las variables (celdas cambiantes y celdas resultantes),
incluyendo además un escenario con los valores actuales. Automáticamente se crea
una hoja en el libro de trabajo activo, llamada Resumen de escenarios. Para ello,
336
se pulsa Resumen en la ventana de Administrador de escenarios. Aparecerá la
siguiente ventana, donde se selecciona la opción Resumen (1) y se indican las celdas
resultantes (2).
El resultado final será algo así:
1
2
337
Utilización de tablas de datos
Se define una tabla de datos como un rango de celdas que muestra los
resultados de sustituir diferentes valores en una fórmula. En las tablas de una
variable se introducen diferentes valores (fila o columna) y se ve el resultado en
una fórmula. Para tablas de dos variables, se introducen valores (fila y columna)
para las dos variables y se ve el resultado en una fórmula. Para utilizar una tabla
de datos con una sola variable, los pasos a seguir son:
1. Se crea una columna o fila con los valores de la variable (2) que serán
sustituidos en la fórmula. Y en la celda siguiente, en la parte superior, se
coloca la fórmula que va a ser sustituida (3).
2. Se selecciona la zona donde se encuentren los datos variables y la
fórmula. En el ejemplo anterior sería A10..B19.
1
3
2
338
3. Ir a la etiqueta “Datos”, seleccionar “Tabla de datos”:
4. Aparece el siguiente cuadro de diálogo:
Para el análisis con una variable, se le indica el dato que va a ser utilizado
para la realización del cálculo. Al pulsar Aceptar aparecerán los datos con los
nuevos valores debajo de la fórmula y al lado de su correspondiente parámetro. En
este ejemplo, el dato variable es la tasa de interés y está en la celda B7. Se
indicaría como Celda de entrada (columna) B7. El resultado sería:
339
En el análisis con dos variables, los pasos son semejantes a los de una
variable, con la diferencia de que la fórmula está en la intersección de la fila con
la columna. En el siguiente ejemplo, los datos variables son el precio de venta, que
está en la celda B5, y la tasa de interés que está en la celda B7. Se indicaría en
Celda de entrada (fila) B5 y Celda de entrada (columna) B7 en la ventana Tabla.
340
El resultado final sería:
341
Buscar objetivo
Si se desea encontrar el número preciso en una fórmula para alcanzar un
determinado valor (objetivo), debe utilizar el comando “Buscar objetivo”.
Para nuestro ejemplo tomaremos una planilla como la que se muestra a
continuación:
En esta planilla se tiene un precio de compra al cual se aplica un porcentaje
de ganancia para obtener el precio de venta. En este caso se aplica un 10% de
ganancia obteniendo como precio de venta 110€. Pero cuánto sería el porcentaje
de ganancia si quisiéramos que nuestro precio de venta fuera 225,15€?
Para averiguar este valor nos valemos de la herramienta “Buscar objetivo”:
342
Para encontrar nuestro porcentaje de venta se debe:
1. Activar la etiqueta “Datos”, desplegar el menú “Análisis y si” y por último
hacer clic en “Buscar objetivo”, se presentará el siguiente cuadro de
diálogo:
En donde:
a) La celda sobre la que se va a obtener el resultado. En nuestro ejemplo
D4.
b) Se escribe el valor que se quiere alcanzar. En nuestro ejemplo 225,15.
c) Se escribe la celda que se va a modificar, y que será sustituida por el valor
que encuentre. En nuestro ejemplo C4.
2. Excel comienza a buscar un nuevo valor para porcentaje de venta.
Pulsaremos Aceptar si aceptamos el valor encontrado. Cancelar si
queremos dejarlo como estaba.
Excel 2016