Formato condicional para destacar cambios en tablas, un plancentero descenso a los infiernos de Google Sheets

¿A quién no le apetece meterse en la madriguera del conejo de las hojas de cálculo de Google con este calor? A mí desde luego mucho 🙋‍♂️... con calor o sin él 🔥.

Te cuento de qué va esto con toda la brevedad de la que soy capaz, y ya si eso tú decides si te quedas conmigo unos minutos o haces algo de mayor provecho con tu vida.

Hace unas semanas lanzamos desde Transformación Educativa / GEG Spain un catálogo de apps para educación que ha quedado bastante resultón, para qué nos vamos a engañar.

Si no lo has visto ya, tal vez te apetezca hacerlo ahora:

👉 apps.transformacioneducativa.es 👈

El tinglado que usamos para montar esto tiene al menos un par o más de partes móviles, la primera y principal de las cuales es... lo has adivinado, una hoja de cálculo de Google, que constituye el glorioso backend de toda esta movida. Te la muestro aquí así en pequeñito para que no se vea nada que no deba verse.

Como te puedes imaginar, este fue un esfuerzo colaborativo en el que nuestro incansable Miguel Ujeda nos animó amablemente 🫵🏻 a participar. La idea consistía en que cada uno de nosotros (me refiero a los miembros del equipo de coordinación de GEG Spain) escogiera una docena o más de apps y rellenara todos esos interminables campos que puedes apreciar en la tabla de la captura anterior para caracterizarlas.

Para organizarnos como es debido, utilizamos una hoja auxiliar que nos permitiera a echar la cuenta de un vistazo de qué aplicaciones habían quedado asignadas a quién, así como de su estado de preparación. Y no nos engañemos, también para poder señalar con el dedo gordo del pie a quienes se estuvieran escaqueando.

Cualquier ser humano convencional hubiera recurrido a una honesta y leal tabla dinámica (a la izquierda) y se hubiera quedado más pancho que largo. Pero nosotros somos unos freaks de las hojas de cálculo, así que fue inevitable que tirásemos de fórmulas para obtener un resultado más elegante (derecha).

Ahora fíjate en esa tabla de asignaciones más decente, según reza el encabezado 🧐...

☝ Para facilitar la identificación visual de las aplicaciones asignadas a cada miembro del equipo GEG se ha utilizado el formato condicional, de manera que cada vez que en la columna G se produce un cambio de responsable, toda la fila cambia de color, alternando entre el azul y el morado.

Seguro que estás de acuerdo conmigo en que esto resulta más eficaz que el conocido recurso de aplicar colores alternos (menú formato → colores alternos) que sí, es automático, pero realmente no ayuda a delimitar visualmente las aplicaciones asignadas a cada persona, puesto que simplemente pinta las filas pares e impares de diferente color, con independencia de su contenido.

Tras el fragor de la batalla que supuso lanzar este catálogo de apps, lo cierto es que se me quedó sonando en bucle el runrún de escribir un pequeño artículo explicando cómo implementar este tipo de formato condicional, poco más que un pequeño divertimento hojacalcúlico estival, si me permites. Por eso...

Hoy abordaremos de manera progresiva el diseño de una serie de reglas de formato condicional que permitan cambiar de manera alterna el color de las filas de una tabla cuando se produzcan cambios en los valores de una de sus columnas.

Ahora ya sabes lo que te (nos) espera en los próximos minutos. ¿Me acompañas? Tal vez no resulte a priori tan atractivo como devorar la segunda temporada de La Casa del Dragón, que se acaba de estrenar, pero oye, que hay tiempo para todo ⚔🐉. O eso dicen.

Nota para mi yo futuro: creo que debo contener la extensión de los preámbulos de mis artículos 😰.

Un primer intento de lo más naif

Tal vez lo primero que se te ocurra para detectar cambios en los valores almacenados en cierta columna de una tabla sea algo simple y directo.

Por ejemplo, utilizar dos reglas de formato condicional basadas en sendas expresiones que comparan el valor de cada celda, en este caso de la columna B, con el de la inmediatamente superior. Aquí, por uniformidad, se realiza idéntica comparación en la fila 3, aunque en la 2 lo que realmente tendremos probablemente será el encabezado de la tabla o algún rótulo explicativo.

=$B3<>$B2
=$B3=$B2

En la columna D he insertado la primera de las expresiones anteriores, concretamente la que puedes ver en F3, para que aprecies el resultado de su evaluación en cada una de las filas de la tabla.

Y aquí tienes las dos reglas de formato condicional necesarias. Como seguro que ya sabes, se utiliza el dolar ($) para fijar las columnas, de modo que las expresiones de cada una de las reglas de coloreado se apliquen de modo correcto a todas las columnas de la tabla en B3:C16.

El problema es que esto, obviamente, no funciona bien.

Vamos a tener que esforzarnos un poco más.

ArrayFormula es tu amiga, aunque no te lo parezca

Una posible estrategia para resolver este pequeño pero interesante problema puede ser la siguiente:

Para cada fila de la tabla:

1. Se recorren las filas desde la primera hasta la actual y cada vez que el valor de la columna que observamos varía se anota un valor de 1. Esto representa un cambio en el valor de la columna. El valor de la primera fila se registra como cambio (1).
2. Se totaliza el número de unos anotados hasta el momento para la fila actual.
3. Si la suma es un número impar se aplica un color determinado; si es par se aplica otro alternativo.

Veámoslo con un nuevo ejemplo.

Por ejemplo, la fórmula que determina si el valor calculado es impar la tienes en la celda F3:

=ES.IMPAR(SUMA(ArrayFormula(SI($B$3:$B3<>$B$2:$B2;1;0))))

Lógicamente, a la hora de construir las reglas de formato condicional necesitaremos una expresión análoga como esta para tratar los valores pares (celda F10).

=ES.PAR(SUMA(ArrayFormula(SI($B$3:$B3<>$B$2:$B2;1;0))))

Para ayudarte a entender mejor cómo funciona este tinglado, en la columna C de la hoja de cálculo de este ejemplo he insertado una fórmula, que forma parte de la expresión finalmente utilizada, que totaliza el número de veces que se han producido cambios en los valores de la columna observada entre la primera fila de la tabla y la fila actual.

=SUMA(ArrayFormula(SI($B$3:$B3<>$B$2:$B2;1;0)))

Y para que todo quede clarinete de verdad, en la columna D verás el resultado de la expresión final, que es la que te muestro en la celda  F3.

¿No te convence aún?

El quid de la cuestión está en el uso de ARRAYFORMULA, esa bendita función que tan a menudo se utiliza para perpetrar diabólicas y muy ofuscadas fórmulas 😵‍💫.  Gracias a ella, lograremos generar sin esfuerzo ese vector de unos y ceros del que te hablaba hace un momento en el primer paso de ese pseudoalgoritmo de tres etapas que estamos utilizando para resolver este problema.

Ya no te pego aquí de nuevo una captura de ambas reglas, tal y como aparecen en el panel de formato condicional, porque creo sinceramente que no es necesario. Del mismo modo que en nuestro primer intento, he aplicado las reglas de formato condicional sobre la totalidad de la tabla, en el intervalo B3:C16.

¡Usa de una vez la sintaxis moderna!

Esa «sintaxis moderna» a la que me refiero de manera decididamente enigmática en el título de este apartado hace referencia a algunas de las fantásticas novedades que llegaron al lenguaje de fórmulas de las hojas de cálculo de Google en agosto de 2022.

Estoy hablando de la función LAMBDA y de sus funciones auxiliares acólitas BYROW, BYCOL, MAP, SCAN, REDUCE y MAKEARRAY, diseñadas para masticar intervalos de datos de un modo más intuitivo, natural y versátil que nuestra por otra parte extraordinaria aliada matricial, ARRAYFORMULA. Lo sé, esto ya no es cosa tan moderna 🤷🏻‍♂️.

Pertrechado de toda esta artillería, Sheets pueden mirar de tú a tú a otras herramientas que manejan un mayor nivel de abstracción en sus lenguajes de fórmulas, a priori más evolucionados. Como muestra, un botón:

La sintaxis LAMBDA  —permíteme que me refiera a ella así— produce expresiones que en mi opinión son más legibles (aunque a costa de resultar menos compactas) y, según mi experiencia, algo menos eficientes cuando se trata de procesar intervalos de datos muy grandes.

En cualquier caso, creo que la sintaxis LAMBDA es el futuro del lenguaje de fórmulas de Sheets, y personalmente la utilizo preferentemente, aunque solo sea por deferencia hacia ese yo futuro al que interpelaba al inicio de este artículo y que a menudo se ve en la obligación de tener que desentrañar los artefactos que su alter ego pretérito ha parido.

Para crear una función al estilo LAMBDA equivalente a la que diseñamos en el apartado anterior mediante ARRAYFORMULA, usaremos la función auxiliar REDUCE, que tiene un funcionamiento de lo más interesante.

Con REDUCE podemos aplicar de manera secuencial un cálculo sobre cada elemento de una matriz de datos de manera que finalmente se devuelva un resultado acumulado. Esto es así porque esta función, al igual que SCAN, tiene memoria, es decir, arrastra un acumulador que podemos ir actualizando en cada iteración hasta alcanzar el resultado final.

Veamos cómo queda la cosa y comentemos un par de cositas sobre la implementación.

En este caso, el etiquetado de las filas en las que se han producido un número acumulado de cambios impares se resuelve así:

=ES.IMPAR(
  REDUCE(0;$B$3:$B3;LAMBDA(total;valor;
    SI(O(total=0;valor<>DESREF(valor;-1;0));total+1;total)
  ))
)

Varias cosas a destacar aquí:

• La estructura REDUCE/LAMBDA opera inicialmente sobre las celdas del intervalo $B$3:$B3. Esta referencia es absoluta por arriba y mixta por abajo, de modo que al arrastrar la fórmula verticalmente trabajará con las celdas de la tabla comprendidas entra la primera y la que se encuentra en la fila actual: B3:B3, B3:B4, B3:B5... B3:B16.
• Ahora se evita la comparación fantasma en el primer elemento de la tabla. Si el acumulador total es 0 es que nos encontramos tratando el primer valor, y por tanto la función lambda devolverá 1, señalizando de ese modo el primer conjunto de datos.
• El parámetro valor representa cada uno de los valores de las celdas en B3:B16. Pero cuidado, no olvides que esa etiqueta constituye realmente una referencia. Por esa razón, podemos utilizar la función DESREF para comparar el valor de cada fila con el de la que tiene justo encima.

¿Te has quedado con ganas de más? Pues hablemos ahora de espacios en blanco.

La memoria de las celdas... vacías

Nuestra estrategia de detección de cambios alternos en los valores de cierta columna de una tabla funcionan. No obstante, dependiendo de cómo se mire la cosa exhibe un comportamiento que podría considerarse mejorable. Mira esto:

En esta captura puedes apreciar cómo las celdas vacías son identificadas por nuestras reglas de formato condicional como valores diferentes, produciéndose un cambio al color alterno correspondiente en cuanto una de ellas aparece en la columna de la tabla que estamos observando.

Claro está que si repasas la implementación de las fórmulas que hemos preparado hasta el momento comprobarás que esto es lo que tenía que pasar, puesto que la prueba lógica utilizada para detectar cambios no era otra que esta:

O(total=0;valor<>DESREF(valor;-1;0))

No obstante, puede que prefieras que las celdas vacías queden fuera de esta comprobación y sean consideradas como parte del grupo inmediatamente anterior de filas, cualquiera que sea el valor que aparezca en su columna de referencia.

Aplicando este nuevo criterio, la serie de datos que te muestro en la captura anterior quedaría coloreada de este modo alternativo:

No sé cómo lo verás tú, pero esta nueva estrategia de coloreado de las celdas personalmente me resulta más coherente e informativa desde un punto de vista visual.

¿Te he convencido? Pues veamos cómo se implementan ahora nuestras nuevas reglas de formato condicional.

Como de costumbre, aquí tienes la fórmula que realiza la detección de cambios impares (F3):

=ES.IMPAR(
  REDUCE(0;$B$3:$B3;LAMBDA(total;valor;
    SI(
      O(
        total=0;
        Y(
          valor<>"";
          valor<>LET(
            anteriores;$B$3:DESREF(valor;-1;0);
            intervalo;SI.ERROR(FILTER(anteriores;anteriores<>"");valor);
            INDICE(intervalo;FILAS(intervalo))
          )
        )
      );
      total+1;total
    )
  ))
)

Sí, la fórmula se nos complica un poco. Y el temido efecto «muro de fórmulas» 🧱 comienza a asomar ya tímidamente su fea cabeza . Analicemos las partes móviles de este pequeño engendro.

La modificación requerida para tratar las celdas vacías situadas por encima de la que se encuentra en la fila que estamos analizando se limita a linterior de la función SI,  concretamente a la comparación del parámetro valor, que antes era tan simple e inmediata como esto:

valor<>DESREF(valor;-1;0)

Ahora, sin embargo, no es posible realizar una comparación directa con la celda inmediatamente superior a la analizada (eso consideraría las celdas vacías como un valor de comparación válido, justo lo que deseamos evitar), sino que debemos remontarnos a la primera celda por encima de la actual que no esté en blanco:

• El intervalo de celdas situadas justo por encima de la que se está analizando en cada iteración de la estructura REDUCE/LAMBDA (¿la podemos llamar ya bucle?) se obtiene mediante la expresión $B$3:DESREF(valor;-1;0). Lo sé, esto parece una marcianada. Estamos construyendo un rango que representa un intervalo (ref_celda1:ref_celda2) utilizando para ella una referencia explícita, inmutable, a la celda superior ($B$3) y una expresión parametrizada para establecer un decalaje vertical hasta la celda con la que se cierra el intervalo mediante DESREF. Y la cosa funciona que da gusto. ¿No es el lenguaje de fórmulas de Sheets maravilloso 🤩?
• El intervalo, vamos a denominar dinámico, anterior se pasa como parámetro a la función FILTER para descartar las celdas vacías. Dejaremos para otro día la eterna discusión acerca de si FILTER es más guapa que QUERY o es justo al revés.
• La expresión de filtrado previa queda envuelta en un SI.ERROR para tratar adecuadamente la circunstancia en que la celda superior del intervalo columna esté vacía. En ese caso se comparará tautologicamente el valor de la celda que se está analizando consigo mismo.
• Mediante la función INDICE se obtiene a continuación la última celda del intervalo devuelto por el filtro que rechaza las celdas vacías.
• Finalmente —por fin llega la parte obvia—, utilizamos la muy conveniente función LET para evitar la repetición de fragmentos de esta expresión, que ya presenta cierta complejidad natural, y hacerla un poco más presentable e inteligible.

Ya sabes lo que viene ahora. Se construye una expresión análoga para detectar las celdas en las que se producen cambios pares y se aplican ambas en sendas reglas de formato condicional. Puedes apreciarlas en la captura de pantalla más reciente.

¡Cosa hecha!

Queremos más colores

¿No te sientes hoy un poco insaciable? Yo sí, debe ser el calor. Por esa razón, vamos a darle una vuelta de tuerca adicional a nuestro colorido tinglado.

¿Cómo? Muy fácil: añadiendo la posibilidad de utilizar más de dos colores alternos. ¿Cuatro te parecen suficientes? Te adelanto que la extensión de esta propuesta a cualquier número de colores —grupos visuales una vez aplicadas las reglas de formato condicional, en definitiva—, es inmediata.

La cosa es mucho más sencilla de lo que tal vez pudiera parecer a simple vista.

Estoy seguro de que mientras me lees, un palabra se ha materializado flotando fantasmalmente delante de ti: RESIDUO 😏.

La función de Sheets a la que da nombre esta arcana palabra sirve para calcular el resto de la división entera. Y si recuerdas, la estrategia que hemos seguido para implementar todo esta movida se basa en el recuento del número de cambios que se produce en los valores de cierta columna de la tabla a medida que nos vamos desplazando hacia su extremo inferior.

Por tanto, para resolver este nuevo reto nos bastará con sustituir la comprobación de paridad que hasta el momento estábamos realizando sobre los valores devueltos por el bucle REDUCE/LAMBDA por otra basada en el cálculo del resto de su división entera entre el número de colores alternos a utilizar.

Para que no nos de un pasmo ni por asomo, que ya estamos en verano y toca disfrutar, hagámoslo primero sobre la implementación básica, que no realiza ningún tratamiento especial sobre las celdas en blanco.

El sortilegio principal hecho fórmula, como de costumbre en la celda F3. 

=RESIDUO(
  REDUCE(0;$B$3:$B3;LAMBDA(total;valor;
    SI(O(total=0;valor<>DESREF(valor;-1;0));total+1;total)
  ))-1;4
)+1

Obviamente, ahora vamos a necesitar cuatro expresiones (reglas) de formato condicional, tantas como colores alternos deseemos aplicar automáticamente a las celdas de la tabla, cuyo resultado compararemos respectivamente con los valores 1, 2, 3 y 4 para determinar qué color debe aplicarse.

Por ejemplo, esta sería la formula utilizada para construir la regla de formato condicional que establece el color de fondo de las celdas a verde, el primero en la secuencia escogida de cuatro colores alternos [verde, amarillo, púrpura, naranja]:

=RESIDUO(
  REDUCE(0;$B$3:$B3;LAMBDA(total;valor;
    SI(O(total=0;valor<>DESREF(valor;-1;0));total+1;total)
  ))-1;4
)+1 = 1

Si es que el trabajo duro ya lo teníamos hecho 🏔⛏ .

Vamos con todo

Con lo del pasmo verás que exageraba.

Conseguir aplicar también un formato condicional multicolor cuando se gestionan las celdas vacías del modo que ya hemos visto es tan simple como el mecanismo de un botijo. De hecho, lo haremos exactamente del mismo modo que en el caso anterior.

Solo tendremos que modificar ligeramente las fórmulas desarrolladas hace dos apartados para introducir de manera análoga el uso de la función RESIDUO.

Y cómo no, aquí la expresión utilizada, que deberemos comparar nuevamente con los valores numéricos 1, 2, 3 y 4 para determinar el color a aplicar.

=RESIDUO(
  REDUCE(0;$B$3:$B3;LAMBDA(total;valor;
    SI(
      O(
        total=0;
        Y(
          valor<>"";
          valor<>LET(
            anteriores;$B$3:DESREF(valor;-1;0);
            intervalo;SI.ERROR(FILTER(anteriores;anteriores<>"");valor);
            INDICE(intervalo;FILAS(intervalo))
          )
        )
      );total+1;total
    )
  ))-1;4
)+1

Por ejemplo, esta sería la regla de formato condicional para el color púrpura (esta vez en modo compacto):

=RESIDUO(REDUCE(0;$B$3:$B3;LAMBDA(total;valor;SI(O(total=0;Y(valor<>"";valor<>LET(anteriores;$B$3:DESREF(valor;-1;0);intervalo;SI.ERROR(FILTER(anteriores;anteriores<>"");valor);INDICE(intervalo;FILAS(intervalo)))));total+1;total)))-1;4)+1 = 3

Aquí tienes la hoja de cálculo que he utilizado para preparar los ejemplos que te he mostrado a lo largo de este artículo, por si te apetece enredar un poco con ellos.

👉 Formato condicional para cambios en valor columna 👈

Comentarios finales y siguientes pasos

El tiempo pasa volando cuando uno se divierte con las hojas de cálculo. No obstante, antes de concluir me gustaría comentar algunas cuestiones que en mi opinión tienen cierta importancia.

La primera es que estas expresiones de formato condicional son relativamente ineficientes. Si desmenuzas las fórmulas, apreciarás sin duda que hay cálculos que se repiten una y otra vez de manera secuencial  a medida que se van analizando los valores de las distintas filas de la columna observada.

Estas fórmulas podrían optimizarse, de modo que para determinar el número de cambios que se han registrado en la fila n se aprovecharan las operaciones previas efectuadas al evaluar la fila n-1.

Lamentablemente, esto no parece factible cuando se trata de construir reglas de formato condicional basadas en fórmulas, puesto que por su naturaleza carecen de algún tipo de memoria interna capaz de almacenar los resultados obtenidos en el contexto de evaluaciones previas.

Para solucionarlo, podríamos insertar una columna auxiliar en la tabla en la que realizar estos cálculos continuos optimizados, ocultarla para que no moleste, y diseñar reglas de formato condicional basadas en ellos. Es una solución que, aunque ciertamente común, me parece fea de narices, así que prefiero someter su implementación a tu consideración ya en solitario.

Doy por sentado que de aplicar las técnicas descritas en este artículo en tablas con decenas o cientos de miles de filas la cosa puede ponerse fea, pero no mucho más de lo que ya se pone en cuanto utilizas en esas situaciones fórmulas de cierta complejidad.

☝ Hablando de cosas que no funcionan en las reglas de formato condicional basadas en fórmulas, y aunque solo sea por poner más pegas, debes saber que no admiten funciones con nombre ni, parece ser, la nueva sintaxis que ha llegado de la mano de las nuevas Tablas de Google Sheets.

🙏 Además, detalles como un panel de gestión de reglas ampliable y un cajetín de expresiones multilínea mejorararían considerablemente la experiencia de usuario al construir reglas con fórmulas complejas.

Y, cambiando un poco de tercio, aquí va la segunda cuestión que quería mencionar. Creo que resultaría notablemente útil enriquecer la estrategia que hemos utilizado a lo largo de este artículo para extenderla a la detección de cambios en varias columnas. Pero cuidado, concatenar a lo bruto los valores almacenados en varias celdas y efectuar la comparación entre ellos podría parecer satisfactorio a primera vista, pero no constituye un procedimiento plenamente seguro. Otra bonita madriguera 🐇 en la que perderse, qué duda cabe.

Ya tienes cosas en las que pensar, así que nada más por hoy. Que las hojas de cálculo (de Google) te acompañen y te sean siempre propicias.