Tdi

Tablas Dinámicas en Excel 2000 Debes elaborar los ejercicios que se indican en este material en Microsoft Excel 2000.

Índice: • • • • • • • • • •

Que es una Tabla Dinámica en Excel Crear una Tabla Dinámica- Ubicación- Diseño Eliminar una Tabla Dinámica Aplicar Filtros a la Tabla Obtener Promedios Gráficos con tablas dinámicas Ejercicio 2 Ejercicio 3 Ejercicio 4 Ejercicio 5

Humberto

1

Que es una Tabla Dinámica en Excel Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen. Debe ser muy importante la cantidad de información a manejar para que el uso de la tabla dinámica se justifique la creación de una tabla dinámica.

Crear una tabla dinámica Veamos cómo podemos crear una tabla dinámica a partir de unos datos que ya tenemos. Para crear una tabla dinámica, Excel nos proporciona el Asistente para tablas y gráficos dinámicos. Captura la tabla que aparece a lado en Excel, supongamos que tenemos una colección de datos de los artículos del almacén con el número de referencia y el mes de compra, además sabemos la cantidad comprada y el importe del mismo. Vamos a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes.

Después de haber creado la tabla anterior procedemos a crear la tabla dinámica, para esto, vamos al asistente, accedemos al menú Datos  Informe de tablas y gráficos dinámicos. Aparece la primera pantalla de tres de las que se compone el asistente para tablas dinámicas, donde nos solicita que indiquemos la situación de los datos a analizar y qué tipo de informe vamos a crear.

Humberto

2

En nuestro caso, indicamos que vamos a seleccionar los de la Lista de Excel y que vamos a crear una Tabla dinámica. En la segunda pantalla seleccionamos el rango dónde tenemos los datos a utilizar en la tabla dinámica. Debemos seleccionar todas las celdas incluyendo las cabeceras… una vez dado el rango pulsamos en siguiente>

A continuación nos aparece el tercer paso del asistente...

En esta última pantalla debemos indicar dónde vamos a situar el resultado de la tabla dinámica. Hemos escogido situarla en una Hoja de cálculo nueva. Pulsamos sobre Diseño... accedemos a un cuadro de diálogo donde podemos personalizar la forma en que van a verse los datos en la tabla dinámica.

Humberto

3

En la imagen podemos apreciar que nos aparece una estructura formada por las filas, columnas, datos y además un campo para el nº de página. Y en el lateral derecho tenemos los campos disponibles de la tabla, los cuales podemos arrastrar a las diferentes partes del diagrama de la izquierda. Por ejemplo podríamos construir una tabla dinámica con la siguiente estructura: - En el campo Fila arrastramos REF, las referencias de nuestros artículos. - En el campo Columna arrastramos MES. - Y en Datos arrastramos IMPORTE Nos quedaría una estructura como podemos ver en la imagen de abajo.

Tras realizar la tabla dinámica (pulsar Aceptar y luego en Finalizar) este sería el resultado obtenido.

Podemos ver que la estructura es la que hemos definido anteriormente, en el campo fila tenemos las referencias, en el campo columnas tenemos los meses y en el centro de la tabla las sumas de los importes. Con esta estructura es mucho más fácil analizar los resultados. Una vez creada la tabla dinámica nos aparece en la hoja, la barra de herramientas Tabla dinámica:

Humberto

4

(La podemos visualizar y ocultar con la opción de menú Ver  Barras de Herramientas.) En esta barra nos encontramos botones útiles como el botón para darle a la tabla un formato predefinido, y otros que veremos a lo largo del tema. También aparece el cuadro Lista de campos de tabla dinámica que nos permite modificar la estructura de la tabla añadiendo campos a las distintas zonas, o bien utilizando el botón Agregar a en combinación con el cuadro de lista que aparece al lado del botón, o simplemente arrastrándolo el campo sobre la zona deseada. De igual forma podemos quitar un campo de una zona arrastrándolo fuera de la tabla. Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados sin casi esfuerzos. Esta lista de campos se puede mostrar u ocultar con el botón námica.

de la barra Tabla di-

Si arrastrásemos a la zona de datos los campos cantidad y total, obtendríamos la siguiente tabla, más compleja pero con más información:

Eliminar una Tabla dinámica Para eliminar una tabla dinámica simplemente debemos seleccionar la tabla en su totalidad y presionar la tecla Supr o con la tabla seleccionada hacer clic con el botón derecho del ratón y seleccionar Eliminar del menú Pop-up.

Humberto

5

Aplicar filtros a una tabla dinámica Otra característica útil de las tablas dinámicas es permitir filtrar los resultados y así visualizar únicamente los que nos interesen en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es importante. Los campos principales vemos que están acompañados, en su parte derecha, de una flecha indicando una lista desplegable. Por ejemplo, si pulsamos sobre la flecha del campo Mes nos aparece una lista como vemos en la imagen con los distintos meses disponibles en la tabla con una casilla de verificación en cada uno de ellos para indicar si los queremos ver o no, más una opción para marcar todas las opciones en este caso todos los meses. Si dejamos marcados los meses Enero y Febrero, los otros meses desaparecerán de la tabla, pero no se pierden, en cualquier momento podemos visualizarlos volviendo a desplegar la lista y marcando la casilla (Mostrar todo). Para cerrar este cuadro debemos pulsar en Aceptar o sobre Cancelar para cerrar y dejarlo como estaba. Aplicando el filtro a varios campos podemos formar condiciones de filtrado más complejas, por ejemplo podemos seleccionar ver los artículos con referencia 1236 de Abril.

Obtener promedios en una tabla dinámica Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatorio, puede interesarnos modificar esas fórmulas por otras como pueden ser sacar el máximo o el mínimo, el promedio, etc. Para hacer esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botón derecho del ratón, nos aparece un menú Pop-up con diferentes opciones, debemos escoger la opción Configuración de campo... y nos aparece un cuadro de diálogo como el que vemos en la imagen. En este cuadro de diálogo podemos escoger cómo queremos hacer el resumen, mediante Suma, Cuenta, Promedio, etc. También podemos abrir el cuadro de diálogo con el botón barra Tabla dinámica.

de la

Gráficos con tablas dinámicas Para crear una gráfica de nuestra tabla dinámica debemos hacer clic con el botón derecho del ratón sobre cualquier celda de la tabla, y del menú Pop-up que nos aparece debemos seleccionar Gráfico dinámico, nos genera un gráfico de barras por defecto en una nueva hoja de gráficos. También podemos utilizar el botón

de la barra Tabla dinámica.

Para cambiar el formato del gráfico a otro tipo de gráfico que nos agrade más o nos convenga más según los datos que tenemos, debemos hacer clic con el botón derecho del ratón sobre el gráfico y del menú Pop-up que nos aparece escoger la opción Tipo de gráfico... y escoger el gráfico que más nos convenga, la mecánica a seguir para trabajar con gráficos es la misma que se vio en el tema de gráficos.

EJERCICIO 2 Humberto

6

1 Si no tienes abierto Excel2003, ábrelo para realizar el ejercicio. 2 Abre el libro de trabajo Tabla dinámica que encontrarás en la carpeta Ejercicios del curso. Te aparecerán los siguientes datos:

3 Guarda el libro con el mismo nombre en tu carpeta Mis documentos. Vamos a crear una tabla dinámica para interpretar mejor estos datos. 4 Selecciona el rango A1:G15 5 Selecciona del menú Datos la opción Informe de tablas y gráficos dinámicos. Se abre el asistente para tablas y gráficos dinámicos. 6 Marca Lista o base de datos de Microsoft Office Excel 7 Marca Tabla dinámica. 8 Pulsa en Siguiente> 9 En Rango: aparecerá el rango que has seleccionado, sino selecciona el rango A1:G15, el rango de datos que se corresponde con la tabla completa, incluyendo la cabecera. 10 Pulsa en Siguiente >. 11 Marca Hoja de cálculo nueva. 12 Presiona sobre el botón Diseño... para definir las zonas de la tabla dinámica. 13 Arrastra el campo Mes donde pone Página. 14 Arrastra el campo Semana donde pone Columna. 15 Arrastra el campo Cliente donde pone Fila. 16 Arrastra el campo Artículo donde pone Datos. 17 Arrastra el campo Total donde pone Datos. 18 Presiona sobre Aceptar. Volvemos a la tercera pantalla de asistente. Humberto

7

19 Haz clic sobre Finalizar. Nos aparece la tabla dinámica en una nueva hoja. En la zona de página vemos (Todas) esto significa que estamos visualizando el resumen de todos los meses. 20 Abre la lista desplegable del campo MES, selecciona Enero y pulsa Aceptar. Ahora nos aparece el resumen del mes de Enero. 21 Abre la lista desplegable del campo MES, selecciona (Todas) y pulsa Aceptar. Nos vuelve a aparecer el resumen completo. Para cada cliente y semana podemos ver la suma del campo ARTICULO y la suma del campo TOTAL. Con la suma del campo TOTAL tenemos la suma de lo que hemos vendido, pero con la suma del campo artículo no obtenemos información útil ya que el campo ARTICULO contiene códigos de artículos, nos sería más interesante saber cuántas ventas hemos realizado, contando los artículos del campo ARTICULO. Para ello debemos cambiar la operación que se efectúa sobre el campo. 22 Posiciónate en cualquier celda correspondiente a la suma de ARTICULO y haz clic con el botón derecho. 23 Selecciona la opción Configuración de campo, también puedes utilizar el botón

de la barra Tabla dinámica.

24 En el cuadro de diálogo que se abre, selecciona la operación Cuenta y haz clic en Aceptar. Observa el resultado. Ahora vamos a quitar ese total de la tabla dinámica. 25 Despliega la lista Datos, desmarca el campo Cuenta de ARTICULO y haz clic en Aceptar. En la zona de datos ahora queda únicamente la suma de TOTAL. Para añadir un nuevo resumen a la zona de datos. 26 Arrastra de la lista de campos, el campo CANTIDAD y suéltalo sobre la zona de datos. Es tan fácil como esto, ahora si no quieres una suma sino otra operación, la puedes cambiar como lo hemos hecho antes con el campo ARTICULO. 27 Por último cierra el libro de trabajo guardando los cambios realizados.

EJERCICIO 3 Capturar la siguiente tabla: Humberto

8

2 Crear una tabla dinámica a partir de la tabla anterior para saber de cada coche, en cada mes, cuántas horas hemos empleado en repararlo y el desembolso realizado. 3 Visualizar únicamente las reparaciones del mes de Enero. 4 Crear una gráfica de la tabla dinámica. 5 Guardar los cambios realizados.

EJERCICIO 4 1. Capturar la siguiente tabla que es acerca de la circulación de vehículos a través de una estación de peaje. A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Mes Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero

B Semana

C D Vehículo Cantidad 1Auto 105000 1Camión 1050 1Ómnibus 1575 1Camioneta 2100 1Moto 583 2Auto 120750 2Camión 1208 2Ómnibus 1411 2Camioneta 2015 2Moto 485 3Auto 122350 3Camión 1124 3Ómnibus 1685 3Camioneta 2247 3Moto 630 4Auto 99000 4Camión 990 4Ómnibus 1485 4Camioneta 1980 4Moto 544

2. Crear la Tabla Dinámica Humberto

9

a) ¿Dónde están los datos que desea analizar? Marcar opción: Lista o base de datos de Microsoft Excel b) ¿Qué tipo de informe desea crear? Marcar la opción: Tabla dinámica c) clic en Siguiente d) Seleccionar el rango de la tabla, incluyendo la fila de titulo (puede aparecer seleccionada por default) e) Siguiente

3° Paso

Humberto

Ubicación de la Tabla 10

f)

Se adopto ubicar la tabla en la misma hoja de cálculo, determinando la celda de comienzo de ejecución de la misma g) Se indica , clic en Diseño , comienza el momento de diseñar la tabla

h) i) j) k)

Arrastrar el cuadrito de Semana a Columna Arrastrar el cuadrito de Vehículo a Fila Arrastrar el cuadrito de Cantidad a Datos Aceptar.

Humberto

11

Aparece la tabla dinámica diseñada, acompañada por una barra de herramientas especial que permite filtrar datos por despliegue de las mismas.

Suma de Cantidad Vehículo Auto Camión Camioneta Moto Ómnibus Total general

Semana 1 105000 1050 2100 583 1575 110308

2 120750 1208 2015 485 1411 125869

3 122350 1124 2247 630 1685 128036

4 99000 990 1980 544 1485 103999

Total general 447100 4371 8342 2242 6157 468212

3. Aplicar Filtros a la Tabla Resultado de la tabla al aplicarle los filtros correspondientes: En primer lugar se despliega la lista en semana y se desactiva la 1° y 2°

En segundo lugar se despliega la lista en vehículo y se desactivan las opciones –Camión- Camioneta- Moto

Humberto

12

Suma de Cantidad Vehículo Auto Ómnibus Total general

Semana 3 122350 1685 124035

4 99000 1485 100485

Total general 221350 3170 224520

4. Cambiar el diseño de la tabla Siguiendo el mismo procedimiento se puede cambiar el diseño de la tabla, por ejemplo a) Arrastrar el cuadrito de Vehículo a Columna b) Arrastrar el cuadrito de Semana a Fila c) Arrastrar el cuadrito de Cantidad a Datos d) Aceptar. Obteniéndose el siguiente el siguiente resultado.

Suma de Cantidad Semana

Total general

Humberto

Vehículo Auto Camión Camioneta Moto 1 105000 1050 2100 2 120750 1207.5 2015 3 122350 1123.5 2247 4 99000 990 1980 447100 4371 8342

Ómnibus Total general 583 1575 110308 485 1411.25 125868.75 630 1685.25 128035.75 544 1485 103999 2242 6156.5 468211.5

13

5. Aplicar Filtros a la Tabla Siguiendo con el mismo criterio, aplicando en Vehículos se dejan activados –Auto-Camión En Semana se deja activada solamente la 1° y 3° Semana. De lo ejecutado surge el siguiente resultado. Suma de Cantidad Vehículo Semana Auto Camión Total general 1 105000 1050 106050 3 122350 1123.5 123473.5 Total general 227350 2173.5 229523.5 6. Obtención de Subtablas Haciendo doble clic sobre una celda, en este caso por ejemplo la segunda de Auto, donde figura la cantidad 122350, Excel automáticamente produce un detalle en hoja aparte según lo siguiente. Mes Semana Vehículo Cantidad Enero 3Auto 122350

Humberto

14