Aprende que es, para que sirven las tablas dinámicas adicionalmente como usarlas con ejercicios el cual vamos a mirar agrupación de datos, rellenar celdas vacías, ranking, comparaciones entre diferentes periodos, segmentación de datos y operaciones con tablas dinámicas.
¿QUE ES Y PARA QUE SIRVEN LAS TABLAS DINÁMICAS EN EXCEL?
Las tablas dinámicas son un tipo de tabla que nos permiten decidir con facilidad los campos que aparecerán como columnas, como filas y como valores de la tabla y podemos hacer modificaciones en el momento que lo deseamos.
Se conocen como tablas dinámicas porque tu decides "dinamicamente" la estructura de la tabla, es decir sus columnas, filas y valores.
Son una gran herramienta que nos ayuda a realizar un análisis profundo de nuestro datos ya que podemos filtrar, ordenar y agrupar la información de la tabla dinámica de acuerdo a nuestras necesidades.
Las tablas dinámicas en excel nos sirven para comparar grandes cantidades de datos e intercambiar fácilmente columnas por filas dentro de la misma tabla y realizar filtros basado a los criterios que deseamos.
¿COMO INSERTAR TABLAS DINÁMICAS EN EXCEL?
Para insertar una tabla dinámica nos vamos para la pestaña de insertar y en el grupo de tablas nos vamos para tabla dinamica
En el campo de seleccione los datos que desea analizar en esta campo podemos seleccionar una tabla del archivo o un rango.
También nos podemos conectar los datos de fuentes externas de otro archivo u otra base de datos.
En la opción de elija donde desea colocar el informe de tabla dinámica podemos seleccionar la opción de nueva hoja de cálculo o en una hoja de calculo existente y aquí seleccionamos una celda donde queremos iniciar la tabla dinámica.
En el siguiente video se explica que es y para que sirven las tablas dinamicas en excel.
EJERCICIOS DE TABLAS DINAMICAS EN EXCEL
Como pueden observar tengo la siguiente información el cual tenemos nombres, lugar, departamento, edad, ocupación y salario y vamos a crear la tabla dinámica para crear resúmenes de información y análisis.
El ejercicio es obtener el promedio de edad de los empleados por DPTO y ocupación
El primer paso es seleccionar los datos y luego insertar la tabla dinámica como se explica en el procedimiento anterior.
Al seleccionar que la tabla dinámica va ser en una nueva hoja obtenemos lo siguiente.
En la parte derecha tenemos los campos de la tabla dinamica el cual vamos a ir arrastrando cada uno de las variables.
Entonces como se muestra en la imagen tenemos los nombres de cada columna, nombre, lugar, dpto, edad, ocupación y salario. Posteriormente en el campo de filtro vamos a poder arrastrar datos ahí para despues aplicar filtros que deseamos.
En el campo de columna podemos arrastrar una variable para que se despliegue por columnas.
En el campo de filas podemos arrastrar una variable para que despliegue por filas.
En el campo de valores arrastramos columnas que contengan valores y queremos realizar los resumenes
Entonces para realizar el ejercicio de obtener el promedio de edad de los empleados por DPTO y ocupación.
Para eso arrastramos la ocupación y el DPTO hacia las filas y el salario arrastramos valores.
Cuando arrastramos campos hacia los valores automaticamente realiza la suma
Queremos cambiar la operación de suma por promedio y presionamos clic en la pestaña de suma de salario.
Posteriormente configuración de campo de valor
Como pueden ver tenemos suma, recuento, promedio, max, min, producto, contar, desvest. Para el ejemplo obtenemos el promedio y presionamos aceptar.
El resultado sería el siguiente, como podemos ver tenemos el promedio por Ocupación en cada departamento y aparte de todo me saca el total entre todos los promedios.
Ahora si empezamos a cambiar las posiciones de ocupación y dpto para que miremos como cambia el diseño.
Anteriormente teníamos las dos variables en las filas ahora pasamos la ocupación en las columnas y el diseño cambia.
Cambiando las dos variables en columnas el diseño se muestra todo en columnas.
Hay que entender que la forma de posiciones depende de buscar siempre la mejor forma que sea entendible para el análisis de la información.
En el siguiente video se explica mas ejercicios sobre tabla dinámicas.
En el siguiente ENLACE puedes DESCARGAR el material para que puedas practicar los ejercicios vistos.
¿COMO AGRUPAR DATOS EN UNA TABLA DINÁMICA POR EDADES, MES, TRIMESTRAL O ANUAL EN EXCEL?
Cuando tenemos una información extensa como por ejemplo una base de datos de todos los meses o de varios años queremos realizar un análisis anual por ejemplo, trimestral, o las fechas las tenemos en día y las queremos agrupar mensual o por intervalos de un rango de valores aquí es cuando utilizamos esta herramienta de agrupación de datos en tablas dinamicas.
El primer ejercicio que vamos a realizar es realizar una sumatoria por ocupación con intervalos de 10 años de edad.
La base es similar a la anterior solo que tenemos un campo de fecha de ingreso.
Seleccionamos la información e insertamos una tabla dinamica como se explica en la parte superior donde colocamos la ocupación y la edad en filas y el salario en el campo de valor. Como se muestra en la imagen la edad se muestra discriminada y aquí es cuando queremos agrupar la información entonces hacemos lo siguiente.
Nos ubicamos en la tabla dinámica y presionamos clic derecho y nos dirigimos en agrupar
Nos abre la ventana de agrupar donde automaticamente nos detecta el número menor y mayor y en el argumento por colocamos el intervalo que deseamos agrupar, para esta caso va ser 10 y presionamos aceptar.
Como se observa en la imagen ya quedó agrupada la información por rango de edad de 10
Cambiamos de posición la edad en columnas para ver el cambio de diseño que ocurre
En el siguiente ejercicio vamos agrupar la información por mes o por trimestre y para eso arrastramos en fila la ocupación y fecha de ingreso, se debe tener presente que dependiendo de la versión de excel, si son las ultimas excel te agrupa de forma automatica por mes pero si tienes una versión anterior y te sale discriminado las fechas lo podemos realizar de la siguiente forma.
Presionamos clic derecho en la tabla dinámica y posteriormente agrupar.
Automaticamente excel detecta que son fechas y nos muestra la fecha inicial y la fecha final y en el argumento de por tenemos muchas opciones para agrupar, por días, meses, trimestre y años, para el ejercicio lo vamos hacer por trimestre y aceptar.
Podemos cambiar la ubicación de fecha por columna y ahora tenemos la agrupación por trimestre y saber la cantidad de ingresos por ocupación.
En el siguiente video se explica como agrupar datos en tablas dinámicas en excel
¿COMO RELLENAR O REEMPLAZAR LAS CELDAS VACÍAS POR CEROS O TEXTOS EN TABLAS DINÁMICAS?
En ocasiones cuando tenemos una tabla dinámica hay variables que no se cuenta con información y dentro de la tabla dinámica esta vacía y queremos cambiarla o reemplazarla por un cero o por un texto que diga por ejemplo no hay datos y para eso nos vamos a basar de la base de datos anterior
Entonces insertamos una tabla dinámica como se explica anteriormente y queremos obtener la cantidad de ingresos por ocupación y lugar.
Entonces tenemos en el campo de fila el lugar, en el campo de columnas tenemos la ocupación y en el campo de valores tenemos que nos cuente el salario.
Como se muestra en la imagen tenemos celdas vacías porque no tenemos datos como por ejemplo, Bello y ocupación Administrativo.
para reemplazar los datos vacíos presionamos clic derecho en la tabla dinámica y nos vamos para la opciones de tabla dinámica.
En la ventana de opciones de tabla dinámica en el campo de para celdas vacías, mostrar aquí colocamos el dato que deseamos por ejemplo cero y presionamos aceptar.
El resultado sería el siguiente
Si cambiamos por un texto como por ejemplo Sin Datos y presionamos aceptar.
El resultado sería el siguiente
En el siguiente video se explica como reemplazar los datos para celdas vacía en tablas dinámicas
¿COMO REALIZAR VARIACIÓN ENTRE MESES EN VALOR Y PORCENTUAL EN TABLAS DINÁMICAS?
En ocasiones tenemos como ejemplo las ventas por mes y queremos obtener la diferencia entre ventas y para eso vamos a utilizarlo en la configuración de campo de valor.
Tenemos la siguiente base de datos donde tenemos el nombre del cliente, la zona, la venta y la fecha
En la tabla arrastramos la fecha en las filas y en el campo de valor las ventas para obtener el total de las ventas
Cambiamos la opción por suma.
Luego nos vamos para la pestaña de mostrar valores como y cambiamos la opción por diferencia de, luego seleccionamos la opción de meses el cual va ser la variable que va tomar para sacar la diferencia y luego en el elemento base seleccionamos la opción anterior, es decir que siempre va restar el valor con el mes anterior, pero podemos hacerlo con el siguiente o con un mes en particular.
Entonces tenemos la variación % entre meses con la tabla dinámica
¿COMO SACAR EL PORCENTAJE DE PARTICIPACIÓN CON TABLAS DINÁMICAS EN EXCEL?
Cuando tenemos información por ejemplo de las ventas de cada mes y queremos saber la participación de la venta de cada mes con respecto al total y para eso excel nos brinda una herramienta para obtenerlo
Tenemos la siguiente base de datos donde tenemos el nombre del cliente, la zona, la venta y la fecha
Insertamos una tabla dinámica como se explica en los pasos anteriores.
En la tabla arrastramos la fecha en las filas y en el campo de valor las ventas para obtener el total de las ventas
Ahora vamos a calcular la participación porcentual de ventas por mes entonces para esto vamos arrastrar de nuevo a campo de valores la venta y nos vamos para la configuración de campo de valor.
Cambiamos la opción por suma.
Luego nos vamos para la pestaña de mostrar valores como y cambiamos la opción por % del total general, luego presionamos aceptar.
El resultado sería el siguiente.
¿COMO APLICAR SEGMENTACIÓN O FILTROS DE DATOS CON TABLAS DINÁMICAS EN EXCEL?
La segmentación de datos es una herramienta excelente que nos brinda las tablas dinámicas el cual me permite aplicar filtros a una tabla dinámica y esto hace que la información y el análisis que realicemos sea dinámica.
Tenemos la siguiente base de datos donde tenemos el nombre del cliente, sexo, lugar, dpto, edad, ocupación y salario.
Insertamos una tabla dinámica como se explica en los pasos anteriores.
En la tabla arrastramos el lugar en el campo de las filas y en el campo de valor arrastramos la suma de salario
Nos ubicamos en la tabla dinámica y nos ubicamos e la pestaña de analizar y desplazamos al grupo de filtrar y presionamos clic en el icono de insertar segmentación de datos
Se nos abre la ventana de Insertar segmentación de datos y aquí vamos a escoger las variables por las que deseamos aplicar los filtros, para este caso va ser el Sexo y la ocupación y presionamos aceptar.
Se nos crea dos ventanas para aplicar los filtros y listo para presionar clic.
El resultado obtenido es el siguiente.
¿COMO REALIZAR OPERACIONES ENTRE COLUMNAS Y FILAS CON TABLAS DINÁMICAS EN EXCEL?
En ocasiones tenemos que realizar operaciones entre una tabla dinámica para obtener un valor deseado o también entre filas y para eso las tablas dinámicas nos brinda dos herramientas campos calculados y elementos calculados.
Tenemos la siguiente base de datos donde tenemos el nombre del cliente, la zona, la venta y la fecha
Insertamos una tabla dinámica como se explica en los pasos anteriores.
En la tabla arrastramos la fecha en las filas y en el campo de valor las ventas para obtener el total de las ventas
Luego nos ubicamos en una celda de la tabla dinámica y luego en la pestaña de analizar nos vamos para campos, elementos y conjuntos y seleccionamos la opción de campo calculado.
Aquí vamos a realizar una formula para obtern las ventas totales incluyendo los impuestos y vamos asumir que el impuesto es el 19% entonces en el argumento de Nombre le vo a colocar el nombre de Total + impuestos luego en el argumento de formula vamos a colocar la formula que va ser igual a Ventas + (Ventas *19%) para colocar la variable en la formula debemos de darle Clic al titulo de campos o escribiéndolo y luego presionamos sumar o aceptar.
Como se muestra en la imagen creamos una columna calculada llamada total + impuestos
Ahora vamos a realizar lo mismo pero a través de filas entonces tenemos la siguiente información, la zona el valor y el tipo de variable es decir sí el valor es un ingreso o un costo
Insertamos una tabla dinámica donde tenemos la zona y el tipo en el campo de filas y el valor en el campo de valores.
Queremos calcular la utilidad agregándola en una fila, entonces nos ubicamos en cualquier celda de etiquetas de fila y luego nos vamos para la pestaña de analizar y luego cálculos y elemento calculado.
En esta ventana vamos a nombrar la variable que va ser utilidad y luego la formula va ser =INGRESO - COSTO, para colocar la variable en la formula le das doble clic al elemento y presionamos aceptar.
Como se observa en la imagen se creo un elemento llamado utilidad
Como se muestra en la imagen la etiqueta creada también la suma y no queremos que nos pase entonces en la pestaña de diseño nos vamos para subtotales y le decimos no mostrar subtotales
El resultado sería el siguiente
En el siguiente video te explico como utilizar la columna calculada en tablas dinámicas en excel
En el siguiente ENLACE puedes DESCARGAR la base de datos para que practiques los ejercicios.
Seguramente quieres sabes más acerca de Excel, no te olvides darte una vuelta por las siguientes secciones disponibles para ti de manera gratuita
Canal de YouTube
Comunidad Telegram
Instagram: Aprende y Enseña Excel
Facebook: Aprende Excel Fácil
Twitter: Aprende y Enseña Excel
Hasta una próxima y te deseo el mayor de los EXITOS.
Aprende y Enseña Excel
