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) sólo puede llevarte hasta cierto punto antes de que la barra de fórmulas se convierta en un lío ilegible.
Las referencias estructuradas en Excel te permiten agilizar ese trabajo asignando nombres a las tablas y sus encabezados. Estos nombres pueden utilizarse como referencias implícitas a celdas para que Excel pueda obtener 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 dentro de tablas
Dado que las referencias estructuradas sólo funcionan en tablas, la mejor forma 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. Observe que hemos nombrado la tabla "Ventas" (vea el "Nombre de la tabla" en la parte superior izquierda).
Click here to preview your posts with PRO themes ››
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 añadirá automáticamente una nueva columna a la tabla.
Paso 2: Nombre la cabecera de la columna G "Total".
Paso 3: En G3, inserte =[@PricePerUnit]*[@Quantity] y pulsa Intro. Dé formato a la salida de la celda según sea necesario.
La función "[@PricePerUnit]" y "[@Quantity]" son referencias a los campos correspondientes de 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. Obtención de un rango fuera de la tabla
Cuando desee utilizar una referencia estructurada en una celda fuera de la tabla, deberá anteponer a la referencia NombreTabla. En nuestro ejemplo anterior, al utilizar "Ventas[Total]" obtendrá todo el rango bajo el encabezado "Total" de la tabla "Ventas". Esto significa que obtendrá múltiples valores en un array que podrá manipular.
Así es como se ve esto dentro de Excel en la celda I3, siempre y cuando dejes suficiente espacio para que el rango se derrame hacia abajo.
3. 3. Suma y suma parcial de una columna
Para sumar rápidamente una columna entera, puede utilizar la marca de verificación "Total Fila" en las opciones de "Diseño de Tabla" (en "Opciones de Estilo de Tabla"). A continuación se muestra un ejemplo para obtener los totales de las columnas "Cantidad" y "Total".
Aunque la fila "Total" por sí misma no se puede mover y se colocará al final de la tabla (permitiendo inserciones), puede 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 sólo la suma de las columnas visibles, por ejemplo después de filtrar la tabla, utilice
=SUBTOTAL(109,Sales[Total]). Esta fórmula es lo que la opción "Total Fila" de Formato de Tabla hace realmente en su fila.
Click here to preview your posts with PRO themes ››
También puede obtener una suma parcial basada en una variable específica encontrada dentro de la tabla sin formatearla. Por ejemplo:
- Para obtener la suma de todas las ventas de Mike, puedes utilizar
=SUMIF(Sales[Seller],”Mike”,Sales[Total]). En la fórmula, "Mike" es una cadena introducida 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 como la columna ProductID tiene un formato "General", puede introducir el número directamente.
4. Validación de datos de una tabla mediante INDIRECTO
Suponga que tiene la tabla Ventas utilizada anteriormente. Puede crear opciones personalizadas de Validación de Datos para facilitar la búsqueda en la tabla. Vamos a crear una tabla más pequeña que le permitirá seleccionar entre ProductIDs, Fechas o Vendedores, y luego elegir cualquier elemento individual 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" entre las opciones "Permitir" y, a continuación, inserte manualmente los valores de las columnas en la casilla "Fuente", separados por comas. En este caso, insertamos "ProductID, Seller, Date".
Paso 3: En la celda C13, cree otra Validación de Datos. De nuevo, elija "Lista". Para "Fuente", inserta la siguiente fórmula: =INDIRECT("Sales["&B13&"]").
Paso 4: En la celda D13, utiliza 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.
Deja una respuesta