E16_UD15

Vista previa del material en texto

UNIDAD - TABLAS DINÁMICAS 
 
Tablas dinámicas 
 
Las tablas dinámicas son una de las herramientas más potentes para análisis de datos de Excel. 
Una tabla dinámica nos va a permitir analizar grandes volúmenes de datos, de una forma muy 
rápida. Nos permite resumirlos, analizarlos, explorarlos y presentarlos de una forma rápida y 
fácil con muy poquitos clics. Vemos aquí el formato que tiene una tabla dinámica. 
 
Nos va a permitir hacer una serie de tareas, que nos va a dar mucha facilitad para presentar 
nuestros datos de forma que sean fáciles de analizar. 
 
Elementos 
 
¿Qué elementos componen una tabla dinámica? 
En una tabla dinámica podemos ver que tenemos distintas zonas, tenemos una serie de 
etiquetas de fila, que me van a conformar la etiqueta de cada una de las filas. 
 
Tenemos también etiquetas de columna, como en cualquier otra tabla. 
 
Tenemos en la parte inferior, los totales, los campos de totales o las filas de totales. 
 
La zona central, es lo que le llamamos “la zona de valores” 
 
Por último, tenemos una zona superior que es la zona de filtro, que me permite filtrar mis datos. 
 
Esas mismas zonas las vemos reflejadas en esta parte de la derecha de la pantalla. 
 
En la tabla tenemos cuatro áreas, el área de filtros, situada arriba, la de las columnas, que es 
esta de aquí, la de las filas que esta de aquí y la de los valores, que es el área central. 
La zona del centro se suele rellenar con valores numéricos, a no ser que lo que necesitemos 
contar cosas. Pero en general suelen ser valores numéricos. 
 
Crear una tabla dinámica 
 
Veamos ahora cómo crear una tabla dinámica. 
 
Para crear una tabla dinámica tenemos que partir de unos datos que estén estructurados con 
forma de tabla. Situamos el cursor dentro de los datos de la tabla, nos vamos a la pestaña 
insertar y vamos al grupo de tablas. 
 
Tenemos tablas dinámicas, lo pulsamos y lo primero que observamos es que ya nos ha 
reconocido el rango total de los datos. 
 
Sí bajamos, vemos que efectivamente me está reconociendo todo el rango. Me dejaría el ítem 
de seleccionar una tabla o rango, me dejaría también utilizar una fuente de datos externa, por 
ejemplo, una base de datos de Access, un archivo XML. 
Lo siguiente que nos pregunta es “Si deseamos colocar esta tabla dinámica en una nueva hoja 
de cálculo”, que es la opción que viene marcada por defecto o en una hoja de cálculo existente 
y nos permitiría ubicarla donde nosotros necesitáramos. Le damos a aceptar y nos lleva a una 
nueva hoja de cálculo donde ya me aparecen en la parte de la derecha los campos que tiene mi 
tabla. 
 
 
Y en la parte izquierda, es la parte donde se van a visualizar los datos, tenemos aquí también 
aquí los cuatro campos posibles. 
Por ejemplo, imaginemos que queremos ver como filas los nombres de los clientes, pues en filas 
marcamos cliente y ya aparece insertado el campo y en esta parte de la izquierda todos los 
clientes. 
 
Observamos, que las herramientas de tabla dinámica, tanto los campos de tabla como las 
pestañas de analizar y diseño, sólo salen cuando estamos dentro del rango que ocupa la tabla 
dinámica. Ya tenemos aquí el cliente, vamos a imaginar que en las columnas queremos los 
meses, puedo o bien marcar como antes o bien seleccionar el campo y arrastrarlo y vemos que 
ya lo ha colocado aquí, enero, febrero…tenemos aquí todos los meses. 
 
Vamos a configurar ahora la zona central que serían los valores. ¿Qué voy a poner aquí? 
Lógicamente, los totales para ver cuánto se ha vendido cada mes a cada cliente. Cogemos el 
campo total y lo colocaría aquí. 
 
Vemos que tenemos, ahora mismo, todas nuestras ventas por clientes y por meses, por ejemplo, 
si quiero saber a este cliente cuanto le he vendido cada mes pues aquí tengo todas las ventas, 
en total 17.019€ le hemos vendido a ese cliente. 
 
También tendríamos abajo el total de ventas, total de ventas por meses y total de ventas 
general. 
¿Cómo hago para sacar otra vez los campos? 
Antes vamos a ver las pestañas de analizar y de diseño. 
 
Si voy a la pestaña analizar, en el botón acciones, vemos que nos va a dejar hacer una serie de 
operaciones con nuestra tabla. 
Antes vamos a mostrar nuevamente los campos, para ello en el botón mostrar, en la pestaña 
analizar, pulsamos “lista de campos”. 
 
Volvemos a tener aquí todos nuestros campos. 
 
Volvemos al botón acciones y vemos que nos va a permitir borrar todo. 
 
Esto no borra la tabla completa, borra los datos de la tabla. 
Seleccionar, por ejemplo, toda la tabla dinámica, esto me permitiría seleccionarla entera, 
también podríamos seleccionarla como hacemos habitualmente, marcando todo el rango. 
 
Más acciones que nos permitiría “mover la tabla dinámica” y nos diría nueva hoja de cálculo, 
hoja de cálculo existente, me permitiría moverla a cualquier zona. 
 
Crear una tabla dinámica recomendada 
 
Vamos a ver ahora otra forma de genera una tabla dinámica mediante las tablas dinámicas 
recomendadas. 
Sí me situó dentro de mi rango de datos, en la pestaña insertar, además de tabla dinámica vemos 
que existe también la posibilidad de tablas dinámicas recomendadas. 
 
Lo pulsamos, vemos que me reconoce todo el rango de mis datos y a partir de ahí, lo que va a 
hacer va a ser recomendarme una serie de tablas, que considera que puedan ser las más 
interesantes, por ejemplo, promedio total por mes, cuenta de nombre de productos, para ver 
número de productos, suma de unidades, suma de número de pedidos, esto no tiene ningún 
sentido para nosotros, aquí si suma total por mes, suma total por cliente, suma de precios 
unitarios, en fin, está dando distintas combinaciones. 
 
Vamos a coger, por ejemplo, suma total por mes, vemos que aquí la vista previa, de lo que va a 
generar y pulsamos en aceptar. 
 
Me abre una hoja de cálculo nueva, con la tabla que yo le he dicho que me construya, puedo 
ahora seguir trabajando con ella, por ejemplo, sí quisiera que además aquí apareciera los 
clientes, sencillamente vendría, metería aquí el campo de cliente y ya tendría los totales de 
ventas por mes y por cliente. 
 
También podría traerme aquí el nombre del producto y lo marco, vemos que ya me lo ha añadido 
aquí, y vemos que ya tenemos aquí el nombre del producto. 
 
Ahora, en la tabla, tenemos el mes, el cliente y el nombre del producto, sí ahora cojo y quito, 
por ejemplo, el cliente, quedaría por meses y por productos. 
 
Es decir, podemos o bien construir nosotros directamente la tabla o bien podríamos también ir 
a una de las recomendadas y luego modificarla hasta adoptarla a lo que nosotros necesitamos. 
Tenemos esas dos formas de poder generar nuestras tablas dinámicas. 
 
Campo de valores 
 
Veamos ahora cómo monta Excel el campo de valores. 
Vemos que nos aparece como SUMA DEL TOTAL, es decir, está sumando los valores para llegar 
a estos resultados. 
 
Puede ser que a mí no me interese que sea una suma, sino que, por ejemplo, sea el “promedio” 
o sea una cuenta, es decir que sea cualquier otra operación. 
Para hacer esto, vamos a las pestañas de Herramientas de Tablas dinámica, Analizar y en el grupo 
de campo activo vemos ya que me está reconociendo que es suma de total, podríamos pinchar 
en el botón configuración de campo. 
 
También podemos hacerlo a través de la zona campos de tabla, en la flechita negra que hay 
junto donde pone “suma total” desplegamos, y vemos que podemos darle a “configuración de 
campo de valor”. 
 
 
 
 
 
 
 
Abre el cuadro de Configuración de campo de valor 
 
Nombre personalizado, aquí en lugar de poner suma del total podríamos poner “total de 
ventas”, el nombre que quisiéramos y en “resumir campo de valor por”, suma, recuento, 
promedio, máximo, mínimo, producto, en fin, vemos que tenemos un montón de funciones. 
Podríamos en lugar de suma, por ejemplo, coger cual ha sido la media de los pedidosdurante el 
mes de marzo de Antonio Moreno Taquería, por ejemplo, y luego buscar el total del promedio 
de sus pedidos. Vamos a dejarlo, por ejemplo, en el promedio. 
 
Aceptar y vemos que ahora me cambia tanto aquí el encabezado, promedio de total me han 
cambiado también los valores y en el campo valores también me aparece promedio del total. 
 
¿Qué más podemos hacer con esta configuración del campo de valores? 
Vemos que tenemos otra pestaña en la ventana de Configuración de campo de valor, pero antes 
vamos a ponerlo, en suma 
 
Ahora le voy a marcar mostrar valores. Me aparece sin cálculo, pero podría querer saber del 
total de pedidos que ha hecho este cliente, qué porcentaje representaría cada uno de ellos y 
con respecto al total de pedidos. 
 
Podría decirle mostrar valores como y decirle porcentaje del total general. 
 
En este caso me va a calcular el total general de ventas, aceptamos y vemos que el pedido me 
ha hecho este cliente en el mes de marzo, representa el 0,65% de mi total de ventas a lo largo 
de todo el año. El total de este cliente sería 3,21% del total de ventas. 
Ya podemos ver que no solamente podemos variar la operación que realiza con los valores sino 
también la manera de presentarlos. 
 
 
Modificar elementos de una tabla dinámica 
 
Cuando trabajamos con tablas como ya hemos visto, al crearlas no necesariamente tenemos 
que adaptarnos a la estructura que nos da. 
 
Nosotros podemos jugar con los datos de forma que nos los llevemos de un sitio a otro y 
modificamos así la estructura de nuestra tabla, sin ningún tipo de problemas. 
 
No tenemos por qué rellenar todos los campos de una tabla, todas las zonas de la tabla de hecho 
hasta ahora hemos estado trabajando sin la opción de filtros, por ejemplo, si me cojo la columna 
el campo mes y lo paso al campo filtro, vemos que en la parte superior me ha generado un 
campo de filtro, ahora podríamos filtrar estos datos por meses. 
En este campo, que lo que me mete es un filtro, por ejemplo, ahora podría traerme esto aquí, 
tener esto y filtrarlo desde el campo de aquí arriba, simplemente, con ese campo. 
 
Cuando trabajo con una tabla dinámica, todos aquellos campos, por ejemplo, vamos a arrastrar 
nuevamente los meses ahí. 
Todos aquellos campos que presenten esta flecha, quieren decir que me van a ofrecer 
herramientas para filtrar y para ordenar los datos. 
 
Sí lo abro vemos que tenemos las opciones que ya conocemos de filtros, de orden. Me permitiría 
filtrar por etiquetas, filtros de valor, ordenar de la A la Z, de la Z a la A, y más opciones de 
ordenación que me llevaría al cuadro que ya conocemos también. 
Es decir, cuando trabajo con una tabla dinámica, como podemos ver nos está añadiendo una 
serie de funcionalidades de forma predefinida. 
Otra de las cosas, que podemos hacer, por ejemplo, voy a quitar el nombre de producto, me voy 
a traer aquí el mes. 
 
Por ejemplo, vemos que me está, no sabemos si nos está agrupando pedidos del mismo cliente, 
en el mismo mes. 
Vamos a imaginarnos en este pedido del mes de mayo, no sé si estos 1523,20€ proceden de un 
único pedido o igual proceden de dos pedidos. 
 
Nos ofrece una forma de verlo, de una manera rápida y que además nos genera lo que se llama 
“SUBTABLAS”. 
Si quiero ver el detalle de algunos de estos valores que tengo en la suma de totales simplemente, 
con darle doble clic al valor, vemos que me ha generado una nueva hoja y me ha hecho un 
desglose con los registros que comprendiese valor. 
 
En este caso equivalía a un mismo pedido, pero con dos artículos distintos, uno de 700€ y el otro 
823,2€. 
Volvemos a nuestra hoja, por ejemplo, vamos a mirar este otro pedido. Este proviene de un 
único artículo. 
 
Con esto yo puedo generar cuantas subtablas necesite. 
 
Modificar elementos de una tabla dinámica, datos de origen 
 
Cuando trabajamos con tablas dinámicas, a veces hacemos cambios en las hojas originales o 
hacemos ampliaciones en el rango de datos. Puedo de repente, aquí añadir nuevos pedidos, en 
fin, podemos sufrir variaciones en los datos originales de nuestra tabla. 
 
Vamos a ver qué herramientas nos proporciona Excel para esto. 
Dentro de la pestaña analizar, tenemos un grupo que es el GRUPO DE DATOS, me va a permitir 
por un lado actualizar y por el otro lado cambiar origen de datos. 
 
En actualizar tenemos dos opciones 
 
“actualizar” que me actualizaría los datos más recientes del origen, pero sólo los que están 
vinculados a la celda que está activa en ese momento y “actualizar todo” que va a actualizar 
todas las fuentes del libro y a partir de ahí me obtiene los últimos datos que haya en la fuente 
original. 
Tenemos luego la opción de cambiar datos, cambiar origen de datos. Esto me valdría o bien si 
es que yo quiero sacar ahora los datos de otro sitio o bien cuando ampliamos el rango que había 
en la tabla original, donde sacamos los datos, tendríamos que venirnos a cambiar origen de 
datos. 
 
Aparece una ventana que me lleva a la hoja del origen de los datos, para que seleccionemos una 
tabla o rango o bien utilicemos una fuente de datos externa. 
Nosotros, en este caso, si tuviéramos filas añadidas marcaríamos el nuevo rango y le diríamos 
aceptar y a partir de ahí la tabla se actualizaría con los nuevos valores del nuevo origen de datos. 
 
Tablas dinámicas y formato 
 
Veamos en esta ocasión opciones que nos permiten respecto al diseño, al formato de las tablas 
dinámicas. 
 
Sí nos venimos a las Herramientas de Tabla Dinámica, pestaña diseño, vemos que tenemos unos 
estilos ya prediseñados, si me situó encima de cualquiera de ellos, voy viendo cómo va a quedar 
mi tabla dinámica, es decir, podría desde aquí cogerme alguno de estos estilos predefinidos y 
utilizarlos. 
 
Otra forma, que tengo también de darle formato, un estilo a mi tabla dinámica sería desde la 
pestaña de inicio con las herramientas que ya conocemos, con la herramienta para hacerlo 
manualmente y de forma individual celda a celda, podría venirme aquí, ponerle un color de 
fondo, ponerle un color en el texto. 
 
Podríamos hacerlo como ya sabemos. 
Además, en la pestaña de diseño nos va a permitir en las opciones de estilo marcar, sí queremos 
columnas con bandas, si queremos filas con bandas, por ejemplo, si quito las columnas quedaría 
así. 
 
 
Sí me vengo aquí, quedarían de otro color, me permite activar y desactivar los encabezados de 
fila y de columna, es lo que quitaría son las negritas, sí yo desactivo “encabezado de columna” 
veo que aquí, se me ha quedado sin negritas y si quito los encabezados de fila, igualmente, me 
quita las negritas de aquí, es decir, lo que me quita es el formato del encabezado. 
Me permitiría desde el grupo de diseño “insertar líneas en blanco después de cada elemento” o 
quitarlas en el caso de que las tengamos. 
 
Sí las inserto vemos que donde acaba cliente, me está insertando una línea en blanco, podría 
volver al botón Filas en blanco y quitarlas. 
Estamos viendo ahora nuestra tabla en VISTA COMPACTA. 
Sí nos venimos a Diseño de Informe, le podemos decir que nos lo muestre en formato de 
esquema. 
 
Vemos que nos ha llevado estas filas más para allá y ha dado más espacio aquí. 
 
Podemos decir, que nos lo muestre en formato tabular, está dando aquí estos totales, podemos 
decir, que se repitan todas las etiquetas de elementos y vemos que nos está, efectivamente 
repitiendo las etiquetas. 
 
Ahora le voy a decir que no las repita. 
Es decir, podemos mostrar en tres formatos distintos y repitiendo o no las etiquetas de los 
elementos. 
También permite jugar con los totales generales, desactivados para filas y columnas, activado 
para filas y columnas, sólo para filas, sólo para columnas y con los subtotales, igual no 
mostrarlos, mostrarlos todos en la parte inferior del grupo, o mostrarlos en la parte superior del 
grupo. 
 
Estas seríanlas opciones de formato de nuestra tabla dinámica. 
 
Opciones de tablas dinámicas 
 
Además, de todas las opciones que hemos estado viendo hasta este momento, tenemos una 
serie de opciones, un poco más avanzadas o afinadas que podemos encontrar, dentro de las 
Herramientas de tabla dinámica, en la pestaña analizar, en el botón de tabla dinámica al 
desplegarlo, vemos que nos permitiría pinchar en opciones. 
 
Nos vamos a mostrar el cuadro de diálogo de opciones de tabla dinámica. 
 
Aquí pues, podríamos cambiarle el nombre, por ejemplo, clientes y nos va a dar una serie de 
opciones de diseño y de formato, pues “combinar y centrar celdas” mostrar campos de área de 
filtro, formato para mensajes, para celdas vacías, autoajustar anchos y formatos. 
 
Dentro de totales y filtro va a dar opciones para los totales y opciones para los filtros. También 
para la ordenación. 
 
Dentro de la pestaña mostrar, va a hacer referencia a los botones para expandir, contraer, 
formación contextual, en fin, me va a dar opciones para que muestre o no determinadas cosas 
y cómo quiero mostrarlas. 
 
Tenemos una pestaña que nos permite configurar opciones para la impresión, imprimir botones 
para expandir y demás, repetir etiquetas de fila, en cada página impresa, imprimir títulos. 
 
La pestaña de datos hace referencia a cómo va a trabajar la tabla dinámica con sus datos. 
 
Texto alternativo, sería un texto que podríamos añadir a nuestra tabla dinámica. Una vez que 
termináramos de configurar todas nuestras opciones simplemente, aceptar. 
 
Creación de gráficos dinámicos 
 
Excel nos da la posibilidad no sólo de crear tablas dinámicas sino también de trabajar con 
gráficos dinámicos. Tenemos dos maneras de crear un gráfico dinámico o bien desde la propia 
hoja de datos, que ya teníamos desde la página de origen de los datos o bien desde una tabla 
dinámica ya creada. 
Vamos a crear lo primero desde el origen de los datos. Vamos a imaginar que no tuviéramos 
creada una tabla dinámica, nos venimos a la zona de datos, seleccionamos una de sus celdas, 
pestaña insertar y dentro del grupo de gráficos, tenemos la opción de “gráfico dinámico”, si lo 
desplegamos podríamos seleccionar “gráfico dinámico” o bien “gráfico dinámico y tabla 
dinámica”. 
 
Nosotros nos vamos a quedar con “gráfico dinámico” me aparece una ventana muy similar a la 
que ya vimos en la creación de tablas dinámicas, me dice que seleccione el rango donde están 
los datos que aparece ya seleccionado y sí la quiero en una nueva hoja de cálculo. 
 
Pulsamos aceptar. 
Vemos que la hoja que nos da, esta vez, no me aparece los campos de tablas, sino que me 
aparecen campos de gráficos. Pero la forma de proceder es la misma y aquí me irá construyendo 
un gráfico. 
 
Vengo al campo “mes” y voy a los totales como valores. Está dando un gráfico que me aparecen 
los totales de venta por meses. 
 
Sí además metiera, por ejemplo, el cliente, vemos que me pondría los nombres de los clientes 
en la leyenda y las ventas por meses. 
 
Aparte de la facilidad que nos permite construirlo. ¿Qué más cosas nos proporciona el gráfico 
dinámico? 
Vemos que aquí tenemos las etiquetas típicas de las tablas dinámicas, suma de total cliente y en 
el campo cliente me permitiría filtrarlo y decirle que sólo quiero ver, por ejemplo, los de estos 
dos clientes. 
 
Automáticamente me adapta el gráfico con ese filtro. 
 
Volvemos a seleccionarlo todo, aceptar y me permitiría asimismo filtrarlo por el campo mes, 
febrero y junio. Pues aquí, tenemos las ventas de febrero y junio. 
 
 
 
 
Como vemos los gráficos dinámicos son tremendamente rápidos de trabajar. 
Otra forma de generar un gráfico sería desde una tabla dinámica que ya tuviéramos como, por 
ejemplo, esta que ya teníamos. Estando dentro de la tabla dinámica en la pestaña de analizar 
tenemos dentro de las Herramientas “gráfico dinámico”. 
 
Se nos abre la ventana de insertar gráfico y lo único que tendremos que hacer es elegir el tipo 
de gráfico que queremos, por ejemplo, circular, de barras, de columnas. Añadiríamos el que 
necesitáramos. 
 
Vamos a volver a coger el de columnas agrupadas y aceptar, vemos que me ha generado el 
mismo gráfico, prácticamente del que teníamos, mes, suma del total y cliente. 
 
Este se me va a ir acompasando junto con la tabla que tengo detrás, porque en realidad es un 
gráfico que está colgando de esta tabla. 
Sí yo me llevo esto, me cambia la tabla y me cambia el gráfico. En el caso del otro gráfico que 
habíamos generado, exactamente lo mismo. 
 
Me van a cambiar los datos de la tabla esta que tengo aquí detrás de esta tabla, que ha generado 
con respecto al gráfico. 
Estás serían las dos maneras de trabajar el gráfico, luego tenemos las dos pestañas de gráficos 
que ya vimos en apartados anteriores. 
 
La de formato con las herramientas de dibujo y la de diseño que me permitiría agregar 
elementos, los títulos, las leyendas, el diseño rápido, cambiarle los colores, cambiar diseños, en 
fin, esto ya trabajaría como un gráfico normal. 
 
La pestaña Diseño me permitiría cambiar entre filas y columnas, cambiar el origen de datos y 
cambiar el tipo de gráfico, en este caso moverlo no porque ya lo tenemos en una hoja aparte.