Cómo utilizar referencias estructuradas en Microsoft Excel para mejorar la eficiencia

El uso de referencias estructuradas en Microsoft Excel es una de las formas más poderosas de mejorar la precisión y eficacia de su trabajo con datos. Ya sea que trabaje en hojas de cálculo grandes o necesite acelerar el proceso de actualizaciones frecuentes, aprender a aplicar estas referencias puede ahorrarle mucho tiempo y esfuerzo. En este artículo, aprenderemos cómo utilizar eficazmente referencias estructuradas para lograr los mejores resultados y mejorar el formato de datos y ecuaciones en archivos de Excel.

Consejos rápidos

• Las referencias estructuradas en Excel solo funcionan en tablas formateadas de esta manera en el programa, no en rangos de datos.
• El uso de referencias estructuradas hace que las fórmulas sean más legibles y dinámicas.
• Cuando formatea una tabla en Excel, cambie su nombre por algo significativo. De lo contrario, Excel lo llamará Tabla[número], lo que puede causar confusión.
• Las referencias estructuradas funcionan dentro y fuera de las tablas, se pueden usar dentro de otras funciones y se actualizarán automáticamente si los encabezados reciben nuevos nombres.

Trabajar en Excel generalmente gira en torno a encontrar conexiones entre diferentes puntos de datos. Sin embargo, al insertar fórmulas complejas, el uso frecuente de referencias de celda absolutas y relativas explícitas (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 simplificar este trabajo asignando nombres a las tablas y sus encabezados. Luego, estos nombres se pueden usar como referencias de celda implícitas para que Excel pueda recuperar y calcular automáticamente datos estructurados.

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

1. Cálculo dentro de tablas.

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

Por ejemplo, crearemos una tabla simple 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 esquina superior derecha).

Calculemos el total de cada venta:

Paso 1: Haga clic en F2 (pero no en el icono del menú 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: Etiquete el encabezado de la columna G como "Total".

En el paso 3: En G3, ingrese =[@PricePerUnit]*[@Quantity] y presione Enter. Formatee la salida de la celda según sea necesario.

“[@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á referencias de la misma fila de la tabla.

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

2. Saque el alcance de la mesa

Cuando desee utilizar una referencia estructurada en una celda fuera de una tabla, deberá proporcionar la referencia con el nombre de la tabla. En nuestro ejemplo anterior, al usar "Ventas[Total]" se colocará todo el rango bajo el encabezado "Total" de la tabla "Ventas". Esto significa que tendrá múltiples valores en una matriz que podrá manipular.

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

3. Suma de columnas y suma parcial

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 totales para las columnas Cantidad y Total.

Si bien la fila Total no se puede mover por sí sola y se colocará al final de la tabla (con inserción permitida), puede duplicar su resultado en otro lugar:

Para obtener la suma de todas las filas de una columna Total, utilice =SUM(Ventas[Total]).
Si desea obtener la suma solo de las columnas visibles, como después de filtrar la tabla, use =SUBTOTAL(109,Ventas[Total]). Esta fórmula es lo que realmente hace la opción "Total de filas" en "Formato de tabla" para su fila.
También puede obtener una suma parcial basada en una variable específica contenida en la tabla sin formatearla. Por ejemplo:

Para obtener la suma de todas las ventas realizadas por Mike, puede utilizar =SUMIF(Ventas[Vendedor],"Mike",Ventas[Total]). En la fórmula, "Mike" es una cadena ingresada manualmente.
Para obtener la suma de todos los productos con ID "41230", use =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. Validar 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. Creemos una tabla más pequeña que le permita elegir entre ID de producto, fechas o vendedores y luego seleccionemos cualquier artículo individual de estos subgrupos 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 ingrese manualmente los valores de las columnas en el cuadro "Fuente", separados por comas. En este caso, ingresamos "ID de producto, vendedor, fecha".

En el paso 3: En la celda C13, cree otra verificación de datos. Nuevamente, elija "Lista". Para "Fuente", ingrese la siguiente fórmula: =INDIRECT("Ventas["&B13&"]").

Paso 4: En la celda D13, use la siguiente fórmula: =SUMAR.SI(INDIRECTO(“Ventas[“&B13&”]”),B14,Ventas[Total]).

Ahora puede elegir opciones de los dos menús de Validación de datos y el subtotal se mostrará en D13.