Normalizar modelo

Tenemos un cuadro donde tenemos un departamento, una serie de años, para los años 2022, y, 2023 tenemos las ventas actuales, y, para los años 2022, 2023, y, 2024 el presupuesto.

Debemos de crear un modelo donde en la primera columna deben de aparecer los departamentos por cada año, en la siguiente columna, los años, en la tercera columna las ventas actuales para cada departamento, y, en la ultima columna el presupuesto para cada departamento.

El modelo debe de quedar como sigue:

En la celda I3, usamos LET, creamos una variable, usamos la función CONCAT, como argumento seleccionamos el rango B4:B8 (departamentos), concatenamos con un espacio, cerramos paréntesis, concatenamos con barra inclinada.

=LET(a;CONCAT(B4:B8&" ")&"/";a)

En una fila tenemos todos los departamentos separados por espacio, y, al final la barra inclinada.

Antes de CONCAT, usamos la función REPETIR, como argumento texto, es la función CONCAT, como argumento número de veces, ponemos 3.

=LET(a;REPETIR(CONCAT(B4:B8&" ")&"/";3);a)

En una fila, tenemos repetidos los departamentos tres veces.

Antes de REPETIR, usamos la función DIVIDIRTEXTO, como argumento texto, es la función REPETIR, como argumento delimitador de columna, entre comillas dobles, ponemos un espacio, como argumento delimitador de fila, entre comillas dobles, ponemos la barra inclinada.

=LET(a;DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");a)

Tenemos una matriz de cuatro columnas, en cada columna tenemos un departamento, y, en la ultima fila tenemos un error.

Antes de la funcion DIVIDIRTEXTO, usamos la funcion ENCOL, donde como argumento matriz que es la funcion DIVIDIRTEXTO, como argumento ignorar, seleccionamos 3, es decir, ignorar blancos y errores.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);a)

Creamos otra variable, usamos la función FILTRAR, como argumento array, ponemos la variable “a”, como argumento include, ponemos la variable “a”, y, comparamos con distinto a blanco.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);b;FILTRAR(a;a<>"");b)

Ya tenemos en vertical tres veces los departamentos.

Creamos otra variable, usamos la función UNICOS, como argumento matriz seleccionamos el rango C3:G3 (años), como argumento by_col, seleccionamos ordenar por columnas.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);b;FILTRAR(a;a<>"");c;UNICOS(C3:G3;VERDADERO);c)

Tenemos los años únicos.

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, preguntamos si el rango B4:B8 es igual a la variable “b”, como argumento valor si verdadero, ponemos la variable “c”, omitimos el argumento valor si falso.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);b;FILTRAR(a;a<>"");c;UNICOS(C3:G3;VERDADERO);d;SI(B4:B8=b;c);d)

Tenemos una matriz desbordada de tres columnas, una por año, con el año donde hay coincidencia, y, error donde no hay coincidencia.

Transponemos.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);b;FILTRAR(a;a<>"");c;UNICOS(C3:G3;VERDADERO);d;TRANSPONER(SI(B4:B8=b;c));d)

Antes de TRANSPONER, usamos la función ENCOL, como argumento matriz es la función TRANSPONER, como argumento ignorar, seleccionamos 3.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);b;FILTRAR(a;a<>"");c;UNICOS(C3:G3;VERDADERO);d;ENCOL(TRANSPONER(SI(B4:B8=b;c));3);d)

Ya tenemos los años para cada departamento.

Creamos otra variable, usamos la función TOMAR, como argumento matriz, seleccionamos el rango C4:G8 (ventas actuales), omitimos el argumento filas, como argumento columnas, ponemos 2, vamos a tomar las dos primeras columnas del rango C4:G8.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);b;FILTRAR(a;a<>"");c;UNICOS(C3:G3;VERDADERO);d;ENCOL(TRANSPONER(SI(B4:B8=b;c));3);e;TOMAR(C4:G8;;2);e)

Transponemos.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);b;FILTRAR(a;a<>"");c;UNICOS(C3:G3;VERDADERO);d;ENCOL(TRANSPONER(SI(B4:B8=b;c));3);e;TRANSPONER(TOMAR(C4:G8;;2));e)

Antes de TRANSPONER usamos la función ENCOL.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);b;FILTRAR(a;a<>"");c;UNICOS(C3:G3;VERDADERO);d;ENCOL(TRANSPONER(SI(B4:B8=b;c));3);e;ENCOL(TRANSPONER(TOMAR(C4:G8;;2)));e)

Ya tenemos las ventas actuales para cada departamento.

Creamos otra variable, es la misma expresión que para la variable “e, pero, cambiamos el argumento columnas de TOMAR, y, ponemos -3, para tomar las últimas tres columnas.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);b;FILTRAR(a;a<>"");c;UNICOS(C3:G3;VERDADERO);d;ENCOL(TRANSPONER(SI(B4:B8=b;c));3);e;ENCOL(TRANSPONER(TOMAR(C4:G8;;2)));f;ENCOL(TRANSPONER(TOMAR(C4:G8;;-3)));f)

Tenemos las ventas presupuestadas para cada departamento.

Usamos el argumento calculo de LET, usamos la función APILARH, como argumento matriz1, ponemos la variable “b”, como argumento matriz2, ponemos la variable “d”, como argumento matriz3, ponemos la variable “e”.

=LET(a;ENCOL(DIVIDIRTEXTO(REPETIR(CONCAT(B4:B8&" ")&"/";3);" ";"/");3);b;FILTRAR(a;a<>"");c;UNICOS(C3:G3;VERDADERO);d;ENCOL(TRANSPONER(SI(B4:B8=b;c));3);e;SI.ERROR(APILARH(ENCOL(TRANSPONER(TOMAR(C4:G8;;2)));ENCOL(TRANSPONER(TOMAR(C4:G8;;-3))));"");APILARH(b;d;e))

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco Garcia