Aquí tienes dos fórmulas que devuelven el mismo número:
=SUMAR.SI.CONJUNTO($D$2:$D$11, $C$2:$C$11, "North")
=SUMAR.SI.CONJUNTO(Jobs[Hours], Jobs[Region], "North")
Resultado: 20 en ambas — las horas que Amara Okafor registró en los tres avisos del norte.
La primera te dice dónde viven los números. La segunda te dice qué son. Ese es todo el argumento a favor de poner nombre a las cosas, y vale más de lo que parece, porque quien tenga que revisar esa fórmula en febrero sueles ser tú, y para entonces $C$2:$C$11 es solo una dirección de un edificio que ya no recuerdas.
Esta guía trata de los dos sistemas que Excel te da para eso: los nombres definidos, que creas a propósito, y las referencias estructuradas, que llegan gratis en cuanto un rango se convierte en Tabla. Cubre cómo funciona cada uno por dentro, dónde ayudan más allá de las fórmulas, cómo hacer un nombre que crezca con tus datos, los fallos que hacen que la gente con experiencia desconfíe de los nombres, y los casos en los que la respuesta honesta es dejar el rango en paz.
Consejo: Todos los ejemplos de abajo funcionan sobre la tabla de servicio técnico que aparece tras la sección 1. Cópiala en una hoja en blanco empezando en A1 y las referencias de celda coincidirán exactamente. Como la tabla de ejemplo está en inglés, los nombres de columna de las referencias estructuradas se dejan tal cual (
Jobs[Hours],[@Rate]) para que puedas copiarlas sin ajustar nada. Las referencias estructuradas necesitan Excel 2007 o posterior;BUSCARXen la sección 6 y el operador#en la sección 7 necesitan Excel 365 o 2021. Todo lo demás funciona en cualquier versión que siga en uso.
1) Qué Es Realmente un Nombre
El modelo mental que menos disgustos da: un nombre no es una etiqueta pegada a un rango. Es una entrada en la tabla de nombres del libro, y lo que guarda es una fórmula. Define un nombre llamado Hours sobre D2:D11 y lo que Excel apunta es:
Hours se refiere a: =Hoja1!$D$2:$D$11
Ese = inicial es toda la razón por la que los nombres pueden hacer cosas que una etiqueta no podría. Como lo que se guarda es una fórmula, no tiene por qué ser un rango: puede ser un número, un texto o un cálculo. Las secciones 4 y 7 viven de ese hecho.
Hay tres formas de crear uno, y sirven para momentos distintos:
El Cuadro de nombres — selecciona D2:D11, haz clic en el cuadro a la izquierda de la barra de fórmulas, escribe Hours y pulsa Intro. Lo más rápido para un solo nombre. Lo único que hay que vigilar: tienes que pulsar Intro. Si haces clic fuera, el nombre no se crea y nadie te lo va a decir.
Fórmulas → Asignar nombre — el camino largo, y el único que te deja fijar el ámbito y un comentario en el momento de crearlo. Úsalo cuando el nombre importe.
Crear desde la selección (Ctrl+Mayús+F3) — selecciona A1:G11, marca Fila superior y Excel crea siete nombres de una vez, tomando cada uno del encabezado que hay encima de la columna. También arregla en silencio lo que debe: un encabezado Travel km se convierte en Travel_km, porque los nombres no pueden llevar espacios. Cómodo, y la forma más rápida de acabar con nombres que no querías: creará Job, Rate y otros cinco tanto si los necesitabas como si no.
Una vez que el nombre existe, =SUMA(Hours) funciona en cualquier punto del libro, y pulsar F3 dentro de una fórmula abre una lista con todos los nombres que puedes pegar.
Trabajos de Servicio Técnico de un Mes
Diez avisos de cuatro técnicos en cuatro regiones. La tarifa se repite por técnico a propósito: es la columna que debería haber salido de una búsqueda contra un baremo con nombre, y la sección 6 la convierte en eso. Tres trabajos no llevan piezas, así que los totales de la sección 4 tienen ceros dentro en lugar de un conjunto limpio de positivos. Los datos están en A2:G11; la fila de encabezados de A1:G1 es lo que Excel convierte en nombres de columna en cuanto pulsas Ctrl+T.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
2) Los Nombres Son Absolutos por Defecto, y Eso Es una Bendición
Cuando creas un nombre a partir de una selección, Excel guarda la referencia absoluta: =Hoja1!$D$2:$D$11, con sus signos de dólar. Nada de un nombre cambia al copiar la fórmula que lo usa.
🎯 Escenario: Un bloque de resumen en el que las mismas tres fórmulas se copian en cuatro columnas de región, y ninguna debe desplazarse.
=SUMA(Hours)
=PROMEDIO(Hours)
=CONTARA(Hours)
Resultado: 53, 5,3 y 10 — y se quedan así pegues donde pegues. La versión equivalente =SUMA($D$2:$D$11) se comporta igual, claro; la diferencia es que tuviste que acordarte de escribir cuatro dólares, y con el nombre solo tuviste que hacerlo una vez.
Excel también admite nombres relativos: define un nombre con una celda concreta seleccionada, quita los dólares en el cuadro Se refiere a, y el nombre se resuelve de forma distinta según dónde se use. Es una técnica real y aparece en unos cuantos libros ingeniosos. También es invisible: una fórmula que pone =FilaAnterior*1,05 no da ninguna pista de que FilaAnterior significa "una celda por encima de donde yo esté". Si no eres quien va a mantener ese libro los próximos tres años, no lo hagas.
Trampa: los nombres no distinguen mayúsculas.
Rate,rateyRATEson un nombre, no tres. Excel además reescribirá en silencio tus fórmulas existentes para adaptarlas a la última grafía que escribiste, lo cual parece corrupción la primera vez que lo ves y no lo es.
3) Ámbito: Libro, Hoja y la Sombra
Todo nombre tiene un ámbito: la parte del libro en la que se puede usar sin cualificar.
| Ámbito | Se crea con | Se usa como | En el Administrador aparece como |
|---|---|---|---|
| Libro | por defecto en el Cuadro de nombres y en Asignar nombre | Hours desde cualquier hoja | Ámbito: Libro |
| Hoja | eligiendo una hoja en Asignar nombre | Hours en esa hoja, Ene!Hours fuera | Ámbito: Ene |
El ámbito de hoja existe por un buen motivo: doce hojas mensuales que quieren cada una un nombre Total son mucho más fáciles de construir que Total_Ene hasta Total_Dic. Copias la hoja y los nombres de ámbito de hoja viajan con ella, apuntando a las celdas de la hoja nueva.
La trampa aparece cuando existen los dos. Si un libro tiene un Rate de ámbito de libro y la hoja en la que estás tiene su propio Rate de ámbito de hoja, gana el de la hoja. Tu fórmula pone =Hours*Rate en todas las hojas y devuelve una tarifa distinta en una de ellas, sin nada en pantalla que lo indique.
🎯 Escenario: Dos personas construyeron el mismo resumen en dos hojas, una de ellas definió Rate localmente, y ahora el total de marzo se desvía un 4% sin ninguna diferencia de fórmula que señalar.
Abre el Administrador de nombres (Ctrl+F3), ordena por la columna Nombre y busca el mismo nombre repetido con ámbitos distintos. Eso lleva unos cinco segundos y es lo primero que hay que comprobar cuando una fórmula con nombres devuelve la forma correcta de respuesta y el número equivocado.
Consejo: pon prefijo a los nombres de ámbito de hoja cuando puedas —
Ene_Totalcon ámbito Ene es redundante, y esa redundancia es lo que hace visible el eclipse en la lista del Administrador.
4) Constantes y Fórmulas con Nombre
Un nombre no necesita celdas detrás. En Asignar nombre, escribe un literal en el cuadro Se refiere a:
VATRate se refiere a: =0,2
MileageRate se refiere a: =0,45
OvertimeAfter se refiere a: =8
CompanyName se refiere a: ="Northgate Field Services"
Ahora el libro tiene una tarifa que no vive en ninguna parte y se puede usar en todas:
🎯 Escenario: Facturar cada trabajo como mano de obra + piezas + kilometraje, y añadir el IVA — sin un bloque de tarifas en la esquina de una hoja que alguien acabará ordenando o borrando.
=(D2*E2 + F2 + G2*MileageRate) * (1 + VATRate)
Resultado para FS-2201: 686,28 — 408 de mano de obra, 145 de piezas, 18,90 de kilometraje y 114,38 de IVA encima.
Fíjate en lo que no ha pasado: ninguna celda auxiliar, ningún $K$1 que proteger, nada que sobrescribir sin querer. Cambiar la tarifa por kilómetro el próximo abril es una edición en el Administrador de nombres y todas las fórmulas del libro la siguen.
Puedes ir más lejos y guardar un cálculo entero:
NetTotal se refiere a: =SUMAPRODUCTO(Jobs[Hours],Jobs[Rate]) + SUMA(Jobs[Parts]) + SUMA(Jobs[Travel km])*MileageRate
Resultado de =NetTotal en cualquier celda: 5184,15 — 3.556,50 de mano de obra, 1.440 de piezas y 187,65 de kilometraje.
Eso es genuinamente potente y genuinamente peligroso en la misma frase. Una fórmula con nombre es invisible: la celda muestra =NetTotal, y la única forma de ver qué hace es abrir el Administrador de nombres. Usada para una o dos constantes de todo el libro es un regalo. Usada para esconder un cálculo de doce funciones de la gente que tiene que auditarlo, es la forma en que una hoja de cálculo se convierte en el idioma privado de alguien.
Trampa: hay un sitio en el que una constante con nombre te dejará tirado — nadie puede verla leyendo una impresión. Si el tipo de IVA es un número por el que va a preguntar un auditor, ponlo en una celda etiquetada y dale nombre a esa celda.
VATRate se refiere a: =Tarifas!$B$2te da las dos cosas.
5) Dónde Compensan los Nombres Fuera de una Fórmula
La mitad infravalorada de los nombres definidos es todo lo que no es una fórmula, porque varios cuadros de diálogo de Excel aceptan un nombre donde no aceptan una referencia a otra hoja.
Validación de datos. Pon tu lista de regiones en un sitio apartado, llámala Regions y pon =Regions como Origen de una lista desplegable. Antes de Excel 365 esta era la única forma limpia de tener una lista desplegable guardada en otra hoja: escribir =Listas!$A$1:$A$4 en Origen se rechazaba sin más, y un nombre lo esquivaba. Sigue siendo lo más mantenible, porque mover la lista solo obliga a actualizar un nombre.
Formato condicional. La misma restricción se aplicó a las reglas de formato durante años. =$D2>OvertimeAfter es además sencillamente más fácil de leer en la lista de reglas que =$D2>8, y deja el umbral editable en un solo sitio.
Series de gráfico. Apunta una serie a un nombre que resuelva a un rango dinámico y el gráfico crecerá con los datos. Este fue el truco estándar del gráfico que se actualiza solo durante una década, y sigue funcionando.
Navegación. Pulsa F5 o Ctrl+I y tendrás todos los nombres del libro en la lista. Llamar Assumptions o InputBlock a un bloque convierte el "¿en qué hoja estaba eso?" en dos pulsaciones. Print_Area y Print_Titles son a su vez nombres reservados que Excel mantiene por ti — llevas usando rangos con nombre sin saberlo cada vez que fijas un área de impresión.
6) Tablas de Excel: Los Nombres Que Te Regalan
Selecciona A1:G11 y pulsa Ctrl+T. Cámbiale el nombre de Tabla1 a Jobs en la pestaña Diseño de tabla — ese cambio de nombre cuesta tres segundos y es la diferencia entre unas referencias estructuradas que se leen como frases y otras que no se leen como nada.
Ahora tienes nombres que nunca definiste:
| Referencia | Significa | En esta tabla |
|---|---|---|
Jobs | el cuerpo de datos, sin encabezados | A2:G11 |
Jobs[Hours] | una columna de datos | D2:D11 |
Jobs[[Hours]:[Rate]] | un tramo de columnas | D2:E11 |
Jobs[#Encabezados] | la fila de encabezados | A1:G1 |
Jobs[#Todo] | todo, con encabezados y totales | A1:G11 |
Jobs[@Hours] o [@Hours] | el valor de esta fila, dentro de la tabla | D6 en la fila 6 |
🎯 Escenario: Añadir una columna de Mano de obra a la propia tabla y totalizarla sin tocar una sola dirección de celda.
Escribe en H1: Labour. En H2:
=[@Hours]*[@Rate]
Resultado: 408 en FS-2201 — y Excel rellena la fórmula por las diez filas él solo, porque eso es lo que hace una columna calculada. Después, en cualquier punto de la hoja:
=SUMA(Jobs[Labour])
Resultado: 3556,5
Las dos cosas que hacen que el Ctrl+T merezca la pena tienen que ver con el tiempo. Primero, el rango crece. Escribe un undécimo trabajo en la fila 12 y se une a la tabla; SUMA(Jobs[Labour]) lo recoge, la columna calculada se rellena sola, y todos los gráficos y tablas dinámicas apuntados a Jobs lo ven. Compáralo con =SUMA($H$2:$H$11), que no lo hace, y tampoco se queja.
Segundo, la fórmula sobrevive a la edición. Inserta una columna entre Region y Hours y Jobs[Hours] sigue significando horas. $D$2:$D$11 ahora significa tarifa.
🎯 Escenario: La columna Rate se escribió a mano y una de las tarifas está mal. Sácala de un baremo en su lugar.
Pon los cuatro técnicos y sus tarifas en J2:K5 y llama a ese bloque RateCard. Después, en E2:
=BUSCARV([@Engineer], RateCard, 2, FALSO)
Resultado: 68 para Amara Okafor, y un #N/D en cuanto alguien escriba un técnico que no está en el baremo — que es justo la gracia. Una tarifa escrita a mano es silenciosa cuando está mal; una búsqueda es ruidosa.
Consejo: la
@de[@Hours]es el operador de intersección, y solo significa "esta fila" desde dentro de la tabla. UsaJobs[@Hours]desde una celda fuera de la tabla y obtendrás#¡VALOR!salvo que tu fila coincida por casualidad con la de la tabla. Desde fuera, agrega —SUMAR.SI.CONJUNTO(Jobs[Hours], Jobs[Job], "FS-2203")— en lugar de intentar alcanzar una fila.
Trampa: las referencias estructuradas no admiten anclaje absoluto como lo hace
$D$2. Arrastrar=[@Hours]*[@Rate]hacia la derecha te da=[@Rate]*[@Parts], porque la referencia se desplaza por columnas igual que una relativa. Si necesitas fijar una columna al arrastrar, escríbela comoJobs[[Hours]:[Hours]]o usa una referencia normal para ese argumento.
7) Rangos Dinámicos: Tres Generaciones de la Misma Idea
Un nombre definido sobre $D$2:$D$11 está fijado en diez filas. El viejo problema —¿cómo se pone nombre a un rango que crece?— tiene tres respuestas, y cuál uses delata más o menos la edad de tu libro.
DESREF, la clásica. En Se refiere a:
=DESREF(Hoja1!$D$2, 0, 0, CONTARA(Hoja1!$D$2:$D$1000), 1)
Empieza en D2, no te muevas, coge tantas filas como celdas no vacías haya, con una columna de ancho. Funciona, y es lo que encontrarás en todos los libros construidos entre 2000 y 2015. El coste es que DESREF es volátil: recalcula ante cualquier cambio en cualquier punto del libro, dependa de lo que dependa. Uno solo no es nada. Cuarenta, alimentando cuarenta series de gráfico, es un libro que tarda dos segundos en responder a una tecla.
INDICE, el arreglo. Mismo comportamiento, no volátil:
=Hoja1!$D$2:INDICE(Hoja1!$D:$D, CONTARA(Hoja1!$D$2:$D$1000)+1)
Los dos puntos entre un inicio fijo y un resultado de INDICE son el truco: INDICE devuelve una referencia, no un valor, así que puede colocarse a la derecha de un operador de rango. Prefiere esto a DESREF en cualquier cosa nueva que aún necesite un nombre dinámico.
El operador #, la respuesta moderna. Si quien produce la lista es una matriz desbordada, apunta al desbordamiento:
N2: =UNICOS(Jobs[Region])
Resultado: North, South, East, West desbordándose por N2:N5. Ahora =N2# significa "lo que sea que haya desbordado esa fórmula", con la longitud que tenga hoy, y Regions se refiere a: =Hoja1!$N$2# te da un origen de lista desplegable que se mantiene solo, en una línea, sin CONTARA y sin volatilidad.
Y la cuarta respuesta, que suele ser la correcta: conviértelo en Tabla. Jobs[Hours] ya es dinámico y no te costó nada.
Trampa:
CONTARAcuenta celdas no vacías, no la última fila usada. Un valor perdido en D400 hace que tu rango dinámico mida 399 filas, y un solo hueco en medio de los datos hace que se quede corto. La Tabla no tiene ninguno de los dos problemas, que es el argumento más fuerte de toda esta sección.
8) El Administrador de Nombres y Seis Formas en que los Nombres se Pudren
Ctrl+F3 abre el Administrador de nombres. Dos costumbres se pagan solas: ordenar por la columna Se refiere a cuando vas a caza de problemas, y usar el desplegable de filtro, que trae una opción Nombres con errores incorporada.
1. Nombres #¡REF!. Borra la columna D y el nombre que apuntaba a ella no desaparece: se convierte en =Hoja1!#¡REF!. Todas las fórmulas que lo usan devuelven ahora #¡REF!, y la fórmula en sí tiene un aspecto perfectamente correcto. Este es con diferencia el fallo más común de los rangos con nombre, y el filtro de errores del Administrador los encuentra todos de una vez.
2. Nombres fantasma de hojas copiadas. Copia una hoja a otro libro y sus nombres de ámbito de hoja van también. Hazlo unas cuantas veces y aparece el diálogo al que todo el mundo ha dado Sí cien veces sin leerlo: "Una fórmula u hoja que desea mover o copiar contiene el nombre X, que ya existe." Cada Sí deja otro nombre muerto detrás, normalmente apuntando al libro original.
3. Vínculos externos que no se mueren. Que es lo que son esos nombres muertos. Un nombre que pone ='C:\Users\...\[Presupuesto v7.xlsx]Hoja1'!$B$2 es la razón por la que Excel pregunta si actualizar vínculos en un archivo que aparentemente no tiene ninguno. Datos → Editar vínculos enseña el vínculo; el Administrador de nombres enseña el motivo.
4. La sombra de la sección 3. Un nombre de ámbito de hoja anulando en silencio el de ámbito de libro que querías.
5. Nombres creados por complementos. Algunos complementos escriben nombres ocultos, que el Administrador de nombres no muestra en absoluto. Si un libro va misteriosamente lento o abultado y el Administrador se ve limpio, los nombres ocultos son sospechosos serios; solo VBA o un limpiador de terceros los listará.
6. Nombres que nadie usa. El lento. Cincuenta nombres se acumulan en tres años, ocho sostienen algo, y nadie sabe cuáles ocho. Borrar un nombre que sigue en uso es instantáneo e irreversible en el mismo clic: todas las fórmulas que lo usaban se vuelven #¿NOMBRE?. Antes de una limpieza, Fórmulas → Utilizar en la fórmula → Pegar lista vuelca todos los nombres y sus referencias en celdas, lo que te da algo contra lo que buscar en el libro.
9) Las Reglas y las Convenciones que Merece la Pena Adoptar
Excel impone estas:
- Sin espacios.
Travel kmse rechaza;Travel_kmyTravelKmvalen. - No puede parecerse a una referencia de celda.
Q1es una dirección real, así que no puede ser un nombre — lo que pilla a todo el que nombra trimestres.Q1_SalesoTrim1funcionan. C,c,Ryrestán reservadas por sí solas. Son la abreviatura de fila/columna del mundo F1C1.- Empieza por letra, guion bajo o barra invertida, nunca por un dígito.
- 255 caracteres como máximo, lo cual no es una invitación.
- No distingue mayúsculas, como se vio en la sección 2.
Y estas son convenciones, no reglas — merecen la pena porque hacen que un nombre se lea de un vistazo:
Di qué es, no dónde está. Hours gana a ColumnaD. VATRate gana a Rate2.
Prefija por tipo si tienes más de una docena. rng para rangos, c para constantes, tbl para tablas — cVATRate, rngRegions. Es feo, y ordena el Administrador de nombres en algo navegable.
Pon el comentario. El cuadro Asignar nombre tiene un campo Comentario que casi nadie rellena. Aparece como información sobre herramientas en la lista de autocompletado. "Tipo general, 20%, sin cambios desde abril de 2011" cuesta diez segundos y responde una pregunta que si no tendrán que hacerte a ti.
10) Cuándo No Poner Nombre
Nombrar no es gratis, y un libro puede tener demasiados nombres con la misma facilidad con que puede tener pocos. Tres casos en los que una referencia normal es la mejor respuesta:
Fórmulas de un solo uso. Un nombre usado una vez es una indirección extra sin ninguna recompensa. =SUMA(D2:D11) en un cálculo de borrador es más claro que =SUMA(Hours), porque D2:D11 se puede verificar mirando la hoja y Hours no.
Cualquier cosa que una Tabla ya te dé. Si los datos son una Tabla, Jobs[Hours] ya es un nombre, y encima autodocumentado y de tamaño automático. Definir un segundo nombre sobre la misma columna es un sinónimo que ahora tienes que mantener sincronizado.
Cuando el nombre mentiría. Sales apuntando a D2:D11 era cierto en enero. Se insertaron filas, alguien reutilizó la columna, y ahora Sales se refiere a unidades. Una dirección ligeramente equivocada es un error que encuentras en diez segundos; un nombre ligeramente equivocado es un error que se lee como correcto durante un año. Los nombres son una promesa, y el coste de mantener esa promesa es real.
La regla honesta: ponle nombre si se usa en más de una fórmula, si tiene que aparecer en un diálogo que no acepta referencias, o si representa una regla de negocio en lugar de una ubicación. Si no, déjalo en paz.
11) Nueve Formas en que los Nombres Salen Mal
- Escribir el nombre en el Cuadro de nombres y hacer clic fuera — sin Intro no hay nombre, y no hay aviso.
- Una columna borrada dejando un nombre
#¡REF!— la fórmula se ve bien y devuelve un error. - Un nombre de hoja eclipsando a uno de libro — la forma correcta, el número equivocado.
- Crear desde la selección sobre un bloque entero — siete nombres que no pediste, uno de ellos
Rate. - Rangos dinámicos con
CONTARAy un valor perdido debajo de los datos — un rango de 400 filas y un gráfico con una cola vacía muy larga. DESREFen cuarenta nombres — un libro volátil que se atasca con cada tecla.- Arrastrar
[@Hours]hacia el lado — las referencias estructuradas se desplazan por columnas, exactamente igual que las relativas. - Una fórmula con nombre escondiendo un cálculo real — la celda dice
=NetTotaly nada más lo dice. - Borrar un nombre en uso —
#¿NOMBRE?instantáneo por todas partes, y ningún aviso de deshacer que valga.
Conclusión
Los nombres definidos y las referencias estructuradas resuelven el mismo problema desde dos direcciones. Los nombres son deliberados: decides que algo merece una palabra y pagas un pequeño coste de mantenimiento para siempre. Las Tablas son automáticas: pulsas Ctrl+T, renombras la tabla, y cada columna se convierte en un nombre que se redimensiona solo y no puede quedarse desfasado.
Si te llevas una sola costumbre de este artículo, llévate la Tabla. Te da fórmulas legibles, un rango que crece y una fila de encabezados que sigue significando lo que dice, a cambio de una tecla y un cambio de nombre. Después define nombres para el puñado de cosas que una Tabla no puede expresar —el tipo de IVA, el umbral de horas extra, la lista que alimenta un desplegable dos hojas más allá— y abre el Administrador de nombres una vez por trimestre para borrar lo que se haya podrido.
La prueba de si funcionó es sencilla. Abre el libro dentro de seis meses, haz clic en una celda del bloque de resumen y lee la barra de fórmulas. Si te dice qué significa el número antes de que tengas que ir a mirar de dónde salió, los nombres hicieron su trabajo.
¿Quieres practicar? Varios ejercicios de la app están construidos exactamente sobre estas formas — una búsqueda contra un baremo de tarifas, un total condicional sobre una columna que después gana una fila, y uno en el que el rango que necesitas es el que creció.