Umbral de sumas iguales a valor objetivo
Resuelto
ask Answer
-
Jeremie -
Jeremie -
Hola,
Usando Microsoft Excel 2007 quiero hacer lo siguiente:
Tengo una base de datos de valores (números) y tengo un valor objetivo. Quiero encontrar todas las combinaciones de sumas de los números posibles (a partir de la base de datos) que igualen el valor objetivo.
¿Es posible?
Configuración: Windows 7 / Safari 535.11
Usando Microsoft Excel 2007 quiero hacer lo siguiente:
Tengo una base de datos de valores (números) y tengo un valor objetivo. Quiero encontrar todas las combinaciones de sumas de los números posibles (a partir de la base de datos) que igualen el valor objetivo.
¿Es posible?
Configuración: Windows 7 / Safari 535.11
13 respuestas
Hola,
Mira si este programa te conviene:
https://www.cjoint.com/?BEushEb54QO
Las explicaciones de los parámetros están en comentarios de las celdas.
eric
Mira si este programa te conviene:
https://www.cjoint.com/?BEushEb54QO
Las explicaciones de los parámetros están en comentarios de las celdas.
eric
Un millón de gracias, Eric. Es muy útil y magnífico.
Solo he notado que el programa no funciona correctamente si:
1- Introduzco en la columna "valor" y/o en la celda del valor objetivo un número negativo.
2- Inserto columnas antes de la columna "valor" (aparece un mensaje de "Incompatibilidad de tipo").
¿Por favor, existe alguna solución para estos casos?
Gracias
Solo he notado que el programa no funciona correctamente si:
1- Introduzco en la columna "valor" y/o en la celda del valor objetivo un número negativo.
2- Inserto columnas antes de la columna "valor" (aparece un mensaje de "Incompatibilidad de tipo").
¿Por favor, existe alguna solución para estos casos?
Gracias
En principio, el archivo está diseñado para pegar los valores allí. Si insertas o eliminas filas o columnas, la macro ya no se orienta. Hay que modificarla en consecuencia.
Y es cierto que me había planteado recuperar sumas de números positivos (volver a encontrar las filas de una factura agrupada cuando solo tienes el total)
¿Quieres explicar tu problemática o ver si hay otros casos particulares o la posibilidad de una artimaña?
Eric
PD: después de la prueba parece responder bien con números negativos
Y es cierto que me había planteado recuperar sumas de números positivos (volver a encontrar las filas de una factura agrupada cuando solo tienes el total)
¿Quieres explicar tu problemática o ver si hay otros casos particulares o la posibilidad de una artimaña?
Eric
PD: después de la prueba parece responder bien con números negativos
En realidad, la necesidad de una aplicación así nace porque trabajo en el ámbito financiero.
Tengo como base de datos un balance financiero (con al menos 3 columnas: Nº de cuenta, concepto y Monto). La empresa me entrega estados financieros en los que cada rubro posee un monto (que en realidad es la suma de un conjunto de cuentas del balance). Como el número de filas del balance suele ser muy alto, pierdo días buscando los montos del balance que coinciden con los rubros de los estados financieros. Los montos del balance pueden ser positivos y negativos.
¿Qué opinas?
Tengo como base de datos un balance financiero (con al menos 3 columnas: Nº de cuenta, concepto y Monto). La empresa me entrega estados financieros en los que cada rubro posee un monto (que en realidad es la suma de un conjunto de cuentas del balance). Como el número de filas del balance suele ser muy alto, pierdo días buscando los montos del balance que coinciden con los rubros de los estados financieros. Los montos del balance pueden ser positivos y negativos.
¿Qué opinas?
¿Y entonces necesitarías una columna adicional, ¿verdad?
Y acabo de ver que hablaste de la celda objetivo en negativo.
¿Es normal que no funcione?
Para eliminar miles (incluso cientos de miles) de pruebas salgo de un bucle en cuanto la suma se excede.
Eliminar esa optimización daría tiempos de respuesta inaceptables.
Hace falta invertir tus números en este caso:
- ingresar -1 en una celda y copiarla.
- seleccionar el rango y 'pegado especial multiplicación'
eric
Y acabo de ver que hablaste de la celda objetivo en negativo.
¿Es normal que no funcione?
Para eliminar miles (incluso cientos de miles) de pruebas salgo de un bucle en cuanto la suma se excede.
Eliminar esa optimización daría tiempos de respuesta inaceptables.
Hace falta invertir tus números en este caso:
- ingresar -1 en una celda y copiarla.
- seleccionar el rango y 'pegado especial multiplicación'
eric
Hola,
Aquí con 5 columnas.
Añadido:
- tratamiento de una suma objetivo negativa (sin manipulación manual).
- un doble clic en una solución filtra los valores concernés.
- un doble clic en un filtro activo lo anula.
https://www.cjoint.com/?BEvjNdp8qA8
Normalmente depurado pero si puedes probar a fondo y señalar los problemas más tarde...
eric
Aquí con 5 columnas.
Añadido:
- tratamiento de una suma objetivo negativa (sin manipulación manual).
- un doble clic en una solución filtra los valores concernés.
- un doble clic en un filtro activo lo anula.
https://www.cjoint.com/?BEvjNdp8qA8
Normalmente depurado pero si puedes probar a fondo y señalar los problemas más tarde...
eric
Hola,
aquí tienes: https://www.cjoint.com/c/CBmqwkGmHcg
eric
--
Nunca responderás a un mp no solicitado...
Bien, ya está.
aquí tienes: https://www.cjoint.com/c/CBmqwkGmHcg
eric
--
Nunca responderás a un mp no solicitado...
Bien, ya está.
Hola,
Pero imaginemos que tenemos una lista de números bastante grandes y precisos, con varios dígitos después de la coma, eso limitaría mucho el número de combinaciones posibles...
No, eso disminuye eventualmente el número de soluciones.
Pero el número de combinaciones (todas deben explorarse) es exponencial. Si realmente tienes muchos valores, prepárate para tiempos de procesamiento largos, y incluso muy, muy largos.
De todas formas me ha parecido una evolución interesante, lo intenté...
Si pudieras darme un feedback de si funciona bien.
Y por curiosidad el número de valores así como el tiempo empleado.
https://www.cjoint.com/c/CBom4p3UbC6
eric
--
Jamás responderás a un mp no solicitado...
Bueno, ya está hecho.
Pero imaginemos que tenemos una lista de números bastante grandes y precisos, con varios dígitos después de la coma, eso limitaría mucho el número de combinaciones posibles...
No, eso disminuye eventualmente el número de soluciones.
Pero el número de combinaciones (todas deben explorarse) es exponencial. Si realmente tienes muchos valores, prepárate para tiempos de procesamiento largos, y incluso muy, muy largos.
De todas formas me ha parecido una evolución interesante, lo intenté...
Si pudieras darme un feedback de si funciona bien.
Y por curiosidad el número de valores así como el tiempo empleado.
https://www.cjoint.com/c/CBom4p3UbC6
eric
--
Jamás responderás a un mp no solicitado...
Bueno, ya está hecho.
Buenas noches,
En realidad no son fallos, es más un problema de retomar el control de la macro durante cálculos largos.
Con 65 valores puede ser muy largo, varias horas... Hay que explorar todas las combinaciones, varios millones.
Si colocas nbTermesmini=7, excluyes todas las combinaciones de las sumas de 2 a 6 términos, podrás ver que la solución se encuentra bastante rápido (en 30s, sin esperar horas).
He cambiado un poco la lógica y añadido un formulario que da elementos para ver el avance de la macro. También permite hacer pausas o detener el análisis.
https://www.cjoint.com/c/DCrxbr4PYST
eric
Además de agradecer (sí, sí, ¡se puede hacer! ), piensa en marcar como resuelto cuando corresponda (en la parte superior junto al título).
Gracias
En realidad no son fallos, es más un problema de retomar el control de la macro durante cálculos largos.
Con 65 valores puede ser muy largo, varias horas... Hay que explorar todas las combinaciones, varios millones.
Si colocas nbTermesmini=7, excluyes todas las combinaciones de las sumas de 2 a 6 términos, podrás ver que la solución se encuentra bastante rápido (en 30s, sin esperar horas).
He cambiado un poco la lógica y añadido un formulario que da elementos para ver el avance de la macro. También permite hacer pausas o detener el análisis.
https://www.cjoint.com/c/DCrxbr4PYST
eric
Además de agradecer (sí, sí, ¡se puede hacer! ), piensa en marcar como resuelto cuando corresponda (en la parte superior junto al título).
Gracias
Hola Eric,
Muchas gracias por tu trabajo, ¡me salva la vida! Sin embargo, mi problema no está totalmente resuelto: tengo una lista de números (alrededor de 350) y quiero hacer combinaciones para llegar a mi valor objetivo de 6. ¿Cómo es posible volver a ejecutar el cálculo ignorando los valores tomados en la primera combinación?
Ejemplo:
Ingreso de valores
1
3
5
2
6
4
1
Mi valor objetivo es 8
El primer resultado que obtengo es la combinación de los valores 5 y 3
Desearía además que el programa siga funcionando para que todas mis valores sean asociadas:
2º resultado: 6+2
3º resultado: 4+1+1
??
Espero que puedas aclararme este punto, ¡si no, gracias de todas formas!
Fanny
Hola,
Vuelvo a este tema que me interesa mucho.
He retomado tu macro desde el inicio, para intentar modificarla y aplicarla a mi problema.
Mi problema: tengo una lista de series de números que van del 1 al 33, y deseo encontrar las mejores combinaciones posibles para que sean iguales a 33 o lo más cercano posible a 33. Sabemos que cada número debe usarse solo una vez.
Espero haber sido claro en mi explicación, y ¿cómo podemos modificar para que los números ya no estén en euros, sino que sean enteros?
Gracias de antemano.
Vuelvo a este tema que me interesa mucho.
He retomado tu macro desde el inicio, para intentar modificarla y aplicarla a mi problema.
Mi problema: tengo una lista de series de números que van del 1 al 33, y deseo encontrar las mejores combinaciones posibles para que sean iguales a 33 o lo más cercano posible a 33. Sabemos que cada número debe usarse solo una vez.
Espero haber sido claro en mi explicación, y ¿cómo podemos modificar para que los números ya no estén en euros, sino que sean enteros?
Gracias de antemano.
Hola,
¿cómo podemos modificar que los números ya no sean en euros, sino enteros?
Solo tienes que modificar el formato de las celdas (clic derecho sobre las celdas).
Por otra parte, en el código deja las variables tipificadas como Currency, de lo contrario podrías pasar por alto la solución exacta.
Este programa no busca la solución más cercana, ese no es su objetivo...
eric
¡Muchas gracias!
Pregunta pequeña: ¿sería posible permitir la combinación de varios números negativos y varios números positivos para alcanzar un resultado dado?
Cuando se combina un negativo y un positivo (por ejemplo: para encontrar -5, el sistema combina "-6" y "1"), funciona.
Pero si se intenta con varios negativos y varios positivos (por ejemplo: para encontrar -17, el sistema combinaría "-15", "3", "-7" y "2"=??), no funciona.
¡AYUDA!
Pregunta pequeña: ¿sería posible permitir la combinación de varios números negativos y varios números positivos para alcanzar un resultado dado?
Cuando se combina un negativo y un positivo (por ejemplo: para encontrar -5, el sistema combina "-6" y "1"), funciona.
Pero si se intenta con varios negativos y varios positivos (por ejemplo: para encontrar -17, el sistema combinaría "-15", "3", "-7" y "2"=??), no funciona.
¡AYUDA!
Hola,
creo que no leíste el post 5.
Para eliminar miles (incluso cientos de miles) de pruebas salgo de un bucle en cuanto la suma se sobrepasa.
Eliminar esta optimización daría tiempos de respuesta insoportables.
En tu caso te sugiero añadir 20 a todos tus números, incluido el total a alcanzar, y hacer la operación inversa al final.
eric
creo que no leíste el post 5.
Para eliminar miles (incluso cientos de miles) de pruebas salgo de un bucle en cuanto la suma se sobrepasa.
Eliminar esta optimización daría tiempos de respuesta insoportables.
En tu caso te sugiero añadir 20 a todos tus números, incluido el total a alcanzar, y hacer la operación inversa al final.
eric
Sí, pero imaginemos que tengamos una lista de números bastante altos y precisos, con varios dígitos después de la coma, eso limitaría mucho la cantidad de combinaciones posibles... ¿la idea aún no es viable? 8-)
gracias por el trabajo eriic tu archivo me ha ayudado mucho hice una prueba usando una base de datos compuesta por 65 montos la meta contiene 7 montos pero después de 3 pruebas consecutivas de 3 minutos no encuentro solución
¿por qué no hay solución si mi base es pequeña?
me gustaría obtener una respuesta, gracias.
¿por qué no hay solución si mi base es pequeña?
me gustaría obtener una respuesta, gracias.
Hola (generalmente empezamos por eso...),
Verifica que nbTermesMaxi en H4 tenga un valor suficiente antes de ejecutar la macro.
Por seguridad colócala en 65.
Y no marques 'Eliminar duplicados'. Hay que hacerlo con conocimiento de causa, eso elimina soluciones.
Ahora, ¿estás 100% seguro de que siempre hay una solución? Tal vez falten facturas.
Por curiosidad, pega aquí tus montos y el total a alcanzar.
Y si conoces la combinación que alcanza el total, proporciona también esa.
eric
Verifica que nbTermesMaxi en H4 tenga un valor suficiente antes de ejecutar la macro.
Por seguridad colócala en 65.
Y no marques 'Eliminar duplicados'. Hay que hacerlo con conocimiento de causa, eso elimina soluciones.
Ahora, ¿estás 100% seguro de que siempre hay una solución? Tal vez falten facturas.
Por curiosidad, pega aquí tus montos y el total a alcanzar.
Y si conoces la combinación que alcanza el total, proporciona también esa.
eric
Hola Eric, respecto a tu pregunta es sí, he respetado las reglas y conozco la combinación; no es más que la suma de siete montos, una fórmula muy simple (una forma de hacer una prueba).
Buenas noches,
Leo esta publicación varios meses después. He recuperado el archivo y he visto el código (Bravo ERIC, de verdad)
Me gustaría saber si es posible hacer este análisis en una tabla con varias columnas?
Explicaciones (o al menos intento :-):
El rango A4:A100 contiene 96 valores y un valor en A2 igual a la suma de algunos de los valores de la columna A
El rango B4:B100 contiene 96 valores y un valor en B2 igual a la suma de algunos de los valores de la columna B
El rango C4:C100 contiene 96 valores y un valor en C2 igual a la suma de algunos de los valores de la columna C
¿Se pueden buscar las filas capaces de satisfacer a la vez los valores de A2, B2, C2?
1°) Los valores son enteros positivos o negativos
2°) El número máximo de filas para llegar a la suma suele ser ≤3
Si alguien me lee y sabe responder a eso, muchas gracias de antemano
Cordialmente
Leo esta publicación varios meses después. He recuperado el archivo y he visto el código (Bravo ERIC, de verdad)
Me gustaría saber si es posible hacer este análisis en una tabla con varias columnas?
Explicaciones (o al menos intento :-):
El rango A4:A100 contiene 96 valores y un valor en A2 igual a la suma de algunos de los valores de la columna A
El rango B4:B100 contiene 96 valores y un valor en B2 igual a la suma de algunos de los valores de la columna B
El rango C4:C100 contiene 96 valores y un valor en C2 igual a la suma de algunos de los valores de la columna C
¿Se pueden buscar las filas capaces de satisfacer a la vez los valores de A2, B2, C2?
1°) Los valores son enteros positivos o negativos
2°) El número máximo de filas para llegar a la suma suele ser ≤3
Si alguien me lee y sabe responder a eso, muchas gracias de antemano
Cordialmente
Como el tema es antiguo te pongo la última versión por si la error se haya corregido.
v4.3.5.3: https://mon-partage.fr/f/WoZQ8D2P/
2h no es nada. Con 700 líneas habría dicho varios días... ;-)
Tengo una idea para pulverizar los tiempos, pero no antes de varias semanas.
No tengo mucho tiempo ni ganas de dedicarle horas en esta temporada.
eric