Total cada 5 años

Jaime Franco Jimenez
10 mar 2024
5 Min. de lectura

Tenemos una serie de años, cada año tiene asignado un valor.

Debemos de calcular la suma y el porcentaje para cada cinco años.

En la celda D2, usamos LET, creamos una variable, seleccionamos el rango A2:A100, creamos otra variable, y, seleccionamos el rango B2:B100.

=LET(a;A2:A100;b;B2:B100

Creamos otra variable, usamos la funcion ORDENAR, como argumento matriz, usamos la funcion UNICOS, como argumento matriz de UNICOS, ponemos la variable “a”, vamos a obtener los años únicos ordenados de menor a mayor.

=LET(a;A2:A100;b;B2:B100;c;ORDENAR(UNICOS(a));c)

Debemos de crear un reporte con el inicio de cada año y el final para cada año, como sigue:

Para realizar este cuadro, debe de ir desde el año mínimo al año máximo haciendo un salto de 1, pero, vemos que del año 2005 pasa al año 2007, por lo que el informe no va a salir bien.

Vamos a crear una lista de años desde el año mínimo al año máximo sin que falte ningún año, para ello, creamos otra variable, usamos la funcion SECUENCIA, como argumento filas, restamos el valor máximo de la variable “c” menos el valor mínimo de la variable “c”, y, sumamos 1, omitimos el argumento columnas, como argumento inicio, es el valor mínimo de la variable “c”.

=LET(a;A2:A100;b;B2:B100;c;ORDENAR(UNICOS(a));d;SECUENCIA(MAX(c)-MIN(c)+1;;MIN(c));d)

Ya tenemos la secuencia de años sin que falte ningún año.

Lo siguiente es calcular la posición donde debemos de empezar a contar años, por ejemplo, a partir del año 1990, debemos de tomar los años 1991, 1992, 1993, y, 1994, que son cinco años, creamos otra variable, usamos la funcion SECUENCIA, como argumento filas, usamos la funcion CONTAR, como argumento valor1, ponemos la variable “d”, dividimos entre 4, que son los saltos que debemos de hacer para cada año, omitimos el argumento columnas, e, inicio, como argumento paso, ponemos 5.

=LET(a;A2:A100;b;B2:B100;c;ORDENAR(UNICOS(a));d;SECUENCIA(MAX(c)-MIN(c)+1;;MIN(c));e;SECUENCIA(CONTAR(d)/4;;;5);e)

Ya tenemos la posición donde debemos de empezar a contar.

Creamos otra variable, usamos la funcion REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, ponemos la variable “e”, como argumento funcion, ponemos LAMBDA, y, declaramos dos variables.

=LET(a;A2:A100;b;B2:B100;c;ORDENAR(UNICOS(a));d;SECUENCIA(MAX(c)-MIN(c)+1;;MIN(c));e;SECUENCIA(CONTAR(d)/4;;;5);f;REDUCE("";e;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 funcion SECUENCIA, como argumento filas, ponemos 5, que son los saltos que debemos de dar por años, omitimos el argumento columnas, como argumento inicio, ponemos la variable “y”.

=LET(a;A2:A100;b;B2:B100;c;ORDENAR(UNICOS(a));d;SECUENCIA(MAX(c)-MIN(c)+1;;MIN(c));e;SECUENCIA(CONTAR(d)/4;;;5);f;REDUCE("";e;LAMBDA(x;y;APILARH(x;SECUENCIA(5;;y))));f)

Tenemos una matriz desbordada de 9 columnas, donde tenemos las filas a recuperar.

El recuento de años es de 34, por lo que la primera y última columna están de más, creamos otra variable, usamos la funcion ELEGIRCOLS, como argumento matriz, ponemos la variable “f”, como argumento numero de columna1, usamos la funcion SECUENCIA, omitimos el argumento filas, como argumento columnas, usamos la funcion CONTARA, como argumento valor1, usamos la funcion TOMAR, como argumento matriz, ponemos la variable “f”, como argumento filas, ponemos 1, cerramos paréntesis, es decir, vamos a crear una secuencia en horizontal desde el numero 1 hasta el numero de columnas de la primera fila, restamos 2, como argumento inicio, ponemos 2, cerramos paréntesis, vamos a tener las columnas con las filas a rescatar.

Creamos otra variable, usamos la funcion INDICE, como argumento matriz, ponemos la variable “d”, como argumento numero de fila, ponemos la variable “g”, vamos a obtener una matriz de siete columnas, y, cinco filas, en cada columna vamos a tener los tramos para cada año.

=LET(a;A2:A100;b;B2:B100;c;ORDENAR(UNICOS(a));cc;BYROW(c;LAMBDA(x;SUMA(SI(a=x;b))));d;SECUENCIA(MAX(c)-MIN(c)+1;;MIN(c));e;SECUENCIA(CONTAR(d)/4;;;5);f;REDUCE("";e;LAMBDA(x;y;APILARH(x;SECUENCIA(5;;y))));g;ELEGIRCOLS(f;SECUENCIA(;CONTARA(TOMAR(f;1))-2;2));h;INDICE(d;g);h)

Tenemos un error, que lo vamos a sustituir por el último año más 1, aunque también, podemos usar la funcion AÑO y como argumento ponemos la funcion HOY(), es decir, el año actual, el problema es que si hacemos el ejercicio el año que viene, pondrá 2025, que no es el siguiente año al último año, después del signo igual, usamos la funcion SI.ERROR, como argumento valor, es la funcion INDICE, como argumento valor si error, sacamos el valor máximo de la variable “c” y sumamos 1.

Vemos que donde aparece un error aparece el siguiente año al año mayor.

Creamos otra variable, usamos la funcion BYROW, como argumento array, transponemos la variable “h”, como argumento funcion, ponemos LAMBDA, declaramos una variable, como argumento calculo de LAMBDA, al valor mínimo de la variable “x”, lo concatenamos con un guion medio, y, concatenamos con el valor máximo de la variable “x”.

Ya tenemos el cuadro con los años, donde debe de empezar y donde debe de acabar.

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, abrimos un paréntesis, ponemos la variable “a”, comparamos con mayor o igual, usamos la funcion IZQUIERDA, como argumento texto, usamos la funcion ENFILA, como argumento ponemos la variable “i”, como argumento numero de caracteres, ponemos 4, multiplicamos por 1, y, cerramos paréntesis.

Esta primera condición, va a comparar cada año de la variable “a” con cada primer año de la variable “i”.

Ponemos el símbolo de asterisco, abrimos otro paréntesis, para poner otra condición, ponemos la variable “a”, comparamos con menor o igual, ponemos la funcion DERECHA, como argumento texto, usamos ENFILA, como argumento ponemos la variable “i”, como argumento numero de caracteres, ponemos 4, multiplicamos por 1, y, cerramos paréntesis.

Como argumento valor si verdadero, ponemos la variable “b”, como argumento valor si falso, ponemos blanco.

Tenemos una matriz de siete columnas, en cada columna tenemos los valores para cada intervalo de años.

Creamos otra variable, sumamos la variable “j”.

=LET(a;A2:A100;b;B2:B100;c;ORDENAR(UNICOS(a));d;SECUENCIA(MAX(c)-MIN(c)+1;;MIN(c));e;SECUENCIA(CONTAR(d)/4;;;5);f;REDUCE("";e;LAMBDA(x;y;APILARH(x;SECUENCIA(5;;y))));g;ELEGIRCOLS(f;SECUENCIA(;CONTARA(TOMAR(f;1))-2;2));h;SI.ERROR(INDICE(d;g);MAX(c )+1);i;BYROW(TRANSPONER(h);LAMBDA(x;MIN(x)&"-"&MAX(x)));j;SI((a>=IZQUIERDA(ENFILA(i);4)*1)*(a<=DERECHA(ENFILA(i);4)*1);b;"");k;SUMA(j);k)

Creamos otra variable, usamos la funcion BYCOL, como argumento array, ponemos la variable “j”, como argumento funcion, ponemos LAMBDA, declaramos una variable, como argumento calculo de LAMBDA, sumamos la variable “·x”, concatenamos con un espacio, concatenamos con la funcion TEXTO, como argumento valor, dividimos la suma de la variable “x” entre la variable “k”, como argumento formato, entre comillas dobles, ponemos 0,00.

Tenemos los valores para cada intervalo de años junto con la diferencia porcentual respecto al total.

Antes de BYCOL, usamos ENCOL, para tener los resultados en vertical.

Usamos el argumento cálculo de LET, usamos la funcion APILARH, como argumento matriz1, ponemos la variable “i”, como argumento matriz2, usamos la funcion DIVIDIRTEXTO, como argumento texto, ponemos la variable “l”, como argumento delimitador de columna, entre comillas dobles, ponemos un espacio, multiplicamos por 1, como argumento matriz3, usamos la funcion TEXTODESPUES, como argumento texto, ponemos la variable “l”, como argumento delimitador, entre comillas dobles, ponemos un espacio, y, multiplicamos por 1.

Aceptamos, y, ya lo tenemos.

Nos colocamos antes de APIALRH, ponemos la funcion APILARV, como argumento matriz1, usamos una constante de matriz, abrimos unas llaves, entre comillas dobles, ponemos Años, ponemos barra inclinada, entre comillas dobles, ponemos Valores, ponemos barra inclinada, entre comillas dobles, ponemos Porcentaje, cerramos llaves, como argumento matriz2, es la funcion APILARH.

Aceptamos, y, hemos puesto el encabezado.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Total cada 5 años

Entradas recientes

Comentarios