2

El uso de referencias en las fórmulas

Muchos de las dudas que llegan al foro de Ayuda Excel tienen que ver con que el usuario no sabe aplicar las referencias de celdas en la forma que corresponde en cada momento. En general, se utilizan bien estas referencias, pero en el contexto equivocado.

Si te encuentras en esta situación, hoy voy a intentar resolver de una vez por todas el problema para que le puedas sacar todo el partido a las fórmulas.

Imagino que ya sabrás que una referencia de celda indica la posición en la que se encuentra dicha celda dentro de la cuadrícula.

Una sola referencia puede abarcar una o varias celdas, y éstas pueden encontrarse en la misma hoja o en diferentes hojas del libro.

Para comenzar, te diré que existen dos formas de escribir una referencia: el estilo A1 y el estilo F1C1 (o R1C1 en inglés).

El estilo de referencia A1

Es la forma “normal” de nombrar una celda. Es la que utilizamos el 99% de los usuarios. La referencia está formada por el nombre de la columna y el número de la fila. Aquí no hay mucho que decir. Te muestro una tabla con los diferentes tipos de referencia:

Si escribes Significa
A15 La celda A15
B15:B20 Las filas 15-20 de la columna B
B15:D25 Las filas 15-25 de las columnas B, C y D
3:3 Todas las celdas de la fila 3
4:8 Todas las celdas de las filas 4, 5, 6, 7 y 8
B:B Todas las celdas de la columna B
B:E Todas las celdas de las columnas B, C, D y E

Al introducir en una fórmula cualquiera de las referencias anteriores, estás llamando a las celdas de la misma hoja en la que introduces la fórmula. Ahora  te indico cómo hacer referencia a celdas en otras hojas y en otros libros.

El estilo de referencia F1C1

También puedes referirte a una celda con este estilo (F1C1, Fila 1, Columna 1).

En este caso, las letras de las columnas se convierten en números. Este es el estilo con el que la grabadora de macros hace su trabajo, así que puede serte útil a la hora de utilizarla.

Como habrás podido apreciar, las referencias están formadas por una F seguida del número de fila y una C seguida del número de columna.

Te muestro unos cuantos ejemplos:

La referencia Significa
F[-2]C Referencia relativa a la celda que se encuentra en la misma columna y dos filas hacia arriba
F[2]C[2] Referencia relativa a la celda que se encuentra dos columnas hacia la derecha y dos filas hacia abajo
F2C2 Referencia absoluta a la celda que se encuentra en la segunda fila y en la segunda columna
F[-1] Referencia relativa a todas las celdas de la fila situada inmediatamente encima
F Referencia absoluta a todas las celdas de la fila actual

Si creas tus macros manualmente sin necesidad de la grabadora, mejor olvídate de este estilo de formato porque seguro que te lía más que ayudarte. No obstante, puedes activarlo desde el cuadro Opciones, en el menú Fórmulas.

¿Qué diferencia hay entre referencias relativas y absolutas?

Si eres de los que habitualmente copian fórmulas en otras partes de Excel, no puedes pasar por alto esta diferencia porque los resultados puede que no sean los que esperes.

Gracias a las referencias relativas, tanto si copias una fórmula en la celda de debajo o si utilizas Autorrellenar, estas referencias se ajustan automáticamente, para mostrar el resultado adecuado. Mira este ejemplo:

Si después de escribir la fórmula anterior, utilizas la herramienta Autorrellenar  para calcular el resto de importes, verás que en cada celda se copian referencias adaptadas a los operadores adecuados.

referencias fórmulas Excel

Una referencia absoluta se reconoce porque muestra algún símbolo de dólar ($) delante de la fila, de la columna o de ambas. Cuando quieres dejar fija una referencia mientras copias las celdas o utilizas la herramienta Autorrelleno, es indispensable su uso.

Mira el siguiente ejemplo:

referencias en fórmulas Excel

Si en el ejemplo del dibujo arrastraras la fórmula hacia abajo obtendrías un error. ¿Por qué? Pues porque al igual que en el ejemplo anterior, las dos referencia se mueven, en este también.

Uso de referencias en fórmulas Excel

Para solucionar estos errores debes mantener fija la referencia de la celda C3 y dejar móvil la otra.

Escribiendo la fórmula de la siguiente forma, ya no tendrás ningún problema:

usar referencias fórmulas Excel

Además de las referencias relativas y absolutas, también puedes encontrar las mixtas. Estas referencias fijan solamente una parte de la celda (la fila o la columna).

El ejemplo más claro de referencias mixtas lo puedes ver al crear una “tabla de multiplicar”:

referencias fórmulas Excel uso

En la referencia $A2 se mantiene fija la columna, pudiendo utilizar hacia abajo la herramienta Autorrellenar. Igualmente, en la referencia B$1, se mantiene fija la fila, pudiendo utilizar el Autorrelleno hacia la derecha.

Referencias a otras hojas y a otros libros

Aunque la mayoría de las fórmulas contienen referencias a celdas de la misma hoja, también es posible mostrar en una hoja, el resultado de un cálculo con datos de otra hoja.

Imagina que en las celdas A1:B3 de la Hoja1 se encuentran unos valores que quieres sumar en la celda A1 de la Hoja2. En este caso tendrías que escribir la siguiente fórmula:

=SUMA(Hoja1!A1:B3)

Para hacer referencia a celdas que se encuentran en otras hojas del mismo libro debes escribir el nombre de la hoja con un símbolo de admiración detrás y seguido de la referencia de la celda.

Si la celda a la que quieres hacer referencia se encuentra en otro libro pero dentro del mismo directorio, bastará con poner delante el nombre del libro entre corchetes:

=SUMA([Libro1.xlsx]Hoja1!A1:B3)

Si el nombre del archivo o el nombre de la hoja contienen algún espacio debes encerrar los entre apóstrofes:

=SUMA(‘[Libro1.xlsx]Hoja1!’A1:B3)

Si, además de eso, la celda se encuentra en otro directorio diferente, tendrás que especificar la ruta completa:

=SUMA(‘[C:Mis documentosLibro1.xlsx]Hoja1!’A1:B3)

Referencias 3D

Una referencia 3D es la forma que hay de trabajar con un rango de celdas que se encuentran en diferentes hojas. Para que veas un buen ejemplo, he hecho muy pequeña la pantalla en la siguiente imagen:

referencias celdas Excel suma

Aquí, se están sumando los valores que hay en la celda B5 de las hojas Enero, Febrero y Marzo.

Comenta si te ha parecido interesante

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 2 comentarios
8 conocimientos que debes comenzar a dominar para convertirte en un experto en Excel - Ayuda Excel

[…] Hace unos meses escribí este artículo sobre el uso de referencias en las fórmulas. […]

Responder
Los 7 errores de fórmulas de Excel y cómo solucionar cada uno - Ayuda Excel

[…] Revisa las referencias de las fórmulas. En muchos casos el error se debe a que no se utilizan referencias absolutas. Echale un vistazo a este artículo donde te muestro cómo utilizar las referencias en las fórmulas de Excel. […]

Responder

Escribe una respuesta:

Powered by WishList Member - Membership Software