Por qué cambia una fórmula al rellenarla

La idea

Cuando escribes =B2*C2 en D2, Excel no guarda "la celda B2". Guarda "la celda dos columnas a mi izquierda, en mi fila" y "la celda una columna a mi izquierda". Cada referencia es un desplazamiento desde la celda que contiene la fórmula. Si mueves la fórmula, el desplazamiento apunta a otro sitio.

Ese único hecho explica el relleno hacia abajo y a la derecha, copiar y pegar, y el error más común de las hojas de cálculo. Una fórmula escrita una vez y rellenada en una columna es correcta en cada fila porque cada copia mira a sus propios vecinos.

Por qué existe

Imagina poner precio a 5,000 líneas de pedido. Si cada fórmula nombrara sus celdas de forma literal, escribirías 5,000 fórmulas distintas. Con desplazamientos escribes una y arrastras. Los desplazamientos son la razón por la que una hoja de cálculo escala desde un pedido de tres líneas hasta todo el catálogo de Harbor & Pine.

El precio de esa comodidad es que algunos punteros no deben moverse. Una tasa de impuesto, un descuento o un umbral de envío gratis está en una sola celda y la lee cada fila. Los signos de dólar son la forma de decirle a Excel qué punteros congelar.

Hay un segundo beneficio. Una fórmula que apunta a una celda de entrada documenta la regla: quien abra la hoja ve que el umbral de envío vive en un solo lugar, lo cambia y ve cómo responde cada fila. Una fórmula con un 75 escrito dentro esconde la regla en cientos de sitios, y la siguiente persona tiene que encontrarlos todos.

Cómo funciona por dentro

Relativa, absoluta, mixta

Un $ delante de una parte de la referencia la congela cuando se copia la fórmula:

• B2 es relativa: la columna y la fila se desplazan con la copia.
• $B$2 es absoluta: ninguna se desplaza. Úsala para una celda de entrada única.
• $B2 es mixta: la columna está congelada y la fila se mueve.
• B$2 es mixta al revés: la fila está congelada y la columna se mueve.

Pulsar F4 con el cursor dentro de una referencia recorre las cuatro formas. En Google Sheets la misma combinación de teclas funciona en la mayoría de los teclados.

Las referencias mixtas llenan un bloque con una sola fórmula

Las referencias mixtas son útiles cuando una cuadrícula tiene una entrada que baja y otra que va a lo ancho. Escribe la fórmula en la celda de la esquina superior izquierda, congela la columna de la entrada vertical y la fila de la entrada horizontal, y rellena todo el bloque.

La prueba para cualquier fórmula que vayas a rellenar: para cada referencia, pregúntate "¿debe seguir a la fila, a la columna, a ambas o a ninguna?" Y coloca los signos de dólar en consecuencia.

Los rangos son argumentos, no sumas

D2:D4 no es un número. Es la descripción de un rectángulo, y la función decide qué hacer con las celdas de dentro. SUM las suma, AVERAGE suma y divide entre la cantidad, MIN y MAX eligen una. Dos detalles causan errores reales. Primero, estas funciones saltan el texto y las celdas vacías, así que AVERAGE divide entre la cantidad de números que encontró, no entre el número de filas. Segundo, un rango queda fijado por sus esquinas: si escribes una línea nueva debajo del rango, el total no la incluirá. Insertar una fila dentro del rango sí lo hace crecer.

IF se evalúa por fila

IF(prueba, valor_si_verdadero, valor_si_falso) es una fórmula como cualquier otra, así que se rellena hacia abajo y cada copia lee su propia fila. Excel evalúa primero la prueba, devuelve uno de los dos valores e ignora el otro.

La prueba puede comparar una celda con un número, con un texto entre comillas o con otra celda. Cuando el umbral pueda cambiar, ponlo en su propia celda y apúntalo con $, igual que la tasa de impuesto. Un número escrito dentro de un IF es fácil de olvidar cuando cambia la regla del negocio.

Dónde cae la fórmula importa

Como las referencias son desplazamientos, cortar y copiar se comportan distinto. Copiar una fórmula conserva sus desplazamientos, así que las referencias se mueven. Cortar y pegar (o arrastrar una celda por su borde) mueve la celda en sí, y Excel reescribe cualquier fórmula que apuntaba a ella para que siga encontrando los mismos datos. Si quieres el mismo texto en otro sitio con los mismos destinos, corta; si quieres el mismo comportamiento en otro sitio, copia.

Cómo comprobar un relleno

No confíes en una columna rellenada solo porque la primera fila se vea bien. Haz clic en la última fila y lee la fórmula en la barra de fórmulas: ¿las referencias siguen apuntando adonde querías? Luego compara el resultado de una fila con una calculadora. Las dos comprobaciones llevan diez segundos y atrapan casi todos los errores de referencia.

Errores comunes

"Rellenar hacia abajo copia la fórmula exactamente como la escribí"

Copia la relación. =B2*C2 pasa a =B3*C3 una fila más abajo. Es lo que quieres para los valores de la propia fila y justo lo que no quieres para una entrada compartida, así que bloquea la entrada compartida con $.

"Si la fórmula devuelve un número, es correcta"

Una referencia que se deslizó una celda hacia un lado también devuelve un número. En el ejemplo del impuesto, la fila 3 lee una celda vacía, la cuenta como cero y devuelve el precio sin impuesto sin ningún aviso. Excel solo muestra un error cuando no puede calcular, no cuando apuntaste a lo equivocado.

"SUM suma todos los números que veo"

SUM suma los números dentro del rango que le diste. Las filas fuera del rango, el texto que parece un número y las celdas con algo escrito con un espacio de más no se cuentan. Si un total sale bajo, revisa primero las esquinas del rango.