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.

BUSCARV para valores repetidos

Archivo utilizado en el vídeo: excelavanzado_buscarv_repetidos.xlsx

La función BUSCARV siempre busca el primer valor que encuentra en el caso de que existan varios repetidos. Vamos a proponer un procedimiento que nos permitirá extraer de una base de datos aquellos registros que indiquemos aunque estén repetidos.

Nota

En el rango W2:Z2 se encuentran las fórmulas aleatorias con las que se han generado los valores de la base de datos.

Hoja1

Hoja2

Usamos la siguiente función matricial.

=SI.ERROR(INDICE($B$6:$B$24;K.ESIMO.MENOR(SI(C6:C24=0;FILA());FILA()-5)-5);"")

Hoja3

En la Hoja3 se mustran los pasos que permite obtener la fórmula de la Hoja3 de la columan amarilla.

Hoja4

Introducimos una fórmula matricial que extrae los registros que cumplen el criterio. En la celda Q9 vemos la siguiene fórmula matricial.

=SI.ERROR(INDICE(factura;K.ESIMO.MENOR(SI($I$5=Comercial;FILA()-8);FILA()-8);1);"")

Esta fórmula matricial no pertenece únicamente a esa celda, sino que abarca el rango Q9:Q58.

Las fórmulas matriciales requieren tres pasos para establecerlas correctamente.

1. Paso 1. Seleccionar el rango donde la fórmula matricial actua. En este caso es el rango Q9;Q58
2. Paso 2. Escribir la fórmula matricial
3. Paso 3. Validar la fórmula pulsando simultáneamente las tres siguiente teclas: Contro+Shift+Enter

Función BUSCARV

Descargar el archivo excelavanzado_buscarv.xlsx

Una de las funciones más utilizadas en Excel, después de la de suma, es la función BUSCARV.

• BUSCARV nos permite hacer una búsqueda vertical en una tabla
• BUSCARH nos permite hacer una búsqueda horizontal en una tabla

Primer Vídeo

Segundo Vídeo

Tercer Vídeo

Función BUSCARV

Descargar el fichero: funcionBUSCARV.xlsx

Vamos a explicar cómo trabaja la función BUSCARV mediante un ejemplo.

Un tema importante es que cuando se utiliza la función BUSCARV con búsqueda por intervalos (por escalones), es imprescindible ordenar la primera columna de la tabla amarilla en orden ascendente (de menor a mayor). Esta primera columna acepta datos numéricos o texto. En el caso de que usemos texto, la necesidad de orden ascendente supone imponer orden alfabético. Para el caso de ordenación exacta no se necesitar orden ascendente en la primera columna de la tabla amarilla.

NOTA 1

En Excel 2010 la función BUSCARV se llama CONSULTAV, y la función BUSCARH se llama CONSULTAH.

NOTA 2

En Microsoft han rectificado ante las peticiones de los usuarios y han vuelto al nombre original de BUSCARV y BUSCARH. Esto se consigue si tienes instalado en el Office 2010 el Service Pack 1, o bien, si tienes activas las actualizaciones automáticas.

Alternativa a BUSCARV

Descargar el fichero: sinbuscarv.xlsx

Buscar en una tabla es una de las tareas más comunes por parte de todo gestor de información. La función BUSCARV permite una búsqueda vertical en una tabla. Existe otro función denominada BUSCARH que permite una búsqueda horizontal en una tabla. BuscarV permite búsquedas por intervalos o búsquedas exactas. Su utilización esta muy extendida, pero tiene algunos inconvenientes.

Basicamente son dos los problemas:

• En búsqueda por intervalos la primera columna debe estar ordenada de menor a mayor. Admite tanto valores numéricos como texto, en este caso el orden de menor a mayor supone orden alfabético. El problema radica en que en ocasiones no es posible tener los datos ordenados de esta forma.
• La tabla que constituye la base de datos requiere que la primera columna sea sobre la que luego se buscará, y todas las demás columnas deben estar a su derecha. Esto en ocasiones no es posible, y la columna que tiene la referencia principal no es la primera columna de la tabla.

Por estos motivos vamos a ver una alternativa a esta fución. Para ello utilizaremos las funciones DESREF y COINCIDIR.

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

La función DESREF se puede utilizar con los tres primeros agumentos o con los cinco. Si usamos todos los argumentos trabaja como una función matricial. Este no es nuestro caso. Nosotros aquí utilizaremos únicamente los tres primeros argumentos, en cuyo caso nos devuelve un valor de una celda. La forma de localizar el valor que devuelve es estableciendo una celda de referencia desde la que contar (ref), y desde ella contar hacia abajo un cierto número de filas, y hacia la derecha un cierto número de columnas. Si el número de fila fuera negativo se va hacia arriba. Si el número de columnas fuera negativo se va hacia la izquierda. Por eso en la fórmula:

=DESREF($E$4;COINCIDIR($B13;$E$5:$E$9;0);-3)

Al poner -3 columnas, esto permite movernos hacia la izquierda. Esto es importante, ya que nos permite tener la columna de referencia en el lugar que queramos y no necesariamente como primera columna.

La función COINCIDIR permite encontrar el orden que ocupa un valor que coincide con un elemento de una lista. Si existen dos o más valores nos da la coincidencia del primero.

=COINCIDIR(valor_buscado;matriz_buscada;tipo_de_coincidencia)

Doble BUSCARV

Descargar el fichero: DobleBuscarv.xlsx

La función BUSCARV es probablemente la función de Excel más utilizada por los gestores de información avanzados. Esta función permite efectuar una búsqueda vertical en una tabla. Existe la función BUSCARH que permite efectuar una búsqueda horizontal en una tabla. Esta función permite efectuar una búsqueda por intervalos o una búsqueda exacta. 

=BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

Supongamos una base de datos con Productos (Asfalto, Butano, Fueloil, Gasoil, Gasolina), Cliente, Mes (Ene, Feb, Mar), Litros y Precio. El precio es un campo calculado en base a una tabla de precios en la que se proporciona un precio distinto por cada producto y mes. Podemos calcular el precio por tres métodos.

Método 1

En este caso calculamos el precio con un doble BUSCARV. Utilizamos un Buscarv dentro de otro Buscarv. Hemos tenido que crear una tabla auxiliar denominada meses, en el rango L13:M15. Esta tabla auxiliar sirve para localizar la columna de la tabla de Precios donde esta el precio correspondiente al mes que buscamos.

Este método tiene el inconveniente de que tanto la tabla meses como la tabla precios debe estar ordenada de menor a mayor en su primera columna.

Método 2

Utilizamos otras fórmulas de excel, como DESREF y COINCIDIR

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

=COINCIDIR(valor_buscado;matriz_buscada;tipo_de_coincidencia)

Método 3

Utilizamos INDICE y COINCIDIR. 

=INDICE(matriz;núm_fila;núm_columna)

Los método 2 y 3 tienen la ventaja de no necesitar ordenar de menor a mayor los elementos de la primera columna de la tabla de precios.