Analisis de datos

Microsoft Excel 2013

Análisis de datos Contenido  





Introducción al análisis "y si" Tablas de datos o Crear una tabla de datos de una variable.  Agregar una nueva fórmula a una tabla de datos de una variable. o Crear una tabla de datos de dos variables. o Actualización de una tabla de datos.  Reconfigurar Opciones de cálculo para todos los libros Excel.  Reconfigurar Opciones de cálculo para el libro actual.  Actualizar las tablas de datos cuyo cálculo no es automático. o Borrar una tabla de datos.  Borrar toda la tabla.  Borrar los valores resultantes de la tabla. Escenarios o Crear un escenario. o Mostrar un escenario. o Crear un informe resumen de escenario. Búsqueda de objetivo o Crear una búsqueda de objetivo.

1

Microsoft Excel 2013

Introducción al análisis "y si" El análisis "y si" es el proceso de cambiar los valores de las celdas para ver cómo estos cambios afectan al resultado de las fórmulas de la hoja de cálculo. En Excel se incluyen tres tipos de herramientas de análisis "y si": tablas de datos, escenarios y búsqueda de objetivo. Los escenarios y las tablas de datos toman conjuntos de valores de entrada y determinan posibles resultados. Una tabla de datos funciona únicamente con una o dos variables, pero puede aceptar muchos valores diferentes para estas variables. Un escenario puede tener muchas variables, pero puede acomodar hasta 32 valores. La búsqueda de objetivo funciona de forma distinta a los escenarios y las tablas de datos ya que toma un resultado y determina los valores de entrada posibles que producen ese resultado.

Tablas de datos Si tiene una fórmula que usa una o dos variables, o varias fórmulas que usan una variable común, puede usar una tabla de datos para ver en un solo lugar todos los resultados de cómo los distintos valores de las variables afectan a las fórmulas. Con el uso de las tablas de datos podrá examinar más fácilmente una variedad de posibilidades de un vistazo. Puesto que se centra en sólo una o dos variables, los resultados son fáciles de leer y compartir en formato tabular. Puede utilizar una tabla de datos de una variable si desea ver de qué manera afectan distintas tasas de interés al pago mensual de una hipoteca. En el siguiente ejemplo, la celda D2 contiene la fórmula de pago =PAGO(B3/12;B4;-B5) que hace referencia a la celda de entrada B3.

La tabla de datos permite ver cómo los cambios en la tasa de interés (B3) afectan al pago mensual (columna D).

Crear una tabla de datos de una variable Se desea analizar cómo afecta el periodo de pago a los pagos mensuales a efectuar por un préstamo. 1. Abra un libro nuevo de Excel. 2. Ingrese los siguientes valores en las celdas mostradas

2

Análisis de datos

3. Digite la lista de valores a sustituir en la celda de entrada en una columna o en una fila. Para este ejemplo la celda de entrada es B3 (el periodo de pago), ya que deseamos saber cuánto es el pago mensual si el periodo de pago es 6, 9, 12, 18, 24 ó 36 meses.

4. Ubique el primer valor de su lista de valores (6); en la celda ubicada a la derecha y una fila por encima (celda C7) digite la fórmula que calcula el pago mensual para un periodo de 12 meses. =PAGO(B2/12,B3,-B1)

3

Microsoft Excel 2013

5. Seleccione el rango de celdas (rango B7:C13) que contiene la fórmula y la lista de valores a ensayar en la celda de entrada.

6. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si, luego en Tabla de datos. se abre el diálogo Tabla de datos.

4

Análisis de datos 7. Como la lista de valores a ensayar en la fórmula está dispuesta en forma de columna, en Celda de entrada (columna) digite la dirección absoluta de la celda de entrada ($B$3), luego haga clic en Aceptar. Excel calcula el pago mensual correspondiente a cada periodo de pago.

8. Para saber cuánto pagaría en total por un préstamo de S/. 10,000 a una tasa de 8% anual para diferentes periodos, en la celda D8 ingrese la fórmula: =B8*C8

9. Finalmente, copie la fórmula en D8 al resto de la columna y guarde el archivo como TablaDatosUnaVariable.xlsx.

5

Microsoft Excel 2013

Agregar una nueva fórmula a una tabla de datos de una variable Para la tabla de datos anterior se desea conocer también cuánto del monto del primer pago corresponde al pago del principal. 1. Abra el archivo TablaDatosUnaVariable.xlsx.

2. En la celda E6 digite la etiqueta Principal Pago1. 3. En la celda E7 digite la fórmula: =PAGOPRIN(B2/12,1,B3,-B1)

6

Análisis de datos

4. Seleccione la tabla de datos incluyendo la columna que contiene la nueva fórmula.

5. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si, y luego en Tabla de datos. Se muestra el diálogo Tabla de datos. 6. En Celda de entrada (columna) especifique la celda $B$3.

7

Microsoft Excel 2013 7. Haga clic en Aceptar. Se calcula la nueva fórmula para los distintos periodos de ensayo.

Crear una tabla de datos de dos variables Se desea conocer cuál será el pago mensual para distintas combinaciones de montos de préstamo y periodos de pago. 1. Abra un libro nuevo de Excel. 2. Ingrese los datos que se muestran a continuación.

3. En la celda B5 ingrese la fórmula que calcula el pago mensual y que hace referencia a las celdas de entrada B1 (monto del préstamo) y B3 (periodo de pago): =PAGO(B2/12,B3,-B1)

8

Análisis de datos

4. Debajo de la celda que contiene la fórmula ingrese los periodos de pago, y a la derecha de la celda con la fórmula ingrese los montos de préstamo a ensayar.

5. Seleccione el rango de celdas que contiene la fórmula y los valores a ensayar.

6. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si, luego en Tabla de datos. Se muestra el diálogo Tabla de datos.

9

Microsoft Excel 2013

7. En Celda de entrada (fila) haga referencia a la celda del monto del préstamo ($B$1), y en Celda de entrada (columna) haga referencia a la celda del periodo de pago ($B$3). Luego haga clic en Aceptar.

8. Guarde el archivo como TablaDatosDosVariables.xlsx.

Actualización de una tabla de datos Las tablas de datos se actualizan cuando se actualiza una hoja de cálculo, aun cuando el contenido de las tablas no haya cambiado. Esta actualización puede tomar tiempo cuando las tablas de datos son grandes. Para acelerar la actualización de una hoja de cálculo que contiene una tabla de datos, se puede cambiar las opciones de Opciones de cálculo para que se actualice automáticamente la hoja de cálculo pero no la tabla de datos. Reconfigurar Opciones de cálculo para todos los libros Excel 1. Haga clic en el Botón de Office, clic en Opciones de Excel, luego clic en la categoría Fórmulas. Se abre el diálogo Opciones de Excel.

10

Análisis de datos

2. En la sección Opciones de cálculo, bajo Cálculo de libro, seleccione Automático excepto para tablas de datos. Luego haga clic en Aceptar. Este cambio en la configuración afecta al libro actual y a todos los libros nuevos. Reconfigurar Opciones de cálculo para el libro actual 1. En la ficha Fórmulas, en el grupo Cálculo, clic en Opciones para el cálculo, luego en Automático excepto en las tablas de datos. Actualizar las tablas de datos cuyo cálculo no es automático 1. En la ficha Fórmulas, en el grupo Cálculo, clic en Calcular ahora (F9) si desea recalcular todo el libro, o clic en Calcular hoja (Mayús+F9) para recalcular solo la hoja actual.

11

Microsoft Excel 2013

Borrar una tabla de datos Borrar toda la tabla 1. Seleccione la tabla de datos. 2. En la ficha Inicio, en el grupo Modificar, clic en el botón Borrar, luego clic en Borrar todo. Borrar los valores resultantes de la tabla 1. En la tabla de datos seleccione los valores resultantes. 2. En la ficha Inicio, en el grupo Modificar, clic en el botón Borrar, luego clic en Borrar contenido.

Escenarios Los escenarios son una herramienta de análisis "y si" para ensayar varios conjuntos de datos en varios casos (escenarios) con distintos valores para cada conjunto. Por ejemplo, suponga que tiene dos escenarios de presupuesto: un caso bueno y otro malo. Puede usar el Administrador de escenarios para crear dos escenarios y, a continuación, cambiar entre ellos. Para cada escenario, debe especificar las celdas que cambiar(1) y los valores que va a usar para dicho escenario. Al cambiar entre los escenarios, la celda de resultado (2) cambia para reflejar los diferentes valores de las celdas cambiantes.

En el ejemplo anterior, cuyo escenario podría denominarse Peor opción, establezca el valor de la celda B1 como 50,000 y el de la celda B2 como 13,200.

Al segundo escenario podría denominarlo Mejor opción y cambiar los valores de B1 a 150,000 y de B2 a 26,000.

12

Análisis de datos

Crear un escenario Antes de crear un escenario, ya debe tener un conjunto de valores inicial en la hoja de cálculo. Para crear informes resumen de escenario que se puedan leer más fácilmente, también debe pensar en dar un nombre a las celdas que planea usar en escenarios. Se desea evaluar distintos escenarios para un préstamo de S/. 10,000 a distintas tasas de interés y periodos de pago. 1. Abra un libro nuevo de Excel. 2. Ingrese los valores que se muestran a continuación:

3. Asigne los siguientes nombres de rango a las siguientes celdas: B1 B2 B3 B4

Préstamo Tasa_de_interés Periodo_de_pago Pago_mensual

4. En la celda B4 ingrese la siguiente fórmula: =PAGO(Tasa_de_interés/12,Periodo_de_pago,-Préstamo)

5. En la ficha Datos, en el grupo Herramientas de datos, clic en Análisis Y si, luego clic en Administrador de escenarios. 6. En el diálogo Administrador de escenarios haga clic en Agregar. Se abre el diálogo Agregar escenario. 7. En Nombre del escenario digite Escenario1. 8. En Celdas cambiantes haga referencia a las celdas B2 y B3. 9. En Protección seleccione las opciones que desee  

Seleccione Evitar cambios para evitar que se edite el escenario cuando la hoja de cálculo está protegida. Seleccione Ocultar para evitar que se muestre el escenario cuando la hoja de cálculo está protegida.

13

Microsoft Excel 2013

10. Haga clic en Aceptar. Se muestra el diálogo Valores del escenario. 11. Especifique los valores a utilizar en las celdas cambiantes para este escenario.

12. Haga clic en Aceptar para crear el escenario. Se muestra el Administrador de escenarios con el escenario recién creado.

14

Análisis de datos

13. Para crear escenarios adicionales repita los pasos del 6 al 12. Luego de crearlos, haga clic en Cerrar del Administrador de escenarios. Debe tener los 3 escenarios que se muestran a continuación. Escenario1

Escenario2

Escenario3

15

Microsoft Excel 2013

Mostrar un escenario 1. En la ficha Datos, en el grupo Administrador de datos, haga clic en Análisis Y si, luego en Administrador de escenarios.

2. En la lista Escenarios del Administrador de escenarios haga clic sobre el escenario deseado, luego clic en Mostrar. Los valores de las celdas muestran el escenario seleccionado.

Crear un informe resumen de escenario 1. En la ficha Datos, en el grupo Administrador de datos, haga clic en Análisis Y si, luego en Administrador de escenarios. 2. En el Administrador de escenarios haga clic en Resumen. Se abre el diálogo Resumen del escenario.

16

Análisis de datos

3. En Tipo de informe seleccione el tipo que desea. 4. En Celdas de resultado haga referencia a las celdas cuyos resultados cambian para los distintos escenarios. 5. Haga clic en Aceptar. Se añade una nueva hoja de cálculo con el resumen del escenario.

6. Guarde el archivo como Escenarios.xlsx.

17

Microsoft Excel 2013

Búsqueda de objetivo Si sabe el resultado que desea de una fórmula, pero no está seguro de cuáles son los datos que requiere la fórmula para obtener dicho resultado, puede usar la función búsqueda de objetivo. Por ejemplo, suponga que tiene que pedir prestado dinero. Conoce la cantidad de dinero que desea, el período en el cual devolverá el préstamo y cuánto puede pagar cada mes. Puede usar la Búsqueda de objetivo para determinar qué tasa de interés debe obtener para cumplir su objetivo de préstamo.

La búsqueda de objetivo funciona con un solo valor de entrada de variable. Si desea determinar más de un valor de entrada, por ejemplo, la cantidad del préstamo y el importe de pago mensual del préstamo, deberá usar el complemento Solver.

Crear una búsqueda de objetivo Para desarrollar este ejemplo utilice los valores de la hoja mostrada en la ilustración debajo. Deberá determinar la tasa de interés que permite que el pago mensual de un préstamo de S/. 100,000 a un plazo de 180 meses sea S/. 900. 1. Abra un libro nuevo de Excel. 2. Ingrese los datos y fórmula mostrados.

3. Enla ficha Datos, en el grupo Herramientas de datos, haga clic en Análisis Y si, luego en Buscar objetivo. Se muestra el diálogo Buscar objetivo. 4. En Definir la celda escriba la referencia a la celda que contiene la fórmula a utilizar (celda B4). 5. En Con el valor digite el resultado deseado para la fórmula (en este ejemplo es 900). 6. En Cambiando la celda indique la referencia a la celda cuyo valor se desea ajustar (en este ejemplo es la tasa de interés, celda B2).

18

Análisis de datos

7. Haga clic en Aceptar. Se muestra el diálogo Estado de la búsqueda de objetivo.

8. Haga clic en Aceptar. La tasa de interés buscada es 7.02%. Guarde el archivo como BuscarObjetivo.xlsx.

19