Referencias de celda en Excel, cómo funcionan

Las referencias de celda constituyen la gran ventaja de Excel, el hecho de no tener que referirnos a un dato concreto si no al contenido de una celda o rango de celdas nos da mucha versatilidad en los cálculos y nos permite hacer construcciones complejas de forma sencilla e intuitiva. Podemos referirnos a una celda o rango de celdas de muchas formas.

Estilo de referencia columna-fila A1

Es la nomenclatura habitual de Excel, la referencia A1 que se refiere a la columna A y la fila 1 que son encabezados de fila y columna de la hoja respectivamente. La última versión de Excel cuenta con hasta 16.384 columnas hasta la XFD y un total de 1.048.576 filas, así que, salvo que manejemos muchos datos, difícilmente se nos quedará corto un libro de Excel. Las distintas formas a las que nos podemos referir a un celda o rango son:

• B15: Se refiere a una sola celda, la situada en la columna B y la fila 15
• B15:B30: Se refiere a un rango de celdas de una sola columna, la B, para las celdas que van desde la fila 15 hasta la fila 30, ambas incluidas.
• B20:D20: Se refiere a un rango de celdas de una misma fila, la fila 20, para todas las celdas situadas entre la columna B y la D, ambas incluidas.
• 10:10: Se refiere al rango que contiene todas las columnas de la fila 10.
• 10:20: Se refiere al rango que contiene todas las columnas de la hoja y las filas que van de la 10 a la 20, ambas incluidas.
• F:F: Se refiere al rango que contiene todas las filas de la columna F de la hoja de cálculo.
• F:J: Se refiere al rango que contiene todas las filas de las columnas entre la F y la J, ambas incluidas.
• B20:D50: Se refiere al rango de celdas situado entre las columnas B y D y las filas 20 y 50.

Celda o rango de otra hoja del mismo libro: todos los estilos de referencia a celdas podemos usarlos para celdas de la misma hoja de cálculo o para otras hojas del mismo libro, para hacer referencia a otra hoja pondremos el nombre de la hoja seguido del signo de exclamación “!” y a continuación el rango deseado. Imaginemos que tenemos un libro con dos hojas, una llamada “informe”, donde haremos el resumen de los datos contenidos en la otra hoja de cálculo llamada “datos”. Como los cálculos los haremos en la hoja de “informe” pero refiriéndonos a la información de la hoja de “datos”, para sumar el rango A1:A15 desde la hoja de "informe", escribiremos: =SUMA(datos!A1:A15)

Referencia relativa, absoluta y mixta: en función del comportamiento de las fórmulas cuando éstas son copiadas a otras celdas hablaremos de estos tres tipos de referencia:

• Referencia relativa: Es la referencia por defecto de Excel. Cuando introducimos una fórmula con referencias de celda relativas, si copiamos la fórmula a otras celdas nos desplazará las filas y/o columnas.
• Referencia absoluta: La referencia absoluta nos permite que la referencia a una celda no se actualice al copiar y se quede en la posición inicial.

Veremos cómo funcionan estas dos referencias con un ejemplo. Imaginemos, que tenemos las ventas de un comercial pero con diferentes precios para una misma cantidad de unidades vendidas. Esto nos podría interesar para ver posibles escenarios de venta según el precio. Tendríamos esta tabla:

En la celda D1 tenemos la fórmula B2*C2, que sería el importe obtenido al vender 14 unidades a 1.141. Esta nomenclatura es la utilizada para decirle a Excel que son referencias relativas. Si arrastramos esta misma fórmula hasta la celda D4 el resultado será este:

Excel calcula de nuevo en D3 la fórmula B3*C3 que da como resultado 0 y en D4 la fórmula B4*C4 que también da como resultado 0 porque no hay unidades en las celdas B3 y B4. Este problema lo solucionaremos con una referencia absoluta. En lugar de escribir la fórmula B2*C2 en la celda D2, escribiremos la fórmula $B$2*C2, haciendo que B2 sea una referencia absoluta, lo que permitirá que esta celda no se actualice al copiarla. El resultado será el siguiente:

Las fórmulas en D3 y D4 se habrán copiado como $B$2*C3 y $B$2*C4 respectivamente. La celda C2 se ha actualizado porque es una referencia relativa, pero la celda B2 no porque es una referencia absoluta. Para asignar una celda como referencia absoluta utilizamos los signos del “$”.

• Referencia mixta: hemos visto como la referencia absoluta es fija y no es posible arrastrarla o copiarla, la referencia mixta tiene uno de los indicadores fijos o como referencia absoluta, el de columna o el de fila, y el otro al revés, de forma que conforman una referencia mixta. Para fijar la columna pondremos el símbolo de “$” a la izquierda de la letra que marca la columna, para fijar la fila lo pondremos a la izquierda del número indicador de fila.

Siguiendo con el ejemplo anterior imaginemos que, además del precio unitario contamos con una columna adicional que indica el peso de cada unidad vendida, de forma que podemos obtener el total producto vendido por peso y el de cada comercial

Para obtener el peso de Emilio, vamos a utilizar la fórmula del importe, en este caso teníamos que el importe de Emilio salía de la fórmula =B2*C2 y que la fórmula para obtener el peso sería =B2*D2. En este caso vemos cómo B2 sería el elemento fijo, pero sólo queremos que quede fija la columna, porque utilizaremos la fila para copiar la fórmula al resto de comerciales, y que vaya cambiando la fila que contiene las unidades, pero no la columna, por tanto, en la celda E2 pondremos la fórmula $B2*C2, y arrastraremos ésta hacia abajo hasta la fila 4 y hacia la derecha hasta la columna F. Vemos como con una sola fórmula hemos podido completar seis datos, y el resultado quedaría así:

Estilo de referencia 3D

Se trata de una funcionalidad muy práctica cuando tenemos muchas hojas de cálculo en un mismo libro y tenemos que obtener datos de todas ellas. El estilo de referencia en 3D contiene una referencia a una celda o rango precedido del nombre de la primera y última hoja deseadas, de forma que aplica la fórmula a todas las celdas o rango de todas esas hojas. Por ejemplo, la fórmula =SUMA(Hoja3:Hoja6!A1:B3), sumará los rangos A1:B3 de todas las hojas comprendidas entre la 3 y la 6, ambas incluidas.

Se puede emplear la referencia 3D con las siguientes funciones: SUMA, PROMEDIO, PROMEDIOA, CONTAR, CONTARA, MAX, MAXA, MIN, MINA, PRODUCTO, DESVEST.P, DESVEST.M, DESVESTA, DESVESTPA, VAR.P, VAR.S, VARA y VARPA, pero no se puede utilizar con matrices, ni con el operador intersección ni en fórmulas con una intersección implícita.

Si utilizas este estilo de referencia debes tener en cuenta qué sucede si eliminas, mueves o añades hojas. Si eliminas hojas éstas desaparecen del cálculo, si añades hojas dentro del rango de hojas delimitado éstas se añadirán de forma automática a la fórmula, y si las mueves dentro o fuera del rango de hojas éstas se añadirán o se eliminarán.

Estilo de referencia F1C1

Es un estilo diferente de referencia en el que se numeran tanto las filas como las columnas, y es utilizado porque las macros utilizan este tipo de referencia. La nomenclatura que se utiliza es una F seguido del número de fila y una C seguido del número de columna. Algunos ejemplos serían:

• F[4]C[5]: Sería la referencia relativa de la celda situada 4 filas hacia abajo y 5 columnas a la derecha.
• F4C5: Sería la referencia absoluta a la celda que se encentra en la cuarta fila y la quinta columna.
• F[-1]C: Sería la referencia relativa de la celda de encima, en la misma columna.
• F: Sería la referencia absoluta a la fila actual.

Debes tener en cuenta que para poder utilizar el estilo de referencia F1C1 debes tenerlo activado. Para activarlo accede al backstage, clica en el menú de opciones, escoge el apartado fórmulas y la sección de “Trabajo con fórmulas”, donde verás que es la primera opción.