Referencias estructuradas en Excel
Referencias estructuradas en Excel
Esta fórmula tiene los siguientes componentes de referencia estructurada: Las referencias estructuradas son referencias que se hacen a elementos que pertenecen a una tabla, es decir, que mantienen el formato de tabla y que contiene nombre, datos, entre otros.
¿Te resulta complicado el tema?
En este artículo te compartiremos pasos sencillos para que puedas desarrollar una referencia estructurada en Excel, sin complicaciones.
Te ayudaremos paso a paso…
Al crear una tabla Excel, Excel designa un nombre a la tabla y a cada encabezado de columna de la tabla. Cuando agrega fórmulas a una tabla de Excel, estos nombres pueden aparecer automáticamente conforme introduce la fórmula, y no tiene que especificar referencias de celda explícitas. A continuación te compartiré de manera gráfica la aplicación de esta referencia.
Ejemplo de referencia de celda explícita →
=SUMA(C2:C7)
Uso de nombre de tabla y columna →
=SUMA(SalarioDept[SalarioAnual])
Esta combinación de nombres de tabla y columna se denomina una referencia estructurada. Los nombres de referencias estructuradas se ajustan siempre que agregue o quite datos de la tabla.
Las referencias estructuradas también aparecen cuando crea una fórmula fuera de una tabla de Excel que hace referencia a los datos de la tabla. Las referencias pueden facilitar la localización de tablas en un libro grande.
Para usar referencias estructuradas en la fórmula, haga clic en las celdas de la tabla a las que desea hacer referencia en lugar de escribir su referencia de celda en la fórmula. Vamos a usar los siguientes datos de ejemplo para introducir una fórmula que usa automáticamente referencias estructuradas para calcular el salario anual.
Pasos a seguir:
- Para crear la tabla, selecciona cualquier celda dentro del rango de datos y presiona Ctrl+T.
- Asegúrate de que el cuadro de tabla tiene encabezados está activado y haga clic en Aceptar.
- Haz clic en la celda G2, escribe un signo igual (=) y haz clic en la celda E2.
En la barra de fórmulas, la referencia estructurada [ @ [Salario]] aparece después del signo igual.
- Escribe un asterisco (*) directamente después del corchete de cierre y multiplicaremos por 12.
En la barra de fórmulas, la referencia estructurada [ @ [Aguinaldo]] aparece después del 12.
- Presione Enter.
Excel crea automáticamente una columna calculada y copia la fórmula debajo de toda la columna para ti, si se ajusta para cada fila.
Veamos ahora, ¿Qué ocurre cuando uso referencias explícitas?
Si especifica referencias de celda explícitas en una columna calculada, puede resultar más complicado ver que es lo que esta calculando dicha formula.
En la hoja de cálculo de ejemplo, haga clic en la celda G2
En la barra de fórmulas, escriba =E2*12 y presione Entrar.
Observe que mientras Excel copia su fórmula debajo de la columna, no usa referencias estructuradas. Si, por ejemplo, agrega una columna entre las columnas existentes C y D, tiene que revisar la fórmula.
Cambiando nombre a la tabla…
Cuando creamos una tabla en Excel, suele asignarse un nombre predeterminado, por ejemplo Tabla1, Tabla2, etc.
Este nombre puede ser modificado para que puedas reconocerlo e identificarlo con mayor facilidad.
Selecciona cualquier celda de la tabla para mostrar la pestaña Herramientas de tabla > Diseño en la cinta de opciones.
Escribe el nombre que desee en el cuadro Nombre de tabla y presione Entrar.
En nuestros datos de ejemplo, hemos usado el nombre SalarioDept.
Sigue las siguientes reglas para los nombres de tabla:
Usar caracteres válidos. Inicia siempre un nombre con una letra, un carácter de subrayado (_) o una barra diagonal inversa (\). Usa letras, números, puntos y caracteres de subrayado en el resto del nombre.
Nota: No puede usar «C», «c», «R» o «r» para el nombre, porque ya están designados como un acceso directo para seleccionar la columna o fila de la celda activa al escribirlas en el cuadro Nombre o Ir a.
No use referencias de celdas. Los nombres no pueden ser idénticos a una referencia de celda, como Z$100 o R1C1.
No usar un espacio para separar palabras. Los espacios no se pueden usar en el nombre. Puede usar el carácter de subrayado (_) y punto (.) como separadores de palabras. Por ejemplo, DepartamentoSistemas, Aguinaldo_Compras o Departamento.Ventas.
No use más de 255 caracteres. Un nombre de tabla puede tener hasta 255 caracteres.
Usar nombres de tabla únicos. Los nombres duplicados no están permitidos. Excel no distingue entre caracteres en mayúsculas y minúsculas en los nombres, por lo que si escribe «Ventas» pero ya tiene otro nombre llamado «VENTAS» en el mismo libro, se le pedirá que elija un nombre único.
Usar un identificador de objeto. Si tiene previsto tener una combinación de tablas, tablas dinámicas y gráficos, es una buena idea anteponerse a los nombres con el tipo de objeto. Por ejemplo: tbl_Sales para una tabla de salario, pt_Sales para una tabla dinámica de salario y chrt_Sales para un gráfico de salario, o ptchrt_Sales para una tabla de salario (Gráfico dinámico).
Esto mantiene todos los nombres en una lista ordenada en el Administrador de nombres.
Reglas y recomendaciones para una sintaxis de las Referencias Estructuradas
También puede escribir o cambiar referencias estructuradas manualmente en la fórmula, pero para ello, ayudará a comprender la sintaxis de referencia estructurada. Repasemos la fórmula siguiente ejemplo:
=SUMA(SalarioDept[ [ #Totales] ; [Aguinaldo] ] ;SalarioDept[ [ #Datos] ; [SalarioAnual] ])
Esta fórmula tiene los siguientes componentes de referencia estructurada:
- Nombre de tabla: «DeptSales» es un nombre de tabla personalizado. Se hace referencia a los datos de tabla, sin ningún encabezado o las filas de totales. Puede usar un nombre de tabla predeterminado, como Tabla1, o cambiarlo para usar un nombre personalizado.
- Especificador de columna: [Aguinaldo] y [Salario Anual] son especificadores de columna que usan los nombres de las columnas que representan. Éstos hacen referencia los datos de la columna, sin cualquier encabezado de columna o fila de totales. Encierre siempre los especificadores entre corchetes como se muestra.
- Especificador de elementos: [#Totals] y [#Data] son especificadores de elementos especiales que hacen referencia a partes específicas de la tabla, como la fila total.
- Especificador de tabla: [ [ #Totales] ; [Salario Anual]] y [ [ #Datos] ; [Aguinaldo]] se tabla partes externas que representan los especificadores de la referencia estructurada. Las referencias externas siguen el nombre de tabla y las encierra entre corchetes.
Referencia estructurada: (DeptVentas[[#Totals],[Aguinaldo]] y Ventasdept[[#Data], [Importe de la comisión]] son referencias estructuradas, representadas por una cadena que comienza con el nombre de la tabla y termina con el especificador de columna.
Cuando se crea o editan referencias estructuras, te sugiero usar estas reglas de sintaxis:
Utilizar especificadores entre corchetes angulares. Todos los especificadores de tablas, columnas y elementos especiales deben ir incluidos entre corchetes ([ ]). Un especificador que contenga otros especificadores requiere corchetes externos para incluir los corchetes internos de los otros especificadores. Por ejemplo: =DeptVentas[[Empleado]:[Región]]
Los encabezados de columna son cadenas de texto. Pero no requieren presupuestos cuando estos se utilizan en una referencia estructurada. Números o fechas, como 2014 o 1/1/2014, también se consideran cadenas de texto. No puede usar expresiones con los encabezados de columna. Por ejemplo, la expresión SalarioDeptAño[ [ 2014] : [ 2012]] no funcionará.
Usar corchetes en torno los encabezados de columna con caracteres especiales. Si hay caracteres especiales, todo el encabezado de columna debe incluirse entre corchetes, lo que significa que corchetes dobles son obligatorios en un especificador de columna. Por ejemplo: =SalarioAnual[ [Total $ Cantidad]]
Aquí se ofrece la lista de caracteres especiales que necesitan un carácter de escape (‘) en la fórmula:
Llave de apertura
Llave de cierre
Almohadilla (#)
Comilla simple ( ‘)
Usar el carácter de espacio para mejorar la legibilidad en una referencia estructurada. Puede usar caracteres de espacio para mejorar la legibilidad de una referencia estructurada. Por ejemplo: =DeptSales[ [Empleado]:[Región] ] o =Ventasdept[[#Headers], [#Data], [% Aguinaldo]]
Es recomendable usar un espacio.
Use un carácter de escape para algunos caracteres especiales en los encabezados de columna. Algunos caracteres tienen un significado especial y requieren el uso de comillas simples ( ‘) como un carácter de escape. Por ejemplo: =AguinaldoDeptAño[ ‘#DeElementos]
Después de un corchete de apertura ( [ )
Antes de un corchete de cierre ( ] ).
Después de un punto y coma.
A continuación se ofrece la lista de caracteres especiales que necesite otro entre corchetes en la fórmula:
Tabulador
Avances de línea
Retorno de carro
Coma
Dos puntos
Período
Llave de apertura
Llave de cierre
Almohadilla (#).
Comilla simple ( ‘)
Comillas dobles ( «)
Izquierda entre llaves ( { } )
Haga clic con el botón secundario llave ( })
Signo de dólar
Acento circunflejo ( ^)
Y comercial
Asterisco ( * )
Signo más
Signo de igualdad
Signo menos
Símbolo mayor que ( >)
Símbolo Menor que ( <)
División (/)
Sugerencias y recomendaciones para trabajar con referencias estructuradas
Ten en cuenta lo siguiente cuando trabajes con referencias estructuradas.
Usar fórmula autocompletar. Se dará cuenta de que usar fórmula autocompletar para escribir referencias estructuradas es muy útil y, además, garantiza que se emplea la sintaxis correcta. Para obtener más información, consulte Usar Fórmula Autocompletar.
Decidir si se generan referencias estructuradas para tablas en semiselecciones. De forma predeterminada, al crear una fórmula, al hacer clic en un rango de celdas dentro de una tabla, se seleccionan las celdas y se escribe automáticamente una referencia estructurada en lugar del rango de celdas de la fórmula. De este modo, es mucho más fácil escribir una referencia estructurada. Para activar o desactivar este comportamiento, active o desactive la casilla «Usar nombres de tabla en fórmulas» en el cuadro de diálogo Opciones de archivo > > fórmulas > Trabajar con fórmulas.
Usar libros con vínculos externos a Excel tablas en otros libros. Si un libro contiene un vínculo externo Excel una tabla de Excel en otro libro, ese libro de origen vinculado debe estar abierto en Excel para evitar errores de #REF! en el libro de destino que contiene los vínculos. Si abres primero el libro de destino y #REF! aparecerán errores, se resolverán sí, a continuación, abre el libro de origen. Si primero abre el libro de origen, no debería ver códigos de error.
Convertir un rango en una tabla y viceversa. Cuando convierte una tabla en un rango, todas las referencias de celda cambian a las referencias absolutas equivalentes de estilo A1. Cuando convierte un rango en una tabla, Excel no cambia automáticamente ninguna referencia de celda de este rango a sus referencias estructuradas equivalentes.
Desactivar encabezados de columna. Puede activar y desactivar los encabezados de columna de tabla desde la pestaña Diseño > fila de encabezado. Si desactiva los encabezados de columna de tabla, las referencias estructuradas que usan nombres de columna no se verán afectadas y podrá usarlas en fórmulas. Las referencias estructuradas que hacen referencia directamente a los encabezados de tabla (por ejemplo, =DeptSales[[#Headers],[%Comisión]]) dará como resultado #REF.
Agregar o eliminar columnas y filas de la tabla. Dado que los rangos de datos de tabla a menudo cambian, las referencias de celda para referencias estructuradas se ajustan automáticamente. Por ejemplo, si usa un nombre de tabla en una fórmula para contar las celdas de datos en una tabla y, a continuación, agrega una fila de datos, la referencia de celda se ajusta automáticamente.
Cambiar el nombre de una tabla o columna. Si cambia el nombre de una columna o tabla, Excel cambia automáticamente el uso de esa tabla o encabezado de columna en todas las referencias estructuradas que se utilizan en el libro.
Mover, copiar y rellenar referencias estructuradas. Cuando copia o mueve una fórmula con referencias estructuradas, las referencias estructuradas permanecen iguales.
A continuación, te compartimos un video donde puedes conocer un poco más sobre el tema, no olvides practicar con los ejemplos.
