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

2 envíos / 0 nuevos
Último envío
roodrigo
Imagen de roodrigo
Offline
última acción: Hace 5 meses 4 semanas
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 1 día 1 hora
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 7570
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