lunes, 26 de octubre de 2009

Macro para agrupar datos de diferentes hojas en Excel.

En una entrada anterior determinabamos, empleando la herramienta Consolidar el número de elementos, agrupados por un concepto, de diferentes rangos de datos situados en varias hojas de nuestro Libro; esta herramienta -Consolidar- está diseñada para obtener un resultado numérico en base a la función objetivo seleccionada.
Veíamos en nuestro ejemplo de Consolidar, que nuestra empresa se dedicada a gestionar un pequeño puerto con tres muelles de atraque, para la cual en nuestro fichero almacenabamos los datos de cada uno de estos muelles en una hoja del libro diferente; en esta ocasión no nos interesa conocer únicamente el número de embarcaciones atracadas en cada fecha, si no que queremos conocer cuáles son los nombres de estos barcos.
Para ello vamos a generar una macro con la que en primer lugar se nos liste en una hoja resumen, de manera conjunta, los datos de todos los barcos de los tres muelles; para posteriormente generar en base a este listado único una Tabla dinámica donde poder ver la información de los barcos agrupados por fecha.
Disponemos de los siguientes datos en cada Muelle:


Será básico antes de comenzar a desarrollar nuestra Macro definir los siguientes nombres para los rangos dinámicos:
listado =DESREF(Resumen!$A$1;;;CONTARA(Resumen!$A:$A);2)
Muelle1 =DESREF(MuelleA!$A$2;;;CONTARA(MuelleA!$A:$A)-1;2)
Muelle2 =DESREF(MuelleB!$A$2;;;CONTARA(MuelleB!$A:$A)-1;2)
Muelle3 =DESREF(MuelleC!$A$2;;;CONTARA(MuelleC!$A:$A)-1;2)
El desarrollo de la macro tendrá que ser:

haz click en la imagen


Analicemos la Macro antes de dar por finalizado el ejercicio.
Las primeras partes nos permiten seleccionar cada uno de los rangos de datos de las diferentes hojas y pegarlos una a continuación del otro en una hoja Resumen:


de forma análoga para las tres hojas (MuelleA, MuelleB y MuelleC) que tenemos.
Una vez agrupado en un único listado todos los datos, continuamos con la Macro e incluimos una tabla dinámica que nos de la siguiente estructura:


Por último, ya creada la Macro, que hemos llamado 'agrupación_lista', asignamos esta Macro a un Botón para ejecutarla.
Ya podemos visualizar los datos de nuestros tres Muelles, agrupados por Fecha, con los nombres de los barcos amarrados...
Adjunto fichero para análisis:

Listar por fecha-varias hojas
Listar por fecha-v...
Hosted by eSnips


P.D.: Al no ser especialista en programación VBA se admiten mejoras en la Macro ;-)

12 comentarios:

  1. Hola. Queria ayuda sobre como hacer una tabla.
    Hice una planilla con 4 hojas, con sus respectivos nombres.
    En 3 de esas hojas cargo datos; Nombre, Apellido, Tel, etc. Hice una macro para guardar estas 3 hojas en otro libro y con el nombre del valor de una celda de una de las hojas. Despues la misma macro "limpia" los formularios de las 3 hojas para cargar nuevos datos.
    Lo que quería hacer es que antes de limpiar el formulario me copie el valor de algunas celdas (de una sola de las hojas) en la 4ta hoja. Al ingresar nuevos datos me copie estos nuevos valores en la fila siguiente o sea la priner fila sea la de los rpimeros datos cargados. Es decir preciso hacer una especie de registro de datos de los formularios que hice.
    Busqué en los foros pero no vi nada lo suficientemente claro para mi nivel primerizo en macros. Probé varias opciones pero no pude hacerlas andar. Con grabación de macros puedo copiar esos valores pero no sé como hacerlo para que al siguiente formulario que guarde lo haga en otra fila ya que hace referencia a celdas fijas. Mis conocimientos llegan hasta ahí.
    Quería saber donde buscar algún requerimiento similar o si pueden ayudarme.
    Desde ya muchas gracias
    Saludos
    Gustavo

    ResponderEliminar
  2. Hola Gustavo,
    más adelante (cuando consiga ponerme al día de correos y comentarios del verano) subiré algún ejemplo de lo que quieres...
    Pero probaría dentro de tu código VBA con la instrucción
    'para insertar fila encima de la celda activa
    ActiveCell.EntireRow.Insert

    'para insertar fila debajo de la celda activa
    ActiveCell.Offset(1).EntireRow.Insert

    Saludos

    ResponderEliminar
  3. HOLA ME INTERESO MUCHO TU EJERCICO MUY BUENO, PERO VEO QUE EL LINK DEL ARCHIVO SE ENCUENTRA ROTO... ESPERO TENGAS AUN EL ARCHIVO DENTRO DE TUS CURIOSIDADES

    ResponderEliminar
    Respuestas
    1. Hola Luís Antonio,
      lo siento de verás, pero hace un tiempo tuve qeu cambiar de equipo y perdí bastantes archivos; especialemente lo más viejos como este...
      :-(
      Tendrás que seguir los pasos explicados.
      Suerte y un saludo

      Eliminar
  4. Quisiera ver si me puedes ayudar?

    En mi caso tengo varios archivos en los que tengo material que llega a la columna AHW. Mientras que en otra fila si solo llega a la colmna z.

    Esto sigue asi por 500 filas.

    ¿Como hago para unir todos los contenidos de estas celdas en una sola columna (A)?

    Para mi seria mas sencillo revisar 65mil celdas (filas) en una sola columna.

    Agradezco la ayuda en esto

    ResponderEliminar
    Respuestas
    1. Hola Harold,
      he de suponer que en los diferentes libros de trabajo, existen campos comunes (y que se llamen igual), que son los que te interesan analizar...
      Si quieres trabajar con esas 50 columnas (50 campos) en una sola, no te queda más remedio que Unirlas con una función CONCATENAR o mediante macros empleando una instrucción UNION, para cada Libro.
      Es difícil saber la mejor opción sin ver con qué tipo de datos trabajas...
      Prueba con CONCATENAR y comentas.

      Saludso

      Eliminar
  5. Ismael, es un gusto saludarte, soy de Chile y quería preguntarte si es posible consolidar la facturación que se ha emitido en excel esto es una hoja una factura por lo que hablamos de 200 hojas ,en una sola hoja en la que las liste por numero de factura, nombre, fecha y valor neto.
    de antemano muchas gracias

    Antonio Luis

    ResponderEliminar
    Respuestas
    1. Hola Antonio,
      ese trabajo requeriría un proceso de programación (una macro) que recupere la información deseada de cada hoja (de cada factura) en una hoja diferentes 'Resumen'.
      Debería tener un bucle (un proceso FOR EACH..IN ...NEXT) que recorra cada una de las 200 hojas(facturas) y recopile los dtos necesitados (factura, nombre, fecha y valor neto), e ir pegando esos valores de celda en la hoja 'Resumen', un registro tras otro...

      Habría que saber dónde, en qué celdas, están esos datos (factura, nombre, fecha y valor neto) para identificarlos correctamente.

      Saludos cordiales

      Eliminar
  6. Estimado Ismael, Muy agradecido por tu respuesta,sin embargo,por lo que dices, me doy cuenta que yo no tendría la suficiente pericia para crear esa macro, no me queda mas que seguir recolectando la información 1 por 1,sin embargo por lo menos se que se podría hacer.

    Un abrazo.

    Antonio Luis

    ResponderEliminar
    Respuestas
    1. Hola antonio,
      si te parece lee primero las normas de uso, y si te interesa, envíame los ficheros de trabajo al correo que se indica.
      un cordial saludo!!!

      Eliminar
  7. Estimado Ismael: Te enviare los archivos solo falta que me digas a que correo los envio,ya que no he podido dar con el.
    saludos

    Antonio Luis

    ResponderEliminar