buscar valores que sumen lo más próximo a un valor buscado

6 envíos / 0 nuevos
Último envío
roodrigo
Imagen de roodrigo
Offline
última acción: Hace 1 año 8 meses
alta: 03/05/2017 - 14:42
Puntos: 25
buscar valores que sumen lo más próximo a un valor buscado

muy buenas tardes, junto con saludar necesito tu ayuda :
Necesito crear una forma de encontrar dentro de un rango de valores, el valor
mas cercano al valor que estoy buscando

Valor buscado $ 5.463.953

rango           valor celda    
A1               $ 5.393.518         
A2               $ 5.463.953            
A3               $ 5.500.000            
A4               $ 6.332.400            
A5               $ 6.726.117

Resultado: Necesito que excel me de como resultado que el monto que esta en la celda A2 ES EL QUE NECESITO


El problema se genera cuando el valor buscado es diferente al rango de valores

Ejemplo :
Valor buscado: $ 1.116

Rango           Monto     
A1               $ 100           
A2               $ 250          
A3               $ 268          
A4               $ 4.987                
A5               $ 763          
Resultado: Necesito resultado que el monto que esta en la celda A1 + A2+ A5 son los montos que mas se acercan al valor buscando 


Favor tu ayuda

Etiquetas: 

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 3 horas 33 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 9650
SOLVER

Ninguna fórmula de Excel calcula varios valores cuya suma se aproxime, pero el complemento SOLVER que viene con la instalación de Excel sí que permite hacer este tipo de cosas

Para cargar el complemento ve a Complementos de Excel y activa Solver

cargar complemento Solver en Excel

 

 

En primer lugar necesitas calcular de alguna manera lo que sumarían unos valores según los consideres o no.

Para esto se me ocurre que puedes poner una columna auxiliar donde anotar un 1 para tener en cuenta el valor o un 0 cero para no tenerlo en cuenta. La suma de los valores a considerar se podría calcular con la función SUMAPRODUCTO,

Calcularíamos también el error de nuestro cálculo mediante el valor absoluto de la diferencia entre el valor buscado y el valor calculado. (se podría calcular ese error de muchas maneras, pero esto puede servirnos)

Este error es lo que queremos minimizar, para obtener la combinación que más se aproxime al valor buscado.

algo así:

 

modelo para optimizar con Solver

 

una vez tenemos nuestro modelo preparado para optimizar, configuramos Solver para que haga los tanteos.

La celda objetivo será la celda donde calculamos la diferencia, que queremos minimizar

definición de objetivo en Solver

Las celdas cambiantes serán las celdas donde tenemos los ceros y unos. esto es lo que queremos tantear para calcular nuestro mínimo.

Pero no pueden cambiar de cualquier manera, sólo pueden ser ceros o unos. esto lo indicamos añadiendo una restricción a esas celdas de tipo binario (eso quiere decir que sólo serán ceros o unos, justo lo que necesitamos)

restricciones a Solver. binario

le damos a Resolver y encontrará esta solución:

solución encontrada por Solver

pues un error de 3, no está mal, poniendo unos a los importes 100, 250 y 763

 

 

 

Data Tools Suite
datos y tablas con Excel

Jesus (no verificado)
Imagen de Jesus
Solver no funciona

Necesito aplicar un formato tipo condicion al rango de celdas que sumen un valor, estas tomando en valor de una celda externa variable... Variable: Que lo modifique si necesito identificar otro valor... y son mas de 40000 celdas como referencia.

Cron
Imagen de Cron
Online
última acción: Hace 10 mins 59 segs
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 11540
Crea un formato diferencial

Crea un formato diferencial con la opción "Utilice una fórmula que determine las celdas para aplicar formato" y apunta a la celda en la que pongas el límite. Recuerda referenciarla con los $ delante.

 

Un saludo

gabriela (no verificado)
Imagen de Jesus
como es el formato diferencia.

hola cron me interesa me expliques tu comentario ya que necesito encontrar los valores que sumados me den esta cantidad$26,835.81

estos son los valores

     57,895.00
     23,924.10
     21,615.00
       3,920.00
       6,655.00
       1,400.00
       2,408.00
       4,030.00
       1,666.00
       2,068.58
       1,936.68
       2,068.58
       4,170.00
       2,308.00
       4,232.00
       1,840.00
       2,024.00
       2,208.00
       3,542.00
       3,566.00
       1,660.00
       2,100.00
     34,800.08
       1,244.00
       6,349.00
       1,104.00
       2,168.00
       1,400.00
       1,114.00
       5,592.00
       3,004.00
       2,100.00
       3,258.00
       3,248.00
       1,400.00
       3,832.01
       1,498.01
       1,953.00
       3,606.00
       1,032.40
       1,498.01
       3,860.00
       4,690.00
       5,549.00
       2,520.00
     23,273.00
       8,204.00
     10,678.00
       3,298.00
       3,388.00
       1,940.00
       2,716.00
       1,009.18
       2,782.00
       5,178.00
       1,144.00
       1,144.00
     31,165.00
       4,326.00
       2,940.00
       1,833.00
       1,948.00
       1,060.00
       1,940.00
       2,140.00
       1,298.00
       6,657.00
       1,090.40
       1,108.00
       2,098.00
       1,470.00
       2,522.00
       1,793.01
       2,093.00
       5,626.00
       4,418.00
       1,144.00
       6,596.00
       2,450.00
     26,450.91
     10,934.00
       1,490.00
       1,092.00
       1,675.00
       2,084.00
       1,050.00
       5,845.00
       1,552.00
       3,422.00
       2,100.00
       3,336.00
       1,171.60
       4,800.01
       5,748.00
       1,070.00
       1,660.00
       6,158.00
       6,974.01
       2,910.00
       1,358.00
       1,498.01
       2,522.00
       1,820.00
     15,443.00
       1,358.00
       4,004.01
       2,738.00
       1,552.00
       2,910.00
       2,000.00
       2,656.00
       1,232.00
       1,552.00
       3,564.00
       6,658.00
       1,240.00
       1,281.80
       5,044.00
       1,316.67
       1,144.00
       6,984.00
     22,477.00
       3,430.00
       4,514.00
       1,060.00
       1,716.00
       5,860.00
       1,512.00
       1,712.00
       5,841.00
       1,552.00
       1,400.00
       1,940.00
           776.00
           394.48
           316.00
           970.00
           776.00
           776.00
             50.00
           333.43
       2,440.00
     17,183.01
       4,324.00
           194.00
           776.00
           313.28
       1,284.00
       2,844.00
       1,746.00
           286.04
       1,174.00
           776.00
           776.00
           116.00
           765.48
       2,866.00
       1,720.00
           776.00
       6,974.00
           700.00
           840.99
             52.72
           517.59
           776.00
           776.00
           848.00
       1,940.00
           611.47
           182.00
           856.00
       1,136.00
           776.00
       2,134.00
           296.02
       3,564.00
           776.00
       2,216.00
           776.00
           130.00
           458.06
       1,820.00
     21,257.00
           194.00
             34.80
           292.30
           124.80
       2,910.00
           776.00
           856.00
       1,726.00
           116.00
           570.22
           756.00
           756.00
           408.62
           816.00
     13,215.01
       1,334.00
           672.80
       3,104.00
       2,713.00
             46.40
           378.31
           700.00
           736.00
             58.00
           527.15
       2,106.00
       1,224.00
           736.00
           276.00
             70.42
       1,666.00
           700.00
     12,949.00
           587.00
           612.00
           736.00
             17.40
           160.88
       1,400.00
           408.00
           595.00
           736.00
           736.00
           736.00
           736.00
           143.08
           388.00
           920.00
           332.49
           736.00
           174.48
           986.00
       2,944.00
           563.37
           601.74
           736.00
           264.87
           110.72
           736.00
       2,576.00
             40.60
             23.20
           471.70
Cron
Imagen de Cron
Online
última acción: Hace 10 mins 59 segs
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 11540
HolaLo que pides no tiene

Hola

Lo que pides no tiene nada que ver con el hilo (Paco, si quieres separa pregunta y respuesta)

Tienes un listado de valores y quieres saber cuáles de ellos debes sumar para llegar a una cantidad.

Este es un típico problema para Solver. Está en la cinta de Datos, a la derecha del todo. Si no lo ves, tendrás que habilitarlo.

Para resolver tu problema, lo que yo haría es crearme una columna accesoria. En esta columna los valores serán 1 o 0, según se sume o no el valor correspondiente de tu columna. Por lo tanto, el resultado será la suma de cada valor tuyo multiplicado por cada 1 o 0 correspondiente. Esto se hace en Excel con SUMAPRODUCTO.

En tercer lugar, Solver no resuelve cosas con más de 200 celdas bailando, así que tenemos que reducir el problema. Para ello, revisa tus repeticiones de valor. Si un valor se repite 11 veces, entonces en la columna accesoria pondremos valores del 0 al 11. Poner un 7 sería como poner 1 a 7 repeticiones del valor, y 0 a las 4 restantes.

Finalmente, al lado de la celda en la que pusimos SUMAPRODUCTO ponemos otra con la siguiente fórmula:

=ABS(26835.81 - C1)       ---suponiendo que el resultado de SUMAPRODUCTO está en la celda C1

 

Ya tenemos todo preparado para lanzar Solver. Lo haremos con los siguientes parámetros:

-Celda objetivo --> la celda donde pusimos la función ABS
-Para --> marcamos la opción Min
-Cambiando las celdas de variables  --> seleccionamos el rango donde están las celdas de la columna accesoria
-Sujeto a las restricciones (esto es lo que hace que todo funcione):

  1. Todo el rango de celdas variable debe ser entero (no queremos sumar 3,25 veces un valor)
  2. Todo el rango de celdas debe ser >= 0 (no queremos valores negativos, pero 0 sí)
  3. Todas las celdas que no se repiten deben ser <= 1 (junto con lo anterior, solo pueden ser 0 o 1)
  4. Todas las celdas que se repitan x veces deben ser <= x (id anterior, podrán ser 0, 1, 2... x)

Finalmente, y no por ello menos importante, elegiremos el tipo de problema Evolutionary. ¿Por qué? Bueno, es un poco largo de explicar, pero básicamente es porque los otros dos tipos de problema resuelven soluciones convergentes. Nuestro problema, al no tener variables continuas (no permitimos decimales), es un problema discreto, por lo que no converge hacia una solución.

Y ya está. A mí no me ha encontrado una solución exacta, se me ha quedado a 0,35

 

Un saludo