En este ejemplo hemos creado un control de los gastos de una casa con diferentes informes para conocer en todo momento cómo está cada una de las partidas de gasto como la luz , teléfono, etc. con respecto a otros años.
Puedes utilizar este ejemplo para controlar los gastos de una empresa.
En la etiqueta Gastos Casa sólo tienes que eliminar los datos de la tabla, el Gasto , Importe y Fecha, recuerda que puedes seleccionar todos los datos con Ctrl+E . (este ejemplo es funcional en Excel 2010-2016 por las segmentaciones, en 2007 no existen, luego al abrirlo sólo podrás utilizar las Tablas dinámicas y Gráficos, pero no segmentaciones).
La columna Gastos tiene una validación de datos, si seleccionas alguno de ellos y vas a la ficha DATOS > Validación de Datos , puedes ver ésta. (Recuerda que el origen de la Validación está en la columna ocultada F)
Luego haz clic en cualquiera de las tablas dinámicas y desde la ficha Herramientas de tabla dinámica > Datos> Actulizar>ActualizarTodo para actualizar todos los informes.
En el primer informe por ejemplo puedes conocer como el gasto de Agua ha evolucionado en los años. Haz clic en la segmentación Gastos en el elemento Agua, con la tecla Ctrl. puedes marcar varios gastos a la vez, por ejemplo Agua+Gas+Luz.
El informe Gastos por Partidas Años y Meses prueba a filtrar por Gasto Luz y un cuatrimestre, comprueba la evolución del gasto Luz en cada año.
El cuarto informe muestra la diferencia de un gasto en € y % entre los diferentes años, por ejemplo observa como el Gas ha subido un 65,86% desde 2011 al 2012.
Descargar archivo con el ejemplo:
Excel Trucos & Power BI Tips 
Hola, muy instructivo, gracias.
¿Me podrías decir como haces para que desde un campo de fecha se haga una segmentación por meses y años? Cuando hago una segmentación por fecha sí, pero no me sale en forma de botones y si segmento de forma normal me aparecen los campos fecha.
No me aparece el campo años como en el ejemplo.
Hola Manuel,
Al tener un campo fecha en la tabla de origen, cuando hacemos una tabla dinámica y añadimos ese campo puedes sobre el AGRUPAR, añade el campo FECHA en la tabla dinámica y selecciona una fecha , con el botón derecho verás la opcion AGRUPAR, agrupa por meses y años y tendrás la posibilidad de crear una segmentación por mes y año.
Otra opción útil es sobre la tabla de origen crear dos campos con funciones uno con mes =MES(Fecha) y otro año =AÑO(Fecha)
Una tercera opción si usas Excel 2013 es la segmentación INSERTAR ESCALA DE TIEMPO.
Saludos y gracias por seguir mi blog.
Hola, me gustaria hacer una tabla de dinamica para controlar las dietas de los choferes por fecha y viaje. Me puedes echar una mano. Mucha sgracias
Hola, gracias por seguir ExcelTrucos, a través del mail de contacto del blog puedes solicitar un presupuesto de tu proyecto.
Saludos.
Excelente ejemplo, muy didactico, recomendare tu blog.
Muchas gracias, seguiremos trabajando en el 😉
excelente planilla,
favor enviar un correo de contacto, me interesa esta planilla
Hola Felix, tienes la plantilla para descargar al final del post.
Control Gastos Tabla dinámica
Hola,
Una pregunta la tabla agrupa las cuentas de gastos por categorías o grupos de gastos?
Saludos
Miguel
Hola Miguel, lo mejor suele ser crear una columna nueva en la tabla, con la categoría o grupo al que pertenece cada gasto, (puedes crear una fórmula con un condicional SI o BuscarV para que automáticamente al escribir el gasto te añada la categoría), así luego lo puedes filtrar por éste o utilizarlo en una tabla dinámica.
Otra opción es agrupar en la Tabla Dinámica pero esa opción es un poco menos funcional.
Gracias por seguir el Blog 😉
Hola Santiago:
Me parecio muy buena la tabla de gatos, pero no puedo ingresar los gastos para probarla al eliminar el contenido de la hoja 1 control de Gastos casa, me aparece el siguiente dialogo; «ESTE VALOR NO COINCIDE CON LAS RESTRICCIONES DE VALIDACION DE DATOS DEFINIDAS PARA ESTA CELDA», Tengo excel 2010, Que debo hacer??
Saludos
Rodrigo Molina
Hola Rodrigo, la columna gastos tiene una Validación de datos, si seleccionas cualquier gasto y vas a la ficha Datos > Validación de Datos, verás que el origen de éstos son los gastos que están en la columna F (ocultada), si la muestras los verás, sólo modifícalos, añade o elimina los que quieras.
Ten en cuenta modificar el rango para la Validación de datos. Ese mensaje te sale porque sólo puedes escribir los gastos que aparezcan en esa lista.
Otra opción sería quitar la Validación, para eso selecciona todos los gastos de la columna y desde la ficha Datos > Validación de Datos –> Borrar todos
Muchas gracias por seguir el Blog.
Buenas Tardes.
Gracias por la tabla, me podría decir en que parte del archivo puedo modificar los gastos, quitar lo que tiene de servicios publico y montar otros concepto paro que me realice lo mismo que hace con los servicios.
Gracias
Hola, tienes la respuesta a tu duda en la pregunta de Rodrigo Molina, tienes que cambiar los datos de la validación.
Gracias por seguir ExcelTrucos 😉
Hola buenas tardes, como inserto en la tabla dinamica en la hoja de caluclo diferencias, las columnas de diferencia importe año anterior en monto y en porcentaje?
Hola en la Tabla Dinámica, al mostrar la lista de campos, en el campo que tengas en el sumatorio de valores, debes ir a Configuración del campo de valor y desde ahí Mostrar valores como.
Gracias por seguir ExcelTrucos.com
Hola, me parece una excelente idea. Pero necesito que por favor me enseñes a cambiar la moneda de todas las pagina, debido a que en mi país se utiliza otra moneda.
Gracias
A la vez, quiero ver la posibilidad de realizarlo mes a mes.
disculpa las molestias.
Gracias
Hola Carlos, el informe de tabla dinámica permite añadir el campo «Fecha» a Filas o Columnas de forma que veas las fechas, posteriormente los puedes agrupar por meses.
Otra opción es que en la tabla de datos, añadas una nueva columna con el cálculo =MES(«campoFecha»), este nuevo campo lo puedes añadir a cualquier tabla dinámica. Te recomiendo lo pruebes con una Tabla Dinámica nueva en otra hoja.
Gracias por seguir ExcelTrucos.com
Hola Carlos, debes hacerlo desde la tabla de datos a toda la columna Importe, desde Más formato de número … > Contabilidad y ahí eliges el símbolo o moneda. Otra opción es con un formato personalizado de tipo #.### «usd»
(luego actualizas las Tablas Dinámicas)
Hola me gustaria un presupuesto para hacer una plantilla para controlar mis gastos de la casa. Muy sencilla pero con mis especificaciones. Como nos podemos poner de acuerdo?
Hola puedes contactar con nosotros a través de nuestra Web en: https://exceltrucos.com/consultoria-formacion/
Hola buenas tardes, me he descargado la plantilla para he intentar personalizarla a mis gastos personales, pero resulta que una vez he cambiado las partidas en la columna F, gastos Partidas, cuando quiero ver el resultado en la segunda hoja, TD Gastos Casa, me siguen saliendo los valores originales así como el año 2011… Como hago para que se vean en las estadísticas mi personalización?
Gracias.
Hola, si modificas los valores de la columna F, debes comprobar que al elegir datos con la Validación de Datos en la tabla han cambiado y que puedes elegir el que quieras de tu listado.
Una vez están los nuevos datos en la tabla, debes actualizar la tabla dinámica y deben aparecer en esta.
Lo mejor es que borres todos los datos de la tabla y empieces desde cero con los datos.
Gracias por seguir el Blog, comparte en Facebook 😉
Hola buenas, debes comprobar que al elegir datos con la Validación de Datos en la tabla han cambiado y que puedes elegir el que quieras de tu listado, hasta aquí todo correcto, Cuando le doy a actualizar la tabla dinámica los valores si que se me actualizan, pero todo lo que tiene que ver con fechas, años, meses… me desaparece… las fechas introducidas son con vistas al 2018 ya que la quiero emplear a partir del próximo año.
Hola, me podrías decir como anexo el campo gastos para determinar el importe y la diferencia con respecto al año anterior en el reporte de diferencias.
Gracias y me parece un excelente ejemplo para gestión profesional de gastos,
Hola, cuando haces la tabla dinámica en Sumatorio de Valores añades el campo importe dos veces.
Al segundo campo añadido en Configuración de campo de valor en Mostrar valores como, eliges Diferencia de o <«% de la diferencia de» y en Elemento Base (anterior)
Gracias por seguir ExcelTrucos.com , síguenos en Facebook y comparte 😉
Hola
Me parece muy útil esta plantilla. Me pregunto si me puedes ayudar, he modificado los datos para utilizarla, y cuando le doy a actualizar me dice «excel was unable to update the pivot table because it would have overlap another pivot table» y no consigo que funcione….
Hola Ainara, eso ocurre seguramente porque has añadido más gastos en la tabla, de esa forma hay más elementos en un campo por lo que en las tablas dinámicas ocupa más filas.
Revisa las dinámicas y añade filas entre estas.
Gracias por seguir excel trucos.