Transponer tabla

Tenemos un modelo de tres columnas, en la primera columna, tenemos una compañía, y, continuación su número de teléfono, en la siguiente columna los elementos que corresponden con cada compañía, y, teléfono, en la última columna, la cantidad para cada compañía y teléfono.

Debemos de crear un modelo de cuatro columnas, en la primera columna debe de aparecer las compañías únicas, en la segunda columna, el teléfono de cada compañía, en la tercera columna, un recuento de elementos por compañía, y, en la ultima columna, el total de cantidades por compañía.

Por ejemplo, para los siguientes datos:

La compañía es Walmart, el teléfono es 67367363, el recuento de elementos es de 2, y, la cantidad total es de 323.

En la celda E1, usamos LET, creamos una variable, seleccionamos el rango, A2:A14, creamos otra variable, seleccionamos el rango C2:C14.

=LET(xx;A2:A14;yy;C2:C14

Creamos otra variable, usamos la función APILARH, como argumento matriz1, usamos la función SCAN, como argumento valor inicial, ponemos blanco, como argumento array, usamos el condicional SI, como argumento prueba lógica, preguntamos si es texto la variable “xx”, como argumento valor si verdadero, ponemos la variable “xx”, como argumento valor si falso, ponemos blanco.

Con el condicional SI, vamos a obtener una matriz desbordada en vertical, con el nombre de la compañía y blanco para el resto de las celdas.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"")

Como argumento función, ponemos LAMBDA, declaramos dos variables, como argumento calculo de LAMBDA, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “y” es igual a blanco, como argumento valor si verdadero, ponemos la variable “x”, como argumento valor si falso, ponemos la variable “y”, cerramos paréntesis, con esta expresión, vamos a rellenar los blancos con el nombre de la compañía anterior, como argumento matriz2 de APILARH, ponemos la variable “yy”.

Probamos variable.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"");LAMBDA(x;y;SI(y="";x;y)));yy);a)

Tenemos el modelo con las compañías, donde es blanco aparece la compañía anterior, y, en la siguiente columna, la cantidad correspondiente con cada compañía.

Vemos que la compañía Kmart aparece un valor como cero.

No nos interesa contar dicho valor, por lo que creamos otra variable, usamos la función FILTRAR, como argumento array, ponemos la variable “a”, como argumento include, usamos la función TOMAR, porque vamos a tomar la última columna, como argumento matriz, ponemos la variable “a”, omitimos el argumento filas, como argumento columnas, ponemos -1, y, comparamos con distinto a cero.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"");LAMBDA(x;y;SI(y="";x;y)));yy);b;FILTRAR(a;TOMAR(a;;-1)<>0);b)

Ya no tenemos ese valor cero.

Lo siguiente es poner en horizontal las compañías únicas, para ello, creamos otra variable, usamos la función ENFILA, como argumento matriz, ponemos la función UNICOS, como argumento, volvemos a usar la función TOMAR, para tomar la primera columna, como argumento matriz, ponemos la variable “b”, omitimos el argumento filas, como argumento columnas, ponemos 1, cerramos paréntesis.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"");LAMBDA(x;y;SI(y="";x;y)));yy);b;FILTRAR(a;TOMAR(a;;-1)<>0);c;ENFILA(UNICOS(TOMAR(b;;1)));c)

Creamos otra variable, usamos la función REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, ponemos la variable “c”, como argumento función, ponemos LAMBDA, y, declaramos dos variables.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"");LAMBDA(x;y;SI(y="";x;y)));yy);b;FILTRAR(a;TOMAR(a;;-1)<>0);c;ENFILA(UNICOS(TOMAR(b;;1)));d;REDUCE("";c;LAMBDA(x;y

Como argumento calculo de LAMBDA, usamos la función APILARH, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos la función SUMA, como argumento numero1, usamos el condicional SI, como argumento prueba lógica, vamos a tomar la primera columna de la variable “b”, por lo que usamos de nuevo la función TOMAR, como argumento matriz, ponemos la variable “b”, omitimos el argumento filas, como argumento columnas, ponemos 1, cerramos paréntesis, e, igualamos a la variable “y”, como argumento valor si verdadero, ponemos 1, como argumento valor si falso, ponemos 0, y, cerramos paréntesis.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"");LAMBDA(x;y;SI(y="";x;y)));yy);b;FILTRAR(a;TOMAR(a;;-1)<>0);c;ENFILA(UNICOS(TOMAR(b;;1)));d;REDUCE("";c;LAMBDA(x;y;APILARH(x;SUMA(SI(TOMAR(b;;1)=y;1;0))

Concatenamos con la barra inclinada, concatenamos de nuevo con la función SUMA, como argumento numero1, usamos el condicional SI, como argumento prueba lógica, vamos a tomar de nuevo la columna 1 de la variable “b”, por lo que usamos de nuevo la función TOMAR, como argumento matriz, ponemos la variable b, omitimos el argumento filas, como argumento columnas, ponemos 1, e, igualamos a la variable “y”, como argumento valor si verdadero, vamos a tomar la ultima columna de la variable “b”, por lo que usamos de nuevo la función TOMAR, como argumento matriz, ponemos la variable “b”, omitimos el argumento filas, como argumento columnas, ponemos -1, como argumento valor si falso, ponemos 0.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"");LAMBDA(x;y;SI(y="";x;y)));yy);b;FILTRAR(a;TOMAR(a;;-1)<>0);c;ENFILA(UNICOS(TOMAR(b;;1)));d;REDUCE("";c;LAMBDA(x;y;APILARH(x;SUMA(SI(TOMAR(b;;1)=y;1;0))&"/"&SUMA(SI(TOMAR(b;;1)=y;TOMAR(b;;-1);0)))));d)

Tenemos una matriz desbordada en horizontal con el recuento de elementos y el total para cada compañía.

En este punto, vemos que la expresión TOMAR(b;;1) la usamos mas de una vez, pues, después de la variable “b”, vamos a crear otra variable, y, ponemos la función TOMAR.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"");LAMBDA(x;y;SI(y="";x;y)));yy);b;FILTRAR(a;TOMAR(a;;-1)<>0);bb;TOMAR(b;;1)

Ahora, sustituimos la función TOMAR por dicha variable, y, tenemos los mismos resultados.

La primera columna está de más, por lo que antes de REDUCE, usamos la función EXCLUIR, como argumento matriz, es la función REDUCE, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"");LAMBDA(x;y;SI(y="";x;y)));yy);b;FILTRAR(a;TOMAR(a;;-1)<>0);bb;TOMAR(b;;1);c;ENFILA(UNICOS(bb));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;SUMA(SI(bb=y;1;0))&"/"&SUMA(SI(bb=y;TOMAR(b;;-1);0)))));;1);d)

Usamos el argumento calculo de LET, usamos la función APIALRH, como argumento matriz1, usamos la función ENCOL, como argumento ponemos la variable “c”, como argumento matriz2, usamos la función FILTRAR, como argumento array, ponemos la variable “xx”, como argumento include, preguntamos si es numero la variable “xx”, vamos a obtener los teléfonos, como argumento matriz3, usamos la función ENCOL, como argumento usamos la función TEXTOANTES, como argumento texto, ponemos la variable “d”, como argumento delimitador, entre comillas dobles, ponemos la barra inclinada, multiplicamos por 1 para que el resultado este en formato de número, como argumento matriz4, usamos la función ENCOL, como argumento usamos la función TEXTODESPUES, como argumento texto, ponemos la variable “d”, como argumento delimitador, entre comillas dobles, ponemos la barra inclinada, y, multiplicamos de nuevo por 1.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"");LAMBDA(x;y;SI(y="";x;y)));yy);b;FILTRAR(a;TOMAR(a;;-1)<>0);bb;TOMAR(b;;1);c;ENFILA(UNICOS(bb));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;SUMA(SI(bb=y;1;0))&"/"&SUMA(SI(bb=y;TOMAR(b;;-1);0)))));;1);APILARH(ENCOL(c);FILTRAR(xx;ESNUMERO(xx));ENCOL(TEXTOANTES(d;"/");3)*1;ENCOL(TEXTODESPUES(d;"/");3)*1))

Ya tenemos el modelo.

Vamos a poner el encabezado, para ello, antes de APILARH, usamos APILARV, como argumento matriz1, usamos una constante de matriz, para ello, abrimos unas llaves, entre comillas dobles, ponemos Compañía, ponemos la barra inclinada, entre comillas dobles, ponemos Teléfono, ponemos la barra inclinada, entre comillas dobles, ponemos Recuento elementos, ponemos barra inclinada, entre comillas dobles, ponemos Total, cerramos llaves, como argumento matriz2, es la función APILARH.

=LET(xx;A2:A14;yy;C2:C14;a;APILARH(SCAN("";SI(ESTEXTO(xx);xx;"");LAMBDA(x;y;SI(y="";x;y)));yy);b;FILTRAR(a;TOMAR(a;;-1)<>0);bb;TOMAR(b;;1);c;ENFILA(UNICOS(bb));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;SUMA(SI(bb=y;1;0))&"/"&SUMA(SI(bb=y;TOMAR(b;;-1);0)))));;1);APILARV({"Compañia"\"Telefono"\"Recuento elementos"\"Total"};APILARH(ENCOL(c);FILTRAR(xx;ESNUMERO(xx));ENCOL(TEXTOANTES(d;"/");3)*1;ENCOL(TEXTODESPUES(d;"/");3)*1)))

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco