Alguien escribió una fórmula en D2, la comprobó con una calculadora y la rellenó hasta D7. La primera línea estaba bien. Las otras cinco estaban mal, y el pedido salió igualmente.
D2: =B2*C2*(1-F2) → 1568,16 descuento comercial, 12% — correcto
D3: =B3*C3*(1-F3) → 1012,30 descontado por el recargo de transporte
D4: =B4*C4*(1-F4) → 2830,94 descontado por el descuento por pronto pago
D5: =B5*C5*(1-F5) → 988,13 descontado por el recargo de exportación
D6: =B6*C6*(1-F6) → 996,00 descontado por el IVA
D7: =B7*C7*(1-F7) → 4033,50 descontado por una celda vacía, o sea, sin descontar
Ni un valor de error. Ni un triángulo verde. Seis números que parecen dinero, en una columna que suma 11.429,03 cuando la respuesta honesta es 10.625,30. La factura va 803,73 de más — un 7,6% — y cada cifra es internamente coherente, porque F3 contiene de verdad 0,045 y Excel hizo de verdad la multiplicación.
Un solo carácter lo arregla. Todo este artículo va de qué carácter, y de cómo saberlo antes de rellenar en lugar de después del cliente.
Qué cubre esto. Los signos de dólar funcionan igual en todas las versiones de Excel que se han publicado, y en Google Sheets y LibreOffice Calc.
ESFORMULAyFORMULATEXTOde la sección 11 necesitan Excel 2013 o posterior. Las referencias estructuradas de la sección 9 necesitan una Tabla, así que Excel 2007 o posterior. Teclado:F4en Windows,⌘+Ten Mac.
1) Una Referencia Es una Dirección de Movimiento, No una Dirección Postal
Esta es la idea. Todo lo demás del artículo es una consecuencia suya.
Cuando escribes =A1 en la celda C5, Excel no guarda "A1". Guarda "dos columnas a mi izquierda, cuatro filas por encima de mí". El texto A1 solo es cómo se dibuja ese movimiento en pantalla, desde el punto de vista de la celda en la que estás. Copia esa fórmula a C6 y el movimiento no cambia — dos a la izquierda, cuatro arriba — así que lo que se muestra pasa a ser A2. La fórmula no se adaptó. Nunca se movió.
Puedes verlo tú mismo, y merece cinco minutos porque convierte el dólar de superstición en mecanismo. Activa el estilo de referencia F1C1: Archivo → Opciones → Fórmulas → Trabajo con fórmulas → Estilo de referencia F1C1 (en Mac, Excel → Preferencias → General). Los encabezados de columna pasan a ser números y cada fórmula de la hoja se redibuja como un desplazamiento:
Escrito en C5 | Mostrado en F1C1 | Se lee como |
|---|---|---|
=A1 | =F[-4]C[-2] | cuatro filas arriba, dos columnas a la izquierda |
=$A$1 | =F1C1 | fila 1, columna 1 — sin corchetes, sin relatividad |
=A$1 | =F1C[-2] | fila 1 exacta, dos columnas a la izquierda |
=$A1 | =F[-4]C1 | cuatro filas arriba, columna 1 exacta |
Los corchetes son la relatividad. Un dólar en estilo A1 y un corchete que falta en estilo F1C1 son el mismo hecho escrito dos veces. Vuelve a desactivarlo después si te cansa la vista — pero fíjate en que, en F1C1, todas las celdas de una columna rellenada muestran un texto idéntico. Esa es la propiedad que en realidad persigues al rellenar una fórmula hacia abajo, y el estilo A1 te la esconde redibujando cada fila de otra manera.
El Pedido de un Distribuidor, Con los Supuestos Apilados al Lado
Seis líneas de pedido en A2:C7 y un bloque de supuestos en E2:F6 — la forma que acaba teniendo casi cualquier hoja real, porque poner las tasas junto a los números parecía ordenado en su momento. La columna D está vacía a propósito: es la columna que rellena este artículo, dos veces, y la diferencia entre los dos intentos son 803,73 que nadie detectaría leyendo. Encabezado en A1:F1, datos en A2:F7.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
2) Las Cuatro Formas de una Misma Referencia
Una referencia tiene dos mitades — una letra de columna y un número de fila — y cada mitad se puede bloquear por separado. Eso da cuatro formas, y F4 las recorre en este orden:
A1 → $A$1 → A$1 → $A1 → A1 → ...
La regla de lectura es mecánica: el dólar va justo delante de aquello que no debe cambiar. $A significa que la columna sigue siendo A. $1 significa que la fila sigue siendo 1. Nada más sutil que eso.
| Forma | Al rellenar a la derecha | Al rellenar hacia abajo | Úsala para |
|---|---|---|---|
A1 | se mueve | se mueve | los valores de la propia fila — lo predeterminado, y correcto casi siempre |
$A$1 | fija | fija | una constante que comparte toda la hoja: una tasa, un umbral, un tipo de cambio |
A$1 | se mueve | fija | una fila de encabezados o tasas situada encima de un bloque |
$A1 | fija | se mueve | una columna clave situada a la izquierda de un bloque |
Usar F4 bien. Pon el cursor dentro de una referencia en la barra de fórmulas, o justo detrás de ella, y pulsa F4 — no hace falta seleccionarla entera. Púlsalo repetidamente para recorrer las formas; púlsalo una quinta vez y vuelves al principio, que es la forma más barata de deshacer una elección equivocada. Selecciona un trozo del texto de la fórmula con varias referencias dentro y F4 las cambia todas a la vez.
La prueba de dos preguntas, más rápida que acordarse de la tabla. Escribe la fórmula de la celda superior izquierda del bloque y pregúntate, para cada referencia:
- Cuando esta fórmula se mueva una columna a la derecha, ¿debería moverse esta referencia con ella? Si no, pon un
$delante de la letra de columna. - Cuando esta fórmula se mueva una fila hacia abajo, ¿debería moverse esta referencia con ella? Si no, pon un
$delante del número de fila.
Dos respuestas de sí o no por referencia, y los dólares se escriben solos. Las secciones 3 a 6 son esa prueba aplicada a las cuatro situaciones que te vas a encontrar de verdad.
3) Una Constante, Bloqueada por los Dos Lados
🎯 Escenario: Valorar las seis líneas del pedido con el descuento comercial que hay en F2.
La fórmula lee el precio y las unidades de su propia fila, y una celda que comparten todas las filas. Aplica la prueba a cada referencia:
B2(precio) — al rellenar hacia abajo, ¿debe pasar aB3? Sí. Sin dólares.C2(unidades) — ¿debe pasar aC3? Sí. Sin dólares.F2(la tasa) — ¿debe pasar aF3? No. Todas las filas quierenF2. Se bloquean las dos mitades.
D2: =B2*C2*(1-$F$2)
Rellena hasta D7 y la columna da 1568,16, 932,80, 2554,20, 925,06, 1095,60 y 3549,48 — total 10.625,30. Compárala ahora con la versión del principio del artículo, que se diferenciaba en una tecla:
| Fila | =B2*C2*(1-F2) | =B2*C2*(1-$F$2) | Qué usó de verdad la versión suelta |
|---|---|---|---|
| 2 | 1568,16 | 1568,16 | Descuento comercial 0,12 — correcto por casualidad |
| 3 | 1012,30 | 932,80 | Recargo de transporte 0,045 |
| 4 | 2830,94 | 2554,20 | Descuento por pronto pago 0,025 |
| 5 | 988,13 | 925,06 | Recargo de exportación 0,06 |
| 6 | 996,00 | 1095,60 | IVA 0,2 |
| 7 | 4033,50 | 3549,48 | F7 está vacía, así que 1-0 |
Por qué esta es la peligrosa. Un dólar que falta y aterriza sobre celdas vacías te da una multiplicación por cero, y una columna de 0,00 se nota en unos cuatro segundos. Un dólar que falta y aterriza sobre otros supuestos te da dinero verosímil. El bloque de supuestos no es una disposición desafortunada — es la disposición que construye todo el mundo, porque apilar las tasas en una columna ordenada junto a los datos es lo evidente. También son seis balas cargadas justo debajo de la única celda que tu fórmula quería leer.
Dos costumbres que hacen que el fallo sea estructuralmente imposible en vez de improbable:
- Pon los supuestos en su propia hoja, o al menos encima de los datos y no al lado. Una fórmula que se rellena hacia abajo no puede desplazarse hacia un bloque que no está debajo.
- Ponle nombre a la celda.
=B2*C2*(1-DescuentoComercial)no puede perder sus dólares, porque no tiene ninguno — un nombre definido es absoluto por defecto. La sección 9 vuelve sobre esto.
4) Referencias Mixtas: Una Fórmula Que Rellena una Tabla Entera
Esta es la sección que paga las otras once. Las referencias mixtas son la forma de que una fórmula, escrita una vez, rellene un rectángulo.
🎯 Escenario: Construir una matriz de precios de tres niveles — cada SKU en el lateral, cada nivel arriba — sin escribir dieciocho fórmulas.
Pon las tasas de cada nivel en una fila de encabezado a la derecha de la cuadrícula, en H1:J1:
H1: 0,12 Comercial
I1: 0,18 Distribuidor
J1: 0,27 Exportación
Ahora la celda superior izquierda de la matriz, H2. Aplica la prueba dos veces a cada referencia:
B2(el precio de tarifa) — al rellenar a la derecha haciaI2, ¿debería pasar aC2? No,C2son unidades. Bloquea la columna:$B2. Al rellenar hacia abajo aH3, ¿debería pasar aB3? Sí, es el precio del siguiente SKU. Deja la fila suelta.H1(la tasa del nivel) — al rellenar a la derecha haciaI2, ¿debería pasar aI1? Sí, es el siguiente nivel. Deja la columna suelta. Al rellenar hacia abajo aH3, ¿debería pasar aH2? No, las tasas solo viven en la fila 1. Bloquea la fila:H$1.
H2: =$B2*(1-H$1)
Copia H2, selecciona H2:J7, pega. Dieciocho precios a partir de una fórmula:
| SKU | Comercial | Distribuidor | Exportación |
|---|---|---|---|
| FX-100 | 130,68 | 121,77 | 108,41 |
| FX-140 | 186,56 | 173,84 | 154,76 |
| VB-220 | 85,14 | 79,34 | 70,63 |
| VB-260 | 115,63 | 107,75 | 95,92 |
| PS-900 | 365,20 | 340,30 | 302,95 |
| TS-410 | 236,63 | 220,50 | 196,30 |
Las tres formas de equivocarse, y lo alto que grita cada una:
| Escrito como | Qué obtienes | Cómo lo notas |
|---|---|---|
=$B$2*(1-H$1) | Todas las filas muestran los precios de FX-100 | Seis filas idénticas — evidente |
=$B2*(1-$H$1) | Todas las columnas muestran el precio comercial | Tres columnas idénticas — evidente |
=B2*(1-H1) | I2 lee unidades y H3 lee como tasa el precio de arriba | -27.492 en la fila 3 — clarísimo |
Fíjate en que los tres fallos son ruidosos. Bloquear de más y bloquear de menos en una matriz producen disparates visibles, que es la razón por la que las referencias mixtas tienen fama de quisquillosas y no de peligrosas. El fallo de la sección 3 es el silencioso. Lo quisquilloso se sobrevive; lo silencioso no.
El atajo mental, una vez lo has hecho unas cuantas veces: la referencia que apunta a tu columna clave de la izquierda lleva bloqueada la columna ($B2), y la que apunta a tu fila de encabezado de arriba lleva bloqueada la fila (H$1). Los dólares apuntan hacia fuera, hacia los bordes del bloque.
5) Bloquear Solo la Columna: Reglas de Fila Completa
🎯 Escenario: Sombrear la fila entera de cualquier línea de pedido por encima de 2.000, y mostrar cada línea como porcentaje del pedido.
El formato condicional es donde las referencias con la columna bloqueada se ganan el sueldo, y donde más gente lo deja por imposible. Una fórmula de formato condicional se escribe para la celda superior izquierda del rango al que la aplicas, y Excel la traduce después a todas las demás celdas exactamente como si la hubieras rellenado tú. Así que las reglas son las de siempre — pero el "relleno" ocurre de forma invisible.
Selecciona A2:F7 y ve a Inicio → Formato condicional → Nueva regla → Utilice una fórmula:
=$D2>2000
El $ delante de D dice: por muy a la derecha que viaje esta regla — hasta B2, C2, hasta F2 — sigue mirando la columna D. El $ que falta delante del 2 dice: según baje, que mire la fila 3, la 4 y así. Columna bloqueada, fila suelta, y la fila entera se enciende a partir del valor de una sola columna. Se sombrean las filas 4 y 7.
Pon esos dos dólares al revés y el comportamiento es diagnóstico:
| Regla | Resultado |
|---|---|
=$D2>2000 | Se sombrean filas enteras — correcto |
=D2>2000 | Solo se sombrean celdas sueltas por encima de 2.000, repartidas por el bloque |
=$D$2>2000 | O se sombrea todo o no se sombrea nada, según una única celda |
La otra trampa del formato condicional. La fórmula es relativa a la celda superior izquierda del rango aplicado, no de lo que tuvieras seleccionado al abrir el cuadro de diálogo ni de la hoja. Aplica una regla a
A2:F7, escribe=$D3>2000y cada fila estará evaluando la fila de debajo. Si una regla se comporta como si estuviera desplazada una fila, abre Administrar reglas y lee la casilla Se aplica a antes de tocar la fórmula.
Porcentaje del total, que es la misma idea en una fórmula normal — una referencia suelta y un rango bloqueado:
E10: =D2/SUMA($D$2:$D$7) → 14,8% rellenada hacia abajo, el denominador no se mueve
Si escribes =D2/SUMA(D2:D7) y la rellenas hacia abajo, el denominador encoge en cada fila hasta que la última línea es el 100% de sí misma. Es la única columna de porcentajes del mundo cuyas entradas suman más de 200%, y aparece en libros reales constantemente.
6) El Rango Que Se Expande: $B$2:B2
🎯 Escenario: Un total acumulado a lo largo del pedido y un número de secuencia por familia.
Bloquea un extremo de un rango y deja el otro suelto, y el rango crece según se rellena la fórmula. Es el truco más elegante de toda esta materia.
G2: =SUMA($D$2:D2)
En G2 ese rango es D2:D2. Rellenado a G3 pasa a ser D2:D3, luego D2:D4, y para cuando llega a G7 es D2:D7. Una fórmula, un total acumulado:
1568,16 2500,96 5055,16 5980,22 7075,82 10625,30
La misma forma cuenta igual de bien que suma. Numerar cada SKU dentro de su familia de producto — FX, VB, PS, TS — cuesta una fórmula:
=CONTAR.SI($A$2:A2, IZQUIERDA(A2,2) & "*") → 1, 2, 1, 2, 1, 1
La fila 3 es el segundo FX; la fila 5 es el segundo VB. El recuento solo ve las filas que están en la actual o por encima, porque el extremo de abajo del rango está suelto. Intercambia los dólares — =CONTAR.SI(A2:$A$7, ...) — y obtienes una cuenta atrás, que de vez en cuando es exactamente lo que quieres para "cuántos de estos quedan por venir".
El uso clásico de esta forma es marcar duplicados conservando el primero:
=SI(CONTAR.SI($A$2:A2, A2) > 1, "duplicado", "")
CONTAR.SI($A$2:$A$7, A2) > 1 marca todas las copias, incluida la original. La versión que se expande marca solo la segunda y siguientes — que es la diferencia entre "esta lista tiene duplicados" y "estas son las filas que hay que borrar".
7) Copiar, Cortar, Insertar, Eliminar — Cuatro Verbos, Cuatro Comportamientos
Los dólares controlan qué pasa cuando una fórmula se copia. No tienen casi nada que decir sobre los otros tres, y aquí es donde se llevan sorpresas quienes entienden el $ perfectamente.
Copiar traduce. Cortar no. Copia D2 a D3 y las referencias relativas se desplazan una fila. Corta D2 a D3 — o arrástrala por su borde — y la fórmula llega intacta, apuntando todavía a B2 y C2. Cortar mueve una fórmula; copiar la reapunta. Ninguno de los dos está mal, pero "la moví y ahora lee la fila equivocada" y "la moví y ahora lee la fila de siempre" son quejas reales de la misma tarde.
Mover la celda a la que apunta una fórmula reescribe la fórmula — dólares incluidos. Esta es la que pilla a todo el mundo. =B2*C2*(1-$F$2) parece clavada con puntas. Corta F2, pégala en F20, y tu fórmula pasa amablemente a ser =B2*C2*(1-$F$20). El dólar nunca prometió seguir apuntando a la celda F2 — prometió no traducirse al copiar. Excel mantiene las referencias apuntando a los datos, y los seguirá adonde los muevas.
Insertar y eliminar filas también desplaza referencias. Inserta una fila encima de la fila 2 y cada $F$2 del libro pasa discretamente a ser $F$3, que es correcto y deseable. Elimina la fila que contiene F2 y las fórmulas que la referenciaban muestran #¡REF! — el único valor de error que es genuinamente una buena noticia, porque la alternativa habría sido una fórmula reapuntada en silencio.
Rellenar, de cuatro maneras:
| Acción | Qué hace |
|---|---|
| Arrastrar el controlador de relleno | Copia con traducción — el caso normal |
| Doble clic en el controlador de relleno | Rellena hacia abajo hasta donde llegan los datos de la columna contigua |
Ctrl+J / Ctrl+D | Rellenar hacia abajo / hacia la derecha en la selección, con traducción |
Ctrl+' (apóstrofo) | Copia la fórmula de la celda de arriba exactamente, sin traducir |
Esa última es la vía de escape cuando necesitas una copia sin cambios de una fórmula una fila más abajo y no te apetece añadir dólares. La otra vía de escape: selecciona el texto de la fórmula en la barra de fórmulas, cópialo como texto, pulsa Esc y pégalo en la celda destino. El texto no tiene referencias que traducir.
Pegado especial. Ctrl+Alt+V y luego Fórmulas pega la fórmula sin el formato, traduciendo como siempre. Valores pega los resultados y tira las referencias del todo, que es el final adecuado para una columna de trabajo que ya nadie debería recalcular.
8) Otras Hojas, Otros Libros
Las reglas del dólar no cambian al cruzar el borde de una hoja; solo les crece texto por delante.
=Tarifas!$B$2 otra hoja de este mismo libro
='Tarifas T3'!$B$2 comillas obligatorias — el nombre lleva un espacio
=SUMA(Ene:Mar!B2) una referencia 3D a través de tres hojas
='[Precios.xlsx]Comercial'!$B$2 otro libro, mientras está abierto
Tres cosas que merece la pena saber:
Las comillas van por el nombre, no por la referencia. Las comillas simples rodean el nombre de la hoja siempre que contenga un espacio, un guion o empiece por un dígito. Excel las pone por ti cuando haces clic en la celda; no las pone cuando escribes, y por eso =Tarifas T3!B2 devuelve #¿NOMBRE? y por un momento parece una función rota.
Las referencias 3D son relativas en una dimensión a la que los dólares no llegan. =SUMA(Ene:Mar!B2) significa "la celda B2 de todas las hojas desde Ene hasta Mar inclusive", y se define por la posición de las hojas, no por su nombre. Arrastra una hoja nueva en medio y se une a la suma en silencio. Saca una hoja de ahí y se va. No hay $ para el orden de las hojas — si eso importa, enumera las hojas explícitamente: =SUMA(Ene!B2, Feb!B2, Mar!B2).
Los vínculos externos se reescriben solos al cerrarse el otro libro. ='[Precios.xlsx]Comercial'!$B$2 pasa a ser ='C:\Usuarios\...\[Precios.xlsx]Comercial'!$B$2 en cuanto se cierra Precios.xlsx, y el valor se congela en lo último que leyó. Manda ese libro a otra persona y recibirá una ruta que no existe en su máquina, más un aviso de seguridad al abrirlo. Para cualquier cosa que salga de la oficina, pega los valores.
9) Tablas y Nombres: La Misma Idea Sin Dólares
Dos funciones sustituyen los dólares por algo mejor, y las dos tienen su propia regla de bloqueo.
Los nombres definidos son absolutos por defecto. Selecciona F2, escribe DescuentoComercial en el Cuadro de nombres, y el Administrador de nombres lo guarda como =Hoja1!$F$2 — con dólares, los pidieras o no. =B2*C2*(1-DescuentoComercial) se puede rellenar en cualquier sitio del libro y no puede desplazarse. (Excel sí permite nombres relativos, definidos quitando los dólares en la casilla Se refiere a con una celda concreta seleccionada. Funcionan, son ingeniosos y son completamente invisibles para quien mantenga la hoja después de ti.)
Las referencias estructuradas no tienen dólares en absoluto, lo que no significa que no se muevan:
=[@[List Price]] * [@Units] el precio de esta fila por las unidades de esta fila
=SUMA(Pedidos[Line Total]) la columna entera
La @ significa "esta fila", así que hace el trabajo de una referencia de fila relativa y no necesita rellenarse — una fórmula escrita en cualquier punto de una columna de Tabla rellena la columna entera sola, y la sigue rellenando según se añaden filas. Pero copia =SUMA(Pedidos[Line Total]) una columna a la derecha y obtienes =SUMA(Pedidos[Assumption]). Las referencias estructuradas se traducen de lado exactamente igual que las normales.
El bloqueo es una duplicación en lugar de un dólar:
=SUMA(Pedidos[Line Total]) se desplaza al copiar a la derecha
=SUMA(Pedidos[[Line Total]:[Line Total]]) se queda en Line Total
Escribir la columna como un rango de una sola columna — de ella misma a ella misma — es el equivalente en referencias estructuradas de $D$2:$D$7. Parece redundante, y es exactamente eso: la redundancia es de lo que está hecha un ancla.
10) INDIRECTO y DESREF: La Referencia Que Se Niega a Moverse
Hay una forma de construir una referencia que de verdad no puede ser reapuntada por nada: construirla con texto.
=INDIRECTO("F2") siempre la celda F2, pase lo que pase en la hoja
Inserta diez filas encima, corta F2 y llévatela a otro continente, elimina la columna — INDIRECTO seguirá leyendo lo que ahora haya en la celda llamada F2. Casi siempre esto es un error disfrazado de solución, porque lo que querías era la tasa, y la tasa se fue a F12 cuando alguien añadió filas. $F$2 la habría seguido. INDIRECTO("F2") lee un nombre de cliente y multiplica por él.
Tres costes más, en el orden en que duelen:
- Es volátil. Cada
INDIRECTOy cadaDESREFse recalcula ante cualquier cambio en cualquier punto del libro, junto con todo lo que dependa de ellos. Unos cuantos cientos son una hoja que se queda pensando mientras escribes. - No ve un libro cerrado.
INDIRECTOapuntando a otro archivo devuelve#¡REF!en cuanto ese archivo se cierra. Un vínculo externo normal conserva su último valor. - Se esconde de la auditoría. Rastrear precedentes no dibuja ninguna flecha desde un
INDIRECTO, y nada más lo hace. La dependencia existe, pero nada en Excel puede verla.
Úsalo cuando la referencia tenga que montarse de verdad en tiempo de ejecución — un nombre de hoja elegido en una lista desplegable, por ejemplo, con =INDIRECTO("'" & $A$1 & "'!B2") — y tira de INDICE en lugar de DESREF siempre que necesites un rango móvil, porque INDICE devuelve una referencia sin ser volátil:
=SUMA($D$2:INDICE($D:$D, $H$1)) suma desde D2 hasta la fila indicada en H1 — no volátil
11) Encontrar el Dólar Que Falta Antes de Que lo Encuentre Otro
🎯 Escenario: Te llega de un compañero una columna con 400 fórmulas. Demuestra que es una fórmula y no 399 fórmulas y una sorpresa.
Cuatro comprobaciones, de la más barata a la más cara.
Muestra las fórmulas. Ctrl + ` (la tilde grave, arriba a la izquierda en casi todos los teclados) cambia la hoja de resultados a fórmulas. Una columna bien rellenada muestra la misma forma 400 veces, y un número escrito a mano en mitad de ella salta a la vista porque es la única entrada sin =. Púlsalo otra vez para volver.
Selecciona las que desentonan. Selecciona la columna y ve a Inicio → Buscar y seleccionar → Ir a Especial → Diferencias entre columnas (Ctrl+Mayús+\). Excel compara cada celda de la columna con la celda activa, teniendo en cuenta las referencias relativas coherentes, y selecciona solo las celdas que rompen el patrón. En un relleno limpio no selecciona nada. En una columna en la que alguien "arregló solo una fila", te lleva directo a ella. Ctrl+\ hace lo mismo a lo largo de una fila.
Deja que Excel te avise. Fórmulas → Comprobación de errores → Opciones → Fórmulas incoherentes con otras fórmulas de la región está activado por defecto y pone un triangulito verde en la celda infractora. Es genuinamente útil y genuinamente fácil de desactivar por hartazgo hace años y no volver a activar. Comprueba que está puesto.
Cuenta los fallos en una celda. ESFORMULA y FORMULATEXTO convierten la auditoría en aritmética:
=CONTAR(D2:D7) - SUMAPRODUCTO(--ESFORMULA(D2:D7)) números escritos a mano en la columna
=SUMAPRODUCTO(--ESERROR(ENCONTRAR("$", FORMULATEXTO(D2:D7)))) fórmulas sin ninguna referencia absoluta
=FORMULATEXTO(D2) la fórmula como texto, para leerla
La primera devuelve 0 en una columna que sea toda fórmulas. La segunda cuenta cada celda cuya fórmula no contiene ningún $ — que, en una columna que se supone que referencia un supuesto, es el recuento de tus errores. También cuenta las celdas vacías y las que no son fórmulas, ya que FORMULATEXTO devuelve #N/D para esas; eso es una virtud, porque esas también son fallos.
Y una costumbre que gana a las cuatro: da color a tus celdas de entrada. Una regla de formato condicional con =ESFORMULA(A1) aplicada a toda la hoja, con un relleno pálido, hace que toda constante de la hoja sea una celda sin relleno. Encuentras el 0,12 escrito a mano que debería haber sido $F$2 mirando la hoja desde el otro lado de la sala.
12) Mini Ejercicios
Copia la cuadrícula en una hoja en blanco empezando en A1. Cada respuesta es una sola fórmula.
- La columna honesta. Escribe la fórmula de
D2que valora la línea con el descuento comercial y sobrevive a ser rellenada hastaD7. Di qué devuelveD7. - La matriz. Pon
0,12,0,18y0,27enH1:J1. Escribe la única fórmula deH2que rellenaH2:J7y di en una línea qué mitad de qué referencia protege cada dólar. - Dos errores. Escribe la misma fórmula de la matriz con las dos mitades de la referencia al precio bloqueadas, y luego con las dos mitades de la referencia a la tasa bloqueadas. Describe el síntoma visible de cada una sin construirlas.
- Peso en el pedido. Escribe la fórmula del porcentaje que representa cada línea sobre el pedido entero, correcta al rellenarla de la fila 2 a la 7. Después di qué devolvería la última fila si te olvidaras de los dólares.
- Acumulado, al revés.
=SUMA($D$2:D2)acumula de arriba abajo. Escribe la versión que acumula desde cada fila hasta el final y di qué referencia has tenido que intercambiar. - La inamovible.
$F$2eINDIRECTO("F2")apuntan las dos aF2. Inserta una fila encima de la fila 2 y describe, en una frase cada una, a qué apuntan ahora las dos fórmulas — y cuál era la que querías.
Resumen
Una referencia es una dirección de movimiento. Rellenar una fórmula hacia abajo es repetir ese movimiento, y un dólar es la única instrucción que dice esta parte no.
Tres cosas se llevan casi todo. Aplica la prueba de dos preguntas a cada referencia de la celda superior izquierda antes de rellenar nada: ¿se mueve a la derecha?, ¿se mueve hacia abajo? Vigila específicamente el fallo silencioso — una tasa sin bloquear que aterriza en celdas vacías produce ceros y se detecta, mientras que una tasa sin bloquear que aterriza sobre otras tasas produce dinero y no se detecta. Y recuerda que los dólares solo gobiernan la copia: cortar la celda referenciada, insertar una fila o eliminar una columna reapuntará tu referencia absoluta sin preguntar, porque Excel sigue a los datos y no a la dirección.
El pedido del principio de este artículo salió con 803,73 de más por dos caracteres, y todas sus fórmulas funcionaban perfectamente.