viernes, 25 de noviembre de 2011

Modelo 347 por trimestres para el 2012 con Excel.

En un correo reciente un lector del blog consultaba la posibilidad de automatizar la agrupación, por ciertas condiciones, de ciertos datos; concretamente quería poder aplicarlo sobre la elaboración de un impuesto (modelo 347 - Declaración anual de operaciones con terceras personas (ver)). Para esto utilizaremos las Tablas dinámicas de Excel, empleando sus herramientas de agrupación y filtro.
La consulta decía:

...En Febrero de 2012 he de declarar la facturación total del 2011 de clientes y proveedores que superen los 3005,06 euros, en el Modelo 347 de Hacienda. Los importes los hallo mediante SUBTOTALES por CIF y suma de Total Fra., pero la novedad y como consecuencia problema, es que solicitan también los importes trimestrales de los que superen dicha cifra y no sé como detallar estos datos de forma automática...


Logícamente partiremos de un listado de las operaciones realizadas durante el año con nuestros proveedores (igual proceder para nuestros clientes). Supongamos un listado de proveedores con los que hemos trabajado:


A simple vista podemos comprobar cómo para cada proveedor, todas sus facturas acumuladas por trimestres, ya algunos de ellos sobrepasan la cantidad estipulada (3.005,06 euros) en el año.

Para conseguir nuestro objetivo, lo primero que haremos será Insertar una Tabla dinámica (o en nuestro caso Resumir la Tabla con Tabla dinámica); agregando el campo 'FECHA' al área de columnas, el campo 'CIF' al área de filas, y el Campo 'Total fra.' al área de datos. También le daremos un aspecto Tabular al diseño de nuestro informe (ver como). Nos quedaría:


Empezamos ahora nuestro trabajo para agrupar por trimestres el campo 'FECHA' (como?). Desde las Herramientas de Tabla dinámica > Diseño > Agrupar > Agrupar campos:

Modelo 347 por trimestres para el 2012 con Excel.


con lo que obtendríamos la agrupación deseada...

Modelo 347 por trimestres para el 2012 con Excel.


Por último, podemos ver fácilmente que algunas de esas agrupaciones (sumas acumuladas) por trimestres no superan el límite de los 3.005,06 euros, por lo que para dejarlos fuera de nuestra vista, aplicaremos un Filtro de valor sobre el campo 'CIF':

Modelo 347 por trimestres para el 2012 con Excel.


Modelo 347 por trimestres para el 2012 con Excel.


con lo que llegamos a nuestro listado definitivo por cada proveedor por trimestres, de aquellos que sobrepasen el límite anual de 3.005,06 eur:

Modelo 347 por trimestres para el 2012 con Excel.


15 comentarios:

  1. Buenos días, muy buena explicación de una aplicación práctica de las tablas dinámicas.
    Simplemente señalar que, si no me equivoco, la cantidad a superar es 3.005,06 euros en computo anual no trimestral con lo cual si filtras de la forma que se indica en la explicación, ocultarás información relevante para el informe.
    Saludos cordiales.

    ResponderEliminar
  2. Muchas gracias Victor,
    es correcto lo que comentas... procedo al cambio.
    Sin embargo el método es igualmente válido con una mínima explicación que añado al post.

    Repito el agradecimiento!!!
    Un saludo

    ResponderEliminar
  3. Gracias a ti por la labor que haces...
    Un saludo

    http://victorbaubarr.blogspot.com/

    ResponderEliminar
  4. antonio muñozfebrero 04, 2012

    Buenos días estoi intentando realizar tabla dinamica para 347 sin embargo, la pestaña agrupar campos no se activa, y si entro en cif filtros de valor tampoco se activa las pestañas, que puedo hacer?
    gracias.

    ResponderEliminar
  5. Hola Antonio,
    la funcionalidad de Agrupar es algo que se activa automáticamente situandote sobre el campo (en la Tabla dinámica) que desees agrupar, asegúrate de situarte en el lugar apropiado, y que los elementos son susceptibles de ser agrupados.
    Si tienes algún problema, después de verificarlo, envíame el fichero a:
    excelforo@gmail.com
    Slds

    ResponderEliminar
  6. Antonio, me pasaba lo mismo y lo arreglé seleccionando en la tabla valores con contenido (mira que no seleccionas celdas sin contenido, si no lo podrás hacerlo).

    Ahora me pasa a mí otra cosa y es que no me da opción a hacer el filtrado al final por los 3000 euros, no se me ilumina la opción de filtrado solo la de filtrado por los "10 mejores..." no sé que estaré haciendo mal. Se agradecería un ayudita.

    ResponderEliminar
    Respuestas
    1. Hola Edu,
      debes seleccionar y desplegar el filtro que te aparece en el campo CIF (del ejemplo)... no sirve si seleccionas el filtro desde alguno de los elementos.
      Un cordial saludo

      Eliminar
  7. Hola, alguno sabría decirme cómo podría exportar el excel que hemos obtenido al formato BOE (que no es mas que un .txt) para que la aplicación de la Agencia Tributaria pueda importarlo y por tanto no tener que picar a mano los resultados?

    Gracias anticipadas, un saludo,

    Miguel

    ResponderEliminar
    Respuestas
    1. Hola Miguel,
      este es un blog de Excel, creo que encontrarás más respuestas en algún foro específico de aplicaciones 'oficiales'; ya que aunque sólo sea un archivo .txt, es necesario conocer la estructura de datos de dicho fichero.
      Suerte un cordial saludo

      Eliminar
  8. Hola,

    Para completar el mod.347 hará falta el nombre del proveedor y la província ¿Se pueden incluir en la tabla dinámica?

    Gracias,

    José

    ResponderEliminar
    Respuestas
    1. Hola José,
      si claro, sólo tienes que añadir el campo 'Nombre' y 'Provincia' en el área de filas y elimiar los Subtotales si es que lo tienes configurado.
      Slds

      Eliminar
    2. Hola,

      En la configuración de filas, indico subtotales "ninguno" pero siguen saliendo. ¿Cómo los puedo quitar?

      Gracias,

      José

      Eliminar
    3. Hola José,
      tienes un par de posibilidades, una es seleccionando el campo que tiene los subtotales y con el botón derecho del ratón, desmarcar del menú contextutal Subtotal 'campoxxx'; otra forma sería ir a Configuración de campo, y en la Pestaña 'Subtotales y filtros' seleccionar 'Ninguno'.
      Lo importante en ambos casos es que tengas seleccionado el campo en el que están los subtotales mostrados.
      Slds

      Eliminar
  9. mi problema es la tabla de partida de este ejemplo. En mi programa de contabilidad no sé de dónde sale eso. Puedo trabajar balances de sumas y saldos para el periodo que yo quiera, o descargar mayores en excel, pero esa primera tabla parece la cuenta contable de compras, que en mi caso, al ser una empresa industrial de cierto volumen se desglosa en unas 30 subcuentas.

    ResponderEliminar
    Respuestas
    1. Hola,
      busca en tu sistema una tabla de movimientos de IVA soportado y repercutido, o en el peor de los casos, del diario filtra para obtener todos los movimientos de compras y ventas...
      Es dificil darte una respuesta sin conocer tu ERP. Otra opción es consolidar esas 30 subcuentas de que hablas.
      Slds

      Eliminar