Tutorial tablas dinámicas en Excel

 

Todo sobre Excel tu fuente de conocimiento : 

Tutorial de tablas dinámicas en Excel ¿ Qué son  y para qué sirven? las tablas dinámicas en Excel es una de las características bastante útil, pero tienen un misterio ya que todos sabemos que existen, pero su uso es poco claro confuso     

INFORMACIÓN 

Si nos preguntamos :  ¿ Que son las tablas dinámicas en Excel ?  Son herramientas muy poderosas ya que podemos resumir, extraer y analizar nuestros datos sin necesidad de estar actualizando manualmente.

Ahora imagina que tienes una serie  de datos con información de ventas varias sucursales y te piden  hagas una tabla de resumen , lo que harías es sacar los datos manualmente y sumar el total de ventas por sucursal. y ponerlo en la tabla, pero pero que pasaría si llegan mas datos aun, tendrías que actualizar otra vez los datos en la tabla de resumen.

Si te digo que eso realmente se puede hacer automática sin importar si agregas o borras datos, tu tabla se va actualizar de forma automática.  

Y que puedes hacer más, puedes ir seleccionando por sucursal para ver solamente sus datos, algún mes en particular y todo esto de forma automática en unos cuantos clics.

    Bueno luego de esta breve introducción ¿ Puedes pensar cómo usarlas en tu trabajo en los datos que deseas analizar con las tablas dinámicas? 

Te voy a mostrar un tutorial práctico sobre como crear  y usar las tablas dinámicas en Excel.

También veremos un ejemplo un poco más avanzado por que vamos a crear una tabla dinámica utilizando datos que están en varias hojas de Excel. pero vamos paso a paso.   

PREGUNTAS PARA MEDITAR  

¿ Cómo hacer tablas dinámicas en Excel ? para comenzar lo primero que debes tener en cuenta son los datos en Excel, especialmente para el tutorial vamos a usar una serie de datos de las ventas por vendedor.

Por lo que tenemos en la siguientes hojas  en Excel.

Hoja de ventas- En esta hoja tenemos todos los datos de las ventas por día y el vendedor que las realizo.

Hoja vendedores - En esta hoja tenemos los datos  de los vendedores.

Vamos a definir nuestros objetivos de información que necesitamos :

1- Obtener las ventas acumuladas por vendedor por mes.

2- Obtener las ventas acumuladas  por sucursal por mes.

3- Ordenar las información de a menor de las ventas de cada vendedor por mes.

Para poder crear tablas dinámicas en Excel debemos tener claro cual es el objetivo que queremos lograr alcanzar, de esta forma nos va hacer mas sencillo saber que datos necesitamos.

Vamos a crear una tabla Dinámica en hoja que se llama tabla dinámica.

Ahora desplegamos el menú de insertar  (1) y en seguida damos clic en una tabla dinámica (2)

Nos va aparecer la siguiente ventana 

En esta ventana es donde vamos a seleccionar  de donde se van a obtener los datos para crear la tabla dinámica

La opción que dice " seleccione una tabla o rango "  es donde vamos a seleccionar los datos que vamos a incluir nuestra tabla.

Para esto, hacemos clic en la flecha después de la tabla o rango (1)

Al presionar el botón nos muestra la siguiente ventana

Esto nos indica que Excel ya esta listo para capturar un rango en donde están nuestros datos.

Con esa ventana, nos vamos a la hoja de ventas y nos muestra en el texto lo siguiente.

El texto Ventas !, nos indica que estamos en la hoja Ventas y es ahí donde se van a obtener los datos. para esto debemos seleccionar todos los datos, puedes teclearlo o puedes seleccionar todos los datos  con el mouse.

Importante : 

Debes seleccionar con todo nombre de columnas.

De tal forma que dentro de la ventana de la tabla dinámica nos queda así.

Ventas !A$2:C$142.

Esta instrucción le dice a Excel que va a tomar  los datos de la hoja de ventas y el rango va hacer de la celda A2 Hasta la celda C142.

Hey  notaste los signos $ ¿ Sabes para que sirven ?

Nuevamente paso explicarle, no quiero hacer una extensa la explicación, pero te puedo decir que es para que Excel siempre mantenga fijo ese rango, es decir, Aun qué la tabla dinámica se mueva de lugar , siempre se van a tomar los datos de ese rango.

Después de seleccionar  nuestro rango nos ponemos en la ventana donde nos muestra el rango y así presionemos enter

Al dar enter nos muestra la misma ventana anterior y podes observar el campo tabla y rango ya se encuentra de donde va tomar los datos.

 Y solo nos falta hacer clic y aceptar.

Nos regresa a la hoja donde tenemos nuestra tabla dinámica y nos muestra los datos de esta manera :

 

 

Bueno en las celdas vas, a observar una grafica en donde nos indica donde va estar nuestra tabla y lo que es del lado derecho observas una serie de cuadros, vamos a ver que significa cada uno

 El cuadro 1  de campos de la tabla dinámica :

Aquí nos muestra las columnas que tienen nuestros datos ¿ ya observaste por que es importante incluir  los nombres de las columnas ?

Podemos seleccionar las columnas que queremos en nuestra tabla dinámica. Excel va tratar de acomodar las columnas de acuerdo a los datos que trae, pero a veces no queda como la necesitamos y es por eso que necesitamos saber para que sirven  los otros cuadros.

El cuadro 2 filtros :

En este cuadro van a ir columnas por la queremos filtrar , regularmente van campos de flechas o algún campo donde nos interese ver el detalle o mas información.

El cuadro 3 columnas :

Excel agrega este campo para indicar los valores de la columnas que vamos agregar. también podemos  agregar columnas con datos que nos interesan, esto vamos a ver en el ejemplo.

El cuadro 4 filas :

Van a estar los datos de lo queremos conocer sus valores, aquí van las columnas como nombre de productos, sucursales, vendedores, etc. Son los campos que nos describen  o nos ayudan a identificar los datos.

El cuadro 5 valores :

Regularmente Excel agrega estos campos como sumatoria y que corresponden a los valores de las columnas.

Para iniciar los valores en nuestra tabla , basta con seleccionemos los campos que queremos en el cuadro.

1, en este caso, voy a seleccionar todos los campos y Excel nos deja esta tabla dinámica de la siguiente forma.

Como vemos Excel hizo unos movimientos interesantes...

Anteriormente solo teníamos solo 3 campos fecha, vendedor y ventas.

 

Al seleccionar el campo de fecha, Excel agregó un campo llamado meses y esto lo hizo para poder agrupar en la tabla dinámica los valores por mes.

Excel creo  3 columnas :

* Etiquetas de fila.

 

* Suma de vendedor.

* Suma  de Ventas.   

Columna 1 etiqueta de filas.

Observas que hay un combo , si le das clic a la flecha a desplegar a una ventana nueva.

Bueno en este cuadro nos sirve para poder filtrar los datos de esta tabla, podemos seleccionar el mes o meses que queremos información o aplicar filtros de fechas específicos como los últimos 30  Días, ultima semana y mucho más.

Columna 2 suma de vendedor. 

Para nosotros no tiene sentido esta columna, lo que hizo sumar es sumar a los vendedores , Esto es por que Excel detecto que es una columna numérica y nos suma los valores.

En esta columna  nosotros tenemos el número de vendedor por lo que no hace sentido esta columna, pero mas adelante vamos a cambiar esta información a un lugar que haga más sentido.

Columna 3- suma de ventas.

Este dato si nos interesa , ya que es la suma de todas las ventajas de todas nuestras sucursales, por que es información muy importante.

Como ya la comentamos anteriormente , a veces Excel no nos deja la tabla dinámica con la información que necesitamos como el caso de la columna suma de vendedor.

Necesitamos conocer las ventas que ha hecho cada vendedor ¿ recuerdas que cuadro es el que necesitamos para que nos muestre los datos que necesitamos ?

Así es el cuadro de filas , es donde necesitamos  al vendedor.

 

¿ Que vamos a hacer ?, mover el dato de suma de Vendedor al cuadro de filas, de la siguiente manera :

Con el mouse , arrastramos el cuadro de suma de vendedores hacia el cuadro de filas 

Observa como Excel ya quito las palabras de suma de y solo dejo vendedores Aparentemente solo  Quito la columna, pero no se en ningún lado, Observa bien la primer columna.

El símbolo + nos indica que hay más datos por desplegar, vamos a hacer clic en enero y  nos despliega la siguiente información.

Se va desplegar los datos en detalle para el mes de enero.

Se visualizan los datos en  el siguiente orden :

Mes- Nos da el total de ventas del mes.

Fecha- Nos da el total de ventas en el día.

Vendedor- nos muestra el número de vendedor y el total de ventas que hizo ese día.

¿ Notaste el orden de los datos ?, Es el que esta definiendo en el cuadro de la derecha que dice filas, observa que en el mismo orden meses, fecha y vendedor.

Como imaginaras el orden de datos los podemos mover mediante el cuadro de filas. Supongamos que queremos desglosar las ventas de los vendedores por día como lo tenemos actualmente, se nos haría  un poco complicado verlo por que la información nos muestra primero el día y luego el vendedor.

Vamos a hacer  un pequeño cambio en el cuadro de filas, solamente arrastramos el cuadro vendedor hacia arriba y la información nos queda así.

La información ha cambiado , el orden de la información  es 

Meses. 

Vendedor.

Fecha.

Ahora la agrupación la esta haciendo por el vendedor y nos muestra por día cuanto ha vendido en ese mes , ya que nuestro objetivo era precisamente esto, con este cambio es mucho más sencillo ver estos datos.

Como esta actualmente nuestra tabla, nos muestra toda la información de los vendedores, pero que pasa si necesitamos tener los valores de un 1 o 2 vendedores solamente necesitamos filtrar los datos.

¿ Como filtrar los datos de una tabla dinámica en Excel ?

Existen 2 formas de hacer esto :

 Filtrar por la información que queremos desplegar.

Filtrar  mediante un campo para seleccionar los datos que necesitamos.

Filtrar por la información que queremos desplegar.

La primera opción, se usa cuando queremos la información fija y no interesa los demás valores.

Para esto debemos poner atención en los campos de la tabla, están al lado derecho y es el primer cuadro, si pasamos el mouse por encima observamos que se resalta el campo y a la derecha aparece una flecha hacia a bajo.

Al dar clic en esa flechita, nos va  a desplegar  el siguiente menú

En este menú nos sirve para filtrar los datos que queremos que aparezcan en nuestra tabla dinámica, Puedes seleccionar más de una opción y solo va presentar los datos que correspondan a esa selección.

En este caso vamos a seleccionar el vendedor 6  y damos clic ACEPTAR.

La tabla dinámica se va actualizar y nos va presentar la información

Observas el embudo que apareció al lado derecho del campo Vendedor, Esto nos indica que para ese campo aplicamos un filtro, de esta manera es muy fácil saber que campos usamos para presentar la información.

Esta opción es muy óptica cuando ya sabemos que datos son los que queremos presentar, si vamos a cambiar los filtros, solamente tenemos que hacer lo que hicimos anteriormente y nuestra tabla dinámica se va actualizar.

Hay otra manera de filtrar los datos, más amigable y sirve más como presentación en lo personal, es la forma que más me gusta  

Filtrar mediante un campo para seleccionar los datos que necesitamos. 

Vamos a regresar a nuestra tabla dinámica original es decir, sin ningún filtro

En los cuadros de la derecha en la parte de Filas tenemos Meses- Vendedores -Fecha,

Vamos a mover la Fila Vendedor hacia el cuadro de Filtro

Nuestra tabla dinámica se modifica, pero se agrega un nuevo renglón que se llama Vendedor y A continuación esta un combo que dice ( Todas).

Esto es por que el mover la fila Vendedor el cuadro de filtros, Excel entiende que queremos filtrar por ese campo y nos agrega un combo para seleccionar al vendedor o vendedores  que queremos ver la información.

Vamos a darle clic al botón del combo que dice ( Todas) y nos va desplegar al siguiente menú :

En este menú, podemos seleccionar al vendedor o vendedores que necesitamos, supongamos que queremos ver al vendedor  6, lo seleccionamos y damos clic  En ACEPTAR.

Nuestras tabla dinámica ya se actualizó con la formación del vendedor 6. Esto es muy útil cuando estás haciendo una presentación o simplemente necesitas sacar datos de uno varios valores.

Imagina que pasas este Excel a tu jefe o profesor o vas hacer una presentación para el, va hacer muy difícil mostrarle la información detallada de cada valor y esto te va ayudar a verte más profesional.

Hasta aquí vimos, como hacer una tabla dinámica en Excel desde una sola hoja  o fuente de datos y todo lo que puedas hacer para presentar la información que necesitas.

Espero desde ya  que te haya servido este tutorial para un entendimiento más profundo de las tablas dinámicas.

      Desde Argentina los saludos cordialmente Joel Acuña. 😎😎😎