Modelo en columnas

Jaime Franco Jimenez
15 jun 2024
4 Min. de lectura

Tenemos el siguiente modelo:

Debemos de crear el modelo como sigue:

En la celda D2, usamos LET, creamos una variable, y, almacenamos el rango A2:A731, creamos otra variable, y, almacenamos el rango B2:B731.

=LET(xx;A2:A731;yy;B2:B731

Creamos otra variable, la llamamos añosunicos, nos traemos los años únicos de la variable “xx”.

=LET(xx;A2:A731;yy;B2:B731;añosunicos;UNICOS(AÑO(xx));añosunicos)

Creamos otra variable, la llamamos mesesunicos, usamos la función APILARV, como argumento matriz1, usamos la función UNICOS, como argumento matriz, ponemos la variable “xx”, como argumento formato de TEXTO, entre comillas dobles, ponemos “mmm”, cerramos paréntesis, como argumento matriz2, ponemos blanco.

=LET(xx;A2:A731;yy;B2:B731;añosunicos;UNICOS(AÑO(xx));mesesunicos;APILARV(UNICOS(TEXTO(xx;"mmm"));" ");mesesunicos)

Tenemos la abreviatura de los meses más una fila en blanco.

Creamos otra variable, la llamamos mesesdobles, usamos la función APILARV, como argumento matriz1, ponemos la variable mesesunicos, como argumento matriz2, volvemos a poner la variable mesesunicos, como argumento matriz3, ponemos un espacio.

=LET(xx;A2:A731;yy;B2:B731;añosunicos;UNICOS(AÑO(xx));mesesunicos;APILARV(UNICOS(TEXTO(xx;"mmm"));" ");mesesdobles;APILARV(mesesunicos;mesesunicos;" ");mesesdobles)

Tenemos el cuadro de los meses preparados para los dos años.

Creamos otra variable, la llamamos añosytotales, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable mesesunicos es igual a ene, como argumento valor si verdadero, usamos la función ENFILA, como argumento ponemos la variable añosunicos.

=LET(xx;A2:A731;yy;B2:B731;añosunicos;UNICOS(AÑO(xx));mesesunicos;APILARV(UNICOS(TEXTO(xx;"mmm"));" ");mesesdobles;APILARV(mesesunicos;mesesunicos;" ");añosytotales;SI(mesesunicos="ene";ENFILA(añosunicos));añosytotales)

Tenemos una matriz desbordada de dos columnas, en cada columna, en la primera fila tenemos los años, en el resto de las celdas hay un error.

Tenemos que añadir otra línea, donde aparezca la palabra Total seguido de los años, volvemos al condicional SI, vamos a añadir el argumento valor si falso, volvemos a poner otro condicional SI, como argumento prueba lógica, preguntamos si la variable mesesunicos es igual a blanco, como argumento valor si verdadero, entre comillas dobles, ponemos la palabra Total, concatenamos con la función ENFILA, como argumento ponemos la variable añosunicos, como argumento valor si falso, ponemos blanco.

Ya tenemos el modelo en dos columnas con los años y la palabra Total junto con los años.

Creamos otra variable, la llamamos modeloañostotal, usamos la función ENCOL, como argumento matriz, transponemos la variable añosytotales.

Ya tenemos el modelo de los años y total preparados.

Creamos otra variable, usamos la función PIVOTARPOR, como argumento row_fields, usamos la función MES, como argumento ponemos la variable “xx”, como argumento col_fields, ponemos la función AÑO, como argumento, ponemos la variable “xx”, como argumento values, ponemos la variable “yy”, como argumento función, ponemos la función APILARH, como argumento matriz1, ponemos SUMA, como argumento matriz2, ponemos PORCENTAJEDE, cerramos paréntesis.

Tenemos un resumen por meses y por años de ventas y porcentajes.

Creamos otra variable, usamos la función FILTRAR, como argumento array, ponemos la variable pivota, como argumento include, ponemos la función TOMAR, como argumento matriz, ponemos la variable pivota, como argumento filas, ponemos 1, e, igualamos, volvemos a usar la función TOMAR, como argumento matriz, ponemos la variable añosunicos, como argumento filas, ponemos 1, cerramos paréntesis, es decir, vamos a filtrar la variable pivota, siempre que la primera fila de dicha variable sea igual al primer año (2022).

Las dos filas primeras están de más, por lo que antes de la función FILTRAR, usamos la función EXCLUIR, como argumento matriz, es la función FILTRAR, como argumento filas, ponemos 2.

Creamos otra variable, la expresión es la misma que la usada para la variable total1, lo único que va a cambiar la siguiente expresión, TOMAR(añosunicos;1)), la vamos a cambiar por la función ELEGIRFILAS, como argumento matriz, ponemos la variable añosunicos, como argumento filas, ponemos 2.

=LET(xx;A2:A731;yy;B2:B731;añosunicos;UNICOS(AÑO(xx));mesesunicos;APILARV(UNICOS(TEXTO(xx;"mmm"));" ");mesesdobles;APILARV(mesesunicos;mesesunicos;" ");añosytotales;SI(mesesunicos="ene";ENFILA(añosunicos);SI(mesesunicos=" ";"Total "&ENFILA(añosunicos);""));modeloañostotal;ENCOL(TRANSPONER(añosytotales));pivota;PIVOTARPOR(MES(xx);AÑO(xx);yy;APILARH(SUMA;PORCENTAJEDE));total1;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=TOMAR(añosunicos;1));2);total2;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=ELEGIRFILAS(añosunicos;2));2);total2)

Ya tenemos los totales y porcentajes de cada año., incluido, el total por años, y, el porcentaje total.

Creamos otra variable, la llamamos trimestres, vamos a calcular el trimestre para cada año, para ello, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable mesesdobles es igual a ene, en ese caso que ponga 1, como argumento valor si falso, ponemos otro condicional SI, preguntamos si la variable mesesdobles es igual a abr, en ese caso, que ponga 2, como argumento valor si falso del segundo condicional SI, preguntamos si la variable mesesdobles, es igual a jul, en ese caso, que ponga 3, como argumento valor si falso del tercer condicional SI, preguntamos si la variable mesesdobles, es igual a oct, en ese caso, que ponga 4, como argumento valor si falso del cuarto condicional SI, ponemos blanco, cerramos paréntesis de los condicionales.

=LET(xx;A2:A731;yy;B2:B731;añosunicos;UNICOS(AÑO(xx));mesesunicos;APILARV(UNICOS(TEXTO(xx;"mmm"));" ");mesesdobles;APILARV(mesesunicos;mesesunicos;" ");añosytotales;SI(mesesunicos="ene";ENFILA(añosunicos);SI(mesesunicos=" ";"Total "&ENFILA(añosunicos);""));modeloañostotal;ENCOL(TRANSPONER(añosytotales));pivota;PIVOTARPOR(MES(xx);AÑO(xx);yy;APILARH(SUMA;PORCENTAJEDE));total1;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=TOMAR(añosunicos;1));2);total2;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=ELEGIRFILAS(añosunicos;2));2);trimestres;SI(mesesdobles="ene";1;SI(mesesdobles="abr";2;SI(mesesdobles="jul";3;SI(mesesdobles="Oct";4;""))));trimestres)

Obtenemos el trimestre de cada mes para cada año.

Tenemos una última fila vacía, pues, volvemos a usar la función EXCLUIR, y, la eliminamos.

=LET(xx;A2:A731;yy;B2:B731;añosunicos;UNICOS(AÑO(xx));mesesunicos;APILARV(UNICOS(TEXTO(xx;"mmm"));" ");mesesdobles;APILARV(mesesunicos;mesesunicos;" ");añosytotales;SI(mesesunicos="ene";ENFILA(añosunicos);SI(mesesunicos=" ";"Total "&ENFILA(añosunicos);""));modeloañostotal;ENCOL(TRANSPONER(añosytotales));pivota;PIVOTARPOR(MES(xx);AÑO(xx);yy;APILARH(SUMA;PORCENTAJEDE));total1;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=TOMAR(añosunicos;1));2);total2;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=ELEGIRFILAS(añosunicos;2));2);trimestres;EXCLUIR(SI(mesesdobles="ene";1;SI(mesesdobles="abr";2;SI(mesesdobles="jul";3;SI(mesesdobles="Oct";4;""))));-1);trimestres)

Usamos el argumento cálculo de LET, usamos la función APILAR, como argumento matriz1, ponemos la variable modeloañostotal, como argumento matriz2, ponemos la variable trimestres, como argumento matriz3, ponemos la variable mesesdobles, como argumento matriz4, ponemos la función APILARV, como argumento matriz1, ponemos la variable total1, como argumento matriz2, ponemos la variable total2, cerramos paréntesis.

=LET(xx;A2:A731;yy;B2:B731;añosunicos;UNICOS(AÑO(xx));mesesunicos;APILARV(UNICOS(TEXTO(xx;"mmm"));" ");mesesdobles;APILARV(mesesunicos;mesesunicos;" ");añosytotales;SI(mesesunicos="ene";ENFILA(añosunicos);SI(mesesunicos=" ";"Total "&ENFILA(añosunicos);""));modeloañostotal;ENCOL(TRANSPONER(añosytotales));pivota;PIVOTARPOR(MES(xx);AÑO(xx);yy;APILARH(SUMA;PORCENTAJEDE));total1;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=TOMAR(añosunicos;1));2);total2;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=ELEGIRFILAS(añosunicos;2));2);trimestres;EXCLUIR(SI(mesesdobles="ene";1;SI(mesesdobles="abr";2;SI(mesesdobles="jul";3;SI(mesesdobles="Oct";4;""))));-1);APILARH(modeloañostotal;trimestres;mesesdobles;APILARV(total1;total2)))

Aceptamos, y, ya lo tenemos.

Pero, tenemos una última fila de más, pues, volvemos a usar la función EXCLUIR.

=LET(xx;A2:A731;yy;B2:B731;añosunicos;UNICOS(AÑO(xx));mesesunicos;APILARV(UNICOS(TEXTO(xx;"mmm"));" ");mesesdobles;APILARV(mesesunicos;mesesunicos;" ");añosytotales;SI(mesesunicos="ene";ENFILA(añosunicos);SI(mesesunicos=" ";"Total "&ENFILA(añosunicos);""));modeloañostotal;ENCOL(TRANSPONER(añosytotales));pivota;PIVOTARPOR(MES(xx);AÑO(xx);yy;APILARH(SUMA;PORCENTAJEDE));total1;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=TOMAR(añosunicos;1));2);total2;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=ELEGIRFILAS(añosunicos;2));2);trimestres;EXCLUIR(SI(mesesdobles="ene";1;SI(mesesdobles="abr";2;SI(mesesdobles="jul";3;SI(mesesdobles="Oct";4;""))));-1);EXCLUIR(APILARH(modeloañostotal;trimestres;mesesdobles;APILARV(total1;total2));-1))

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Modelo en columnas

Entradas recientes

Comentarios