Usar referencias estructuradas con las tablas de Excel

Cuandocree una tabla de Excel, Excel asigna un nombre a la tabla y a cada columna de la tabla. Cuando agrega fórmulas a una tabla de Excel, dichos nombres pueden aparecer automáticamente conforme introduce la fórmula, y no tiene que especificar referencias de celda explícitas. Este es un ejemplo de lo que hace Excel:

En lugar de usar referencias de celda explícitas

Excel usa nombres de tabla y columna

=SUMA(C2:C7)

=SUMA(VentasDept[Importe de ventas])

Dicha combinación de nombres de tabla y columna se denomina una referencia estructurada, y Excel las usa porque los nombres ajustan siempre que agregue o quite datos de la tabla.

Las referencias estructuradas también aparecen cuando crea una fórmula fuera de una tabla de Excel que hace referencia a los datos de la tabla. Las referencias pueden facilitar la localización de tablas en un libro grande.

Para usar referencias estructuradas en la fórmula, haga clic en las celdas de la tabla a las que desea hacer referencia en lugar de escribir su referencia de celda en la fórmula. Vamos a usar los siguientes datos de ejemplo para introducir una fórmula que usa automáticamente referencias estructuradas para calcular la cantidad de una comisión de venta.

Vendedor

Región

Sales[Amount]

Comisión

Comisión

Joe

Norte

260

10%

Robert

Sur

660

15%

Michelle

Este

940

15%

Erich

Oeste

410

12%

Dafna

Norte

800

15%

Rob

Sur

900

15%

  1. Copie los datos de ejemplo en la tabla de arriba, incluidos los encabezados de columna y pegarlo en la celda A1 de una nueva hoja de cálculo de Excel en blanco.

  2. Para crear la tabla, seleccione la celda A1 y presione Ctrl+T.

  3. Compruebe que ha seleccionado los datos correctos y que la casilla La tabla tiene encabezados está activada. A continuación, haga clic en Aceptar.

  4. Haga clic en la celda E2, escriba un signo igual (=) y haga clic en la celda C2.

En la barra de fórmulas, la referencia estructurada [ @ [Importe de ventas]] aparece después del signo igual.

  1. Escriba un asterisco (*) directamente después del corchete de cierre y haga clic en la celda D2.

En la barra de fórmulas, la referencia estructurada [ @ [% de Comisión]] aparece después del asterisco.

  1. Presione Entrar.

Excel crea automáticamente una columna calculada y copia la fórmula debajo de toda la columna para usted, si se ajusta para cada fila. Para obtener más información acerca de las columnas calculadas, vea Usar columnas calculadas en una tabla de Excel.

¿Qué ocurre cuando uso referencias de celda explícitas?

Si especifique referencias de celda explícitas en una columna calculada, puede resultar más difícil ver qué está calculando la fórmula.

  1. En su hoja de cálculo de Excel de muestra, haga clic en la celda E2

  2. En la barra de fórmulas, escriba =C2*D2 y presione Entrar.

Observe que mientras Excel copia su fórmula debajo de la columna, no usa referencias estructuradas. Si, por ejemplo, agrega una columna entre las columnas existentes C y D, tiene que revisar la fórmula.

¿Cómo puedo cambiar un nombre de tabla?

Siempre que crea una tabla Excel, Excel crea un nombre de tabla predeterminado (Tabla1, Tabla2, etc.). Puede cambiar el nombre de la tabla para que sea más significativo.

  1. Haga clic en cualquier lugar de la tabla para mostrar Herramientas de tabla de la cinta de opciones.

  2. Diseño en la pestaña, en el cuadro Nombre de la tabla, escriba el nombre que quiera y presione Entrar.

En nuestros datos de ejemplo, hemos usado el nombre VentasDept.

Siga las siguientes reglas para los nombres de tabla:

  • Use caracteres válidos Inicie siempre un nombre con una letra, un carácter de subrayado (_) o una barra diagonal inversa (\). Use letras, números, puntos y caracteres de subrayado en el resto del nombre.

No puede usar "C", "c", "R" o "r" para el nombre, porque ya están designados como un acceso directo para seleccionar la columna o fila de la celda activa al escribirlas en el cuadro Nombre o Ir a.

  • No use referencias de celda Los nombres no pueden ser iguales al de una referencia de celda, tales como Z$100 y R1C1.

  • No use un espacio para separar las palabras No se pueden usar espacios en el nombre. Puede no usar ningún espacio o escribir el carácter de subrayado (_) y el punto (.) como separadores de palabras. Por ejemplo, DepartamentoVentas, Impuesto_Ventas o Primer.Trimestre.

  • No utilice más de 255 caracteres un nombre de tabla puede tener hasta 255 caracteres.

  • Usar nombres de tabla única nombres duplicados no están permitidos. Excel no distingue entre mayúsculas y minúsculas en los nombres si introduces "Ventas" pero si ya tiene otro nombre llamado "VENTAS" en el mismo libro, se le pedirá que elija un nombre único.

Reglas de sintaxis de las referencias estructuradas

También puede introducir o cambiar las referencias estructuradas manualmente en la fórmula pero para ello, tendrá ayudar a comprender la sintaxis de una referencia estructurada. Repasemos la fórmula siguiente ejemplo:

=SUMA(VentasDept[ [ #Totales] ; [Importe de ventas] ] ;VentasDept[ [ #Datos] ; [Importe de la Comisión] ])

Esta fórmula tiene los siguientes componentes de referencia estructurada:

  • Nombre de tabla   VentasDept es un nombre de tabla personalizado. Se hace referencia a los datos de tabla, sin ningún encabezado o las filas de totales. Puede usar un nombre de tabla predeterminado, como Tabla1, o cambiarlo para usar un nombre personalizado.

  • Especificador de columna:   [Importe Ventas]e[Importe Comisión] son los especificadores de columna que utilizan los nombres de las columnas que representan. Éstos hacen referencia los datos de la columna, sin cualquier encabezado de columna o fila de totales. Encierre siempre los especificadores entre corchetes como se muestra.

  • Especificador de elemento:   [#Totales] y [#Datos] son especificadores de elementos especiales que hacen referencia a partes específicas de la tabla, como la fila Totales.

  • Especificador de tabla:   [ [ #Totales] ; [Importe de ventas]] y [ [ #Datos] ; [Importe de la Comisión]] se tabla partes externas que representan los especificadores de la referencia estructurada. Las referencias externas siguen el nombre de tabla y las encierra entre corchetes.

  • Referencia de estructuras:   (VentasDept[ [ #Totales] ; [Importe de ventas]] y VentasDept[ [ #Datos] ; [Importe de la Comisión]] sonreferencias estructuradas, representado por una cadena que comienza con el nombre de la tabla y termina con el especificador de columna.

Cuando cree o edite referencias estructuras, use estas reglas de sintaxis:

  • Utilizar especificadores entre corchetes angulares    Todos los especificadores de tablas, columnas y elementos especiales deben ir incluidos entre corchetes ([ ]). Un especificador que contenga otros especificadores requiere corchetes externos para incluir los corchetes internos de los otros especificadores. Por ejemplo: =VentasDept[[Comercial]:[Región]]

  • Los encabezados de columna son cadenas de texto    Pero no requieren presupuestos cuando estos se utilizan en una referencia estructurada. Números o fechas, como 2014 o 1/1/2014, también se consideran cadenas de texto. No puede usar expresiones con los encabezados de columna. Por ejemplo, la expresión ResumenVentasDeptAño[ [ 2014] : [ 2012]] no funcionará.

Usar corchetes en torno los encabezados de columna con caracteres especiales    Si hay caracteres especiales, todo el encabezado de columna debe incluirse entre corchetes, lo que significa que corchetes dobles son obligatorios en un especificador de columna. Por ejemplo: =ResumenVentasDeptAño[ [Total $ Cantidad]]

A continuación se ofrece la lista de caracteres especiales que necesite otro entre corchetes en la fórmula:

  • Tabulador

  • Avances de línea

  • Retorno de carro

  • coma

  • Dos puntos

  • Período

  • Llave de apertura

  • Llave de cierre

  • Almohadilla (#).

  • Comilla simple ( ')

  • Comillas dobles ( ")

  • Izquierda entre llaves ( { } )

  • Haga clic con el botón secundario llave ( })

  • Signo de dólar

  • Acento circunflejo ( ^)

  • Y comercial

  • Asterisco ( * )

  • Signo más

  • Signo de igualdad

  • Signo menos

  • Símbolo mayor que ( >)

  • Símbolo Menor que ( <)

  • División (/)

  • Use un carácter de escape para algunos caracteres especiales en los encabezados de columna    Algunos caracteres tienen un significado especial y requieren el uso de comillas simples ( ') como un carácter de escape. Por ejemplo: =ResumenVentasDeptAño[ '#DeElementos]

Aquí se ofrece la lista de caracteres especiales que necesitan un carácter de escape (‘) en la fórmula:

  • Llave de apertura

  • Llave de cierre

  • Almohadilla (#)

  • Comilla simple ( ')

Usar el carácter de espacio para mejorar la legibilidad en una referencia estructurada    Los caracteres de espacio se pueden utilizar para mejorar la legibilidad de la forma siguiente: Por ejemplo: =VentasDept[ [Comercial]:[Región] ] o =VentasDept[ [ #Encabezados], [ #Datos], [% Comisión]]

Es recomendable usar un espacio:

  • Después de un corchete de apertura ( [)

  • Antes de un corchete de cierre ( ] ).

  • Después de un punto y coma.

Operadores de referencia

Para una mayor flexibilidad cuando especifique rangos de celdas, puede utilizar los operadores de referencia siguientes para combinar especificadores de columna.

Esta referencia estructurada:

Hace referencia a:

Mediante:

Que es el rango de celdas:

=VentasDept[[Comercial]:[Región]]

Todas las celdas de dos o más columnas adyacentes

dos puntos (:) u operador de rango

A2:B7

=VentasDept[Importe de ventas] ;VentasDept[Importe de la Comisión]

Una combinación de dos o más columnas

punto y coma (;) u operador de unión

C2:C7; E2:E7

=VentasDept[ [Representante de ventas] : [Importe de ventas]] VentasDept[ [Región] : [% de Comisión]]

La intersección de dos o más columnas

(espacio) u operador de intersección

B2:C7

Especificadores de elementos especiales

Para hacer referencia a partes específicas de una tabla, como la fila de totales, puede usar cualquiera de los siguientes especificadores de elementos especiales de la referencias estructuradas.

Este especificador de elemento especial:

Hace referencia a:

#Todas

Toda la tabla, incluidos los encabezados de columna, datos y totales (si los hay).

#Datos

Solo las filas de datos.

#Encabezados

Solo la fila de encabezado.

#Totales

Solo la fila del total. Si no hay ninguna, devuelve un valor nulo.

#Esta Fila

O bien

@

O bien

@ [Nombre de columna]

Solo las celdas en la misma fila que la fórmula. Estos especificadores no se puede combinar con ningún especificador de elemento especial. Utilícela para forzar una intersección implícita de la referencia o para invalidar ese comportamiento y hacer referencia a valores individuales de una columna.

Excel cambia automáticamente de #Esta Fila a los especificadores @ en las tablas en las que tiene más de una fila de datos. Si la tabla tiene solo una fila, Excel no reemplaza los especificadores #Esta Fila, lo que puede ocasionar cambios inesperados en los resultados del cálculo al agregar más filas. Para evitar problemas de cálculo, asegúrese de escribir varias filas en la tabla antes de introducir cualquier fórmula de referencia estructurada.

Calificar referencias estructuradas de columnas calculadas

Cuando crea una columna calculada, a menudo usa una referencia estructurada para crear la fórmula. Esta referencia estructurada puede tener un nombre no completo o parcialmente completo. Por ejemplo, para crear la columna calculada denominada ImptCom, que calcula el importe de las comisiones en dólares, puede utilizar las siguientes fórmulas:

Tipo de referencia estructurada

Ejemplo

Comentario

No calificada

= [Ventas Amount] * [% de Comisión]

Multiplica los valores correspondientes en la fila actual.

Nombre completo

=VentasDept[Ventas Amount] * VentasDept[% de Comisión]

Multiplica los valores correspondientes de ambas columnas para cada fila.

La regla general es la siguiente: si usa referencias estructuradas en una tabla, como cuando crea una columna calculada, puede usar una referencia estructurada no calificada, pero si usa esta referencia fuera de la tabla, deberá usar una referencia estructurada con un nombre completo válido.

Ejemplos de uso de referencias estructuradas

Estas son algunas maneras de usar referencias estructuradas.

Esta referencia estructurada:

Hace referencia a:

Que es el rango de celdas:

=VentasDept[ [ #Todas] ; [Importe de ventas] ].

Todas las celdas de la columna Sales Amount.

C1:C8

=VentasDept[ [ #Encabezados] ; [% de Comisión]]

La cabecera de la columna % de Comisión.

D1

=VentasDept[[#Totales];[Región]]

El total de la columna Región. Si no hay ninguna fila Totales, devuelve un valor nulo.

B8

=VentasDept[ [ #Todas] ; [Importe de ventas] : [% de Comisión]]

Todas las celdas de Importe de ventas y % de Comisión.

C1:D8

=VentasDept[ [ #Datos] ; [% de Comisión] : [Importe de la Comisión]]

Solo los datos de las columnas Importe % y la Comisión.

D2:E7

=VentasDept[ [ #Encabezados] ; [Región] : [Importe de la Comisión]]

Solo los encabezados de las columnas entre Región y PctCom e ImptCom.

B1:E1

=VentasDept[ [ #Totales] ; [Importe de ventas] : [Importe de la Comisión]]

Totales de las columnas ImptVentas a ImptCom. Si no hay ninguna fila Totales, devuelve un valor nulo.

C8:E8

=VentasDept[ [ #Encabezados] ; [ #Datos] ; [% de Comisión]]

Solo el encabezado y los datos de PctCom.

D1:D7

=VentasDept[ [ #Esta Fila], [Importe de la Comisión]]

O bien

=VentasDept[ @Importe de la Comisión]

La celda ubicada en la intersección de la fila actual y la columna ImptCom. Si se utiliza en la misma fila que un encabezado o fila total, esto devolverá un # ¡VALOR! .

Si escribe el formulario ya de esta referencia estructurada ( #Esta fila) en una tabla con varias filas de datos, Excel reemplaza automáticamente con la forma más corta ( @ ). Ambas funcionan de la misma.

E5 (si la fila actual es la fila 5)

Estrategias para trabajar con referencias estructuradas

Tenga en cuenta lo siguiente cuando trabaje con referencias estructuradas.

  • Usar Fórmula Autocompletar    Se dará cuenta de que usar Fórmula Autocompletar para escribir referencias estructuradas es muy útil y, además, garantiza que se emplea la sintaxis correcta. Para obtener más información, consulte Usar Fórmula Autocompletar.

  • Decidir si se generan referencias estructuradas para tablas en semiselección    De forma predeterminada, cuando crea una fórmula, si hace clic en un rango de celdas de una tabla, se seleccionan algunas celdas y automáticamente se escribe una referencia estructurada, en vez del rango de celdas en la fórmula. De este modo, es mucho más fácil escribir una referencia estructurada. Puede habilitar o deshabilitar este comportamiento activando o desactivando la casilla de verificación Usar nombres de tabla en las fórmulas, en la sección Trabajar con fórmulas de la categoría Fórmulas del cuadro de diálogo Opciones de Excel.

  • Usar libros que contienen vínculos externos a tablas de Excel en otros libros    Si un libro contiene un vínculo externo a una tabla Excel en otro libro, dicho libro "de origen" vinculado debe abrirse en Excel para evitar códigos de error #REF! en el libro de "destino" que contiene los vínculos. Si abre el libro de destino en primer lugar y aparecen códigos de error #REF!, se resolverán si abre entonces el libro de origen. Si abre el libro de origen primero, no debería ver códigos de error.

  • Convertir un rango en una tabla y viceversa    Cuando convierte una tabla en un rango, todas las referencias de celda cambian a las referencias absolutas equivalentes de estilo A1. Cuando convierte un rango en una tabla, Excel no cambia automáticamente ninguna referencia de celda de este rango a sus referencias estructuradas equivalentes.

  • Desactivar los encabezados de columna   si desactiva los encabezados de columna de una tabla (En la tabla, en la pestaña DiseñoOpciones de estilo de tabla grupo, desactive Fila de encabezado), las referencias estructuradas que utilizan los nombres de columna no se vea afectado, y puede seguir utilizándolas en las fórmulas. Las referencias estructuradas que hacen referencia directamente a los encabezados de tabla (por ejemplo, =VentasDept[ [ #Encabezados] ; [ %Comisión]]) tendrá como resultado # ¡REF.

  • Agregar o eliminar columnas y filas de la tabla    Como los rangos de datos de la tabla cambian con frecuencia, las referencias de celda de las referencias estructuradas se ajustan automáticamente. Por ejemplo, si usa un nombre de tabla en una fórmula para contar las celdas de datos en una tabla y, a continuación, agrega una fila de datos, la referencia de celda se ajusta automáticamente.

  • Cambiar el nombre de una tabla o columna    Si cambia el nombre de una columna o tabla, Excel cambia automáticamente el uso de esa tabla o encabezado de columna en todas las referencias estructuradas que se utilizan en el libro.

  • Mover, copiar y rellenar referencias estructuradas    Cuando copia o mueve una fórmula con referencias estructuradas, las referencias estructuradas permanecen iguales.

    Nota: No es lo mismo copiar una referencia estructurada que realizar un relleno de una referencia estructurada. Al copiar, todas las referencias estructuradas permanecen iguales, mientras que al rellenar una fórmula, las referencias estructuradas completas ajustan los especificadores de columna como una serie, tal y como se resume en la tabla siguiente.

Si la dirección de relleno es:

Y mientras rellena la tabla, presiona:

Entonces

Arriba o abajo

Nada

No se ajusta ningún especificador de columna.

Arriba o abajo

Ctrl

Los especificadores de columna se ajustan como una serie.

Derecha o izquierda

Ninguna

Los especificadores de columna se ajustan como una serie.

Arriba, abajo, derecha o izquierda

Mayús

Se mueven los valores actuales de las celdas, en vez de sobrescribirlos, y se insertan especificadores de columna.

Vea también

Información general sobre fórmulas

Ampliar sus conocimientos
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×