Análisis comparativo de 3+1 estrategias para obtener submatrices de datos en Google Sheets

Construyamos y comparemos cuatro funciones con nombre para las hojas de cálculo de Google capaces de extraer un subconjunto rectangular de cualquier intervalo o matriz de datos utilizando diferentes técnicas.

Definiendo el problema

En noviembre de 2022 publiqué un proyecto denominado Horarios a Calendar (HaC), un artefacto basada en hojas de cálculo de Google y Apps Script cuya misión era facilitar la creación y administración de los horarios de clase de un centro educativo utilizando eventos recurrentes en Google Calendar. Tal vez lo recuerdes si me sigues habitualmente.

HaC permite diseñar los horarios de cada grupo en pestañas independientes de la hoja de cálculo. El usuario dispone las sesiones en el calendario semanal del grupo, HaC se encarga de extraer de él una lista de clases y finalmente se asignan instructores y espacios a cada una de ellas.

A continuación, en la hoja de gestión de eventos, el usuario selecciona un grupo y las clases establecidas para él aparecen en la tabla. Entonces tiene la posibilidad, si lo desea, de añadir sesiones adicionales de manera manual por debajo de las recuperadas automáticamente, antes de proceder a la generación de los eventos pertinentes en Google Calendar.

Esta capacidad de añadir información a una tabla, que en principio se rellena automáticamente a partir de un intervalo de datos de altura variable localizado en otra hoja, hizo que tuviera que diseñar una fórmula para recuperar únicamente las filas de la tabla horaria del grupo seleccionado que contuvieran sesiones.

Esta fórmula utilizaba (y sigue utilizando) dos funciones con nombre:

• FILA_CONTIENE_FORMULA. Localiza la primera fila de la tabla que contiene las sesiones de clase. Esta fila podría ser diferente en las distintas pestañas de los horarios de cada grupo dependiendo del número de franjas horarias diarias que se hayan dispuesto en cada uno de los horarios semanales.
• RECORTAR_FILAS_POSTERIOR. Se encarga precisamente de limitar por abajo el intervalo de datos, excluyendo las filas no utilizadas, antes de volcarlo sobre la tabla del panel de generación de eventos.

=SI(
  ESBLANCO(C3);
  {"Clase"\"Días"\"Hora inicio"\"Hora fin"\"Instructor"\"Aula"};
  SI.ERROR(
    {
      "Clase"\"Días"\"Hora inicio"\"Hora fin"\"Instructor"\"Aula";
      RECORTAR_FILAS_POSTERIOR(
        INDIRECTO(C3 & "!B" & FILA_CONTIENE_FORMULA(INDIRECTO(C3 & "!B9:B");
        "EXTRAEREVENTOS(";1;VERDADERO) & ":G"))
    };{"Clase"\"Días"\"Hora inicio"\"Hora fin"\"Instructor"\"Aula"}))

La función con nombre RECORTAR_FILAS_POSTERIOR tiene una implementación relativamente sencilla y compacta. Se apoya en la función ARRAY_CONSTRAIN para descartar las filas que están totalmente en blanco. También en una pequeña pirueta para identificar la última fila del intervalo que contiene datos en la que interviene la función SEQUENCE. No te preocupes por ella ahora, no viene demasiado al caso.

=ARRAY_CONSTRAIN(
  intervalo;
  MAX(ArrayFormula(SI(intervalo<>"";SEQUENCE(FILAS(intervalo));0)));
  COLUMNAS(intervalo))

Sí, podría haber utilizado una expresión de filtrado más convencional basada en FILTER o QUERY, pero puesto que únicamente deseaba descartar una fila cuando todas sus celdas estuvieran vacías, opté por hacerlo de este modo, indudablemente un tanto más aparatoso.

La tal vez poco conocida función nativa ARRAY_CONSTRAIN se utiliza para limitar el número de filas y columnas de un intervalo o matriz, produciendo un efecto de recorte por abajo (en filas) y por la derecha (columnas), pero siempre comenzando a incluirlas desde su fila superior y columna izquierda.

☝ En lo que sigue denominaré intervalo a una celda o rango de celdas de la hoja de calculo, en tanto que emplearé los términos vector o matriz para referirme a cualquier estructura de datos, uni o bidimensional, que se obtiene como resultado de la evaluación de una expresión matricial en el contexto de una fórmula. La diferencia parece sutil pero resultará crucial a lo largo del artículo.

Por ejemplo:

A1
B3:B20
A2:F

FILTER(A2:D10;A2:A10="CFGS")
{1;2;3;4}
{1\2\3\4;5\6\7\8;9\10\11\12}

Creo que escenarios de uso similares al descrito son relativamente habituales cuando se trabaja con datos tabulares en una hoja de cálculo. Por esta razón, me parece interesante disponer de un mecanismo sencillo que permita seleccionar con total libertad una submatriz arbitraria de un intervalo o matriz de datos. 

Y es precisamente este convencimiento el que motiva el artículo que tenemos entre manos.

Los requerimientos de diseño que propongo son los siguientes:

1. Partiendo de un intervalo o matriz de datos de entrada arbitrario, deseamos extraer la submatriz compuesta por ciertas filas y columnas contiguas.
2. La selección se realizará definiendo un subconjunto rectangular caracterizado por los parámetros siguienes: fila inicial, columna inicial, número de filas y número de columnas.
3. Los índices de filas y columnas se indicarán como números positivos desde el 1, en orden creciente de arriba a abajo (filas) e izquierda a derecha (columnas).
4. Se limitará el tamaño de la matriz devuelva cuando los valores anteriores excedan el tamaño del intervalo o matriz de datos de entrada.
5. Si los índices de fila o columna inicial se encuentran fuera de el intervalo o matriz no se devolverá ningún resultado.
6. Para que el resultado sea transferible y cómodo de utilizar en nuestros tinglados hojacalcúlicos, la implementación se realizará mediante las fascinantes funciones con nombre, de las que ya he hablado en varias ocasiones en este espacio, por ejemplo aquí y también —comparándolas con las funciones personalizadas Apps Script—  aquí. Bautizaremos nuestra función con nombre como SUBMATRIZ.

Pongámonos pues manos a la obra raudos y veloces.

Versión 1 | INDICE

Esta es probablemente la versión más inmediata, que sin embargo no está exenta de cierta idea feliz.

Seguramente ya conoces la función INDICE. Si utilizas las fórmulas de Google Sheets en español puedes escribir INDICE o INDEX indistintamente, aunque la ayuda contextual al introducir el nombre de la función solo aparecerá en el primer caso).

Con ella podemos obtener el valor almacenado en la fila y columna indicada de un intervalo (recuerda, rango de celdas) o matriz arbitraria. También es capaz de devolver todos los valores de la fila o columna seleccionada cuando se omite el indicador de columna o fila, respectivamente.

Me parece importante destacar, aunque la ayuda oficial no lo indique claramente, que INDICE no solo funciona con referencias a intervalos de celdas, sino que también podemos usarla sobre matrices construidas mediante expresiones, esto es, matrices calculadas:

INDICE({1;2;3;4};2) → 2
INDICE({1\2\3\4;5\6\7\8;9\10\11\12};;1) → {1;5;9}

Pero esta interesante capacidad no serviría de gran cosa por sí misma puesto que para alcanzar nuestro objetivo precisamos extraer un intervalo de filas o columnas.

Afortunadamente la función INDICE tiene otro as en la manga. Si usamos dos de ellas, separadas por dos puntos [:], podemos designar un intervalo compuesto por múltiples filas y columnas. Por ejemplo:

INDICE(B2:G8;2;2):INDICE(B2:G8;4;5) → contenido celdas C3:F5

Veamos la implementación de la primera versión de nuestra función con nombre.

SUBMATRIZ_V1(intervalo; fila; columna; num_filas; num_columnas)

=LET(

  filaFinal;SI(
    fila+num_filas-1<=FILAS(intervalo);
    fila+num_filas-1;
    FILAS(intervalo));

  columnaFinal;SI(
    columna+num_columnas-1<=COLUMNAS(intervalo);
    columna+num_columnas-1;
    COLUMNAS(intervalo));

  SI.ERROR(
    INDICE(intervalo;fila;columna):INDICE(intervalo;filaFinal;columnaFinal)))

⚠️ Ten mucho cuidado con los nombres que les asignas a los parámetros de las funciones que crees (aquí intervalo, fila, columna, num_filas y num_columnas, su propósito no precisa de aclaración alguna). Si alguno de ellos coincide con el de una función nativa del lenguaje de fórmulas de Google Sheets también utilizada en su definición, comprobarás que la cosa falla. Y además lo hará silenciosamente, sin mediar mensaje de error alguno, si capturas los errores, lo que puede causar aún más confusión.

En este caso, mi primer impulso fue utilizar los nombres filas y columnas para parametrizar el tamaño de la submatriz a devolver por la función con nombre, pero esto colisiona con los nombres de las funciones internas FILAS y COLUMNAS. ¡Maaaal! 👎

Las estructuras condicionales SI gemelas se utilizan para limitar el tamaño de la submatriz resultante de modo que no exceda el de la matriz sobre la que se está operando. Esto será algo que mantendremos en el resto de versiones de la función con nombre que iremos desarrollando en los siguientes apartados.

Con la función LET se definen nombres temporales (lo que vienen a ser las variables en un lenguaje de programación clásico) para los índices de las filas y columnas finales a extraer (filaFinal y columnaFinal, respectivamente). Realmente solo las vamos a utilizar una vez, en la expresión INDICE:INDICE que opera sobre el intervalo de datos entrada. No obstante, ten en cuenta que no solo querrás tirar de LET para capturar el resultado de una expresión complicada y evitar su recálculo, sino también para que todo quede más claro en tus fórmulas.

☝ Por cierto, ¿sabías que LET también sirve para definir funciones privadas con parámetros?

El SI.ERROR con el que envolvemos la expresión de doble INDICE, que es la que realmente genera la submatriz, se utiliza para tratar el caso en el que la fila o la columna inicial se encuentra fuera de rango. Esto será también una constante en las versiones posteriores.

❌ Esta implementación de nuestra función canónica, que explota una característica inusual de la función INDICE, es buena, bonita y barata. Pero sin embargo no cumple con los requisitos que hemos planteado.

¿Por qué?

Veamos qué pasa cuando se utiliza una matriz calculada, en lugar de una referencia a un intervalo de celdas, como conjunto de datos de entrada. He eliminado el SI.ERROR, porque de lo contrario la función parece simplemente no devolver ningún resultado.

SUBMATRIZ_V1({1\2\3\4;5\6\7\8;9\10\11\12};2;2;2;1) → #N/A

Y es que esta sintaxis INDICE:INDICE tan sorprendente, si me permites, realmente representa una referencia a un intervalo de celdas, un rango. Y lo que le hemos pasado a la función no lo es en este caso.

☝ Una alternativa, digamos que un tanto menos oscura, a este abuso de la función INDICE consiste en tirar en su lugar de la función nativa denominada DESREF (en la documentación oficial en español aparece como OFFSET, que también puede utilizarse, aunque sin ayuda contextual, pero es reemplazada automáticamente por la primera). El problema es el mismo: funciona sobre un intervalo de celdas, pero no sobre matrices de datos genéricas.

Vamos a tener que esforzarnos un poco más. Afortunadamente nos quedan muchas opciones.

Versión 2 | MAKEARRAY

La segunda propuesta que te presento es la que probablemente considero más atractiva e inspiradora, aunque seguramente no sea la más eficiente.

En este caso vamos a tirar de la formidable sintaxis LAMBDA, introducida en Google Sheets en agosto de 2022. De manera más específica, recurriremos a su sugerente función auxiliar MAKEARRAY.

MAKEARRAY tiene un funcionamiento de lo más interesante. Se le pasan como parámetros dos valores numéricos que determinarán el tamaño (filas y columnas) de la matriz que nos devolverá y una función LAMBDA, parametrizada con ellos, que utilizaremos para iterar sobre cada elemento de la matriz y establecer su valor. Por ejemplo:

                                                   { 2 3 4
MAKEARRAY(3;3;LAMBDA(fila;columna;fila+columna)) →   3 4 5
                                                     4 5 6 }

Puedes utilizar esta estrategia de construcción iterativa del resultado en diferentes escenarios en los que otras funciones se quedan cortas. ¡El cielo es el límite!

Tenemos un conjunto de datos de entrada. Conocemos también  cuáles son la fila y columna iniciales. Y, sobretodo, sabemos de antemano cuál es el tamaño de la submatriz resultante.

Solo nos separan unas pocas líneas de código —concédeme que denomine «código» a las fórmulas de las hojas de cálculo— para mapear el resultado sobre la submatriz de salida a partir de la de entrada a través de un desplazamiento elemental de coordenadas.

Pero el movimiento se demuestra andando:

SUBMATRIZ_V2(intervalo; fila; columna; num_filas; num_columnas)

=SI.ERROR(

  MAKEARRAY(
  
    SI(
      fila+num_filas>FILAS(intervalo);
      FILAS(intervalo)-fila+1;
      num_filas);
    
    SI(
      columna+num_columnas>COLUMNAS(intervalo);
      COLUMNAS(intervalo)-columna+1;
      num_columnas);
    
    LAMBDA(f;c;INDICE(intervalo;f+fila-1;c+columna-1))))

✅ No sé qué pensarás tú, pero personalmente esta implementación basada en un proceso iterativo facilitado por MAKEARRAY me parece una solución muy hermosa, que además cumple con todos nuestros requisitos.

=SUBMATRIZ_V2({1\2\3\4;5\6\7\8;9\10\11\12};2;2;2;1) → {6;10}

Versión 3 | CHOOSECOLS/ROWS + SEQUENCE + REDUCE

De haber escrito este artículo antes de febrero de 2023, seguramente este sería el apartado de comentarios finales y siguientes pasos.

Pero es que el 1 de febrero de 2023, el equipo de desarrollo de Sheets nos hacia otro regalazo. Once nuevas funciones que venían a hacerle la vida considerablemente más fácil a todo friqui de las hojas de cálculo de Google.

Entre ellas se encontraban CHOOSEROWS y CHOOSECOLS.

Sus nombres son absolutamente esclarecedores. Les pasas un intervalo o matriz de datos (soportan los segundos de manera nativa), a continuación una lista de índices de fila o de columna (respectivamente) separados por puntos y comas [;]  y obtendrás a cambio una submatriz compuesta únicamente por las filas o columnas extraídas.

CHOOSEROWS(matriz; núm_fila1; [núm_fila2])
CHOOSECOLS(matriz; num_col1; [num_col2])

Sí, son algo así como un INDICE que opera sobre filas o columnas completas. Aunque me parecen ambas más intuitivas. Y con algunos superpoderes especiales.

¿Y cómo podemos emular la particular —pero parcialmente fallida—  expresión INDICE:INDICE? Muy fácil. ¡Combinando ambas!

CHOOSEROWS(CHOOSECOLS({1\2\3\4;5\6\7\8;9\10\11\12};2);2;3) → {6;10}

Ya tenemos todo lo que necesitamos para montar la tercera versión de nuestra función.

SUBMATRIZ_V3(intervalo; fila; columna; num_filas; num_columnas)

=LET(

  listaFilas;SEQUENCE(
    SI(
      fila+num_filas-1<=FILAS(intervalo);
      num_filas;
      FILAS(intervalo)-fila+1
    );1;fila;1);

  listaColumnas;SEQUENCE(
    SI(
      columna+num_columnas-1<=COLUMNAS(intervalo);
      num_columnas;COLUMNAS(intervalo)-columna+1
    );1;columna;1);

  matrizPorFilas;SI.ERROR(
    REDUCE(;listaFilas;LAMBDA(resultado;filaMatriz;
      SI(
        resultado="";
        CHOOSEROWS(intervalo;filaMatriz); 
        {resultado;CHOOSEROWS(intervalo;filaMatriz)}))));
  
  SI.ERROR(
    REDUCE(;listaColumnas;LAMBDA(resultado;columnaMatriz;
      SI(
        resultado="";
        CHOOSECOLS(matrizPorFilas;columnaMatriz);
        {resultado\CHOOSECOLS(matrizPorFilas;columnaMatriz)})))))

Nuevamente recurrimos a LET para definir una serie de variables temporales en el contexto de la función con nombre y tratar así de que el código resulte más fácil de digerir.

En las variables listaFilas y listaColumnas se almacenan sendos vectores que enumeran los índices de las filas y columnas, respectivamente, comprendidas entre la inicial y la final, de acuerdo con los parámetros de la función. Estas listas de índices se generan mediante SEQUENCE. 

La idea feliz surge al definir matrizPorFilas. Lo que hacemos aquí es aplicar repetidamente CHOOSEROWS sobre el parámetro de entrada intervalo, que contiene la matriz original, para seleccionar, una a una, las filas que deseamos extraer. En el lenguaje de fórmulas de Google Sheets no hay bucles explícitos, pero de nuevo las funciones LAMBDA vienen al rescate. En este caso particular lo hará la enormemente versátil REDUCE.

☝ De manera muy resumida y un tanto imprecisa, REDUCE realiza una serie de operaciones sobre los elementos de una matriz y devuelve un resultado final que ha ido construyendo en el transcurso del proceso. Puede tratarse de un simple valor numérico o alfanumérico, pero también de un nuevo vector o matriz.

Cada ciclo en el que se aplican una serie de operaciones sobre uno de los elementos de la matriz de entrada constituye una iteración, un término que procede del campo de la algoritmia, pero que creo es plenamente aplicable en este contexto.

En nuestra implementación, esta función recibe tres parámetros:

• Un valor inicial, que en este caso simplemente omitimos escribiendo un punto y coma [;].
• La lista de filas a extraer, que se han guardado previamente en la matriz unidimensional (vector) listaFilas.
• La función LAMBDA que realiza el cálculo iterativo a partir de estos elementos. Esta función LAMBDA, a su vez, define otros tres parámetros:
  • Un acumulador (resultado), que se va actualizando en cada iteración.
  • Una variable (filaMatriz) que contendrá el índice de la fila que se está procesando en cada iteración.
  • Una expresión, que será evaluada tantas veces como elementos haya dentro de listaFilas, y podrá utilizar los valores actuales de filaMatriz y resultado a la hora de realizar sus operaciones.

El SI en el interior de la sección LAMBDA se emplea para diferenciar la primera iteración del resto. Aquí se toma la decisión de simplemente guardar la fila extraída en la variable acumulador (resultado) cuando este está vacío, acumulador que se arrastrará durante todo el proceso iterativo, o de añadir la fila extraída a las anteriores ya almacenados en la variable resultado.

Por tanto, cuando el primer REDUCE finalice, lo que nos encontraremos dentro de la variable matrizPorFilas será una submatriz que únicamente contendrá las filas del conjunto de datos de entrada que deseábamos obtener.

Solo nos quedará ahora por realizar un proceso análogo para seleccionar las columnas pertinentes (vector listaColumnas), pero en lugar de aplicar CHOOSECOLS sobre el parámetro de entrada intervalo, lo haremos sobre la variable matrizPorFilas, que como ya sabemos contendrá la submatriz parcial de filas obtenida en el paso anterior.

Cuesta más decirlo que hacerlo 😅.

✅ Esta tercera versión de nuestra función SUBMATRIZ se basa en las funciones CHOOSECOLS y CHOOSEROWS, pero necesita apoyarse también en SEQUENCE para generar los vectores de índices de columnas y filas y, muy especialmente, en un proceso iterativo facilitado por REDUCE que tal vez de entrada te resulte un poco marciano. No obstante, dado que también cumple con todos los requisitos de diseño establecidos inicialmente, le ofrecemos unabrazo y la damos por buena.

La función auxiliar REDUCE es ciertamente peculiar hasta que interiorizas su funcionamiento... tras haberla usado una docena de veces tal vez. Pero créeme cuanto te digo que te va a compensar hacer el esfuerzo de encajar tus procesos mentales de diseño de fórmulas en su filosofía de trabajo. Como MAKEARRAY, es extraordinariamente versátil.

Pero no te vayas todavía que aún no hemos terminado.

Versión 4 | CHOOSECOLS/ROWS + SEQUENCE

A veces hay que leer la documentación oficial con la mente abierta. La de CHOOSEROWS dice esto, por ejemplo:

Pero a veces, como dice la canción, la vida te da sorpresas, sorpresas te da la vida. Eso pasa, por ejemplo, con las funciones BUSCARV/H, que esconden una inesperada sorpresa...

Inspirado precisamente por esta circunstancia, se me ocurrió intentar abusar de CHOOSEROWS de un modo parecido:

¡Bingo! Resulta que la lista de columnas que le pasamos a esta función no tiene por qué limitarse a una colección de índices numéricos separados por [;], como establece su página de ayuda. También podemos utilizar expresiones matriciales, que podemos combinar libremente con índices numéricos convencionales, siempre separados por punto y coma, por supuesto.

Vale, ¿y en qué mejora esto a nuestra pizpireta función SUBMATRIZ_V3?

Pues en que ya no necesitamos iterar para ir extrayendo fila a fila primero y columna a columna a después. Podemos obtener la submatriz de filas de una vez y a continuación la final obteniendo las columnas deseadas a partir de ella. ¡Sin usar REDUCE!

Veamos cómo queda la cosa:

SUBMATRIZ_V4(intervalo; fila; columna; num_filas; num_columnas)

=LET(

  listaFilas;SEQUENCE(
    SI(
      fila+num_filas-1<=FILAS(intervalo);
      num_filas;
      FILAS(intervalo)-fila+1
    );1;fila;1);

  listaColumnas;SEQUENCE(
    SI(
      columna+num_columnas-1<=COLUMNAS(intervalo);
      num_columnas;COLUMNAS(intervalo)-columna+1
    );1;columna;1);

  SI.ERROR(CHOOSECOLS(CHOOSEROWS(intervalo;listaFilas);listaColumnas)))

✅ Ahora ya me cuesta decidirme por la V2 (MAKEARRAY) o esta V4 con CHOOSEROWS/COLS simplificada. El hecho de que podemos prescindir de las dos estructuras iterativas REDUCE simplifica notablemente el código de la función, cuya secuencia de trabajo es ahora más intuitiva y natural, al tiempo que probablemente más eficiente tanto que la versión 3 como la versión 2.

Igual un día de estos me animo a portar mi función personalizada Apps Script HDCP_CHOOSECOLSROWS, que por cierto está incorporada en mi complemento HdC+, aprovechando este pequeño hallazgo.

Comentarios finales y siguientes pasos

Ahora sí que hemos llegado al final de este segundo artículo estival —escribo esto a 9 de agosto 🏖️— sobre mis queridas hojas de cálculo de Google.

En esta hoja de cálculo encontrarás las cuatro versiones de la función con nombre SUBMATRIZ que hemos desarrollado juntos.

👉 Fx con nombre SUBMATRIZ (3 + 1 versiones) 👈

Una de las cosas que quería transmitirte con este artículo es que las funciones con nombre, la sintaxis LAMBDA y ciertas funciones que llegaron posteriormente y que hemos usado en esta ocasión como LET, CHOOSEROWS / CHOOSECOLS, pero también otras como WRAPROWS / WRAPCOLS, o HSTACK / VSTACK a las que no hemos recurrido hoy, han elevado sensiblemente el nivel de abstracción del lenguaje de fórmulas de Google Sheets, facilitando de manera notable la creación de soluciones más modulares y reutilizables.

Si he logrado este objetivo, aunque solo sea parcialmente, me doy por satisfecho.

Familiarízate con todo este universo de nuevas posibilidades y haz estas herramientas tuyas cuanto antes. Te ayudarán no solo a reducir la complejidad de tus hojas de cálculo, sino también a mantenerlas a lo largo del tiempo con un menor esfuerzo. Tu yo futuro seguro que te lo agradecerá.

¡Hasta la próxima! 👋