Pentland Estates certificó la liquidación anual de Dalegate House en 133.854,70 €. Las cantidades y las tarifas de esa misma hoja valoran esas doce líneas en 138.557,90 €. No había nada roto y no había nada en rojo.
Tres celdas de la columna de totales no eran fórmulas. Se escribieron a mano en julio, durante una llamada con el perito de los inquilinos, y durante tres meses parecieron exactamente iguales que las nueve celdas que seguían calculando a su lado.
Un número escrito a mano y uno calculado son idénticos en pantalla. Misma fuente, misma alineación, mismo formato de número, y los dos suman. La única diferencia es que uno de ellos no vuelve a cambiar nunca.
| Doce líneas de gasto | En el certificado | A las tarifas de al lado |
|---|---|---|
| Total repercutido | 133.854,70 € | 138.557,90 € |
| Las tres celdas escritas | 23.410,00 € | 28.113,20 € |
| Celdas con fórmula | 9 de 12 | 12 de 12 |
| Sin repercutir | 4.703,20 € | 0,00 € |
Alcance.
ESFORMULA,FORMULATEXTO, Ir a Especial, Mostrar fórmulas y las reglas de comprobación de errores, en Excel para Microsoft 365, 2021, 2019 y Excel para Mac.
ESFORMULAyFORMULATEXTOllegaron con Excel 2013. Las formas matriciales deMAXyCOINCIDIRque aparecen abajo necesitan Microsoft 365 o Excel 2021, o Ctrl+Mayús+Entrar en versiones anteriores.En toda la guía, la columna B es la cantidad, la columna C la tarifa y la columna D el total de línea, y las doce líneas van de la fila 2 a la 13.
01Las Tres Celdas Que Dejaron de Moverse
En julio el perito de los inquilinos cuestionó el borrador y el administrador estaba al teléfono. Tres cifras se acordaron de viva voz, así que las escribió directamente en la columna de totales para fijarlas mientras se revisaba el resto.
Nunca volvieron a moverse. En septiembre se cuadraron los partes de los contratistas, las cantidades de la columna B subieron, y nueve de los doce totales de línea subieron con ellas.
La línea de limpieza de cristales es la más clara. La columna B dice 418 bajadas, la columna C dice 27,40 € y la columna D dice 9.590,00 €, que son 350 bajadas a 27,40 €: la cifra acordada en julio.
La hoja no guarda ni rastro de todo eso. La celda tiene un número plausible, con el formato correcto, en la columna correcta, bajo un encabezado que dice Total.
Doce Líneas de Gasto, Tres Congeladas en Julio
La hoja de liquidación de Dalegate House tal como se certificó, en A1:F15. La columna B es la cantidad de los partes de los contratistas después del cuadre de septiembre, la columna C la tarifa acordada y la columna D el total tal como lo llevó el certificado. La columna E es lo que dan la cantidad y la tarifa de esa misma fila: la columna que la hoja no tenía. Las tres filas donde D y E no coinciden son las tres celdas que alguien escribió a mano en julio; las dos filas del final son el certificado emitido frente al certificado a las tarifas de la hoja.
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
02Un Número Escrito Parece una Respuesta
Excel dibuja el resultado de =B2*C2 y los dígitos que alguien ha tecleado exactamente igual. Los dos van alineados a la derecha, los dos toman el formato de la columna y los dos entran en =SUMA(D2:D13).
Hacer clic en la celda y leer la barra de fórmulas es la única forma normal de distinguirlos, y nadie hace clic en doce celdas antes de emitir un certificado.
Ctrl y la tecla de acento grave — la de encima del tabulador — ponen la hoja entera en Mostrar fórmulas. Cada fórmula aparece como su propio texto, y cada celda escrita a mano destaca como un número suelto en una columna de =B2*C2.
Mostrar fórmulas ensancha las columnas mientras está activo y no cambia nada en el fichero. Las mismas teclas devuelven la hoja a su sitio, igual que Fórmulas ▸ Mostrar fórmulas.
Escenario: Abre tu propia hoja de facturación y pulsa Ctrl y el acento grave una vez. Recorre la columna calculada de arriba abajo: todo lo que no sea una fórmula es un número que alguien escribió. Vuelve a pulsarlas para dejar la hoja como estaba.
Pruébalo en la cuadrícula03ESFORMULA Cuenta lo Que No Debería Estar Ahí
=ESFORMULA(D2) devuelve VERDADERO cuando la celda tiene una fórmula y FALSO cuando tiene un valor. Una columna con esa función lee la hoja entera de una vez.
La cuenta es el número que merece quedarse en la hoja. =SUMAPRODUCTO(--NO(ESFORMULA(D2:D13))) devuelve 3, y en una columna que tiene que calcular hasta abajo la única respuesta aceptable es 0.
| Pregunta | Fórmula | En esta hoja |
|---|---|---|
| ¿D2 calcula? | =ESFORMULA(D2) | FALSO |
| ¿Cuántas celdas están escritas? | =SUMAPRODUCTO(--NO(ESFORMULA(D2:D13))) | 3 |
| ¿Cuántas calculan? | =SUMAPRODUCTO(--ESFORMULA(D2:D13)) | 9 |
| ¿Cuál es la primera fila? | =COINCIDIR(FALSO;ESFORMULA(D2:D13);0)+1 | 3 |
Esa última devuelve 3, y la fila 3 es la de limpieza de cristales. En Excel 2019 y anteriores necesita Ctrl+Mayús+Entrar; en el resto es una fórmula corriente.
Escenario: Pon =SUMAPRODUCTO(--NO(ESFORMULA(D2:D13))) en una celda encima de tu columna calculada y déjala ahí. Cualquier respuesta distinta de 0 significa que alguien ha escrito encima de una celda desde la última vez que miraste.
04Ir a Especial las Encuentra de una Tecla
Selecciona la columna, pulsa F5 ▸ Especial ▸ Constantes y Excel selecciona todas las celdas que no son fórmulas. Sin columna auxiliar, sin fórmula que escribir y sin requisito de versión.
Al revés también funciona. F5 ▸ Especial ▸ Fórmulas selecciona las celdas que calculan, que es como se rellenan los huecos de una vez cuando ya sabes dónde están.
Diferencias entre columnas es la versión fina. Con la celda correcta activa, Ctrl y la tecla de la barra invertida — o Inicio ▸ Buscar y seleccionar ▸ Ir a Especial ▸ Diferencias entre columnas — selecciona solo las celdas que rompen el patrón de la columna, teniendo en cuenta las referencias que deben cambiar fila a fila.
Pinta la selección mientras sigue seleccionada. Un relleno sobre las celdas escritas es un mapa que sobrevive a que se pierda la selección, e Inicio ▸ Borrar ▸ Borrar formatos lo quita después.
Constantes incluye texto y fechas. En un bloque mixto selecciona encabezados y etiquetas además de cifras, así que selecciona solo la columna que estás auditando y no la hoja entera.
Las cuatro casillas bajo Constantes — Números, Texto, Lógicos, Errores — la afinan. Números a secas es la que responde a «cuáles de estos totales se escribieron a mano».
Escenario: Selecciona D2:D13 en tu hoja, pulsa F5 ▸ Especial ▸ Constantes con Números marcado y pinta la selección de ámbar. Cada celda ámbar es una cifra que no se moverá hasta que la mueva una persona.
Pruébalo en la cuadrícula05El Aviso Que Excel Ya Había Dado
Excel se dio cuenta en julio. Una celda que rompe el patrón de las de alrededor recibe un triángulo verde en la esquina y el mensaje Fórmula incoherente u Omitir celdas adyacentes.
Ese triángulo se quita con un clic, y Omitir error es permanente para esa celda. No vuelve a aparecer, en ese libro, para nadie que lo abra.
Las reglas viven en Archivo ▸ Opciones ▸ Fórmulas, bajo Reglas de comprobación de errores. Las dos que importan aquí son Fórmulas incoherentes con las demás fórmulas de la región y Fórmulas que omiten celdas de una región, y cualquiera de las dos puede estar desactivada en todo el libro.
Restablecer errores omitidos es el botón que hay que pulsar. Está en ese mismo cuadro, tarda un segundo y devuelve todos los triángulos que alguien haya quitado alguna vez en ese fichero.
Escenario: Abre Archivo ▸ Opciones ▸ Fórmulas en tu libro, confirma que las dos reglas de coherencia están marcadas y pulsa Restablecer errores omitidos. Después recorre la columna calculada buscando esquinas verdes.
Pruébalo en la cuadrícula06Cinco Formas de Que una Fórmula Se Vuelva Número
Nadie borra una fórmula a propósito. Lo hacen cinco gestos corrientes, y cuatro de ellos no dejan ni rastro.
| Cómo pasa | Qué queda detrás |
|---|---|
| Teclear una cifra para comprobar algo | El número se queda, la fórmula no |
| Pegado especial ▸ Valores sobre una celda viva | Un bloque entero de respuestas, congelado |
| Arrastrar una constante por la columna | La misma cifra en todas las filas |
| Pegar desde un correo o un PDF | Texto y números, nunca fórmulas |
Sustituir un #¡REF! por la última cifra buena | Un número plausible en una columna rota |
Pegado especial ▸ Valores es el mayor de los cinco, porque es lo que hace todo el mundo para dejar una hoja presentable antes de mandarla. Está bien hecho sobre una copia y está mal hecho sobre el original.
Escenario: Copia un bloque de celdas calculadas de tu hoja, pégalo encima de sí mismo con Ctrl+Alt+V ▸ Valores y pulsa F5 ▸ Especial ▸ Constantes para ver lo que acabas de hacer. Deshaz, y fíjate en lo poco que cambió la pantalla mientras tanto.
Pruébalo en la cuadrícula07FORMULATEXTO Pone la Fórmula Junto a la Respuesta
=FORMULATEXTO(D2) devuelve la fórmula de D2 como texto legible: =B2*C2. En una celda con un número escrito a mano devuelve #N/D, que es la señal más ruidosa de todo el artículo.
Una columna con esa función al lado de los totales es una auditoría que cualquiera sabe leer. Doce filas que deberían decir de =B2*C2 a =B13*C13, y tres #N/D donde tendría que estar la fórmula.
| Celda | =FORMULATEXTO(D…) | Qué significa |
|---|---|---|
| D2 | =B2*C2 | Calculada desde su propia fila |
| D3 | #N/D | Un número escrito a mano |
| D5 | =B5*12,75 | La tarifa está dentro de la fórmula |
| D9 | =7450 | Una fórmula que es solo una constante |
Envuélvela para que la columna se lea de un tirón: =SI(ESFORMULA(D2);FORMULATEXTO(D2);"ESCRITO: "&TEXTO(D2;"#.##0,00")) nombra el fallo en vez de enseñar un error.
Escenario: Pon =FORMULATEXTO(D2) en una columna libre de tu hoja y arrástrala junto a los totales. Lee la columna una vez y llévatela después a una hoja Auditoría oculta, donde no pueda borrarla nadie que esté dejando la hoja presentable.
08Una Fórmula También Puede Ser un Número Escrito
ESFORMULA responde a «¿hay un signo igual?», no a «¿esta celda se calcula desde la hoja?». Dos celdas de esta hoja pasan esa prueba y son números escritos a mano igualmente.
D9 contiene =7450. Es el tanto alzado de la limpieza de canalones, hoy es correcto, y es una constante con un signo igual delante. Toda auditoría que cuente fórmulas la cuenta como sana.
D5 contiene =B5*12,75. La tarifa de residuos está en la fórmula en vez de en C5, así que la columna C se puede renegociar toda la tarde y la columna D no se moverá.
Un literal dentro de una fórmula es un número escrito con mejor camuflaje. =CONTAR.SI(F2:F13;"*,*") sobre la columna de FORMULATEXTO de la sección 7 cuenta las fórmulas que llevan una coma decimal, que es donde se esconden las tarifas.
Escenario: Pulsa Ctrl+B en tu hoja, escribe una tarifa que te sepas de memoria, pon Buscar dentro de en Fórmulas y pulsa Buscar todos. Cada resultado es una fórmula que lleva dentro un número que debería ser una referencia.
Pruébalo en la cuadrícula09El Total de Control Que Falla en Voz Alta
Las columnas de auditoría acaban borradas. Un total de control es una sola celda, va encima de los datos e informa de un número que debería ser 0.
=REDONDEAR(SUMA(D2:D13)-SUMAPRODUCTO(B2:B13;C2:C13);2) devuelve 4.703,20 € en esta hoja. Donde cada total de línea es la aritmética de su propia fila devuelve 0, y el REDONDEAR evita que un céntimo de coma flotante dé la voz de alarma sin motivo.
Le da igual cómo se estropeó la celda. Una cifra escrita a mano, un pegado viejo, una tarifa enterrada en una fórmula, una referencia que se desplazó una fila: todas salen en el mismo número.
| Control | Fórmula | Esta hoja |
|---|---|---|
| La columna cuadra | =REDONDEAR(SUMA(D2:D13)-SUMAPRODUCTO(B2:B13;C2:C13);2) | 4.703,20 |
| Todas las líneas calculan | =SUMAPRODUCTO(--NO(ESFORMULA(D2:D13))) | 3 |
| No falta nada | =CONTARA(D2:D13) | 12 |
| La peor línea suelta | =MAX(ABS(D2:D13-B2:B13*C2:C13)) | 1.863,20 |
Dale una palabra en vez de un número y nadie tendrá que interpretarlo: =SI(REDONDEAR(SUMA(D2:D13)-SUMAPRODUCTO(B2:B13;C2:C13);2)=0;"Cuadra";"REVISAR").
Escenario: Pon la celda de control encima de tu total, añade un formato condicional que la ponga en rojo cuando no sea 0 y haz que sea lo último que miras antes de que salga el fichero. No emitas nada mientras ponga REVISAR.
Pruébalo en la cuadrícula10Haz Que la Columna Sea Difícil de Sobrescribir
Auditar es lo que se hace cuando no hubo prevención. Tres cosas mantienen calculada una columna calculada.
Ctrl+T convierte el rango en una Tabla, y una columna de Tabla rellena una sola fórmula en todas las filas, incluidas las que se añadan después. Escribe un número en una celda y Excel ofrece restaurar la columna calculada: es un aviso y no una prohibición, pero es un aviso ruidoso.
La protección lo convierte en una negativa. Selecciona las celdas de entrada de las columnas B y C, desmarca Bloqueada en Formato de celdas ▸ Proteger y ve a Revisar ▸ Proteger hoja. La columna de totales rechaza la pulsación sin más.
Dales un sitio a las cifras escritas a mano. Los importes acordados y los ajustes manuales van en su propia columna, con un encabezado que lo diga, para que un número que hay que escribir se escriba donde se esperan números escritos.
Escenario: Convierte tu rango con Ctrl+T, escribe un número en mitad de la columna calculada y lee lo que te ofrece Excel. Deshaz, desbloquea las columnas B y C y protege la hoja.
Pruébalo en la cuadrícula11Seis Comprobaciones Antes de Certificar
- Cuenta las celdas escritas.
=SUMAPRODUCTO(--NO(ESFORMULA(D2:D13)))tiene que devolver 0. - Cuadra la columna contra la aritmética.
=REDONDEAR(SUMA(D2:D13)-SUMAPRODUCTO(B2:B13;C2:C13);2)tiene que devolver 0. - Mira las fórmulas. Ctrl y el acento grave, y recorre la columna calculada.
- Selecciona las constantes. F5 ▸ Especial ▸ Constantes ▸ Números no debería seleccionar nada dentro de D2:D13.
- Caza los literales.
=CONTAR.SI(F2:F13;"*,*")sobre una columna deFORMULATEXTOtiene que devolver 0. - Devuelve los triángulos. Archivo ▸ Opciones ▸ Fórmulas ▸ Restablecer errores omitidos, y busca esquinas verdes.
12Ocho Trampas
- Escribir una cifra «por ahora». No hay después: la celda parece terminada en cuanto pulsas Entrar.
- Pegado especial ▸ Valores sobre el original. Congela un bloque de respuestas y deja la hoja exactamente con el aspecto que tenía.
- Omitir error. Un clic convierte el aviso del propio Excel en historia permanente, para esa celda y para todo el que abra el fichero.
- Una tarifa dentro de una fórmula.
=B5*12,75ignora la columna C para siempre, yESFORMULAdice que la celda está bien. =7450. Un signo igual delante de una constante pasa cualquier recuento de fórmulas y no calcula nada.- Una columna de auditoría que se borra al ordenar. Quédate con el total de control: una celda, encima de los datos, difícil de borrar sin querer.
- Arrastrar una constante por la columna. El controlador de relleno copia lo que haya en la celda, y un número se copia como número.
- Fiarte del total porque cuadra. Las celdas escritas entran en
=SUMA(D2:D13), así que un total que suma no es un total que esté bien.
13Cinco Ejercicios
- Reprodúcelo. Monta doce filas de
=B2*C2, escribe encima de tres y comprueba que=SUMA(D2:D13)se traga las cifras escritas sin rechistar. - Cuéntalas. Pon
=SUMAPRODUCTO(--NO(ESFORMULA(D2:D13)))encima de la columna y comprueba que devuelve 3. - Encuentra la primera.
=COINCIDIR(FALSO;ESFORMULA(D2:D13);0)+1tiene que decirte la primera fila rota antes de ir a buscarla. - Atrapa el literal. Sustituye una fórmula por
=B5*12,75, cambia C5 y mira cómo se mueve el total de control mientras la celda parece intacta. - Protégela. Desbloquea las columnas B y C, protege la hoja e intenta escribir en la columna de totales.
Qué Llevarte
La hoja no estaba rota. Doce líneas, doce cifras plausibles, una columna que cuadraba y un total que nadie tenía motivos para poner en duda.
Lo que faltaba era la aritmética de tres celdas. Alguien escribió tres cifras acordadas en julio, en septiembre se cuadraron las cantidades, y esas tres celdas siguieron informando del verano.
Una columna calculada hay que comprobarla como columna, no leerla como una lista de números. Una cuenta, un total de control, y los dos dicen 0 en una hoja que sigue haciendo sus propias sumas.
Seis segundos de comprobación, frente a 4.703,20 € que el propietario ya no puede repercutir una vez emitido el certificado.