Control Gastos con Tabla Dinámica y Segmentaciones

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.

control gastos casa 1

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.

control gastos casa 2control gastos casa 3

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.

control gastos casa 4 control gastos casa 5

Descargar archivo con el ejemplo:

Control Gastos Tabla dinámica

Anuncios

20 comentarios en “Control Gastos con Tabla Dinámica y Segmentaciones

  1. 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.

  2. 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 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 😉

  3. 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.

  4. 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

  5. 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

  6. 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

      • 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)

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s