Función para "predecir" la siguiente referencia

apoh07 · el 25 de julio

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

Israel Cassales · el 19 de diciembre

En base a lo explicado y si lo entendí bien, sí es posible crear una función personal en VBA:

Function PredecirReferencia(rango As Range) As Variant
    Dim celda As Range
    Dim refAnterior As String
    Dim refActual As String
    Dim saldo As Double
    Dim encontrado As Boolean

    ' Inicializamos las variables
    refAnterior = rango.Cells(rango.Cells.Count).Value
    encontrado = False

    ' Recorremos el rango de celdas de abajo a arriba
    For Each celda In rango.Cells
        refActual = celda.Value
        saldo = celda.Offset(0, 5).Value ' Asumimos que la columna F está 5 columnas a la derecha de la columna A

        ' Comprobamos si la referencia actual es igual a la anterior y si el saldo es mayor que 0
        If refActual = refAnterior And saldo > 0 Then
            ' Si encontramos una coincidencia, establecemos el valor de la función y salimos del bucle
            PredecirReferencia = refActual
            encontrado = True
            Exit For
        End If

        ' Actualizamos la referencia anterior
        refAnterior = refActual
    Next celda

    ' Si no encontramos ninguna coincidencia, devolvemos un valor vacío
    If Not encontrado Then PredecirReferencia = CVErr(xlErrNA)
End Function

Ten en cuenta que este es un código de ejemplo y puede que necesites ajustarlo un poco para situaciones específicas.

En 25/7/2023 at 11:59 , apoh07 dijo:

creando mi propia función? No sé casi nada de VBA

Ha pasado tiempo, pero si es posible haz pruebas y comparte lo que hayas logrado o si te has detenido en algo, compártelo.

Israel Cassales · el 19 de diciembre

Ejemplo: si estás trabajando en la celda A9 y quieres predecir la siguiente referencia basándote en las referencias y saldos desde A2 hasta A8 y F2 hasta F8, puedes usar la función de la siguiente manera:

=PredecirReferencia(A2:A8)

La fórmula buscará la referencia en la celda A8 en el rango A2:A8 y devolverá la primera referencia cuya primera y última aparición dentro de ese rango tengan un saldo a pagar mayor a 0 en el rango F2:F8

Saludines

apoh07 · el 20 de diciembre

hace 21 horas, Israel Cassales dijo:

En base a lo explicado y si lo entendí bien, sí es posible crear una función personal en VBA:

Function PredecirReferencia(rango As Range) As Variant
    Dim celda As Range
    Dim refAnterior As String
    Dim refActual As String
    Dim saldo As Double
    Dim encontrado As Boolean

    ' Inicializamos las variables
    refAnterior = rango.Cells(rango.Cells.Count).Value
    encontrado = False

    ' Recorremos el rango de celdas de abajo a arriba
    For Each celda In rango.Cells
        refActual = celda.Value
        saldo = celda.Offset(0, 5).Value ' Asumimos que la columna F está 5 columnas a la derecha de la columna A

        ' Comprobamos si la referencia actual es igual a la anterior y si el saldo es mayor que 0
        If refActual = refAnterior And saldo > 0 Then
            ' Si encontramos una coincidencia, establecemos el valor de la función y salimos del bucle
            PredecirReferencia = refActual
            encontrado = True
            Exit For
        End If

        ' Actualizamos la referencia anterior
        refAnterior = refActual
    Next celda

    ' Si no encontramos ninguna coincidencia, devolvemos un valor vacío
    If Not encontrado Then PredecirReferencia = CVErr(xlErrNA)
End Function

Ten en cuenta que este es un código de ejemplo y puede que necesites ajustarlo un poco para situaciones específicas.

Ha pasado tiempo, pero si es posible haz pruebas y comparte lo que hayas logrado o si te has detenido en algo, compártelo.

Hola, Israel!

Veo que también te gusta este tipo de retos xD La opción que propones no logra lo que busco; por lo poco que entiendo, creo que da la primera referencia del rango que cumpla las condiciones. Además, tiene el inconveniente de que, si la referencia anterior es un préstamo nuevo (y por ende no se repite), nunca va a encontrar una coincidencia.

Sin embargo, hace tiempo logré dar con una solución ingeniosa. La clave fue primero acotar el rango para sólo trabajar con las referencias del último mes. Fórmula en A92:

=LET(FINICIO,
             FECHA.MES($C91, -1),
     CONTADOR,
             SI(CONTAR.SI($C$2:$C91, FINICIO) = 0, 0, CONTAR.SI($C$2:$C91, $C91)),
     FIREAL,
             MIN(SI($C$2:$C91 >= FINICIO, $C$2:$C91)),RFECHA,
CELDA("direccion",INDICE($C$2:$C91,COINCIDIR(FIREAL,$C$2:$C91,0))),
     IINICIO,
             DESREF(INDIRECTO(RFECHA),CONTADOR, -2),PREDICCION(IINICIO:$A91))

El contador fue la solución que se me ocurrió para los casos en los que haya préstamos en la misma fecha, así el índice siempre va a omitir la misma cantidad de préstamos que ya hayan dado el pago del mes. Esto tiene fallos, pero son muy puntuales.

Después creé esta función con la ayuda de ChatGPT:

Function PREDICCION(rng As Range) As Variant
    Dim cell As Range
    Dim dict As Object
    Dim offsetCell As Range
    Dim resultValue As Variant
    
    ' Crear un diccionario para almacenar las últimas direcciones y sus ocurrencias
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Recorrer cada celda en el rango y almacenar la última dirección para cada valor
    For Each cell In rng
        dict(cell.Value) = cell.Address
    Next cell
    
    ' Recorrer nuevamente el rango para encontrar el primer elemento con valor mayor a 0,
    ' en la última aparición de ese valor dentro del rango
    For Each cell In rng
        Set offsetCell = Range(dict(cell.Value)).Offset(0, 5)
        If cell.Address = dict(cell.Value) And IsNumeric(offsetCell.Value) And offsetCell.Value > 0 Then
            resultValue = cell.Value
            PREDICCION = resultValue
            Exit Function ' Salir de la función cuando se encuentre el primer elemento válido
        End If
    Next cell
    
    ' Si no se encuentra ningún elemento válido, devolver un mensaje o valor apropiado
    PREDICCION = "No se encontró ningún elemento válido"
    
    ' Liberar el objeto del diccionario
    Set dict = Nothing
End Function

Hasta ahora, es la mejor solución que he encontrado :)