¿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, asignarle un nombre a dicha columna y, posteriormente, hacer referencia a ese rango mediante su nombre en lugar de usar referencias de celda. Sin embargo, en la mayoría de los casos necesitarás añadir nuevos datos para ampliar los valores del rango al que haces referencia en el futuro. En ese caso, tendrás que volver a Fórmulas > Administrador de nombres y redefinir el rango para incluir los nuevos valores. Para evitar esto, puedes crear un rango con nombre dinámico, lo que significa que no tendrás que ajustar las referencias de celda cada vez que añadas una nueva fila o columna a la lista.
Crear un rango con nombre dinámico en Excel mediante la creación de una tabla
Crear un rango con nombre dinámico en Excel con una función
Crear un rango con nombre dinámico en Excel con código VBA
Crear un rango con nombre dinámico en Excel mediante la creación de una tabla
Si utiliza Excel 2007 o versiones posteriores, la forma más sencilla de crear un rango con nombre dinámico es mediante una tabla de Excel con nombre.
Supongamos que dispone de un rango con los siguientes datos que deben convertirse en un rango con nombre dinámico.
1. En primer lugar, voy a definir un nombre de celda para este rango. Seleccione el rango A1:A6 e introduzca el nombre Fecha en el Cuadro de nombres y pulse la tecla Intro. A continuación, defina también un nombre para el rango B1:B6 como PrecioVenta, siguiendo el mismo procedimiento. Al mismo tiempo, cree una fórmula =SUMA(PrecioVenta) en una celda vacía; consulte la captura de pantalla:
2. Seleccione el rango y haga clic en Insertar > Tabla; consulte la captura de pantalla:
3. En el cuadro de diálogo Crear tabla, active la casilla Mi tabla tiene encabezados (si el rango no tiene encabezados, desmárquela) y haga clic en el botón Aceptar; los datos del rango se convertirán en una tabla. Consulte las capturas de pantalla:
 |  |  |
4. Y cuando introduzca nuevos valores tras los datos existentes, el rango con nombre se ajustará automáticamente y la fórmula creada también se actualizará. Consulte las siguientes capturas de pantalla:
 | |  |
Notas:
1. Los nuevos datos que introduzca deben estar adyacentes a los anteriores, es decir, no puede haber filas ni columnas en blanco entre los datos nuevos y los ya existentes.
2. En la tabla, puede insertar datos entre los valores ya existentes.
Crear un rango con nombre dinámico en Excel con una función
En Excel 2003 o versiones anteriores, el primer método no estará disponible, así que aquí tiene otra opción. La siguiente función DESREF( ) puede realizar esta tarea, aunque resulta algo complicada. Supongamos que tengo un rango de datos con nombres de celda definidos: por ejemplo, A1:A6, cuyo nombre de celda es Fecha, y B1:B6, cuyo nombre de celda es PrecioVenta. A la vez, creo una fórmula para el PrecioVenta. Consulte la captura de pantalla:
Puede convertir el Nombre de celda en un Nombre de celda dinámico siguiendo estos pasos:
1. Vaya y haga clic en Fórmulas > Administrador de nombres; consulte la captura de pantalla:
2. En el cuadro de diálogo Administrador de nombres, seleccione el elemento que desea utilizar y haga clic en el botón Editar.
3. En el cuadro de diálogo emergente Editar nombre, introduzca esta fórmula =DESREF(Hoja1!$A$1;0;0;CONTARA($A:$A);1) en el cuadro de texto Hace referencia a; consulte la captura de pantalla:
4. A continuación, haga clic en Aceptar y repita los pasos 2 y 3 para copiar esta fórmula =DESREF(Hoja1!$B$1;0;0;CONTARA($B:$B)), 1) en el cuadro de texto Hace referencia a para el nombre de celda PrecioVenta.
5. Así habrá creado rangos con nombre dinámicos. Al introducir nuevos valores tras los datos existentes, el rango con nombre se ajustará automáticamente y la fórmula creada se actualizará también. Consulte las capturas de pantalla:
| | |
Nota: Si hay celdas vacías en medio del rango, el resultado de la fórmula será incorrecto. Esto se debe a que las celdas no vacías no se cuentan, por lo que el rango resultante será más corto de lo necesario y las últimas celdas quedarán excluidas.
Consejo: explicación de esta fórmula:
- =DESREF(referencia,filas,columnas,[alto],[ancho])
- =DESREF(Hoja1!$A$1;0;0;CONTARA($A:$A);1)
- referenciacorresponde a la posición de la celda inicial; en este ejemplo,Hoja1!$A$1;
- fila hace referencia al número de filas que se desplazará hacia abajo respecto a la celda inicial (o hacia arriba si introduce 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 se desplazará hacia la derecha respecto a la celda inicial (o hacia la izquierda si introduce un valor negativo). En la fórmula anterior, 0 indica que no se expandirá ninguna columna hacia la derecha.
- [alto] corresponde a la altura (o número de filas) del rango que comienza en la posición ajustada. $A:$A contará todos los elementos introducidos en la columna A.
- [ancho] 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.
Puede ajustar estos argumentos según sus necesidades.
Crear un rango con nombre dinámico en Excel con código VBA
Si tiene varias columnas, podría repetir e introducir una fórmula individual para todas las columnas restantes, pero sería un proceso largo y repetitivo. Para facilitar las cosas, puede utilizar un código que cree automáticamente el rango con nombre dinámico.
1. Activa la hoja de cálculo.
2. Mantenga pulsadas las teclas ALT + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.
3. Haga clic en Insertar > Módulo y pegue el siguiente código en la ventana del módulo.
Código VBA: crear un 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. A continuación, pulse la tecla F5 para ejecutar el código; se generarán varios rangos con nombre dinámicos basados en los valores de la primera fila y también se creará un rango dinámico llamado MisDatos que abarcará todos los datos.
5. Cuando introduzca nuevos valores después de las filas o columnas, el rango se ampliará automáticamente. Consulte las capturas de pantalla:
 |
 |
 |
Notas:
1. Con este código, los nombres de celda no aparecen en el Cuadro de nombres. Para ver y utilizar cómodamente los nombres de celda, he instalado Kutools para Excel, y gracias a su Navegación, se muestran los nombres de celda dinámicos creados.
2. Con este código, todo el rango de datos puede ampliarse tanto vertical como horizontalmente, pero recuerde que no debe haber filas ni columnas en blanco entre los datos al introducir nuevos valores.
3. Al utilizar este código, sus rangos de datos deben comenzar en la celda A1.
Artículo relacionado:
¿Cómo actualizar automáticamente un gráfico tras introducir nuevos datos en Excel?
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active 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 dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos