Convertir 0 y 1 en meses y trimestres

Vamos a ver otro ejemplo de Excel BI.

Dicho desafío consiste en que tenemos una serie de números entre 1 y 0.

En este caso, tenemos tres números, cada numero es un mes, para el numero 101, son los meses enero, febrero, y, marzo.

El desafío consiste en que si el numero es 1, debe de aparecer las iniciales del mes, un guion medio, la letra Q y el trimestre al que corresponde, si el valor es 0, dicho mes no debe de aparecer.

Si es mas de un mes, cada mes debe de estar separado por una coma.

En las celdas D1:D12, tenemos cada nombre del mes.

En las celdas E1:F4, en la primera columna tenemos los números 1,4,7, y 10, en la segunda columna, tenemos Q1, Q2, Q3, y, Q4.

Empecemos…

Haremos como en otros ejemplos, vamos a usar la función LET, e, iremos dividiendo los pasos a realizar en diferentes Variables.

En la celda B2, vamos a extraer cada carácter de la celda A2, para ello, vamos a usar la función EXTRAE, como argumento texto, es la celda A2, como argumento posición inicial, usamos la función SECUENCIA, como argumento filas de SECUENCIA, usamos LARGO y como argumento es la celda A2, como argumento numero de caracteres, ponemos 1.

Cerramos paréntesis.

Probamos la variable.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));a)

Aceptamos, y, tenemos una matriz desbordada en vertical con cada carácter de la celda A2.

Vamos a crear otra variable, donde vamos a almacenar una secuencia de números, empezando desde el numero 1 hasta la longitud de la cadena, para ello, usamos la función SECUENCIA, y, como argumento filas es la longitud de la celda A2.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));b)

Con la variable “b”, ya sabemos los meses a rescatar del rango D1:D12, en este caso, son los meses enero, febrero, y, marzo.

En la siguiente variable que vamos a crear, nos vamos a traer el nombre del mes y al trimestre al que pertenece, para ello, para extraer el mes, vamos a usar la función INDICE, como argumento matriz, es el rango D1:D12, como argumento numero de fila, es la variable “b”, omitimos el argumento número de columna.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)

Concatenamos con un guion medio.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"

Volvemos a concatenar, ahora, usamos BUSCARV para buscar el trimestre, como argumento valor buscado, es la variable “b”, como argumento matriz_tabla, seleccionamos el rango E1:F4, como argumento indicador columnas, ponemos 2, como argumento tipo de coincidencia, o, rango, seleccionamos aproximada.

Cerramos paréntesis de BUSCARV.

Probamos la variable.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);c)

Tenemos una matriz desbordada en vertical con las iniciales del mes, un guion medio, y, el trimestre correspondiente.

Pero, solo deben de aparecer los meses con el valor 1, como argumento calculo de LET, usamos el condicional SI, donde preguntamos si el resultado de la variable “a” es igual a 0, que ponga un texto en blanco, en caso contrario, que devuelva la variable “c”.

LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);SI(a=0;"";c))

Vemos que ahora donde los meses son ceros, no aparecen.

Usamos UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, y dejamos un espacio, ignoramos celdas vacías, y, como argumento texto1, es la expresión anterior.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);UNIRCADENAS(", ";VERDADERO;SI(a=0;"";c)))

Aceptamos, arrastramos, y, ya lo tenemos.

A partir de ahora, los ejemplos han sigo agregado por mí.

Y, si nos hemos equivocado, y, hemos puesto números que no son 1 y 0, pues, tomaría esos números como 1, y, nos devolverá los meses.

Pero, si no es 1 no 0, no debe de devolver los meses, es como si fuera cero.

Debemos de preguntar si la variable “a” es igual a 0, o, diferente a 1, para ello, debemos de usar el operador lógico O, pero, ya sabemos que no podemos usarlo de forma matricial, para hacerlo, vamos a usar la función BYROW.

Vamos a la expresión, vemos que hemos usado el condicional SI, para preguntar si el valor de la variable “a” es 0, o, 1.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);UNIRCADENAS(", ";VERDADERO;SI(a=0;"";c)))

Borramos el argumento prueba lógica.

Como argumento prueba lógica, ponemos BYROW, como argumento array, ponemos la variable “a”.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);UNIRCADENAS(", ";VERDADERO;SI(BYROW(a

Como argumento función de BYROW, ponemos LAMBDA, y, creamos una variable, dicha variable almacena la variable “a”.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);UNIRCADENAS(", ";VERDADERO;SI(BYROW(a;LAMBDA(x

Como argumento calculo de LAMBDA, ponemos el operador lógico O, como argumento valor logico1, ponemos la variable “x” y la igualamos a 0, como argumento valor logico2, ponemos la variable “x”, y, ponemos indiferente a 1.

Cerramos paréntesis del operador lógico O, y, de LAMBDA.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);UNIRCADENAS(", ";VERDADERO;SI(BYROW(a;LAMBDA(x;O(x=0;x<>1)

Como argumento valor si verdadero, ponemos un texto en blanco, como argumento valor si falso, ponemos la variable “c”, cerramos paréntesis.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);UNIRCADENAS(", ";VERDADERO;SI(BYROW(a;LAMBDA(x;O(x=0;x<>1)));"";c)))

Aceptamos, arrastramos, y, vemos que donde es un número que no es 1, ni 0, no aparece el mes.

Y si se equivocan, y, ponen una letra.

Vamos a la expresión, a la expresión de la variable “a”, antes de ABS, ponemos la función FILTRAR, como argumento array, es la función ABS y EXTRAE.

=LET(a;FILTRAR(ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1))

Como argumento include, usamos la función ESNUMERO, como argumento es la función ABS y EXTRAE, y, la igualamos a VERDADERO.

=LET(a;FILTRAR(ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));ESNUMERO(ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1)))=VERDADERO)

Tenemos que cambiar en la expresión que corresponde a la variable “b”, el argumento filas de SECUENCIA, porque ahora es la variable “a”, que tiene los caracteres correctos.

=LET(a;FILTRAR(ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));ESNUMERO(ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1)))=VERDADERO);b;SECUENCIA(SUMA(LARGO(a)));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);d;UNIRCADENAS(", ";VERDADERO;SI(BYROW(a;LAMBDA(x;O(x=0;x<>1)));"";c));d)

El resto de la expresión queda igual.

=LET(a;FILTRAR(ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));ESNUMERO(ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1)))=VERDADERO);b;SECUENCIA(SUMA(LARGO(a)));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);d;UNIRCADENAS(", ";VERDADERO;SI(BYROW(a;LAMBDA(x;O(x=0;x<>1)));"";c));d)

Aceptamos, arrastramos, y, ahora contempla cuando el numero no es 0 ni 1, ni cuando hay un texto.

El siguiente desafío, es que además de aparecer los datos que hemos obtenido anteriormente, tenemos otra hoja llamada datos, que es el modelo que suelo usar habitualmente, con las ventas para el año 2022, pues, vamos a añadir el total de cada mes para.

Para este ejemplo, no vamos a tener en cuenta si hay algún numero que no sea 0 o 1, o, si hay texto.

Tenemos la expresión del ejemplo1.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);UNIRCADENAS(", ";VERDADERO;SI(a=0;"";c)))

La ultima variable que creamos fue la variable “c”.

Vamos a borrar la siguiente expresión:

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);UNIRCADENAS(", ";VERDADERO;SI(a=0;"";c)))

Creamos otra variable, ponemos la función BYROW, como argumento array, ponemos la variable “b”.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);d;BYROW(b

Como argumento función, ponemos LAMBDA, declaramos una variable, que almacena la variable “ b”.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);d;BYROW(b;LAMBDA(x

Ponemos la función SUMA.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);d;BYROW(b;LAMBDA(x;SUMA(

Como argumento de SUMA, abrimos un paréntesis, donde vamos a poner una condición, dicha condición es que el mes de la columna fecha de ventas sea igual a la variable “x”, cerramos paréntesis de la condición.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);d;BYROW(b;LAMBDA(x;SUMA((MES(Ventas[Fecha])=x)

Ponemos asterisco, abrimos otro paréntesis, ponemos la columna total, cerramos paréntesis de la condición, es decir, nos va a devolver el total, si el mes es igual a uno de los devueltos por la variable “x”.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);d;BYROW(b;LAMBDA(x;SUMA((MES(Ventas[Fecha])=x)*(Ventas[Total])

Cerramos paréntesis.

Probamos la variable, y, vemos que tenemos el total para cada mes del valor de la celda A2.

Pero, se contemplan los meses que son ceros.

Borramos el cierre de paréntesis, y, la variable.

Ponemos el condicional SI, preguntamos si la variable “a” es igual a cero, en ese caso, que devuelva un texto en blanco, en caso contrario, usamos APILARH, como argumento matriz1, es la variable “c”, como argumento matriz2, usamos TEXTO, como argumento valor, es la variable “d”, como argumento formato, ponemos "#.##0,00 €", es decir, que nos devuelva un numero con dos decimales, cerramos paréntesis de TEXTO de APILARH, y, de LET.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);d;BYROW(b;LAMBDA(x;SUMA((MES(Ventas[Fecha])=x)*(Ventas[Total]))));SI(a=0;"";APILARH(c;TEXTO(d;"#.##0,00 €"))))

Aceptamos, y, tenemos una matriz desbordada de dos columnas, en la primera columna, tenemos los meses, y, en la segunda columna, tenemos los totales para cada mes, pero, ya no aparecen los meses con ceros.

Usamos UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, y, dejamos un espacio, ignoramos celdas vacías, y, como argumento texto1, es la expresión anterior.

=LET(a;ABS(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;SECUENCIA(LARGO(A2));c;INDICE($D$1:$D$12;b)&"-"&BUSCARV(b;$E$1:$F$4;2;VERDADERO);d;BYROW(b;LAMBDA(x;SUMA((MES(Ventas[Fecha])=x)*(Ventas[Total]))));UNIRCADENAS(", ";VERDADERO;SI(a=0;"";APILARH(c;TEXTO(d;"#.##0,00 €")))))

Aceptamos, arrastramos, y, ya lo tenemos.

Miguel Angel Franco