¿Alguna vez te has encontrado perdido entre datos desorganizados en Excel? La herramienta más popular para la gestión de hojas de cálculo ofrece un sinfín de posibilidades, pero muchas veces la clave para aprovechar al máximo su potencial radica en el uso efectivo de referencias estructuradas. En este artículo, exploraremos cómo las referencias estructuradas en tablas de Excel pueden transformarse en tu mejor aliado para optimizar el manejo de información. Descubre técnicas y consejos prácticos que te permitirán trabajar de manera más eficiente y organizada, facilitando el análisis de datos y mejorando la presentación de tus informes. ¡Prepárate para llevar tus habilidades en Excel al siguiente nivel!
Las referencias estructuradas son una herramienta invaluable para organizar y analizar datos en hojas de cálculo de Excel. Cuando se trata de manejar grandes cantidades de información, una estructura adecuada se vuelve esencial para simplificar el proceso y obtener resultados precisos. En este artículo, exploraremos cómo utilizar tablas de Excel para crear referencias estructuradas, aprovechando al máximo las funciones y características que ofrece este programa. Descubre cómo puedes optimizar tus análisis y mejorar tu flujo de trabajo con esta práctica técnica ¡Sigue leyendo!
Este tutorial explica los conceptos básicos de las referencias estructuradas de Excel y comparte algunos trucos para usarlas en fórmulas de la vida real.
Una de las características más útiles de las tablas de Excel son las referencias estructuradas. Cuando acaba de tropezar con una sintaxis especial para hacer referencia a tablas, puede parecer aburrido y confuso, pero después de experimentar un poco seguramente verá lo útil y genial que es esta característica.
referencia estructurada de excel
A referencia estructuradao referencia de tablaes una forma especial de hacer referencia a tablas y sus partes que utiliza una combinación de nombres de tablas y columnas en lugar de direcciones de celda.
Esta sintaxis especial es necesaria porque las tablas de Excel (frente a los rangos) son muy potentes y resistentes, y las referencias de celdas normales no se pueden ajustar dinámicamente a medida que se agregan o eliminan datos de una tabla.
Por ejemplo, para sumar los valores en las celdas B2:B5, usa la función SUMA con una referencia de rango habitual:
=SUM(B2:B5)
Para sumar los números en la columna «Ventas» de la Tabla 1, utiliza una referencia estructurada:
=SUM(Table1[Sales])
>Características clave de las referencias estructuradas
En comparación con las referencias de celda estándar, las referencias de tabla tienen varias funciones avanzadas.
Fácilmente creado
Para agregar referencias estructuradas a su fórmula, simplemente seleccione las celdas de la tabla a las que desea hacer referencia. No se requiere conocimiento de una sintaxis especial.
Resistente y actualizado automáticamente
Cuando cambia el nombre de una columna, las referencias se actualizan automáticamente con el nuevo nombre y la fórmula no se rompe. Además, a medida que agrega nuevas filas a la tabla, se incluyen inmediatamente en las referencias existentes y las fórmulas calculan el conjunto completo de datos.
Entonces, cualquiera que sea la manipulación que haga con sus tablas de Excel, no tiene que preocuparse por actualizar las referencias estructuradas.
Se puede utilizar dentro y fuera de una mesa.
Las referencias estructuradas se pueden utilizar en fórmulas tanto dentro como fuera de una tabla de Excel, lo que facilita la localización de tablas en libros grandes.
Autocompletar fórmula (columnas calculadas)
Para realizar el mismo cálculo en cada fila de la tabla, basta con ingresar una fórmula en una sola celda. Todas las demás celdas de esa columna se rellenan automáticamente.
Cómo crear una referencia estructurada en Excel
Hacer una referencia estructurada en Excel es muy fácil e intuitivo.
Si está trabajando con un rango, primero conviértalo en una tabla de Excel. Para ello seleccione todos los datos y presione Ctrl+T. Para obtener más información, consulte Cómo crear una tabla en Excel.
Para crear una referencia estructurada, esto es lo que debe hacer:
Como ejemplo, sumemos las cifras de ventas de 3 meses en cada fila de nuestra tabla de muestra, denominada Ventas. Para esto, escribimos =SUMA( en E2, seleccionamos B2:D2, escribimos el paréntesis de cierre y presionamos Enter:
>Como resultado, toda la columna E se completa automáticamente con esta fórmula:
=SUM(Sales[@[Jan]:[Mar]])
Aunque la fórmula es la misma, los datos se calculan en cada fila individualmente. Para comprender la mecánica interna, eche un vistazo a la sintaxis de referencia de tabla.
Si está ingresando una fórmula fuera de la mesay esa fórmula requiere solo un rango de celdas, una forma más rápida de hacer una referencia estructurada es esta:
Por ejemplo, para encontrar el número más grande en nuestra tabla de muestra, comenzamos a escribir la fórmula MAX, después del paréntesis de apertura escribe «s», seleccionamos el Ventas tabla en la lista y presione Tab o haga doble clic en el nombre.
Como resultado, tenemos esta fórmula:
=MAX(Sales)
>Sintaxis de referencia estructurada
Como ya se mencionó, no necesita conocer la sintaxis de las referencias estructuradas para incluirlas en sus fórmulas; sin embargo, le ayudará a comprender qué hace realmente cada fórmula.
Normalmente, una referencia estructurada está representada por una cadena que comienza con el nombre de una tabla y termina con un especificador de columna.
Como ejemplo, analicemos la siguiente fórmula que suma los totales de Sur y Norte columnas de la tabla denominada Regiones:
>La referencia incluye tres componentes:
Para ver qué celdas se calculan realmente, seleccione la celda de fórmula y haga clic en cualquier lugar de la barra de fórmulas. Excel resaltará las celdas de la tabla a las que se hace referencia:
>Nombre de la tabla
El nombre de la tabla hace referencia sólo a la datos de la tabla, sin fila de encabezado ni filas totales. Puede ser un nombre de tabla predeterminado como Tabla 1 o un nombre personalizado como Regiones. Para darle un nombre personalizado a tu mesa, sigue estos pasos.
Si su fórmula se encuentra dentro de la tabla a la que hace referencia, el nombre de la tabla generalmente se omite porque está implícito.
Especificador de columna
El especificador de columna hace referencia a los datos de la columna correspondiente, sin la fila de encabezado ni la fila total. Un especificador de columna está representado por el nombre de la columna entre corchetes, por ejemplo [South].
Para hacer referencia a más de una columna contigua, utilice el operador de rango como [[South]:[East]].
especificador de artículo
Para hacer referencia a partes específicas de una tabla, puede utilizar cualquiera de los siguientes especificadores.
especificador de artículoSe refiere a[#All]La tabla completa, incluidos los datos de la tabla, los encabezados de las columnas y la fila total.[#Data]Las filas de datos.[#Headers]La fila del encabezado (encabezados de columna).[#Totals]La fila total. Si no hay una fila total, devuelve nulo.[@Column_Name]La fila actual, es decir, la misma fila que la fórmula.
Tenga en cuenta que el signo de almohadilla (#) se utiliza con todos los especificadores de artículos, excepto la fila actual. Para hacer referencia a las celdas en la misma fila donde ingresa la fórmula, Excel usa el carácter @ seguido del nombre de la columna.
Por ejemplo, para sumar números en el Sur y Oeste columnas de la fila actual, usarías esta fórmula:
=SUM(Regions[@South], Regions[@West])
Si los nombres de las columnas contienen espacios, signos de puntuación o caracteres especiales, aparecerá un conjunto adicional de corchetes alrededor del nombre de la columna:
=SUM(Regions[@[South sales]], Regions[@[West sales]])
Operadores de referencia estructurados
Los siguientes operadores le permiten combinar diferentes especificadores y agregar aún más flexibilidad a sus referencias estructuradas.
Operador de rango (dos puntos)
Al igual que con las referencias de rango normales, se utilizan dos puntos (:) para hacer referencia a dos o más columnas adyacentes en una tabla.
Por ejemplo, la siguiente fórmula suma los números en todas las columnas entre Sur y Este.
=SUM(Regions[[South]:[East]])
Operador de unión (coma)
Para hacer referencia a columnas no adyacentes, separe los especificadores de columna con comas.
Por ejemplo, así es como puedes sumar las filas de datos en el Sur y Oeste columnas.
=SUM(Regions[South], Regions[West])
Operador de intersección (espacio)
Se utiliza para referirse a una celda en la intersección de una fila y columna específicas.
Por ejemplo, para devolver un valor en la intersección de la Total fila y Oeste columna, utilice esta referencia:
=Regions[#Totals] Regions[[#All],[West]]
Tenga en cuenta que el [#All] El especificador es obligatorio en este caso porque el especificador de columna no incluye la fila total. Sin él, la fórmula devolvería #NULL!.
Reglas de sintaxis de referencia de tabla
Para editar o hacer referencias estructuradas manualmente, siga estas pautas:
1. Incluya los especificadores entre paréntesis
Todos los especificadores de columnas y elementos especiales deben estar encerrados en [square brackets].
Un especificador que contiene otros especificadores debe estar entre corchetes exteriores. Por ejemplo, regiones[[South]:[East]].
2. Separe los especificadores internos con comas.
Si un especificador contiene dos o más especificadores internos, esos especificadores internos deben separarse con comas.
Por ejemplo, para devolver el encabezado del Sur columna, escribe una coma entre [#Headers] y [South] y encierre toda esta construcción en un juego adicional de corchetes:
=Regions[[#Headers],[South]]
3. No utilice comillas alrededor de los encabezados de las columnas.
En las referencias de tablas, los encabezados de las columnas no requieren comillas, ya sean texto, números o fechas.
4. Utilice comillas simples para algunos caracteres especiales en los encabezados de las columnas.
En las referencias estructuradas, algunos caracteres como los corchetes izquierdo y derecho, el signo de almohadilla (#) y las comillas simples (‘) tienen un significado especial. Si alguno de los caracteres anteriores se incluye en el encabezado de una columna, se debe utilizar una comilla simple antes de ese carácter en el especificador de columna.
Por ejemplo, para el encabezado de columna «Artículo #», el especificador es [Item ‘#].
5. Utilice espacios para hacer que las referencias estructuradas sean más legibles.
Para mejorar la legibilidad de las referencias de sus tablas, puede insertar espacios entre especificadores. Normalmente, se considera una buena práctica utilizar espacios después de las comas. Por ejemplo:
=AVERAGE(Regions[South], Regions[West], Regions[North])
Referencias de tablas de Excel: ejemplos de fórmulas
Para comprender mejor las referencias estructuradas en Excel, repasemos algunos ejemplos de fórmulas más. Intentaremos que sean simples, significativos y útiles.
Encuentre el número de filas y columnas en una tabla de Excel
Para obtener el recuento total de columnas y filas, utilice las funciones COLUMNAS y FILAS, que solo requieren el nombre de la tabla:
COLUMNAS(mesa)
FILAS(mesa)
Por ejemplo, para encontrar el número de columnas y filas de datos en la tabla nombrada Ventasutilice estas fórmulas:
=COLUMNS(Sales)
=ROWS(Sales)
para incluir el encabezamiento y filas totales en el conteo, use el [#ALL] especificador:
=ROWS(Sales[#All])
La siguiente captura de pantalla muestra todas las fórmulas en acción:
>Contar espacios en blanco y no espacios en blanco en una columna
Al contar algo en una columna específica, asegúrese de generar el resultado fuera de la tabla; de lo contrario, podría terminar con referencias circulares y resultados incorrectos.
Para contar espacios en blanco en una columna, utilice la función CONTAR EN BLANCO. Para contar celdas que no están en blanco en una columna, utilice la función CONTAR.
Por ejemplo, para saber cuántas celdas hay en el Ene las columnas están vacías y cuántas contienen datos, utilice estas fórmulas:
Espacios en blanco:
=COUNTBLANK(Sales[Jan])
No espacios en blanco:
=COUNTA(Sales[Jan])
>Para contar celdas que no están en blanco en filas visibles en una tabla filtrada, use la función SUBTOTAL con num_función establecido en 103:
=SUBTOTAL(103,Sales[Jan])
Suma en una tabla de Excel
La forma más rápida de sumar números en una tabla de Excel es habilitar la opción Fila total. Para hacer esto, haga clic derecho en cualquier celda dentro de la tabla, apunte a Mesay haga clic Fila de totales. La fila del total aparecerá al final de la tabla inmediatamente.
A veces, Excel puede asumir que desea totalizar solo la última columna y dejar otras celdas en la fila Total en blanco. Para solucionar este problema, seleccione una celda vacía en la fila Total, haga clic en la flecha que aparece al lado de la celda y luego seleccione la función SUMA en la lista:
>Esto insertará una fórmula SUBTOTAL que suma valores sólo en filas visiblesignorando las filas filtradas:
=SUBTOTAL(109,[Jan])
Tenga en cuenta que esta fórmula sólo funciona en el Fila total. Si intenta insertarlo manualmente en una fila de datos, esto creará una referencia circular y devolverá 0 como resultado. Una fórmula SUM con una referencia estructurada tampoco funcionará por el mismo motivo:
>Entonces, si quieres los totales dentro de la mesadebe habilitar la fila Total o utilizar una referencia de rango normal como:
=SUM(B2:B5)
fuera de la mesala fórmula SUM con una referencia estructurada funciona bien:
=SUM(Sales[Jan])
Tenga en cuenta que, a diferencia de SUBTOTAL, la función SUMA suma valores en todas las filas, visibles y ocultas.
Referencias estructuradas relativas y absolutas en Excel.
De forma predeterminada, las referencias estructuradas de Excel se comportan de la siguiente manera:
- Varias columnas las referencias son absoluto y no cambian cuando se copian las fórmulas.
- Una sola columna las referencias son relativo y cambia cuando se arrastra a través de columnas. Cuando se copian/pegan mediante el comando correspondiente o los atajos (Ctrl+C y Ctrl+V), no cambian.
En situaciones en las que necesita una combinación de referencias de tablas relativas y absolutas, no hay forma de copiar la fórmula y mantener las referencias de las tablas correctas. Arrastrar la fórmula cambiará las referencias a columnas individuales, y copiar/pegar accesos directos hará que todas las referencias sean estáticas. ¡Pero hay un par de trucos sencillos que puedes utilizar!
Referencia estructurada absoluta a una sola columna.
Para convertir una referencia de una sola columna en absoluta, repita el nombre de la columna para convertirla formalmente en una referencia de rango.
Referencia de columna relativa (predeterminada)
table[column]
Referencia de columna absoluta
table[[column]:[column]]
Para hacer una referencia absoluta para el fila actualpreceda el identificador de columna con el símbolo @:
table[@[column]:[column]]
Para ver cómo funcionan en la práctica las referencias de tablas relativas y absolutas, considere el siguiente ejemplo.
Supongamos que desea sumar las cifras de ventas de un producto específico durante 3 meses. Para esto, ingresamos el nombre del producto objetivo en alguna celda (F2 en nuestro caso) y usamos la función SUMAR.SI para obtener el total de Ene ventas:
=SUMIF(Sales[Item], $F$2, Sales[Jan])
El problema es que cuando arrastramos la fórmula hacia la derecha para calcular los totales de los otros dos meses, el [Item] la referencia cambia y la fórmula se rompe:
>Para solucionar este problema, haga el [Item] referencia absoluta, pero mantener [Jan] relativo:
=SUMIF(Sales[[Item]:[Item]], $F$2, Sales[Jan])
Ahora puedes arrastrar la fórmula modificada a otras columnas y funciona perfectamente:
>Referencia estructurada relativa a múltiples columnas.
En las tablas de Excel, las referencias estructuradas a varias columnas son absolutas por naturaleza y permanecen sin cambios cuando se copian a otras celdas.
Para mí, este comportamiento es muy razonable. Pero si necesita hacer relativa una referencia de rango estructurado, preceda cada especificador de columna con el nombre de la tabla y elimine los corchetes exteriores como se muestra a continuación.
Referencia de rango absoluto (predeterminado)
table[[column1]:[column2]]
Referencia de rango relativo
table[column1]:table[column2]
Para referirse a la fila actual dentro de la tablautilice el símbolo @:
[@column1]:[@column2]
Por ejemplo, la siguiente fórmula con una referencia estructurada absoluta suma los números en la fila actual del Ene y Feb columnas. Cuando se copia a otra columna, aún sumará Ene y Feb.
=SUM(Sales[@[Jan]:[Feb]])
En caso de que desee que la referencia cambie según una posición relativa de la columna donde se copia la fórmula, hágalo relativo:
=SUM(Sales[@Jan]:Sales[@Feb])
Observe la transformación de la fórmula en la columna F (el nombre de la tabla se omite porque la fórmula está dentro de la tabla):
>Así es como se hacen referencias a tablas en Excel. Para ver más de cerca los ejemplos discutidos en este tutorial, no dude en descargar nuestro libro de trabajo de muestra para Referencia estructurada de Excel. Te agradezco por leer y espero verte en nuestro blog la próxima semana.
Usted también podría estar interesado en
Referencias estructuradas en tablas de Excel
Las referencias estructuradas en las tablas de Excel son una herramienta poderosa que permite organizar y analizar grandes conjuntos de datos de manera eficiente. Se trata de una función avanzada que puede agilizar tus tareas y facilitar el procesamiento de la información dentro de tus hojas de cálculo.
En este artículo, responderemos a las preguntas más frecuentes sobre las referencias estructuradas en tablas de Excel.
1. ¿Qué son las referencias estructuradas?
Las referencias estructuradas son una forma de referirse a los datos de una tabla de Excel utilizando etiquetas, en lugar de con referencias de celda tradicionales. Esto facilita la identificación y utilización de conjuntos de datos específicos dentro de una tabla.
2. ¿Cuáles son los beneficios de utilizar referencias estructuradas?
Utilizar referencias estructuradas tiene varias ventajas. Algunas de ellas son:
3. ¿Cómo utilizar las referencias estructuradas en Excel?
Para utilizar las referencias estructuradas en Excel, debes convertir tu rango de datos en una tabla. A continuación, puedes utilizar las etiquetas de columna para referirte a los datos de la tabla en lugar de utilizar referencias de celda tradicionales.
Por ejemplo, si tienes una tabla de ventas con columnas etiquetadas como «Producto», «Cantidad» y «Precio», puedes utilizar estas etiquetas en tus fórmulas en lugar de referencias de celda como «A1», «B1» y «C1». Esto facilita la comprensión de la fórmula y evita posibles errores.
4. ¿Cuáles son las etiquetas disponibles en las referencias estructuradas?
Al utilizar referencias estructuradas en Excel, puedes utilizar una variedad de etiquetas para referirte a los distintos componentes de una tabla. Algunas de las etiquetas más comunes son:
- [Nombre de la tabla]: Utilizada para referirse a la tabla en general.
- [Columna]: Utilizada para referirse a una columna específica dentro de la tabla.
- [@Columna]: Utilizada para referirse a la columna actual dentro de la fórmula.
- [#Totales]: Utilizada para referirse a la fila de totales en una tabla.
Estas son solo algunas de las etiquetas disponibles, y puedes explorar más opciones en la documentación de Microsoft Excel.
Las referencias estructuradas en las tablas de Excel son una función valiosa que puede mejorar la eficiencia y el análisis de tus hojas de cálculo. Aprovecha esta herramienta y descubre cómo puede facilitar tu trabajo con grandes conjuntos de datos. ¡No dudes en experimentar y explorar nuevas formas de utilizar las referencias estructuradas!
[automatic_youtube_gallery type=»search» search=»Referencias estructuradas en tablas de Excel.» limit=»1″]
Tenen que son más comprensibles para los usuarios, especialmente cuando se trabaja con tablas grandes.
- Claridad: Al usar nombres de columnas en lugar de referencias de celda (como A1, B2, etc.), es más fácil entender la intención de la fórmula.
- Flexibilidad: Las referencias estructuradas se ajustan automáticamente si se agregan o eliminan filas o columnas, lo que hace que las fórmulas sean más dinámicas.
- Errores Reducidos: Al usar nombres de columnas, hay menos probabilidades de referirse a la celda incorrecta.
- ¿Cómo se crean las tablas en Excel?
Para crear una tabla en Excel, selecciona el rango de datos que quieres incluir y ve a la pestaña «Insertar». Luego, haz clic en «Tabla». Excel automáticamente reconocera los encabezados de columna y convertirá tu rango en una tabla estructurada.
- ¿Qué funciones son útiles con las referencias estructuradas?
Algunas de las funciones más comunes que puedes usar con referencias estructuradas incluyen:
– SUM: Sumar valores en una columna específica.
– AVERAGE: Calcular el promedio de los valores.
– COUNT, COUNTA, COUNTBLANK: Contar celdas según diferentes criterios.
– SUMIF y SUMIFS: Sumar celdas que cumplen con criterios específicos.
- ¿Se pueden utilizar referencias estructuradas en fórmulas de matrices?
Sí, las referencias estructuradas se pueden integrar en fórmulas de matrices, lo que te permite realizar cálculos en rangos de datos más amplios sin necesidad de ingresar referencias de celda individuales.
- ¿Qué sucede si reservo caracteres especiales en los nombres de las columnas?
Si un nombre de columna incluye caracteres especiales (como signos de almohadilla, corchetes, comillas simples, etc.), deberás encapsular esos caracteres con comillas simples para que Excel pueda interpretar correctamente la referencia estructurada.
- ¿Las referencias estructuradas funcionan con tablas filtradas?
Sí, puedes usar referencias estructuradas con tablas filtradas. Sin embargo, ten en cuenta que algunas funciones como SUBTOTAL están diseñadas para funcionar específicamente con datos visibles en tablas filtradas, mientras que SUM sumará todos los valores, sin importar si están visibles o no.
- ¿Es posible usar nombres de tablas en fórmulas fuera de la tabla?
Sí, puedes usar referencias estructuradas que incluyan el nombre de la tabla desde cualquier lugar en tu hoja de cálculo, no solo dentro de la tabla misma.
Con estas herramientas y funcionalidades, las referencias estructuradas en Excel pueden hacer que trabajar con datos sea mucho más fácil, claro y eficiente. Sin embargo, como cualquier herramienta, requiere cierta práctica para dominarla completamente y aprovechar al máximo sus beneficios.
Swelahe: ¡Totalmente de acuerdo, Iban! Yo también solía tener mil problemas con las referencias en Excel, pero desde que descubrí las tablas estructuradas, me siento como un genio. Antes, las columnas y filas me volvían loco, pero ahora todo fluye mucho mejor. Además, cuando tengo que hacer reportes, es un paseo. ¡Gracias por compartir este tipo de contenido tan útil!
Iban: ¡Me encantó el artículo! La verdad es que siempre he fumado mucho con las referencias en Excel, pero desde que empecé a usar tablas estructuradas, todo ha cambiado para mejor. Recuerdo que antes me volvía loco cuando tenía que actualizar mis datos y las fórmulas se rompían. ¡Menuda pesadilla! Pero ahora, gracias a esas tablas, es mucho más sencillo y me ahorro un montón de tiempo. ¡Sigan compartiendo tips así!