Tengo un excel para el manejo de préstamos, y con el tiempo lo he ido mejorando porque me divierte y me gusta intentar automatizarlo tanto como se pueda.
Si bien la hoja tiene varias columnas, para el tema en cuestión sólo es necesario mencionar 2. En la columna A pongo la referencia (con este dato hago la mayoría de fórmulas), y en la F se pone el saldo que queda por pagar.
Ahora bien, llevo varios días intentando lograr "predecir" la referencia que sigue al añadir otra fila. Mi mejor éxito es esta fórmula (que corresponde a la celda A9):
Básicamente primero creo un índice buscando la última aparición de la referencia que esté en la celda de arriba y bajando una fila con DESREF, y después sólo busco la primera referencia cuyo saldo a pagar sea mayor que 0.
Esta fórmula funciona bien en muchos casos, pero tiene algunos "bugs". Por ejemplo, si liquidan un préstamo antes de que sea "su turno" provoca que, aunque su primera aparición dentro del índice su saldo a pagar sea mayor a 0, en realidad ya esté pagado. Otro caso es si la referencia de la celda de arriba es de un préstamo nuevo, ahí no se puede crear el índice.
Hasta donde yo sé y por lo que he investigado, no hay mucho más que se pueda lograr sólo con fórmulas, pero qué tal creando mi propia función? No sé casi nada de VBA, por eso les quiero consultar si es posible crear una función que haga esto:
-Tomar la referencia de la celda de arriba y comprobar si se repite más de una vez, si es así, crear un índice bajando una fila a partir de la penúltima aparición, si no subir otra fila y hacer lo mismo con esa referencia.
-Una vez teniendo el índice, buscar y devolver la primera referencia cuya primera y última aparición dentro de ese índice tengan un saldo a pagar mayor a 0.
Adjunto un excel de ejemplo preparado para hacer pruebas.
De antemano muchas gracias por su tiempo y atención. ¡Quedo atento a sus respuestas!
Hola!
Tengo un excel para el manejo de préstamos, y con el tiempo lo he ido mejorando porque me divierte y me gusta intentar automatizarlo tanto como se pueda.
Si bien la hoja tiene varias columnas, para el tema en cuestión sólo es necesario mencionar 2. En la columna A pongo la referencia (con este dato hago la mayoría de fórmulas), y en la F se pone el saldo que queda por pagar.
Ahora bien, llevo varios días intentando lograr "predecir" la referencia que sigue al añadir otra fila. Mi mejor éxito es esta fórmula (que corresponde a la celda A9):
=INDICE(DESREF(BUSCARX($A8,$A$2:DESREF($A8,-1,0),$A$2:DESREF($A8,-1,0),,0,-1),1,0):$A8,COINCIDIR(VERDADERO,DESREF(BUSCARX($A8,$A$2:DESREF($A8,-1,0),$F$2:DESREF($F8,-1,0),,0,-1),1,0):$F8>0,0))
Básicamente primero creo un índice buscando la última aparición de la referencia que esté en la celda de arriba y bajando una fila con DESREF, y después sólo busco la primera referencia cuyo saldo a pagar sea mayor que 0.
Esta fórmula funciona bien en muchos casos, pero tiene algunos "bugs". Por ejemplo, si liquidan un préstamo antes de que sea "su turno" provoca que, aunque su primera aparición dentro del índice su saldo a pagar sea mayor a 0, en realidad ya esté pagado. Otro caso es si la referencia de la celda de arriba es de un préstamo nuevo, ahí no se puede crear el índice.
Hasta donde yo sé y por lo que he investigado, no hay mucho más que se pueda lograr sólo con fórmulas, pero qué tal creando mi propia función? No sé casi nada de VBA, por eso les quiero consultar si es posible crear una función que haga esto:
-Tomar la referencia de la celda de arriba y comprobar si se repite más de una vez, si es así, crear un índice bajando una fila a partir de la penúltima aparición, si no subir otra fila y hacer lo mismo con esa referencia.
-Una vez teniendo el índice, buscar y devolver la primera referencia cuya primera y última aparición dentro de ese índice tengan un saldo a pagar mayor a 0.
Adjunto un excel de ejemplo preparado para hacer pruebas.
De antemano muchas gracias por su tiempo y atención. ¡Quedo atento a sus respuestas!
Préstamos tests.xlsm