Hadleigh Homes marcó nueve parcelas en un registro de remates. La certificación reclamó seis. Las tres que faltaron estaban marcadas en pantalla, en una columna donde todos los tics se ven igual.
Larkfield Rise es una promoción de doce parcelas. Antes de entregar una parcela hay que ejecutar y firmar su lista de remates, y el registro es una hoja: parcela, gremio, coste del repaso y una casilla que marca el jefe de obra.
El 29 de septiembre el aparejador preparó la certificación del mes con una sola fórmula.
=SUMAR.SI.CONJUNTO(C2:C13;D2:D13;VERDADERO) → 6.940,75
Había nueve casillas marcadas. El total que devolvió cubre seis.
Tres de esos tics no son tics. Las parcelas 18, 22 y 29 contienen la palabra VERDADERO, pegada desde la exportación de la aplicación de obra, y una palabra es texto. El texto que se lee VERDADERO no es el valor lógico VERDADERO, y nada en la hoja trata igual a los dos.
| Lo que se le preguntó a la hoja | Fórmula | Respuesta |
|---|---|---|
| Sumar la columna de tics | =SUMA(D2:D13) | 0 |
| Contar las entradas | =CONTARA(D2:D13) | 12 |
| Contar los tics | =CONTAR.SI(D2:D13;VERDADERO) | 6 |
| Contar las casillas vacías | =CONTAR.SI(D2:D13;FALSO) | 3 |
| Gastar los tics | =SUMAR.SI.CONJUNTO(C2:C13;D2:D13;VERDADERO) | 6.940,75 £ |
Ese mes se habían firmado 16.736,50 £ de repasos. A la certificación llegaron 6.940,75 £ y se quedaron fuera 9.795,75 £.
La certificación se firmó el 30 de septiembre. El agujero apareció el 21 de octubre, con tres semanas del siguiente ciclo de pago ya consumidas.
Para entonces, lo único que se podía hacer con 9.795,75 £ de obra terminada y sin discutir era volver a reclamarla al mes siguiente.
Nadie revisó la columna de tics, porque en pantalla no había nada que revisar. Nueve tics en una columna, tres de ellos alineados a la izquierda para quien supiera que la alineación significa algo.
01Nueve Tics, Seis Que Cuentan
Doce Parcelas, Nueve Tics en Pantalla y Seis Que una Fórmula Puede Contar
El registro tiene una fila por parcela en una promoción de doce: la parcela, el gremio que ejecutó el repaso de remates, lo que costó ese repaso y una columna de firma que el jefe de obra marca antes de poder certificar la parcela. La columna D es lo que todo el mundo lee y la columna E es lo que guardan de verdad las celdas, que es la mitad que nadie ve: nueve de las doce filas se leen VERDADERO, pero solo seis contienen el valor lógico VERDADERO. Las otras tres — las parcelas 18, 22 y 29 — contienen la palabra, pegada desde la exportación de la aplicación de obra, y valen 9.795,75 £ entre las tres. Todos los importes están en libras. Las doce parcelas suman 31.972,00 £, las nueve firmadas suman 16.736,50 £ y las seis que un criterio VERDADERO puede emparejar suman 6.940,75 £, que es la cifra que entró en la certificación.
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
La columna D es el registro tal y como lo lee todo el mundo. La columna E es lo que guardan las celdas, que es la mitad que nadie ve. Las filas que se diferencian en la columna E son idénticas en la columna D, y la columna D es toda la interfaz.
Las tres filas de texto tampoco están repartidas al azar. Son las tres parcelas que exportó la aplicación de obra, así que el fallo llegó en un solo pegado, una sola tarde, y volverá a llegar en el siguiente.
Escenario: Abre una hoja tuya con una columna de tics, banderas o Sí/No. Pon debajo =CONTARA(D2:D13)-CONTAR.SI(D2:D13;VERDADERO)-CONTAR.SI(D2:D13;FALSO). Cualquier cosa distinta de 0 es el número de entradas que no son valores lógicos.
02Una Casilla Es un Valor, No un Objeto
Insertar ▸ Casilla pone la casilla dentro de la celda. El valor de la celda es el VERDADERO o el FALSO lógico, y la casilla es un formato dibujado sobre ese valor — la misma relación que tiene un formato de moneda con un número.
Al hacer clic se alterna el valor. También con la barra espaciadora sobre un rango seleccionado, que marca o desmarca una columna entera de una tecla.
Lo demás es una celda normal: se ordena con su fila, se filtra con su fila y cualquier fórmula puede leerla.
El control antiguo es otra cosa. Programador ▸ Insertar ▸ Casilla dibuja un control de formulario que flota sobre la cuadrícula con un vínculo a alguna celda. Ordena las filas y los valores se mueven mientras las casillas se quedan donde estaban.
Esa es la única razón para preferir la casilla en celda en cualquier lista que algún día se vaya a ordenar o filtrar: el tic y la fila no se pueden separar.
Qué cubre esto.
SUMA,CONTAR,CONTARA,CONTAR.SI,CONTAR.SI.CONJUNTO,SUMAR.SI.CONJUNTO,SUMAPRODUCTO,ESTEXTO,SI,Y,ESPACIOSyMAYUSCfuncionan en todas las versiones de este siglo.Las casillas en celda son nuevas: Insertar ▸ Casilla llegó a Excel para Microsoft 365 y a Excel en la web en 2024, y no está en Excel 2021 ni anteriores.
FILTRAR,BUSCARXyLETpiden Microsoft 365 o Excel 2021.Todo lo demás va de valores lógicos, que llevan cuarenta años comportándose así. Una columna donde alguien escribió VERDADERO y FALSO a mano se comporta igual que una columna de casillas, y todas las fórmulas de abajo valen para las dos.
Escenario: Coge una lista tuya, selecciona una columna vacía al lado y usa Insertar ▸ Casilla. Marca tres filas, pon =CONTAR.SI(E2:E20;VERDADERO) debajo de la columna y mira cómo se mueve el número según haces clic.
03SUMA Sobre una Columna de Tics Siempre Da 0
=SUMA(D2:D13) devuelve 0 en una columna con seis tics. No es un error, ni un aviso, ni un triángulo verde: es cero. Los valores lógicos dentro de una referencia los ignoran SUMA, PROMEDIO, CONTAR, MIN y MAX.
Escritos directamente en la fórmula sí cuentan. =SUMA(VERDADERO;VERDADERO) es 2, porque un argumento que escribes tú se toma tal cual. Leídos de celdas, esos mismos dos valores se saltan.
Así que la regla va de dónde viene el valor, no de lo que es. =CONTAR(D2:D13) devuelve 0 por lo mismo: cuenta números, y un valor lógico no lo es.
=CONTARA(D2:D13) devuelve 12, porque cuenta toda celda que no esté vacía, marcada o sin marcar. Las dos funciones a las que recurre cualquiera primero responden 0 y 12 en una columna cuya respuesta honrada es 6. Ninguna se equivoca; a ninguna le hicieron la pregunta.
04CONTAR.SI Cuenta los Tics, SUMAR.SI.CONJUNTO los Gasta
=CONTAR.SI(D2:D13;VERDADERO) → 6
=CONTAR.SI(D2:D13;FALSO) → 3
=SUMAR.SI.CONJUNTO(C2:C13;D2:D13;VERDADERO) → 6.940,75
=CONTAR.SI.CONJUNTO(D2:D13;VERDADERO;B2:B13;"Electricidad") → 2
Un criterio VERDADERO sin comillas alrededor es el valor lógico, y el valor lógico es lo que contiene una casilla marcada. Esa fórmula es todo lo que hay que saber para contar casillas.
SUMAR.SI.CONJUNTO acepta el mismo criterio, así que una columna de costes y una de tics te dan en una sola celda el dinero que hay detrás de los tics. Aquí son 6.940,75 £: aritmética correcta sobre las seis filas que es capaz de ver.
El número está bien y el registro está mal, que es el error más difícil de encontrar. Nada falla, nada está en blanco y la respuesta es una fracción verosímil de un total que nadie calculó.
Los porcentajes lo heredan. =CONTAR.SI(D2:D13;VERDADERO)/CONTARA(D2:D13) informa de un 50% completado en un registro que cualquiera ve marcado en tres cuartas partes.
Escenario: Pon el recuento de la fórmula y el del ojo uno al lado del otro en tu registro: =CONTAR.SI(D2:D13;VERDADERO) en una celda y los tics que ves en la siguiente. Si no coinciden, la diferencia es texto.
05De Dónde Sale la Palabra VERDADERO
Nadie la escribe a propósito. Llega por tres vías, y las tres se parecen exactamente a un tic.
Una fórmula con comillas. =SI(E2>=F2;"VERDADERO";"FALSO") escribe texto siempre, porque todo lo que va entre comillas es texto. La versión sin ellas, =SI(E2>=F2;VERDADERO;FALSO), escribe valores lógicos — y =E2>=F2 sola escribe lo mismo tecleando menos.
Un pegado. Escribe verdadero en una celda y Excel lo interpreta como el VERDADERO lógico. Pega esas mismas letras desde una página web, una aplicación o un informe y pueden aterrizar como texto, porque un pegado no es una entrada tecleada y nada vuelve a leerlo.
Una columna con formato de Texto. Da formato de Texto a una columna — habitual en registros nacidos de una importación, para que no se estropee una referencia de parcela — y todo lo que se escriba después se queda en texto, la palabra VERDADERO incluida.
Escenario: Haz clic en uno de los tics de tu registro y mira la alineación. Los valores lógicos se centran solos; el texto se queda a la izquierda salvo que alguien lo haya cambiado. Luego escribe =ESTEXTO(D5) al lado de la fila sospechosa.
06Las Fórmulas Que Se Niegan a Disimular
Tres fórmulas de esta hoja devuelven un error en vez de un número, y las tres te están haciendo un favor.
=SUMAPRODUCTO(--D2:D13) → #¡VALOR! (6 en una columna limpia)
=FILTRAR(A2:A13;D2:D13) → #¡VALOR!
=SI(D5;"Firmada";"Abierta") → #¡VALOR! en una fila con la palabra
El doble signo menos -- convierte VERDADERO en 1 y FALSO en 0, que es la forma más antigua de contar tics y todavía la más corta. No puede convertir una palabra en número, así que se para en vez de adivinar.
FILTRAR quiere valores lógicos en su argumento de inclusión y una palabra no lo es. SI quiere una condición que pueda leer como cierta o falsa, y "VERDADERO" no es ninguna de las dos cosas.
Los errores son la mitad honrada de la hoja. CONTAR.SI y SUMAR.SI.CONJUNTO se saltan lo que no saben emparejar y devuelven un número; estas tres lo dicen en voz alta.
Una celda con #¡VALOR! ya te ha contado más que una certificación que pone 6.940,75 £. Taparlo es lo único que no hay que hacer: =SI.ERROR(SUMAPRODUCTO(--D2:D13);0) convierte una pregunta en un cero, y el cero es sobre lo que construirá el siguiente.
07Tres Comprobaciones de una Celda Cada Una
La comprobación aritmética. Entradas, menos los tics, menos las casillas vacías:
=CONTARA(D2:D13)-CONTAR.SI(D2:D13;VERDADERO)-CONTAR.SI(D2:D13;FALSO) → 3
En una columna limpia da 0. Aquí da 3, y 3 es el número de celdas que contienen algo que no es un valor lógico.
La comprobación de tipo. =SUMAPRODUCTO(--ESTEXTO(D2:D13)) devuelve también 3. Cuenta las celdas con texto diga lo que diga el texto, así que pilla Sí, S y un espacio suelto al final junto con la palabra VERDADERO.
La ordenación. Ordena la columna de A a Z. Excel coloca primero los números, luego el texto, luego FALSO y por último VERDADERO — así que los tics de texto aterrizan por encima de todos los de verdad, y tres celdas idénticas se separan solas de un clic. Después vuelve a ordenar por la columna de parcela.
Una cuarta, de regalo: abre el desplegable de filtro de la columna. Una columna con los dos tipos de tic te ofrece VERDADERO dos veces.
Escenario: Pon la comprobación aritmética debajo de cada columna de tics, banderas o Sí/No del libro en el que más confíes. Es una celda por columna, cuesta un minuto y la respuesta debería ser 0 en todas.
Pruébalo en la cuadrícula08Convertir las Palabras Otra Vez en Tics
Una columna auxiliar lo repara sin adivinar nada:
=SI(ESTEXTO(D2);MAYUSC(ESPACIOS(D2))="VERDADERO";D2)
ESTEXTO pregunta si esta es una de las celdas malas. Si lo es, ESPACIOS quita los espacios que deja una exportación, MAYUSC iguala verdadero y Verdadero, y la comparación devuelve un valor lógico de verdad. Si no lo es, el valor original pasa intacto.
Copia la columna auxiliar, haz Pegado especial ▸ Valores encima de la original y aplica Insertar ▸ Casilla a la columna. Ahora cada fila contiene un valor lógico y cada fila dibuja una casilla.
Demuéstralo antes de borrar la auxiliar. =SUMAPRODUCTO(--ESTEXTO(D2:D13)) debe devolver 0 y =CONTAR.SI(D2:D13;VERDADERO) debe devolver ya 9 — el número que el registro llevaba afirmando desde el principio.
Texto en columnas ▸ Finalizar merece un intento sobre una copia de la columna, porque vuelve a interpretar las entradas como lo hace teclear. Vayas por donde vayas, termina en esos dos recuentos: una reparación que no has contado es una esperanza.
Escenario: Sobre una copia de tu registro, añade la columna auxiliar, pega sus valores encima de la columna de tics y ejecuta las dos pruebas. Guarda la fórmula en una hoja de notas: la próxima exportación la va a volver a necesitar.
Pruébalo en la cuadrícula09Filas de Totales, Dinámicas y Búsquedas
Una columna de tics dentro de una tabla de Excel se porta bien, con una excepción. La Suma de la fila de totales devuelve 0, porque es un SUBTOTALES y SUBTOTALES ignora los valores lógicos igual que SUMA. Elige Cuenta o escribe tú el SUMAR.SI.CONJUNTO.
Una tabla dinámica trata la columna como no numérica y ofrece Cuenta, que cuenta por igual las filas marcadas y las vacías. Filtra el campo a VERDADERO, o añade una columna auxiliar numérica con =--D2 y suma esa.
Llevarse un tic a otra hoja es una búsqueda normal:
=BUSCARX(A2;Registro!$A$2:$A$13;Registro!$D$2:$D$13;FALSO)
El cuarto argumento es una decisión, no un valor por defecto: una parcela que no está en el registro se informa como no firmada. Si faltar debe sonar más fuerte que estar sin marcar, devuelve "NO ESTÁ EN EL REGISTRO" y deja que la hoja de entrega lo enseñe.
Dos celdas más que merece la pena guardar. =PROMEDIO(--D2:D13) es la fracción firmada — 0,75 con el registro ya limpio, y #¡VALOR! mientras quede una palabra dentro. Y una barrera para el expediente:
=Y(CONTAR.SI(D2:D13;FALSO)=0;SUMAPRODUCTO(--ESTEXTO(D2:D13))=0)
Eso es VERDADERO solo cuando todas las parcelas están firmadas y todos los tics son tics. Un informe incapaz de engañarte está a un LET de distancia:
=LET(tics;D2:D13;palabras;SUMAPRODUCTO(--ESTEXTO(tics));
SI(palabras>0;"REVISAR "&palabras&" TICS DE TEXTO";SUMAR.SI.CONJUNTO(C2:C13;tics;VERDADERO)))
10Ocho Cosas Que Muerden
=SUMAsobre una columna de tics. Devuelve 0 con seis tics dentro, y 0 es un número que un informe imprime tan contento.=CONTARAcomo recuento de tics. Cuenta también las casillas sin marcar: 12 en un registro con 9 tics.=SI(E2>=F2;"VERDADERO";"FALSO"). Las comillas hacen texto, y ese texto no coincide con ningún criterio VERDADERO del libro.- Una columna de tics con formato de Texto. Todo lo que se escriba se queda en palabra, así que la columna se llena de impostores fila a fila.
- Casillas de control de formulario en una lista que ordenas. Los valores se mueven con las filas y las casillas no, y ya nada dice qué casilla es de qué parcela.
SI.ERRORalrededor de una conversión.--yFILTRARfallan por algo; taparlos con un 0 tira ese algo y se queda el número.- La fila de totales de una tabla puesta en Suma.
SUBTOTALESignora los valores lógicos igual queSUMA, así que el total bajo una columna marcada pone 0. - Contar los tics a ojo. Nueve en pantalla, seis en la fórmula, y la diferencia solo aparece en un número que nadie pidió.
11Mini Ejercicios
- Construye los tres totales del registro en tres celdas: todo,
=SUMA(C2:C13); todo lo marcado, a mano; y todo lo que alcanza un criterio VERDADERO,=SUMAR.SI.CONJUNTO(C2:C13;D2:D13;VERDADERO). Espera 31.972,00 £, 16.736,50 £ y 6.940,75 £. - Pon
=CONTARA(D2:D13)-CONTAR.SI(D2:D13;VERDADERO)-CONTAR.SI(D2:D13;FALSO)bajo la columna de tics, vuelve a escribir una de las tres celdas malas como tic de verdad y mira cómo la comprobación baja a 2. - Escribe
=CONTAR.SI.CONJUNTO(D2:D13;VERDADERO;B2:B13;"Electricidad")y después cuenta a ojo las firmas de Electricidad. La diferencia es una parcela y 4.120,00 £. - Repara la columna con la fórmula auxiliar, pega los valores encima y demuestra la reparación dos veces:
=SUMAPRODUCTO(--ESTEXTO(D2:D13))en 0 y=CONTAR.SI(D2:D13;VERDADERO)en 9.
Lo Que Hay Que Llevarse
Una casilla es un valor lógico disfrazado de cuadrito. Todo lo raro que tiene contar casillas es simplemente cómo Excel ha tratado siempre a VERDADERO y FALSO: ignorarlos dentro de una referencia y emparejarlos solo contra un criterio del mismo tipo.
Así que cuéntalos con CONTAR.SI, gástalos con SUMAR.SI.CONJUNTO, conviértelos con -- cuando quieras aritmética y no recurras nunca a SUMA. Esas cuatro costumbres cubren cualquier columna de tics que te vayas a encontrar.
Y comprueba el tipo, no lo que se ve. La palabra VERDADERO y el valor VERDADERO se ven idénticos, caen en la misma columna y solo se diferencian en lo que una fórmula puede hacer con ellos — que es la diferencia entre 16.736,50 £ y 6.940,75 £ en una certificación que nadie tenía motivo para dudar.