Añadir columna con valores máximos y años correspondientes

Jaime Franco Jimenez
4 ago 2023
6 Min. de lectura

Para el siguiente ejemplo, tenemos un modelo, donde tenemos centros comerciales en filas, y, en columnas, tenemos años, y, las ventas para cada año y centro comercial.

Vamos a añadir dos columnas más, en la primera de ellas, vamos a extraer el valor máximo de ventas de todos los años para cada centro comercial, y, en la segunda columna, decir que el valor máximo puede repetirse en más de un año, queremos los años de cada centro comercial donde aparece el valor máximo.

Pero, vamos a crear una matriz única, donde tengamos todos los datos, es decir, el modelo, y, columnas a añadir.

En la celda A9, usamos LET, creamos una variable, para dicha variable, usamos APILARH, y almacenamos el modelo, probamos dicha variable.

=LET(a;APILARH(A1:I7);a)

Vamos a crear otra variable, pero el calculo a realizar, lo vamos a realizar en una celda aparte, y, después lo añadimos a la función LET.

Vamos a hacer uso de la función ESNUMERO, donde como argumento es la matriz desbordada obtenida anteriormente.

=ESNUMERO(A9#)

Obtenemos una matriz desbordada con VERDADERO donde es número, y, FALSO donde no lo es.

Con el condicional SI, vamos a preguntar que, si el resultado de ESNUMERO es VERDADERO, pero, no debemos de igualar a VERDADERO, porque de forma predeterminada, comparara con VERDADERO, en ese caso, que nos devuelva la matriz desbordada, en caso contrario, debe de poner un texto en blanco.

Vamos a obtener una matriz desbordada, en donde era VERDADERO tenemos los valores numéricos, y, donde era FALSO, aparece en blanco.

=SI(ESNUMERO(A9#);A9#;"")

Como tenemos que trabajar con cada columna de forma independiente, vamos a hacer uso de la función BYROW, como argumento array, es la expresión anterior.

=BYROW(SI(ESNUMERO(A9#);A9#;"")

Como argumento calculo, ponemos LAMBDA, declaramos una variable, que almacena el argumento array.

=BYROW(SI(ESNUMERO(A9#);A9#;"");LAMBDA(x

Como argumento calculo de LAMBDA, ponemos la función MAX, y, como argumento ponemos la variable “x”, cerramos paréntesis.

=BYROW(SI(ESNUMERO(A9#);A9#;"");LAMBDA(x;MAX(x)))

Aceptamos, y, tenemos una matriz desbordada con el valor máximo para cada centro, y, blanco para la primera fila.

Copiamos la expresión, vamos a la expresión LET, declaramos otra variable, pegamos la expresión, y, cambiamos A9# por la variable “a”.

Probamos la variable.

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));b)

Obtenemos el mismo resultado que el anterior.

Vemos que la primera celda de la matriz desbordada obtenida es cero, pues, vamos a sustituir dicho cero por la palabra Máximo años, para ello, usamos la función SUSTITUIR, como argumento texto, es la función BYROW.

=LET(a;APILARH(A1:I7);b;SUSTITUIR(BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)))

Como argumento texto original, ponemos cero, como argumento texto nuevo, entre comillas dobles, ponemos “Máximo años”.

Cerramos paréntesis.

=LET(a;APILARH(A1:I7);b;SUSTITUIR(BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));0;"Maximo años");b)

Aceptamos, y, vemos que en la primera celda aparece la palabra Máximo años.

Usamos la función APILARH, como argumento matriz1, es la variable “a”, y, como argumento matriz2, ponemos la variable “b”.

Cerramos paréntesis.

Probamos la variable.

=LET(a;APILARH(A1:I7);b;SUSTITUIR(BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));0;"Años maximos");c;APILARH(a;b);c)

Obtenemos una matriz desbordada con el modelo mas la columna años máximos.

Pero, que ocurre si uno de los valores máximos, termina en cero, vamos a cambiar el valor del año 2016 para el centro comercial C.C. Nervion, por el valor 90.

Si miramos la matriz desbordada obtenida anteriormente donde hemos obtenido los valores máximos, vemos que para el valor 90, en vez de 0, aparece Máximo años.

A mi se me ocurre lo siguiente:

Vamos a borrar la función SUSTITUIR de nuestra expresión.

=LET(a;APILARH(A1:I7);b;SUSTITUIR(BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));0;"Maximo años");b)

Quedaría:

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));b)

Volvemos a obtener la matriz desbordada con los valores máximos y blanco para la primera celda, donde podemos ver el número 90.

Vamos a crear otra variable, ya sabéis que suelo usar la función LET para ir dividiendo los pasos, y, no tener una última función muy larga.

Usamos la función FILTRAR, como argumento array, es la variable “b”, como argumento include es si la variable “b” es igual a 0, cerramos paréntesis, probamos la variable.

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));c;FILTRAR(b;b=0);c)

Nos devuelve el único valor cero que tenemos, que es la primera celda.

Usamos la función SUSTITUIR, como argumento texto es la función FILTRAR.

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));c;SUSTITUIR(FILTRAR(b;b=0)

Como argumento texto antiguo, ponemos 0, como argumento texto nuevo, entre comillas dobles, ponemos máximo años.

Cerramos paréntesis.

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));c;SUSTITUIR(FILTRAR(b;b=0);0;"Maximo años");c)

Aceptamos, y, vemos que aparece la palabra máximo años, pero los valores máximos, pues, volvemos al argumento texto nuevo de SUSTITUIR, antes de la palabra Máximo años, ponemos la función APILARV, como argumento matriz1, es la palabra Máximo años.

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));c;SUSTITUIR(FILTRAR(b;b=0);0;APILARV("Maximo años"

Como argumento matriz2, filtramos la variable “b”, siempre que dicha variable, sea diferente a 0.

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));c;SUSTITUIR(FILTRAR(b;b=0);0;APILARV("Maximo años";FILTRAR(b;b<>0)));c)

Vemos que ya tenemos el encabezado y los valores máximos, independientemente, de que hay valores ceros en los valores máximos.

Continuamos…

Creamos otra variable.

Usamos APILARH, como argumento matriz1, es la variable “a”, como argumento matriz2, es la variable “c”, probamos dicha variable.

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));c;SUSTITUIR(FILTRAR(b;b=0);0;APILARV("Maximo años";FILTRAR(b;b<>0)));d;APILARH(a;c);d)

Obtenemos el modelo mas una columna a la derecha con los valores máximos para cada centro comercial.

Creamos otra variable, usamos la función INDICE, como argumento array, es la variable “d”, omitimos le argumento número de fila, como argumento numero de columna, usamos la función COLUMNAS, y, como argumento ponemos la variable “d”, probamos dicha variable.

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));c;SUSTITUIR(FILTRAR(b;b=0);0;APILARV("Maximo años";FILTRAR(b;b<>0)));d;APILARH(a;c);e;INDICE(d;;COLUMNAS(d));e)

Obtenemos la última columna.

Vamos a quedarnos con el valor absoluto, para ello, usamos la función ABS.

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));c;SUSTITUIR(FILTRAR(b;b=0);0;APILARV("Maximo años";FILTRAR(b;b<>0)));d;APILARH(a;c);e;ABS(INDICE(d;;COLUMNAS(d)));e)

Si nos fijamos en los resultados antes de insertar la función ABS, vemos que los valores numéricos están alineados a la izquierda, quiere decir, que están en formato de texto, ahora, quedan alineados a la derecha, están en formato de número, pero como la palabra no tiene un valor absoluto, nos devuelve un error.

Usamos la función SI.ERROR, y, donde haya error, ponemos un texto en blanco.

Vamos a preguntar si la expresión anterior es igual a la variable “d”.

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

Con el condicional SI, vamos a preguntar si la variable “e” es VERDADERO, que nos devuelva la variable “d”, en caso contrario, que devuelva un texto en blanco.

Tenemos una matriz desbordada, donde era VERDADERO tenemos el valor máximo que le corresponde, y, donde era FALSO, tenemos blanco.

Pero, queremos los años, no las cantidades, entonces, vamos al argumento valor si verdadero del condicional SI, borramos la variable “d”, y, ponemos la función INDICE, como argumento array es la variable “d”, como argumento numero de fila, ponemos 1, como argumento numero de columna, usamos SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos la función COLUMNAS, y, como argumento ponemos la variable “d”.

Es decir, si hay coincidencia que nos devuelva los encabezados correspondientes.

=LET(a;APILARH(A1:I7);b;BYROW(SI(ESNUMERO(a);a;"");LAMBDA(x;MAX(x)));c;SUSTITUIR(FILTRAR(b;b=0);0;APILARV("Maximo años";FILTRAR(b;b<>0)));d;APILARH(a;c);e;SI(SI.ERROR(ABS(INDICE(d;;COLUMNAS(d)));"")=d;INDICE(d;1;SECUENCIA(;COLUMNAS(d)));"");e)

Vemos que las cantidades han sido sustituidas por los encabezados.

Nos queda poner estos años en filas, ignorando celdas vacías, para ello, creamos otra variable, punto y coma, como tenemos que trabajar con cada columna de forma independiente, ponemos la función BYROW, como argumento array, es la variable “e”, como argumento función de BYROW, ponemos LAMBDA, creamos una variable, que almacena el argumento array.

Como argumento calculo de LAMBDA, ponemos la función UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, ignoramos celdas vacías, y, como argumento texto, es la variable “x”.

Probamos la variable.

Obtenemos una matriz desbordada donde en la primera celda aparece en blanco, y, en el resto de las celdas, aparecen los años donde tenemos el valor máximo.

Pero, queremos ver solo los años, no la palabra año, para ello, vamos a la expresión de la variable “f”, antes de BYROW, ponemos la función SUSTITUIR, como argumento texto, es la función BYROW, como argumento texto antiguo, es la palabra Año, como argumento texto nuevo, ponemos un texto en blanco, cerramos paréntesis.

Vemos que solo aparecen los años.

Aun, debemos de traernos el resto del modelo, pues, vamos a crear otra variable, usamos APILARH, como argumento matriz1, es la variable “d”, como argumento matriz2, es la variable “f”.

Probamos la variable.

Ya tenemos nuestro modelo.

Pero, vemos que hay un blanco, justamente en ese blanco debe de aparecer la palabra Año/s, pues, vamos a usar el argumento calculo de LET, usamos el condicional SI, preguntamos si la variable “g” es igual a blanco, que ponga la palabra Año/s, en caso contrario, que devuelva la variable “g”.

Ya lo tenemos.

Y si queremos tener los encabezados de los años actualizados al año actual, es decir, estamos en el año 2023, tenemos 8 años, quiere decir, que debe de ir del año 2016 al año 2023, el año que viene debe de ir desde el año 2017 al año 2014, así consecutivamente.

Borramos todos los años.

Vamos al modelo original, a la celda B2, donde tenemos el primer año.

En dicha celda (B2), ponemos la función AÑO, y, como argumento ponemos la función HOY().

=AÑO(HOY())

Obtenemos el año actual.

Como estamos trabajando con 8 años, al año actual debemos de restar 8, para ello, ponemos el signo de resta, volvemos a poner la expresión anterior, y, restamos 7.

AÑO(HOY())-7

Usamos la función SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 8, como argumento inicio, es la expresión anterior, y, como argumento paso, ponemos 1.

=SECUENCIA(;8;AÑO(HOY())-7;1)

Pero, debe de ir acompañado de la palabra año, por lo que después del signo igual, entre comillas dobles, ponemos Año, y, concatenamos con la expresión anterior.

="Año "&SECUENCIA(;8;AÑO(HOY())-7;1)

Aceptamos, y, ya tendremos siempre los años actualizados, sea el año que sea.

Como hemos hecho en ejemplos anteriores, podemos dar un formato condicional.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Añadir columna con valores máximos y años correspondientes

Entradas recientes

Comentarios