sábado, 6 de febrero de 2016

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.

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.




1 comentario:

  1. suponiendo que en una columna de alimentos tenemos varias filas con el concepto refresco, y en la siguiente columna tenemos las diferentes marcas de refrescos y en la siguiente columna tenemos sus respectivos precios...
    Necesito crear una formula que me arroje al comparar la palabra refresco y me de linea por linea el orden consecutivo de la segunda columna en la que vienen las marcas y yo ya con eso sabria como crear la busqueda de referencia de los precios, pero no puedo aplicar un filtro directamente de busqueda.. no se si me explico ...
    por decir
    en celda A1 al insertar la palabra refresco necesito que a partir de la celda A3 me responda con la primer marca y A4 la siguiente y asi sucesivamente hasta que termine de buscar en la columna o rango determinado, se puede de manera facil acaso?

    ResponderEliminar