Referencia absoluta de tabla de Excel para celdas, columnas y filas

Una de las primeras cosas que aprendemos en Excel es la magia del símbolo $. Congela la fila o columna, por lo que la referencia de la celda no cambia al copiar una fórmula. Esto se conoce como referencia absoluta. Con la introducción de las tablas surgió una forma diferente (y más semántica) de hacer referencia a las celdas, denominada referencias estructuradas. Sin embargo, las referencias estructuradas no siguen los mismos principios que el sistema de referencias de estilo estándar A1 que utilizamos habitualmente. utilizar. Como resultado, el método del símbolo $ no funcionará. Pero no se preocupe; Al final de esta publicación, sabrá cómo crear una referencia absoluta de tabla de Excel.

Tabla de contenido

• Guión
• Referencia absoluta de la tabla de Excel para la columna
• Referencia relativa y absoluta para celdas de la misma fila.
• Referencia relativa y absoluta para cualquier celda.
• Referencias absolutas en Cabeceras y Totales
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0122 Tabla Excel referencia absoluta.xlsx

Tener acceso

Guión

Todos los ejemplos de esta publicación utilizan la siguiente tabla.

La tabla muestra los costos que podría incurrir en un Safari Park por poseer diferentes tipos de animales. El nombre de la tabla es myTable , aunque no es un buen nombre, funcionará para este ejemplo.

Referencia absoluta de la tabla de Excel para la columna

Cuando se utilizan referencias estructuradas, se hace referencia a columnas completas con esta sintaxis:

tableName[columnName]

Usando los datos del ejemplo para sumar la columna Total, la fórmula sería:

=SUM(myTable[Total])

Si esto se arrastrara o copiara a otra columna, la fórmula cambiaría automáticamente. Si se copiara a la izquierda, cambiaría a:

=SUM(myTable[Qty])

Si se copia a la derecha, volverá a la primera columna de la tabla y cambiará a:

=SUM(myTable[Animal])

NOTA: Esta es una de las peculiaridades al trabajar con columnas de tabla. Una vez que una referencia de columna estructurada llega al final de la tabla, regresa al inicio.

Si utilizamos la referencia de estilo estándar A1, podríamos agregar los signos $ y cambiar el rango de G2:G9 (una referencia relativa) a $G$2:$G$9 (una referencia absoluta). Pero no podemos usar $ con las referencias estructuradas que se encuentran en Tablas.

Para lograr el mismo resultado, necesitamos agregar más corchetes, dos puntos ( : ) y repetir el nombre de la columna.

=SUM(myTable[[Total]:[Total]])

La diferencia entre la referencia relativa y absoluta para las columnas se muestra en azul arriba.

Si ha estado usando Tablas por un tiempo, notará que esta es la misma sintaxis que cuando hace referencia a varias columnas.

=SUM(myTable[[Food]:[Other]])

La referencia anterior muestra cómo sumar las columnas de Alimentos a Otros en los datos de ejemplo. Esto significa que las referencias de varias columnas seleccionadas con el mouse son absolutas de forma predeterminada.

Para crear una referencia relativa de varias columnas, debemos eliminar los corchetes exteriores y repetir el nombre de la tabla, como se muestra a continuación.

=SUM(myTable[Food]:myTable[Other])

O puede incluir cada columna individualmente dentro del cálculo como se muestra a continuación, lo que logra el mismo resultado.

=SUM(myTable[Food],myTable[Keepers],myTable[Other])

Referencia relativa y absoluta para celdas de la misma fila.

Cuando se utilizan tablas, la sintaxis para hacer referencia a una celda en la misma fila es la siguiente.

=[@Total]    or    =[@[Total Value]]

NOTA: la segunda sintaxis en el ejemplo anterior es donde el encabezado contiene un espacio o un carácter especial.

Si la fórmula está contenida en una celda fuera de la Tabla, también se debe agregar el nombre de la Tabla:

=myTable[@Total]  or  =myTable[@[Total Value]

Para hacer que una referencia de fila sea absoluta, se aplican los mismos principios que vimos para las referencias de columnas. No importa si la referencia está dentro o fuera de la tabla; el nombre de la tabla es obligatorio para ambos.

=myTable[@[Total]:[Total]]

Para hacer referencia a varias columnas, la sintaxis es similar.

=SUM(myTable[@[Food]:[Other]])

La referencia anterior muestra cómo sumar las columnas de Alimentos a Otros a partir de los datos de ejemplo. Esta es una referencia de columna absoluta.

Para crear una referencia relativa de varias columnas, debemos eliminar los corchetes exteriores.

=SUM([@Food]:[@Other])  or  =SUM(myTable[@Food]:myTable[@Other])

NOTA: El símbolo @ tiene el mismo propósito que cuando se trabaja con matrices dinámicas; calcula en función de la intersección implícita. Consulte esta publicación para obtener más información: Matrices dinámicas en Excel

Referencia relativa y absoluta para cualquier celda.

En la sección anterior, consideramos el escenario en el que la columna era absoluta mientras que la fila se basaba en una celda de la misma fila. Sin embargo, hacer referencia a una fila que no está en la misma fila tiene un enfoque diferente.

La función ÍNDICE devuelve una referencia de celda o un valor de una ubicación especificada en un rango o matriz. Como ejemplo simple, mire la siguiente fórmula:

=INDEX($G$2:$G$9,1)

Esta fórmula siempre devolvería la primera fila de las celdas G2:G9. Para devolver la segunda celda del mismo rango, la fórmula sería

=ÍNDICE($G$2:$G$9,2)

Por lo tanto, usar INDEX con referencias estructuradas nos permite hacer referencia a cualquier celda individual del rango. Por ejemplo, lo siguiente devuelve el primer elemento de la columna Total.

=INDEX(myTable[Total],1)

El remolque es una referencia absoluta, pero la columna es relativa; por lo tanto, esta es una referencia mixta.

Para crear una referencia relativa completa, también debemos encontrar una manera de contar los números de fila dentro del ÍNDICE.

A continuación se utiliza ROW(A1) para calcular una referencia de fila relativa. FILA(A1) se evalúa como 1. Cuando se arrastra hacia abajo, FILA(A2) se evalúa como 2. Por lo tanto, FILA crea el número relativo para usar en la función ÍNDICE.

=INDEX(myTable[Total],ROW(A1))

La fórmula anterior es una referencia de tabla totalmente relativa.

Referencias absolutas en Cabeceras y Totales

Hacer referencia a las filas Encabezado y Total dentro de una tabla vuelve a ser ligeramente diferente. Esto se debe a que los encabezados y los totales tienen inherentemente referencias absolutas a filas, pero no referencias a columnas.

La referencia relativa para los encabezados se muestra a continuación:

=myTable[[#Headers],[Food]]

Para convertir la referencia del encabezado en una referencia absoluta, agregue la sección resaltada a continuación.

=myTable[[#Headers],[Food]:[Food]]

Si ha leído esta publicación de principio a fin, entonces la referencia absoluta de varias columnas no le sorprenderá. La fórmula para contar los encabezados de las columnas se muestra a continuación.

=COUNTA(myTable[[#Headers],[Food]:[Other]])

Y la referencia relativa de varias columnas tampoco te sorprenderá.

=COUNTA(myTable[[#Headers],[Food]]:myTable[[#Headers],[Other]])

Todos los ejemplos de esta sección incluyen la fila Encabezado ( #Headers ). Para hacer referencia a la fila Total, utilice #Totals en su lugar.

Conclusión

Con una tabla, la sintaxis diferente para proporcionar referencias relativas o absolutas puede ser bastante sutil. Una @ o un corchete fuera de lugar puede causar un error que puede ser increíblemente frustrante de encontrar. Es más fácil comenzar con una referencia válida y luego ajustarla, en lugar de escribir la referencia desde cero.

Como ocurre con todo, la práctica es vital. En poco tiempo, crear una referencia absoluta de tabla de Excel será algo natural.

Artículos Relacionados:

• Total acumulado en una tabla de Excel
• Cómo usar una tabla de Excel dentro de una lista de validación de datos (3 formas)

---