Resumiendo filas relacionadas en las nuevas Tablas de Google Sheets (+ una extraña peculiaridad 😵‍💫)

Una inocente fórmula para consolidar la información almacenada en dos inocentes tablas relacionadas me hace caer en la inesperada madriguera del conejo de las nuevas y estupendas Tablas de Google Sheets. ¿Me acompañas? 🐰🕳️

Preámbulo

El mes de julio ha resultado más que agotador en el centro. El trabajo, en múltiples frentes, se me ha comido por los pies, para qué voy a negarlo.

Entre otras muchas cosas, he dedicado parte de la tregua que concede el final del periodo lectivo del curso a hacer limpieza en algunos de los tinglados digitales que tengo montados, que son muchos y muy (demasiado) diversos.

Uno de esos tinglados ha sido una aplicación que tenía en funcionamiento en Knack desde el 2016 para el registro y seguimiento de las estancias formativas en centros de trabajo de los estudiantes de FP y de otras áreas formativas del centro. Nada del otro mundo, unas cuantas tablas relacionadas (empresas, estudiantes, estancias, seguimientos de estancias y evaluaciones de empresas) y otras tantas vistas asociadas para facilitar la introducción y revisión de la información pertinente de manera cómoda y rápida.

Y es que finalmente decidí ahorrarme el coste anual del plan contratado con Knack, que por otra parte es una herramienta excelente, para dedicarlo a otras cosas. Por aquello de lograr una cierta cohesión digital, me pareció oportuno integrar esta aplicación (gesFCT) con el sistema de gestión integral que he ido construyendo a lo largo del curso sobre Notion (del que en el centro usamos un plan educativo) para dar soporte a las múltiples normas ISO 9001/14001/27001/45001 😰 en las que estamos certificados. Otro día igual te cuento más cosas sobre él.

Como paso previo a la cancelación de mi cuenta en Knack, que por prudencia voy a mantener un tiempo congelada antes de su desactivación definitiva, exporté toda la información almacenada en las cinco tablas de gesFCT a sencillos y honestos archivos CSV, que a continuación importé en las distintas pestañas de una leal hoja de cálculo de Google.

Tienes razón, podría haber trasladado todas las estancias de prácticas ya registradas en gesFCT/Knack sobre el nuevo espacio de gestión en Notion, pero me pareció apropiado hacer un reboot en toda regla y partir de un sistema vacío, sin registros de previos, para descartar información que podría ya estar obsoleta. Estos "reinicios" a menudo resultan de lo más beneficiosos.

Pero por supuesto, era necesario habilitar al mismo tiempo un mecanismo sencillo de consulta del histórico de estudiantes, empresas y estancias para mantener la trazabilidad.

Y aquí es donde realmente comienza lo que quiero contarte hoy, que aunque no lo parezca va de hojas de cálculo.

Fin del preámbulo (como ves sigo teniendo problemas de verbosidad con ellos 😅).

Una formula para resumir la información relacionada

En gesFCT, cada estancia formativa en la empresa se identifica mediante una columna que contiene un identificador numérico único (ID) y queda perfectamente caracterizada por la combinación de estudiante, empresa y periodo. Lógicamente, en la tabla también se captura otra información significativa sobre cada programa de prácticas.

La persona responsable del correcto desarrollo de cada estancia debe realizar una serie de acciones de seguimiento para comprobar que todo está desarrollándose como debe. Estos registros de seguimiento se guardan en la tabla denominada Seguimientos prácticas. Cada uno de ellos se relaciona con una estancia específica mediante el campo ID estancia, en el que puedes ver los valores de ID correspondientes a las estancias registradas en la tabla Estancias. 

☝ Sí, estoy usando mucho en mis tinglados las nuevas Tablas de Google Sheets, de las que ya he hablado aquí y aquí. Y lo cierto es que me gustan muchas cosas de ellas: su elegancia, sus sencillos y rápidos controles para ordenar, filtrar y ahora también agrupar la información, la facilidad con la que se expanden y, por supuesto, la nueva sintaxis de selección de columnas de la que disfrutaremos al trabajar con ellas desde nuestras fórmulas.

👍 Y si bien es cierto que estos nuevos artefactos aún presenta algunos aspectos por pulir y que no siempre constituyen la elección idónea a la hora de presentar la información, creo que en general simplifican las cosas.

🤔 Si me preguntas a mí, yo diría que en tanto los usuarios que vayan a consumir la información de una hoja de cálculo estén lo suficientemente familiarizados con sus técnicas básicas de uso como para no hacer una maleza cada dos por tres, las Tablas son una alternativa para montar tus tinglados que resulta muy práctica y ahorra tiempo. En otras situaciones creo que no queda otra que seguir tirando de las "viejas" vistas de datos propulsadas por controles de filtro, a pesar de sus manías al parecer incorregibles).

Para evitar tener que ir saltando entre la tabla de estancias y la de seguimientos para obtener información acerca del desarrollo de cada estancia formativa, me pareció conveniente añadir una columna a la tabla de estancias que mostrara de manera resumida las acciones de seguimiento realizadas en cada una de ellas. La información relevante a consolidar era:

• Fecha de la acción de seguimiento.
• Tipo (inicial, intermedio, final).
• Resultado (negativo, neutral, positivo).

Para que lo veas más claro, te lo muestro con un ejemplo simplificado que es sin embargo análogo al resuelto sobre las tablas reales de gesFCT:

🎯 El objetivo es generar un secuencia de texto multilínea con el contenido de las columnas fecha, tipo y resultado (separadas por comas) de los registros de seguimiento de cada estancia.

La fórmula utilizada es esta:

=SI.ERROR(
  TEXTJOIN(CARACTER(10);VERDADERO;
    BYROW(
      SORT(
        FILTER(Seguimientos;Seguimientos[ID Estancia]=F2);
        1;VERDADERO);
      LAMBDA(fila;TEXTJOIN(", ";VERDADERO;
        {
          TEXTO(CHOOSECOLS(fila;1);"DD/MM/YYYY") \
          CHOOSECOLS(fila;2;3)
        }
      ))
));"ND")

Hablemos un poco de ella, pero lo haremos yendo —más o menos— del interior al exterior, como atravesando las capas de una tierna cebolla 🧅.

Con la función FILTER se recuperan todas las filas de la tabla Seguimientos cuyos valores en la columna ID Estancia son coincidentes con los correspondiente a cada una de las estancias.

Presta ahora mucha atención a la nueva sintaxis introducida por las Tablas (con la inicial en mayúscula) que permite utilizar el nombre de la tabla y la etiqueta del encabezado de la columna que se desea referenciar entre corchetes (como nombre_tabla[encabezado_columna]). No me negarás que tiene un aroma a AppSheet o Coda de lo más refrescante. Este nuevo estilo de referencia trae de la mano tres importantes mejoras:

1. Si cambias la etiqueta del encabezado (en este caso en la celda D1) o mueves la columna a otra posición dentro de la tabla, la expresión cambiará de manera acorde y seguirá funcionando perfectamente.
2. Si la columna crece, tanto al añadir filas en su interior (aquí no hay diferencias con respecto al uso de las referencias convencionales de tipo columna / fila o simplemente A1), como haciéndolo por sus extremos superior o inferior (esto sí es nuevo), la referencia se actualiza de manera automática para incluir las nuevas celdas.
3. No necesitas arrastrar tu fórmula ni usar ARRAYFORMULA. Al introducir o modificar una expresión dentro de la Tabla, un oportuno diálogo flotante te facilitará que la apliques cómodamente al resto de celdas de la columna.

Tal vez te preguntes por qué demonios no hemos utilizado esta nueva sintaxis tan fantástica en la condición utilizada en la función FILTER para obtener los registros de seguimiento asociados. Esta expresión utiliza una referencia absolutamente convencional (de tipo columna / fila o simplemente A1, como se suele decir) a la celda F2...

FILTER(Seguimientos;Seguimientos[ID Estancia]=F2)

...en lugar de algo como esto:

FILTER(Seguimientos;Seguimientos[ID Estancia]=Estancias[ID])

Respuesta rápida: porque no funciona ❌.

Recuérdame que volvamos a hablar de esto en la siguiente sección del artículo, íntegramente dedicada a los fenómenos ¿paranormales? 👻 en las nuevas Tablas. Pero por ahora prosigamos con nuestro análisis.

Las filas de la tabla de Seguimientos coincidentes con una estancia dada se ordenan por fecha ascendentemente por medio de la función SORT y a continuación son procesadas, una a una, gracias a BYROW/LAMBDA. No te pierdas, pasamos a ver qué hace y cómo funciona la expresión que se encuentra donde el banderín rojo 🚩.

BYROW(filas_seguimientos_ordenadas;LAMBDA(fila; 🚩)

La expresión dentro de la función LAMBDA construye un vector fila a partir de la fecha (columna 1), tipo (columna 2) y resultado (columna 3) de cada fila obtenida mediante el filtro aplicado sobre la tabla de seguimientos en la fase anterior. Para seleccionar cada elemento se usan dos CHOOSECOLS consecutivos (el segundo múltiple) y para combinarlos la sintaxis matricial de siempre { ... \ ... }. También podríamos haber hecho algo probablemente más inteligible como:

HSTACK(TEXTO(CHOOSECOLS(fila;1);"DD/MM/YYYY");CHOOSECOLS(fila;2;3))

La fecha se convierte a texto en el formato día/mes/año gracias la función TEXTO. Esto es necesario porque de no hacerlo así obtendríamos un valor numérico dado que la columna Fecha se ha definido como de tipo fecha en la Tabla (otra vez en mayúsculas) e internamente las fechas se almacenan como números.

Los tres valores de texto del vector fila se combinan en una cadena única de elementos separados por comas mediante TEXTJOIN.

⚠️ Cuidadín, porque si has configurado Google Sheets con los nombres de las funciones en español, no debes utilizar UNIRCADENAS como indica erróneamente la página de la ayuda oficial, sino el nombre original en inglés, TEXTJOIN.

El TEXTJOIN exterior, por su parte, se encargará de combinar las cadenas de texto obtenidas a partir de la fecha, tipo y resultado de cada registro de seguimiento en una única cadena multilínea. Esto se consigue utilizando el carácter Unicode 10 (salto de línea), que se invoca mediante la no demasiado conocida —debo decir— función CARACTER.

Finalmente, el SI.ERROR más exterior se utiliza para mostrar un mensaje (no disponible) cuando no se han encontrado acciones de seguimiento asociadas.

SI.ERROR(TEXTJOIN(CARACTER(10);VERDADERO;cadenas_resumen_seguimientos)

Y ahora es cuando hablamos de rarezas

Pues sí, la fórmula no tenía excesivo misterio. La extraordinaria sintaxis LAMBDA ha venido al rescate para ayudarnos a construir una fórmula razonablemente intuitiva, al menos cuando se juega con el indentando del código (algún día será automático 🙏)  para presentarla.

Pero las nuevas (y estupendas) tablas esconden alguna que otra peculiaridad. O mejor dicho, es la nueva sintaxis que podemos utilizar en las fórmulas que las manipulan quien parece esconderlas.

¿No me crees? Mira esto...

Es evidente que en las celdas B8 y D8, la expresión...

=Test[Valor 1]

...devuelve, tal y como sugiere la documentación oficial, todas las celdas de la columna cuya etiqueta de encabezado es Valor 1. Por esa razón, la fórmula en la celda D8 falla con el consabido error #VALUE! y es necesario envolverla con un ARRAYFORMULA para expandir el resultado, como hacemos en B8 del modo habitual cuando la expresión es matricial.

Sin embargo, al emplear este mismo estilo de referencia propia de las Tablas en la columna D, la fórmula se comporta exactamente del mismo modo que en otras herramientas que también trabajan con tablas y fórmulas, como las ya mencionadas AppSheet, Notion y Coda. Es decir, se utilizan los valores de las columnas referenciadas fila a fila, lo que por otra parte resulta natural, ¿no te parece?

De haberlo hecho más a la derecha, por ejemplo en la columna G, que no es contigua a la Tabla, hubiera pasado exactamente lo mismo. Y otro tanto hubiera ocurrido su hubiéramos insertado la fórmula a la izquierda de la tabla, claro.

☝ Esto pone en evidencia que las expresiones que contienen referencias a las columnas de una Tabla se comportan de un modo distinto cuando se utilizan en fórmulas situadas justo a la derecha o la izquierda de la misma, pero de la manera convencional cuando se encuentran por encima o debajo de ella. ¡Interesante!

Pero es que hay rarezas dentro de rarezas. Si esto es así, ¿por qué razón al tratar de explotar este nuevo y conveniente estilo de referencia en la fórmula utilizada en el apartado anterior para filtrar los seguimientos de una estancia específica la cosa fallaba?

FILTER(Seguimientos;Seguimientos[ID Estancia]=Estancias[ID])

Veamos, parto de que me cuesta justificar totalmente este comportamiento, pero yo diría que tiene que ver con el hecho de que estamos evaluando la condición de selección dentro de una función FILTER. La expresión de filtrado debe devolver una vector de valores VERDADERO o FALSO, tantos como filas tengamos en el intervalo de datos que deseamos filtrar.

En esta situación, el intérprete del lenguaje de fórmulas de Google Sheets entiende que la referencia Estancias[ID] no debe limitarse al valor de la columna ID de la fila en la que se encuentra la fórmula, sino que realmente deseamos referirnos a la columna completa, con todas sus filas.

Por ejemplo, en el caso de la fórmula de la fila 1 de la tabla de Estancias, la comparación fila a fila  arrojaría este resultado:

Eso supone que la fórmula solo recuperará el primer seguimiento de la tabla, en el que la condición se cumple (VERDADERO), por lo que el resultado será naturalmente el que te acabo de mostrar en la captura:

05/07/2024, Final, Positivo

Puedes construir tú mismo la tabla de comparación correspondiente a la estancia en la fila 2. Comprobarás que es idéntica y por tanto el resultado, el mismo.

En cambio, cuando utilizamos en la condición de filtrado una referencia a una celda única, que además se irá desplazando hacia abajo en la tabla cuando la fórmula se propaga verticalmente, su evaluación dentro de la expresión de filtrado sí arrojará el resultado deseado.

FILTER(Seguimientos;Seguimientos[ID Estancia]=F2)

☝ Debes recordar que las expresiones condicionales de selección dentro de la función FILTER se comportan de un modo similar a ARRAYFORMULA, comparando por tanto en este caso cada valor de la columna Seguimientos[ID Estancia] con el valor de la columna ID de una estancia específica.

Lo sé, un poco confuso. Pero es lo que hay 🤷‍♂️.

Pasan cosas aún más rarunas con las Tablas; si insertas una fórmula donde no debes, ¡se rompe!

Copia cualquier fórmula de la fila inferior de la Tabla, pégala en la misma celda, pero entrando en ella (doble clic o intro) para editarla. Aparecerá de la nada una inesperada doble línea en el borde superior en la fila y el cálculo fallará con error. Aunque la fórmula sea idéntica a las que quedan por arriba en la columna. Y solo en la última fila. Como rareza no está nada mal 😏.

Y espera un momento, porque aún hay más. Ahora es cuando te cuento una de esas cosas que pueden derretir tu cerebro. Y que además resultan prácticamente imposibles de verificar —no tenemos una máquina del tiempo— para determinar si realmente se te está yendo la pinza o es que algo ha cambiado subrepticiamente en Matrix 😵‍💫.

Verás, resulta que las expresiones que contienen referencias a datos que no están en una de esas nuevas y flamantes Tablas se comportan exactamente del modo descrito hace un momento por lo que hace la posición relativa de las fórmulas con respecto a los datos que manipulan. ¿Está ARRAYFORMULA a punto de morir?

Y mira bien, no solo eso, sino que al introducir fórmulas o información inmediatamente a la derecha de una tabla (esta vez con te minúscula), el formato aplicado a sus filas también se extiende naturalmente para acogerlas.

Aquí tienes la hoja de cálculo que he utilizado para realizar las capturas de pantalla del artículo. Para tu disección en privado.

👉 Resumir valores relacionados usando Tablas (y peculiaridades) 👈

¿Me estoy flipando absolutamente como consecuencia de mi ya avanzada edad, del calor veraniego, o de la extraordinaria combinación lineal de ambos factores? ¿Este comportamiento siempre ha sido el esperado?

¿O será que estos encuentros hojacalcúlicos en la tercera fase 👽 son realmente cosa nueva y apuntan a una posible convergencia entre las "nuevas" y las "viejas" tablas? 

Pues no sabría decirte (emoticono de señor que se encoge de hombros otra vez).

Pero lo que sí me parece evidente es que las Tablas han llegado a Google Sheets para quedarse, así que diles hola como se merecen y vete acostumbrando a ellas. Y a sus manías, por supuesto, para que ellas te correspondan. El amor exige dedicación y paciencia 💙.