4 métodos eficaces para utilizar referencias estructuradas en Microsoft Excel

El uso de Excel suele centrarse en identificar relaciones entre diferentes conjuntos de datos. Sin embargo, al insertar fórmulas complejas, el uso excesivo de referencias de celdas explícitas, tanto relativas como absolutas (como “B7” y sus variantes) puede generar una barra de fórmulas desordenada y caótica.

El uso de referencias estructuradas en Excel puede mejorar este proceso al permitirle asignar nombres a las tablas y sus encabezados. Estos nombres designados sirven como referencias de celda implícitas, lo que permite que Excel recupere y calcule automáticamente los datos estructurados.

A continuación se presentan varios métodos efectivos para emplear referencias estructuradas en Excel.

1. Realizar cálculos dentro de tablas

Las referencias estructuradas funcionan exclusivamente con tablas, por lo que el método óptimo para utilizarlas es dentro de esos tipos de tablas.

Por ejemplo, cree una tabla sencilla que abarque desde B2 hasta F8 y que contenga información de ventas de una tienda. Asegúrese de etiquetar la tabla como “Ventas” (consulte el “Nombre de la tabla” en la esquina superior izquierda).

A continuación, calcularemos el total de cada venta individual:

Paso 1: Seleccione la celda F2 (asegurándose de que el ícono desplegable no esté seleccionado). Vaya a “Inicio”, luego a “Insertar” y elija “Insertar columnas de tabla a la derecha” para incluir automáticamente una nueva columna en la tabla.

Paso 2: Designe el encabezado de la nueva columna G como “Total”.

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

Las referencias “[@PricePerUnit]” y “[@Quantity]” corresponden a sus respectivos campos dentro de las columnas. El símbolo “@” que precede a los nombres de las columnas indica que cada celda de resultado obtendrá sus valores de la misma fila de la tabla.

Para aclarar, la fórmula =[@PricePerUnit]*[@Quantity]en G3 corresponde efectivamente a =$C3*$D3.

2. Recuperación de un rango más allá de la tabla

Cuando desee utilizar una referencia estructurada en una celda que se encuentra fuera de la tabla, debe iniciar la referencia con el nombre de la tabla. Por ejemplo, en nuestro ejemplo anterior, al utilizar “Ventas[Total]” se recuperará todo el rango de valores de la columna “Total” de la tabla “Ventas”. Esto proporciona una matriz de múltiples valores para una mayor manipulación.

Así es como aparece esto en Excel, específicamente en la celda I3, donde se deja suficiente espacio para que el rango se expanda hacia abajo.

3. Sumar y sumar parcialmente una columna

Para obtener una suma rápida de una columna completa, utilice la casilla de verificación “Total de filas” ubicada en la configuración de “Diseño de tabla” (dentro de “Opciones de estilo de tabla”). A continuación, se muestra cómo obtener los totales de las columnas “Cantidad” y “Total”.

Aunque la fila ‘Total’ no se puede reubicar y siempre se ubicará al final de la tabla (lo que permite entradas adicionales), puede duplicar su resultado en otro lugar:

Para calcular el total de cada entrada dentro de la columna “Total”, utilice =SUM(Sales[Total]).
Para obtener la suma de solo las entradas visibles, como las que aparecen después de filtrar la tabla, aplique =SUBTOTAL(109,Sales[Total]). Esta fórmula refleja lo que hace la opción “Total de filas” en Formato de tabla para su fila.

También puede calcular una suma parcial en función de un criterio específico que se encuentre dentro de la tabla sin necesidad de formatearla. Por ejemplo:

Para encontrar las ventas totales atribuidas a Mike, utilice =SUMIF(Sales[Seller]," Mike" ,Sales[Total]). Aquí, “Mike” es una cadena ingresada manualmente.
Para sumar todos los productos representados por el ID de producto “41230”, deberá utilizar =SUMIF(Sales[ProductID],41230,Sales[Total]). Tenga en cuenta que, dado que la columna ID de producto tiene el formato “General”, puede ingresar el número directamente.

4. Implementación de la validación de datos desde una tabla con INDIRECT

Suponiendo que está trabajando con la tabla de ventas mencionada anteriormente, puede establecer configuraciones de validación de datos personalizadas para facilitar la búsqueda en los datos. Creemos una tabla más pequeña que permita seleccionar ID de producto, fechas o vendedores, lo que le permitirá elegir un solo artículo de estos conjuntos para mostrar el subtotal.

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

Validación de datos de una tabla mediante INDIRECT 1

Paso 2: En la ventana emergente que aparece, seleccione “Lista” en el menú desplegable “Permitir” e ingrese los valores de la columna manualmente en el campo “Fuente”, separados por comas. Para este ejemplo, ingresamos “ProductID, Vendedor, Fecha”.

Validación de datos de una tabla mediante INDIRECT 2

Paso 3: En la celda C13, establezca otra instancia de Validación de datos. Nuevamente, seleccione “Lista”. En “Fuente”, incluya la fórmula: =INDIRECT("Sales["&B13&"]").