Permutar aleatoriamente datos entre columnas y copiar en otra ubicación

[Salva Roselló]

Hola a todos,

Para una simulación docente utilizo un archivo de Excel que contiene columnas pareadas de datos que forman grupos. Adjunto archivo con datos y código para se entienda mejor lo que tengo y lo que necesito.

Con esos datos quiero:

·       Que en determinados puntos de cada par de columnas (mismo color) se permuten datos de forma aleatoria.

·       Que el resultado el proceso de permuta se copie en una nueva ubicación.

·       Que el proceso se realice para todos los grupos de datos hasta acabar.

Que es lo que tengo:

·       He escrito una macro que realiza correctamente todo el proceso (está en el archivo y contiene comentarios en cada paso indicando lo que se hace).

·       EL PROBLEMA es que cuando se trabaja con muchos grupos de datos (cientos o miles) el proceso se ralentiza bastante.

Por ello:

·       Os agradecería enormemente si alguien pudiera sugerir una manera de escribir la macro (o mejor proporcionar el código) de modo que, aunque se utilicen grandes cantidades de grupos de datos, el proceso se realice de forma más rápida que ahora.

Contenido del archivo xlsm que adjunto:

·       4 hojas. En la primera hay 4 grupos de datos. En la segunda se añade a los datos anteriores el resultado esperado. La tercera (20 Grupos) y la cuarta (100 Grupos) son datos adicionales por si se quiere probar con mayores cantidades de grupos de datos.

·       En cada hoja, en la parte superior, hay dos botones. El de generar subgrupos permite ejecutar la macro y obtener el resultado. El de borrar subgrupos permite reutilizar la hoja.

Muchas gracias de antemano por vuestras sugerencias. Espero que a alguien le pique la curiosidad con este tema y suba sugerencias o soluciones.

Saludos.

PermutaDatosGrupos_D1.2.xlsm.zip

[Abraham Valencia]

Hola

Humm, así, sin cambiar mucha cosa, podrías vaciar el portapapeles en algunos procesos antes y/o después de cada copia; asimismo, supongo que por una cuestión estética cambias el mensaje de la barra de estado en cada bucle, pero sinceramente eso solo demora un poco más cada proceso, mejor un solo mensaje inicial y uno final; por último, al ser bucles y usar varias variables de objeto, es una ayuda vaciar cada vez las variables respectivas "Set variable = Nothing".

 

[Salva Roselló]

Hola Abraham. Gracias por la sugerencia. Lo pruebo y comento la mejora.

Saludos.

[Salva Roselló]

Hola Abraham. He probado lo que sugerías (con una hoja con 1000 grupos) y la verdad es que no se observa mucha mejora (apenas 20 seg par una ejecución de 2 min y 30 seg). Lo que mejor ha ido ha sido limpiar el portapapeles  (11 seg) y quitar el mensaje de la barra de estado (9 seg). Lo del mensaje de la barra de estado no lo pongo por estética sino para saber cómo va el proceso porque a veces hay muchos grupos.

He estado pensando que igual se puede acelerar el proceso si antes de aplicar los bucles paso los datos a una colección y trabajo desde allí y luego genero una salida a la hoja de resultados (para que queden como se muestran en el archivo). He leído que de esa forma VBA trabaja mucho más rápido, pero no tengo experiencia con esto y antes de reescribir el código me gustaría contar con alguna opinión experta sobre este particular.  ¿Qué opináis? ¿Cómo lo haríais?

Muchas gracias por vuestra atención y ayuda.

Saludos.

[Abraham Valencia]

Hola nuevamente

En general las macros que copian y pegan, que es lo que finalmente haces, tienen a no ser tan rápidas y lo que tú haces es eso, copiar y pegar solo que en distintas posiciones. El trabajar en memoria, más aún de la forma en que tú tienes los datos, la verdad implica varios cambios que ameritan un tiempo que, en mi caso, yo no tengo (no olvides que los foros son de participación gratuita y damos tiempos libres). Algunas alternativas: 

- No uses tantos Randomize, no es necesario.

- Cambia esto así:

Cells(3, poscoldatosgenerados1(0)) = "Subgrupo"
Cells(3, poscoldatosgenerados2(0)) = "Subgrupo"

Finaliza, solo por si acaso, con un "DoEvents", siento que a pesar de que la macro corre totalmente, se demora un par de segundo en recuperar el control para Excel (aunque a veces eso sucede porque los archivos que copian/pegan mucho van corrompiéndose un poquito).

Ah, por último, la velocidad depende mucho de las caracteristicas de tu computadora, en mi caso 100 grupos los hace en 18/19 segundos.

Saludos.

[Salva Roselló]

Hola Abraham,

Muchas gracias por tus aportes y por tu tiempo, soy consciente que la ayuda que se presta en el foro es desinteresada y eso hace que sea aún más de agradecer. También creo que la forma en que tengo escrito el macro no permite demasiadas mejoras por eso me planteaba el usar colecciones. Se que esto implica reescribir totalmente el macro y trabajar de otra forma y que eso llevará tiempo. Simplemente, preguntaba por si los que tenéis más experiencia pensabais que ese trabajo extra valdría la pena. En caso contrario ni si quiera lo intentaría ya que tengo que aprender cómo se trabaja con colecciones y tampoco sé si los procesos de permuta y aleatorización que uso se pueden utilizar al trabajar con colecciones. Respecto de los Randomize, no puedo prescindir de ninguno de ellos ya que, como comentaba en la descripción inicial, intento hacer una simulación docente de procesos naturales para mis estudiantes y estas son así, si prescindiera de alguno de ellos no se reflejaría adecuadamente el proceso natural que quiero simular.

En cualquier caso, cualquier idea será bienvenida y gracias de nuevo por tu interés.

Saludos.

[Salva Roselló]

Hola @Abraham Valencia y resto de compañeros,

he estado buscando soluciones y creo que casi lo he resuelto. Leí que trabajando con rangos y celdas el trabajo se ralentiza mucho comparado con cuando se utilizan arrays. En primer lugar he modificado los datos rellenando las celdas vacías con 0 y aplicando formato condicional para que no se vean. He modificado el código para que lea los datos a un array y trabaje con él para hacer las permutaciones y asignaciones aleatorias de datos generados a subgrupos. El código finaliza pasando los datos de los subgrupos (arrays) a los rangos especificados para subgrupos en la hoja de Excel y aplicando formato condicional para que se vean igual que los originales. Ahora el macro es 50 veces más rápido que el original (para la hoja de 100 grupos). Sin embargo, aún queda un pequeño bug. La permutaciones y asignaciones aleatorias de datos las realiza correctamente para el primer grupo, pero en la siguientes iteraciones ni permuta ni asigna aleatoriamente en subgrupos. No veo donde está el error en el código. Os agradecería que, si alguno ve donde está el problema, me lo indicara junto con alguna sugerencia de solución.

Muchas gracias de antemano.

Saludos.PermutaDatosGrupos_D_arr6.zip

[Salva Roselló]

Hola a todos de nuevo,

ya he encontrado el fallo y lo he resuelto. Era un problema con los limites de manejo de datos  en el array inicial al ir pasando de un grupo a otro. Estaban mal definidos. Ahora ya funciona correctamente. Lo comento para que no dediquéis más tiempo a este tema. Ya esta resuelto y como decía en el mensaje anterior ahora el macro es 50 veces más rápido que el original. Por curiosidad lo he probado con una hoja con 5000 grupos (más de 200000 filas implicadas). Con el macro original le cuesta unas 4 horas de completar, con el nuevo unos 5 minutos. Para las simulaciones que quiero hacer es un cambio brutal. Os dejo el archivo por si alguien tiene curiosidad.

Muchas gracias por vuestro tiempo y atención. PermutaDatosGrupos_D_arr_RESUELTO.zip