Sus datos de Excel cambian a menudo, por lo que es útil crear un rango definido dinámico 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 usan dos fórmulas para crear rangos dinámicos: OFFSET e INDEX. Este post se centrará en el uso de la función INDICE, puesto que es un enfoque más eficiente. OFFSET es una función volátil y puede ralentizar hojas de cálculo grandes.
Cree un rango dinámico definido 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> Establecer nombre.
Escriba «países» en el cuadro «Nombre» y después 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 después 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 después 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. La función ÍNDICE se utiliza después con la función CONTAR. CONTAR 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, puesto que encontrará la última fila. Ahora puede utilizar este nombre definido por “países” dentro de una regla de validación de datos, fórmula, gráfico 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. A pesar de esto, 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> Establecer nombre.
Escriba «ventas» en el cuadro «Nombre» e ingrese la fórmula a continuación en el cuadro «Se refiere a».
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Esta fórmula utiliza $ A $ 1 como celda de inicio. Posteriormente, la función INDICE utiliza un rango de toda la hoja de trabajo ($ 1: $ 1048576) para buscar y regresar.
Una de las funciones COUNTA se utiliza para contar las filas que no están en blanco, y otra se utiliza para las columnas que no están en blanco, lo que la hace dinámica en ambas direcciones. Aún cuando esta fórmula comenzó desde A1, podría haber especificado cualquier celda de inicio.
Ahora puede utilizar este nombre definido (ventas) en una fórmula o como una serie de datos de gráficos para hacerlos dinámicos.
