Referencias relativas, absolutas y mixtas en Excel o similar

Para poder trabajar sin problemas con las planillas de cálculo es necesario tener muy claro como funcionan las referencias a celdas y/o rangos, aquí se plantea un artículo que pretende explicar su funcionamiento.

Parte de la información que se presenta fue extraída de la ayuda de Microsoft Excel, si bien fue escrita para los productos de Microsoft es válida para todas las plantillas de cálculo como ser: OpenOffice.org Calc, Google Docs, Numbers, etc.

Según la gente de Microsoft: “Una referencia identifica una celda o un rango de celdas en una hoja de cálculo e indica a Microsoft Excel en qué celdas debe buscar los valores o los datos que desea utilizar en una fórmula. En las referencias se puede utilizar datos de distintas partes de una hoja de cálculo en una fórmula, o bien utilizar el valor de una celda en varias fórmulas. También puede hacerse referencia a las celdas de otras hojas en el mismo libro y a otros libros. Las referencias a celdas de otros libros se denominan vínculos.".

Existen tres tipos de referencias: relativas, absolutas y mixtas.

Referencias relativas Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas. Por ejemplo, si copia una referencia relativa de la celda B2 a la celda B3, se ajusta automáticamente de =A1 a =A2."
La figura muestra el copiado de la celda B2 a B3, como se puede observar se ha desplazado una fila, y de haberse copiado a la celda C3, el contenido de la misma estaría apuntando a B2.

Referencias absolutas Una referencia de celda absoluta en una fórmula, como $A$1, siempre hace referencia a una celda en una ubicación específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias absolutas. Por ejemplo, si copia una referencia absoluta de la celda B2 a la celda B3, permanece invariable en ambas celdas =$A$1."
La figura muestra el copiado de la celda B2 a B3, como se puede observar que ahora no se ha desplazado ninguna fila, y de haberse copiado a la celda C3, el contenido de la misma estaría también apuntando a B1.

Referencias mixtas Una referencia mixta tiene una columna absoluta y una fila relativa, o una fila absoluta y una columna relativa. Una referencia de columna absoluta adopta la forma $A1, $B1, etc. Una referencia de fila absoluta adopta la forma A$1, B$1, etc. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia relativa y la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia relativa se ajusta automáticamente y la referencia absoluta no se ajusta. Por ejemplo, si se copia una referencia mixta de la celda A2 a B3, se ajusta de =A$1 a =B$1"
La figura muestra el copiado de la celda B2 a C3, como se puede observar no se ha desplazado la fila, pero si la columna. Si el contenido de B2 hubiese sido =$A1 al copiarla a C3 tendría =$A2.

Si se edita la formula, por ejemplo presionado F2 sobre la celda, y se sitúa el cursor sobre la referencia, al presionar F4 se van alternando las combinaciones mencionadas. Esto es para evitar digitar los símbolos de $.

A continuación se mostrarán un par de ejercicios o ejemplos:

Ejemplo 1:

En una hoja de cálculos se realizaron 5 cuadros, en el cuadro número 1 se ingresaron números del 1 al 9 ordenados.
En el centro del cuadro 2, se ingresó una referencia relativa al centro del cuadro 1: =C5.
En el centro del cuadro 3, se ingresó una referencia absoluta al centro del cuadro 1: =$C$5.
En el centro del cuadro 4, se ingresó una referencia mixta al centro del cuadro 1 fijando la fila: =C$5.
En el centro del cuadro 5, se ingresó una referencia mixta al centro del cuadro 1 fijando la columna: =$C5.
En los cuadros 2 al 5 se copia la celda del centro a las 8 celdas vecinas de los bordes.

Los resultados muestran que:

• Al utilizar referencias relativas en el cuadro 2 se logró reproducir exactamente la muestra.
• Al utilizar referencias absolutas en el cuadro 3 se llenó con el valor del centro de la muestra.
• Al utilizar referencias mixtas fijando la fila en el cuadro 4 solo varía las celdas de las columnas.
• Al utilizar referencias mixtas fijando la columna en el cuadro 5 se ve lo contrario al cuadro 4.

Ejemplo 2:

En la siguiente figura las formulas introducidas en las celdas con colores se visualizan a la izquierda de cada una.

Aquí se ingresó información desde la celda C2 hasta G6 alterando con celdas sin datos.

En las celdas E14 a E17 se definieron referencias a la celda E4 que contiene la palabra Centro con los tres tipos de referencia relativas, absolutas, y mixtas (una para fila y otra para columna).

Por ultimo se copió el rango E14 hacia las celdas C12, G12, C16 y G16 (defasaje de 2 celdas en diagonal para todas las direcciones).

Luego de realizar el copiado se puede ver los diferentes resultados según las referencias introducidas.

Errores frecuentes con referencias:

Uno de los errores mas frecuentes cuando se trabaja con diversas funciones se debe a no utilizar referencias absolutas.

Por ejemplo cuando se utiliza la famosa función BUSCARV o VLOOKUP, si a la matriz a buscar se la define con referencias relativas en lugar de absolutas, y cuando la función final se copia a las filas de abajo, la matriz también se desplazará hacia abajo provocando que apunte a un área donde no se encuentre el dato a buscar.

Uso erróneo de la función:

=BUSCARV(A1;F1:H100;2;FALSO)

NOTA: No siempre está mal utilizar referencias relativas en esta función, hay veces -generalmente muy pocas- en que se necesita este desplazamiento.

Uso correcto de la función:

=BUSCARV($A1;$F$1:$H$100;2;FALSO)

Normalmente se fija la columna done se encuentra el dato a buscar ($A1), que es utilizado cuando se quiera copiar la formula a la derecha para buscar una segunda columna de datos.

La matriz a buscar generalmente ($F$1:$H$100) es fija.