Cuatro formas de utilizar referencias estructuradas en Microsoft Excel

Trabajar en Excel normalmente gira en torno a encontrar conexiones entre varios puntos de datos. Sin embargo, al insertar fórmulas complicadas, el uso repetido de referencias de celda explícitas relativas y absolutas (como “B7” o sus variaciones) solo puede llevarlo hasta cierto punto antes de que la barra de fórmulas se convierta en un desastre ilegible.

Las referencias estructuradas en Excel le permiten agilizar ese trabajo asignando nombres a las tablas y sus encabezados. Luego, esos nombres se pueden usar como referencias de celda implícitas para que Excel pueda recuperar automáticamente los datos estructurados y calcularlos.

Estas son algunas de las formas más comunes de utilizar referencias estructuradas en Excel.

1. Cálculo de tablas interiores

Dado que las referencias estructuradas sólo funcionan en tablas, la mejor manera de utilizarlas es dentro de esas mismas tablas.

Por ejemplo, crearemos una tabla simple de B2 a F8 con datos de ventas de una tienda. Tenga en cuenta que llamamos a la tabla "Ventas" (consulte "Nombre de la tabla" en la parte superior izquierda).

Calculemos el total de cada venta:

Paso 1: Haga clic en F2 (pero no en el icono desplegable). Vaya a "Inicio", luego a "Insertar" y seleccione "Insertar columnas de tabla a la derecha". Esto agregará automáticamente una nueva columna a la tabla.

Paso 2: Nombra el encabezado de la columna G "Total".

Paso 3: En G3, inserte =[@PricePerUnit]*[@Quantity] y presiona Enter. Formatee la salida de la celda según sea necesario.

El "[@PricePerUnit]" y "[@Quantity]”son referencias a los campos correspondientes en esas columnas. El argumento "@" antes de los nombres de las columnas significa que cada celda de resultado utilizará las referencias de la misma fila de la tabla.

Para traducir, la fórmula =[@PricePerUnit]*[@Quantity] en G3 es esencialmente lo mismo que escribir =$C3*$D3.

2. Obtener un rango fuera de la tabla

Cuando desee utilizar una referencia estructurada en una celda fuera de la tabla, deberá anteponer la referencia a TableName. En nuestro ejemplo anterior, usando “Ventas[Total]”obtendrá todo el rango bajo el encabezado “Total” de la tabla “Ventas”. Esto significa que obtendrá múltiples valores en una matriz que podrá manipular.

Así es como se ve esto dentro de Excel en la celda I3, siempre que deje suficiente espacio para que el rango se extienda.

3. Sumar y Sumar Parcialmente una Columna

Para sumar rápidamente una columna completa, puede usar la marca de verificación "Fila total" en las opciones de "Diseño de tabla" (en "Opciones de estilo de tabla"). A continuación se muestra un ejemplo de cómo obtener los totales de las columnas "Cantidad" y "Total".

Si bien la fila "Total" por sí sola no se puede mover y se colocará al final de la tabla (lo que permite inserciones), puedes duplicar su resultado en otro lugar:

• Para obtener la suma de todas las filas de la columna "Total", utilice =SUM(Sales[Total]).
• Si desea obtener solo la suma de las columnas visibles, como después de filtrar la tabla, use =SUBTOTAL(109,Sales[Total]). Esta fórmula es lo que realmente hace la opción “Fila total” en Formato de tabla en su fila.

También puedes obtener una suma parcial basada en una variable específica que se encuentra dentro de la tabla sin formatearla. Por ejemplo:

• Para obtener la suma de todas las ventas de Mike, puedes usar =SUMIF(Sales[Seller],”Mike”,Sales[Total]). En la fórmula, "Mike" es una cadena que se ingresó manualmente.
• Para obtener la suma de todos los productos con el ID "41230", utilice lo siguiente =SUMIF(Sales[ProductID],41230,Sales[Total]). Tenga en cuenta que, dado que la columna ProductID tiene un formato "General", puede ingresar el número directamente.

4. Validación de datos de una tabla vía INDIRECTA

Supongamos que tiene la tabla Ventas utilizada anteriormente. Puede crear opciones de validación de datos personalizadas para facilitar la búsqueda en la tabla. Hagamos una tabla más pequeña que le permitirá seleccionar entre ID de producto, fechas o vendedores, y luego elija cualquier artículo de esos subconjuntos para mostrar el subtotal.

Paso 1: En la celda B13, cree Validación de datos (pestaña Datos > Herramientas de datos > Validación de datos).

Paso 2: En la ventana emergente, elija "Lista" de las opciones "Permitir", luego inserte los valores de las columnas manualmente en el cuadro "Fuente", separados por comas. En este caso, insertamos "ProductID, Vendedor, Fecha".

Paso 3: En la celda C13, cree otra Validación de datos. Nuevamente, elija "Lista". Para "Fuente", inserte la siguiente fórmula: =INDIRECT("Sales["&B13&"]").

Paso 4: En la celda D13, use la siguiente fórmula: =SUMIF(INDIRECT("Sales["&B13&"]"),B14,Sales[Total]).

Ahora puede elegir las opciones de las dos listas de validación de datos y el subtotal se mostrará en D13.