Introducción a los rangos dinámicos
La función BUSCARV se utiliza a menudo para buscar información almacenada en tablas en Excel. Por ejemplo, si tenemos una lista de nombres y edades de personas:
Y luego podemos en una celda cercana usar la función BUSCARV para determinar la edad de Paul:
Hasta ahora, esto es bastante estándar. Pero, ¿qué pasa si necesitamos agregar más nombres a la lista? La idea obvia sería modificar el rango en BUSCARV. Sin embargo, en un modelo realmente complejo, puede haber varias referencias a BUSCARV. Esto significa que tendríamos que cambiar cada referencia, suponiendo que supiéramos dónde estaban.
Sin embargo, Excel proporciona una forma alternativa, llamada rango DINÁMICO. Este es un rango que se expande y se actualiza automáticamente. Esto es perfecto si sus listas se expanden constantemente (por ejemplo, datos de ventas de mes a mes).
Para configurar un rango dinámico, necesitamos tener un nombre de rango, por lo que llamaremos al nuestro AGE_DATA. El enfoque para configurar rangos dinámicos difiere entre Excel 2007 y versiones anteriores de Excel:
En Excel 2007, haga clic en "Definir nombre" en las fórmulas:
En versiones anteriores de Excel, haga clic en "Insertar" y luego en Nombres ".
En el cuadro emergente, ingrese el nombre de nuestro rango dinámico, que es "DATOS DE EDAD":
En el cuadro etiquetado "Se refiere a", debemos ingresar el rango de nuestros datos. Este será un logro utilizado por una función OFFSET. Esto tiene 5 argumentos:
= DESPLAZAMIENTO (Referencia, Filas, Columnas, Alto, Ancho)
- La referencia es la dirección de la esquina SUPERIOR IZQUIERDA de nuestro rango, en este caso la celda B5
- Las Filas es el número de filas desde ARRIBA IZQUIERDA que queremos que sea ese rango, que será 0 en este caso
- Cols es el número de filas desde ARRIBA IZQUIERDA que queremos que sea ese rango, que será 0 en este caso
- La altura del rango - ver más abajo para esto
- El ancho del rango: esto es 2 si tenemos DOS columnas en nuestro rango (el nombre de las personas y su edad)
Ahora la altura del rango tendrá que variar dependiendo del número de entradas en nuestra tabla (que actualmente es 7).
Por supuesto, queremos una forma de contar las filas en nuestra tabla que se actualice automáticamente, por lo que una forma de hacerlo es usar la función CONTAR. Esto solo cuenta el número de celdas que no están en blanco en un rango. Como nuestros nombres están en la columna B, el número de entradas en nuestros datos es COUNTA (B: B).
Tenga en cuenta que si pusiera esto en una celda, obtendría el valor 8, ya que incluye los nombres del encabezado. Sin embargo, eso es irrelevante.
Así que en el cuadro "Se refiere a" ponemos:
= COMPENSACIÓN ($ B $ 5,0,0, cuenta (B: B), 2)
Y haga clic en el botón Aceptar. Ahora se crea nuestro rango dinámico.
Ahora regrese a las fórmulas VLOOKUP y reemplace el rango $ B: 4: $ C11 con el nombre de nuestro nuevo rango dinámico AGE_DATA para que tengamos:
Hasta ahora nada ha cambiado. Sin embargo, si agregamos algunos nombres más a nuestra tabla:
Y en la celda donde teníamos a Paul, reemplácelo con un nuevo nombre como Pedro (que no estaba en la lista original):
Y vemos que Excel ha devuelto automáticamente la edad de Pedro, aunque no hemos cambiado las fórmulas de BUSCARV. En cambio, el alcance del rango dinámico ha aumentado para incluir los nombres adicionales.
Los rangos dinámicos son muy útiles cuando tenemos volúmenes crecientes de datos, especialmente cuando se requieren tablas VLOOKUP y PIVOT.
