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
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
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í:
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
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)
le damos a Resolver y encontrará esta solución:
pues un error de 3, no está mal, poniendo unos a los importes 100, 250 y 763
------
Ya sé Excel, pero necesito más.
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.
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
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
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):
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
[comentario movido a tema nuevo en el foro: http://www.necesitomas.com/alternativa-solver]