los rangos con nombre son una de estas características antiguas y crujientes en Excel que pocos usuarios entienden. Los nuevos usuarios pueden encontrarlos extraños y aterradores, e incluso las manos viejas pueden evitarlos porque parecen inútiles y complejos.
pero los rangos con nombre son en realidad una característica bastante genial. Pueden hacer fórmulas *mucho* más fáciles de crear, leer y mantener. Y como beneficio adicional, hacen que las fórmulas sean más fáciles de reutilizar (más portátiles).
de hecho, uso rangos con nombre todo el tiempo cuando pruebo y prototipo fórmulas. Me ayudan a hacer que las fórmulas funcionen más rápido., También uso rangos con nombre porque soy perezoso y no me gusta escribir referencias complejas 🙂
los conceptos básicos de rangos con nombre en Excel
¿qué es un rango con nombre?
un rango con nombre es solo un nombre legible por humanos para un rango de celdas en Excel. Por ejemplo, si nombro el rango A1:A100 «datos», puedo usar MAX para obtener el valor máximo con una fórmula simple:
=MAX(data) // max value
La belleza de los rangos nombrados es que puede usar nombres significativos en sus fórmulas sin pensar en las referencias de celda., Una vez que tenga un rango con nombre, simplemente úselo como una referencia de celda. Todas estas fórmulas son válidas con el rango con nombre «data»:
=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min valueVideo: Cómo crear un rango con nombre
crear un rango con nombre es fácil
crear un rango con nombre es rápido y fácil. Simplemente seleccione un rango de celdas y escriba un nombre en nombre caja. Cuando presiona return, se crea el nombre:
para probar rápidamente el nuevo rango, elija el nuevo nombre en el menú desplegable junto al cuadro Nombre. Excel seleccionará el rango en la hoja de trabajo.,
Excel puede crear nombres automáticamente (ctrl + shift + F3)
Si tiene datos bien estructurados con etiquetas, puede hacer que Excel cree rangos con nombre para usted. Simplemente seleccione los datos, junto con las etiquetas, y use el comando «Crear desde la selección» en la pestaña Fórmulas de la cinta:
También puede usar el atajo de teclado control + shift + F3.
Usando esta característica, podemos crear rangos con nombre para la población de 12 estados en un solo paso:
Cuando hace clic en Aceptar, se crean los nombres., Encontrará todos los nombres recién creados en el menú desplegable junto al cuadro Nombre:
con los nombres creados, puede usarlos en fórmulas como esta
=SUM(MN,WI,MI)actualizar rangos con nombre en el Administrador de nombres (Control + F3)
Una vez que cree un rango con nombre, use el administrador de nombres (control + F3) para actualizar según sea necesario. Seleccione el nombre con el que desea trabajar, luego cambie la referencia directamente (es decir, edite «se refiere a»), o haga clic en el botón de la derecha y seleccione un nuevo rango.,
no es necesario hacer clic en el botón Editar para actualizar una referencia. Cuando haga clic en cerrar, el nombre del rango se actualizará.
Nota: Si selecciona un rango con nombre completo en una hoja de trabajo, puede arrastrar a una nueva ubicación y la referencia se actualizará automáticamente. Sin embargo, no conozco una manera de ajustar las referencias de rango haciendo clic y arrastrando directamente en la hoja de trabajo. Si usted sabe una manera de hacer esto, chime abajo!
ver todos los rangos con nombre (control + F3)
para ver rápidamente todos los rangos con nombre en un libro de trabajo, use el menú desplegable junto al cuadro Nombre.,
si desea ver más detalles, abra el Administrador de nombres (Control + F3), que enumera todos los nombres con referencias y también proporciona un filtro:
nota: en un Mac, no hay Administrador de nombres, por lo que verá el cuadro de diálogo Definir nombre.
copie y pegue todos los rangos con nombre (F3)
si desea un registro más persistente de rangos con nombre en un libro de trabajo, puede pegar la lista completa de nombres en cualquier lugar que desee., Vaya a fórmulas > Use en Fórmula (o use el atajo F3), luego elija Pegar nombres > pegar lista:
Cuando haga clic en el botón Pegar lista, verá los nombres y referencias pegados en la hoja de trabajo:
ver nombres directamente en la hoja de trabajo
Si establece el nivel de zoom en menos del 40%, Excel mostrará nombres de rango directamente en la hoja de trabajo:
¡Gracias por este consejo, Felipe!,
los nombres tienen reglas
al crear rangos con nombre, siga estas reglas:
rangos con nombre en fórmulas
los rangos con nombre son fáciles de usar en fórmulas
por ejemplo, digamos que nombra una celda en su libro de trabajo «actualizado». La idea es que puede poner la fecha actual en la celda (Ctrl + 😉 y referirse a la fecha en otro lugar del libro de trabajo.
La fórmula en B8 se parece a esto:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")Usted puede pegar esta fórmula en cualquier lugar en el libro y se mostrará correctamente., Cada vez que cambie la fecha en «Actualizado», el mensaje se actualizará dondequiera que se use la fórmula. Vea esta página para más ejemplos.
los rangos con nombre aparecen al escribir una fórmula
Una vez que haya creado un rango con nombre, aparecerá automáticamente en las fórmulas cuando escriba la primera letra del nombre. Presione la tecla tab para ingresar el nombre cuando tenga una coincidencia y desee que Excel ingrese el nombre.,
los rangos con nombre pueden funcionar como constantes
debido a que los rangos con nombre se crean en una ubicación central, puede usarlos como constantes sin una referencia de celda. Por ejemplo, puede crear nombres como » MPG «(millas por galón) y» CPG»(costo por galón) y asignar valores fijos:
luego puede usar estos nombres en cualquier lugar que desee en fórmulas y actualizar su valor en una ubicación central.,
los rangos con Nombre son absolutos por defecto
Por defecto, los rangos con nombre comportan como referencias absolutas. Por ejemplo, en esta hoja de trabajo, la fórmula para calcular el combustible sería:
=C5/$D$2
La referencia a D2 es absoluta (bloqueada) por lo que la fórmula se puede copiar D2 cambiando.,
si nombramos D2 «MPG» la fórmula se convierte en:
=C5/MPG
dado que MPG es absoluto por defecto, la fórmula se puede copiar hacia abajo en la columna D tal cual.
los rangos con nombre también pueden ser relativos
aunque los rangos con nombre son absolutos por defecto, también pueden ser relativos. Un rango con nombre relativo se refiere a un rango que es relativo a la posición de la celda activa en el momento en que se crea el rango. Como resultado, los rangos con nombre relativo son útiles para construir fórmulas genéricas que funcionan dondequiera que se muevan.,
por ejemplo, puede crear un rango genérico con nombre «CellAbove» como este:
- seleccione la celda A2
- Control + F3 para abrir la pestaña Administrador de nombres
- En la sección ‘se refiere a’, luego escriba: =A1
CellAbove ahora recuperará el valor de la celda anterior dondequiera que se use.
importante: asegúrese de que la celda activa esté en la ubicación correcta antes de crear el nombre.
aplicar rangos con nombre a fórmulas existentes
Si tiene fórmulas existentes que no usan rangos con nombre, puede pedirle a Excel que aplique los rangos con nombre en las fórmulas por usted., Comience seleccionando las celdas que contienen fórmulas que desea actualizar. A continuación, ejecute fórmulas > Definir nombres > aplicar nombres.
Excel reemplazará las referencias que tengan un rango con nombre correspondiente con el nombre en sí.
También puede aplicar nombres con buscar y reemplazar:
importante: Guarde una copia de seguridad de su hoja de trabajo y seleccione solo las celdas que desea cambiar antes de usar Buscar y reemplazar en las fórmulas.,beneficios clave de los rangos con nombre
los rangos con nombre hacen que las fórmulas sean más fáciles de leer
el mayor beneficio individual de los rangos con nombre es que hacen que las fórmulas sean más fáciles de leer y mantener. Esto se debe a que reemplazan referencias crípticas con nombres significativos. Por ejemplo, considere esta hoja de trabajo con datos sobre planetas en nuestro sistema solar., Sin rangos con nombre, una fórmula VLOOKUP para obtener «Position» de la tabla es bastante críptica:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
Sin embargo, con B3:E11 llamado «data», y H4 llamado «planet», podemos escribir fórmulas como esta:
=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites
de un vistazo, puede ver la única diferencia en estas fórmulas en el índice de columna.
rangos con nombre hacen que las fórmulas sean portátiles y reutilizables
los rangos con nombre pueden hacer que sea mucho más fácil reutilizar una fórmula en una hoja de trabajo diferente., Si define nombres con anticipación en una hoja de trabajo, puede pegar una fórmula que use estos nombres y «solo funcionará». Esta es una gran manera de obtener rápidamente una fórmula de trabajo.
por ejemplo, esta fórmula cuenta valores únicos en un rango de datos numéricos:
=SUM(--(FREQUENCY(data,data)>0))para «portar» rápidamente esta fórmula a su propia hoja de trabajo, nombre un rango «datos» y pegue la fórmula en la hoja de trabajo. Mientras «datos» contenga valores numéricos, la fórmula funcionará inmediatamente.,
consejo: le recomiendo que cree los nombres de rango necesarios *primero* en el libro de trabajo de destino, luego copie la fórmula solo como texto (es decir, no copie la celda que contiene la fórmula en otra hoja de trabajo, solo copie el texto de la fórmula). Esto evita que Excel cree nombres sobre la marcha y le permite controlar completamente el proceso de creación de nombres. Para copiar solo el texto de la fórmula, copie el texto de la barra de fórmulas o copie a través de otra aplicación (es decir, navegador, editor de texto, etc.).).
los rangos con nombre se pueden usar para la navegación
los rangos con nombre son excelentes para la navegación rápida., Simplemente seleccione el menú desplegable junto al cuadro Nombre y elija un nombre. Cuando suelte el ratón, se seleccionará el rango. Cuando existe un rango con nombre en otra hoja, se le llevará a esa hoja automáticamente.
los rangos con Nombre trabajar bien con hipervínculos
los rangos con Nombre hacer hipervínculos fácil. Por ejemplo, si nombra a A1 en Sheet1 «home», puede crear un hipervínculo en otro lugar que lo lleve de vuelta allí.,
Para utilizar un rango dentro de la función de hipervínculos, agregar un símbolo de libra en frente de el rango con nombre:
=HYPERLINK("#home","take me home")Nota: curiosamente, usted no puede hipervínculo a una tabla como puede un rango normal nombre. Sin embargo, puede definir un nombre igual a una tabla (es decir, =Table1) e hipervínculo a eso. Si alguien conoce una manera de enlazar directamente una mesa, chime in!,
rangos con nombre para la validación de datos
los rangos de nombres funcionan bien para la validación de datos, ya que le permiten usar una referencia con nombre lógico para validar la entrada con un menú desplegable. A continuación, el rango G4:G8 se llama «statuslist», luego aplica la validación de datos con una lista vinculada como esta:
el resultado es un menú desplegable en la columna E que solo permite valores en el rango nombrado:
h2>
Los rangos de nombres son extremadamente útiles cuando se ajustan automáticamente a nuevos datos en una hoja de trabajo., Un rango configurado de esta manera se denomina «rango dinámico con nombre». Hay dos formas de hacer un rango dinámico: fórmulas y tablas.
rango dinámico con nombre con una tabla
Una tabla es la forma más fácil de crear un rango dinámico con nombre. Seleccione cualquier celda en los datos, luego use el control de acceso directo + T:
Cuando crea una tabla de Excel, se crea automáticamente un nombre (por ejemplo, Table1), pero puede cambiar el nombre de la tabla como desee. Una vez que haya creado una tabla, se expandirá automáticamente cuando se agreguen datos.,
rango dinámico con nombre con una fórmula
También puede crear un rango dinámico con nombre con fórmulas, utilizando funciones como OFFSET e INDEX. Aunque estas fórmulas son moderadamente complejas, proporcionan una solución ligera cuando no desea usar una tabla., Los siguientes enlaces proporcionan ejemplos con explicaciones completas:
- Ejemplo de fórmula de rango dinámico con índice
- Ejemplo de fórmula de rango dinámico con desplazamiento
nombres de tabla en validación de datos
dado que las tablas de Excel proporcionan un rango dinámico automático, parecen ser un ajuste natural para las reglas de validación de datos, donde el objetivo es validar contra una lista que siempre puede estar cambiando. Sin embargo, un problema con las tablas es que no puede usar referencias estructuradas directamente para crear reglas de validación de datos o formato condicional., En otras palabras, no puede usar un nombre de tabla en las áreas de entrada de formato condicional o validación de datos.
sin embargo, como solución alternativa, puede definir un rango con nombre que apunte a una tabla y, a continuación, usar el rango con nombre para la validación de datos o el formato condicional. El siguiente video recorre este enfoque en detalle.
Video: cómo usar rangos con nombre con tablas
eliminar rangos con nombre
Nota: Si tiene fórmulas que se refieren a rangos con nombre, es posible que desee actualizar las fórmulas primero antes de eliminar nombres. De lo contrario, verás # nombre ?, errores en fórmulas que aún se refieren a nombres eliminados. Siempre Guarde su hoja de trabajo antes de eliminar los rangos con nombre en caso de que tenga problemas y necesite volver al original.
los rangos con nombre se ajustan al eliminar e insertar celdas
Cuando elimina *parte* de un rango con nombre, o si inserta celdas/filas/columnas dentro de un rango con nombre, la referencia del rango se ajustará en consecuencia y seguirá siendo válida. Sin embargo, si elimina todas las celdas que encierran un rango con nombre, el rango con nombre perderá la referencia y mostrará un error #REF., Por ejemplo, si nombro A1 «prueba», luego elimino la columna A, el administrador de nombres mostrará «se refiere a» como:
=Sheet1!#REF!eliminar nombres con el Administrador de nombres
para eliminar rangos con nombre de un libro de trabajo manualmente, abra el administrador de nombres, seleccione un rango y haga clic en el botón Eliminar. Si desea eliminar más de un nombre al mismo tiempo, puede cambiar + clic o Ctrl + Clic para seleccionar varios nombres, luego eliminar en un solo paso.,
eliminar nombres con errores
Si tiene muchos nombres con errores de referencia, puede usar el botón Filtrar en el administrador de nombres para filtrar nombres con errores:
luego Mayús+Clic para seleccionar todos los nombres y eliminar.
rangos con nombre y alcance
los rangos con nombre en Excel tienen algo llamado «alcance», que determina si un rango con nombre es local a una hoja de trabajo dada o global en todo el libro de trabajo. Los nombres globales tienen un ámbito de «libro de trabajo», y los nombres locales tienen un ámbito igual al nombre de hoja en el que existen., Por ejemplo, el ámbito para un nombre local podría ser «Sheet2».
el propósito del ámbito
los rangos con nombre con un ámbito global son útiles cuando desea que todas las hojas de un libro tengan acceso a ciertos datos, variables o constantes. Por ejemplo, puede usar un rango con nombre global una suposición de tasa impositiva utilizada en varias hojas de trabajo.
ámbito Local
ámbito Local significa que un nombre solo funciona en la hoja en la que se creó. Esto significa que puede tener varias hojas de trabajo en el mismo libro de trabajo que usan el mismo nombre., Por ejemplo, tal vez tenga un libro de trabajo con hojas de seguimiento mensuales (una por mes) que usan rangos con nombre con el mismo nombre, todos con ámbito local. Esto podría permitirle reutilizar las mismas fórmulas en hojas diferentes. El ámbito local permite que los nombres de cada hoja funcionen correctamente sin chocar con los nombres de las otras hojas.
para referirse a un nombre con un ámbito local, puede prefijar el nombre de la hoja al nombre del rango:
Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenueLos nombres de rango creados con el cuadro Nombre tienen automáticamente un ámbito global., Para anular este comportamiento, agregue el nombre de la hoja al definir el nombre:
Sheet3!my_new_nameámbito Global
ámbito Global significa que un nombre funcionará en cualquier lugar de un libro de trabajo. Por ejemplo, podría nombrar una celda «last_update», ingresar una fecha en la celda. Luego puede usar la fórmula a continuación para mostrar la fecha de la última actualización en cualquier hoja de trabajo.
=last_updateGlobal nombres deben ser únicos dentro de un libro.
ámbito Local
los rangos con nombre de ámbito local tienen sentido para las hojas de trabajo que usan rangos con nombre solo para supuestos locales., Por ejemplo, tal vez tenga un libro de trabajo con hojas de seguimiento mensuales (una por mes) que usan rangos con nombre con el mismo nombre, todos con ámbito local. El ámbito local permite que los nombres de cada hoja funcionen correctamente sin chocar con los nombres de las otras hojas.
administrar el alcance del rango con nombre
de forma predeterminada, los nombres nuevos creados con el cuadro de nombres son globales y no puede editar el alcance de un rango con nombre después de crearlo. Sin embargo, como solución alternativa, puede eliminar y volver a crear un nombre con el ámbito deseado.,
si desea cambiar varios nombres a la vez de global a local, a veces tiene sentido copiar la hoja que contiene los nombres. Cuando duplica una hoja de trabajo que contiene rangos con nombre, Excel copia los rangos con nombre en la segunda hoja, cambiando el ámbito a local al mismo tiempo. Después de tener la segunda hoja con nombres de ámbito local, puede eliminar opcionalmente la primera hoja.
Jan Karel Pieterse y Charles Williams han desarrollado una utilidad llamada Name Manager que proporciona muchas operaciones útiles para rangos con nombre. Puede descargar la utilidad Administrador de nombres aquí.,
