Excel - Crear combinaciones con suma máxima y mínima

 
Vista:
sin imagen de perfil

Crear combinaciones con suma máxima y mínima

Publicado por Jessica (19 intervenciones) el 22/10/2013 10:10:20
Hola!!
Acabo de terminar un curso de Excel 2007 Avanzado y me quiero comer el mundo haciendo programaciones de corte lineal.
Lo difícil lo tengo hecho, pero hay una parte muy laboriosa que es buscar todos los planos posibles para cada tipo de corte. Aquí es donde os necesito!!

Necesitaría un programa o una función en la que se hagan todas las combinaciones posibles de unos números determinados, cuya suma no supere 2850 y no sea inferior a 2720.

Ejemplo:

Los números son:

909 - 809 -709 - 609 - 509 - 459 - 409 - 359 - 309

909 + 909 + 909 = 2727 OK
909 + 909 + 809 = 2627 NO!
904 + 304 + 304 + 304 + 304 + 304 + 304 OK
(...)

Si me solucionáis esto, probablemente salvéis mi puesto de trabajo!!!!!

Muchas gracias y un saludo!
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de JuanC

Crear combinaciones con suma máxima y mínima

Publicado por JuanC (1237 intervenciones) el 22/10/2013 12:44:26
estas son todas las combinaciones de todos los números pero sin repeticiones que cumplen
la condición de máx y mín...

2-upla
-
3-upla
-
4-upla
909+709+609+509 = 2736
909+809+609+409 = 2736
909+809+609+459 = 2786
909+809+609+509 = 2836
909+809+709+309 = 2736
909+809+709+359 = 2786
909+809+709+409 = 2836
-
5-upla
809+609+509+459+359 = 2745
809+609+509+459+409 = 2795
809+709+459+409+359 = 2745
809+709+509+409+309 = 2745
809+709+509+409+359 = 2795
809+709+509+459+309 = 2795
809+709+509+459+359 = 2845
809+709+609+359+309 = 2795
809+709+609+409+309 = 2845
909+609+459+409+359 = 2745
909+609+509+409+309 = 2745
909+609+509+409+359 = 2795
909+609+509+459+309 = 2795
909+609+509+459+359 = 2845
909+709+459+359+309 = 2745
909+709+459+409+309 = 2795
909+709+459+409+359 = 2845
909+709+509+359+309 = 2795
909+709+509+409+309 = 2845
909+809+409+359+309 = 2795
909+809+459+359+309 = 2845
-
6-upla
709+509+459+409+359+309 = 2754
-
7-upla
-
8-upla
-

Saludos, desde Baires, JuanC
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil

Crear combinaciones con suma máxima y mínima

Publicado por Jessica (19 intervenciones) el 22/10/2013 13:00:40
Muchas gracias!

Pero lo necesitaría con repeticiones de números.

Podrías explicarme cómo lo has hecho? Porque este proceso tengo que hacerlo también con otras medidas...

Gracias!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de JuanC

Crear combinaciones con suma máxima y mínima

Publicado por JuanC (1237 intervenciones) el 22/10/2013 15:42:32
con repeticiones es 'casi imposible', necesitaría una súper-computadora y quizás varios
días... además habría que acotar el número de repeticiones de cada número...

lo hice en C++, en excel es demasiado 'lento'...

Saludos, desde Baires, JuanC
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Crear combinaciones con suma máxima y mínima

Publicado por Antoni (2463 intervenciones) el 23/10/2013 13:41:54
Jessica descarga la hoja de calculo y ejecuta la macro, el final de la macro da el tiempo de ejecución y en la hoja1 da el resultado.

En mi maquina tarda la friolera de 136 micro segundos

Como dije una vez, para lo imposible se tarda un poco más que para lo que no se puede hacer.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
Imágen de perfil de JuanC

Crear combinaciones con suma máxima y mínima

Publicado por JuanC (1237 intervenciones) el 23/10/2013 14:21:49
muy bueno Antoni!
podrías explicar algo del algoritmo o simplemente 'salió así'!!
supongo que el secreto es no calcular todas las combinaciones posibles
sino sólo aquellas que 'sirven'...

Saludos, desde Baires, JuanC
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antonio Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Crear combinaciones con suma máxima y mínima

Publicado por Antonio Masana (2463 intervenciones) el 24/10/2013 08:58:48
Ayer tenia prisa y no pude comentar el codigó.

Hay varias formas de hacerlo.

Una de ellas es encadenando FOR, pero el número de bucles es variables en función de los valores y la cantidad de elementos y se crea un código larguísimo muy repetitivo e imposible de mantener.

Otra forma de hacerlo es utilizando la opción de la recursividad de las funciones, que para el que no lo sepa es la llamada de una función a sí misma como si fuese otra función. Esto tiene un límite y es de 64 llamadas para algunas versiones de programas.

Como Funciona:

Lo que hago es comparar todos teniendo en cuenta el valor máximo.

Es decir en teoría debería hacer un número muy grande de combinaciones pero tampoco son tantas porque si el total supera el máximo salta al valor siguiente.

La idea de esta función salió de una pregunta en el foro en no sé qué lenguaje en el que se quería encadenar 200 FOR es decir:

1
2
3
4
5
6
7
8
9
10
11
FOR a001=1 to N
    FOR a002=1 to N
        ...
                      FOR a199=1 to n
                          FOR a200=1 to n
                              ...
                          NEXT
                      NEXT
         ...
    NEXT
NEXT

Aparte del problema de escribir tanto código el límite del lenguaje era de 64 FOR .

La función se comporta como un montón de FOR encadenados y un montón de llamas recursivas en la que el límite de llamadas esta en la cantidad de elementos de la tabla y/o el límite de memoria de la máquina.

A continuación pongo el código comentado (ver fichero adjunto)
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil

Crear combinaciones con suma máxima y mínima

Publicado por Jessica (19 intervenciones) el 24/10/2013 09:26:21
Eres genial!! Moltes gràcies!!!!
Te puedes creer que estaba haciendo todas esas combinaciones a mano???
Estoy feliz!!!!!!!
Pero he intentado cambiarle los números a combinar y la cantidad y no me sale... Por ejemplo, poniendo 804 - 754 - 706 - 704 ... Y poniendo valores 4 ... Me da error ... Y si quisiera ponerle hasta 12 valores?
De momento me has solucionado una buena papeleta y tengo trabajo para el día de hoy!!
Te lo agradezco muchísimo! Pero seguro que para ti es más fácil solucionar lo que falta que para mi...
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil

Crear combinaciones con suma máxima y mínima

Publicado por Jessica (19 intervenciones) el 24/10/2013 09:33:46
Perdón, rectifico! Sí que me deja poner menos valores, lo que pasa es que no me salía la combinación que yo buscaba! Pero Sí funciona!!! Eso sí, necesitaría que pudieran ponerse hasta 12 valores. O hasta 15, para no pillarnos los dedos!
No te imaginas lo que has conseguido facilitarme!!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Crear combinaciones con suma máxima y mínima

Publicado por Antoni Masana (2463 intervenciones) el 24/10/2013 11:54:18
Te envío una nueva macro que permite más elementos (el limite lo marca el número de sumatorios),

Permite hacer con o sin repetición y valida algunos datos.

Ten en cuenta que el tiempo de ejecución de la MACRO es exponencial con respecto al número de elementos.

Para interrumpir una MACRO debería poderse hacer con un CONTROL + PAUSE.

También hay limite y es el número de resultados.

El Excel 2010 tiene 1.048.576 líneas, en versiones anteriores era de 65.535 líneas

Esto no lo controlo y si se da el caso se producirá un error.

Saludos.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil

Crear combinaciones con suma máxima y mínima

Publicado por Jessica (19 intervenciones) el 24/10/2013 12:22:33
No puedo abrir el archivo...
Pero gracias ante todo! Estoy alucinada! Es genial!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil

Crear combinaciones con suma máxima y mínima

Publicado por Jessica (19 intervenciones) el 24/10/2013 13:39:21
Es una pasada! Me resuelve muchos problemas! El campo que se puede cambiar es el de con repetición si/no, no? El de sumatorio no lo puedo cambiar y el de repeticiones tampoco, no?
Cuando tenga acabada mi hoja si quieres te enseño mi triste macro, para lo que lo necesitaba...
De nuevo MIL GRACIAS!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Crear combinaciones con suma máxima y mínima

Publicado por Antoni Masana (2463 intervenciones) el 25/10/2013 08:39:12
He realizado unas pequeñas modificaciones.

Las celdas que puedes modificar son las que tienen el fondo de color Amarillo y Naranja.

Las celdas de fondo de color Gris son informativas.

Me gustaría ver tu macro, pero no creas que es triste, todos tenemos un inicio y un pasado y a cual peor.

Si fuésemos cirujanos algunos operaríamos de almorranas por el agujero de la nariz y con las anos en la espalda.

Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Crear combinaciones con suma máxima y mínima

Publicado por Antoni Masana (2463 intervenciones) el 25/10/2013 09:52:13
La frase correcta es:

Si fuésemos cirujanos algunos operaríamos de almorranas por el agujero de la nariz y con las manos en la espalda
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil

Crear combinaciones con suma máxima y mínima

Publicado por Jessica (19 intervenciones) el 25/10/2013 11:23:10
Mi gozo en un pozo. Todo lo que tu has hecho me va de fábula, pero cuando he ido a aplicar mis conocimientos básicos me entero de que Solver no puede tener tantas celdas cambiantes. Te adjunto el archivo para que le eches un ojo a lo que pretendía hacer. Sólo era el principio, pero ahora creo que se tendrá que quedar así...
Te explico: en la primera hoja que pone 580 CON, usando Solver me optimiza, en función de los planos que yo he puesto gracias a tu ayuda, las cantidades de piezas que yo introduzco en "Cantidad". Hasta ahí genial. El problema viene con la hoja 330CON. Las celdas cambiantes son 840 y Solver no me lo resuelve. Y tengo que hacer otra hoja en la que el resultado será superior!
Existe la manera de hacerlo o definitivamente tiro la toalla??
Por cierto, la macro la tenía hecha pero la borré para empezar de nuevo cuando todo estuviera optimizado.
Gracias y un saludo!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Crear combinaciones con suma máxima y mínima

Publicado por Antoni Masana (2463 intervenciones) el 25/10/2013 12:07:03
No conozco esta herramienta.

He encontrado esta página buncando Restricciones Solver Excel:

http://support.microsoft.com/kb/75714/es

Un resumen de lo que he encontrado.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
El número máximo de celdas ajustables y las restricciones que se puede
utilizar un modelo  de Solver se enumeran en la tabla siguiente.
 
 
 
   Microsoft Excel   Adjustable cells   Constraints
   version
   ---------------------------------------------------
 
   3.x               200                299
   4.x               200                500
   5.0               200                500
   7.0               200                500
   97                200                unlimited/500*
   98                200                unlimited/500*
   2000              200                unlimited.500*
 
 
* Si está trabajando con un problema lineal, el número máximo de
  restricciones que puede utilizar es ilimitado. Si está trabajando con un
  problema no lineal,   el número máximo de restricciones que puede utilizar
  es de 500.


No se si esto responde a tu primera pregunta.

Se puede hacer hacer de otra forma pero antes deberia saber que hace el Solver para hacer una macro que lo haga lo mismo salvando los limites.

No tires la toalla.

Yo siempre pienso en aquella frase de: "Lo consiguieron porque no sabían que era imposible."

Saludos.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil

Crear combinaciones con suma máxima y mínima

Publicado por Jessica (19 intervenciones) el 25/10/2013 12:55:24
Ya... ya he visto que sólo acepta 200 celdas... Por tanto mal lo tenemos... Solver hace una optimización lineal teniendo en cuenta una serie de restricciones dadas. En mi hoja, por ejemplo, le digo que la celda objetivo sea la suma TOTAL DE TIRAS. Esa cantidad debe ser MÍNIMA. Para ello me compara la columna Resultado con Cantidad, entre las cuales, la columna Resultado debe ser mayor o igual que la de Cantidad, que es lo que necesito. Siempre debe ajustarse lo máximo posible, minimizando la merma. Y estas restricciones son las que le pongo al Solver.
Pero, claro, me gustaría saber hacerlo a mi, porque tengo que hacerlo, además de para esta hoja, para unas 10 hojas más.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil

Crear combinaciones con suma máxima y mínima

Publicado por Jessica (19 intervenciones) el 25/10/2013 13:34:29
Sé que no es imposible. Es mi meta. Me faltan conocimientos. Pero créeme cuando te digo que me tiene totalmente obsesionada. No duermo por las noches pensando en resolver esta hoja! El diseño está en mi cabeza y sé que se puede hacer. Pero hoy venía feliz a seguir con mi trabajo y de pronto el castillo de naipes se ha caído por culpa de la limitación de Solver, que yo pensaba que era ideal para mi. Igual me complico demasiado y hay una forma más sencilla de hacer eso mismo que pretendo, pero lo dudo. No ando lejos, pero no tengo recursos. De momento seguiré perdiendo el tiempo con el diseño final, hasta que se me vuleva a iluminar la bombilla. Pero por lo pronto la bombilla está fundida!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil

Crear combinaciones con suma máxima y mínima

Publicado por Jessica (19 intervenciones) el 28/10/2013 09:21:50
Pasado el fin de semana, he buscado y buscado y no he encontrado nada... Pedí en un foro el código de Solver pero no me lo han mandado (aunque lo tenían), y si lo recibiera, para mi es como leer chino!
La única alternativa que he encontrado es de pago y dudo que mi jefe se quiera dejar tanto dinero ahora en eso...
Encontré el archivo que adjunto, que en esencia es lo que necesito, pero claro con otras medidas. No sé si yo me estaba complicando demasiado con el tema de los planos, y hay alguna forma de hacerlo más fácil, como en esta macro...
Dame ideas! Y gracias!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Crear combinaciones con suma máxima y mínima

Publicado por ppg (8 intervenciones) el 05/11/2013 20:59:23
Y si quisieramos hacer las combinaciones de 10 elementos tomados de dos en dos, qué deberia de cambiar.
Gracias máquina
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar