EJERCICIO 18 DE EXCEL

EXCEL EJERCICIO 18

TABLAS DINÁMICAS Y GRÁFICOS DINÁMICOS

Tablas dinámicas

Las tablas dinámicas son una herramienta relacionada con las listas o bases de datos creadas en hojas de cálculo. Sirven para organizar de manera sencilla y rápida grandes cantidades de datos procedentes de dichas listas. Permiten agrupar y filtrar la información por cualquiera de los campos de la lista, creando una tabla-resumen.

ACTIVIDAD A REALIZAR Disponemos de una hoja de cálculo con las horas trabajadas durante una semana por los programadores de una empresa en unos determinados proyectos, tal como aparece en la siguiente tabla:

A

B

C

D

Fecha 22/02/2011 22/02/2011 23/02/2011 24/02/2011 23/02/2011 26/02/2011 25/02/2011 26/02/2011 25/02/2011

Horas 9,5 3,6 5,5 8 4 10 4 7 6,5

1

Empleado 3 Rodríguez 4 Pérez 5 Roig 6 Pérez 7 Rodríguez 8 Rodríguez 9 Roig 10 Pérez 11 Roig 2

Proyecto Contaplus Elite Contaplus Elite Nómina Plus Factplus Nómina Plus Factplus Contaplus Elite Factplus Contaplus Elite

Crea esta tabla en la Hoja 1 (llamada Horas trabajadas) de un libro de Excel que guardarás con el nombre 18ex Tablas y gráficos dinámicos

1

EJERCICIO 18 DE EXCEL Queremos crear una tabla dinámica que muestre, para cada programador empleado de la empresa, en páginas individuales, el total de horas trabajadas en cada uno de los proyectos durante la última semana.

PROCEDIMIENTO: 1º. Sitúate en una de las celdas que contienen los datos (rango A2:D11) y, en la pestaña Insertar, haz clic en el botón Tabla dinámica. 2º. El cuadro de diálogo emergente permite seleccionar el rango de celdas en el que están situados los datos a organizar. Si el propio asistente indica el rango correcto ($A$2:$D$11), pulsa Aceptar

Al dejar activada esta opción indicamos que la tabla dinámica se inserte en una nueva hoja. Acepta y, en una hoja nueva, se mostrará el cuadro de abajo.

2

EJERCICIO 18 DE EXCEL

En este caso queremos mostrar en la tabla dinámica todos los datos (campos) así que activa todas las casillas.

El programa coloca automáticamente los campos, según el tipo de dato, como rótulo (de columna o de fila) o valor (datos numéricos con los que puedan realizarse operaciones). Ahora, arrastra el campo Empleado al cuadro Filtro de informe y el campo Fecha al cuadro Rótulos de columnas.

El resutado es la tabla dinámica siguiente:

3

EJERCICIO 18 DE EXCEL La tabla nos informa, para todos los empleados, de: -

En qué proyectos han intervenido La distribución de las horas por fechas Las horas totales dedicadas, por fechas y por proyecto.

En caso de que sólo nos interese la información relativa a uno o dos de los empleados, hacemos clic en la flecha a la derecha de Todas y configuramos la lista como se muestra a continuación:

Lo mismo vale para los campos Proyecto y Fecha, que, además, permiten aplicar múltiples opciones de filtrado (ya vistas en ejercicios anteriores). Para acabar, selecciona sucesivamente las celdas A3, A4 y B3 y modifica su contenido de forma que el resultado final sea el siguiente:

A la hora de organizar los datos en nuestro ejemplo deberá tenerse en cuenta lo siguiente:  El campo colocado en el cuadro Filtro de informe aparece en la esquina superior izquierda de la tabla dinámica en forma de una lista desplegable desde la que se podrá seleccionar aquel elemento (o elementos) del que se desee mostrar el resumen.

4

EJERCICIO 18 DE EXCEL  El campo que se coloque en la sección Rótulos de fila mostrará sus elementos como encabezados o títulos de las filas en la tabla  El campo que se coloque en la sección Rótulos de columna, mostrará sus elementos como encabezados de las columnas de la tabla  En cuanto al campo situado en la sección Valores, sus datos se someterán a una determinada operación de cálculo: Suma (es la que se ofrece por defecto cuando los datos de este campo son todos numéricos), Contar (la que se ofrece por defecto en los demás casos), Promedio, Mínimo, Máximo, Producto, etc.

Para dar a la tabla un formato algo más presentable, haz clic con el botón derecho en cualquier celda de la tabla dinámica y activa la pestaña Diseño de la cinta de opciones. Despliega la lista del apartado Estilos de tablas dinámicas y, del apartado Oscuro, elige la segunda de la primera fila:

5

EJERCICIO 18 DE EXCEL El aspecto final será como el que se muestra abajo:

La ventaja de las tablas dinámicas (y lo que les da el nombre) es su gran flexibilidad para analizar una información desde diferentes perspectivas. Ya que es posible, en cualquier momento cambiar su diseño simplemente seleccionando cualquiera de sus celdas y modificando la configuración del panel derecho Lista de campos de tabla dinámica

Notas: a) La forma elegida aquí para organizar los datos sólo es una de entre todas las posibles. Cabe organizar los datos de otra manera; no obstante, hemos de procurar que la forma elegida sea la más clara y fácil de interpretar. b) Aunque los datos de una tabla dinámica tienen el mismo aspecto que cualquier hoja de cálculo, no se pueden introducir ni editar los datos directamente en ella. Para modificar sus resultados deberán modificarse forzosamente los datos a partir de los cuales se ha creado. c) No obstante, las tablas dinámicas no se actualizan automáticamente cuando los datos de origen cambian, sino que, cambiados los datos fuente es necesario seleccionar con el botón derecho del ratón una celda cualquiera de la tabla y elegir la opción Actualizar del menú contextual correspondiente.

Gráficos dinámicos Para acabar, es posible también presentar la información de la tabla dinámica en un gráfico que será también dinámico, ya que podremos cambiar en el mismo los datos que deseamos representar.

ACTIVIDAD A REALIZAR Muestra en un gráfico dinámico de tipo Columnas apiladas la información de la tabla dinámica creada en la actividad anterior.

6

EJERCICIO 18 DE EXCEL

PROCEDIMIENTO Para crear el gráfico, selecciona cualquier celda de la tabla dinámica y activa la pestaña Opciones de la cinta de opciones. Haz clic en el botón Gráfico dinámico.

Como tipo de gráfico elige éste (columnas apiladas) y acepta.

El gráfico se inserta en la misma hoja de la tabla dinámica y tendrá este aspecto:

En este caso, la columna de Nomina Plus incluye un solo color debido a que todas las horas dedicadas a ese proyecto coincidieron en la misma fecha (cosa que no ocurre con los otros proyectos)

7

EJERCICIO 18 DE EXCEL Para modificar el aspecto del gráfico de forma dinámica, utilizaremos el Panel de filtros del gráfico dinámico que ha aparecido al mismo tiempo que el gráfico. P.ej, para ocultar el proyecto Contaplus Elite configuraríamos el panel como se muestra a continuación (no hace falta que lo hagas):

Hay que tener en cuenta que: - el aspecto del gráfico dependerá de los campos que hayamos incluido en la tabla dinámica y de la distribución que les hayamos dado. - es posible cambiar el aspecto del gráfico moviendo los campos de un área a otra en el panel derecho Lista de campos de tabla dinámica. P.ej, moviendo el campo Fecha de Campos de leyenda a Campos de eje). Aunque, en este caso, la tabla dinámica también cambiará. Para acabar, aplicando los procedimientos vistos en los ejercicios 8 y 9 de Excel, cambia el gráfico a una hoja nueva y muestra el título del gráfico y el del eje vertical: -

Título del gráfico: Proyectos de trabajo Título del eje Y (vertical): Horas dedicadas

Cambia el nombre de la hoja con el gráfico a Gráfico dinámico. 8

EJERCICIO 18 DE EXCEL

Tablas dinámicas con datos externos

Finalmente, también es posible crear una tabla dinámica utilizando datos de otros libro de Excel (o incluso de archivos de datos en otros formatos, pero eso sería adelantarnos demasiado).

ACTIVIDAD A REALIZAR Descarga de nuevo de la web del módulo el archivo 15ex clientes EstilGarden.xlsx pero renómbralo como 18ex datos tabla dinámica.xlsx. En una nueva hoja (que llamarás Datos externos) del libro de Excel 18ex Tablas y gráficos dinámicos crea, a partir de los datos contenidos en el archivo 18ex datos tabla dinámica, una tabla dinámica que muestre, para cada provincia, cuántos clientes pagan al contado y cuántos a 30 días.

PROCEDIMIENTO Haz clic en la celda A1 de la nueva hoja Datos externos y, luego, en el botón Tabla dinámica de la pestaña Insertar de la cinta de opciones.

Activa la opción Utilice una fuente de datos externa y luego, haz clic en Elegir conexión.

En el cuadro de diálogo Conexiones existentes haz clic en el botón (abajo) Examinar en busca de más… Busca y haz doble clic sobre el archivo 18ex datos tabla dinámica.

9

EJERCICIO 18 DE EXCEL

En este cuadro, no cambies nada y haz clic en Aceptar.

En el cuadro Crear tabla dinámica, finalmente, haz clic en Aceptar. Para conseguir la tabla dinámica solicitada, activa sólo los campos Provincia, Nombre y Forma de pago. Colócalos así: -

PROVINCIA: en el área Filtro de informe NOMBRE: en el área Valores (se le asignará automáticamente una función de cuenta) FORMA DE PAGO: en el área Rótulos de fila

Asimismo, cambia las celdas A3 y B3 para que digan, respectivamente, Forma de pago y Nº de clientes. Para acabar, aplica a la nueva tabla dinámica alguno de los estilos de la lista de la pestaña Opciones. Un posible aspecto final de la tabla es el que sigue:

ACTIVIDAD A REALIZAR Como repaso de la parte de gráficos dinámicos, crea, a partir de la tabla dinámica anterior un gráfico dinámico circular (en 3D) que muestre, en porcentajes, la proporción de clientes que pagan a 30 días o al contado. Sitúa el gráfico en la misma hoja Datos externos. El gráfico deberá mostrar los porcentajes y llevará como título Formas de pago.

10

EJERCICIO 18 DE EXCEL

11