Concepto de fórmula matriz o array formula y uso de la función ARRAYFORMULA

Supongamos que en EXCEL tenemos el rango C1:C3 con los números 1,2,3. Y en la columna de al lado tenemos el rango D1:D3 con los valores 4,5,6. Buscamos una fórmula que rellene la siguiente columna con la suma de sus filas, es decir, el rango E1:E3 debe contener la suma C1+D1, C2+D2 y C3+D3. Para ello en EXCEL basta con incluir la fórmula C1:C3+D1:D3 en la primera celda de la tercera columna, rellenándose automáticamente esas sumas en la columna. Esto se observa en la Figura ejecutándolo en una hoja de EXCEL.

Esta forma de operar con rangos (matrices o arrays) es lo que se conoce como una fórmula de matriz o array formula en inglés.

Las celdas donde se extenderán los resultados deben estar vacías. En la Figura vemos que la celda E2 contiene la cadena "abcd". Esto origina un error que EXCEL denomina de desbordamiento.

Para ejecutar lo mismo en GoogleSheet hemos de usar la función ARRAYFORMULA(C1:C3+D1:D3), como se observa en la Figura.

Si una celda donde se van a extender los resultados tiene contenido, en GoogleSheet también acusa un error. En este caso es más descriptivo indicando que la matriz de resultados no se ha podido extender porque se sobrescribirían los datos de la celda E2.

Esta característica no está disponible en WXTABLE. Si ejecutamos el ejemplo anterior con los valores en los rangos equivalentes en WXTABLE con [R1C3]:[R3C3]+[R1C4]:[R3C4] obtendremos el resultado 1,2,7,5,6. Esto es porque WXTABLE envía un rango al módulo CALC como una lista de valores separados por comas. Asi que la expresión que recibe CALC sería 1,2,3+4,5,6. Esto es un expresión válida en CALC, calculando cada subexpresión entre comas y devolviendo el resultado 1,2,7,5,6 también separado por comas. Esta forma de calcular las expresiones no es posible en XLSX.

Y por otro lado una matriz literal o un array formula no son posibles en WXTABLE. Pero vamos a usarlo para traducir ciertas funciones que admiten argumentos como matrices.

Matrices literales y argumentos matriz en funciones XLSX

En WXTABLE La función sort(ascDesc, listaValores) permite ordenar una lista de valores según que argumento ascDesc sea "asc" ascendente o "desc" descendente. Tal como se observa en la Figura, pasando sort("asc", [R1C3]:[R4C3]) devuelve la lista ordenada.

En lugar del rango podemos pasar una lista de valores, como se observa en la Figura usando la fórmula sort("asc", 5, 1, 3, 2). Vamos a ver como podemos traducir estos dos casos en XLSX.

En XLSX la función SORT(rango, col1, asc1, col2, asc2, col3, asc3, ...) permite ordenar un rango con una o más columnas, pudiendo ordenar cada columna (índices columna en col1, col2, ...) por orden ascendente o descendente (asc1, asc2, ...). Si sólo pasamos SORT(rango) se ordena por la primera columna ascendente.

En la Figura se observa una ejecución en GoogleSheet con la fórmula SORT(C1:C4), funcionando igual en EXCEL. Es una función que admite una matriz como argumento y devuelve una matriz con el resultado. Como comentamos en el tema anterior, las celdas donde se extenderán los resultados deben estar vacías.

En XLSX es posible representar una matriz literal de valores (o array literal) como {1,2;3,4;5,6} separando con punto y coma las filas y con comas las columnas. En la Figura se observa la fórmula ={1\2;3\4;5\6} ejecutada en GoogleSheet, donde las comas se deben poner con barras invertidas debido a un problema de confusión con la posible coma decimal en los valores cuando se usa idioma español en la aplicación, pero en el XLSX se almacenan con comas.

La fórmula se inserta en una celda y el resultado se extiende a las celdas adyacentes hacia la derecha y abajo, que como ya hemos dicho, celdas que han de estar vacías.

Usando el array literal, podemos ejecutar el ejemplo que estamos viendo SORT({5;1;3;2}) obteniéndose el mismo resultado, sin necesidad de tener un rango explícito de valores en la hoja de cálculo.

Así que la traducción a XLSX de sort("asc", [R1C3]:[R4C3]) sería SORT(C1:C4, 1, TRUE) y la de sort("asc", 5, 1, 3, 2) sería SORT({5;1;3;2}, 1, TRUE), donde hemos agregado a las fórmulas XLSX dos argumentos, pues se ordena por la primera y única columna (segundo argumento índice 1) y es ascendente (tercer argumento TRUE). Estos argumentos no serían necesarios pues son los valores por defecto, pero los incluimos para generalizar el ejemplo.

Pero esa ejecución debe devolver una lista de valores y no una matriz para que sea coherente con la que devuelve WXTABLE. Así que necesitamos convertirla con TEXTJOIN(",", FALSE, SORT({5;1;3;2}, 1, TRUE)). La ejecución en GoogleSheet se observa en la Figura para el caso de una lista de valores. Para un rango la fórmula traducida sería TEXTJOIN(",", FALSE, SORT(C1:C4, 1, TRUE)).

Ya hemos comentado los esquemas de fórmula que usamos en el módulo CALC para traducir funciones a XLSX. En este caso en el JavaScript del módulo CALC tenemos esta definición del esquema:

xlsx: {
    formula: '_xlfn.TEXTJOIN(",",FALSE,_xlfn.SORT({[1...]},1,IF([0]="asc",1,0)))',
    join: ";",
    type: "str"
}

Vea que usamos el prefijo _xlfn para ambas funciones TEXTJOIN y SORT, motivo que ya hemos comentado. Recuerde que lo función de WXTABLE es sort("asc", [R1C3]:[R4C3]) para la versión con un rango y sort("asc", 5, 1, 3, 2) para una lista de valores. Así que el esquema [0] es el primer argumento "asc". El tercer argumento de SORT es un IF que devuelve un 1 equivalente a TRUE si es "asc" y un 0 equivalente a FALSE en otro caso.

En cuanto al primer argumento, la lista de valores, el esquema es {[1...]}. Ya hemos visto que [1...] recupera la lista de argumentos de sort("asc", [R1C3]:[R4C3]) cuando es un rango o bien sort("asc", 5, 1, 3, 2) cuando es una lista de valores, recuperación que se inicia en la posición 1, siempre numerándose desde 0. Así que el módulo CALC recupera en un Array de JavaScript ["[R1C3]:[R4C3]"] cuando es un rango, entrecomillándose siempre los rangos o referencias pues CALC no permite expresiones con corchetes. Y [5, 1, 3, 2] cuando es una lista de valores. Y a continuación el esquema de corchetes {} convierte ese Array ["[R1C3]:[R4C3]"] o [5, 1, 3, 2] concatenándolo con un punto y coma, tal como expone la propiedad join, ";" del objeto esquema, quedando finalmente "{5;1;3;2}" cuando es una lista de valores y "[R1C3]:[R4C3]" cuando es un rango, omitiéndose las llaves {} en el caso de rangos. El rango entrecomillado será traducido a XLSX posteriormente en el módulo exportador.

En la Figura puede ver la ejecución de la traducción de SORT usando un rango. En la columna E también hemos incluido la fórmula con una lista de valores. Este ejemplo puede ejecutarlo en formato WXTABLE usando los datos de este enlace sort.txt y su traducción a XLSX en sort.xlsx.

En la hoja de datos del XLSX se declaran las dos fórmulas:

<c r="D1" t="str" s="0">
    <f>_xlfn.TEXTJOIN(",",FALSE,_xlfn.SORT($C$1:$C$4,1,IF("asc"="asc",1,0)))</f>
    <v>1,2,3,5</v>
</c>
<c r="E1" t="str" s="0">
    <f>_xlfn.TEXTJOIN(",",FALSE,_xlfn.SORT({5;1;3;2},1,IF("asc"="asc",1,0)))</f>
    <v>1,2,3,5</v>
</c>

Matrices de dos dimensiones en argumentos de funciones WXTABLE

En el módulo CALC y, por extensión, en WXTABLE, sólo se permiten valores primitivos como argumentos de una función. Es decir, números, cadenas y booleanos. No es posible pasar como argumento una matriz (o array). Cuando necesitamos pasar una lista de valores, que viene a ser una matriz de una dimensión, lo que hacemos es portarla como los argumentos finales de la función. Así sort("asc", 5, 1, 3, 2) tiene un primer argumento que indica el orden y el resto de argumentos se trata como una lista de valores: una matriz o array de una dimensión.

Tanto en XLSX como en otras tecnologías esto es algo usual. Por ejemplo, en XLSX la función MAX(argumentos) devuelve el máximo de sus argumentos. En JavaScript tenemos algo similar Math.max(argumentos). Y en CALC también tenemos max(argumentos). Si se pasa un rango desde WXTABLE a CALC para su cálculo, se convierte previamente en WXTABLE el rango en una lista de argumentos. Así que CALC siempre recibe para ejecutar el cálculo los argumentos como valores simples: números, cadenas o booleanos. Esta forma de actuar facilita enormemente el proceso de cálculo en CALC.

Sin embargo es posible tratar en CALC una matriz de dos dimensiones para las siguientes funciones en WXTABLE, donde podemos ver que hay versiones de funciones de una dimensión con otras equivalentes para dos dimensiones que acaba en "data":

Vamos a ver el caso de sortdata(ascDesc, index, cols, val1, val2, val2, ...) en WXTABLE, como se observa en la Figura. La función sortdata("asc", 1, 3, [R1C1]:[R3C3]) es parecida a la versión corta sort("asc", [R1C1]:[R3C3]) de una dimensión. Encontramos el primer argumento para ordenar "asc" o "desc". El argumento index nos dice que columna servirá como índice para ordenar, teniendo en cuenta que se enumeran desde cero. Nos dice que se ordene ascendente por la segunda columna, pues index es 1. Y todo lo que viene a continuación es la matriz de dos dimensiones con los valores. El argumento cols nos dice el número de columnas que tiene la matriz, en este caso 3. Los argumentos que vienen a continuación son la lista de valores que se obtienen de la matriz procedente del rango [R1C1]:[R3C3]:

Si ordenamos las filas por la segunda columna ascendente tendríamos esto:

Como en CALC no se pueden recibir rangos cuando se ejecuta el cálculo, la función sortdata("asc", 1, 3, [R1C1]:[R3C3]) en WXTABLE se envía a CALC como sortdata("asc", 1, 3, "A", "3", "D", "B", "2", "E", "C", 1, "F") extrayendo la lista de valores "A", "3", "D", "B", "2", "E", "C", 1, "F" del rango [R1C1]:[R3C3], realizando un propagado por filas.

Esta estructura cols, listaValores, donde la lista propaga por filas una matriz, es la que se utiliza en las funciones de la tabla anterior filterdata, searchdata, sortdata y sumdata. La función getdata toma una lista propagada por columnas y la convierte en una propagada por filas, que a su vez nos puede servir de entrada de valores para las otras funciones "data".

El resultado de la función sortdata("asc", 1, 3, [R1C1]:[R3C3]) es a su vez una lista propagada por filas también "C,1,F,B,2,E,A,3,D" devolviéndose en un String de valores separados por comas. Este resultado más el valor del argumento cols podría servir como estructura de datos de una matriz propagada por filas que sirva de entrada para otra función "data".

Esquema de traducción a XLSX de la función sortdata()

El esquema de traducción a XLSX de la función sortdata es parecido al de sort() que vimos en un apartado anterior:

xlsx: {
    formula: '_xlfn.TEXTJOIN(",",FALSE,_xlfn.SORT({[3:2...]},[1]+1,IF([0]="asc",1,0)))',
    join: ";",
    type: "str"
}

Supongamos que la función en WXTABLE no usa rangos sino la propia lista de valores sortdata("asc", 1, 3, "A", 3, "D", "B", 2, "E", "C", 1, "F") y vamos a usar CALC no para realizar el cálculo sino para traducir esa fórmula a XLSX. El segundo argumento de SORT tiene por esquema [1]+1, puesto que el esquema [1] recupera el argumento con índice 1 (contando desde cero) que es el valor 1. Hemos de sumarle 1 puesto que en XLSX el índice de ordenamiento se inicia en uno. El tercer argumento para "asc" o "desc" es igual que el que vimos en el apartado anterior, usando [0] que es el argumento con índice 0 cuyo valor es "asc". Veamos el primer argumento, la lista de valores.

El esquema de la lista de valores es ahora {[3:2...]}. En [3:2...] el número 3 indica que los valores empiezan en el argumento con índice 3 (contando desde cero), es decir, empiezan en el argumento con valor "A". Mientras que el número 2 indica el valor del argumento con índice 2, es decir, el valor 3 que indica el número de columnas que tiene cada fila propagada. Con estos datos CALC construye un Array de JavaScript de 2 dimensiones, con filas conteniendo 3 columnas. Y dado que el esquema contiene llaves {}, pasa ese array a un String usando el método join de Array en JavaScript con el separador "," para las columnas y ";" para las filas. Este último se indica en la propiedad join: ";" del objeto esquema anterior.

El ejemplo que estamos viendo puede ser importado en WXTABLE usando los datos del enlace sortdata.txt. Y puede verlo traducido a XLSX en el enlace sortdata.xlsx. Los datos de la celda con la fórmula en el archivo XLSX quedan así:

<row r="4" hidden="0">
    <c r="D4" t="str" s="0">
        <f>_xlfn.TEXTJOIN(",",FALSE,_xlfn.SORT({"A",3,"D";"B",2,"E";"C",1,"F"},1+1,IF("asc"="asc",1,0)))</f>
        <v>C,1,F,B,2,E,A,3,D</v>
    </c>
</row>

Observe la matriz literal {"A",3,"D";"B",2,"E";"C",1,"F"} donde las filas se separan con ";" y las columnas con ",". En la Figura puede ver una captura de ejecución en GoogleSheet, donde las comas se sustituyen con la barra invertida para evitar posible confusión con la coma decimal de los números en español.

En la Figura puede ver la función usando un rango con los mismos valores y ejecutada en EXCEL. Los datos de la fórmula resultante en el XLSX son los siguientes:

<c r="D1" t="str" s="0">
    <f>_xlfn.TEXTJOIN(",",FALSE,_xlfn.SORT($A$1:$C$3,1+1,IF("asc"="asc",1,0)))</f>
    <v>C,1,F,B,2,E,A,3,D</v>
</c>

Ejemplo de uso de la función searchdata de WXTABLE

Veamos un ejemplo de aplicación práctica de la función de WXTABLE searchdata( valSearch, colSearch, colReturn, cols, listValues ), donde los argumentos cols, listValues representan una matriz compuesta por filas con un ancho de cols columnas. La función busca el primer valor valSearch en las filas de la columna colSearch, devolviendo el valor de la celda en la posición de esa fila y columna colReturn.

El ejempo facturas.txt en formato WXTABLE y su traducción a XLSX en facturas.xlsx contiene una aplicación práctica de una plantilla para editar una factura.

El libro dispone de varias hojas, una de ellas contiene la tabla de clientes. La primera columna contiene el identificador del cliente y en la segunda el nombre, tal como se observa en la Figura.

La hoja principal es la de la factura, Figura. En la celda [R5C4] disponemos de un desplegable que permite elegir identificadores de la tabla de clientes. Esto es un control que veremos en un tema posterior. Al seleccionar un identificador de cliente se actualiza automáticamente los campos de nombre, dirección y CIF. Vease la fórmula que incluye searchdata([r0c-1], 1, 2, 2, [clientes!R1C1]:[clientes!RMC2]). La referencia [r0c-1] es relativa y apunta a [R5C4].

El resultado en GoogleSheet se muestra en la Figura. A excepción de algunos detalles que no son traducibles, como el formato 0001/20 que agrega una barra derecha y los dos dígitos de la fecha inferior al número de factura, en general se consigue una aceptable traducción. Para traducir search a XLSX se usan las funciones INDEX y FILTER.