Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo crear un rango con nombre dinámico 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 del tiempo, necesita agregar datos nuevos para expandir los valores de datos de su rango referido en el futuro. En este caso, tienes que volver a Fórmulas > Administrador de nombre y redefinir el rango para incluir el nuevo valor. Para evitar esto, puede crear un rango con nombre dinámico, 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 con nombre dinámico en Excel creando una tabla

Crear rango con nombre dinámico en Excel con Función

Crear rango con nombre dinámico en Excel con código VBA

Ficha Office Habilite la edición y navegación con pestañas en Office y haga su trabajo mucho más fácil ...
Kutools for Excel resuelve la mayoría de sus problemas y aumenta su productividad en un 80%
  • Reutilizar cualquier cosa: Agregue las fórmulas más utilizadas o complejas, gráficos y cualquier otra cosa a sus favoritos, y reutilícelos rápidamente en el futuro.
  • Más de características de texto 20: Extraer número de la cadena de texto; Extraer o eliminar parte de los textos; Convertir números y monedas a palabras en inglés ...
  • Herramientas de fusión: Múltiples libros de trabajo y hojas en uno; Fusionar múltiples celdas / filas / columnas sin perder datos; Combinar filas duplicadas y sumar ...
  • Herramientas divididas: Dividir datos en varias hojas en función del valor; Un libro de trabajo para múltiples archivos Excel, PDF o CSV; De una columna a varias columnas ...
  • Saltar pasta Filas ocultas / filtradas; Cuenta y suma por color de fondo; Crear lista de correo y Enviar correos electrónicos por el valor de la celda...
  • Súper filtro: Cree esquemas de filtro avanzados y aplique a cualquier hoja; Tipo de Propiedad por semana, día, frecuencia y más; Filtrar por negrita, fórmulas, comentario ...
  • Más de potentes funciones de 300; Funciona con Office 2007-2019 y 365; Soporta todos los idiomas; Fácil implementación en su empresa u organización.

flecha azul burbuja derecha Cree un rango con nombre dinámico en Excel creando una tabla


Si está 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.

Digamos que tiene un rango de datos siguientes que deben convertirse en rangos dinámicos.

doc-dynamic-range1

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 entrar llave. Para definir un nombre para el rango B1: B6 como Saleprice de la misma manera. Al mismo tiempo, creo una fórmula = suma (precio de venta) en una celda en blanco, ver captura de pantalla:

doc-dynamic-range2

2. Seleccione el rango y haga clic recuadro > Mesa, mira la captura de pantalla:

doc-dynamic-range3

3. En la Crear mesa cuadro de aviso, verificar Mi mesa tiene encabezados (si el rango no tiene encabezados, desmárquelo), haga clic en OK botón, y los datos de rango se han convertido a la tabla. Ver capturas de pantalla:

doc-dynamic-range4 -2 doc-dynamic-range5

4. Y cuando ingrese nuevos valores después de los datos, el rango nombrado se ajustará automáticamente y también se cambiará la fórmula creada. Vea las siguientes capturas de pantalla:

doc-dynamic-range6 -2 doc-dynamic-range7

Notas:

1. Sus nuevos datos de entrada deben ser adyacentes a los datos anteriores, significa que no hay filas o columnas en blanco entre los datos nuevos y los datos existentes.

2. En la tabla, puede insertar datos entre los valores existentes.


flecha azul burbuja derecha Crear rango con nombre dinámico en Excel con Función

En Excel 2003 o una versión anterior, el primer método no estará disponible, así que aquí hay otra forma de hacerlo. El seguimiento COMPENSAR( ) función puede hacer este favor para usted, pero es un tanto problemático. 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 B1: B6 el nombre del rango es Precio de venta, al mismo tiempo, creo una fórmula para Precio de venta. Ver captura de pantalla:

doc-dynamic-range2

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, mira la captura de pantalla:

doc-dynamic-range8

2. En la Administrador de nombre cuadro de diálogo, seleccione el elemento que desea usar y haga clic Editar botón.

doc-dynamic-range9

3. En el estallado Editar nombre diálogo, ingrese esta fórmula = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) en el Se refiere a cuadro de texto, ver captura de pantalla:

doc-dynamic-range10

4. Luego haga clic OK, y luego repita step2 y step3 para copiar esta fórmula = OFFSET (Sheet1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) en el Se refiere a cuadro de texto para el Precio de venta nombre de rango

5. Y los rangos dinámicos nombrados se han creado. Cuando ingrese nuevos valores después de los datos, el rango nombrado se ajustará automáticamente y también se modificará la fórmula creada. Ver capturas de pantalla:

doc-dynamic-range6 -2 doc-dynamic-range7

Nota: Si hay celdas en blanco en el medio de su rango, el resultado de su fórmula será incorrecto. Eso 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.

Consejo: explicación para esta fórmula:

  • = DESPLAZAMIENTO (referencia, filas, columnas, [altura], [ancho])
  • -1
  • = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
  • referencia corresponde a la posición de la celda inicial, en este ejemplo Sheet1! $ A $ 1;
  • fila se refiere al número de filas que va a 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 moverá hacia la derecha, en relación con la celda de inicio (o hacia la izquierda, utilizando un valor negativo). En la fórmula de ejemplo anterior, 0 indica expandir columnas 0 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 artículos 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 será 1 en toda la columna.

Puede cambiar estos argumentos según su necesidad.


flecha azul burbuja derecha Crear rango con nombre dinámico en Excel con código VBA

Si tiene varias columnas, puede repetir e ingresar fórmula individual para todas las columnas restantes, pero ese sería un proceso largo y repetitivo. Para facilitar las cosas, puede usar un código para crear automáticamente el rango dinámico nombrado.

1. Activa tu hoja de trabajo.

2. Mantenga presionado el ALT + F11 llaves, y abre la Ventana de Microsoft Visual Basic para Aplicaciones.

3. Hacer clic recuadro > Móduloy pegue el siguiente código en 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 nombrados que se nombran con los valores de la primera fila y también crea un rango dinámico llamado Mis datos que cubre toda la información.

5. Cuando ingrese nuevos valores después de las filas o columnas, también se expandirá el rango. Ver capturas de pantalla:

doc-dynamic-range12
-1
doc-dynamic-range13

Notas:

1. Con este código, los nombres de rango no se muestran en Nombre de buzón, para ver y usar los nombres de rango convenientemente, he instalado Kutools for Excel, Con su Panel de exploración, los nombres del rango dinámico creado se enumeran.

2. Con este código, el rango completo de los datos se puede expandir vertical u horizontalmente, pero para recordar que no debe haber filas o columnas en blanco entre los datos cuando ingresa valores nuevos.

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?


Kutools for Excel resuelve la mayoría de sus problemas y aumenta su productividad en un 80%

  • Reutilizar: Insertar rápidamente fórmulas complejas, gráficos y cualquier cosa que hayas usado antes; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas sin perder datos; Contenido de celdas divididas; Combinar filas / columnas duplicadas... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Más de potentes funciones de 300. Admite Office / Excel 2007-2019 y 365. Soporta todos los idiomas. Fácil implementación en su empresa u organización. Funciones completas de prueba gratuita de 30-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    loyiso · 3 years ago
    please help i am trying to create a dynamic named range on excel 2016 typing "=offset(DATAENTRY!$B$6,,,counta(DATAENTRY!$B$6:$B$13))" but still it gives me an error saying it is not a formula.
  • To post as a guest, your comment is unpublished.
    marge · 4 years ago
    You are a very good teacher: 1) step-by-step approach; 2) you do not bore the student with obvious material or conclusions; 3) yet you include all necessary material. I look forward to more tutorials from you.
  • To post as a guest, your comment is unpublished.
    Iran · 5 years ago
    Thanks for good article