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:
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:
con lo que obtendríamos la agrupación deseada...
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':
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:
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:
con lo que obtendríamos la agrupación deseada...
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':
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:
Buenos días, muy buena explicación de una aplicación práctica de las tablas dinámicas.
ResponderEliminarSimplemente 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.
Muchas gracias Victor,
ResponderEliminares 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
Gracias a ti por la labor que haces...
ResponderEliminarUn saludo
http://victorbaubarr.blogspot.com/
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?
ResponderEliminargracias.
Hola Antonio,
ResponderEliminarla 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
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).
ResponderEliminarAhora 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.
Hola Edu,
Eliminardebes 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
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?
ResponderEliminarGracias anticipadas, un saludo,
Miguel
Hola Miguel,
Eliminareste 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
Hola,
ResponderEliminarPara completar el mod.347 hará falta el nombre del proveedor y la província ¿Se pueden incluir en la tabla dinámica?
Gracias,
José
Hola José,
Eliminarsi 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
Hola,
EliminarEn la configuración de filas, indico subtotales "ninguno" pero siguen saliendo. ¿Cómo los puedo quitar?
Gracias,
José
Hola José,
Eliminartienes 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
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.
ResponderEliminarHola,
Eliminarbusca 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