Índice
Saltar al contenido
LAS REFERENCIAS ESTRUCTURADAS
Las referencias estructuradas nos permiten trabajar con los datos de las tablas de manera fácil y más intuitivo cuando empleamos fórmulas que hacen referencia a una tabla (muchas funciones trabajan con rangos, como BUSCARV / CONSULTARV, que no asumen encabezados).
Son útiles porque los rangos de datos de la tabla cambian a menudo y las referencias de celda de referencias estructuradas se ajustan automáticamente, sin tener que volver a escribir fórmulas cuando se agregan o eliminan filas y columnas de una tabla o se actualizan los datos externos.
Trabajando con tablas (Paso a paso)
Sobre nuestra hoja con datos de las ventas a los clientes deseamos adaptarla para interpretar los resultados.
ABRIR UN NUEVO LIBRO DE EXCEL
1.1. 
Descargar hoja de cálculo con los datos y descomprimir el archivo .zip, o también...
1.2. Ir a: Cinta de opciones
Ficha: Archivo Nuevo. Mostrará el cuadro de diálogo de archivo nuevo.
1.2.1. Ir a: Plantillas disponiblesDoble clic en Libro en blanco; o Ir a: Vista previa de Libro en blanco Pulsar sobre CREAR. Mostrará un libro nuevo.
1.2.2. Insertar los datos según se indica:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Cliente | Trimestre 1 | Trimestre 2 | Trimestre 3 | Trimestre 4 |
| 2 | Cliente 1 | 450,00 | 625,50 | 1.200,00 | 1.200,00 |
| 3 | Cliente 2 | 675,00 | 855,00 | 450,00 | 1.350,00 |
| 4 | Cliente 3 | 1.200,00 | 427,00 | 1.500,00 | 450,00 |
| 5 | Cliente 4 | 845,00 | 235,45 | 1.200,00 | 855,00 |
| 6 | Cliente 5 | 425,50 | 850,00 | 855,00 | 210,00 |
CREAR UNA TABLA
Cuando deseamos convertir el rango de datos en tabla de Excel.
2.1. Ir a: Cinta de opciones Ficha: Insertar Grupo: Tablas Tabla 
. Mostrará cuadro de diálogo de tabla.
2.1.1. Ir a: ¿Dónde están los datos de la tabla?Seleccionar el rango A1:E6. Datos con el encabezado.
2.1.2. Activar la Casilla: La lista tiene encabezados. Al incluir la fila1 el encabezado de los datos.
2.1.3. Clic Botón: Aceptar. Cierra la ventana y crea la tabla.
NOMBRE DE LA TABLA
Podemos aplicar a la tabla el nombre descriptivo deseado.
3.1.Ir a: Cinta de opciones Ficha: Diseño (Herramientas de tabla)Grupo: PropiedadesIr al cuadro: Nombre de la tabla Escribir: Ventas. O el nombre deseado.
OPERADORES DE REFERENCIA
Podemos utilizar los operadores de referencia para combinar especificadores de columna, especificando rangos de celdas.
DOS PUNTOS
Empleando el operador de dos puntos ":" u operador de rango para especificar todas las celdas de dos o más columnas adyacentes.
4.1.Clic sobre la celda A10 Escribir: 1 Semestre. Encabezado para el resultado.
4.2. Clic sobre la celda B10 Escribir la función: =SUMA(Ventas[[Trimestre 1]:[Trimestre 2]]).
Sumará todos los datos de la columna Trimestre 1 y Trimestre 2, equivale al rango B2:C6.
PUNTO Y COMA
Empleando el operador de punto y coma ";" u operador de unión para especificar una combinación de dos o más columnas.
5.1.Clic sobre la celda A11 Escribir: 1/3 Trimestre. Encabezado para el resultado.
5.2.Clic sobre la celda B11 Escribir la función: =SUMA(Ventas[[Trimestre 1]:[Trimestre 3]]).
Sumará todos los datos de la columna Trimestre 1 y Trimestre 3, equivale a los rangos B2:B6 y D2:D6
ESPACIO
Empleando el operador de espacio u operador de intersección para especificar la intersección de dos o más columnas.
6.1.Clic sobre la celda A12 Escribir: 3 Trimestre. Encabezado para el resultado.
6.2.Clic sobre la celda B12 Escribir la función: =SUMA(Ventas[[Trimestre 1]:[Trimestre 3]] Ventas[[Trimestre 3]:[Trimestre 4]]).
Sumará todos los datos de la columna Trimestre 3, equivale al rango D2:D6 (intersección de Trimestre 1 / Trimestre 3 y Trimestre 3 / Trimestre 4.
ESPECIFICADORES DE ELEMENTOS ESPECIALES
Podemos utilizar elementos especiales que hacen referencia a las partes de una tabla, permitiendo de forma fácil referenciarlas en fórmulas.
TODA LA TABLA
Toda la tabla (encabezados, datos y totales incluidos).
7.1. Clic sobre la celda A15 Escribir: Todo. Encabezado para el resultado.
7.2. Clic sobre la celda B15 Escribir la función: =SUMA(Ventas[#Todo]).
SOLO LOS DATOS
8.1. Clic sobre la celda A16 Escribir: Venta más alta. Encabezado para el resultado.
8.2. Clic sobre la celda B16 Escribir la función: =MAX(Ventas[#Datos]).
FILA DE ENCABEZADO
Toda la tabla (encabezados, datos y totales incluidos).
9.1. Clic sobre la celda A17 Escribir: Nº Encabezados. Encabezado para el resultado.
9.2. Clic sobre la celda B17 Escribir la función: =CONTARA(Ventas[#Encabezados])
FILA TOTAL
Toda la tabla (encabezados, datos y totales incluidos).
10.1. Clic sobre la celda A18 Escribir: Total. Encabezado para el resultado.
10.2. Clic sobre la celda B18 Escribir la función: =SUMA(Ventas[#Totales]). Mostrará el error #¡REF! al no estar activada la fila de totales.
10.3. Ir a: Cinta de opciones Ficha Diseño (Herramientas de tabla) Grupo: Opciones de estilo de tabla Clic sobre Fila de totales. El valor de B18 se actualiza al total de los datos.
FILA ACTUAL
Sólo la parte de las columnas de la fila actual. #Esta fila no se puede combinar con ningún especificador de elemento especial. Permite forzar una intersección implícita de la referencia o para invalidar ese comportamiento y hacer referencia a valores individuales de una columna.
11.1. Clic sobre la celda L2 Escribir: Total Cliente 2. Encabezado para el resultado.
11.2. Clic sobre la celda M2Escribir la función: =SUMA(Ventas[@]). Mostrará la suma de los datos de la fila 2.
REFERENCIAS ESTRUCTURADAS DE COLUMNAS CALCULADAS
Cuando crea una columna calculada, normalmente utilizamos una referencia estructurada para crear la fórmula. Esta referencia estructurada puede tener un nombre no completo o completo.
Si utilizamos referencias estructuradas en una tabla, como cuando creamos una columna calculada, podemos utilizar una referencia estructurada no calificada, pero si utilizamos esta referencia fuera de la tabla, necesitamos utilizar una referencia estructurada con un nombre completo válido.
NO CALIFICADA
12.1. Clic sobre la celda F1 Escribir: 1 Trim Descuento 5%. Encabezado para la columna.
12.2. Clic sobre la celda F2Escribir la función: =[Trimestre 1]*5/100. La referencia no hace mención a la tabla ventas.
NOMBRE COMPLETO
13.1. Clic sobre la celda A19 Escribir: 1 Trim Descuento 5%. Encabezado para la columna.
13.2. Clic sobre la celda B19Escribir la función: =SUMA(Ventas[Trimestre 1])*5/100. La referencia hace mención a la tabla ventas.
AUTOCOMPLETAR
La opción de autocompletar en las tablas, permite autocompletar en una fórmula los nombres de tablas, columnas y demás estructuras.
14.1. Clic sobre la celda A20 Escribir: 1 Semestre. Encabezado para el resultado.
14.2. Clic sobre la celda B20Escribir: =SUMA(V. Mostrará ventana desplegable de autocompletar, con las entradas que comienzan por V (incluyendo fórmulas, rangos y nombres.
14.3. Ir al: Desplegable Clic sobre Ventas. Insertará el nombre de la tabla.
14.4. Escribir [ (apertura de corchete). Mostrará ventana desplegable de autocompletar, con los nombres de columnas y estructuras especiales.
14.5. Ir al: Desplegable Clic sobre Trimestre 1. Insertará el nombre de la columna.
14.6. Escribir ]:Ven (cierre de corchete, dos puntos Ven). Mostrará ventana desplegable de autocompletar.
14.7. Ir al: Desplegable Clic sobre Ventas. Insertará el nombre de la tabla.
14.8. Escribir [Trim (apertura de corchete, trim). Mostrará ventana desplegable de autocompletar.
14.9. Ir al: Desplegable Clic sobre Trimestre 3. Insertará el nombre de la columna.
14.10. Escribir ]) (cierre de corchete y cierre de paréntesis). Aplica el valor de la fórmula.
REGLAS DE SINTAXIS
Lista de reglas de sintaxis para crear y editar referencias estructuradas.
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.
=SUMA(Ventas[[Trimestre 1]:[Trimestre 2]]). Muestra la suma de los datos de Trimestre 1 y Trimestre 2 en la tabla Ventas.
ENCABEZADOS DE COLUMNAS CON CADENA DE TEXTO
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 2011 o 1/1/2011, 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.
=Ventas[[Trimestre 1]:[Trimestre 2]]. Encabezados con espacio, Trimestre 1 y Trimestre 2.
=Ventas[[2010]:[2011]]. Encabezados con años 2010 y 2011.
=Ventas[[01/01/2011]:[01/02/2011]]. Encabezados con fechas 01/01/2011 y 01/02/2011.
CARACTERES ESPECIALES EN ENCABEZADOS DE COLUMNA
Si un encabezado de columna contiene uno de los siguientes caracteres especiales, se debe incluir todo el encabezado entre corchetes. Por lo 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 (/).
=SUMA(Ventas[Trimestre$4])
=SUMA(Ventas[Trimestre"4"])
=SUMA(Ventas[Trimestre 4])
La única excepción a esta regla es cuando sólo se utiliza el carácter especial de espacio.
CARACTERES ESPECIALES EN ENCABEZADOS CON CARÁCTER DE ESCAPE
Los siguientes caracteres 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 (').
=Ventas['#c]
CARÁCTER DE ESPACIO
Los caracteres de espacio se pueden utilizar para mejorar la legibilidad de la forma siguiente:
=Ventas[[Trimestre 1]:[Trimestre 2]]
=Ventas[[#Encabezados];[#Datos];[Trimestre1]]