Victor7

He desarrollado un modelo (archivo adjunto) que comparto gustosamente (traspasos de efectivo entre bancos para eliminar/evitar saldos negativos), para determinar qué transferencias deben efectuarse entre las respectivas entidades para cubrir los descubiertos existentes. Vayamos con las explicaciones, mejor a la vista del archivo: Los únicos datos a introducir serán las columnas B:C, con los nombres de los bancos y los saldos existentes en cada uno de ellos (en este ejemplo proponemos 10 diferentes bancos). Las fórmulas de las columnas D:E:F nos ofrecen una visión de los saldos por separado: negativos, positivos, y las necesidades, que no son más que los negativos cambiados de signo. Es necesario plasmarlo así para facilitar futuras fórmulas. La fórmula de H2 transpone los nombres de bancos; esta fila será la base de la matriz principal para ir componiendo los movimientos necesarios que eliminen saldos negativos: abscisas=fila 2=entidades que efectuarán transferencias, y ordenadas=columna B=entidades receptoras de las mismas. La fórmula de H3 puede copiarse horizontal y verticalmente hasta donde sea necesario, para completar la matriz. En este ejemplo, sería hasta la celda Q12. Una vez completado el copy/paste, obtendremos las cantidades a traspasar. Nota: es necesario dejar una columna en blanco (en este caso la G), también para acortar y facilitar la composición de estas fórmulas. Las columnas S:T:U nos ofrecerán un vistazo global sobre la situación (saldo) inicial y final de cada una de las entidades. En T14 y U14 tenemos las respectivas sumas de cada una de esas columnas; lógicamente la posición de tesorería inicial será igual a la final, ya que no estamos ejercitando sino un juego de suma 0. Y por último, en el rango W:X:Y (1 única fórmula en W3) tenemos un valor añadido: se despliega una matriz con las transferencias necesarias a efectuar (entidades emisora y receptora e importe). La fase anterior (hasta la columna U) puede ejecutarse con cualquier versión de Excel, pero esta última requiere M365. Las celdas del rango D:Q llevan un formato personalizado que invisibiliza los 0; lo he hecho así para evitar contaminación visual, pero puede modificarse a voluntad. Otro detalle a comentar es que no se requiere ningún orden concreto para introducir los bancos, pero hay que tener presente que los desembolsos serán efectuados empezando por las primeras posiciones, hasta que vayan quedándose sin recursos. En este sentido, si tenemos alguna preferencia de conservar saldos (por ejemplo, queremos evitar que [siempre que sea posible] Caixa y BBV queden a 0, deberemos introducirlos en los últimos lugares (en el ejemplo, filas 11 y 12). Si quedan remanentes, serán estos 2 bancos los que los sigan disfrutando. Los saldos colocados en la columna C son cantidades aleatorias; cada vez que pulsemos F9 se recalculará todo el modelo. Lógicamente, los resultados deseables se darán cuando el total (celda C14) ofrezca saldo positivo. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Y ahora vayamos con los pasos necesarios si se necesita incrementar el número de Bancos. Seguiremos este orden: 1. Iremos incluyendo en las columnas B:C los nombres de bancos y sus respectivos saldos. 2. Completaremos las columnas D:E:F copiando verticalmente las fórmulas hasta el último registro. 3. Nos situaremos en R1 e insertaremos tantas columnas como sea necesario, de forma que puedan quedar recogidos todos los nuevos bancos. 4. La fórmula que hay ahora en H2: =TRANSPONER(B3:B12) la transformaremos en: =TRANSPONER(B3:Bxx) siendo xx el número de la fila donde aparezca el último banco. 5. La fórmula que hay ahora en H3: =MIN($F3-SUMA($G3:G3);BUSCARV(H$2;$B$3:$E$12;4;)-SUMA(H$2:H2)) la transformaremos en: =MIN($F3-SUMA($G3:G3);BUSCARV(H$2;$B$3:$E$xx;4;)-SUMA(H$2:H2)) siendo xx el número de la fila donde aparezca el último banco. 6. Una vez recompuesta H3, copiaremos la fórmula horizontal y verticalmente hasta xx : yy siendo xx la columna que ocupe el último banco, e yy la última fila ocupada asimismo por el último banco. 7. Las columnas S:T:U también habrá que completarlas de forma que recojan las nuevas filas ocupadas; bastará con copiarlas verticalmente. 8. No nos olvidemos de las sumas de las columnas C, T y U que ahora están en la fila 14: habrá que añadirlas al final, para verificar la bondad de los resultados. 9. Por último, la fórmula que hay ahora en W3: =LET(a;H3:Q12;b;a>0;APILARH(ENCOL(SI(b;H2#;x);2);ENCOL(SI(b;B3:B12;y);2);ENCOL(SI(b;a;z);2))) la transformaremos en: =LET(a;H3:xxyy;b;a>0;APILARH(ENCOL(SI(b;H2#;x);2);ENCOL(SI(b;B3:Bxx;y);2);ENCOL(SI(b;a;z);2))) siguiendo el mismo razonamiento que en los anteriores casos: en vez de Q12 introduciremos la última celda de la nueva matriz, y en vez de B12 colocaremos la última celda de la columna B donde haya datos. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Creo que no olvido nada, y lamento la extensión de este post, pero no he sido capaz de reducirlo. Saludos, Automatizar traspasos (C).xlsx