Transponer tabla

Jaime Franco Jimenez
13 ago 2023
5 Min. de lectura

Tenemos los siguientes datos.

Debemos de transponer la tabla, por ejemplo, tenemos G1, de G1 cuelga HR, y, Sales, HR tiene los valores 1,2,3, y, Sales tiene el valor de 4, y, 5, los valores hacen referencia a equipos, entonces, el resultado debe de ser.

Este paso debemos de realizarlo con los demás elementos.

Lo primero que vamos a realizar es encontrar la letra “G” en el rango A1:J1, para ello, usamos la función ENCONTRAR, como argumento texto buscado, entre comillas dobles, ponemos la letra G, como argumento dentro del texto, seleccionamos el rango A1:J1.

=ENCONTRAR("G";A1:J1)

Aceptamos, tenemos una matriz desbordada en horizontal con la posición de la letra G, y, error donde no hay coincidencia.

Preguntamos si el resultado de la función ENCONTRAR es igual a VERDADERO, pero no tenemos que poner la comparación, porque de forma predeterminada, comparara con VERDADERO.

=SI(ENCONTRAR("G";A1:J1)

Como argumento valor si verdadero, ponemos el rango A1:J1, como argumento valor si falso, ponemos un error, para ello, usamos la función NOD().

=SI(ENCONTRAR("G";A1:J1);A1:J1;NOD())

Obtenemos las palabras que comienzan por G, y, error donde no hay coincidencia.

Usamos la función ENFILA, como argumento matriz, es el condicional SI, como argumento ignorar, seleccionamos la opción 3, que es omitir blancos y errores.

=ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3)

Obtenemos solo los valores, los errores fueron ignorados.

Vamos a usar LET, y, vamos almacenando los pasos en variables.

Ponemos LET, creamos una variable, y, almacenamos la expresión anterior, y, probamos la variable.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);a)

Tenemos los mismos resultados.

Creamos otra variable, usamos la función BUSCARX, como argumento valor buscado, es la variable “a”, como argumento matriz de búsqueda, seleccionamos el rango A1:J1, como argumento matriz devuelta, usamos la función COLUMNA, como argumento, seleccionamos el rango A1:J1.

Probamos variable.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;BUSCARX(a;A1:J1;COLUMNA(A1:J1));b)

Obtenemos una matriz desbordada en horizontal con los números de columnas.

La función BUSCARX es el argumento numero de columna de la función INDICE que vamos a usar ahora para extraer los valores, para ello, ponemos la función INDICE, como argumento matriz es el rango A2:J4, como argumento numero de fila, usamos una constante de matriz, para ello, abrimos unas llaves, ponemos los números 1,2, y, 3 separados por punto y coma, cerramos las llaves.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3}

Como argumento numero de columna, es la función BUSCARX.

Probamos la variable.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1

)));b)

Aceptamos, tenemos una matriz desbordada de tres columnas, en cada columna, tenemos los nombres a los que le pertenecen valores.

Creamos otra variable, concatenamos la variable “a” con una coma, y, con la variable B.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;a&","&b;c)

Tenemos las mismas columnas que anteriormente, pero, después de cada nombre aparece el valor G que le corresponde.

Debemos de poner estos valores en filas, si usamos la función ENCOL, nos lo pone en filas, pero, no en el orden en el que deben de aparecer.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(a&","&b);c)

Por lo que primero, transponemos, y, después usamos ENCOL.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));c)

Ahora, tenemos el orden correcto.

Los valores donde solo aparecen los valores G (G1, G2, G3), no nos interesan, por lo que vamos a crear otra variable, preguntamos si la longitud de la variable “c” es diferente a 3, que nos devuelva la variable “c”, en caso contrario, que devuelva un error.

Envolvemos el condicional SI en la función ENCOL.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c )<>3;c;NOD());3);d)

Ya tenemos los valores con los que trabajar.

Declaramos otra variable, preguntamos si el rango A2:J4 es igual a 0, en ese caso, que nos devuelva un error, en caso contrario, que nos devuelva el rango A2:J4.

Tenemos el modelo, y, donde era cero, aparece un error.

Transponemos.

Usamos ENCOL, como argumento matriz es la función TRANSPONER, como argumento ignorar, ponemos 3.

Antes de ENCOL, usamos la función ABS.

Obtenemos una matriz desbordada con error donde no es número, y, los números.

Filtramos la expresión anterior, siempre que dicha expresión no sea un error.

Obtenemos una matriz desbordada con los valores.

Creamos otra variable, concatenamos la variable “d con una coma, y, con la variable “e”.

Tenemos una matriz desbordada con la unión del grupo con el departamento y grupo.

Creamos otra variable, usamos la función TEXTOANTES, como argumento texto, ponemos la variable “f”, como argumento delimitador, entre comillas dobles, ponemos coma.

Obtenemos el grupo.

Concatenamos con una coma, concatenamos con la función TEXTOANTES, como argumento texto, ponemos la función TEXTODESPUES, como argumento texto, ponemos la variable “f”, como argumento delimitador, entre comillas dobles, ponemos coma, como argumento delimitador de la función TEXTOANTES, entre comillas dobles, ponemos coma.

¿Por qué usamos como argumento delimitador de TEXTOANTES, la función TEXTODESPUES?

Tenemos tres cadenas separadas por comas, si usamos la función TEXTODESPUES, como argumento texto, ponemos la variable “f”, y, como delimitador, entre comillas dobles, ponemos coma.

=TEXTODESPUES(f;",")

Nos devuelve la segunda, y, tercera cadena.

Si usamos la función TEXTODESPUES, como argumento texto de TEXTOANTES, obtenemos la segunda cadena que es la que necesitamos.

Concatenamos con una coma, concatenamos con la función TEXTOANTES, como argumento texto, es la variable “e”, que son los valores, como argumento delimitador, entre comillas dobles, ponemos coma.

Obtenemos una matriz desbordada con el grupo, departamento, y, equipo, separadas por comas.

Vemos un error, es debido a que tenemos un valor que es 10, que no tiene separación, vamos a usar la función SI.ERROR, como argumento valor, es la expresión anterior, como argumento valor si error, es la misma expresión, pero, cambiamos la expresión TEXTOANTES(e;",") por la variable “e”.

Tenemos una matriz desbordada, donde vemos que el error ha sido sustituido por el valor 10.

Tenemos que crear otra expresión, pero, para traernos el segundo valor.

La siguiente expresión, corresponde a la variable G, lo que aparece en negrita, es lo mismo que debemos de poner para la variable nueva que vamos a crear.

G=SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTOANTES(e;",");TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&e)

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c)<>3;c;NOD());3);e;FILTRAR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3));NO(ESERROR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3)))));f;d&","&e;g;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTOANTES(e;",");TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&e);h;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&

A continuación, ponemos la función TEXTODESPUES, como argumento texto, es la variable “e”, como argumento delimitador, entre comillas dobles, ponemos coma.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c)<>3;c;NOD());3);e;FILTRAR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3));NO(ESERROR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3)))));f;d&","&e;g;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTOANTES(e;",");TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&e);h;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTODESPUES(e;",")

Tenemos una matriz desbordada con los mismos valores obtenidos anteriormente, pero, en el grupo equipo, aparecen los segundos valores.

Nos devuelve el mismo error que anteriormente, pero en este caso, no vamos a recuperar el valor, vamos a poner un error, usamos la función SI.ERROR.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c)<>3;c;NOD());3);e;FILTRAR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3));NO(ESERROR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3)))));f;d&","&e;g;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTOANTES(e;",");TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&e);h;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTODESPUES(e;",");NOD());h)

Creamos otra variable, usamos la función ENCOL, como argumento matriz, usamos APILARH, como argumento matriz1, es la variable “g”, como argumento matriz2, es la variable “h”, como argumento ignorar, ponemos 3, es decir, ignorar blancos y errores.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c)<>3;c;NOD());3);e;FILTRAR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3));NO(ESERROR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3)))));f;d&","&e;g;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTOANTES(e;",");TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&e);h;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTODESPUES(e;",");NOD());i;ENCOL(APILARH(g;h);3);i)

Obtenemos una matriz desbordada con el modelo preparado para ser separado.

Como argumento cálculo de LET, usamos APILARH, como argumento matriz1, usamos la función TEXTOANTES, como argumento texto, ponemos la variable “i”, como argumento delimitador, entre comillas dobles, ponemos coma.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c)<>3;c;NOD());3);e;FILTRAR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3));NO(ESERROR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3)))));f;d&","&e;g;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTOANTES(e;",");TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&e);h;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTODESPUES(e;",");NOD());i;ENCOL(APILARH(g;h);3);APILARH(TEXTOANTES(i;",")

Como argumento matriz2, usamos la función TEXTODESPUES, como argumento texto, usamos la función TEXTOANTES, como argumento texto, es la variable “i”, como argumento delimitador, entre comillas dobles, ponemos coma, como argumento delimitador de la función TEXTODESPUES, ponemos entre comillas dobles, ponemos coma.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c)<>3;c;NOD());3);e;FILTRAR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3));NO(ESERROR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3)))));f;d&","&e;g;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTOANTES(e;",");TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&e);h;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTODESPUES(e;",");NOD());i;ENCOL(APILARH(g;h);3);APILARH(TEXTOANTES(i;",");TEXTODESPUES(TEXTOANTES(i;",";2);",")

Como argumento matriz3, usamos la función TEXTODESPUES; como argumento texto, es la variable “i”, como argumento delimitador, entre comillas dobles, ponemos coma, como argumento numero de instancia, ponemos 2.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c)<>3;c;NOD());3);e;FILTRAR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3));NO(ESERROR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3)))));f;d&","&e;g;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTOANTES(e;",");TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&e);h;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTODESPUES(e;",");NOD());i;ENCOL(APILARH(g;h);3);APILARH(TEXTOANTES(i;",");TEXTODESPUES(TEXTOANTES(i;",";2);",");TEXTODESPUES(i;",";2)))

Aceptamos, y, ya lo tenemos.

Ponemos encabezados.

Damos un formato.

Podemos acortar un poco la función, la función TEXTOANTES usando la misma variable, la usamos varias veces.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c)<>3;c;NOD());3);e;FILTRAR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3));NO(ESERROR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3)))));f;d&","&e;g;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTOANTES(e;",");TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&e);h;SI.ERROR(TEXTOANTES(f;",")&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTODESPUES(e;",");NOD());i;ENCOL(APILARH(g;h);3);APILARH(TEXTOANTES(i;",");TEXTODESPUES(TEXTOANTES(i;",";2);",");TEXTODESPUES(i;",";2)))

Después de LET, creamos una variable, almacenamos la función TEXTOANTES, y, sustituimos.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c)<>3;c;NOD());3);e;FILTRAR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3));NO(ESERROR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3)))));f;d&","&e;xx;TEXTOANTES(f;",");g;SI.ERROR(xx&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTOANTES(e;",");xx&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&e);h;SI.ERROR(xx&","&TEXTOANTES(TEXTODESPUES(f;",");",")&","&TEXTODESPUES(e;",");NOD());i;ENCOL(APILARH(g;h);3);APILARH(TEXTOANTES(i;",");TEXTODESPUES(TEXTOANTES(i;",";2);",");TEXTODESPUES(i;",";2)))

La siguiente expresión TEXTOANTES(TEXTODESPUES(f;","), también, la usamos más de una vez, pues, hacemos lo mismo que antes.

=LET(a;ENFILA(SI(ENCONTRAR("G";A1:J1);A1:J1;NOD());3);b;INDICE(A2:J4;{1;2;3};BUSCARX(a;A1:J1;COLUMNA(A1:J1)));c;ENCOL(TRANSPONER(a&","&b));d;ENCOL(SI(LARGO(c)<>3;c;NOD());3);e;FILTRAR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3));NO(ESERROR(ABS(ENCOL(TRANSPONER(SI(A2:J4=0;NOD();A2:J4));3)))));f;d&","&e;xx;TEXTOANTES(f;",");yy;TEXTOANTES(TEXTODESPUES(f;",");",");g;SI.ERROR(xx&","&yy&","&TEXTOANTES(e;",");xx&","&yy&","&e);h;SI.ERROR(xx&","&yy&","&TEXTODESPUES(e;",");NOD());i;ENCOL(APILARH(g;h);3);APILARH(TEXTOANTES(i;",");TEXTODESPUES(TEXTOANTES(i;",";2);",");TEXTODESPUES(i;",";2)))

Ya tenemos la expresión algo más corta.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Transponer tabla

Entradas recientes

Comentarios