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.
Sobre nuestra hoja con datos de las ventas a los clientes deseamos adaptarla para interpretar los resultados.
1.1. Descargar hoja de cálculo con los datos y descomprimir el archivo .zip, o también...
1.2. Ir a: Cinta de opcionesFicha: 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 |
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.
Podemos aplicar a la tabla el nombre descriptivo deseado.
3.1.Ir a: Cinta de opciones Ficha: Diseño (Herramientas de tabla)
Grupo: Propiedades
Ir al cuadro: Nombre de la tabla
Escribir: Ventas. O el nombre deseado.
Podemos utilizar los operadores de referencia para combinar especificadores de columna, especificando rangos de celdas.
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.
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
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.
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 (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]).
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]).
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])
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.
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.
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.
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.
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.
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.
Lista de reglas de sintaxis para crear y editar referencias estructuradas.
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.
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.
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.
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]
Los caracteres de espacio se pueden utilizar para mejorar la legibilidad de la forma siguiente:
=Ventas[[Trimestre 1]:[Trimestre 2]]
=Ventas[[#Encabezados];[#Datos];[Trimestre1]]