BUSCARV acumulado

Puede descargar el archivo de Excel: TotalBuscarV.xlsm

La función BUSCARV realiza una búsqueda vertical en una tabla. Puede ver una entrada donde se habla de este tema clásico en Excel.

• Función BUSCARV

Ahora lo que deseamos es buscar varios valores y crear una función que nos de la suma de esas celdas buscadas.

Disponemos de una tabla con productos, e importes por meses. Queremos diseñar una función similar a BuscarV (Vlookup) que proporcione el acumulado. Deseamos que nos de el importe acumulado de los meses, hasta el mes indicado inclusive. La función así creada se llama BuscarVV. Si deseamos acumular entre dos meses, el de inicio y el de final, incluidos ambos, lo que haremos es restar una BuscarVV de otra. Ver ejemplo.

Los valores de la tabla se generan con números aleatorios, al igual que las celdas de color naranja donde se elijen los siguientes valores:

• Celda Q9. Se establece el producto entre 1 y 20
• Celda Q11: Mes de inicio
• Celda Q12: Mes de final

Deseamos acumular los valores correspondientes al Producto seleccionados entre los meses de inicio y final ambos inclusive. Puesto que estos valores se eligen de forma aleatoria, al pulsar la tecla de función F9 se produce un recálculo manual que hace que los valores cambien.

Mediante formato condicional seleccionamos con fondo verde las celdas de la tabla que deseamos acumular. La fórmula que podemos ver en el formato condicional sobre la celda C10 es el siguiente.

=Y(VALOR(+DERECHA(C$9;2))<=$Q$12;VALOR(+DERECHA(C$9;2))>=$Q$11;$B10=$Q$10)

Disponemos de tres métodos para obtener el acumulado.

Método 1

El la celda Q13 figura la siguiente expresión.

=buscarvv(Q10;tabla;Q12+1)-buscarvv(Q10;tabla;Q11)

La función BuscarVV es una función que hemos programado usando Macros de Excel. Se trata de una función (function) que es la siguiente.

Function BuscarVV(Valor, Tabla, Hasta_Columna, Optional exacto)
    Dim i As Byte
    Dim Total
    For i = 2 To Hasta_Columna
        Total = Total + WorksheetFunction.VLookup(Valor, Tabla, i, exacto)
    Next i
    BuscarVV = Total
End Function

La función lo que hace es llamar a la función BUSCARV que en inglés es VLOOKUP. La llamada se efectúa usando la expresión:

WorksheetFunction.Función en ingles(parámetro1,parámentro2,...)

La función BuscarVV lo que hace es sumar los valores del Producto indicado, del rango Tabla, hasta la columna indicada).

El nombre de rango Tabla se corresponde con el rango B10:N29.

Método 2

El la celda Q14 figura la siguiente expresión.

=SUMA(INDIRECTO("F"&COINCIDIR(Q10;B10:B29;0)+9&"C"&Q11+2&":F"&COINCIDIR(Q10;B10:B29;0)+9&"C"&Q12+2;0);0)

Esta fórmula no utiliza macros y se basa en las funciones INDIRECTO y COINCIDIR.

Método 3

El la celda Q15 figura la siguiente expresión.

=SUMA(DESREF(B9;COINCIDIR(Q10;B10:B29;0);Q11;1;Q12-Q11+1))

Esta fórmula no utiliza macros y se basa en las funciones DESREF y COINCIDIR.

Ultimo valor. Una aplicación de DESREF

Descargar el fichero: ultimo_valor.xlsx

Ultimo valor nos permite calcular el último valor de una columna. Para ello se utiliza la función DESREF y COINCIDIR. Utilizaremos un caso que nos permite gestionar las participaciones de un Fondo de Inversión.

Disponemos de tres tablas y un gráfico.

En las celdas amarillas se introducen los datos y en las verdes se calculan las fórmulas.

La tabla central contiene el valor de una participación en diferentes fechas. Con esta información creamos el gráfico que nos muestra el valor cotizado a lo largo del tiempo de una participación del fondo de inversión. Esta tabla central irá creciendo hacia abajo a medida que se introduzcan nuevos valores en diferentes fechas.

La tabla de la izquierda permite ir anotando los movimientos (compras y ventas) que se producen en la cartera de este fondo de inversión en concreto. Las compras suponen un número positivo de participaciones y las ventas implican un número negativo de participaciones. A medida que se vayan produciendo nuevas compras y ventas de participaciones esta tabla irá creciendo hacia abajo, anotándose una nueva línea por cada operación realizada.

El precio de la celda E5 se obtiene con la expresión:

=DESREF(H4;COINCIDIR(B5;H:H)-4;1)

La función COINCIDIR tiene la siguiente sintaxis:

=COINCIDIR(valor_buscado; matriz_buscada; tipo_de_coincidencia])

Y la función DESREF utiliza los siguientes argumentos:

=DESREF(ref;filas;columnas;alto;ancho)

A la función COINCIDIR tenemos que restarle 4 porque las fechas de la columna H comienzan en la fila 5. Esto supone que los datos deben comenzar siempre en la fila 5, no pudiéndose insertar filas.

En la celda K5 determinamos las participaciones en cartera sumando todos los valores de la columna D. Esto se hace con la expresión:

=SUMA(D:D)

Esto supone que no se deben introducir valores numéricos ni fechas en las celdas

D1 a D4.

La tabla de la derecha contiene tres fórmulas, y es una tabla que no crece. Esto es, no es una base de datos como las dos anteriores sino que esta constituida por tres fórmulas de resultados. En ella se determina el valor actual de la cartea de participaciones de este fondo de inversión concreto. Este valor actual se calcula como el producto del número de participaciones existentes en el momento actual, multiplicado por el último valor conocido de la participación.

La celda L5 se calcula con la expresión:

=DESREF(I4;COINCIDIR(MAX(H:H);H:H)-4;0)

Al ser la columna H la de fechas, su máximo nos da el valor de la fecha correspondiente a la fecha más actual, la más tardía. La función COINCIDIR nos indica cuantas fechas hay en la columna H. Restamos 4 ya que los datos comienzan en la fila 5. Por eso no se deben insertar o eliminar filas antes de la fila 5. La función DESREF nos lleva hasta el último precio de la participación anotado en la columna I. Por tanto la celda L5 contiene el último precio conocido de la participación.

En M5 calculamos el Valor Actual de la cartera simplemente multiplicando el número de participaciones que tenemos en cartera por el último precio conocido de una participación.

=K5*L5