Victor7

He compuesto un modelo (que gustosamente comparto), válido para Excel-365, para el cálculo del importe a satisfacer en caso de cantidades gravadas por diferentes porcentajes según tramos. Ejemplo desarrollado en el archivo adjunto: De una cierta cantidad, los primeros 6.000€ pagarán un 19%. El 2º tramo (entre 6.000,01 y 50.000) pagará un 21% El 3er tramo (entre 50.000,01 y 200.000) pagará un 23% El 4º tramo (entre 200.000,01 y 300.000) pagará un 27% El 5º y último tramo (a partir de 300.000,01) pagará un 28% Si introducimos la cantidad a evaluar en E4, obtendremos en H6 la cuota resultante a pagar. Y a partir de J2, un detalle de cada uno de los tramos con sus respectivas cuantías parciales. Modificando tramos y/o porcentajes (columnas F:G), obtendremos nuevos [y corregidos] resultados. De la misma forma, podemos ampliar tramos y porcentajes en ese mismo rango (F:G). Para que las fórmulas lo recojan, deberemos modificar esa repercusión en las fórmulas de cálculo: éstas son sólo 2, en las celdas H6 y J2. Saludos a todos,

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

Hola a todos, He compuesto el archivo-plantilla adjunto para el caso de que necesitemos contar [o evaluar] datos ocurridos en tramos horarios definidos a voluntad. Supongamos, por ejemplo, que de una lista vertical de fechas (en formato hh:mm:ss) queremos averiguar cuántas ocurrencias existen por tramo. Seleccionaremos Hoja1 e introduciremos los datos en la columna A (a partir de A2), y en C14 el tramo horario que queremos evaluar (en este ejemplo, he introducido 2 horas, pero puede modificarse por cualquier otro). Previamente, en C6 y C3 tendremos, respectivamente, el primer y último tramos afectados. En C9 la secuencia de datos según tramo horario, fecha inicial y final. A partir de ahí, en E3 se creará una matriz dinámica con todos los resultados: columna E=momento inicial del tramo; columna F=momento final, y columna G=número de eventos en el tramo. -:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-: A efectos didácticos, me parece oportuno ofrecer un explicativo de los pasos dados hasta la fórmula final [única] de E3. Yendo por partes, tenemos en el rango I:K el primer paso: fórmulas fáciles de interpretar, que componen las 3 variables por separado. En el segundo paso (rango M:O), simplemente apilamos ambos 3 conceptos variables en 1 única fórmula. En el paso 3 no hacemos más que sustituir I3#;J3#;K3# por cada una de las fórmulas que se hallan en aquellas celdas (lo que entendemos en lenguaje Excel como megafórmula). De hecho, podríamos quedarnos aquí; esta fórmula de Q3, como vemos, sólo toca rangos de las columnas A y C, por lo que ya no utiliza cálculos o columnas auxiliares,... pero como Excel nos ha regalado en su versión 365 la función =LET, podemos hacer un uso provechoso y productivo de la misma para acortar significativamente la fórmula (paso 4) : creamos las variables a, b y c, que nos permiten sustituir cálculos repetitivos por sus nombres. Este paso 4 es lo mismo que la solución inicial del rango E:G. -:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-: Con esto queda todo explicado; la diferencia con Hoja2 es que aquí añadimos una nueva variable de cálculo en la columna B, que nos permite obtener un resultado acumulativo no de eventos ocurridos entre fechas, sino de suma de partidas. Se modifica =CONTAR.SI.CONJUNTO por =SUMAR.SI.CONJUNTO, y ahora nos aparecerá en la columna H la suma de partidas de la columna B ordenada por los tramos horarios elegidos. Comprobaremos que en B1 y en H1 tenemos el mismo resultado: la suma de ambas columnas. -:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-: Notas finales: (1). Las cantidades de la columna B, Hoja2, son cifras aleatorias; cada vez que pulsemos F9 se recalcularán los resultados. (2). Ahora mismo, el archivo evalúa el rango A2:A2000. Si hubiere más registros, habrá que modificar la fórmula sustituyendo, A2:A2000 por A2:A20000 (o superior, de ser necesario). Saludos a todos,