Tiempo atrás, expliqué en un post la manera de conciliar ciertos importes, empleando para ello la herramienta de Excel Solver (ver). Al hilo de este post un usuario preguntaba a través de un comentario:
La respuesta a esta cuestión sería afirmativa, claro está bajo ciertos supuestos, ya que en caso de múltiples coincidencias solver siempre se decantaría por una de las posibles soluciones. sin embargo construiremos un modelo en Excel que nos muestre en todo caso, de todas las aproximaciones posibles a buscar, la que sea más próxima al valor buscado.
Importante aclarar, buscamos por aproximación al total de la suma.
Veamos nuestros datos de partida:
Realmente, nuestra base de trabajo es similar a la expuesta en la entrada Conciliar una partida con Solver, si bien, necesitaremos ciertas modificaciones. En primer lugar, construiremos dos columnas C y D anexas, ambas con la misma fórmula
=A2*B2
para luego acabar sumando estos valores en C8 y D8
=SUMA(C2:C7) y =SUMA(D2:D7); sumas que emplearemos después al Agregar nuestras condiciones de Solver.
Importante también es definir correctamente la que será celda objetivo E8, con la fórmula:
=ABS(((G2*2)-(D8+C8))/2)
sabiendo que nuestro modelo es el siguiente, con la disposición de celdas que vemos en la imagen:
Además de la formulación de la celda $E$8 (futura celda objetivo), es clave determinar cuál es el factor de aproximación, en mi ejemplo será de 2 por encima o por debajo del valor buscado o a conciliar (27,92); estos valores serán empleados al construir las condiciones de Solver.
Ejecutemos la herramienta Solver, así que iremos a la Ficha Datos > Análisis > Solver, y configuraremos los diferentes parámetros:
Observemos como hemos seleccionado minimizar el valor de la celda objetivo $E$8, cambiando el rango de celdas $B$2:$B$7, es decir, las celdas de nuestra Tabla origen, por las que multiplicaremos cada importe. Y en cuanto a las tres restricciones, forzamos a que el rango $B$2:$B$7 sean 1 ó 0 (resultado binario), además de restringir que la suma del producto de cada registro por ese valor binario a obtener sea mayor o igual que el mínimo (valor buscado menos la aproximación) y menor o igual que el máximo (valor buscado más la aproximación).
Con estas restricciones, al minimizar el modelo desarrollado, conseguiremos llegar a las partidas que mejor se aproximan al valor buscado, esto es a la 'conciliación por aproximación'.
Si Aceptamos la configuración, vemos la respuesta dada:
Que al comprobar manualmente con las diferentes combinaciones que se acercan al resultado buscado, podemos ver que ciertamente el resultado propuesto es, de todos los posibles, el más próximo.
Para nuestro ejemplo, las partidas cuya suma se aproximan más a 27,92 son 10,00 + 15,00 + 2,90.
Por supuesto, este método, sería válido también para la conciliación exacta.
| ...¿podríamos hacer que las posibilidades de suma fueran por un aproximado de por ejemplo 5 unidades arriba y 5 abajo?... |
La respuesta a esta cuestión sería afirmativa, claro está bajo ciertos supuestos, ya que en caso de múltiples coincidencias solver siempre se decantaría por una de las posibles soluciones. sin embargo construiremos un modelo en Excel que nos muestre en todo caso, de todas las aproximaciones posibles a buscar, la que sea más próxima al valor buscado.
Importante aclarar, buscamos por aproximación al total de la suma.
Veamos nuestros datos de partida:
Realmente, nuestra base de trabajo es similar a la expuesta en la entrada Conciliar una partida con Solver, si bien, necesitaremos ciertas modificaciones. En primer lugar, construiremos dos columnas C y D anexas, ambas con la misma fórmula
=A2*B2
para luego acabar sumando estos valores en C8 y D8
=SUMA(C2:C7) y =SUMA(D2:D7); sumas que emplearemos después al Agregar nuestras condiciones de Solver.
Importante también es definir correctamente la que será celda objetivo E8, con la fórmula:
=ABS(((G2*2)-(D8+C8))/2)
sabiendo que nuestro modelo es el siguiente, con la disposición de celdas que vemos en la imagen:
Además de la formulación de la celda $E$8 (futura celda objetivo), es clave determinar cuál es el factor de aproximación, en mi ejemplo será de 2 por encima o por debajo del valor buscado o a conciliar (27,92); estos valores serán empleados al construir las condiciones de Solver.
Ejecutemos la herramienta Solver, así que iremos a la Ficha Datos > Análisis > Solver, y configuraremos los diferentes parámetros:
Observemos como hemos seleccionado minimizar el valor de la celda objetivo $E$8, cambiando el rango de celdas $B$2:$B$7, es decir, las celdas de nuestra Tabla origen, por las que multiplicaremos cada importe. Y en cuanto a las tres restricciones, forzamos a que el rango $B$2:$B$7 sean 1 ó 0 (resultado binario), además de restringir que la suma del producto de cada registro por ese valor binario a obtener sea mayor o igual que el mínimo (valor buscado menos la aproximación) y menor o igual que el máximo (valor buscado más la aproximación).
Con estas restricciones, al minimizar el modelo desarrollado, conseguiremos llegar a las partidas que mejor se aproximan al valor buscado, esto es a la 'conciliación por aproximación'.
Si Aceptamos la configuración, vemos la respuesta dada:
Que al comprobar manualmente con las diferentes combinaciones que se acercan al resultado buscado, podemos ver que ciertamente el resultado propuesto es, de todos los posibles, el más próximo.
Para nuestro ejemplo, las partidas cuya suma se aproximan más a 27,92 son 10,00 + 15,00 + 2,90.
Por supuesto, este método, sería válido también para la conciliación exacta.
Muchísimas gracias!!!!!
ResponderEliminarJusto lo que necesitábamos
Oteic