Una referencia absoluta deja fija una celda al copiar una fórmula. En =B2*$E$1, los signos de dólar fijan E1: copia la fórmula hacia abajo y todas las filas siguen multiplicando por E1, mientras que B2 pasa a B3, B4 y así sucesivamente. Para añadir los signos de dólar, haz clic en la referencia dentro de la fórmula y pulsa F4 (en Mac, Cmd+T).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
=B2*$E$1C2 se escribió una vez y se copió hacia abajo. Haz clic en C4: su fórmula es =B4*$E$1. La celda de ventas bajó a la fila 4 y la tasa se quedó en E1. Cambia la tasa de E1 a 8% y se actualizan todas las comisiones.
Referencias relativas y absolutas
| Referencia | Nombre | Copiada una fila abajo y una columna a la derecha |
|---|---|---|
A1 | relativa | B2 |
$A$1 | absoluta | $A$1 |
A$1 | mixta: fila fija | B$1 |
$A1 | mixta: columna fija | $A2 |
Una referencia normal como B2 es relativa: Excel la guarda como "la celda que está en esta posición respecto a mí", así que una copia una fila más abajo apunta una fila más abajo. Es justo lo que quieres para datos por fila, y es lo predeterminado. El $ delante de una letra de columna o de un número de fila fija esa parte.
El error clásico: copiar hacia abajo sin $
Aquí está otra vez la hoja de comisiones, con =B2*E1 en C2 y sin signos de dólar. La primera fila está bien. Las demás dan 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
=B3*E2C3 contiene =B3*E2: la referencia de la tasa bajó a E2, que está vacía, y una celda vacía cuenta como 0. Arréglalo aquí: haz clic en C2, cambia la fórmula a =B2*$E$1 y pulsa Enter. Toda la columna se corrige, porque C3:C6 son copias de C2. Cuando la celda fija es un divisor, como en =B2/B7 para la parte de un total, el mismo error muestra #¡DIV/0! (#DIV/0! en inglés, como lo muestra la tabla) en vez de 0 (porcentaje del total es el caso habitual).
Pulsa F4 para añadir los signos de dólar
Mientras escribes o editas una fórmula, pon el cursor en una referencia (o justo detrás) y pulsa F4. Cada pulsación pasa a la forma siguiente:
E1 -> $E$1 -> E$1 -> $E1 -> E1
En muchos portátiles F4 controla la pantalla o el sonido, así que pulsa Fn+F4. En Mac, usa Cmd+T, o Fn+F4. También puedes escribir tú los signos $.
Referencias mixtas: fijar solo la fila o la columna
Una referencia mixta tiene un solo signo de dólar. $A2 siempre lee la columna A pero deja moverse la fila; B$1 siempre lee la fila 1 pero deja moverse la columna. Con las dos en una fórmula, una sola fórmula copiada por una cuadrícula construye una tabla de multiplicar:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
=$A2*B$1B2 contiene =$A2*B$1. Haz clic en F6: contiene =$A6*F$1, el número de fila de la columna A por el número de columna de la fila 1, así que muestra 25. Quita un signo de dólar en B2 y la tabla se desarma, porque las copias empiezan a multiplicar celdas vecinas en lugar de los encabezados.
El mismo patrón calcula los precios de una lista con varios descuentos: =$A2*(1-B$1) con los precios en la columna A y los descuentos en la fila 1.
Total acumulado con un rango fijo por un extremo
Un rango puede quedar fijo solo por un extremo. =SUMA($B$2:B2) siempre empieza en B2, mientras que su final baja al copiar la fórmula, así que cada fila suma todo hasta ella misma. La tabla también acepta las fórmulas escritas en español, con punto y coma (o con comas, como en México):
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
=SUMA($B$2:B2)C6 contiene =SUM($B$2:B6) y muestra 2230, el total de los cinco meses. El mismo rango fijo por un extremo hace que =CONTAR.SI($A$2:A2;A2) cuente cuántas veces ha aparecido ya un valor, que es como se marcan los duplicados después del primero.
Práctica: una fórmula para toda la tabla
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
Tu turno: En B2, escribe el precio del primer producto con el descuento de B1. Usa $ para que la misma fórmula, copiada a lo ancho y hacia abajo hasta D5, dé todos los precios de la tabla.
La hoja copia tu fórmula en cada celda de B2:D5, como lo haría el controlador de relleno, y Comprobar lee los doce resultados. Sin los signos de dólar correctos, las copias de la fila 3 o de la columna C leen el precio o el descuento equivocado.
Referencias absolutas a otra hoja o a una tabla de búsqueda
Los signos de dólar funcionan igual con un nombre de hoja: =B2*Settings!$B$1. Importan sobre todo en las búsquedas, donde la tabla debe quedarse quieta mientras el valor buscado se mueve: =BUSCARV(A2;$E$2:$F$10;2;FALSO) copiada hacia abajo sigue buscando en E2:F10, mientras que =BUSCARV(A2;E2:F10;2;FALSO) desliza la tabla una fila hacia abajo en cada copia y empieza a perder las primeras filas (BUSCARV, VLOOKUP en inglés). Si una celda fija se usa en muchas fórmulas, también puedes darle un nombre con Fórmulas > Definir nombre y escribir =B2*Rate; un nombre definido así apunta a la misma celda desde cualquier fórmula, como $E$1.
Preguntas frecuentes
¿Qué significa el signo $ en una fórmula de Excel?
Fija la parte de la referencia que va detrás. En $E$1 quedan fijas la columna E y la fila 1, así que la referencia sigue siendo E1 copies la fórmula donde la copies. E$1 fija solo la fila y $E1 solo la columna.
¿Cuál es el atajo de la referencia absoluta en Excel?
Haz clic dentro de la referencia mientras editas la fórmula y pulsa F4 (Fn+F4 en muchos portátiles). Cada pulsación pasa por $A$1, A$1, $A1 y A1. En Mac, pulsa Cmd+T, o Fn+F4.
¿Qué diferencia hay entre referencias relativas y absolutas?
Una referencia relativa como B2 se mueve al copiar la fórmula: una fila más abajo pasa a ser B3. Una referencia absoluta como $B$2 sigue siendo $B$2. Usa referencias absolutas para una celda que necesitan todas las filas, como una tasa o un total.
¿Qué es una referencia mixta en Excel?
Una referencia con un solo signo de dólar: $A2 fija la columna y deja moverse la fila, B$1 fija la fila y deja moverse la columna. =$A2*B$1 copiada a lo ancho y a lo alto de una cuadrícula construye una tabla de multiplicar.
¿Por qué mi fórmula muestra 0 o #¡DIV/0! después de arrastrarla hacia abajo?
Una referencia que debía quedarse fija se movió con la copia. Si la fila 2 tiene =B2/B7, la fila 3 recibe =B3/B8, y B8 está vacía. Fija el total con =B2/$B$7 y vuelve a copiar.
