Excel o VB, como repartir una herencia
Quisiera plantear un problema: Tengo una base de datos en una o varias columnas. Cada celda contiene un valor alfanumérico único y se corresponde con una celda con un valor determinado.
Por otro lado tenemos varias celdas cada una con un valor asignado. Se trata de repartir los objetos de la base de datos entre las otras celdas consiguiendo que su valor total se corresponda con el valor asignado a cada una de esas celdas.
El problema podría ser el reparto de una herencia: tengo un listado de objetos, todos únicos e identificados con su código, cada uno con su precio, que podría ser repetido o no.
Por otro lado, tengo tres cajas donde quiero repartir esos objetos. Corresponden a tres (o más) herederos, y se ha calculado previamente la proporción de cada uno y pueden ser iguales o no.
He sumado los valores de los objetos y calculado el importe total que corresponde a cada caja.
El problema es que los objetos son únicos y se debe calcular cuales se ponen en cada caja de manera que se consiga un total dado. (O cercano)
Espero que se entienda la exposición, y que a alguien se le ocurra una solución. Sin haberlo pensado me ha salido un pareado ;-)
1 respuesta
Me parece que es un problema de combinatoria, y dependiendo del número de elementos la cantidad de combinaciones puede llegar a ser enorme, concretamente 2^número de elementos-1
Yo escribí hace años una función para encontrar de entre un conjunto de sumandos los que arrojan determinada suma, pero si son muchos sumandos el cálculo se puede demorar un buen rato, por lo que en el código dicho número está limitado a 28 (aunque lógicamente esto se puede ampliar modificando el código). Para hacernos una idea de lo que estamos hablando, 28 sumandos representan 268.435.455 combinaciones
He preparado un libro de ejemplo en cuya Hoja1!A1:A28 he puesto 28 números elegidos aleatoriamente entre 1 y 100 usando =ALEATORIO.ENTRE(1;100). En B1:B3 están los 3 porcentajes (he puesto 50%, 30% y 20%) de reparto, en C1:C3 las cantidades que corresponden a dichos porcentajes, y en D1 la fórmula que calcula qué sumandos devuelven el resultado más cercano al primer importe.
En la columna E se eliminan los sumandos ya escogidos, y de entre los que quedan se eligen los que se aproximen más al 2º importe. Finalmente, en la columna G aparecen los sumandos que quedan, que lógicamente serán la mejor aproximación al 3er importe.
Por la forma de trabajar de la función, en condiciones normales debería tardar menos si se colocan los sumandos en orden ascendente, pero en este caso quizás esto no sea conveniente porque la función tendería a elegir para el primer porcentaje los objetos más valiosos, y de entre los que quedasen elegiría de nuevo los más valiosos para el segundo importe (aunque obviamente si se pone en primer lugar el menor porcentaje los objetos más valiosos irían a parar a él).
Los rangos A1:A28 y E1:E28 tienen un formato condicional para que aparezcan en color amarillo las celdas que ya han sido escogidas por la función.
El libro de ejemplo está aqui: http://www.jrgc.es/ejemplos/ejemplo_20141023a.xlsm pero aviso que el cálculo puede tomarse su tiempo (en el PC donde estoy ha tardado unos 45 segundos).
¡Gracias! La voy a estudiar despacio. En cuanto a que tarde 45 segundos, no me parece mucho tiempo. Obviamente no es una función para usar todos los días. Un saludo y gracias otra vez
Es que son 45 segundos con esos sumandos, pero puede ser más o menos dependiendo de las cantidades.
Por ejemplo, si en A1 se sustituye el 69 por 66 los cálculos son casi instantáneos, y además encuentra los importes exactos. Es lo malo (y lo bueno) de trabajar con combinaciones.
