cómo definir y usar en fórmulas

tipos de nombres de excel

En Microsoft Excel, puede crear y utilizar dos tipos de nombres:

Nombre definido – un nombre que hace referencia a una sola celda, rango de celdas, valor constante o fórmula. Por ejemplo, cuando define un nombre para un rango de celdas, se llama rango con nombreo rango definido. Estos nombres son el tema del tutorial de hoy.

Nombre de la tabla – un nombre de una tabla de Excel que se crea automáticamente cuando inserta una tabla en una hoja de cálculo (Ctrl+T). Para obtener más información sobre las tablas de Excel, consulte Cómo crear y usar una tabla en Excel.

Escriba un nombre en el cuadro de nombre

El Nombre de Caja en Excel es la forma más rápida de crear un rango con nombre:

1. Seleccione una celda o un rango de celdas al que desee nombrar.
2. Escriba un nombre en el Nombre de Caja.
3. Presione la tecla Intro.

¡Listo, se crea un nuevo rango con nombre de Excel!

Cree un nombre utilizando la opción Definir nombre

Otra forma de crear un rango con nombre en Excel es esta:

1. Seleccione las celdas.
2. Sobre el Fórmulas pestaña, en la Definir nombres grupo, haga clic en el Definir nombre botón.
3. En el Nuevo nombre cuadro de diálogo, especifique tres cosas:
   • En el Nombre escriba el nombre del rango.
   • En el Alcance menú desplegable, configure el alcance del nombre (Libro de trabajo por defecto).
   • En el Se refiere a marque la referencia y corríjala si es necesario.
4. Hacer clic DE ACUERDO para guardar los cambios y cerrar el cuadro de diálogo.

En comparación con el método anterior, utilizando Definir nombre en Excel requiere algunos clics adicionales, pero también proporciona un par de opciones más, como configurar el nombre alcance y agregando un comentario que explique algo sobre el nombre. Además, Excel Definir nombre Esta característica le permite crear un nombre para un constante o fórmula.

Haga un rango con nombre usando el Administrador de nombres de Excel

Generalmente, el Administrador de nombres en Excel se utiliza para trabajar con nombres existentes. Sin embargo, también puede ayudarte a crear un nuevo nombre. Así es cómo:

1. Ve a la Fórmulas pestaña > Nombres definidos grupo, haga clic Administrador de nombres. O simplemente presione Ctrl + F3 (mi forma preferida).
2. En la esquina superior izquierda del Administrador de nombres ventana de diálogo, haga clic en el Nuevo… botón:
   
3. Esto abrirá el Nuevo nombre cuadro de diálogo donde configura un nombre como se demostró en la sección anterior.

Nombres de nivel de hoja de trabajo

Un nombre a nivel de hoja de trabajo se reconoce dentro de la hoja de trabajo donde se encuentra. Por ejemplo, si crea un rango con nombre y establece su alcance en Hoja1será reconocido sólo en Hoja1.

Para poder utilizar un nombre a nivel de hoja de trabajo en otra hoja de trabajodebe anteponer el nombre de la hoja de trabajo seguido del signo de exclamación (!), así:

Hoja1!lista_de_elementos

Para hacer referencia a un nivel de hoja de trabajo nombre en otro libro de trabajotambién debe incluir el nombre del libro entre corchetes:

[Sales.xlsx]Hoja1!lista_de_elementos

Si el nombre de la hoja o el nombre del libro contiene espaciosdeben ir entre comillas simples:

‘[Sales 2017.xlsx]Hoja1’!items_list

Nombres de nivel de libro de trabajo

Un nombre a nivel de libro se reconoce en todo el libro y puede consultarlo simplemente por su nombre desde cualquier hoja del mismo libro.

Un uso para un nombre a nivel de libro de trabajo en otro libro de trabajopreceda el nombre con el nombre del libro (incluida la extensión) seguido del signo de exclamación:

Libro1.xlsx!items_list

Prioridad del alcance

Un nombre definido debe ser único dentro de su alcance. Puede utilizar el mismo nombre en diferentes ámbitos, pero esto podría crear un conflicto de nombres. Para evitar que esto suceda, de forma predeterminada, el el nivel de la hoja de trabajo tiene prioridad sobre el nivel del libro de trabajo.

Si hay algunos rangos con nombres idénticos con diferentes alcances y desea utilizar el nombre del nivel del libro de trabajo, anteponga el nombre al nombre del libro de trabajo como si se estuviera refiriendo a un nombre en otro libro de trabajo, por ejemplo: Libro1.xlsx!datos. De esta manera, el conflicto de nombres se puede anular para todas las hojas de trabajo excepto para la primera hoja, que siempre usa el nombre del nivel de hoja de trabajo local.

Cómo editar el rango con nombre en Excel

Para cambiar un nombre de Excel existente, abra el Administrador de nombresseleccione el nombre y haga clic en Editar… botón. Esto abrirá el Editar nombre cuadro de diálogo donde puede cambiar el nombre y la referencia. El alcance del nombre no se puede cambiar.

A editar una referencia de nombreno es necesario abrir el Editar nombre caja de diálogo. Simplemente seleccione el nombre de su interés en Excel. Administrador de nombresy escriba una nueva referencia directamente en el Se refiere a o haga clic en el botón de la derecha y seleccione el rango deseado en la hoja. Después de hacer clic en Cerca botón, Excel le preguntará si desea guardar los cambios y haga clic en Sí.

Cómo filtrar nombres en Excel

Si tiene muchos nombres en un determinado libro, haga clic en el Filtrar en la esquina superior derecha de la ventana del Administrador de nombres de Excel para ver solo los nombres relevantes en un momento dado. Los siguientes filtros están disponibles:

• Nombres con ámbito de hoja de trabajo o libro de trabajo
• Nombres con o sin errores
• Nombres definidos o nombres de tablas.

Cómo eliminar un rango con nombre en Excel

A eliminar un rango con nombreselecciónelo en el Administrador de nombres y haga clic en el Borrar botón en la parte superior.

A borrar varios nombreshaga clic en el primer nombre y luego presione la tecla Control y manténgala presionada mientras hace clic en otros nombres que desea eliminar. Luego haga clic en Borrar y todos los nombres seleccionados se eliminarán de una sola vez.

A eliminar todos los nombres definidos en un libro de trabajo, seleccione el primer nombre en la lista, presione y mantenga presionada la tecla Mayús y luego haga clic en el apellido. Suelta la tecla Shift y haz clic Borrar.

1. Los nombres de Excel hacen que las fórmulas sean más fáciles de crear y leer

No es necesario escribir referencias complejas ni ir y venir seleccionando rangos en la hoja. Simplemente comience a escribir el nombre que desea usar en la fórmula y Excel le mostrará una lista de nombres coincidentes para que elija. Haga doble clic en el nombre deseado y Excel lo insertará en la fórmula inmediatamente:

2. Los nombres de Excel permiten crear fórmulas expandibles

Al utilizar rangos con nombres dinámicos, puede crear una fórmula «dinámica» que incluya automáticamente nuevos datos en los cálculos sin tener que actualizar cada referencia manualmente.

3. Los nombres de Excel hacen que las fórmulas sean más fáciles de reutilizar

Los nombres de Excel hacen que sea mucho más fácil copiar una fórmula a otra hoja o trasladar una fórmula a un libro diferente. Todo lo que tiene que hacer es crear los mismos nombres en el libro de destino, copiar y pegar la fórmula tal como está y lo hará funcionar de inmediato.

4. Los rangos con nombre simplifican la navegación

Para llegar rápidamente a un rango con nombre específico, simplemente haga clic en su nombre en el cuadro Nombre. Si un rango con nombre reside en otra hoja, Excel lo llevará a esa hoja automáticamente.

5. Los rangos con nombre permiten crear listas desplegables dinámicas

Para crear una lista desplegable ampliable y actualizable, cree una rango dinámico con nombre primero y luego cree una lista de validación de datos basada en ese rango. Las instrucciones detalladas paso a paso se pueden encontrar aquí: Cómo crear un menú desplegable dinámico en Excel.

Cómo obtener una lista de todos los nombres en el libro de trabajo

Para obtener una lista más tangible de todos los nombres en un libro de trabajo actual, haga lo siguiente:

1. Seleccione la celda superior del rango donde desea que aparezcan los nombres.
2. Ve a la Fórmulas pestaña > Definir nombres grupo, haga clic Uso en fórmulasy luego haga clic en Pegar nombres… O simplemente presione el F3 llave.
3. En el Pegar nombres cuadro de diálogo, haga clic en Pegar lista.

Esto insertará todos los nombres de Excel junto con sus referencias en la hoja de trabajo actual, comenzando en la celda seleccionada.

Nombres absolutos de Excel frente a nombres relativos de Excel

De forma predeterminada, los nombres de Excel se comportan como referencias absolutas, bloqueadas en celdas específicas. Sin embargo, es posible crear un rango con nombre. relativo hacia posición de la celda activa en el momento en que se define el nombre. Los nombres relativos se comportan como referencias relativas: se modifican cuando la fórmula se mueve o se copia a otra celda.

De hecho, no se me ocurre ninguna razón por la que uno querría crear un rango con nombre relativo, excepto tal vez cuando un rango consta de una sola celda. Como ejemplo, creemos un nombre relativo que haga referencia a una celda una columna a la izquierda de la celda actual, en la misma fila:

1. Seleccione la celda B1.
2. Prensa Ctrl + F3 para abrir el Administrador de nombres de Excel y haga clic en el Nuevo…
3. En el Nombre cuadro, escriba el nombre deseado, por ejemplo, elemento_izquierdo.
4. En el Se refiere a la cajatipo =A1.
5. Hacer clic DE ACUERDO.

Ahora, veamos qué sucede cuando usamos el elemento_izquierdo nombre en una fórmula, por ejemplo:

=SUMAR.SI(lista_artículos, artículo_izquierdo, ventas)

Dónde lista_artículos se refiere a $A$2:$A$10 y ventas se refiere a $B$2:$B$10 en la siguiente tabla.

Cuando ingresa la fórmula en la celda E2 y luego la copia en la columna, calculará las ventas totales de cada producto individualmente porque elemento_izquierdo es un nombre relativo y su referencia se ajusta en función de la posición relativa de la columna y fila donde se copia la fórmula:

Cómo aplicar nombres de Excel a fórmulas existentes

Si ha definido los rangos que ya se utilizan en sus fórmulas, Excel no cambiará las referencias a los nombres apropiados automáticamente. Sin embargo, en lugar de reemplazar las referencias con nombres a mano, puede hacer que Excel haga el trabajo por usted. Así es cómo:

1. Seleccione una o más celdas de fórmula que desee actualizar.
2. Ve a la Fórmulas pestaña > Definir nombres grupo y haga clic en Definir nombre > Aplicar nombres…
   
3. En el Aplicar nombres cuadro de diálogo, haga clic en los nombres que desea aplicar y luego haga clic en DE ACUERDO. Si Excel puede hacer coincidir cualquiera de los nombres existentes con las referencias utilizadas en sus fórmulas, los nombres se seleccionarán automáticamente:
   

Además, hay dos opciones más disponibles (seleccionadas de forma predeterminada):

• Ignorar relativo/absoluto – mantenga marcada esta casilla si desea que Excel aplique solo los nombres con el mismo tipo de referencia: reemplace las referencias relativas con nombres relativos y las referencias absolutas con nombres absolutos.
• Utilice nombres de filas y columnas – si se selecciona, Excel cambiará el nombre de todas las referencias de celda que puedan identificarse como la intersección de una fila y una columna con nombre. Para más opciones, haga clic en Opciones

Atajos de nombres de Excel

Como suele ser el caso en Excel, se puede acceder a las funciones más populares de varias maneras: a través de la cinta, el menú contextual y los métodos abreviados de teclado. Los rangos con nombre de Excel no son una excepción. Aquí hay tres atajos útiles para trabajar con nombres en Excel:

Errores de nombre de Excel (#REF y #NAME)

De forma predeterminada, Microsoft Excel hace todo lo posible para mantener los nombres definidos coherentes y válidos ajustando las referencias de rango automáticamente cuando inserta o elimina celdas dentro de un rango con nombre existente. Por ejemplo, si ha creado un rango con nombre para las celdas A1:A10 y luego inserta una nueva fila en cualquier lugar entre las filas 1 y 10, la referencia del rango cambiará a A1:A11. De manera similar, si elimina alguna celda entre A1 y A10, su rango nombrado se contraerá en consecuencia.

Sin embargo, si usted eliminar todas las celdas que componen un rango con nombre de Excel, el nombre deja de ser válido y muestra un #¡ÁRBITRO! error en el Administrador de nombres. El mismo error aparecerá en una fórmula que hace referencia a ese nombre:

Si una fórmula se refiere a un nombre inexistente (mal escrito o eliminado), el #¿NOMBRE? error aparecerá. En cualquier caso, abra el Administrador de nombres de Excel y verifique la validez de sus nombres definidos (la forma más rápida es filtrar nombres con errores).