01 / 14La Semana en Que el Color Era el Único Control

Color de CeldaFormato CondicionalCuentas a Pagar

Sumar y contar celdas por color en Excel

La Remesa Pagó 18.640 € a Proveedores con Incidencia y Retuvo 9.430 € al Proveedor Que Esperaba, Porque No Pagar Era un Color de Relleno

Calderstone Joinery marca en ámbar la factura en disputa y paga todo lo demás. El jueves la remesa pagó 40.917,80 € donde tocaba pagar 31.707,80 €. Todos los importes de la hoja eran correctos.

Dos facturas con incidencia salieron juntas: 18.640,00 € entre las dos. Una tercera que nadie había cuestionado se quedó atrás, y el proveedor bloqueó la cuenta por 9.430,00 €.

Ninguna función de Excel sabe leer un color de relleno. El ámbar era lo único en esa hoja que decía qué filas saltarse, y lo único capaz de leerlo era una persona.

La remesa del juevesPor el colorPor una columna de estado
Pagado40.917,80 €31.707,80 €
Pagado con incidencia18.640,00 €0,00 €
Pendiente y retenido9.430,00 €0,00 €
Fórmula detrásninguna=SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Due")

Alcance. Colores de relleno, Filtrar por color, Ordenar por color, SUBTOTALES, AGREGAR y el formato condicional, en Excel para Microsoft 365, 2021, 2019 y Excel para Mac.

FILTRAR, UNICOS y ORDENAR necesitan Microsoft 365 o Excel 2021. SUMAPRODUCTO hace el mismo trabajo en cualquier versión. En toda la guía, la columna C es el importe, la columna E el estado, y el libro mayor va de la fila 2 a la 13.

01La Semana en Que el Color Era el Único Control

Doce facturas abiertas, 67.048,00 € en total. El ámbar significa que hay una incidencia abierta y la factura espera. El verde, que ya se pagó con tarjeta. Sin relleno, se paga el jueves.

Esa regla no está escrita en ninguna parte del archivo. Vive con la responsable de crédito, que esa semana estaba de vacaciones.

El jueves por la mañana tres facturas tenían que estar en ámbar. La incidencia de INV-4471 llegó por correo sin nadie que la pintara, y el ámbar de INV-4473 se cayó en un pegado.

INV-4475 fue al revés. Nadie la había cuestionado, entró debajo de una fila ámbar y pasó la semana con cara de disputa.

Doce Facturas, Tres Significados, una Columna de Color

El libro mayor de compras de Calderstone Joinery el jueves por la mañana, en A2:F15. La columna D es el relleno que llevaba cada fila, escrito como texto porque un color no se puede copiar dentro de un artículo. La columna E es la columna que el libro no tenía: el estado, reconstruido después a partir de los correos de la responsable de crédito. Las dos filas del final son la remesa tal como salió y la remesa tal como debería haber salido, y la diferencia entre ambas son 18.640,00 € pagados con incidencia frente a 9.430,00 € retenidos a un proveedor al que se le debían.

ABCDEF
1
Invoice
Supplier
Amount
Fill on Thursday
Status
What happened
2
INV-4471
Thwaite Timber
12480
None
Query
A short delivery, queried by email on Wednesday afternoon. The credit controller was on leave, nobody painted the row amber, and Thursday's run treated it like any other invoice and paid it.
3
INV-4472
Pennine Ironmongery
3215.4
None
Due
Nothing wrong with it anywhere. No fill, no query, and one of the seven rows where the colour and the fact agreed all week.
4
INV-4473
Garsdale Veneers
6160
None
Query
Amber on Monday. On Wednesday a corrected line arrived by email, somebody copied it into row 4, and the paste brought white with it. The hold came off without anyone touching the fill.
5
INV-4474
Calder Fixings
2765.2
Amber
Query
A price query raised a fortnight ago. Still amber, still unpaid, and the row that shows the arrangement works perfectly as long as nobody edits around it.
6
INV-4475
Thwaite Timber
9430
Amber
Due
Inserted under row 5 on Tuesday, so it was born amber: a new row copies the formatting of the row above it. No query was ever raised, the invoice sat back, and the supplier put the account on stop on Friday.
7
INV-4476
Marsden Glass
4998.3
None
Due
Due, unqueried and paid on Thursday, which is what was meant to happen to seven of these twelve rows.
8
INV-4477
Holbeck Adhesives
1842.75
None
Due
Paid correctly. A small invoice that nobody has ever had a reason to colour, which is why it has never been at risk.
9
INV-4478
Garsdale Veneers
7940
None
Due
The second Garsdale invoice of the month, this one undisputed. It was paid on Thursday and should have been.
10
INV-4479
Pennine Ironmongery
1206.45
None
Due
Paid correctly on Thursday. Three of the twelve suppliers appear twice on this sheet, and only the colour said which copy was in dispute.
11
INV-4480
Marsden Glass
5320
Green
Paid
Settled by company card on the 2nd. Green means do not pay again, which is a third meaning loaded into a column that can only carry one at a time.
12
INV-4481
Calder Fixings
3074.9
None
Due
Paid correctly. The second Calder invoice: the other one is the amber that survived the week.
13
INV-4482
Thwaite Timber
8615
Green
Paid
Also settled by card. The two greens are why the payment run can never be a plain total of column C, whatever the colours say.
14
Run, by the fill colour
8 invoices
40917.8
No fill
—
What went out on Thursday: every row that had no fill on it that morning. Filter by Colour, No Fill, and SUBTOTAL with function 109 returns the same figure.
15
Due, by the status column
7 invoices
31707.8
—
Due
What should have gone out: SUMIFS over column E. It is the only one of the two numbers that does not depend on somebody remembering to paint a cell.

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

02Ninguna Fórmula Ve un Relleno

=SUMA(C2:C13) devuelve 67.048,00 € tengan las filas el color que tengan. Y lo mismo cualquier función condicional: leen valores, y un relleno no es un valor.

=CELDA("color";C2) parece la excepción y no lo es. Devuelve 1 cuando el formato de número pinta los negativos y 0 en cualquier otro caso. Del relleno no dice nada.

Un color es formato, y el formato no entra en ningún cálculo. El relleno es lo único de una hoja que solo puede auditar un ojo humano.

Escenario: Pon =SUMA(C2:C13) junto a tu columna de colores y cambia el relleno de una fila. Pulsa F9 y mira cómo el total no se mueve. Ese es el control que creías tener.

Pruébalo en la cuadrícula

03Filtrar por Color Mide, No Decide

Excel sí lee el relleno en un sitio: Datos ▸ Filtro ▸ Filtrar por color, que ofrece todos los rellenos presentes en la columna. Eliges el ámbar y en pantalla solo quedan esas filas.

Un total bajo un filtro necesita la función correcta. =SUMA(C2:C13) sigue sumando las filas ocultas; =SUBTOTALES(109;C2:C13) suma solo las visibles, y =AGREGAR(9;5;C2:C13) hace lo mismo.

Filtrar por color ▸ Sin rellenoFórmulaDevuelve
Toda la columna=SUMA(C2:C13)67.048,00 €
Solo filas visibles=SUBTOTALES(109;C2:C13)40.917,80 €
Solo filas visibles=AGREGAR(9;5;C2:C13)40.917,80 €

Esos 40.917,80 € son exactamente lo que pagó la remesa. El filtro midió bien el color; el color estaba mal.

Escenario: Filtra tu libro mayor por el color que significa retener y pon =SUBTOTALES(109;C2:C13) y =SUMA(C2:C13) una al lado de la otra. Anota la diferencia, quita el filtro y mira cuál de las dos cambia.

Pruébalo en la cuadrícula

04Cómo Perdió INV-4473 Su Ámbar

Nadie se lo quitó. Lo hizo una edición corriente, y esa edición no deja rastro.

Al pegar se arrastra el formato de origen. El proveedor mandó una línea corregida por correo, alguien la copió del correo a la fila 4, y el pegado trajo el blanco consigo. Una tecla, y la factura dejó de estar retenida.

Una fila insertada copia el formato de la fila de arriba. La fila 6 se añadió bajo una fila ámbar, así que nació ámbar. INV-4475 estaba lista para pagar y parecía una incidencia.

Pegado especial ▸ Valores (Ctrl+Alt+V) respeta el formato de destino, y el botón Opciones de inserción que aparece junto a la fila nueva ofrece Borrar formato. Son dos gestos fáciles, y los dos dependen de que alguien se acuerde.

Escenario: Copia una celda de otra hoja, pégala sobre una fila de color de tu libro mayor y mira qué hace el relleno. Deshaz, pega otra vez con Ctrl+Alt+V ▸ Valores y compara.

Pruébalo en la cuadrícula

05El Color de una Regla No Es un Relleno

Una hoja puede estar ámbar de dos maneras: alguien aplicó un relleno, o una regla de formato condicional pintó la fila. Se ven igual y se comportan distinto.

Filtrar por color ve las dos. Buscar y reemplazar solo ve el relleno. Busca por formato, elige el ámbar de una celda, pulsa Buscar todos, y las filas pintadas por una regla no aparecen en la lista.

Borrarlas marca aún más la diferencia. Inicio ▸ Borrar ▸ Borrar formatos se lleva el relleno y deja la regla, así que el color vuelve al instante y parece que Excel te ignora.

¿Cuál de las dos es? Selecciona la fila y abre Inicio ▸ Formato condicional ▸ Administrar reglas ▸ Esta hoja.

Si una regla incluye la fila en su rango "Se aplica a", el color es el resultado de esa regla. Si la lista está vacía, alguien la pintó a mano y detrás no hay nada.

06GET.CELL Funciona Hasta Que Alguien Cambia un Color

El apaño clásico es una función macro de Excel 4. Administrador de nombres ▸ Nuevo, la llamas FillCode y le das =GET.CELL(63;INDIRECTO("rc[-1]";FALSO)). Escribes =FillCode al lado de una celda y obtienes su relleno como número.

Tiene tres problemas, y el tercero es el que cuesta dinero.

El libro hay que guardarlo como .xlsm, porque ahora contiene una función macro. El resultado es un índice de color, así que tu fórmula compara contra un número que nadie sabe leer.

Cambiar un relleno no dispara ningún recálculo. Excel recalcula cuando cambian valores, y un color no es un valor, así que FillCode devuelve la respuesta de ayer hasta que otra cosa de la hoja obligue a recalcular.

Escenario: Monta FillCode, pon =CONTAR.SI(F2:F13;40) al lado, repinta una fila y pulsa F9. El recuento no se moverá. Pulsa Ctrl+Alt+F9 y míralo saltar: así de viejo puede ser un total basado en colores.

Pruébalo en la cuadrícula

07Escribe el Estado en una Columna

Todo lo anterior es un rodeo para suplir una columna que falta. Añade la columna E, llámala Status y escribe lo que el color quería decir: Due, Query, Paid.

Tres palabras hacen contable el libro entero. =CONTAR.SI(E2:E13;"Query") devuelve 3, =SUMAR.SI(E2:E13;"Query";C2:C13) devuelve 21.405,20 €, y las dos se actualizan solas.

Cierra el vocabulario con Datos ▸ Validación de datos ▸ Lista apuntando a $H$2:$H$4. Una columna escrita a mano se inventa sus propias grafías, y un estado que nadie eligió de una lista es un estado que nadie puede sumar.

El texto libre tiene su propio fallo. SUMAR.SI.CONJUNTO encuentra Query y no encuentra Query con un espacio al final, así que una tecla de más deja una factura fuera del total sin avisar.

=SUMAPRODUCTO(--(LARGO(E2:E13)<>LARGO(ESPACIOS(E2:E13)))) devuelve cuántas celdas llevan espacios sobrantes. Debería dar 0, y en un libro importado casi nunca lo da a la primera.

Escenario: Añade una columna de estado a tu hoja, rellénala con los colores que todavía ves y pon =ORDENAR(UNICOS(E2:E13)) en una celda vacía. Todo lo que aparezca y no quisieras escribir es un estado que tus totales no están viendo.

Pruébalo en la cuadrícula

08Que el Color Sea la Salida, No la Entrada

El ámbar no tiene que desaparecer. Tiene que dejar de ser la única copia del dato.

Selecciona A2:F13, abre Inicio ▸ Formato condicional ▸ Nueva regla ▸ Utilice una fórmula y escribe =$E2="Query" con relleno ámbar. La fila se pone ámbar porque el estado dice Query, y deja de estarlo en cuanto el estado cambia.

Ahora el color no puede desviarse. Un pegado que borre el relleno no cambia nada, porque la regla lo repinta desde la columna E; una fila insertada no hereda nada, porque su celda de estado está vacía hasta que alguien la rellene.

Una regla puede llevar lógica que el ojo no lleva. =Y($E2="Query";$C2>5000) separa las incidencias grandes, y =O($E2="Query";$E2="Paid") marca todo lo que la remesa debe saltarse.

Escenario: Borra todos los rellenos manuales con Inicio ▸ Borrar ▸ Borrar formatos y añade la regla =$E2="Query". Cambia un estado a Due y mira cómo la fila se queda en blanco sola.

Pruébalo en la cuadrícula

09SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO y un Total Que Cuadra

Con una columna de estado, la remesa es una fórmula: =SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Due") devuelve 31.707,80 €, que es la cifra que el jueves tenía que haber salido.

Los recuentos vienen del mismo sitio. =CONTAR.SI.CONJUNTO(E2:E13;"Due") devuelve 7, =CONTAR.SI.CONJUNTO(E2:E13;"Query";C2:C13;">5000") devuelve 2, y =MAX(FILTRAR(C2:C13;E2:E13="Query")) devuelve 12.480,00 €, la mayor factura retenida.

PreguntaFórmulaEl jueves
Por pagar=SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Due")31.707,80 €
Retenido=SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Query")21.405,20 €
Ya liquidado=SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Paid")13.935,00 €
Libro entero=SUMA(C2:C13)67.048,00 €

Las tres primeras suman la cuarta, y ese es el control. =LET(d;SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Due");q;SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Query");p;SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Paid");REDONDEAR(d+q+p-SUMA(C2:C13);2)) devuelve 0 cuando cada fila lleva un estado que Excel reconoce.

En Excel 2019 y anteriores, =SUMAPRODUCTO(--(E2:E13="Due");C2:C13) devuelve los mismos 31.707,80 €.

Y =INDICE($I$2:$I$4;COINCIDIR(E2;$H$2:$H$4;0)) traduce un estado en una instrucción igual que =BUSCARX(E2;$H$2:$H$4;$I$2:$I$4;"Revisar") en 365.

La instrucción de cada fila es una sola pregunta repetida. =SI(E2="Query";"Hold";"Pay") cubre dos salidas, =SI.CONJUNTO(E2="Query";"Hold";E2="Paid";"Settled";VERDADERO;"Pay") cubre tres, y =SI.ERROR(INDICE($I$2:$I$4;COINCIDIR(E2;$H$2:$H$4;0));"Estado no reconocido") lo dice en voz alta cuando una fila lleva otra cosa.

Escenario: Pon los cuatro totales de arriba en cuatro celdas de tu libro mayor y añade el control. Si devuelve algo distinto de 0, alguna fila lleva un estado que ninguna de tus fórmulas está mirando.

Pruébalo en la cuadrícula

10Lo Que el Color No Sobrevive

Un relleno es lo menos portátil de un libro, y el fichero de pagos sale del libro todas las semanas.

Guarda como CSV y los rellenos desaparecen. Pega como valores en un correo y el color se va con el relleno. Imprime en blanco y negro y el ámbar y el verde son dos grises.

Aproximadamente uno de cada doce hombres no distingue el ámbar del verde, y ningún lector de pantalla ha anunciado nunca un color de relleno.

Una columna de estado sobrevive a todo eso. Se exporta, se pega, se imprime, se lee en voz alta y además se suma.

Escenario: Guarda una copia de tu libro de colores como CSV, ciérralo y vuelve a abrirlo. Todo el significado que no puedas reconstruir desde las columnas es significado que nunca estuvo en el archivo.

Pruébalo en la cuadrícula

11Cinco Controles Antes de Mandar la Remesa

  1. Cuadra los estados contra el libro. =SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Due")+SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Query")+SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Paid") tiene que dar lo mismo que =SUMA(C2:C13): 67.048,00 €.
  2. Cuenta lo que no tiene estado. =CONTARA(A2:A13)-CONTAR.SI(E2:E13;"Due")-CONTAR.SI(E2:E13;"Query")-CONTAR.SI(E2:E13;"Paid") devuelve 0 cuando todas las facturas están clasificadas.
  3. Lee el vocabulario. =ORDENAR(UNICOS(E2:E13)) debería listar tres palabras y ninguna variante parecida.
  4. Busca los espacios sobrantes. =SUMAPRODUCTO(--(LARGO(E2:E13)<>LARGO(ESPACIOS(E2:E13)))) devuelve 0 en una columna limpia.
  5. Compara el color con la columna. Filtra por color y enfrenta =SUBTOTALES(109;C2:C13) a =SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Due"). Una diferencia significa que un relleno se ha separado del dato que había detrás.

12Nueve Trampas

  1. El color como único registro. Si se borra el relleno se borra el dato, y nada en el archivo recuerda que estuvo ahí.
  2. Una celda pegada que repinta la fila. El pegado arrastra formato; Ctrl+Alt+V ▸ Valores, no.
  3. Una fila insertada que hereda el relleno. Las filas nuevas copian la de arriba, así que una factura en blanco nace retenida.
  4. Ordenar una columna sola. Si rechazas "ampliar la selección", los valores se mueven y los rellenos se quedan donde estaban.
  5. SUMA bajo un filtro. Suma las filas que el filtro esconde. SUBTOTALES(109;…) y AGREGAR(9;5;…), no.
  6. Borrar formatos contra una regla condicional. El relleno se va, la regla lo repinta y la hoja parece rota.
  7. GET.CELL en un total vivo. No recalcula cuando cambia un color, así que informa de los colores de otro momento.
  8. Dos ámbares. Dos rellenos de tonos parecidos son un significado para quien lee y dos filtros distintos para Excel.
  9. =CELDA("color";C2) como prueba de color. Responde a una pregunta sobre formatos de número y no dice nada del relleno.

13Cinco Ejercicios

  1. Reprodúcelo. Monta las doce filas, pinta tres de ámbar y comprueba que =SUMA(C2:C13) ignora el color por completo.
  2. Mide el filtro. Filtra por color ▸ Sin relleno y comprueba que =SUBTOTALES(109;C2:C13) da 40.917,80 € frente a los 67.048,00 € de =SUMA(C2:C13).
  3. Rompe un relleno. Pega una celda de otra hoja sobre una fila ámbar e inserta una fila debajo de otra. Anota qué factura ha cambiado de bando sin avisar.
  4. Añade la columna. Escribe Due, Query o Paid en E2:E13 y pon =SUMAR.SI.CONJUNTO(C2:C13;E2:E13;"Due") en una celda. Tiene que dar 31.707,80 €.
  5. Invierte el color. Borra los rellenos, añade la regla =$E2="Query" y cambia un estado. La fila debería repintarse sola.

Qué Llevarte

El libro mayor no estaba mal. Doce facturas, doce importes correctos y un total que cuadraba al céntimo en una hoja donde no había nada roto.

Lo que faltaba era una columna. El ámbar significaba "hay una incidencia abierta", y esa frase vivía en el formato, donde ninguna fórmula llegaba y dos ediciones corrientes podían borrarla.

Escribe el dato en una celda, deja que una regla pinte el color desde esa celda, y el color pasa a ser el informe de algo en vez de la única copia.

Una columna de tres palabras, frente a 18.640,00 € pagados con incidencia y un proveedor bloqueando la cuenta por 9.430,00 €.

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