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

22 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
  8. Cómo realizó un fórmula para un inventario de ventas que si escribo en una celda un código que está en.otra celda me aparezcan los valores de otra celda ejemplo a son las celdas que quiero me aparezcan el resultado la.celda b son los código y vamos des b1hasta b500 y c son la celdas de los valores en dinero y van desde c1 hasta c500

    ResponderEliminar
    Respuestas
    1. Hola, que tal estás?
      espero te encuentres bien.

      Una fórmula que se adapta a lo que indicas sería BUSCARV.
      Deberías aplicarla sobre la matriz de búsqueda B1:C500, por ejemplo, en G1 escribes un código y en H1 escribes:
      =BUSCARV(G1;B1:C500;2;0)
      Saludos cordiales

      Eliminar
  9. Hola, mira yo necesito simplemente que de unas cuantas pestañas (4), se me consolide por día un resumen de recaudaciones (diario, por fecha del día) que ellos ingresarán en sus correspondientes pestañas, el problema es que es un archivo compartido, y entiendo debe ser algo simple para que funcione. Por favor tu ayuda, te lo agradecería millón.

    ResponderEliminar
    Respuestas
    1. Hola Andrea,
      el problema de trabajar con Libros compartidos de Excel es que se limitan bastantes acciones... por ejemplo, emplear Tablas dinámicas (que podría ser una solución a tu problema).
      Lo más sencillo sería emplear la herramienta Consolidar de la ficha Datos... el problema que tendrás que lanzar cada vez la herramienta...
      Otras opciones, al ser solo 4 hojas sería emplear funciones tipo SUMAR.SI.CONJUNTO
      Saludos

      Eliminar
  10. Hola buenas noches,

    quisiera ver si me puedes ayudar.

    Tengo un archivo donde tiene una hoja con 16.392 filas, la cual tengo que separar por la columna A en distintas hojas los valores que se encuentran, es decir un total de 295.

    cual es la forma para realizar esto de manera automática.

    Saludos Cordiales.

    ResponderEliminar
  11. Hola buen dia necesito ayuda, tengo un libro de excel donde quiero llevar el control de los ingresos diarios, son 140 filas las que tengo con cada uno de los registro, en los cuales esta, clave, cuenta y descripción, yo quiero, usar una sola fila para registrar los ingresos de los diferentes rubros, acción que realizó con la función, buscar, pero no puedo hacer que me registre en hojas diferentes los registros que realizó en el dia, para el cual tengo el formato así; Fecha, clave, cuenta, Descripción y Valor.
    agradecere me pueda ayudar con esto

    ResponderEliminar
    Respuestas
    1. Hola Edin,
      la alternativa para completar los diferentes registros de cada día como planteas, esto es completando todo desde una misma fila, sería aplicando macros...
      o bien empleando esta funcionalidad de Formulario de datos:
      v

      Espero te oriente
      Saludos

      Eliminar
  12. Hola Ismael, quisiera que me ayudes, quisiera copiar datos de una hoja a varias hojas del mismo libro, pero que se peguen solo los valores, tengo el sgte codigo
    Sub copiardatos()
    Sheets("Hoja6").Range("B6:B15").Copy Destination:=Sheets("1").Range("D16")
    Sheets("Hoja6").Range("B18:B27").Copy Destination:=Sheets("2").Range("D16") Sheets("Hoja6").Range("B30:B39").Copy Destination:=Sheets("3").Range("D16")
    Sheets("Hoja6").Range("B42:B51").Copy Destination:=Sheets("4").Range("D16")
    Sheets("Hoja6").Range("B54:B63").Copy Destination:=Sheets("5").Range("D16")
    End Sub
    El problema es que me copia todo y solo quiero copiar los valores.
    Saludos,gracias.

    ResponderEliminar
    Respuestas
    1. Hola Erick,
      emplear el método .Copy como lo haces es equivalentes a realizar un copiar y pegar normal, y por tanto, pega todo(fórmulas, formatos, etc).
      Si solo quieres pegar valores tendrás que emplear, por ejemplo, un PasteSpecial, echa un vistazo a este post:
      http://excelforo.blogspot.com.es/2011/02/vba-formas-de-copiar-rangos-o-celdas.html
      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.