Opciones avanzadas

Primero debes completar Tablas dinámicas: ¿Qué son y cómo se crean? antes de ver esta lección
Por favor compra el curso antes de comenzar esta lección.

Opciones avanzadas de tablas dinámicas

Vamos a trabajar ahora con la siguiente tabla que contiene cada una de las ventas de distintas gamas de helados durante la campaña de verano que queremos analizar:

ventas-helados

Haciendo clic en cualquier celda perteneciente a la tabla, haremos clic sobre el botón de “Crear tabla dinámica” y la insertaremos en una nueva hoja de cálculo dentro de nuestro archivo.

crear-tabla-dinamica

En primer lugar, vamos a analizar cuál ha sido nuestro producto estrella, para ello pondremos los tipos de helados en Filas y la cantidad vendida en Valores. Nos quedará una tabla parecida a la de la imagen siguiente:

opciones-tabla-dinamica

Excel ha agrupado todas las ventas cada tipo de helado. A la vista de estos resultados, vemos que la gama de helados mantecados y helados de crema tienen una buena acogida por parte de nuestros clientes. En cambio, los helados de sabores exóticos, de leche desnatada y limón helado no acaban de despegar.

Veamos ahora un análisis por estacionalidad, para ello compararemos las gamas de helados por cantidad de helados vendidos cada uno de los días. Como hay más días que gamas de helados moveremos el campo “Día” al área de Columnas y “Tipo de helados” lo pondremos en Filas.

campos-tabla-dinamica
Asignaremos los campos a las áreas de la tabla dinámica.

Quizá las ventas diarias como las hemos reflejado en la imagen anterior no nos aporten de una manera clara lo que ha sucedido. Estaría mucho mejor que las ventas las pudiésemos agrupar entre ellas, por ejemplo, por semanas o quincenas… esto es muy sencillo usando las funciones de agrupar:

Seleccionaremos cualquier celda de la columna de los días y con el botón derecho del ratón desplegaremos el menú contextual con las funciones que nos recomienda Excel, seleccionando la opción de “Agrupar”. O de forma alternativa nos dirigiremos a la pestaña “Analizar” y desplegaremos el botón de “Agrupar”.

agrupar-valores
Agruparemos los días por quincenas.

Automáticamente, Excel habrá detectado que se trata de una variable de tiempo. Con lo cual ya nos presenta el tramo de días y nos ofrece la opción de agrupación por días, meses, trimestres, etc. Nosotros vamos a seleccionar la agrupación por días y grupos de 15 días.

tabla-dinamica
Los valores se encuentran recogidos por quincenas.

Ahora las ventas de cada producto están agrupadas por quincenas. La tabla dinámica que nos devuelve Excel está muy bien, pero vemos que ahora hay más columnas (9 tipos de helado) que filas (6 quincenas). Con lo cual, preferiríamos poner los tipos de helados en filas y las quincenas en columnas, para ello es tan fácil como mover los campos entre filas y columnas y viceversa:

columnas-agrupadas
Pasamos los días a Columnas

Hemos arrastrado el campo “Día” que se encontraba en el área de filas al área de columnas. Por su parte, el campo “Tipo de helado” que encontraba en el área de columnas lo hemos desplazado al área de filas. Con lo cual el resultado es el siguiente:

campo-tabla-agrupada
Los días están agrupados por quincenas, lo cual nos facilita la lectura de la tabla si colocamos dicho campo en columnas.

Antes de presentar nuestra tabla es posible que queramos modificar el nombre de las etiquetas, así pues, podemos cambiar “Suma de Cantidad Vendida” por “Unidades vendidas” así como también cambiar “Etiquetas de fila” y “Etiquetas de columna” por otra denominación que defina mejor el tipo de valores representados, como “Tipo de helado” y “Quincena” respectivamente.

Para hacerlo, lo más sencillo es escribir directamente en dichas celdas, aunque también podemos entrar en la “configuración de campo de valor” (haciendo clic derecho sobre la celda y seleccionando la opción) introducir un nombre personalizado:

nombre-campo-dinamico
Cambiamos el nombre de la etiqueta «Suma de cantidad vendida» por «Unidades vendidas»

En cualquier caso, el resultado será el mismo. También podemos dar formato a nuestra tabla dinámica para que nos quede más vistosa. Por ejemplo, podemos orientar los rótulos de las quincenas en vertical, cambiar el formato de celdas de las ventas para que use el separador de miles, cambiar los colores, etc.

tabla-dinamica
Presentamos un estilo de tabla dinámica prediseñado. En esta lección vimos como cambiar el estilo de la tabla dinámica con los distintos estilos con que venía Excel y cómo crear otros nuevos.

Podemos apreciar que las ventas se disparan durante la segunda quincena de julio y primera de agosto, coincidiendo con la época de más calor. Así también podríamos analizar la causa que explica que la buena aceptación que tuvieron los granizados al principio de la temporada no se tradujera en un aumento en la misma medida que en los demás productos en las quincenas de julio y agosto (¿cambio de precios o de calidad?, ¿nueva tendencia en los gustos de los consumidores?, ¿perdida de algún cliente al que se le servía este producto?.

De igual modo convendría explicar a qué se debe el arranque tan flojo que tuvieron los helados de sabores exóticos y su repunte a final de temporada (¿una publicidad poco efectiva?, ¿era un producto nuevo y se fue paulatinamente expandiendo en el mercado?).

Imaginemos ahora que en vez de plasmar las unidades vendidas en cada semana queremos plasmar el número de operaciones de ventas que se han realizado en cada una de las semanas. Para ello vamos a mantener la tabla dinámica que acabamos de hacer y realizar una copia de ésta unas filas más abajo (seleccionamos todo el rango de la tabla y utilizamos el comando copiar y pegar).

Para que en la segunda tabla se presenten las veces que se ha realizado una venta, debemos seleccionar una celda con datos de la tabla y seleccionar la opción de “Configuración de campo” que se encuentra dentro de la pestaña “Analizar”. Como las opciones más habituales, también podremos encontrar la opción de “Configuración de campo” en el menú desplegable si hacemos clic con el botón derecho del ratón.

Vamos a cambiar el modo en que Excel resume y muestra los datos.

En la nueva ventana, nos dirigiremos a la primera pestaña, “Resumir valores por” y cambiaremos la opción de “suma” (que como su nombre indica, suma los valores para los mismos tipos de helado y campaña) a la opción “contar” que se encargará de decirnos para cada tipo de helado y semana cuantas veces aparecen registros en la tabla, independientemente del valor que tome cada uno de los registros (es decir, reflejaremos el número de ventas independientemente de las unidades vendidas en cada venta).

contar-tabla-dinamica
Antes nos sumaba todos los valores, ahora cuenta la cantidad de celdas en que aparece cada registro.

Ahora tenemos dos tablas, en una nos muestra las unidades vendidas totales para cada tipo de producto y quincena y otra nos muestra el número de ventas por producto y quincena. Ahora  bien, ¿podríamos crear una tabla que unificase las dos anteriores? Esto resulta tan sencillo como añadir otra vez el campo de “Cantidad vendida” al área de valores dentro de los campos de tabla dinámica como se muestra en la imagen:

suma-cuenta
Muestra las cantidades vendidas en unidades físicas y el número de operaciones de venta.

El campo de “Cantidad vendida” aparece dos veces en el área “Valores” pero cada una de ellas tiene un formato distinto: Mientras que el primero de ellos resume los datos sumando los valores, el segundo cuenta los registros. Por lo que para cada quincena aparece cantidad vendida y seguidamente el número de operaciones de venta que se producen durante la misma.

suma-cuenta

 

Etiquetas de lecciones: Excel, tabla dinámica
Volver a: Excel para Contables y Controllers > Tablas dinámicas