Vamos a actualizar nuestra Política de privacidad próximamente. Te recomendamos consultar el avance.

Excel 2016: Tablas dinámicas

Costes de personal e IRPF trimestral en Excel 2016

¡Prueba gratis durante 10 días

nuestros 1290 cursos !

Prueba gratis Mostrar modalidades de suscripción
En esta ocasión ofrecemos un ejemplo de análisis de la información relativa a los costes de personal e impuestos en una pequeña empresa. Para ello usaremos tablas dinámicas cuyo origen de datos será las tablas de empleados y sus nóminas.

Transcripción

Vamos a realizar un ejercicio recopilatorio en el cual vamos a tratar muchos de los temas que se han explicado a lo largo del curso. Pero antes de empezar con la realización de varias tablas dinámicas, vamos a dar un paseo por las distintas tablas que componen este ejercicio. Aquí tenemos una tabla principal en la hoja 'Nóminas' la cual relaciona todas las nóminas que se van generando o devengando a lo largo del tiempo y también para cada uno de los trabajadores de una empresa. Por lo tanto, esta es una tabla que se va generando día a día, mes a mes, a medida que se van generando esas nóminas como hemos comentado anteriormente. Por ejemplo el dato 'Id Nombre trabajador' es una lista desplegable que tiene como origen de datos la tabla 'Trabajadores' que está en la hoja del mismo nombre. Si volvemos a la hoja 'Nóminas' vemos como 'ID Área' es una abreviatura del área donde trabaja cada una de las personas. Este dato se consigue mediante una función 'BUSCARV' la cual extrae el dato de la tabla 'Trabajadores'. Vemos que cada trabajador tiene un área asignada aquí. El resto de datos hasta 'IRPF' se van generando nómina a nómina y además van cambiando todos los meses. También tenemos otro desplegable que es el campo 'ID Tesorería' para poder elegir el banco por el cual se va a pagar dicha nómina. Este desplegable también tiene como origen de datos una tabla llamada 'Tesorería' que tenemos también en la hoja del mismo nombre. Con todo ello y partiendo de la tabla 'Nóminas' como origen de datos principal, vamos a generar en estas tres hojas 'IRPF por trabajador', 'Comisiones por trabajador' y 'Gastos de personal por Áreas', unas tablas dinámicas que nos ofrezcan información interesante para la gestión económico-financiera de la empresa. Vamos a comenzar por crear una tabla dinámica que nos diga el IRPF por cada uno de los trabajadores, que se ha devengado cada trimestre. Nos vamos a situar en esta celda y ahora vamos a la ficha 'Insertar', 'Tabla dinámica' y ahora tenemos que elegir 'Tabla o rango'. Hacemos uso del selector de rangos activamos la ficha 'Nóminas' y seleccionamos la tabla. Vemos que al hacerlo obtenemos el nombre de esta tabla de Excel que es 'TablaNóminas'. Presionamos 'Intro' y aceptamos. Aquí tenemos ya el área de trabajo para nuestra tabla dinámica. Ahora, el nombre del trabajador lo vamos a arrastrar a 'FILAS' y ahora el campo 'IRPF' que al fin y al cabo en estos momentos es el protagonista para nuestra tabla objetivo, lo vamos a arrastrar a 'VALORES'. Además, también la fecha, es decir 'Fecha nómina' vamos a arrastrarla a 'COLUMNAS'. Puesto que el IRPF es un concepto que se genera de forma trimestral en la mayoría de las empresas, vamos a cambiar la agrupación de mensual a trimestral. Hacemos clic en cualquiera de los meses de las columnas, 'Agrupar' y ahora marcamos 'trimestres' y desmarcamos 'días' y 'meses'. Ya tenemos ahora la información por trimestres. Con esto ya tenemos la tabla dinámica confeccionada de forma básica pero tenemos que hacer algunos ajustes. Vamos a seleccionar cualquiera de los valores y vamos a formato de número. Elegimos 'Moneda'. Además, también tenemos que hacer algunas configuraciones en el cuadro de diálogo 'Opciones de tabla dinámica'. Por ejemplo, la configuración imprescindible de desmarcar esta casilla, 'Autoajustar anchos de columna al actualizar'. Ahora ya podemos establecer un ancho de columna adecuado. También podemos cambiar esta etiqueta para que ponga 'Meses'. Tan solo tenemos que escribir encima y esta otra sobrescribimos con la palabra 'Trabajadores'. Si intentamos eliminar este texto que no nos hace falta, Excel nos lo va a prohibir. Por lo tanto, lo que podemos hacer es sobrescribir con un espacio. Ahora alineamos correctamente y ya tenemos nuestra tabla. Vamos ahora a hacer algo parecido con una tabla dinámica que nos informe de las comisiones que se le pagan a los trabajadores. Nos situamos por ejemplo aquí, vamos a 'Insertar', 'Tabla dinámica' y de nuevo tenemos que elegir nuestra tabla en el rango 'Nóminas'. En este caso no sería necesario seleccionar toda la tabla sino que podríamos seleccionar solamente estas columnas desde 'Id Nombre trabajador' hasta 'Comisiones'. Ahora, el nombre del trabajador lo llevamos a 'FILAS' y el campo 'Comisiones' a 'VALORES'. Si queremos también saber la distribución por fechas podemos llevar fechas a 'COLUMNAS'. Vamos a hacer ahora también algunos ajustes similares a la otra tabla. Y este podría ser un resultado. Y para ir finalizando vamos a crear nuestra última tabla dinámica, que se trata de una combinación de los gastos de personal por cada una de las áreas de la empresa. Vamos a ir a 'Insertar', 'Tabla dinámica' y en este caso también nos damos cuenta de que no es necesario seleccionar toda la tabla sino que hasta la columna 'Devengos' sería suficiente. Ahora 'Devengos' sería el valor protagonista, es decir, el coste de personal. 'Fecha nómina' podría ir en 'FILAS' y 'Área' a 'COLUMNAS'. De nuevo vamos a hacer unos ajustes. Por ejemplo el formato de número, también las etiquetas en este caso son 'Áreas'. Volvemos a sobrescribir con un espacio para eliminar esa etiqueta. Aquí serían 'Meses'. E importante también, es la configuración de desmarcar el autoajuste de columnas. De esta forma podemos establecer el ancho que nosotros queramos. Y ahora para esta tabla vendría muy bien generar un gráfico dinámico. Desde la ficha 'Analizar', 'Gráfico dinámico' y vamos a elegir por ejemplo 'Columnas' pero en este caso 'Columnas apiladas'. De esta forma tendríamos para cada columna el coste total en personal para ese mes pero apilando el coste parcial de cada una de las áreas.

Excel 2016: Tablas dinámicas

Aprende a gestionar y crear tablas dinámicas en Excel 2016 a partir de los datos que necesitas para agilizar tu trabajo diario y organizarlo según el interés de cada instante.

2:24 horas (39 Videos)
Actualmente no hay comentarios.
 
Fecha de publicación:15/12/2016

Este curso video2brain está disponible como descarga y para ser visualizado online. ¡Pero no hace falta que decidas entre las dos opciones! Al comprar el curso, disfrutarás de ambas posibilidades.

La descarga te permite ver las lecciones sin estar conectado/a a internet y supone una navegación fácil y ágil entre capítulo y capítulo. Si vas a trabajar en diferentes ordenadores o si no quieres descargarte el curso completo, entra en la web con tus datos de acceso y disfruta directamente de tus vídeos online. Te deseamos que disfrutes de este curso y te resulte de mucha utilidad.

Estamos a tu disposición si tienes cualquier tipo de duda o pregunta.