Iniciar sesión

Utilizar 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í.

Las referencias estructuradas hacen que el trabajo con los datos de las tablas sea más fácil y más intuitivo cuando se utilizan fórmulas que hacen referencia a una tabla, ya sea a partes de una tabla o a toda la tabla. Son especialmente útiles porque los rangos de datos de la tabla cambian a menudo y las referencias de celda de referencias estructuradas se ajustan automáticamente. De este modo, se reduce de forma considerable la necesidad de volver a escribir fórmulas cuando se agregan o eliminan filas y columnas de una tabla o se actualizan los datos externos.

Esta referencia estructurada es más fácil de entender:

Que la referencia de celda:

=SUMA(VentasDept[ImptVentas])

=SUMA(C2:C7)

En este artículo

Ejemplo de la tabla del departamento de ventas

Componentes de una referencia estructurada

Nombres de tabla y especificadores de columna

Operadores de referencia

Especificadores de elementos especiales

Calificar referencias estructuradas de columnas calculadas

Ejemplos de uso de referencias estructuradas

Trabajar con referencias estructuradas

Reglas de sintaxis de las referencias estructuradas

Ejemplo de la tabla del departamento de ventas

A continuación, se muestra un ejemplo, al que se hace referencia a lo largo de todo este artículo, de una tabla basada en las ventas de un departamento formado por seis empleados, que incluye los importes y comisiones de ventas más recientes.

Tabla de ejemplo del departamento de ventas
Una tabla del departamento de ventas

1. La tabla completa (A1:E8)

2. Los datos de la tabla (A2:E7)

3. Una columna y un encabezado de columna (D1:D8)

4. Una columna calculada (E1:E8)

5. La fila Totales (A8:E8)

Inicio de la página

Componentes de una referencia estructurada

Para trabajar con tablas y referencias estructuradas de forma eficaz, es necesario comprender cómo se crea la sintaxis de las referencias estructuradas al crear las fórmulas. Los componentes de una referencia estructurada se muestran en el ejemplo siguiente de una fórmula que suma los importes y las comisiones totales de las ventas.

Una fórmula con referencias estructuradas

1. Un nombre de tabla es un nombre significativo que hace alusión a los datos reales de la tabla (excluidas la fila de encabezados y la fila de totales, si las hay).

2. Un especificador de columna se obtiene a partir del encabezado de columna, va encerrado entre corchetes y hace referencia a los datos de la columna (excluidos el encabezado de columna y el total, si los hay).

3. Un especificador de elemento especial es una forma de hacer referencia a partes específicas de la tabla, como la fila Totales.

4. El especificador de tabla es la parte externa de la referencia estructurada que va entre corchetes y a continuación del nombre de la tabla.

5. Una referencia estructurada es toda la cadena que comienza con el nombre de la tabla y termina con el especificador de tabla.

Inicio de la página

Nombres de tabla y especificadores de columna

Cada vez que inserte una tabla, Microsoft Office Excel crea un nombre de tabla predeterminado (Tabla1, tabla2 etc.) en el nivel de libro global o ámbito. Puede cambiar fácilmente el nombre para que sea más significativo para usted. Por ejemplo, para cambiar Tabla1 a VentasDept, puede usar el cuadro de diálogo Editar nombre. (En la ficha Diseño, en el grupo Propiedades, editar el nombre de tabla en el cuadro Nombre de tabla ).

Un nombre de tabla se refiere a todo el rango de datos de la tabla excepto el encabezado y el total de filas. En el ejemplo de la tabla del departamento de ventas, el nombre de la tabla, VentasDept, hace referencia al rango de celdas a2: E7.

De forma similar a los nombres de tablas, los especificadores de columna representan referencias a los datos de toda la columna, a excepción del encabezado de columna y el total. En el ejemplo de la tabla del departamento de ventas, el especificador de columna [Región] hace referencia al rango de celdas B2:B7, y el especificador de columna [PctCom] hace referencia al rango de celdas D2:D7.

Inicio de la página

Operadores de referencia

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

Esta referencia estructurada:

Se refiere a:

Mediante:

Que, en la ejemplo , es el rango de celdas:

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

Todas las celdas de dos o más columnas adyacentes

dos puntos (:) u operador de rango

A2:B7

=VentasDept[ImptVentas];VentasDept[ImptCom]

Una combinación de dos o más columnas

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

C2:C7; E2:E7

=VentasDept[[Vendedor]:[ImptVentas]] VentasDept[[Región]:[PctCom]]

La intersección de dos o más columnas

(espacio) u operador de intersección

B2:C7

Inicio de la página

Especificadores de elementos especiales

Para mayor comodidad, también puede usar elementos especiales para hacer referencia a diversas partes de una tabla, como la fila de totales, para que sea más fácil hacer referencia a las partes siguientes en las fórmulas. Los siguientes son los especificadores de elementos especiales que puede usar en una referencia estructurada:

Este especificador de elemento especial:

Se refiere a:

Que, en la ejemplo , es el rango de celdas:

=VentasDept[#Todas]

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

A1:E8

=VentasDept[#Datos]

Sólo los datos.

A2:E7

=VentasDept[#Encabezados]

Sólo la fila de encabezado.

A1:E1

=VentasDept[#Totales]

Sólo la fila del total. Si no hay ninguna, devuelve un valor nulo.

A8:E8

=VentasDept[#Esta fila]

La parte de las columnas de la fila actual. #ThisRow no se puede combinar con otros especificadores de elementos especiales. Utilizar para forzar el comportamiento de intersección implícita de la referencia o para invalidar el comportamiento de intersección implícita y hacer referencia a valores únicos de una columna. Para obtener más ejemplos, vea ejemplos de uso de referencias estructuradas.

A5:E5 (si la fila actual es la fila 5)

Inicio de la página

Calificar referencias estructuradas de columnas calculadas

Cuando crea una columna calculada, normalmente utiliza una referencia estructurada para crear la fórmula. Esta referencia estructurada puede tener un nombre no completo o 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

=[ImptVentas]*[PctCom]

Multiplica los valores correspondientes en la fila actual.

Nombre completo

=VentasDept[ImptVentas]*VentasDept[PctCom]

Multiplica los valores correspondientes de ambas columnas para cada fila.

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

Inicio de la página

Ejemplos de uso de referencias estructuradas

Hay varias formas de utilizar estos elementos especiales y combinarlas con nombres de tabla y referencias de columna como se muestra en la siguiente información:

Esta referencia estructurada:

Se refiere a:

Que, en la ejemplo , es el rango de celdas:

=VentasDept[[#Todas];[ImptVentas]]

Todas las celdas de la columna ImptVentas.

C1:C8

=VentasDept[[#Encabezados];[PctCom]]

El encabezado de la columna PctCom.

C1

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

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

B8

=VentasDept[[#Todas];[ImptVentas]:[PctCom]]

Todas las celdas de ImptVentas y PctCom.

C1:D8

=VentasDept[[#Datos];[PctCom]:[ImptCom]]

Sólo los datos de las columnas PctCom e ImptCom.

D2:E7

=VentasDept[[#Encabezados];[Región]:[ImptCom]]

Los encabezados de las columnas entre región y ComPct ComAmt.

B1:E1

=VentasDept[[#Totales];[ImptVentas]:[ImptCom]]

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

C8:E8

=VentasDept[[#Encabezados];[#Datos];[PctCom]]

Sólo el encabezado y los datos de PctCom.

D1:D7

=VentasDept[[#EstaFila], [ImptCom]]

La celda ubicada en la intersección de la fila actual y la columna ImptCom.

E5 (si la fila actual es la fila 5)

Inicio de la página

Trabajar con referencias estructuradas

Tenga en cuenta lo siguiente cuando trabaje con referencias estructuradas.

Usar Fórmula Autocompletar   

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, vea Usar Fórmula Autocompletar.

Decidir si se generan referencias estructuradas para tablas en semiselección   

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 la sección trabajando con fórmulas de la categoría fórmulas en el cuadro de diálogo Opciones de Excel.

Libros que contienen vínculos externos con 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 se convierten a las referencias equivalentes de estilo A1. Cuando convierte un rango en una tabla, Excel no convierte automáticamente ninguna referencia de celda de este rango en sus nombres de tabla y referencias de columna equivalentes.

Desactivar los encabezados de columna   

Si desactiva los encabezados de columna de tabla (en la tabla ficha Diseño, en el grupo Opciones de estilo de tabla, borrar Fila de encabezado ), no se ven afectadas referencias estructuradas que utilizan estos encabezados y puede usarlos en las fórmulas.

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 utiliza un nombre de tabla en una fórmula para contar todas las celdas de datos que contiene la tabla del departamento de ventas, como =CONTARA(VentasDept) en el ejemplo de la tabla del departamento de ventas, el valor devuelto será 30 porque el rango de datos es A2:E7. Si, a continuación, agrega una fila de datos, la referencia de celda se ajusta automáticamente a A2:E8 y el nuevo valor devuelto es 35.

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   

Todas las referencias estructuradas se conservan las cuando copia o mueve una fórmula que usa una referencia estructurada.

Cuando rellena una fórmula, las referencias estructuradas que tengan nombres completos pueden ajustar los especificadores de columna como una serie, de la forma mostrada en la tabla siguiente.

Si la dirección de relleno es:

Y mientras rellena la tabla, presiona:

A continuación:

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

Nada

Los especificadores de columna se ajustan como una serie.

Derecha o izquierda

CTRL

No se ajusta ningún especificador de columna.

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.

Inicio de la página

Reglas de sintaxis de las referencias estructuradas

A continuación, se muestra una lista de las reglas de sintaxis que necesita conocer para crear y editar referencias estructuradas.

Nota: Los nombres de las tablas siguen las mismas reglas que las de los nombres definidos. Para obtener más información, vea Usar nombres para que las fórmulas sean más claras.

Uso de corchetes en los especificadores   

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.

Ejemplo     =VentasDept[ [Vendedor]:[Región] ]

Los encabezados de columna son cadenas de texto   

Todos los encabezados de columna son cadenas de texto, pero no es necesario que vayan entre comillas cuando se utilizan en una referencia estructurada. Si un encabezado de columna contiene números o fechas, como 2004 o 1/1/2004, se siguen considerando como cadenas de texto. Debido a que los encabezados de columna son cadenas de texto, no se pueden utilizar expresiones entre corchetes.

Ejemplo     =ResumenVentasDeptAño[[2004]:[2002]]

Caracteres especiales en encabezados de columna de tablas   

Si un encabezado de columna contiene uno de los siguientes caracteres especiales, se debe incluir todo el encabezado entre corchetes. De hecho, esto significa que los corchetes dobles son necesarios en un especificador de columna con los caracteres especiales siguientes: espacio, tabulación, avance de línea, retorno de carro, coma (,), dos puntos (:), punto (.), corchete de apertura ([), corchete de cierre (]), símbolo de almohadilla (#), comillas simples ('), comillas dobles ("), llave izquierda ({), llave derecha (}), símbolo de dólar ($), acento circunflejo (^), "y" comercial (&), asterisco (*), signo más (+), signo igual (=), signo menos (-), signo mayor que (>), signo menor que (<) y signo de división (/).

Ejemplo     =ResumenVentasDeptAño[[Importe$Total]]

La única excepción a esta regla es cuando sólo se utiliza el carácter especial de espacio.

Ejemplo    =VentasDept[Importe total]

Caracteres especiales en encabezados de columna que requieren la utilización del carácter de escape   

Los caracteres siguientes tienen un significado especial y requieren el uso de comillas simples (') como un carácter de escape: corchete de apertura ([]), corchete de cierre (]), apertura llave de cierre almohadilla, y comilla simple (').

Ejemplo     =ResumenVentasDeptAño['#c]

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:

  • Un carácter de espacio después de un corchete de apertura ([]) y antes de un corchete (]).

  • Un carácter de espacio después de un punto y coma.

Ejemplo     =VentasDept[ [Vendedor]:[Región] ]

Ejemplo     =VentasDept[[#Encabezados]; [#Datos]; [ImptCom]]

Inicio de la página

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.



¿Le ha sido útil esta información?

¿Cómo podemos mejorarlo?

¿Cómo podemos mejorarlo?

Para proteger su privacidad, no incluya información de contacto en sus comentarios. Revisar política de privacidad.

¡Gracias por sus comentarios!