01 / 14Las Tres Celdas Que Dejaron de Moverse

Auditoría de FórmulasExcelCalidad de Datos

Cómo detectar valores fijos en fórmulas de Excel

La Liquidación Se Quedó Corta en 4.703,20 € y el Certificado Ya Estaba Emitido, Porque Tres Totales de Línea Eran Números Escritos a Mano

22 oct 202614 min de lectura

UsaSUMAPRODUCTOCONTAR, CONTARAREDONDEARSUMA

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 gastoEn el certificadoA las tarifas de al lado
Total repercutido133.854,70 €138.557,90 €
Las tres celdas escritas23.410,00 €28.113,20 €
Celdas con fórmula9 de 1212 de 12
Sin repercutir4.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.

ESFORMULA y FORMULATEXTO llegaron con Excel 2013. Las formas matriciales de MAX y COINCIDIR que 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.

ABCDEF
1
Service line
Qty
Rate
Certified
At the rates
What happened
2
Cleaning, common parts
1842
18.6
34261.2
34261.2
The biggest line on the schedule and one of the nine that calculated all year. The hours rose from 1,790 to 1,842 when the logs were trued up in September, and the total rose with them without anybody touching it.
3
Window cleaning
418
27.4
9590
11453.2
Typed in July during the call: 350 drops at £27.40. The logs show 418 drops, the cell still shows the July figure, and the £1,863.20 between them is the largest single error on the schedule.
4
Entrance mat hire
86
64
5504
5504
Eighty-six weeks of hire across two entrances, calculated from its own row. Nothing has ever gone wrong with this line, which is why it is useful: it is what the other eleven are supposed to look like.
5
Waste collection
2262
12.75
28840.5
28840.5
The number is right and the formula is not: D5 holds =B5*12.75, with the rate inside the formula rather than in C5. Nothing is wrong today, and nothing will change in column D the day the rate is renegotiated.
6
Consumables
1840
4.95
7920
9108
The second cell typed in July, at 1,600 units. The true count is 1,840, so the schedule under-recovered £1,188.00 on soap, towels and bin liners, and the cell looked identical to the ones around it.
7
Planted displays
984
8.4
8265.6
8265.6
Calculated from its own row, start to finish. Quantities moved twice during the year and the total moved with them both times.
8
Pest control
310
21.5
6665
6665
Calculated. A quiet line that nobody has ever had a reason to type over, which is the only thing keeping it right.
9
Gutter clearance
1
7450
7450
7450
A lump sum, and the third shape of the problem: D9 holds =7450. ISFORMULA returns TRUE because there is an equals sign, FORMULATEXT shows the constant, and the cell calculates nothing at all.
10
Lift maintenance
64
118
5900
7552
The third cell typed in July, at 50 visits of £118.00. Sixty-four visits were logged, the cell never moved, and £1,652.00 of a line that the tenants would have paid without query went unrecovered.
11
External sweeping
2262
3.2
7238.4
7238.4
Calculated from the same area figure as the waste line, and one of the nine that followed the September true-up without anybody noticing it had.
12
Site supervision
18
395
7110
7110
Eighteen days at the agreed daily rate, calculated. One of the three lines the surveyor queried in July, and the only one of the three that was left as a formula.
13
Fire alarm testing
140
36.5
5110
5110
Calculated. The last line of the schedule, and the one most likely to be typed over next: it sits directly above the total, where people experiment.
14
Certificate as issued
12 lines
133854.7
138557.9
What =SUM(D2:D13) added up on the day the certificate was issued, against what the same twelve lines are worth at the quantities and rates printed beside them. Three cells hold the whole of the difference.
15
The three typed cells
3 of 12
23410
28113.2
The three line totals that are numbers rather than formulas, added up on their own: £23,410.00 as typed against £28,113.20 at the schedule's own rates, and £4,703.20 of service charge between them.

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ícula

03ESFORMULA 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.

PreguntaFórmulaEn 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)+13

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.

Pruébalo en la cuadrícula

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ícula

05El 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ícula

06Cinco 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 pasaQué queda detrás
Teclear una cifra para comprobar algoEl número se queda, la fórmula no
Pegado especial ▸ Valores sobre una celda vivaUn bloque entero de respuestas, congelado
Arrastrar una constante por la columnaLa misma cifra en todas las filas
Pegar desde un correo o un PDFTexto y números, nunca fórmulas
Sustituir un #¡REF! por la última cifra buenaUn 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ícula

07FORMULATEXTO 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*C2Calculada desde su propia fila
D3#N/DUn número escrito a mano
D5=B5*12,75La tarifa está dentro de la fórmula
D9=7450Una 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.

Pruébalo en la cuadrícula

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ícula

09El 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.

ControlFórmulaEsta 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ícula

10Haz 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ícula

11Seis Comprobaciones Antes de Certificar

  1. Cuenta las celdas escritas. =SUMAPRODUCTO(--NO(ESFORMULA(D2:D13))) tiene que devolver 0.
  2. Cuadra la columna contra la aritmética. =REDONDEAR(SUMA(D2:D13)-SUMAPRODUCTO(B2:B13;C2:C13);2) tiene que devolver 0.
  3. Mira las fórmulas. Ctrl y el acento grave, y recorre la columna calculada.
  4. Selecciona las constantes. F5 ▸ Especial ▸ Constantes ▸ Números no debería seleccionar nada dentro de D2:D13.
  5. Caza los literales. =CONTAR.SI(F2:F13;"*,*") sobre una columna de FORMULATEXTO tiene que devolver 0.
  6. Devuelve los triángulos. Archivo ▸ Opciones ▸ Fórmulas ▸ Restablecer errores omitidos, y busca esquinas verdes.

12Ocho Trampas

  1. Escribir una cifra «por ahora». No hay después: la celda parece terminada en cuanto pulsas Entrar.
  2. Pegado especial ▸ Valores sobre el original. Congela un bloque de respuestas y deja la hoja exactamente con el aspecto que tenía.
  3. Omitir error. Un clic convierte el aviso del propio Excel en historia permanente, para esa celda y para todo el que abra el fichero.
  4. Una tarifa dentro de una fórmula. =B5*12,75 ignora la columna C para siempre, y ESFORMULA dice que la celda está bien.
  5. =7450. Un signo igual delante de una constante pasa cualquier recuento de fórmulas y no calcula nada.
  6. 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.
  7. 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.
  8. 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

  1. 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.
  2. Cuéntalas. Pon =SUMAPRODUCTO(--NO(ESFORMULA(D2:D13))) encima de la columna y comprueba que devuelve 3.
  3. Encuentra la primera. =COINCIDIR(FALSO;ESFORMULA(D2:D13);0)+1 tiene que decirte la primera fila rota antes de ir a buscarla.
  4. 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.
  5. 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.

Comparte este artículo:
Volver al Blog
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy