Total por nombre y año

Jaime Franco Jimenez
24 sept 2023
3 Min. de lectura

Tenemos el siguiente modelo:

Debemos de crear un modelo donde aparezcan en vertical los nombres, en horizontal los años únicos, y, debajo los totales por año y nombre.

Si nos fijamos en los años, vemos que cada año se encuentra en una celda combinada, y, ocupa el numero de celdas que cantidades hay para cada año, eso, nos complica el calculo del total por año, por lo que primero, debemos de crear un encabezado donde en cada celda se encuentre el año correspondiente.

Por ejemplo, en la celda C2, tenemos el año 2019.

Si en una celda ponemos:

=C2:Q2

Vemos que nos devuelve los años, pero son celdas combinadas, vemos que pone cero donde no hay año.

Vamos a crear el encabezado donde vamos a sustituir cada cero por el año que le corresponde.

En la celda B11, ponemos LET, creamos una variable, transponemos los años, probamos variable.

=LET(a;TRANSPONER(C2:Q2);a)

Tememos los años en una columna.

Creamos otra variable, usamos SCAN, como argumento valor inicial, ponemos 0, como argumento array, ponemos la variable “a”.

=LET(a;TRANSPONER(C2:Q2);b;SCAN(0;a

Como argumento función, ponemos LAMBDA, y, creamos dos variables, la primera variable, empezara valiendo cero, y, la segunda variable, vale cada valor de la variable “a”.

=LET(a;TRANSPONER(C2:Q2);b;SCAN(0;a;LAMBDA(x;y

Como argumento calculo de LAMBDA, ponemos el condicional SI, como argumento prueba lógica, preguntamos si la variable “y” es igual a cero, como hemos dicho, la variable “y” vale cada valor de la variable “a”, entonces, vamos a buscar un valor que sea igual a cero.

=LET(a;TRANSPONER(C2:Q2);b;SCAN(0;a;LAMBDA(x;y;SI(y=0

Como argumento valor si verdadero, sumamos las variables “x”, e, “y”.

=LET(a;TRANSPONER(C2:Q2);b;SCAN(0;a;LAMBDA(x;y;SI(y=0;x+y

Como argumento valor si falso, multiplicamos cero por la variable “x”, y, sumamos la variable “y”.

=LET(a;TRANSPONER(C2:Q2);b;SCAN(0;a;LAMBDA(x;y;SI(y=0;x+y;0*x+y)))

Veamos cómo funciona, la primera celda es 2019, que es el primer valor de la variable “y”, ¿la variable Y es igual a cero?, la respuesta es no, entonces ejecuta el valor si falso del condicional SI, es, 0*0+2019, el segundo cero es el argumento valor inicial, y, la variable “y”, vale el primer valor de la variable “a”, como resultado tenemos 2019.

Ahora, el argumento valor inicial, vale 2019, y, el siguiente valor de la variable “y” es cero.

Preguntamos, ¿la variable Y es igual a cero?, la respuesta es sí, entonces, se ejecuta el argumento valor si verdadero del condicional SI, “x+y”, la variable “x” vale 2019, la variable “y”, vale cero, la suma de ambas variables, nos devuelve 2019.

Cuando llegamos al año 2020, y, preguntamos ¿la variable Y es igual a cero?, la respuesta es no, entonces, ejecuta el argumento valor si falso del condicional SI, es decir, “0*x+y”, es decir, “0*2019+2020, como resultado, tenemos 2020, así con el resto de las cantidades.

Esta función tal cual esta, se usa para calcular un acumulado.

Seguimos…

Creamos otra variable, usamos la función APILARV, como argumento matriz1, usamos APILARH, como argumento matriz1, entre comillas dobles, ponemos Nombre, como argumento matriz2, transponemos la variable “b”, cerramos paréntesis de APILARH, como argumento matriz2 de APILARV, seleccionamos el rango B3:Q9.

=LET(a;TRANSPONER(C2:Q2);b;SCAN(0;a;LAMBDA(x;y;SI(y=0;x+y;0*x+y)));c;APILARV(APILARH("Nombres";TRANSPONER(b));B3:Q9);c)

Ya tenemos el modelo preparado para poder calcular el total por año y nombre.

En la celda T11, nos tramos los años únicos del rango C11:Q11, pero, usamos el argumento by_col de UNICOS, y, seleccionamos devolver columnas únicas.

=UNICOS(C11:Q11;VERDADERO)

En la celda S12, nos traemos los nombres.

En la celda T12, abrimos paréntesis, seleccionamos el rango B12:B18, e, igualamos a S12, cerramos paréntesis.

=(B12:B18=S12)

Obtenemos una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.

Volvemos a la expresión, ponemos el símbolo de asterisco, abrimos otro paréntesis, seleccionamos el rango C11:Q11, e, igualamos a T11, cerramos paréntesis.

=(B12:B18=S12)*(C11:Q11=T11)

Obtenemos 1 donde hay coincidencia, y, o donde no la hay.