¿Cómo crear un rango dinámico con nombre en Excel?
Normalmente, Rangos con nombre son muy útiles para los usuarios de Excel, puede definir una serie de valores en una columna, darle un nombre a esa columna y luego puede referirse a ese rango por nombre en lugar de sus referencias de celda. Pero la mayoría de las veces, necesita agregar nuevos datos para expandir los valores de datos de su rango referido en el futuro. En este caso, debe volver a Fórmulas > Administrador de nombre y redefina el rango para incluir el nuevo valor. Para evitar esto, puede crear un rango dinámico con nombre, lo que significa que no necesita ajustar las referencias de celda cada vez que agrega una nueva fila o columna a la lista.
Cree un rango dinámico con nombre en Excel creando una tabla
Cree un rango dinámico con nombre en Excel con la función
Cree un rango dinámico con nombre en Excel con código VBA
Cree un rango dinámico con nombre en Excel creando una tabla
Si está utilizando Excel 2007 o versiones posteriores, la forma más sencilla de crear un rango dinámico con nombre es crear una tabla de Excel con nombre.
Digamos que tiene un rango de datos siguientes que deben convertirse en rango dinámico con nombre.
1. En primer lugar, definiré los nombres de rango para este rango. Seleccione el rango A1: A6 e ingrese el nombre Fecha en el Nombre de buzón, entonces presione Participar llave. Para definir un nombre para el rango B1: B6 como Precio de venta de la misma manera. Al mismo tiempo, creo una fórmula = suma (precio de venta) en una celda en blanco, vea la captura de pantalla:
2. Seleccione el rango y haga clic recuadro > Mesa, ver captura de pantalla:
3. En la Crear mesa cuadro de aviso, marque Mi mesa tiene encabezados (si el rango no tiene encabezados, desmárquelo), haga clic en OK y los datos del rango se han convertido en tabla. Ver capturas de pantalla:
 |
 |
 |
4. Y cuando ingrese nuevos valores después de los datos, el rango con nombre se ajustará automáticamente y la fórmula creada también se cambiará también. Vea las siguientes capturas de pantalla:
 |
|
 |
Notas:
1. Los nuevos datos que ingresa deben estar junto a los datos anteriores, lo que significa que no hay filas o columnas en blanco entre los nuevos datos y los datos existentes.
2. En la tabla, puede insertar datos entre los valores existentes.
Cree un rango dinámico con nombre en Excel con la función
En Excel 2003 o una versión anterior, el primer método no estará disponible, así que aquí hay otra forma para usted. El seguimiento COMPENSAR( ) La función puede hacerte este favor, pero es algo problemático. Suponiendo que tengo un rango de datos que contiene los nombres de rango que he definido, por ejemplo, A1: A6 el nombre del rango es Fechay B1: B6 el nombre del rango es Precio de venta, al mismo tiempo, creo una fórmula para el Precio de venta. Ver captura de pantalla:
Puede cambiar los nombres de rango a nombres de rango dinámico con los siguientes pasos:
1. Ir a hacer clic Fórmulas > Administrador de nombre, ver captura de pantalla:
2. En la Administrador de nombre cuadro de diálogo, seleccione el elemento que desea utilizar y haga clic en Editar del botón.
3. En el estalló Editar nombre diálogo, ingrese esta fórmula = COMPENSACIÓN (Hoja1! $ A $ 1, 0, 0, CONTAR ($ A: $ A), 1) en el Se refiere a cuadro de texto, ver captura de pantalla:
4. Luego haga clic OK, y luego repita los pasos 2 y 3 para copiar esta fórmula = COMPENSACIÓN (Hoja1! $ B $ 1, 0, 0, CONTAR ($ B: $ B), 1) en el Se refiere a cuadro de texto para el Precio de venta nombre del rango.
5. Y se han creado los rangos dinámicos con nombre. Cuando ingresa nuevos valores después de los datos, el rango con nombre se ajustará automáticamente y la fórmula creada también se cambiará también. Ver capturas de pantalla:
|
|
|
Nota: Si hay celdas en blanco en el medio de su rango, el resultado de su fórmula será incorrecto. Esto se debe a que las celdas que no están en blanco no se cuentan, por lo que su rango será más corto de lo que debería, y las últimas celdas del rango se dejarán fuera.
Consejo: explicación de esta fórmula:
- = DESPLAZAMIENTO (referencia, filas, columnas, [alto], [ancho])
- = COMPENSACIÓN (Hoja1! $ A $ 1, 0, 0, CONTAR ($ A: $ A), 1)
- referencia corresponde a la posición de la celda inicial, en este ejemplo Hoja1! $ A $ 1;
- fila se refiere al número de filas que se moverá hacia abajo, en relación con la celda inicial (o hacia arriba, si usa un valor negativo), en este ejemplo, 0 indica que la lista comenzará desde la primera fila hacia abajo
- visión de conjunto corresponde al número de columnas que se moverá hacia la derecha, en relación con la celda inicial (o hacia la izquierda, usando un valor negativo), en la fórmula de ejemplo anterior, 0 indica expandir 0 columnas hacia la derecha.
- [altura] corresponde a la altura (o número de filas) del rango comenzando en la posición ajustada. $ A: $ A, contará todos los elementos ingresados en la columna A.
- [anchura] corresponde al ancho (o número de columnas) del rango comenzando en la posición ajustada. En la fórmula anterior, la lista tendrá una columna de ancho.
Puede cambiar estos argumentos según sus necesidades.
Cree un rango dinámico con nombre en Excel con código VBA
Si tiene varias columnas, puede repetir e ingresar fórmulas individuales para todas las columnas restantes, pero eso sería un proceso largo y repetitivo. Para facilitar las cosas, puede utilizar un código para crear el rango dinámico con nombre automáticamente.
1. Active su hoja de trabajo.
2. Mantenga pulsado el ALT + F11 llaves, y abre el Ventana de Microsoft Visual Basic para aplicaciones.
3. Hacer clic recuadro > Móduloy pegue el siguiente código en el Ventana de módulo.
Código vba: crear rango dinámico con nombre
Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
myName = Replace(Cells(Rowno, i).Value, " ", "_")
If myName <> "" Then
wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
End If
Next
End Sub
4. Entonces presione F5 clave para ejecutar el código, y se generarán algunos rangos dinámicos con nombre que se nombran con los valores de la primera fila y también crea un rango dinámico llamado Mis datos que cubre todos los datos.
5. Cuando ingresa nuevos valores después de las filas o columnas, el rango también se expandirá. Ver capturas de pantalla:
 |
|
 |
Notas:
1. Con este código, los nombres de rango no se muestran en el Nombre de buzón, para ver y usar los nombres de rango convenientemente, he instalado Kutools for Excel, Con su Panel de exploración, se enumeran los nombres de rango dinámico creados.
2. Con este código, todo el rango de datos se puede expandir vertical u horizontalmente, pero recuerde que no debe haber filas o columnas en blanco entre los datos cuando ingresa nuevos valores.
3. Cuando usa este código, su rango de datos debe comenzar en la celda A1.
Artículo relacionado:
¿Cómo actualizar automáticamente un gráfico después de ingresar nuevos datos en Excel?
Las mejores herramientas de productividad de oficina
Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haga clic aquí para obtener la función que más necesita...
Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
- ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
