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

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,

Hola a todos, Progresiones aritméticas y geométricas: Antiguo (o no) concepto matemático con vida propia en el universo de las magnitudes evolutivas numéricas... A efectos didácticos y resolutivos, me he permitido componer un archivo-modelo [que gustosamente comparto], con un desarrollo que calcula cada una de las variables que intervienen en esa particular área de Matemáticas. Además de una plantilla Excel, quizá pueda ser sea útil también a estudiantes: si se observan las fórmulas que obtienen cada una de las variables, pueden deducirse fácilmente las ecuaciones para su resolución. He colocado en color azul las celdas que contienen fórmulas, donde no deben introducirse datos. En el archivo, las columnas H y K también llevan fórmulas, pero son para validar los datos de partida, que aparecen en la columna D. El archivo, en origen, lleva estos datos configurados como números aleatorios, así que cada vez que pulsemos F9 obtendremos nuevas variables y desarrollos. Se pueden introducir manualmente otros datos en la columna D, con lo que se desarrollará la progresión, verticalmente, a partir de la celda B4. Asimismo, se pueden introducir manualmente otros datos en las columnas H o K (dependiendo de lo que se busque) : obtendremos el resultado en su correspondiente epígrafe. Saludos a todos,

Hola a todos, He desarrollado un método para localizar valores repetidos en 2 columnas (algo que no tendría mucha complicación si no existieran duplicados; es en ese caso cuando empiezan los obstáculos). Empecemos con las explicaciones: La parte a considerar serían los importes de las columnas D y E. Ahí los introduciremos, y serán la base de partida: el objetivo es localizar cuáles coinciden en ambas columnas. Primero cambiaremos a abscisas/coordenadas ambas partidas: la fórmula de K5 nos transpondrá la columna C, mientras la fórmula de I7 copiará los valores de la columna D. Ya tenemos el terreno abonado para construir una gran base de datos que nos ilustre sobre las repeticiones: La fórmula de K7, copiada horizontal y verticalmente hasta límites suficientes, nos devolverá un 1 allá donde coincida el valor de la fila 5 y la columna I. Con eso, daremos el paso necesario para conocer en qué celdas se encuentran las coincidencias. Serán las fórmulas de K1 y K2, respectivamente (ambas también copiadas a la derecha), las que nos informen dónde se encuentran las incógnitas que buscamos. Y por último, hay un formato condicional aplicado a las columnas D y E que nos colorea las duplicidades para su fácil identificación. -:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-: Notas al margen: Ahora mismo, las fórmulas de la BD general llegan hasta la fila 71; si se necesitan más no hay problema en copiarlas y pegarlas verticalmente lo que sea necesario. De la misma forma, las fórmulas de K5 e I7 contemplan hasta 150 registros. Pueden ampliarse también sin problemas. He colocado unos cuantos registros de ejemplo en las columnas D y E que son números aleatorios; cada vez que pulsemos F9 se recalcularán los resultados, observando los cambios habidos. No es necesario que todas las celdas de las columnas D y E estén llenas; puede haber vacías, en cuyo caso quedará un espacio en blanco en la columna I o en la fila 5. No afecta a los resultados esperados. Sin embargo, cuando se añadan registros es conveniente verificar que el formato condicional los abarca todos. -:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-: Comparto gustosamente el archivo, que espero sea útil a efectos prácticos o quizá también didácticos. Saludos a todos,