Como Expandir automáticamente el rango de datos BUSCARV

Índice
  1. Tablas de Excel
    1. Montaje de la mesa
    2. Usando la mesa
  2. Rangos dinámicos con nombre
    1. Configuración rápida de rango con nombre
    2. Definiendo el nombre
    3. Configurar un rango dinámico con nombre
    4. Usando un rango dinámico con nombre con BUSCARV
  3. Descargue la hoja de referencia de BUSCARV avanzada
  4. Otras publicaciones en la serie Mastering VLOOKUP

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:

  1. Tablas de Excel
  2. 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)

BUSCARV Ampliar automáticamente Insertar tabla

Se abrirá la ventana Crear tabla.

BUSCARV Ampliar automáticamente 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).

BUSCARV Expandir automáticamente nueva tabla

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.

BUSCARV Cambio de nombre de tabla de expansión automática

Usando la mesa

Ahora podemos usar la tabla “TelephoneList” como table_array dentro de BUSCARV.

BUSCARV Función de expansión automática de tabla

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.

BUSCARV Auto Expandir nuevos datos

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.

BUSCARV rango con nombre rápido

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.

BUSCARV rango con nombre nombre definido

Aparecerá una nueva ventana en la que podrá ingresar la información para su rango con nombre. Luego haga clic en Aceptar.

BUSCARV rango con nombre - nuevo rango

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.

BUSCARV Rango de expansión automática: nombre dinámico

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

BUSCARV Ampliación automática CONTARA - Nombre dinámico

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.

BUSCARV Rango dinámico con nombre en fórmula

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.

BUSCARV Rango con nombre dinámico Nueva línea

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.

Hoja de referencia avanzada de BUSCARV

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

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Subir

Este sitio web utiliza cookies para mejorar la experiencia del usuario y garantizar un funcionamiento eficiente. Al utilizar nuestro sitio web, aceptas todas las cookies de acuerdo con nuestra política de cookies. Leer mas...