Como Expandir automáticamente el rango de datos BUSCARV
El segundo argumento en la función BUSCARV es table_array. Este es el rango de celdas que contiene los datos que se utilizarán dentro de la función BUSCARV. Por ejemplo, en el código siguiente A1:C9 está table_array.
=BUSCARV("O Redding",A1:C9,3,FALSO)
Cuando usamos la función BUSCARV, a menudo sabemos cuántas filas hay en los datos, por lo tanto, sabemos qué referencias de celda incluir en table_array. Pero también hay circunstancias en las que se agregarán filas adicionales a los datos existentes, por lo que no se conoce el rango exacto de datos. Si ha experimentado esto, probablemente haya seguido uno de los siguientes métodos:
- Se incluye un table_array que es significativamente más grande de lo requerido para que se puedan agregar datos futuros (este método puede resultar en tiempos de cálculo más lentos).
- Se actualizó el argumento table_array cada vez que los datos cambian para incluir datos adicionales (este método requiere actualizaciones/cambios periódicos en la fórmula BUSCARV).
- Insertó las nuevas filas en el table_array existente, en lugar de en la parte inferior (este método requiere que recuerde insertarlas en el medio y no en la parte inferior. Además, ¿cómo sabrán otros usuarios agregar los nuevos datos en el medio)?
Pero, ¿sabías que existen otras formas? Formas que, una vez configuradas, no requieren la actualización de la función BUSCARV ni la ubicación inteligente de los datos. En esta publicación, consideraremos estas opciones:
- Tablas de Excel
- Rangos dinámicos con nombre
Tablas de Excel
Una tabla de Excel es una característica que a menudo se malinterpreta, pero no entraremos en detalles aquí. El punto clave de esta publicación es que una tabla se expande o retrae automáticamente para ajustarse a los datos.
Montaje de la mesa
Para configurar una tabla, simplemente resalte las celdas y haga clic en Insertar – Tabla (atajo: Ctrl + T)
Se abrirá la ventana Crear tabla.
Nuestros datos tienen encabezados y están dentro del rango mostrado, por lo que podemos hacer clic en Aceptar.
La tabla ahora tiene un formato automático para tener franjas (esto se puede cambiar fácilmente si así lo desea).
Cambiaremos el nombre de la tabla por un nombre más útil. Haga clic en cualquier lugar de la tabla, luego haga clic en Herramientas de tabla: Diseño en la cinta. Cambie el nombre de la tabla en el cuadro Nombre de la tabla.
Usando la mesa
Ahora podemos usar la tabla “TelephoneList” como table_array dentro de BUSCARV.
La celda F4 incluye la siguiente fórmula:
=BUSCARV(F2,ListaTeléfonos,3,FALSO)
Cada vez que se agregan nuevas filas a los datos, la fuente de BUSCARV se actualizará automáticamente para incluir estas celdas. En la captura de pantalla a continuación, B Adams se agregó a las celdas A10-C10 y "TelephoneList" también se actualizó automáticamente. B Adams ahora se puede encontrar en BUSCARV sin necesidad de cambiar la fórmula BUSCARV.
Rangos dinámicos con nombre
Un rango con nombre es un grupo de celdas al que se puede hacer referencia con otro nombre. Por ejemplo, puede hacer clic en las celdas A1-C9 y darles el nombre "myRange". "myRange" ahora se puede usar dentro de fórmulas en lugar de hacer referencia a A1-C9.
Para configurar un rango con nombre, usaría uno de dos métodos
Configuración rápida de rango con nombre
Para configurar rápidamente un rango con nombre, simplemente resalte las celdas y escriba el nombre en el cuadro de rango.
Definiendo el nombre
Para una configuración más detallada, utilice la sección Definir nombre de la cinta Fórmulas.
Usando los menús, haga clic en: Fórmulas – Definir nombres – Definir nombre.
Aparecerá una nueva ventana en la que podrá ingresar la información para su rango con nombre. Luego haga clic en Aceptar.
Configurar un rango dinámico con nombre
Pero podemos dar un paso más. Podemos usar fórmulas para permitir que Excel determine el tamaño que debe tener el rango con nombre, esto se denomina rango con nombre dinámico. Al utilizar un rango con nombre dinámico, se expandirá automáticamente para incluir cualquier dato nuevo agregado en la parte inferior.
Estos rangos dinámicos con nombres experimentan problemas cuando hay celdas en blanco o cuando hay datos adicionales insertados debajo de la tabla de búsqueda. A continuación se utilizan dos métodos diferentes para abordar estos dos problemas.
Para cualquiera de las opciones, configure el rango dinámico con nombre definiendo el nombre. Pero en lugar de usar referencias a un rango de celdas, usaremos la función ÍNDICE para crear el rango.
OPCIÓN 1: Base el rango en la última celda utilizada en una columna.
Mire la función utilizada en este ejemplo.
=Hoja1!$A$1:ÍNDICE(Hoja1!$C:$C,MAX((Hoja1!$C:$C"")*(FILA(Hoja1!$C:$C))))
Esta es una fórmula matricial, así que presione Ctrl + Shift + Enter para ingresar la fórmula en el cuadro Se refiere a:.
La fórmula comienza con el nombre de la hoja y una referencia a la primera celda. La función ÍNDICE se utiliza junto con las funciones MAX y FILA para devolver la dirección de la última celda utilizada en la columna. En el ejemplo anterior, la función ÍNDICE devuelve una referencia de celda de la columna C.
Esta fórmula todavía funciona donde hay celdas en blanco, pero puede crear rangos más grandes de lo necesario si hay celdas utilizadas debajo de lookup_data.
OPCIÓN 2: Cuente el número de celdas en una columna
Mire la función utilizada en este ejemplo.
=Hoja1!$A$1:ÍNDICE(Hoja1!$C:$C,CONTARA(Hoja1!$C:$C))
La fórmula comienza con el nombre de la hoja y una referencia a la primera celda. Luego, la función ÍNDICE se usa junto con las funciones CONTARA para devolver la dirección en función del número de celdas que no están en blanco. En el ejemplo anterior, la función ÍNDICE devuelve una referencia de celda de la columna C.
Esta fórmula no funciona correctamente cuando hay celdas en blanco, ya que puede crear un rango más pequeño de lo necesario.
Usando un rango dinámico con nombre con BUSCARV
Ahora podemos usar "myRange" como table_array dentro de BUSCARV.
La celda F4 incluye la siguiente fórmula:
=BUSCARV(F2,miRango,3,FALSO)
Cada vez que se agregan nuevas filas a los datos, BUSCARV se actualizará automáticamente para incluir estas celdas. B Adams se agregó a las celdas A10-C10 y "myRange" también se actualizó automáticamente. B Adams ahora se puede encontrar en BUSCARV.
Podría seguir agregando datos hasta 500 filas y BUSCARV funcionaría. Si desea más de 500 filas, simplemente actualice las referencias de celda a la función ÍNDICE dentro de "myRange".
Descargue la hoja de referencia de BUSCARV avanzada
Descargue la hoja de referencia de BUSCARV avanzada. Incluye la mayoría de los consejos y trucos que hemos cubierto en esta serie, incluidos cálculos más rápidos, criterios múltiples, búsqueda por la izquierda y mucho más.
Descárgalo y fíjalo en el trabajo; incluso puedes reenviarlo a tus amigos y compañeros de trabajo.
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0166 BUSCARV Avanzada.pdf
Tener acceso
Otras publicaciones en la serie Mastering VLOOKUP
- Cómo utilizar BUSCARV
- BUSCARV: ¿Qué hace la declaración Verdadero/Falso?
- BUSCARV: Cómo calcular más rápido
- Cómo BUSCAR V a la izquierda
- BUSCARV: cambia el número de columna automáticamente
- BUSCARV con múltiples criterios
- Usando comodines con BUSCARV
- Cómo usar BUSCARV con columnas y filas
- Expandir automáticamente el rango de datos BUSCARV
- BUSCARV: busque el enésimo elemento (sin columnas auxiliares)
- BUSCARV: enumera todos los elementos coincidentes
- Hoja de referencia avanzada de BUSCARV
Deja una respuesta