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
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)
Deja una respuesta