Blog de Ninja ExcelFórmulas y funciones de ExcelReferencias absolutas y mixtas en Excel: aprende a usarlas

Referencias absolutas y mixtas en Excel: aprende a usarlas

Mujer frente a la pizarra

Las referencias absolutas y mixtas es un conocimiento esencial que debes dominar al utilizar Excel. En este post te entregamos todo lo que necesitas saber para aplicarlas de manera correcta.

Información clave sobre referencias absolutas y mixtas

Una REFERENCIA ABSOLUTA es “bloquear” o “fijar” las columnas y/o filas de una celda de Excel para que no cambien cuando se copia la fórmula de una celda a otra. A diferencia de una referencia relativa, se tiene como objetivo a fijar cierta celda para usar en fórmulas.

Lo básico sobre referencias

Concepto: Una referencia es el uso de una celda en una fórmula de Excel. En vez de escribir en la celda A1 “=2+5”, lo que haces es darle el valor 2 la celda A2, el valor 5 a A3 y finalmente en A1 escribes “=A2+A3” para obtener el resultado.

Finalidad: Permite darle flexibilidad a las fórmulas, en el sentido que si cambias el valor de una de las celdas, automáticamente se ajustará el valor que toma la referencia. En el mismo ejemplo anterior si A2 cambiase a 7, se ajusta el valor de A1 a 12 (7+5) sin que hagas nada. Además, al ser automático se evitan errores y se ahorra tiempo.

Introducción: referencias relativas

Para entender una referencia absoluta, primero debemos entender las referencias relativas en Excel. Las referencias relativas son la manera “normal” en la que uno escribe fórmulas, en que se indica una celda (o conjunto de celdas).

Así, la celda a la que se hace referencia, va a ir cambiando a medida que copias la fórmula en otra celda, dado que lo que Excel entiende es la posición relativa de las celdas. Es más fácil entenderlo con el siguiente ejemplo:

Primer ejemplo referencias relativas Excel

Tenemos una tabla en que tenemos el pago por hora y las horas trabajadas con objetivo de obtener el pago total para cada trabajador. Así, necesitamos multiplicar el pago por hora por las horas trabajadas. Para eso, escribimos =C3*D3 en la celda E3:

Explicación referencias relativas en ExcelPara rellenar el resto de la tabla debemos arrastrar la fórmula usando la esquina inferior derecha de la celda E3 o bien haciendo doble click en esta misma esquina

Click arrastrar referencias relativas Excel
Resultado final referencias relativas Excel

Como se ve en la imagen, Ana trabajó 3 horas y le pagan $13 por hora. Así, su pago total es $39 y vemos que la fórmula en E5 es =C5*D5. Esto es una referencia relativa: Excel recuerda la fórmula como “multiplicar la celda de la izquierda por la que está dos a la izquierda”.Explicación referencias relativas en Excel

Referencias absolutas

Cuando queremos usar una fórmula de Excel pero queremos fijar una celda para que no se mueva si copiamos o arrastramos la fórmula usamos referencias absolutas. Así podemoss escribir la fórmula una vez y no tenemos que reescribir las fórmulas en cada celda.

Pensemos en el ejemplo anterior, pero que a todos les pagan $11 por hora trabajada, como se ve en la imagen:

Ejemplo referencias absolutas Excel

Así, todos deben ser multiplicados por el mismo factor. Si es que usaramos referencias relativas en D3 escribiríamos “=C3*G3” y el primer resultado nos dará correcto.

Aplicar referencias relativas de manera incorrecta. Referencias absolutas Excel
Resultado de una referencia relativa en Excel

Sin embargo, si arrastramos la fórmula, vemos que los otros valores que obtuvimos son $0.

Arrastrar referencias relativas con resultado incorrecto. Referencias absolutas Excel

¿Por qué es esto? Porque la referencia es relativa. Por ejemplo, para Ana vemos que la fórmula es “=C5*G5” y G5 no tiene ningún valor asignado y Excel lo toma como 0.

Uso de referencia relativa incorrecto en Excel. Referencia absoluta en Excel

Entonces, ¿cómo fijamos la celda G3 para que no se mueva al arrastrar o copiar la fórmula? Esto lo haremos usando el signo peso o dólar ($) al escribir la celda que queremos fijar. Aplicándolo al ejemplo, escribimos en la celda D3 “=C3*$G$3”.

Referencia absoluta Excel correctamente usadaLuego, si arrastramos la fórmula (o copiamos la fórmula más abajo) vemos que se multiplica la celda de la izquierda (horas trabajadas) por la celda G3 siempre.

Referencias absolutas en Excel ejemplo correctamente usadas Por ejemplo, para Ana la fórmula es “=D5*$G$3”.

Ejemplo correcto de referencia absoluta en Excel

Tip Ninja: No gastes tiempo escribiendo “$” a mano para hacer referencias absolutas en Excel, puedes aplicarlo apretando F4 o Fn+F4 en Windows o Cmd+T en Mac. Puedes avanzar en tu conocimiento respecto de atajos en Excel visitando este post si utilizas Windows o este si utilizas Mac.

Referencias mixtas

Una referencia mixta es una referencia que es parte relativa y parte abosulta. Es decir, se mantiene alguna parte “libre” y otra fija. Este tipo de referencias se usan para copiar o arrastrar fórmulas sin tener que reescribirlas o editarlas manualmente, ahorrando tiempo y evitando errores.

En general es para fijar sólo una columna o sólo una fila:

  • Al usar “=$A1” se fija la columna A, dejando que la fila se mueva
  • Al usar “=A$1” se fija la fila 1, dejando que las columnas se muevan

Para ilustrar las referencias mixtas, veremos el siguiente ejemplo en que debemos llenar una tabla con las comisiones de cada monto de venta para cada porcentaje de comisión:

Plantilla de referencia mixta referencia absoluta en ExcelPor ejemplo, si queremos llenar la siguiente tabla en que debemos sacar el monto de una comisión para cada precio de producto y cada porcentaje de comisión, si es que usamos referencias relativas obtenemos y arrastramos:

Uso incorrecto de referencias relativas en Excel. Referencias mixtas en ExcelObtenemos:

Referencias relativas en Excel con uso incorrecto. Referencias mixtas en Excel.Tip Ninja: Cuando en una celda aparece “########” no es que haya un error en la fórmula, sino que el número es tan grande que ocupa más espacio que el ancho de la celda. Si ajustamos el ancho de la celda podemos ver el número completo:

Explicación de qué son los signos gato. Referencias mixtas en Excel. Referencias relativas en Excel.

Vemos que, por ejemplo, para la celda D5, se está multiplicando las celdas C5 por D4 y no quedó fija la columna de precios ni la fila de porcentaje de comisiones.

Uso incorrecto de referencias relativas en Excel. Referencias absolutas en Excel.Para resolver este problema, podríamos usar referencias absolutas en C4 escribir “=B4*$C$3” y arrastrar hacia abajo, para que para cada precio se multiplique por 10%.

Ejemplo referencia absoluta en Excel y referencia mixta en Excel

Esto nos daría el resultado correcto arrastrando hacia abajo, pero si arrastramos hacia al lado no nos da un resultado correcto:

Uso de referencias absolutas en Excel y referencia mixta en ExcelUso incorrecto de referencia absoluta en Excel, referencia mixta ExcelSi nos fijamos, en la celda D5 vemos que usa la celda a su derecha porque sigue con referencia relativas y la multiplica por la comisión de 10% porque fijamos la celda C3.

Uso incorrecto de referencias absolutas en Excel. Referencias mixtas Excel

Una solución es hacer el mismo proceso de referencias absolutas para cada columna, pero eso puede ser mucho trabajo si trabajamos con más datos y hay más probabilidades de cometer errores.

Para solucionar esto, usamos referencias mixtas. Para eso, en la celda C4 escribiremos “=$B4*C$3”.

Referencias mixtas en Excel ejemplo

¿Qué significa esto?

En cuanto “$B4”, estamos fijando la columna B para que cuando arrastremos la fórmula hacia a la derecha siga siendo referenciada la columna B, pero que cuando arrastremos hacia abajo sí se mueva de fila hacia B5, B6, etc.

En cuanto a “C$3”, estamos fijando la fila 3 para que cuando arrastremos hacia abajo se siga haciendo referencia a la fila 3, pero que cuando arrastremos hacia la derecha sí se mueva de columna hacia D3, E3, etc.

Como se ve en en la imagen:

Explicación referencia mixta en Excel

Luego, al arrastrar hacia la derecha:

Ejemplo referencia mixta en Excel, arrastrar hacia abajo y a la derecha correctamente
Ejemplo referencia mixta en Excel, arrastrar hacia abajo correctamente con explicación

Tip Ninja: El atajo para hacer referencias mixtas es apretando F4 en Windows o Cmd+T en Mac una segunda o tercera vez. Por ejemplo, si tienes la celda “B2” y apretas F4, vamos a obtener “$B$2”. Si la apretamos por segunda vez obtenemos “B$2”. Si lo hacemos una tercera vez obtenemos “$B2”. Finalmente, una cuarta vez, se vuelve a “B2”. En resumen:

Cantidad de veces F4 o Cmd+TResultado
NingunaB2
1 vez$B$2
2 vecesB$2
3 veces$B2
4 vecesB2
Logo ninja excel blanco

La plataforma líder de capacitación en Excel para empresas.

Compañía

Copyrigth © 2022 Ninja Excel.