☰

Devolver lista de valores y hacer coincidir con el tipo de producto

tag-icon • tag-icon microsoft-excel search-indexing
Devolver lista de valores y hacer coincidir con el tipo de producto

Estoy buscando una fórmula que cree un informe de vencimiento del acceso a determinadas formaciones.

Actualmente mi mesa se ve así:

Nombre Entrenamiento1 Formación2 Formación3
John 08/01/2023 08/01/2023 08/01/2023
Marca 08/01/2023 01/06/2023 10/01/2023
esteban 01/06/2023 01/06/2023 01/06/2023

Me gustaría crear un mecanismo que me muestre el vencimiento del entrenamiento dentro de 45 días a partir de ahora y vencido, con una lista de nombres, módulos y fecha en que, algo como:

Con vencimiento dentro de los 45 días:

Nombre Módulo Expira el
John Entrenamiento1 08/01/2023
John Formación2 08/01/2023
John Formación3 08/01/2023
Marca Entrenamiento1 08/01/2023

Venció:

Nombre Módulo Expira el
Marca Formación2 01/06/2023
esteban Entrenamiento1 01/06/2023
esteban Formación2 01/06/2023
esteban Formación3 01/06/2023

Logré obtener una fórmula para enumerar los nombres, pero no puedo entender cómo hacer coincidir la Capacitación en el encabezado o la fecha.

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:D)/(B:D<TODAY()+45), ROW(1:1))),"") / expiring within 45 days
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:D)/(B:D<TODAY()), ROW(1:1))),"") / expired

¿Cómo reconstruir la fórmula para que coincida con el encabezado de capacitación y la fecha de cada persona?

Respuesta1

Una forma de lograrlo es utilizar una LET()declaración que le permita almacenar resultados intermedios. De esa manera, podrá traer sus datos en el formato correcto y filtrarlos según sus condiciones, por ejemplo, fecha <hoy para las capacitaciones vencidas.

Amplié ligeramente su ejemplo para tener una cantidad diferente de nombres y módulos para que el procedimiento sea más fácil de digerir. Los datos se almacenan en el rango A1:D5:

Nombre Entrenamiento1 Formación2 Formación3
John 08/01/2023 08/01/2023 08/01/2023
Marca 08/01/2023 01/06/2023 10/01/2023
esteban 01/06/2023 01/06/2023 01/06/2023
Factura 01/06/2023 08/01/2023 10/01/2023

La LET()declaración queda de la siguiente manera:

=LET(
data,$A$1:$D$5,

nMod,COLUMNS(INDEX(data,1,))-1,
nRow,ROWS(INDEX(data,,1))-1,

tmpM,CHOOSEROWS(TRANSPOSE(INDEX(data,1,)),SEQUENCE(nMod,,2)),
modules,SORT(INDEX(tmpM,MOD(SEQUENCE(nMod*nRow)-1,nMod)+1)),

tmpN,CHOOSEROWS(INDEX(data,,1),SEQUENCE(nRow,,2)),
names,INDEX(tmpN,MOD(SEQUENCE(nMod*nRow)-1,nRow)+1),

dates,TOCOL(CHOOSEROWS(CHOOSECOLS(data,SEQUENCE(nMod,,2)),SEQUENCE(nRow,,2)),0,TRUE),

combinedTable, HSTACK(names,modules,dates),
filteredData,SORT(FILTER(combinedTable, INDEX(combinedTable,,3)<TODAY()),1),

result, VSTACK(HSTACK("Name", "Module", "Expiring on"), filteredData),
result)

Empiece por especificar el rango de sus datos, por ejemplo, A1:D5en este caso. Potencialmente, esto podría automatizarse aún más incluyendo una COUNTA()declaración o algo similar, pero dado que el rango solo debe especificarse una vez, en la mayoría de los casos esto es suficiente. Todo lo demás se calcula automáticamente.

A continuación, comenzamos calculando el número de módulos y filas, como el número de columnas/filas en "datos" - 1, dado que el "Nombre" y el "Encabezado" no son ningún módulo o fila relevante. En el siguiente paso, extraemos los nombres respectivos de los módulos (por ejemplo, Capacitación1-Entrenamiento3) como tmpM. Los módulos deben repetirse x veces según la cantidad de módulos y filas relevantes. Para conseguirlo combinamos INDEX()con MOD()y SEQUENCE(). Se repite el mismo procedimiento para crear la secuencia de nombres respectiva que repite el nombre tantas veces como sea necesario según los módulos. Por último, necesitamos apilar verticalmente todas las fechas en una columna en lugar de tenerlas como una matriz. Para ello utilizamos principalmente la TOCOL()función. Además, también seleccionamos sólo las fechas, es decir, no utilizamos nombres ni encabezados adicionalmente CHOOSEROWSy CHOOSECOLS. Una vez hecho esto, apilamos horizontalmente los "nombres", "módulos" y "fechas" usando HSTACK(). La tabla resultante tiene el siguiente aspecto:

A B C
John Entrenamiento1 08/01/2023
Marca Entrenamiento1 08/01/2023
esteban Entrenamiento1 01/06/2023
Factura Entrenamiento1 01/06/2023
John Formación2 08/01/2023
Marca Formación2 01/06/2023
esteban Formación2 01/06/2023
Factura Formación2 08/01/2023
John Formación3 08/01/2023
Marca Formación3 10/01/2023
esteban Formación3 01/06/2023
Factura Formación3 10/01/2023

El siguiente paso es una FILTER()declaración simple que filtre los datos que tienen una fecha < TODAY(). Para ello, queremos filtrar la "tabla combinada" según la tercera columna, es decir, la fecha. Para usar esta información en la declaración de filtro, usamos la INDEX()función.

Si desea filtrar datos que se encuentran dentro de los próximos 45 días, debe ajustar la FILTER()declaración de la siguiente manera, de modo que filtre solo aquellos casos que tienen una fecha > TODAY()y una fecha <= TODAY()+45:

filteredData,SORT(FILTER(combinedTable,
(INDEX(combinedTable,,3)>TODAY())*(INDEX(combinedTable,,3)<=TODAY()+45)),1),

Una vez filtrados los datos, recopilamos los resultados, es decir, incluimos los encabezados relevantes y utilizamos una VSTACK()declaración para combinar los encabezados con los datos filtrados. El resultado es una matriz única que tiene toda la información relevante y solo necesita una fórmula en una sola celda. El resultado final tiene el siguiente aspecto:

Venció:

Nombre Módulo Expira el
Factura Entrenamiento1 01/06/2023
Marca Formación2 01/06/2023
esteban Entrenamiento1 01/06/2023
esteban Formación2 01/06/2023
esteban Formación3 01/06/2023

Con vencimiento dentro de los 45 días:

Nombre Módulo Expira el
Factura Formación2 08/01/2023
John Entrenamiento1 08/01/2023
John Formación2 08/01/2023
John Formación3 08/01/2023
Marca Entrenamiento1 08/01/2023

Si desea mostrar los pasos intermedios, simplemente puede reemplazar el último "resultado" de la fórmula con cualquier otro nombre definido, por ejemplo, "tabla combinada", "fechas", etc.

información relacionada