Cómo crear rangos con nombre en Excel (una guía paso a paso)

¿Qué hay en el nombre?

Si está trabajando con hojas de cálculo de Excel, podría significar un gran ahorro de tiempo y eficiencia.

En este tutorial, aprenderá cómo crear rangos con nombre en Excel y cómo usarlo para ahorrar tiempo.

Rangos con nombre en Excel: una introducción

Si alguien tiene que llamarme o referirse a mí, usarán mi nombre (en lugar de decir que un hombre se queda en tal y cual lugar con tal o cual altura y peso).

¿Correcto?

De manera similar, en Excel, puede dar un nombre a una celda o un rango de celdas.

Ahora, en lugar de usar la referencia de celda (como A1 o A1: A10), simplemente puede usar el nombre que le asignó.

Por ejemplo, suponga que tiene un conjunto de datos como se muestra a continuación:

En este conjunto de datos, si tiene que hacer referencia al rango que tiene la Fecha, tendrá que usar A2: A11 en las fórmulas. De manera similar, para Representante de ventas y Ventas, deberá usar B2: B11 y C2: C11.

Si bien está bien cuando solo tiene un par de puntos de datos, pero en caso de que tenga conjuntos de datos enormes y complejos, el uso de referencias de celda para hacer referencia a los datos podría llevar mucho tiempo.

Los rangos con nombre de Excel facilitan la consulta de conjuntos de datos en Excel.

Puede crear un rango con nombre en Excel para cada categoría de datos y luego usar ese nombre en lugar de las referencias de celda. Por ejemplo, las fechas se pueden llamar "Fecha", los datos del Representante de ventas se pueden llamar "SalesRep" y los datos de ventas se pueden llamar "Ventas".

También puede crear un nombre para una sola celda. Por ejemplo, si tiene el porcentaje de comisión de ventas en una celda, puede nombrar esa celda como "Comisión".

Beneficios de crear rangos con nombre en Excel

Estos son los beneficios de usar rangos con nombre en Excel.

Use nombres en lugar de referencias de celda

Cuando crea rangos con nombre en Excel, puede usar estos nombres en lugar de las referencias de celda.

Por ejemplo, puede utilizar = SUMA (VENTAS) en lugar de = SUMA (C2: C11) para el conjunto de datos anterior.

Eche un vistazo a las fórmulas que se enumeran a continuación. En lugar de usar referencias de celda, he usado los rangos con nombre.

• Número de ventas con un valor superior a 500: = CONTAR.SI (Ventas, ”> 500 ″)
• Suma de todas las ventas realizadas por Tom: = SUMIF (SalesRep, "Tom", Sales)
• Comisión ganada por Joe (ventas de Joe multiplicadas por el porcentaje de comisión):
  = SUMIF (SalesRep, "Joe", Sales) * Comisión

Estaría de acuerdo en que estas fórmulas son fáciles de crear y fáciles de entender (especialmente cuando las comparte con otra persona o las revisa usted mismo).

No es necesario volver al conjunto de datos para seleccionar celdas

Otro beneficio significativo de usar rangos con nombre en Excel es que no es necesario volver atrás y seleccionar los rangos de celdas.

Puede simplemente escribir un par de alfabetos de ese rango con nombre y Excel mostrará los rangos con nombre coincidentes (como se muestra a continuación):

Los rangos con nombre hacen que las fórmulas sean dinámicas

Al utilizar rangos con nombre en Excel, puede hacer que las fórmulas de Excel sean dinámicas.

Por ejemplo, en el caso de la comisión de ventas, en lugar de usar el valor 2.5%, puede usar el rango con nombre.

Ahora, si su empresa más tarde decide aumentar la comisión al 3%, simplemente puede actualizar el rango con nombre, y todo el cálculo se actualizará automáticamente para reflejar la nueva comisión.

Cómo crear rangos con nombre en Excel

Aquí hay tres formas de crear rangos con nombre en Excel:

Método n. ° 1: uso de Definir nombre

Estos son los pasos para crear rangos con nombre en Excel usando Definir nombre:

• Seleccione el rango para el que desea crear un rango con nombre en Excel.
• Vaya a Fórmulas -> Definir nombre.
• En el cuadro de diálogo Nuevo nombre, escriba el nombre que desea asignar al rango de datos seleccionado. Puede especificar el alcance como el libro completo o una hoja de trabajo específica. Si selecciona una hoja en particular, el nombre no estaría disponible en otras hojas.
• Haga clic en Aceptar.

Esto creará un SALESREP de rango con nombre.

Método n. ° 2: usar el cuadro de nombre

• Seleccione el rango para el que desea crear un nombre (no seleccione encabezados).
• Vaya al cuadro de nombre a la izquierda de la barra de fórmulas y escriba el nombre con el que desea crear el rango con nombre.
• Tenga en cuenta que el nombre creado aquí estará disponible para todo el libro de trabajo. Si desea restringirlo a una hoja de trabajo, use el Método 1.

Método n. ° 3: uso de la opción Crear a partir de selección

Esta es la forma recomendada cuando tiene datos en forma tabular y desea crear un rango con nombre para cada columna / fila.

Por ejemplo, en el conjunto de datos a continuación, si desea crear rápidamente tres rangos con nombre (Fecha, Sales_Rep y Sales), puede usar el método que se muestra a continuación.

Estos son los pasos para crear rápidamente rangos con nombre a partir de un conjunto de datos:

• Seleccione todo el conjunto de datos (incluidos los encabezados).
• Vaya a Fórmulas -> Crear a partir de la selección (Atajo de teclado: Control + Mayús + F3). Se abrirá el cuadro de diálogo "Crear nombres a partir de la selección".
• En el cuadro de diálogo Crear nombres a partir de la selección, marque las opciones donde tiene los encabezados. En este caso, seleccionamos la fila superior solo porque el encabezado está en la fila superior. Si tiene encabezados tanto en la fila superior como en la columna izquierda, puede elegir ambos. Del mismo modo, si sus datos están organizados cuando los encabezados están solo en la columna de la izquierda, solo marque la opción Columna de la izquierda.

Esto creará tres rangos con nombre: Fecha, Sales_Rep y Sales.

Tenga en cuenta que recoge automáticamente los nombres de los encabezados. Si hay algún espacio entre las palabras, inserta un guión bajo (ya que no puede haber espacios en los rangos con nombre).

Convención de nomenclatura para rangos con nombre en Excel

Hay ciertas reglas de nomenclatura que debe conocer al crear rangos con nombre en Excel:

• El primer carácter de un rango con nombre debe ser una letra y un carácter de subrayado (_) o una barra invertida (\). Si es algo más, mostrará un error. Los caracteres restantes pueden ser letras, números, caracteres especiales, punto o guión bajo.
• No puede usar nombres que también representen referencias de celda en Excel. Por ejemplo, no puede usar AB1 ya que también es una referencia de celda.
• No puede utilizar espacios al crear rangos con nombre. Por ejemplo, no puede tener Representante de ventas como rango con nombre. Si desea combinar dos palabras y crear un rango con nombre, use un guión bajo, un punto o caracteres en mayúscula para crearlo. Por ejemplo, puede tener Sales_Rep, SalesRep o SalesRep.
  • Al crear rangos con nombre, Excel trata las mayúsculas y las minúsculas de la misma manera. Por ejemplo, si crea un rango con nombre VENTAS, no podrá crear otro rango con nombre, como "ventas" o "Ventas".
• Un rango con nombre puede tener hasta 255 caracteres.

¿Demasiados rangos con nombre en Excel? No te preocupes

A veces, en grandes conjuntos de datos y modelos complejos, puede terminar creando muchos rangos con nombre en Excel.

¿Qué sucede si no recuerda el nombre del rango con nombre que creó?

No te preocupes - aquí hay algunos consejos útiles.

Obtener los nombres de todos los rangos con nombre

Estos son los pasos para obtener una lista de todos los rangos con nombre que creó:

• Vaya a la pestaña Fórmulas.
• En el grupo Definido con nombre, haga clic en Usar en fórmula.
• Haga clic en "Pegar nombres".

Esto le dará una lista de todos los rangos nombrados en ese libro de trabajo. Para usar un rango con nombre (en fórmulas o una celda), haga doble clic en él.

Visualización de rangos con nombre coincidentes

• Si tiene alguna idea sobre el nombre, escriba algunos caracteres iniciales y Excel mostrará un menú desplegable de los nombres coincidentes.

Cómo editar rangos con nombre en Excel

Si ya ha creado un rango con nombre, puede editarlo siguiendo estos pasos:

• Vaya a la pestaña Fórmulas y haga clic en Administrador de nombres.
• El cuadro de diálogo Administrador de nombres enumerará todos los rangos con nombre en ese libro de trabajo. Haga doble clic en el rango con nombre que desea editar.
• En el cuadro de diálogo Editar nombre, realice los cambios.
• Haga clic en Aceptar.
• Cierre el cuadro de diálogo Administrador de nombres.

Atajos de rango con nombre útiles (el poder de F3)

Aquí hay algunos atajos de teclado útiles que serán útiles cuando trabaje con rangos con nombre en Excel:

• Para obtener una lista de todos los rangos con nombre y pegarla en la fórmula: F3
• Para crear un nombre nuevo usando el cuadro de diálogo Administrador de nombres: Control + F3
• Para crear rangos con nombre a partir de la selección: Control + Mayús + F3

Creación de rangos dinámicos con nombre en Excel

Hasta ahora en este tutorial, hemos creado rangos con nombre estáticos.

Esto significa que estos rangos con nombre siempre se referirán al mismo conjunto de datos.

Por ejemplo, si A1: A10 se ha denominado "Ventas", siempre se referirá a A1: A10.

Si agrega más datos de ventas, tendrá que actualizar manualmente la referencia en el rango nombrado.

En el mundo de los conjuntos de datos en constante expansión, esto puede acabar consumiendo mucho tiempo. Cada vez que obtenga nuevos datos, es posible que deba actualizar los rangos con nombre en Excel.

Para abordar este problema, podemos crear rangos con nombre dinámicos en Excel que contabilizarían automáticamente los datos adicionales y los incluirían en el rango con nombre existente.

Por ejemplo, por ejemplo, si agrego dos puntos de datos de ventas adicionales, un rango dinámico con nombre se referiría automáticamente a A1: A12.

Este tipo de rango dinámico con nombre se puede crear utilizando la función ÍNDICE de Excel. En lugar de especificar las referencias de celda mientras creamos el rango con nombre, especificamos la fórmula. La fórmula se actualiza automáticamente cuando se agregan o eliminan los datos.

Veamos cómo crear rangos dinámicos con nombre en Excel.

Suponga que tenemos los datos de ventas en la celda A2: A11.

Estos son los pasos para crear rangos dinámicos con nombre en Excel:

1. 1. Vaya a la pestaña Fórmula y haga clic en Definir nombre.
   2. En el cuadro de diálogo Nuevo nombre, escriba lo siguiente:
      • Nombre: Ventas
      • Alcance: Libro de trabajo
      • Se refiere a: = $ A $ 2: INDICE ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Haga clic en Aceptar.

¡Hecho!

Ahora tiene un rango dinámico con nombre con el nombre "Ventas". Esto se actualizará automáticamente cada vez que le agregue o elimine datos.

¿Cómo funcionan los rangos dinámicos con nombre?

Para explicar cómo funciona esto, necesita saber un poco más sobre la función ÍNDICE de Excel.

La mayoría de la gente usa ÍNDICE para devolver un valor de una lista basada en el número de fila y columna.

Pero la función INDICE también tiene otro lado.

Se puede utilizar para devolver una referencia de celda cuando se utiliza como parte de una referencia de celda.

Por ejemplo, aquí está la fórmula que hemos utilizado para crear un rango dinámico con nombre:

= $ A $ 2: INDICE ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))

INDICE ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Se espera que esta parte de la fórmula devuelva un valor (que sería el décimo valor de la lista, considerando que hay diez ítems).

Sin embargo, cuando se usa delante de una referencia (=$ A $ 2:INDICE ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) devuelve la referencia a la celda en lugar del valor.

Por lo tanto, aquí devuelve = $ A $ 2: $ A $ 11

Si agregamos dos valores adicionales a la columna de ventas, devolvería = $ A $ 2: $ A $ 13

Cuando agrega nuevos datos a la lista, la función CONTAR.SI de Excel devuelve el número de celdas que no están en blanco en los datos. La función INDICE utiliza este número para obtener la referencia de celda del último elemento de la lista.

Nota:

• Esto solo funcionaría si no hay celdas en blanco en los datos.
• En el ejemplo anterior, he asignado una gran cantidad de celdas (A2: A100) para la fórmula de rango con nombre. Puede ajustar esto en función de su conjunto de datos.

También puede usar la función OFFSET para crear rangos dinámicos con nombre en Excel, sin embargo, dado que la función OFFSET es volátil, puede generar un libro de Excel lento. INDEX, por otro lado, es semivolátil, lo que lo convierte en una mejor opción para crear rangos dinámicos con nombre en Excel.

También le pueden gustar los siguientes recursos de Excel:

• Plantillas de Excel gratuitas.
• Capacitación gratuita en línea de Excel (curso de video en línea de 7 partes).
• Ejemplos de códigos de macro de Excel útiles.
• 10 ejemplos avanzados de BUSCARV de Excel.
• Crear una lista desplegable en Excel.
• Creación de un rango con nombre en Google Sheets.
• Cómo hacer referencia a otra hoja o libro de trabajo en Excel