Utilizar referencias estructuradas con las tablas de Excel

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:

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.

Una tabla del departamento de ventas

Tabla de ejemplo 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)

Volver al principio

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.

Volver al principio

Nombres de tabla y especificadores de columna

Cada vez que se inserta una tabla, Microsoft Office Excel crea un nombre de tabla predeterminado (Tabla1, Tabla2, etc.) en el nivel o ámbito global del libro. Puede cambiar fácilmente este nombre con el fin de darle un mayor significado. Por ejemplo, para cambiar Tabla1 a VentasDept, puede utilizar el cuadro de diálogo Editar nombre (en la ficha Diseño, en el grupo Propiedades, edite el nombre de la tabla en el cuadro Nombre de la tabla).

El nombre de una tabla hace referencia a todo el rango de datos de la tabla, exceptuando las filas de encabezado y de totales. 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.

Volver al principio

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, en el 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

Volver al principio

Especificadores de elementos especiales

Para una mayor comodidad, también puede utilizar elementos especiales para hacer referencia a diversas partes de una tabla, como la fila Totales, con el fin de que sea más fácil incluir referencias a estas partes de la tabla en las fórmulas. A continuación, se muestran los especificadores de elementos especiales que puede utilizar en una referencia estructurada:

Este especificador de elemento especial:

Hace referencia a:

Que, en el 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]

Sólo la parte de las columnas de la fila actual. #Esta fila 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. Para obtener más ejemplos, vea Ejemplos de uso de referencias estructuradas.

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

Volver al principio

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.

Volver al principio

Ejemplos de uso de referencias estructuradas

Los elementos especiales se pueden utilizar y combinar con nombres de tablas y referencias de columnas de muchas formas, como se muestra a continuación:

Esta referencia estructurada:

Hace referencia a:

Que, en el Ejemplo , es el rango de celdas:

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

Todas las celdas de la columna ImptVentas.

C1:C8

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

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]]

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

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)

Volver al principio

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 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 Trabajando con fórmulas de la categoría Fórmulas del cuadro de diálogo Opciones de Excel.

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 deshabilita los encabezados de columna de una tabla (En la ficha Diseño, en el grupo de opciones Estilos de tabla, desactive la casilla de verificación Fila de encabezado), esto no afecta a las referencias estructuradas que utilizan estos encabezados y puede seguir utilizándolas 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    

Cuando se copia o mueve una fórmula se conservan todas las referencias estructuradas que utilice esa fórmula.

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:

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

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.

Volver al principio

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 (]), símbolo de almohadilla (#) y comillas simples (').

Ejemplo     =ResumenVentasDeptAño['#c]

Utilizar 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 de cierre (]).

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

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

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

Volver al principio

Se aplica a: Excel 2007



¿Le ha sido útil esta información?

No

¿Cómo podemos mejorarlo?

255 caracteres restantes

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

¡Gracias por sus comentarios!

Recursos de soporte técnico

Cambiar idioma