Referencias estructuradas en Excel

Transcripción del video

Las referencias estructuradas nos permiten acceder los datos de una tabla utilizando el nombre de sus columnas, en lugar de utilizar una dirección de rango, como normalmente lo haríamos en las referencias de Excel.

Esto quiere decir que, para una tabla, podemos utilizar tanto las referencias normales, como las referencias estructuradas y solo será cuestión de nuestra preferencia por cualquiera de estos dos métodos, aunque pronto notarás que la creación de fórmulas se agilizará al utilizar las referencias estructuradas.

Veamos un ejemplo sencillo de este tipo de referencias.

En esta hoja, tengo un listado de vendedores y el monto de ventas para cada uno de ellos.

Además, una columna que indica el porcentaje de comisión que le corresponde a cada uno.

Activaré una celda de los datos y pulsaré el atajo Ctrl + T para crear la tabla y haré clic en el botón Aceptar.

Observa que en la Cinta de opciones tenemos la pestaña Diseño y dentro del grupo Propiedades tenemos el nombre de la tabla.

En este momento nuestra tabla tiene el nombra Tabla1 y lo cambiaré por un nombre más amigable.

Ingresaré el nombre TablaVentas y pulsaré la tecla Entrar y con eso hemos cambiado el nombre de nuestra tabla.

Para calcular la comisión de cada vendedor, en la celda D1 ingresaré el título de columna “Comisión” y al pulsar Entrar, Excel aplicará automáticamente el formato a la columna.

La comisión es una operación muy sencilla que podemos obtener multiplicando el monto de la venta por el porcentaje.

Primero te mostraré el método tradicional para construir esta fórmula y para eso ingresaré el símbolo igual y en seguida la referencia a la celda B2 y lo multiplicaré por la celda C2.

Al pulsar la tecla Entrar obtendremos el cálculo para todas las filas.

Esta fórmula utiliza las referencias tal como las conocemos en Excel, pero voy a eliminar todos los valores de la columna para crear la fórmula utilizando referencias estructuradas.

De nuevo ingresaré el símbolo igual, y en seguida abriré el corchete y de inmediato Excel sabrá que estoy intentando ingresar una referencia estructurada y mostrará algunas opciones.

Puedes notar que la primera opción de la lista es el símbolo arroba y la descripción nos dice: “Elija solo esta fila de la columna especificada”.

Cuando hablamos de referencias estructuradas, la arroba nos ayudará a indicar a Excel que queremos utilizar una celda de la misma fila en la que nos encontramos.

Ingresaré una arroba y en seguida puedo elegir el nombre de alguna de las columnas de la tabla y para nuestro ejemplo necesito la columna Ventas así que haré doble clic en su nombre para elegirla y cerraré los corchetes.

Con esta referencia estamos accediendo al valor de la columna Ventas que se encuentra en la misma fila que nuestra fórmula y puedes ver que Excel ya ha colocado un borde de color diferente en la celda B2.

En seguida ingresaré un asterisco porque vamos a multiplicarlo por el valor de la columna porcentaje y de nuevo abriré el corchete y el símbolo arroba y elegiré la columna Porcentaje.

Finalmente cierro el corchete y puedes observar un borde de color diferente en la celda C2 que es la celda de la columna Porcentaje que se encuentra en la misma fila que nuestra fórmula.

Pulso la tecla Entrar y Excel calculará los valores para toda la columna.

Puedes revisar las fórmulas y verás que todas son iguales y esto se debe a que el símbolo arroba es el que le indica a Excel que debe utilizar las celdas de la misma fila tanto para la columna Ventas como para la columna Porcentaje.

Seleccionaré todas las celdas de la columna Comisión y aplicaré el formato Moneda para que podamos ver adecuadamente los decimales.

Recuerda que los nombres de las columnas de una tabla son únicos, y por lo tanto esta referencia siempre accederá el valor de la columna indicada.

Observa lo que sucede si cambio el nombre de la columna Ventas por VentasN.

Al pulsar Entrar y revisar las fórmulas de la columna Comisión, notarás que Excel ha cambiado automáticamente el nombre de la columna a VentasN para continuar la referencia a los mismos valores.

Hasta ahora hemos aprendido que, para hacer referencia a un valor de la misma fila utilizaremos la arroba seguida del nombre de la columna y todo eso encerrado en corchetes.

En este ejemplo ha sido suficiente el nombre de la columna porque la fórmula se encuentra dentro de la tabla, pero si queremos crear una fórmula fuera de la tabla será necesario agregar su nombre a la referencia estructurada.

En la columna F calcularemos de nuevo las comisiones utilizando referencias estructuradas.

En la celda F2 ingresaré el símbolo igual y en primer lugar ingresaré el nombre de la tabla que es TablaVentas y en seguida abriré el corchete y colocaré la arroba seguida de la columna VentasN y cierro el corchete.

Esta referencia le dice a Excel que debe ir a la tabla llamada TablaVentas y a la columna VentasN para obtener el valor que se encuentra en la misma fila que nuestra fórmula.

Si cierro los corchetes y pulso la tecla Entrar, obtendremos el valor 5,379 que es el mismo valor de la celda B2.

Para continuar con el cálculo de las comisiones, editaré la fórmula e ingresaré el asterisco para multiplicar este valor por el porcentaje de comisión y por lo tanto ingresaré de nuevo el nombre de la tabla, que es TablaVentas, y en seguida el corchete, la arroba y el nombre de la columna Porcentaje y finalmente cerramos los corchetes.

Nuestra fórmula, que se encuentra fuera de la tabla, puede acceder los datos con solo anteponer el nombre de la tabla en las referencias estructuradas.

Pulso la tecla Entrar y copio la fórmula hacia abajo y aplico el formato de Moneda y obtendremos los mismos valores de la columna Comisiones, pero haciendo el cálculo fuera de la tabla.