BUSCARV de Excel con referencia de columna dinámica

Suponga que cada vez que trabaja con tablas de datos colosales y desea implantar una función BUSCARV que se actualiza dinámicamente a la parte adjunta a medida que la copia. En ese caso, BUSCARV con la capacidad de COLUMNAS es lo que realmente te importa. Eso es; el col_index_num, una parte de la capacidad de BUSCARV, revive intensamente a medida que lo copia en su hoja de trabajo. Antes de aprender BUSCARV con referencia de columna dinámica, primero aprendamos qué es la función BUSCARV,

BUSCARV

BUSCARV se utiliza cuando se necesita rastrear el valor de una celda/los datos de esa celda que están disponibles en otro lugar (también pueden estar disponibles en otra hoja). La siguiente es la capacidad de BUSCARV,

=BUSCARV(valor_buscado,array_tabla,núm_índice_col,[búsqueda_rango])

Ejemplo:

De acuerdo, deberíamos imaginar que este resumen tiene cientos o incluso una cantidad considerable de secciones y realmente queremos encontrar una estrategia rápida para obtener información sobre los gastos y el fabricante a pedido (que no sea CTRL + F para Buscar con claridad). En la tabla de abajo tenemos el título del libro, el autor y el precio.

El título del libro se encuentra utilizando una lista de validación de datos o una lista desplegable, y en las secciones C y D queremos una ecuación BUSCARV que encuentre el autor y el precio por separado. Podríamos hacer esto utilizando una receta estándar de BUSCARV como esta: En la celda C16,

Además, en la celda D16,

Sin embargo, queremos alterar físicamente la referencia de la sección para cada segmento en el que duplicamos BUSCARV. Además, suponiendo que tenemos una tonelada de segmentos que debemos examinar, entonces, en ese punto, en lugar de codificar el número de referencia de la sección, es mucho más rápido utilizar la capacidad de COLUMNAS de esta manera: en la celda C16,

Además, en la celda D16

Nota: Asegúrese de utilizar COLUMNAS (plural) y no COLUMNAS, ya que funcionan de forma inesperada.

Función COLUMNAS

La capacidad COLUMNAS devuelve la cantidad de secciones en un grupo. La estructura del lenguaje es =COLUMNAS(array), donde ‘cluster’ es el rango de la sección. Por ejemplo:

=COLUMNAS($B4:B4)

Nos da 1. Por ejemplo, la exhibición tiene 1 sección de ancho. Entonces, todo lo que realmente estamos haciendo es calcular el número de segmento que necesitamos utilizando la función COLUMNAS.

Nota: $B4:B4 podría ser simplemente B:B como este =COLUMNAS($B:B), ya que no hace ninguna diferencia exactamente cuál es la referencia de la columna.

BUSCARV usando la función COLUMNAS explicada

En nuestra ecuación BUSCARV anterior (en la celda C16), nuestra receta COLUMNAS es COLUMNAS ($B4: B4) + 1, porque,

• El segmento que necesitamos es la segunda sección de nuestra tabla BUSCARV,
• Es más, dado que =COLUMNAS($B4:B4) da como resultado 1, realmente queremos sumar 1 para obtener 2 para el segmento subsiguiente.

En la función BUSCARV en la celda D16, nuestra capacidad COLUMNAS es COLUMNAS ($B4: C4) + 1 y se evalúa como 2 + 1 = 3. Hemos utilizado una referencia de celda directa en $B4:C4 (=2) de modo que cuando replicamos la receta BUSCARV frente a C16 a D16, las COLUMNAS, una parte de la ecuación, se incrementará naturalmente en 1 (de $B4:B4 a $B4:C4) para darnos el número de segmento correcto.

Búsqueda bidireccional con BUSCARV (coincidencia)

Dentro de la función BUSCARV, la contención del registro de sección se codifica regularmente como un número estático. Sea como fuere, también puede crear un archivo de segmento único utilizando la capacidad MATCH para encontrar la sección correcta. Esta estrategia le permite realizar una consulta bidireccional potente, haciendo coincidir las dos líneas y secciones. También puede hacer que una fórmula de BUSCARV sea más sólida: BUSCARV puede fallar cuando las secciones se incrustan o eliminan de una tabla, sin embargo, una ecuación con BUSCARV + COINCIDIR puede seguir funcionando con precisión incluso si se realizan cambios en los segmentos.

En el modelo, estamos utilizando esta receta para consultar progresivamente las dos líneas y secciones con BUSCARV:

=BUSCARV(G4,B1:D11,COINCIDIR(G5,B1:D1,0),0)

Explicación

Esta es una ecuación de coordenadas cuidadosa de BUSCARV estándar con una excepción: el archivo de sección lo proporciona la capacidad COINCIDIR.

Tenga en cuenta que el grupo de consulta dado a MATCH (B1:D1) que direcciona los encabezados de la sección incorpora intencionalmente la celda vacante B1. Esto se hace para que el número devuelto por COINCIDIR esté en un estado de armonía con la tabla utilizada por BUSCARV. Como tal, realmente desea darle a MATCH un alcance que atraviese una cantidad similar de segmentos que VLOOKUP está involucrando en la tabla. En el modelo (para febrero), COINCIDIR devuelve 3, por lo que después de ejecutar COINCIDIR, la receta BUSCARV parece ser la siguiente:

=BUSCARV(G4,B1:D11,3,0)

Que devuelve ofertas para B (línea 3) en febrero (sección 4), que es de $41.