Usar referencias estructuradas con las tablas de Excel

Importante:  Este artículo se ha traducido con traducción automática; vea la declinación de responsabilidades. Para su referencia, puede encontrar la versión en inglés de este artículo aquí.

Al crear una tabla de Excel, Excel asigna un nombre a la tabla y a cada encabezado de columna en la tabla. Al agregar fórmulas a una tabla de Excel, los nombres pueden aparecer automáticamente como escribir la fórmula y seleccione las referencias de celda de la tabla en lugar de escribir manualmente ellos. Aquí tiene un ejemplo de las funciones de 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.

Ventas Persona

Región

Sales[Amount]

Comisión

Importe 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. Copiar los datos de ejemplo en la tabla anterior, incluidos los encabezados de columna y pegarlo en la celda A1 de una nueva hoja de cálculo de Excel.

  2. Para crear la tabla, seleccione cualquier celda del rango de datos y a continuación, presione Ctrl+t.

  3. Asegúrese de que la casilla la tabla tiene encabezados está activada y 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.

  5. Escriba un asterisco (*) directamente después del corchete de cierre y a continuación, haga clic en la celda D2.

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

  6. 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 la hoja de cálculo 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. Seleccione cualquier celda de la tabla para mostrar las Herramientas de tabla > ficha Diseño en la cinta de opciones.

  2. Escriba el nombre que desee en el cuadro Nombre de tabla y presione ENTRAR.

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

Utilice las siguientes reglas para nombres de tabla:

  • Utilizar caracteres válidos  Iniciar siempre un nombre con una letra, un carácter de subrayado (_) o una barra invertida (\). Usar letras, números, puntos y caracteres para el resto del nombre de subrayado. No puede usar "C", "c", "R" o "r" para el nombre, ya que ya están designado como un acceso directo para seleccionar la columna o fila de la celda activa al escribir en el cuadro nombre o Ir a .

  • No use referencias de celda  Nombres no pueden ser idénticos a una referencia de celda, como Z$ 100 o R1C1.

  • No use un espacio para separar palabras  No se pueden usar espacios en el nombre. Puede usar el carácter de subrayado (_) y un punto (.) como separadores de palabras. Por ejemplo, VentasDept, 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.

  • Usar un identificador de objeto  Si va a tener una combinación de tablas, tablas dinámicas y gráficos, es una buena idea prefijo sus nombres con el tipo de objeto. Por ejemplo: tbl_Sales para una tabla de ventas, pt_Sales para una tabla dinámica de ventas y chrt_Sales para un gráfico de venta o ptchrt_Sales de un gráfico dinámico venta. Esto mantiene todos los nombres en una lista ordenada en el Administrador de nombres.

Reglas de sintaxis de las referencias estructuradas

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

=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 la tabla:   VentasDept es un nombre de tabla personalizada. Hace referencia a los datos de tabla, sin ninguna fila de encabezado o total. Puede usar un nombre de tabla predeterminado, como Tabla1, o cambiar para usar un nombre personalizado.

  • Especificador de columna:   [Importe de ventas]y[Importe Comisión] son especificadores de columna que utilizan los nombres de las columnas que representan. Que hacen referencia los datos de la columna, sin ninguna fila de encabezado o total de columna. Incluir siempre especificadores entre paréntesis tal como se muestra.

  • Especificador de elemento:   [#Totals] y [#Data] 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 estructurada:   (VentasDept [[#Totals], [importe de ventas]] y VentasDept [[#Data], [importe Comisión]] son referencias estructuradas, representadas 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:

  • Especificadores entre corchetes de uso    Todos los especificadores de elementos especiales, columnas y tablas necesitan incluirse entre corchetes ([]). Un especificador que contenga otros especificadores requiere corchetes externos para incluir los corchetes internos de los otros especificadores. Por ejemplo: = VentasDept [[representante de ventas]: [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 en la intersección de la fila actual y la columna de la Comisión. Si se usa en la misma fila como un encabezado o una fila de totales, devolverá un #VALUE! error.

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 el punto y selecciones    De forma predeterminada, cuando se crea una fórmula, haga clic en una celda intervalo dentro de una tabla se seleccionan algunas de las celdas y especificará automáticamente una referencia estructurada en lugar de en el rango de celdas de la fórmula. Este comportamiento semiselección facilita mucho introducir una referencia estructurada. Puede activar o desactivar este comportamiento activando o desactivando la casilla de verificación usar nombres de tabla en las fórmulas en el archivo > Opciones > fórmulas > cuadro de diálogo trabajando con fórmulas.

  • Libros de uso con vínculos externos a las tablas de Excel en otros libros    Si un libro contiene un vínculo externo a una tabla de Excel en otro libro, ese libro de origen vinculado debe estar abierto en Excel para evitar #REF! errores en el libro de destino que contiene los vínculos. Si abre el libro de destino en primer lugar y #REF! aparecen errores, se ha resueltos si, a continuación, abra el libro de origen. Si abre el libro de origen en primer lugar, 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    Puede activar o desactivar los encabezados de columna de tabla y desactivar desde la pestaña Diseño de tabla > Fila de encabezado. Si desactiva los encabezados de columna de tabla, las referencias estructuradas que utilicen nombres de columna no se ven afectadas y puede usarlos en las fórmulas. Referencias que hacen referencia directamente a los encabezados de tabla estructuradas (por ejemplo, = VentasDept [[#Headers], [% de Comisión]]) se traducirán en #REF.

  • Agregar o eliminar columnas y filas de la tabla    Porque los rangos de datos de tabla cambian a menudo, referencias de celda de referencias estructuradas se ajustan automáticamente. Por ejemplo, si usar un nombre de tabla en una fórmula para contar todas las celdas de datos de una tabla y, a continuación, agregue 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.

¿Necesitas más ayuda?

Siempre puede preguntar a un experto en Excel Tech Community, obtener soporte técnico en la Comunidad de respuestas o sugerir una característica nueva o mejora en el UserVoice de Excel.

Temas relacionados

Información general sobre las tablas de Excel
vídeo: crear y aplicar formato a una tabla de Excel
Total de los datos en una tabla de Excel
formato de una tabla de Excel
cambiar el tamaño de una tabla agregando o quitando filas y columnas
Filtrar datos en un rango o tabla
convertir una tabla en un rango
problemas de compatibilidad de tabla de Excel
exportar una tabla de Excel en SharePoint
general de las fórmulas en Excel

Nota: Declinación de responsabilidades de traducción automática: Este artículo se ha traducido con un sistema informático sin intervención humana. Microsoft ofrece estas traducciones automáticas para que los hablantes de otros idiomas distintos del inglés puedan disfrutar del contenido sobre los productos, los servicios y las tecnologías de Microsoft. Puesto que este artículo se ha traducido con traducción automática, es posible que contenga errores de vocabulario, sintaxis o gramática.

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.

×