Las referencias
en Excel son parte fundamental de la hoja de cálculo ya que a través
de ellas podemos identificar cada una de las celdas de un libro de trabajo y de
esa manera acceder al valor contenido en cualquiera de ellas.
El objetivo de este
artículo es aclarar la mayor cantidad de dudas sobre el tema de referencias en
Excel y será útil para aquellas personas que comienzan a utilizar la
herramienta pero también para aquellos que ya tienen un poco de tiempo
utilizando la hoja de cálculo y no han escuchado sobre temas como las referencias
3D o el estilo de referencia F1C1.
Ya que todos los
valores de una hoja de cálculo están almacenados en las celdas, es de suma
importancia para los usuarios de Excel conocer la manera en que deberán acceder
a dichos valores utilizando las referencias. Sin más preámbulo, comenzaremos
con este estudio de las referencias en Excel.
¿Qué son las referencias en Excel?
Todo usuario de Excel
debe saber que todas las hojas están divididas en columnas y filas. Esta
división se hace evidente en la interfaz gráfica de Excel de la siguiente
manera:
- Por
debajo de la barra de fórmulas se encuentran los encabezados de columna
identificados por una letra.
- En
el extremo izquierdo están los encabezados de fila que muestran un número.
- En
el área de trabajo tenemos una cuadrícula que delimita el área de cada
celda de la hoja y donde se puede observar que cada una de ellas pertenece
a una determinada columna y fila.
En la imagen anterior,
la celda seleccionada (celda activa)es la celda que se encuentra bajo la
columna B y en la línea 3 y por lo tanto su dirección dentro de la hoja será
B3. La referencia de una celda siempre constará de dos partes: la
primera parte indicará la letra (o letras) de la columna a la que pertenece y
la segunda parte indicará su número de fila.
Excel siempre nos
ayudará a conocer fácilmente la referencia de la celda activa. En primer lugar,
el encabezado de la columna y fila de la celda seleccionada estarán resaltados en un color diferente al resto de los
encabezados. Además elCuadro de nombres siempre mostrará la
referencia de la celda activa tal como lo puedes observar en la siguiente
imagen:
Gráfico 20: referencias
En conclusión, la referencia
de una celda nos indica su ubicación dentro de una hoja, es decir, nos
ayuda a conocer rápidamente la columna y fila a las que pertenece.
Tipos de
referencias en Excel
Una referencia siempre
será la dirección de una celda dentro de una hoja y siempre
estará formada por la columna y fila en donde se ubica la celda. Así que,
cuando hablamos de tipos de referencias en Excel estamos
hablando sobre los diferentes tipos de comportamiento que pueden tener las
referencias al momento de ser copiadas a otras celdas.
Es muy fácil copiar fórmulas en Excel, pero ¿qué sucede con las referencias de dicha fórmula al
momento de hacer la copia? Es ahí en donde su comportamiento dependerá del tipo
de referencia que se haya utilizado y analizaremos las alternativas que tenemos
en las próximas tres secciones.
Referencias
relativas en Excel
De manera
predeterminada, las referencias en Excel son relativas. El
término relativo significa que al momento de copiar una
fórmula, Excel modificará las referencias en relación a la nueva posición donde
se está haciendo la copia de la fórmula. Por ejemplo, supongamos que en la
celda C1 tenemos la fórmula =A1+B1. En la siguiente imagen puedes observar el
resultado de esta operación si tenemos los valores 5 y 3 en dichas celdas:
Si ahora copio (Ctrl+C)
la fórmula de la celda C1 y la pego (Ctrl+V) en la celda C3, obtendré el
siguiente resultado:
La fórmula que ha sido
copiada a la celda C3 nos devolverá como resultado el valor cero. Al revisar la
fórmula de dicha celda nos daremos cuenta que eso se debe a que Excel ha
modificado automáticamente las referencias de la fórmula. En lugar de la celda
A1 ahora tenemos la celda A3, y en lugar de la celda C1 que teníamos originalmente,
ahora tenemos la celda C3.
En este ejemplo hemos
copiado la fórmula hacia una celda de la misma columna, pero dos filas por
debajo y eso es lo que indicará a Excel el ajuste a realizar en las
referencias. Ya que no hubo cambio en la columna, ésta permanecerá igual en la
nueva fórmula, pero ya que copiamos una celda que está dos filas hacia abajo,
Excel realizará dicho ajuste sumando la misma cantidad de filas a las
referencias. Ahora observa lo que sucede si vuelvo a copiar la fórmula de la
celda C1 y la pego en la celda F2.
En esta ocasión la
nueva celda se encuentra una fila hacia abajo y tres columnas a la derecha de
la celda original y por lo tanto las referencias se adecúan a tal movimiento añadiendo
tres columnas y una fila a cada referencia de la fórmula copiada.
Las referencias
relativas son muy útiles cuando necesitamos aplicar una misma fórmula sobre un
rango de datos. Por ejemplo, en la siguiente imagen puedes ver una hoja donde
las columnas A y B tienen valores numéricos y ahora necesito colocar la
multiplicación de ambos valores en la columna C. Para hacerlo rápidamente,
coloco la fórmula =A1*B1 en la celda C1 y posteriormente copio dicha fórmula
hacia debajo:
De esta manera
obtendremos automáticamente el resultado correcto para cada fila ya que Excel
modifica las referencias en cada fórmula mientras es copiada hacia abajo.
Referencias
absolutas en Excel
Hay ocasiones en las que
necesitamos “fijar” la referencia a una celda de manera que permanezca igual
aún después de ser copiada. Si queremos impedir que Excel modifique las
referencias de una celda al momento de copiar la fórmula, entonces debemos
convertir una referencia relativa en absoluta y eso lo podemos hacer
anteponiendo el símbolo “$” a la letra de la columna y al número de la fila de
la siguiente manera:
Si tomamos el primer
ejemplo de la sección anterior y utilizamos la fórmula =$A$1+$B$1 en la celda
C1 y posteriormente copiamos dicha fórmula a la celda C3, obtendremos el
siguiente resultado:
Gráfico 26: referencia absoluta
En este caso la celda
C3 devolverá el mismo resultado que la celda C1 porque ambas fórmulas hacen
referencia a las mismas celdas aún después de haberla copiado. Tendremos el
mismo efecto al copiar la fórmula de la celda C1 hacia la derecha:
Al convertir las
referencias en absolutas, no importa a donde copiemos la fórmula, dichas
referencias permanecerán siempre fijas. Para dejar en claro el uso de las
referencias absolutas, supondremos una tabla de datos como la siguiente:
La celda C2 hace el
cálculo del área para el primer círculo tomando en cuenta el valor de Pi que
está almacenado en la celda F1. En este momento la referencia a la celda F1 es
relativa y si copio la formula hacia abajo obtendré el siguiente resultado:
Al copiar la fórmula
hacia abajo, Excel modifica la referencia a la celda F1 y le agrega una fila
más para convertirla en una referencia a la celda F2 que es el comportamiento
natural de las referencias relativas. Para hacer que nuestras fórmulas hagan
siempre referencia a la celda F1 debemos transformar dicha referencia en
absoluta:
Observa con
detenimiento que la única referencia que he convertido en absoluta es la de la
celda $F$1. La otra referencia a la celda B2 la he dejado como relativa porque
necesito que sea modificada por Excel para tomar el valor del radio de
cada círculo conforme se copie la fórmula hacia abajo. El resultado después de
copiar la fórmula será el siguiente:
Todas las fórmulas
harán referencia a la celda F1 porque está establecida como absoluta, pero
Excel modificará correctamente las referencias a las celdas de la columna B
para obtener el resultado correcto.
Con esto hemos cubierto
los dos tipos de referencias en Excel más utilizados. Si quieres ver algunos
ejemplos adicionales sobre estos dos tipos de referencias te recomiendo consultar
el siguiente video tutorial: Referencias relativas y absolutas en
Excel.
Referencias
mixtas en Excel
En los ejemplos
anteriores sobre referencias absolutas utilizamos el símbolo “$” para fijar
tanto la columna como la fila. Sin embargo, es posible fijar solo la columna o
fijar solo la fila y a estas variaciones se les conoce como referencias
mixtas.
Si queremos fijar
solamente la columna, anteponemos el símbolo “$” a la letra de la columna y
dejamos la fila sin dicho símbolo. Si por el contrario deseamos fijar solamente
la fila, entonces anteponemos el símbolo “$”al número de la fila. En la
siguiente imagen podrás ver un resumen de los tipos de referencias que hemos
revisado hasta ahora:
Si colocas la fórmula
=$A1 dentro de la celda B1 y copias la fórmula hacia la derecha verás que la
fórmula se mantiene igual en todas las columnas porque hemos indicado que
deseamos tener la columna $A fija. Por el contrario, si copias la fórmula hacia
abajo, Excel modificará el número de fila:
Sin embargo, si cambiamos
el tipo de referencia mixta de la celda B1 por la fórmula =A$1 y copiamos hacia
la derecha y hacia abajo, obtendremos un resultado diferente al anterior ya que
habremos fijado solamente la fila:
Estos son los cuatro
tipos de referencias de los cuales puedes elegir al momento de construir tus
fórmulas. Recuerda que esta clasificación se basa en el comportamiento de la
referencia al copiar una fórmula.
Cómo cambiar entre
tipos de referencia
Un consejo muy útil
para hacer más eficiente el tiempo invertido al ingresar cualquier referencia
en una fórmula es el uso de latecla F4 cuando nos encontramos en el
modo de edición. Cuando el cursor de edición se encuentra sobre una referencia
y pulsamos la tecla F4, Excel cambiará el tipo de referencia sin la necesidad
de que ingresemos manualmente el símbolo “$”. Hagamos el siguiente ejemplo para
conocer el funcionamiento de este atajo de teclado:
- Selecciona
la celda B1 e ingresa la fórmula =A1 pero no pulses la tecla Entrar.
- El
cursor de edición se encontrará parpadeando al final de la referencia y si
pulsas la tecla F4 una vez, la referencia se transformará automáticamente
en absoluta =$A$1.
- Si
vuelves a pulsar la tecla F4 tendrás una referencia mixta como =A$1.
- Y si
pulsas una cuarta vez la tecla F4, la referencia será cambiada por la otra
opción de referencia mixta =$A1.
- Al
pulsar una quinta vez la tecla F4 volverás a la referencia relativa
original.
La tecla F4 solamente
cambiará la referencia que está sobre el cursor de edición así que, si quieres
aplicar este cambio en varias referencias de una misma fórmula, entonces
deberás colocar el cursor sobre cada referencia que será cambiada y pulsar F4
cada vez.
Referencias
a rangos de celdas
Hasta ahora hemos visto
cómo hacer referencia a una sola celda, pero Excel nos permite hacer referencia
a un conjunto de celdas adyacentes utilizando el operador de rango que
está representado por los dos puntos (:) y que deberá colocarse entre dos
referencias de celdas. Sin embargo, esas dos referencias de celdas no son al
azar, sino que deben ser el extremo superior izquierdo y el extremo inferior
derecho de las celdas adyacentes a las que deseamos referirnos.
Considera el siguiente
grupo de celdas adyacentes donde cada una de ellas tiene un valor numérico. La
referencia para este rango de celdas será B2:D4 y puedes ver que dicho rango es
reconocido por Excel al momento de hacer la suma.
Esto definitivamente es
una gran ventaja ya que de lo contrario tendríamos que indicar todas las
referencias a las nueve celdas, pero con este método podemos referirnos
fácilmente a todo el rango de celdas. Solo debes recordar que la primera
referencia corresponde a la celda del extremo superior izquierdo y la segunda
referencia a la celda inferior derecha.
En este ejemplo ambas
referencias son relativas, pero nada impide que utilicemos referencias
absolutas al crear una referencia a un rango, por ejemplo: $B$2:$D$4. Todas las
reglas que explique anteriormente para las referencias relativas, absolutas y
mixtas también aplicarán para las referencias a rangos de celdas.
Referencias
a celdas en otra hoja
En todos los ejemplos
mostrados hasta este punto he utilizado referencias dentro de la misma hoja
pero en más de una ocasión nos veremos en la necesidad de hacer referencia a
una celda que se encuentra en una hoja diferente.
La única regla que
debemos seguir es utilizar el nombre de la hoja donde se encuentra la celda de
nuestro interés e inmediatamente colocar el signo de exclamación (¡) seguido de
la referencia a la celda. De esta manera, si queremos referirnos a la celda C5
de la Hoja2, debemos hacerlo de la siguiente manera:
Una práctica que es
altamente recomendable en las referencias a celdas de otra hoja, es rodear el
nombre de la hoja con comillas simples (‘) ya que eso evitará cualquier error
en caso de que el nombre de la hoja tenga un espacio en blanco, por ejemplo:
Para saber más sobre
este tipo de referencias te recomiendo consultar el artículo Hacer referencia a
celdas de otras hojas en Excel.
Referencias
a celdas en otro libro
Si la celda que
necesitas acceder se encuentra en otro libro, entonces deberás indicar el
nombre del libro encerrado entre corchetes [] antes del nombre de la hoja de la
siguiente manera:
Este tipo de referencia
funcionará solamente si el libro especificado está abierto y además está
guardado en la misma ubicación que el libro actual. En caso contrario será
necesario indicar la ruta completa donde se encuentra dicho libro:
Observa que en este
último ejemplo el nombre del libro y la hoja, así como la ruta, están
encerrados entre comillas sencillas lo cual es ampliamente recomendable en caso
de tener espacios en blanco en los nombres de las carpetas de la ruta, o en el
nombre del libro u hoja.
Video instructivo
Video instructivo
No hay comentarios:
Publicar un comentario