05 sep 2021

Fórmulas XLSX

Fórmulas y funciones XLSX

Una fórmula es una expresión de cálculo que contiene valores, constantes, operadores y funciones. El módulo calc.js (CALC) es un motor para calcular expresiones. Lo implementé en 2019 y ahora lo he actualizado para que permita traducir funciones a XLSX. La Figura es una captura de su implementación en una aplicación a modo de calculadora. Ese módulo también sirve de motor de cálculo para el Gestor de Tablas WXTABLE, permitiendo ahora exportar las fórmulas a XLSX.

En la Figura vemos la función round(1.2567, 2) que redondea el número decimal 1.2567 a dos decimales resultando 1.26. La traducción a XLSX es en muchos casos directa puesto que la función correspondiente en XLSX es similar. En este caso se trata de la función ROUND(1.2567, 2) que tiene la misma estructura de argumentos. En EXCEL los nombres de las funciones se ponen en mayúsculas, mientras que en GoogleSheet parece que es indiferente. En todo caso usaré las mayúsculas para diferenciar claramente si es una función de WXTABLE o de XLSX. Además los nombres de las funciones deben usarse en inglés, aunque en EXCEL se escriban con su traducción al idioma en que se ejecute la aplicación.

El módulo CALC dispone actualmente de los siguientes recursos:

5 valores: NaN, Infinity, Null, true, false
8 constantes: E, PI, LOG2E, LOG10E, LN2, LN10, SQRT1_2, SQRT2
9 operadores aritméticos: %, *, +, -, /, mod, +%, -%, ^
3 operadores booleanos: !, &, |
6 operadores comparación: <, =, >, !=, <=, >=
1 operador texto: #
10 funciones aritméticas: sum(), mult(), div(), sign(), ...
29 funciones texto: join(), sort(), left(), lower(), ...
8 funciones redondeo: abs(), round(), floor(), ceil(), ...
18 funciones matemáticas: cos(), sin(), log(), pow(), sqrt(), ...
11 funciones estadísticas: average(), count(), max(), median(), ...
11 funciones estructurales: if(), switch(), get(), set(), run(), ...
12 funciones estilos: bold(), italic(), color(), sub(), sup(), ...
27 funciones fecha: date(), dateadd(), datediff(), weeknum(), ...
264 iconos SVG insertables: , , , , , , ...

La mayor parte de esos recursos son traducibles a XLSX. No he podido traducir algunas funciones estructurales. Ni los iconos SVG pues XLSX no parece soportarlo. Otras traducciones son parcialmente llevadas a cabo. Por ejemplo, la función de texto bold("abc") devuelve un HTML <b>abc</b> que en WXTABLE se permite insertar en una celda. XLSX no permite HTML y se traduce devolviendo el texto "abc".

Para llevar a cabo la traducción en el módulo CALC representaremos los argumentos con un número entre corchetes, iniciándose en cero. Así si la fórmula es f(x, y), la traducción se esquematizará con F([0], [1]), siendo F la función equivalente en XLSX, [0] el valor del primer argumento y [1] el del segundo.

La expresión F([0], [1]) debemos entenderla como un esquema para la traducción a XLSX de la función f(x, y). Puede contener también valores literales, como F([0], [1], 23) + 45, donde el tercer argumento de la función es el número 23 y al final a su resultado se le suma a 45. Usamos los corchetes pues en el módulo CALC no se permite su uso, sólo se permiten paréntesis. En WXTABLE los corchetes se reservan para las referencias a celdas y rangos. Pero antes de enviar la fórmula para su cálculo en CALC, se extraen de la tabla los valores de esas referencias. Así que a CALC no puede llegar ninguna fórmula con corchetes a excepción de los que estén dentro de una cadena entrecomillada.

La función anterior tiene un número determinado de argumentos: se deben pasar 2 argumentos. Pero hay funciones que calculan sobre un número indeterminado de argumentos. Son funciones como la suma de argumentos sum(x₁, x₂, x₃, ...), esquematizándose con SUM([...]). La función equivalente en XLSX también tiene ese nombre SUM. El término [...] indicará que se aplica sobre todos los argumentos.

En el módulo CALC los nombres de las funciones pueden tener alias. Así la función round() de la Figura tiene un alias redondear(). La función sum tiene los alias add, sumar, suma.

En la Figura vemos que la traducción a XLSX también indica el tipo Type="n" para resaltar que la función devolverá un número. Vease el tema Tipos de contenidos de celda" sobre los tipos de valores en XLSX.

También puede devolver otro indicador Join, como es el caso de la función div(x₁, x₂, x₃, ...) que realiza la operación división entre sus argumentos x₁ ÷ x₂ ÷ x₃ ÷ .... En GoogleSheet encontramos la función equivalente DIVIDE, pero que no es soportada en EXCEL. Sin embargo ambos soportan el cálculo con el operador división "/". Así que la función div(x₁, x₂, x₃, ...) se traduce como Formula=[...] Type="n" Join="/". El indicador Join hará que el traductor concatene los argumentos [...] de la fórmula con el operador división "/".

Algunas funciones son fáciles de traducir. Otras han sido díficiles. Y para algunas me han resultado imposible. De 147 funciones y operadores del módulo CALC y, por tanto de WXTABLE, no he logrado aún traducir las funciones chart, clear, datelocale, dateobject, easter, get, getdata, icon, set. La traducción de algunas funciones simples resulta algo engorrosa en XLSX, como con la función número de semana weeknum("31/5/2021", 4), donde el primer argumento es la fecha y el segundo el número de días como mínimo que debe tener la primera semana del año. La he traducido como WEEKNUM([0], IF(ISNUMBER([1]), IF([1]=1, 1, IF([1]>=4, 21, 2)) ,21)) intentando obtener el tipo de la siguiente tabla:

tipo	Día en que comienza la semana	Día en que termina la semana	Sistema
1 u omitido	Domingo	Sábado	1
2	Lunes	Domingo	1
11	Lunes	Domingo	1
12	Martes	Lunes	1
13	Miércoles	Martes	1
14	Jueves	Miércoles	1
15	Viernes	Jueves	1
16	Sábado	Viernes	1
17	Domingo	Sábado	1
21	Lunes	Domingo	2

Esa tabla se encuentra en la documentación de ayuda de la función WEEKNUM en GoogleSheet y es algo engorrosa de hacerla adaptar con la función propia weeknum.

En la documentación OOX puede encontrar todas las funciones de XLSX en el apartado 18.17.7 Predefined Function Definitions, página 2081. Sin embargo no he seguido esa documentación en este aspecto, sino basándome en las ayudas disponibles en GoogleSheet y Excel.

No es cuestión en este tema de extedernos con todas las funciones disponibles. He preparado un WXTABLE funciones.txt que he exportado a XLSX en el enlace funciones.xlsx, donde se exponen ejemplos de la traducción de funciones. Cuando no exista traducción para una función se mostrará el texto #ERROR translating to XLSX: Function chart() not supported, por ejemplo para el caso de la función chart() que inserta una gráfica en WXTABLE y que aún no he podido traducir a XLSX (no he encontrado una función XLSX que realice esto). En los siguientes apartados analizaremos algunas funciones para comentar detalles importantes.

Funciones JOIN y TEXTJOIN y uso del prefijo _xlfn

En WXTABLE la función join(separador, listaValores) concatena la lista de valores con la cadena separadora. Por ejemplo, join(",", "a", 1, "b", 2) devuelve la cadena "a,1,b,2". La lista de valores puede ser un rango como join(",", [R1C1]:[R3C1]) que concatenará los contenidos de las celdas [R1C1], [R2C1] y [R3C1]. La función no ignora los elementos vacíos.

El esquema de traducción a XLSX es _xlfn.TEXTJOIN([0], FALSE, [1...]) para que también sea compatible con EXCEL. La función TEXTJOIN en XLSX tiene tres argumentos. El primero es un String para el separador, el segundo es un booleano que indica si se ignoran los valores vacíos y el tercero es la lista de valores a concatenar, bien una lista de argumentos o un rango de celdas. El indicador [0] recupera el valor del argumento separador, argumento con índice 0. Mientras que el indicador [1...] recupera el resto de argumentos a partir del índice 1 inclusive. El segundo argumento siempre se traduce a FALSE. La función finalmente se exporta como _xlfn.TEXTJOIN(",", FALSE, "a", 1, "b", 2).

GoogleSheet dispone además de la función JOIN(separador, valor_o_rango1, valor_o_rango2, ...) que permite concatenar valores o rangos, con lo que se puede comportar también como TEXTJOIN.

Lo que queda por comentar es el uso del prefijo _xlfn. Tiene que ver con las versiones de las aplicaciones como EXCEL o GoogleSheet. La función TEXTJOIN (en español UNIRCADENAS) solo se soporta en las versiones EXCEL de Office 2019 o si se tiene una suscripción Microsoft 365 que es la versión web, como puede observar en la Figura. Se puede usar la fórmula =TEXTJOIN(",", FALSE, "a", 1, "b", 2) escribiéndola en GoogleSheet directamente. Y en EXCEL web con lenguaje español escribiendo =UNIRCADENAS(","; FALSO; "a"; 1; "b"; 2), donde se usan puntos y comas en lugar de comas y los nombres de las funciones y valores booleanos se escriben en español.

Si usamos la función TEXTJOIN(",", FALSE, "a", 1, "b", 2) sin el prefijo _xlfn en el exportador obtendremos un error en EXCEL de que no reconoce la fórmula. En el enlace funciones.xlsx, donde probamos todas las funciones, exportamos esa fórmula usando el prefijo _xlfn, obteniéndose un resultado correcto. En el archivo XML xl/worksheets/sheet1.xml encontramos la fórmula en la fila 115:

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    ...
    <sheetData>
        ...
        <row r="115" hidden="0">
            <c r="G115" t="str" s="0">
                <f>_xlfn.TEXTJOIN(",",FALSE,"a",1,"b",2)</f>
                <v>a,1,b,2</v>
            </c>
            ...
        </row>
        ...
    </sheetData>
    ...
</worksheet>

Cuando exportamos desde WXTABLE a XLSX generamos archivos XML que contienen atributos de espacios de nombres. En lo anterior vemos los atributos xmlns para el espacio de nombres global de las hojas de cálculo. Y el atributo xmlns:r para las relaciones entre archivos, algo que ya comentamos en temas anteriores. Pero si creamos en GoogleSheet una hoja de de cálculo en blanco obtenemos todos estos atributos:

<worksheet mlns:xm="http://schemas.microsoft.com/office/excel/2006/main" 
xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" 
xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" 
xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" 
xmlns:mv="urn:schemas-microsoft-com:mac:vml" 
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main" 
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" 
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    ...
</worksheet>

En EXCEL se obtienen estos, algunos diferentes de los que descarga GoogleSheet:

<worksheet xr:uid="{00000000-0001-0000-0000-000000000000}" 
xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" 
xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" 
xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" 
xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" 
mc:Ignorable="x14ac xr xr2 xr3" 
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" 
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    ...
</worksheet>

Esos atributos tienen que ver con el control de versiones de Office. Esto de las distintas versiones es desesperante. Al final opté por solo incluir el básico xmlns y el de las relaciones xmlns:r. En ese caso cuando EXCEL intenta abrir un XLSX generado en WXTABLE entiende que procede de versiones antiguas y, por tanto, no tiene por que reconocer la función TEXTJOIN. A menos que la dotemos del prefijo _xlfn.

Ya he comentado en un tema anterior que he podido probar algunos ejemplos en EXCEL 2010 de escritorio, versión 14. Fueron pruebas consideradas como simples revisiones visuales, pues hay muchas versiones de escritorio que suponen una tarea compleja llevar a cabo la verificación en todas ellas. Lo que se observa en EXCEL 2010 es que no reconoce el prefijo _xlfn, dado que es una característica de versiones posteriores.

Las funciones CONCAT y CONCATENATE en XLSX

Un ejemplo de los problemas que nos podemos encontrar al intentar traducir funciones desde WXTABLE a XLSX es la función concat(). La función es muy simple, concatena los valores de sus argumentos. Por ejemplo, en WXTABLE la función concat("a", "b", "c") devuelve "abc". También es posible en WXTABLE usar referencias a celdas o rangos en sus argumentos. Por ejemplo, si la celdas [R2C4]:[R4C4] contiene los valores "a", "b" y "c" en sus tres celdas, entonces concat([R2C4]:[R4C4]) también nos devolverá el mismo valor "abc".

En EXCEL existe la función CONCAT(D2:D4), siendo D2:D4 la traducción del rango [R2C4]:[R4C4]. Funciona exactamente igual, tal como se observa en la Figura. Permite concatenar un número indeterminado de valores, refencias o rangos. Es una función con soporte Office 2019 y Microsoft 365, por lo que habría que exportarla con prefijo _xlfn.CONCAT(D2:D4).

Hasta ahora bien. Pero cuando vamos a probarlo en GoogleSheet con más de dos argumentos o que no sean valores nos encontraremos con un error. Tal como se observa en la Figura, al intentar introducir el tercer argumento nos lo pone subrayado en rojo, indicativo de error.

Para GoogleSheet la función CONCAT() sirve para concatenar dos valores tal como lo haríamos con el operador &. Nada de incluir referencias ni rangos en sus argumentos.

Seguimos indagando y vemos que GoogleSheet tiene además la función CONCATENATE. Es exactamente la función que estamos buscando, como se observa en la Figura. Hace lo mismo que CONCAT en EXCEL, permitiendo indeterminado número de argumentos valores, referencias o rangos.

Sólo nos quedaría comprobar que EXCEL soporte CONCATENATE.

En principio parece que sí, como se observa en la Figura. Sin embargo su comportamiento en caso de rangos es diferente. Como se observa en la imagen, su resultado es la aplicación de argumentos como Arrays (matrices), algo que veremos en un tema siguiente. Además en las ayudas de EXCEL web dice que CONCATENATE se ha sustituido por CONCAT. Se sigue soportando por compatibilidad, pero se recomienda no usar CONCATENATE pues puede que no esté disponible en versiones futuras.

Así que para EXCEL la función adecuada sería _xlfn.CONCAT(D2:D4) y para GoogleSheet sería CONCATENATE(D2:D4). Pero esto sería incompatible con nuestro traductor. No podemos diferenciar la traducción según sea la aplicación que vaya a abrir el XLSX.

Una solución pasa por utilizar otra función que concatene los argumentos, sean valores, referencias o rangos. Y que funcione en GoogleSheet y EXCEL. Por ejemplo usando la función TEXTJOIN(separador, ignorarVacios, lista) que ya vimos en un apartado anterior para traducir la función join(separador, lista) de WXTABLE. Con el ejemplo quedaría así: _xlfn.TEXTJOIN("",FALSE,D2:D4). En EXCEL en español la función tiene por nombre UNIRCADENAS, como se observa en la Figura.

El esquema que usamos en CALC para traducir concat(listaValores) es _xlfn.TEXTJOIN("",FALSE,[...]). El esquema [...] propaga los argumentos. Cuando tenemos concat("a", "b", "c") entonces [...] en el módulo CALC será un Array JavaScript ["a", "b", "c"]. Y cuando tenemos concat([R2C4]:[R4C4]) se convierte en ["[R2C4]:[R4C4]"], entrecomillándose siempre las referencias o rangos pues CALC no permite expresiones con corchetes. Al final se devolverá ese Array aplicándole el método join(",") de Array de JavaScript, obteniéndose "a, b, c" o bien "[R2C4]:[R4C4]" como argumentos respectivamente de cada versión lista de valores o rango. Posteriormente en el exportador XLSX se convierten las referencias y rangos entrecomillados, siendo en este caso D2:D4.