Tabla dinamica

Monday, June 13, 2005

Pasos para crear una tabla dinamica

Para crear tablas dinámicas hemos de tener previamente una tabla de datos preparada y posteriormente acceder a Datos – Asistente para tablas dinámicas.
1. Crea la siguiente tabla de datos:
2. Selecciona toda la tabla y accede a Datos – Asistente para tablas dinámicas.
En primer lugar aparece una pantalla que representa el primer paso en el asistente para tablas dinámicas. Aceptaremos la tabla que hay en pantalla.
3. Pulsa en Siguiente.
4. Acepta el rango pulsando en Siguiente.
En el siguiente paso, Excel nos muestra la pantalla de diseño de la tabla.
Los campos del origen de los datos están situados en la parte derecha del cuadro de diálogo. Aquí veremos la estructura final que tendrá la tabla. Lo que hay que hacer es “arrastrar” los campos de la derecha hacia la posición deseada en el interior de la tabla.
5.
Arrastra los campos de la parte derecha según se ve en la ilustración:
6. Pulsa en Siguiente.
Como último paso, Excel nos propone crear la tabla en la misma hoja de trabajo a partir de una celda determinada, o bien en una hoja completamente nueva (opción elegida por defecto).
7. Asegúrate de que está activada esta última opción y pulsa en Terminar.
Observa el resultado de la tabla dinámica. Hemos diseñado la estructura para que nos muestre los productos en su parte izquierda, los meses en columnas, y además, el precio de cada producto en la intersección de la columna.
Observa también que se han calculado los totales por productos y por meses.
Si modificamos algún dato de la tabla original, podemos actualizar la tabla dinámica desde la opción Datos – Actualizar datos siempre que el cursor esté en el interior de la tabla dinámica.
Al actualizar una tabla, Excel compara los datos originales. Pero si se han añadido nuevas filas, tendremos que indicar el nuevo rango accediendo al paso 2 del Asistente. Esto podemos hacerlo accediendo nuevamente a Datos – Asistente para tablas dinámicas y volviendo atrás un paso.
Es posible que al terminar de diseñar la tabla dinámica nos interese ocultar algún subtotal calculado. Si es así, debemos pulsar doble click en el campo gris que representa el nombre de algún campo, y en el cuadro de diálogo que aparece, elegir la opción Ninguno. Desde este mismo cuadro podemos también cambiar el tipo de cálculo.
Es posible también mover los campos de sitio simplemente arrastrando su botón gris hacia otra posición. Por ejemplo, puede ser que queramos ver la tabla con la disposición de los campos al revés, es decir, los productos en columnas y los meses en filas.
Si no está al crear la tabla, podemos activar la visualización de la barra de herramientas para tablas dinámicas (Ver- Barras de herramientas – Tablas dinámicas).
Desde aquí podemos realizar operaciones de actualización, selección de campos, ocultar, resumir, agrupar, etc. Puedes practicar sin miedo los diferentes botones de la barra.
Búsqueda de objetivos
Hay veces en los que al trabajar con fórmulas, conocemos el resultado que se desea obtener, pero no las variables que necesita la fórmula para alcanzar dicho resultado. Por ejemplo, imaginemos que deseamos pedir un préstamo al bando de 2.000.000 de pts y disponemos de dos años para pagarlo. Veamos cómo se calcula el pago mensual:
La función =PAGO(interés/12;período*12;capital) nos da la cuota mensual a pagar según un capital, un interés y un período en años.
1.
Escribe los siguientes datos:
2. Escribe en la celda B5 la fórmula: =PAGO(B2/12;B3*12;B1).
3. Quita los decimales.
4. Vemos que la cuota a pagar es de 87.296 Pts.
La función =PAGO() siempre nos dará el resultado en números negativos. Si queremos convertirlo en resultado positivo, debemos encerrar la función en otra función: la función =ABS() Esta función convierte cualquier número en positivo (valor absoluto)
5. Modifica la función y escribe: =ABS(PAGO(B2/12;B3*12;B1))
Ahora podemos variar los valores de las tres casillas superiores para comprobar diferentes resultados. Pero vamos a lo que vamos: imaginemos que sólo disponemos de 80.000 pts para pagar cada mes. El banco actual nos ofrece un interés del 4,5%, así que vamos a ver qué interés tendríamos que conseguir para llegar a pagar las 80.000 que podemos pagar. Podríamos ir cambiando manualmente la celda del interés hasta conseguir el resultado requerido, pero a veces hay cálculos complejos y nos llevaría tiempo ir probando con decimales hasta conseguirlo.
Para ello, tenemos la opción Buscar objetivos, a través de la cual Excel nos proporcionará el resultado buscado.
5. Sitúa el cursor en B5 si no lo está ya.
6. Accede a Herramientas – Buscar objetivos.
7. Rellena las casillas como ves a continuación y acepta el cuadro.
Excel avisa que ha hallado una solución al problema.
8. Acepta este último cuadro de diálogo.
Sin embargo, si observas la celda del interés, aparece en negativo, por lo que el resultado no ha sido el esperado (evidentemente, el banco no nos va a pagar el interés a nosotros), por lo que nos vemos obligados a cambiar otra celda.
El capital no podemos cambiarlo. Necesitamos los 2.000.000, así que vamos a intentarlo con los años.

9. Deshaz la última acción desde
10.
Vuelve a preparar las siguientes casillas:
11. Acepta la solución de Excel.
Observa que han aparecido decimales, pero ya sabemos que podemos cambiar el número de meses a pagar si es que no podemos tocar el interés. Quita los decimales. Necesitaremos dos años y dos meses.
Posiblemente otro banco nos ofrezca un interés más bajo, por lo que podemos volver a buscar un nuevo valor para el período.
Para trabajar con la opción de Buscar objetivos, hay que tener presente lo siguiente:
· Una celda cambiante (variable) debe tener un valor del que dependa la fórmula para la que se desea encontrar una solución específica.
· Una celda cambiante no puede contener una fórmula.
· Si el resultado esperado no es el deseado, debemos deshacer la acción.
Tablas de datos de una y dos variables
Existe otro método para buscar valores deseados llamado tablas de variables. Existen dos tipos de tablas:
· Tabla de una variable: utilizada cuando se quiere comprobar cómo afecta un valor determinado a una o varias fórmulas.
· Tabla de dos variables: para comprobar cómo afectan dos valores a una fórmula.
A continuación modificaremos la tabla de amortización del préstamo de forma que Excel calcule varios intereses y varios años al mismo tiempo. Para crear una tabla hay que tener en cuenta:
· La celda que contiene la fórmula deberá ocupar el vértice superior izquierdo del rango que contendrá el resultado de los cálculos.
· Los diferentes valores de una de las variables deberán ser introducidos en una columna, y los valores de la otra variable en una fila, de forma que los valores queden a la derecha y debajo de la fórmula.
· El resultado obtenido es una matriz, y deberá ser tratada como tal (ver lección 8)
1. Prepara la siguiente tabla. En ella, hemos dispuesto varios tipos de interés y varios años para ver distintos resultados de una sola vez.
2. Selecciona el rango B5:F9 y accede a Datos – Tabla
3.
Rellena las casillas como ves a continuación y acepta.
4.
Debes seleccionar el rango C6:F9 y arreglarlo de forma que no se vean decimales, formato millares y ajustar el ancho de las columnas.
De esta forma podemos comprobar de una sola vez varios años y varios tipos de interés.
Escenarios
Un Escenario es un grupo de celdas llamadas Celdas cambiantes que se guarda con un nombre.
1. Haz una copia de la hoja con la que estamos trabajando y en la copia, modifica los datos:
2. Accede a Herramientas – Escenarios y pulsa en Agregar.
3. Rellena las casillas tal y como ves en la página siguiente:
4. Acepta el cuadro de diálogo.
5. Vuelve a aceptar el siguiente cuadro de diálogo.
6. Vuelve a pulsar en Agregar.
7.
Colócales el nombre:
8.
Acepta y modifica el siguiente cuadro:
9. Acepta y agrega otro escenario.
10.
Vuelve a escribir igual que antes:
11. Acepta y modifica la línea del interés:
11. Acepta.
Acabamos de crear tres escenarios con distintas celdas cambiantes para un mismo modelo de hoja y una misma fórmula.
12. Selecciona el primer escenario de la lista y pulsa en Mostrar. Observa el resultado en la hoja de cálculo.
13. Haz lo mismo para los otros dos escenarios. Muéstralos y observa el resultado.
Podemos también crear un resumen de todos los escenarios existentes en una hoja para observar y comparar los resultados.
14. Pulsa en Resumen y acepta el cuadro que aparece.
Observa que Excel ha creado una nueva hoja en formato de sub-totales (o en formato tabla dinámica si se hubiera elegido la otra opción). Esta hoja puede ser tratada como una hoja de sub-totales expandiendo y encogiendo niveles.
Solver
El programa Solver se puede utilizar para resolver problemas complejos, creando un modelo de hoja con múltiples celdas cambiantes.
Para resolver un problema con Solver debemos definir:
· La celda objetivo (celda cuyo valor deseamos aumentar, disminuir o determinar)
· Las celdas cambiantes (son usadas por Solver para encontrar el valor deseado en la celda objetivo)
· Las restricciones (límites que se aplican sobre las celdas cambiantes)
1. Crea la hoja que viene a continuación teniendo en cuenta las fórmulas de las siguientes celdas:
B9 =B4-B8 B3 =35*B2*(B6+3000)^0,5
B4 =B3*35
(Al margen de las típicas sumas de totales)

Hemos calculado el beneficio restando los gastos de los ingresos. Por otro lado, los ingresos son proporcionales al número de unidades vendidas multiplicado por el precio de venta (35 pts).
2. Observa la fórmula de la celda B3
Las unidades que esperamos vender en cada trimestre son el resultado de una compleja fórmula que depende del factor estacional (en qué períodos se espera vender) y el presupuesto en publicidad (supuestas ventas favorables). No te preocupes si no entiendes demasiado esta fórmula.
3. Sitúa el cursor en F9.
El objetivo es establecer cuál es la mejor distribución del gasto en publicidad a lo largo del año. En todo caso, el presupuesto en publicidad no superará las 40.000 anuales.
Resumiendo: queremos encontrar el máximo beneficio posible (F9), variando el valor de unas determinadas cedas, que representan el presupuesto en publicidad(B6:E6), teniendo en cuenta que dicho presupuesto no debe exceder las 40.000 pesetas al año.
4.
Elige Herramientas – Solver.
La Celda objetivo es aquella cuyo valor queremos encontrar (aumentándolo o disminuyéndolo).
El campo Cambiando las celdas indicará las celdas cuyos valores se pueden cambiar para obtener el resultado buscado. En nuestro ejemplo serán aquellas celdas donde se muestra el valor del gasto en publicidad para un período determinado.
5. Sitúa el cursor en el campo Cambiando las celdas y pulsa el botón rojo (minimizar diálogo).
6. Introduce (o selecciona con el ratón) el rango B6:E6.
7. Vuelve a mostrar el cuadro de diálogo desde el botón rojo.
A continuación vamos a añadir las restricciones que se deberán cumplir en los cálculos. Recuerda que el presupuesto en publicidad no excederá las 40.000 pts.
8. Pulsa el botón Agregar.
9. Pulsa F6 en la hoja de cálculo.
10. Haz click en el campo Restricción.
11. Escribe el valor: 40000.
12. Pulsa el botón Agregar del mismo cuadro de diálogo.
Otra restricción es que el gasto de cada período sea siempre positivo.
13. Pulsa en el gasto de publicidad del primer período B6.
14. Elige el operador >= de la lista del medio y completa el cuadro de la siguiente forma:
15. Introduce las demás restricciones correspondientes a los tres períodos que faltan de la mima forma.
16. Acepta el cuadro para salir al cuadro de diálogo principal.
17. Pulsa en el botón Resolver.
Observa que Excel ha encontrado una solución que cumple todos los requisitos impuestos. Ahora podemos aceptarla o rechazarla.
18. Pulsa en Aceptar.
Observa que ahora la hoja de cálculo muestra el beneficio máximo que podemos conseguir jugando con el presupuesto en publicidad.
Como detalle curioso, observa cómo no deberíamos programar ninguna partida presupuestaria para la publicidad del primer período.
Configuración del Solver
Desde Herramientas – Solver (botón Opciones...) tenemos varias opciones para configurar Solver. Las más importantes son:
· Tiempo máximo: segundos transcurridos para encontrar una solución. El máximo aceptado es de 32.767 segundos.
· Iteraciones: número máximo de iteraciones o cálculos internos.
· Precisión: número fraccional entre 0 y 1 para saber si el valor de una celda alcanza su objetivo o cumple un límite superior o inferior. Cuanto menor sea el número, mayor será la precisión.
· Tolerancia: tanto por ciento de error aceptable como solución óptima cuando la restricción es un número entero.
· Adoptar modo lineal: si se activa esta opción, se acelera el proceso de cálculo.
· Mostrar resultado de iteraciones: si se activa, se interrumpe el proceso para visualizar los resultados de cada iteración.
· Usar escala automática: se activa si la magnitud de los valores de entrada y los de salida son muy diferentes.

0 Comments:

Post a Comment

<< Home