2 votos

Búsqueda de valores máximos y temporales en X horas

Me gustaría utilizar la función Max de Excel para devolver el número máximo en un rango de números dentro de 2 horas desde mi hora de inicio y luego devolver la hora exacta en que se produjo el máximo.

He calculado el valor máximo entre la marca de tiempo y 2 horas después.

=MAXIFS($B:$B,$A:$A,"<="&D2+TIME(2,0,0),$A:$A,">="&D2)
=MAXIFS($B:$B,$A:$A,"<="&D3+TIME(2,0,0),$A:$A,">="&D3)
etc.

Sin embargo, me cuesta encontrar el valor de fecha y hora (columna F) en que se produjo este máximo (dentro de esa ventana de 2 horas). He probado con Index/match, pero no me devuelve la fecha y hora dentro de ese intervalo de 2 horas en la que se produjo el valor máximo (me devuelve las fechas y horas correspondientes a esos números fuera del intervalo de 2 horas). ¿Alguna idea?

Esta es la fórmula de Index Match que he probado y que se rompe al llegar a F6

=INDEX($A:$A,MATCH(E6,$B:$B,0))

Creo que necesita la misma lógica que la fórmula MAXIFS para que sólo coincida con los valores de la columna B si su correspondiente marca de tiempo (columna A) está dentro de la marca de tiempo de {columna D, columna D + 2 horas}.

enter image description here

Gracias.

3voto

FlexYourData Puntos 36

Puede hacerlo con la función FILTRO.

He recreado algunos de tus datos y he formateado ambos conjuntos de datos como Tablas. También he puesto la diferencia de horas permitida en la celda E1 y la he llamado dentro_horas.

enter image description here

Para simplificar las cosas, he añadido una columna llamada "LatestAllowableTime", que tiene esta fórmula:

=[@Timevalue]+TIME(within_hours,0,0)

Para obtener el valor máximo entre el inicio y el final permitido es exactamente como usted dijo - utilizando MAXIFS.

=MAXIFS(dataset1[Value],dataset1[Timevalue],"<=" & [@LatestAllowedTime],dataset1[Timevalue],">=" & [@Timevalue])

Puede filtrar el conjunto de datos1 utilizando algunos criterios mediante la función FILTRO. Para utilizar varias condiciones Y en el segundo parámetro de la función FILTRO, se encierra cada condición entre paréntesis y se pone un asterisco entre ellas. Así, el filtro para obtener los valores de tiempo del conjunto de datos1 basado en el valor de la columna MaxValueWithinXHours es:

=FILTER(dataset1[Timevalue],(dataset1[Value]=[@MaxValueWithinXHours])*(dataset1[Timevalue]<=[@LatestAllowedTime])*(dataset1[Timevalue]>=[@Timevalue]))

¡Observará que para el valor=91, aparece el mensaje #SPILL! Esto se debe a que hay más de 1 fila entre las horas de inicio y fin con ese valor. Esas filas están coloreadas en verde en mi captura de pantalla.

Por lo tanto, para elegir uno de esos valores de tiempo, podemos utilizar MIN o MAX. Parece por su captura de pantalla que desea MIN (es decir, la fila con 91 en el período de 2 horas con el tiempo más temprano):

=MIN(FILTER(dataset1[Timevalue],(dataset1[Value]=[@MaxValueWithinXHours])*(dataset1[Timevalue]<=[@LatestAllowedTime])*(dataset1[Timevalue]>=[@Timevalue])))

Creo que con eso basta.

EDITAR:

Es posible que obtenga mejores resultados con PowerQuery. Cree dos conexiones a cada una de las tablas: dataset1 (la lista de la que desea devolver valores) y dataset2 (la lista de la que desea devolver valores). En el caso del conjunto de datos 2, he conservado las dos primeras columnas de la captura de pantalla original. Para crear una consulta, seleccione una celda de la tabla y elija "Datos > Obtener y transformar > De tabla/rango".

Para el conjunto de datos Query2, añadí una columna Index para que fuera más fácil ver lo que estaba pasando. Para ello, en el Editor de Power Query, seleccioné "Añadir columna > Columna de índice". A continuación, moví la columna de índice a la izquierda. Esto me dio lo siguiente:

enter image description here

Después de asegurarme de que todas mis columnas de fecha/hora eran del tipo correcto, añadí una columna personalizada a dataset2, configurada de la siguiente manera:

enter image description here

La fórmula:

let x = [StartTime], y = [LatestAllowedTime] in Table.SelectRows(dataset1,each[TimeValue] >= x and [TimeValue] <= y)

El Table.SelectRows era bastante fácil de encontrar, pero la parte difícil que necesitaba un poco de investigación (en última instancia, me dieron la pista aquí ) era que tenía que almacenar los valores de la fila actual en dataset2 en variables antes de poder utilizarlos dentro de la función Table.SelectRows.

La idea de esta fórmula es utilizar los valores de la fila actual para filtrar y devolver un conjunto de filas del conjunto de datos1 cuyo Timevalue se encuentre entre el StartTime y el LatestAllowedTime. La verdad es que me costó un poco entenderlo, ¡pero fue una experiencia de aprendizaje!

Después de expandir las filas devueltas por la fórmula, obtuve esto:

enter image description here

¡Progreso! Finalmente conseguí las filas correctas de dataset1 en la consulta.

A continuación, para obtener el valor máximo de cada índice en dataset2.

Para ello, he duplicado dataset2 y lo he llamado dataset2_grouped. Luego utilicé Agrupar por en la pestaña Transformar, configurado así:

enter image description here

Eso me dio este resultado:

enter image description here

Esos son los valores máximos correctos. Ahora sólo necesito obtener el dataset1[TimeValue] correcto. Para ello, utilizo 'Inicio > Fusionar consultas > Fusionar consultas como nuevas', configurado así:

enter image description here

Los resultados después de eliminar la columna 'dataset2_grouped', que no necesitamos:

enter image description here

El último paso es obtener la fila de cada índice que tenga el valor mínimo en dataset1rows.TimeValue:

enter image description here

Tras hacer clic en OK en Agrupar por y luego en 'Inicio > Cerrar y cargar', obtenemos los resultados correctos:

enter image description here

2voto

Rajesh S Puntos 11

enter image description here

Cómo funciona:

  • Una matriz (CSE) formulain Celda W2:

    {=MAX(IF((T$2:T$11>=V2)*(T$2:T$11<=V2+TIME(2,0,0)),U$2:U$11,0))}
  • Fórmula de acabado con Ctrl+Mayús+Intro y relleno de plumón.

  • Fórmula de búsqueda en la celda X2:

    =IFERROR(INDEX(T$2:T$11,MATCH(W2,U$2:U$11,0)),"")

Ajuste las referencias de celda en la fórmula según sea necesario.

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by:

X