¿Cómo crear un rango con nombre dinámico en Excel?
Normalmente, los Rangos con Nombre son muy útiles para los usuarios de Excel. Puedes definir una serie de valores en una columna, darle un nombre a esa columna y luego referirte a ese rango por su nombre en lugar de usar referencias de celda. Sin embargo, la mayoría de las veces necesitarás agregar nuevos datos para expandir los valores de tu rango referido en el futuro. En este caso, tendrías que volver a Fórmulas > Administrador de Nombres y redefinir el rango para incluir el nuevo valor. Para evitar esto, puedes crear un rango con nombre dinámico, lo que significa que no necesitas ajustar las referencias de celda cada vez que agregas una nueva fila o columna a la lista.
Crear un rango con nombre dinámico en Excel creando una tabla
Crear un rango con nombre dinámico en Excel con Función
Crear un rango con nombre dinámico en Excel con código VBA
Crear un rango con nombre dinámico en Excel creando una tabla
Si estás utilizando Excel 2007 o versiones posteriores, la forma más fácil de crear un rango con nombre dinámico es crear una tabla de Excel con nombre.
Supongamos que tienes un rango de los siguientes datos que necesitan convertirse en un rango con nombre dinámico.
1. Primero, definiré nombres de rango para este rango. Selecciona el rango A1:A6 e introduce el nombre Fecha en el Cuadro de Nombres, luego presiona la tecla Enter. Define un nombre para el rango B1:B6 como PrecioVenta de la misma manera. Al mismo tiempo, creo una fórmula =suma(PrecioVenta) en una celda en blanco, ver captura de pantalla:
2. Selecciona el rango y haz clic en Insertar > Tabla, ver captura de pantalla:
3. En el cuadro de diálogo Crear Tabla, marca Mi tabla tiene encabezados (si el rango no tiene encabezados, desmarca esta opción), haz clic en el botón Aceptar, y los datos del rango se habrán convertido en una tabla. Ver capturas de pantalla:
 |  |  |
4. Y cuando ingreses nuevos valores después de los datos, el rango con nombre se ajustará automáticamente y la fórmula creada también cambiará. Ver las siguientes capturas de pantalla:
 | |  |
Notas:
1. Tus nuevos datos deben estar adyacentes a los datos anteriores, lo que significa que no debe haber filas o columnas en blanco entre los nuevos datos y los datos existentes.
2. En la tabla, puedes insertar datos entre los valores existentes.
Crear un rango con nombre dinámico en Excel con Función
En Excel 2003 o versiones anteriores, el primer método no estará disponible, así que aquí hay otra forma para ti. La siguiente OFFSET( ) función puede hacer esto por ti, pero es algo complicado. Supongamos que tengo un rango de datos que contiene los nombres de rango que he definido, por ejemplo, A1:A6 el nombre del rango es Fecha, y el nombre del rango B1:B6 es PrecioVenta, al mismo tiempo, creo una fórmula para PrecioVenta. Ver captura de pantalla:
Puedes cambiar los nombres de rango a nombres de rango dinámicos con los siguientes pasos:
1. Ve a hacer clic en Fórmulas > Administrador de Nombres, ver captura de pantalla:
2. En el cuadro de diálogo Administrador de Nombres, selecciona el elemento que deseas usar y haz clic en el botón Editar.
3. En el cuadro de diálogo Editar Nombre emergente, introduce esta fórmula =OFFSET(Hoja1!$A$1, 0, 0, CONTARA($A:$A), 1) en el cuadro de texto Se refiere a, ver captura de pantalla:
4. Luego haz clic en Aceptar, y repite el paso 2 y el paso 3 para copiar esta fórmula =OFFSET(Hoja1!$B$1, 0, 0, CONTARA($B:$B), 1) en el cuadro de texto Se refiere a para el nombre de rango PrecioVenta.
5. Y se han creado los rangos con nombre dinámicos. Cuando ingreses nuevos valores después de los datos, el rango con nombre se ajustará automáticamente y la fórmula creada también cambiará. Ver capturas de pantalla:
| | |
Nota: Si hay celdas en blanco en medio de tu rango, el resultado de tu fórmula será incorrecto. Esto se debe a que las celdas no vacías no se cuentan, por lo que tu rango será más corto de lo que debería ser y las últimas celdas del rango quedarán fuera.
Consejo: explicación de esta fórmula:
- =OFFSET(referencia,filas,columnas,[altura],[anchura])
- =OFFSET(Hoja1!$A$1, 0, 0, CONTARA($A:$A), 1)
- referencia corresponde a la posición inicial de la celda, en este ejemplo Hoja1!$A$1;
- fila se refiere al número de filas que vas a mover hacia abajo, relativo a la celda inicial (o hacia arriba, si usas un valor negativo). En este ejemplo, 0 indica que la lista comenzará desde la primera fila hacia abajo.
- columna corresponde al número de columnas que moverás a la derecha, relativo a la celda inicial (o a la izquierda, usando un valor negativo). En la fórmula del ejemplo anterior, 0 indica expandir 0 columnas a la derecha.
- [altura] corresponde a la altura (o número de filas) del rango que comienza 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 que comienza en la posición ajustada. En la fórmula anterior, la lista tendrá un ancho de 1 columna.
Puedes cambiar estos argumentos según tus necesidades.
Crear un rango con nombre dinámico en Excel con código VBA
Si tienes múltiples columnas, podrías repetir e ingresar fórmulas individuales para todas las columnas restantes, pero sería un proceso largo y repetitivo. Para facilitar las cosas, puedes usar un código para crear automáticamente el rango con nombre dinámico.
1. Activa tu hoja de trabajo.
2. Mantén presionadas las teclas ALT + F11, y se abrirá la ventana Microsoft Visual Basic para Aplicaciones.
3. Haz clic en Insertar > Módulo, y pega el siguiente código en la Ventana de Módulo.
Código VBA: crear rango con nombre dinámico
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. Luego presiona la tecla F5 para ejecutar el código, y se generarán algunos rangos con nombre dinámico que están nombrados con los valores de la primera fila y también crea un rango dinámico llamado MisDatos que cubre todos los datos.
5. Cuando ingreses 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 Cuadro de Nombres; para ver y usar convenientemente los nombres de rango, he instalado Kutools para Excel, con su Panel de Navegación, los nombres de rango dinámico creados están listados.
2. Con este código, todo el rango de datos puede expandirse vertical u horizontalmente, pero recuerda que no debe haber filas o columnas en blanco entre los datos cuando ingreses nuevos valores.
3. Cuando uses este código, tu 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 para Office
Mejora tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab incorpora la interfaz de pestañas en Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!
Todos los complementos de Kutools. Un solo instalador
El paquete Kutools for Office agrupa complementos para Excel, Word, Outlook y PowerPoint junto con Office Tab Pro, ideal para equipos que trabajan en varias aplicaciones de Office.
- Suite todo en uno: complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un solo instalador, una licencia: configuración en minutos (compatible con MSI)
- Mejor juntos: productividad optimizada en todas las aplicaciones de Office
- Prueba completa de30 días: sin registro ni tarjeta de crédito
- La mejor relación calidad-precio: ahorra en comparación con la compra individual de complementos