Sus datos de Excel cambian con frecuencia, por lo que es útil crear un rango dinámico definido que se expanda y contraiga automáticamente al tamaño de su rango de datos. Veamos cómo.
Al utilizar un rango definido dinámico, no necesitará editar manualmente los rangos de sus fórmulas, gráficos y tablas dinámicas cuando cambien los datos. Esto sucederá automáticamente.
Se utilizan dos fórmulas para crear rangos dinámicos: OFFSET e INDEX. Este artículo se centrará en el uso de la función INDICE, ya que es un enfoque más eficiente. OFFSET es una función volátil y puede ralentizar hojas de cálculo grandes.
Tabla de contenido
Cree un rango definido dinámico en Excel
Para nuestro primer ejemplo, tenemos la lista de datos de una sola columna que se ve a continuación.
Necesitamos que esto sea dinámico para que si se agregan o eliminan más países, el rango se actualice automáticamente.
Para este ejemplo, queremos evitar la celda de encabezado. Como tal, queremos el rango $ A $ 2: $ A $ 6, pero dinámico. Haga esto haciendo clic en Fórmulas> Definir nombre.
Escriba «países» en el cuadro «Nombre» y luego ingrese la fórmula a continuación en el cuadro «Se refiere a».
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Escribir esta ecuación en una celda de la hoja de cálculo y luego copiarla en el cuadro Nombre nuevo es a veces más rápido y fácil.
¿Como funciona esto?
La primera parte de la fórmula especifica la celda de inicio del rango (A2 en nuestro caso) y luego sigue el operador de rango (:).
=$A$2:
El uso del operador de rango obliga a la función INDICE a devolver un rango en lugar del valor de una celda. Luego, la función INDICE se usa con la función CONTAR. COUNTA cuenta el número de celdas que no están en blanco en la columna A (seis en nuestro caso).
INDEX($A:$A,COUNTA($A:$A))
Esta fórmula solicita a la función INDICE que devuelva el rango de la última celda que no está en blanco en la columna A ($ A $ 6).
El resultado final es $ A $ 2: $ A $ 6, y debido a la función CONTAR, es dinámico, ya que encontrará la última fila. Ahora puede usar este nombre definido por “países” dentro de una regla, fórmula, gráfico de validación de datos o donde necesitemos hacer referencia a los nombres de todos los países.
Cree un rango definido dinámico bidireccional
El primer ejemplo fue solo dinámico en altura. Sin embargo, con una ligera modificación y otra función CONTAR, puede crear un rango que sea dinámico tanto en altura como en ancho.
En este ejemplo, usaremos los datos que se muestran a continuación.
Esta vez, crearemos un rango dinámico definido, que incluye los encabezados. Haga clic en Fórmulas> Definir nombre.
Escriba «ventas» en el cuadro «Nombre» e ingrese la fórmula siguiente en el cuadro «Se refiere a».
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Esta fórmula usa $ A $ 1 como celda inicial. Luego, la función INDICE usa un rango de la hoja de trabajo completa ($ 1: $ 1048576) para buscar y regresar.
Una de las funciones COUNTA se usa para contar las filas que no están en blanco y otra se usa para las columnas que no están en blanco, lo que la hace dinámica en ambas direcciones. Aunque esta fórmula comenzó desde A1, podría haber especificado cualquier celda de inicio.
Ahora puede usar este nombre definido (ventas) en una fórmula o como una serie de datos de gráficos para hacerlos dinámicos.