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.

Función Personalizada

Puede descargar el fichero: Funcion_Personalizada.xlsm

Programar una función personalizada en #Excel es una tarea sencilla y que nos puede reportar un gran ahorro de tiempo. Dispone de un vídeo donde podemos ver el proceso para crear una función que calcula el margen comercial de un establecimiento comercial.

Vídeo

Pasar matrices a una función

Pasar matrices a una función en programación de macros para Excel es de lo que nos vamos a ocupar en este Post. También veremos el caso en el que la función devuelve una matriz. Disponemos de un procedimiento (Sub) que llama a una función (Function), la cual devuelve o bien un valor, o bien una matriz.

Primer caso

La función recibe una matriz y devuelve un único valor.

Código:

Sub principal()
Dim A(2)
A(1) = 1
A(2) = 2
MsgBox sumatorio(A)
End Sub

Private Function sumatorio(B)
Dim s
s = B(1) + B(2)
sumatorio = s
End Function

Segundo caso

La función no recibe nada y devuelve una matriz.

Es importante definir la función Genera escribiendo el tipo y unos paréntesis detras del tipo de variable:

Function Genera() As Byte()

Estos últimos paréntesis son imprescindibles.

Código:

Sub Ejecuta()
Dim Z() As Byte
Z = Genera()
MsgBox (Z(1) & ", " & Z(2))
End Sub

Private Function Genera() As Byte()
Dim A(2) As Byte
A(1) = 1: A(2) = 2
Genera = A
End Function

Otro ejemplo que se ajustaría a este segundo caso se da cuando se pide a la matriz que genere ciertos números aleatorios. Por ejemplo, la función podría generar números aleatorios sin repetición en cierto rango de valores enteros.

Tercer caso

La función recibe una matriz y devuelve otra matriz.

Código:

Sub Transforma()
Dim Z() As Byte
Dim p, q
p = 4: q = 5
Z = Dobla(p, q)
MsgBox (Z(1) & ", " & Z(2))
End Sub

Private Function Dobla(x, y) As Byte()
Dim A(2) As Byte
A(1) = x * 2
A(2) = y * 2
Dobla = A
End Function