Tablas Dinámicas Excel nos ofrece recursos para analizar los datos cuando estos alcanzan un gran tamaño, las tablas dinámicas no permiten disfrutar de la flexibilidad de las tablas y la facilidad para introducir los cambios necesarios según las necesidades del usuario.

Disponemos de la siguiente tabla de un comerciante que dispone de distintos puntos de venta en diferentes localidades del norte de España.

La tabla contiene datos sobre las características de los productos vendidos, fechas de las ventas, etc.

Debido a las limitaciones que presenta analizar y filtrar estos datos como una tabla, configuramos la misma como tabla dinámica, para ello en la ficha Insertar de la aplicación Excel seleccionamos la opción Tabla Dinámica

Al hacer clic sobre la opción Tablas Dinámicas se abre la siguiente ventana:

Tienda Vendedor/a Puesto Dia Mes Dia Semana Mes Producto Fabricacion Talla Tipo Color Unidades Precio UNITARIOImporte VentaComisiones SalarioSantander Carmen Encargado/a 1 Lunes Abril Botines de InviernoNacional 36 Señora Blanco 1 65 65 5,525 950Santander Pablo Empleado/a 1 Lunes Abril Castellanos Nacional 37 Señora Azul 1 55 55 4,675 880Santander Luis Empleado/a 1 Lunes Abril Mocasines Nacional 38 Señora Negro 1 35 35 2,975 880Santander Luis Empleado/a 1 Lunes Abril Manoletinas Mexicano 39 Señora Marron 1 40 40 3,4 880Santander Carmen Encargado/a 1 Lunes Abril Pisa mierdas Asiatico 39 Caballero Blanco 1 30 30 2,55 880Santander Pablo Empleado/a 1 Lunes Abril Sport urbanoAsiatico 40 Señora Azul 1 55 55 4,675 880Santander Luis Empleado/a 1 Lunes Abril Botas Marruecos 40 Caballero Negro 2 80 160 13,6 880Santander Carmen Encargado/a 1 Lunes Abril outlet Asiatico 41 Caballero Marron 1 20 20 1,7 880Santander Luis Empleado/a 1 Lunes Abril Botines de InviernoNacional 42 Caballero Blanco 1 65 65 5,525 880Santander Carmen Encargado/a 1 Lunes Abril Castellanos Nacional 36 Señora Azul 2 55 110 9,35 880Santander Pablo Empleado/a 2 Martes Abril Mocasines Nacional 37 Señora Negro 1 35 35 2,975 880Santander Luis Empleado/a 2 Martes Abril Manoletinas Mexicano 38 Señora Marron 1 40 40 3,4 880Santander Pablo Empleado/a 2 Martes Abril Pisa mierdas Asiatico 39 Señora Blanco 1 30 30 2,55 880Santander Luis Empleado/a 2 Martes Abril Sport urbanoAsiatico 39 Caballero Azul 1 55 55 4,675 880Santander Carmen Encargado/a 2 Martes Abril Botines de InviernoNacional 40 Señora Negro 1 65 65 5,525 880Santander Pablo Empleado/a 2 Martes Abril Castellanos Nacional 40 Caballero Marron 1 55 55 4,675 880Santander Luis Empleado/a 2 Martes Abril Mocasines Nacional 41 Caballero Blanco 2 35 70 5,95 880Santander Luis Empleado/a 2 Martes Abril Manoletinas Mexicano 42 Caballero Azul 1 40 40 3,4 880

Es importante que antes de hacer clic sobre la opción tablas dinámicas,

tuviéramos seleccionado el rango, o simplemente que el foco se encontrara en una celda del rango o tabla origen de los datos.

Por otra parte, al sistema le diremos si la tabla dinámica la inserta en una nueva hoja de cálculo o bien

en una ya existente. Normalmente la primera vez le podemos dejar al sistema que sea quien nos lo inserte en una nueva hoja.

Como resultado en la nueva hoja se mostrará algo como lo siguiente:

En la parte izquierda de la hoja en la sección de la hoja de trabajo el espacio que se muestra es donde figurara el informe a partir de los datos de los campos que

nosotros seleccionemos. A la derecha aparece la

sección donde se encuentra la lista de los campos. (Cada columna de nuestra tabla o rango de celdas, se corresponde con un campo).

Para comenzar a dar sentido a nuestra tabla dinámica, deberemos seleccionar un campo y ubicarlo en una de las cuatro ventanas en que se divide la parte inferior de la sección donde se encuentra la lista de campos.

Podemos comenzar a experimentar con la ventana de filas, columnas y valores. Seleccionaré el campo tiendas y lo ubicaré en la ventana de “Filas”, mientras que reservaré para columnas el campo mes; y en valores agregaré el campo importe venta automáticamente a la izquierda se muestra el resultado:

A continuación personalizare el texto de la tabla, para ello, selecciono la celda que muestra el texto “Suma de Importe de Venta”, y en la barra de fórmula de Excel escribo el texto que deseo que en su lugar muestre la tabla: “Ventas por”

A continuación rectifico el texto de Etiquetas de columna, por Mes

Seguidamente, procederemos a formatear el texto con formato de millares y dos decimales, selecciono primero las celdas que contienen la parte numérica:

Suma de Importe Venta Etiquetas de columnaTienda Abril Mayo Junio endasBilbao 15915 25645 26480 68040Logroño 16265 25710 26815 68790Palencia 15990 25690 26680 68360Pamplona 16025 25675 26535 68235Santander 15825 25000 26010 66835Torrelavega 15240 24885 25930 66055Valladolid 15890 25655 26650 68195Vitoria 15905 25525 26655 68085endas 127055 203785 211755 542595

Ventas por MesTienda Abril Mayo Junio endasBilbao 15915 25645 26480 68040Logroño 16265 25710 26815 68790Palencia 15990 25690 26680 68360Pamplona 16025 25675 26535 68235Santander 15825 25000 26010 66835Torrelavega 15240 24885 25930 66055Valladolid 15890 25655 26650 68195Vitoria 15905 25525 26655 68085endas 127055 203785 211755 542595

Pulsamos sobre la cuña del capo situado en la ventana de valores de la lista de campos

Se nos muestra la siguiente ventana:

de la que pulsamos sobre el botón “Formato de número” y nos muestra la siguiente

ventana de Formato

en la que seleccionamos la categoría “Número” y pulsamos sobre el cuadro “millares”. Pulsamos sucesivamente aceptar.

Como resultado la tabla ahora se nos mostrará de la siguiente manera:

El sistema nos ha incorporado automáticamente los totales de columnas y filas;

si deseamos desactivarlos, solo tenemos que seleccionar en la pestaña “Diseño” de la Ficha Herramientas de Tabla Dinámica, la opción Tablas Generales,

y en el desplegable seleccionar “Desactivado para filas y columnas”.

Ventas por MesTienda Abril Mayo Junio VentasBilbao 15.915,00 25.645,00 26.480,00 68.040,00Logroño 16.265,00 25.710,00 26.815,00 68.790,00Palencia 15.990,00 25.690,00 26.680,00 68.360,00Pamplona 16.025,00 25.675,00 26.535,00 68.235,00Santander 15.825,00 25.000,00 26.010,00 66.835,00Torrelavega 15.240,00 24.885,00 25.930,00 66.055,00Valladolid 15.890,00 25.655,00 26.650,00 68.195,00Vitoria 15.905,00 25.525,00 26.655,00 68.085,00Ventas 127.055,00 203.785,00 211.755,00 542.595,00

Segundo Ejercicio

Este ejercicio consistirá en obtener un informe de las ventas de los vendedores de cada tienda por unidades e importe global:

Volvemos nuevamente a la hoja que contiene los todos los datos que deseamos analizar, igual que hicimos para el caso anterior vamos a la ficha Insertar y seleccionamos la opción Tabla Dinámica.

Si te fijas en la anterior imagen no muestra el rango de celdas que contienen los datos con los que vamos a trabajar, y ahora he seleccionado el botón para la Hoja de cálculo siguiente, donde he seleccionado la Hoja de cálculo TD! y la celda A$17 donde a partir de la cual deseo que me inserte la tabla dinámica.

Ventas por MesTienda Abril Mayo Junio VentasBilbao 15.915,00 25.645,00 26.480,00 68.040,00Logroño 16.265,00 25.710,00 26.815,00 68.790,00Palencia 15.990,00 25.690,00 26.680,00 68.360,00Pamplona 16.025,00 25.675,00 26.535,00 68.235,00Santander 15.825,00 25.000,00 26.010,00 66.835,00Torrelavega 15.240,00 24.885,00 25.930,00 66.055,00Valladolid 15.890,00 25.655,00 26.650,00 68.195,00Vitoria 15.905,00 25.525,00 26.655,00 68.085,00Ventas 127.055,00 203.785,00 211.755,00 542.595,00

En este caso como ante dije deseo que me devuelva lasa ventas por unidades e importe de los empleados de cada tienda.

En la sección de lista de clientes seleccionaré estos campos y los ubicaré conforme a lo siguiente:

En la sección de las filas he ubicado en primer lugar la tienda y después el campo vendedores. En la sección Columnas he colocado el campo mes y posteriormente en la sección valores he insertado los campos Importe Ventas y Unidades. Al hacer esta última selección en la columnas se reflejará un sumatorio que he arrastrado hacia la parte superior del campo mes para reordenar los datos en la tabla dinámica.

A continuación he modificado el texto de los campos Ventas y unidades Vendidas, y Vendedores por tiendas, de la misma manera que hicimos en el ejercicio anterior, selecciono la celda y en la barra de direcciones de la hoja de cálculo escribo directamente; esto mIsmo se puede realizar desde el botón izquierdo del ratón seleccionando la opción: Configuración de campo de valor.

Tercer Ejercicio

Para este ejercicio me planteo analizar las ventas unitarias de cada uno de los productos por cada día de la semana

Ventas Unidades vendidas

Vendedores por tienda Abril Mayo Junio Abril Mayo JunioBilbao 15915 25645 26480 325 521 539

Abel 5505 8405 8715 113 174 176Antonio 5305 8640 9350 107 172 188Carlos 5105 8600 8415 105 175 175

Logroño 16265 25710 26815 330 522 541Palencia 15990 25690 26680 329 520 541Pamplona 16025 25675 26535 328 521 541Santander 15825 25000 26010 325 517 536Torrelavega 15240 24885 25930 317 511 529Valladolid 15890 25655 26650 328 521 541

Candela 4940 8340 8680 101 165 174Luisa 6000 9200 9475 125 190 194Mati 4950 8115 8495 102 166 173

Vitoria 15905 25525 26655 327 520 541Total general 127055 203785 211755 2609 4153 4309

El resultado es lo que vemos en la tabla anterior, para lo cual repetimos los pasos anteriores, seleccionamos una celda de la tabla de la hoja de cálculo TD, que es donde se encuentran los datos, seleccionamos la ficha Insertar, y la opción Tabla de Datos, en la ventana que se muestra por defecto se muestra todo el rango y en la parten inferior le indicamos la celda de la hoja donde queremos que se inserte nuestra nueva tabla de datos:

Seleccionamos los campos dia semana, que ubicamos en la sección filas; producto en la sección Columnas y Unidades e la sección Valores

A continuación procedemos a ajustar los texto de la tabla: Dia Semana y Modelo, conforme aparece en la tabla del ejercicio.

A continuación deseo que los valores se muestren en términos porcentuales, para lo cual sitúo el puntero sobre los números de la tabla y pulso botón

derecho, para que me muestre la ventana de “Configuración de campo valor, en esta ventana selecciono la pestaña: Mostrar valores como”, y en el desplegable selecciono % total de filas, en la parte inferior aparecerá el campo Día Semana, y sin más pulso en aceptar.

Modelos

Dia semana BotasBotines Invierno Castellanos Manoletinas Mocasines outlet Pisa mierdas Sport urbano Total general

Lunes 5,50% 22,01% 19,28% 15,92% 16,93% 4,86% 7,05% 8,44% 100,00%Martes 4,97% 20,09% 21,04% 13,67% 17,19% 5,52% 8,04% 9,49% 100,00%Miércoles 6,11% 20,24% 19,31% 14,58% 15,95% 6,66% 7,76% 9,41% 100,00%Jueves 5,43% 20,14% 20,63% 15,74% 17,07% 5,61% 6,15% 9,23% 100,00%Viernes 5,26% 20,03% 21,00% 14,88% 19,30% 4,53% 6,79% 8,21% 100,00%Sábado 6,86% 20,04% 19,02% 14,47% 15,35% 7,61% 7,47% 9,18% 100,00%Domingo 0,00% 0,00% 0,00% 0,00% 12,50% 0,00% 0,00% 87,50% 100,00%Total general 5,73% 20,41% 19,98% 14,85% 16,90% 5,85% 7,23% 9,05% 100,00%

Cuarto Ejercicio

Creamos una nueva tabla dinámica a partir de los campos Fabricación y Ventas; el campo Ventas lo configuramos para que su valor nos lo muestre como un % del total de columnas.

A continuación, vamos a trabajar con los segmentos de datos que nos permitirán filtrar de una forma fácil y ágil nuestra tabla.

Al pulsar sobre el botón de la opción “Insertar Segmentación de datos” se muestra el panel para seleccionar los campos

En este caso se han seleccionado los campos Día Semana, Mes y Producto, se muestran unos paneles que contienen cada uno de los elementos que al pulsar sobre ellos nos permitirán filtrar la tabla de forma sencilla y fácil.

Si pulsamos sobre los meses Abril y Mayo vemos cual es el resultado

Origen Productos % ventasAsiatico 16,35%Marruecos 9,52%Mexicano 12,37%Nacional 61,75%Total general 100,00%

Para eliminar el filtro pulsaremos sobre la imagen de la parte

superior derecha del panel

Quinto Ejercicio

En este ejercicio vamos a crear una tabla dinámica que nos permita analizar las unidades de producto vendidas por tienda y sus empleados durante cada uno de los días de la semana, filtrándolos por meses; posteriormente configuraremos los datos para que nos permita compararlos con los de la tienda de Bilbao.

Repetimos las acciones ya realizadas en ejercicios anteriores, seleccionamos la hoja donde se encuentran todos los datos, ponemos el foco en una de las celdas que forman parte del rango o tabla; seleccionamos la ficha insertar y hacemos clic sobre el botón Tabla Dinámica.

En la ventana asistente comprobamos como nos muestra el rango de las celdas que contienen los datos, y contraemos la ventana del asistente para seleccionar la celda de la hoja donde deseamos añadir la nueva tabla dinámica.

En el panel de la lista de campos ponemos el mes en la seccion de filtros, el dia de la semana en la de columnas, las tiendas y los vendedores en la sección de filas y las unidades en el sumatorio de valores. Como resultado nos mostrará la tabla la siguiente información:

En la tabla fíjate que aparecen unos botones a la izquierda de cada una de las tiendas, se expanden o comprimen pulsando sobre los mismos, mostrando u ocultando los elementos de los vendedores de cada tienda en cuestión.

A continuación vamos a comparar la diferencia de unidades vendidas por cada tienda respecto de la de Bilbao que se toma como base:

Mes Abril

Suma de Unidades Dia semanaEtiquetas de fila Lunes Martes Miércoles Jueves Viernes Sábado

Bilbao 61 59 47 57 51 50Logroño 63 62 46 56 51 52Palencia

Adela 23 19 15 20 17 20Aitor 23 16 13 18 16 12Amaya 19 22 18 21 18 19

Total Palencia 65 57 46 59 51 51

Pamplona 63 59 46 59 50 51Santander 63 56 47 60 50 49Torrelavega

Iziar 21 22 13 18 20 14Tito 24 20 19 22 19 20Victor 16 15 13 16 10 15

Total Torrelavega 61 57 45 56 49 49

Valladolid 63 59 46 59 50 51Vitoria 63 59 46 58 52 49

En la sección de sumatorio de valores clica sobre el botón desplegable del campo de las unidades

Selecciona configuración de campo de valor y a continuación en la ventana que se muestra selecciona la pestaña Mostrar valores como. Veras que el nombre personalizado es el del campo valor (puedes modificarlo) y en la caja de texto Mostrar valores como pulsa sobre el botón desplegable para realizar

la selección de Diferencia de… En las dos ventanas inferiores se muestran el Campo Base, en nuestro caso es la Tienda y en la de la derecha seleccionamos el elemento que deseamos figure como el de referencia para establecer las diferencias.

Como resultado obtienes que en la tabla dinámica se muestra así:

Observa que en la fila de la tienda de Bilbao se han ocultado los datos, y respecto a los datos de las otras tiendas se han establecido las unidades de diferencia (positiva o negativa) respecto de las de la tienda de Bilbao.

Sexto Ejercicio

En el siguiente ejercicio pondremos dos filtros (tienda y mes) a la tabla dinámica, agregamos en la sección de las filas el campo Día de la semana, y en el de valores el campo de las unidades y el de importe de venta; configuramos el valor de este campo como se ve en la imagen y seleccionamos para que el valor nos lo muestre en promedio

Mes (Todas)

Unidades Dia semanaTienda Lunes Martes Miércoles Jueves Viernes Sábado Domingo

BilbaoLogroño 3 3 0 2 -2 2 0Palencia 4 2 -1 3 -6 3 0Pamplona 2 2 0 1 -2 2 0Santander -3 2 -3 3 -4 -2 0Torrelavega -3 -3 -8 -3 -8 -3 0Valladolid 2 2 0 1 -2 2 0Vitoria 3 0 -2 3 -1 0 0

Tienda (Todas)Mes (Todas)

Etiquetas de fila Unidades vendidas Importe MedioLunes 1872 56,32Martes 1792 54,95Miércoles 1818 55,85Jueves 1658 55,30Viernes 1767 56,60Sábado 2156 55,20Domingo 8 52,50Total general 11071 55,69