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.