☰

Excel: suma datos consecutivos cada intervalo de 30 minutos

tag-icon • tag-icon microsoft-excel worksheet-function
Excel: suma datos consecutivos cada intervalo de 30 minutos

Tengo datos recopilados de un pluviómetro en el siguiente formato:

Marca de tiempo de datos de índice

1. 0    2020-01-05T00:00:00
2. 0    2020-01-05T01:00:00
3. 1    2020-01-05T01:20:15
4. 0    2020-01-05T02:00:00
5. 1    2020-01-05T02:09:00
6. 1    2020-01-05T02:09:45
7. 1    2020-01-05T02:20:00
8. 1    2020-01-05T02:20:01
9. 0    2020-01-05T03:00:00

Me gustaría sumar los datos consecutivos cada 30 minutos. Algo como lo siguiente:

Marca de tiempo de datos de índice

1. 0    2020-01-05T00:00:00
2. 0    2020-01-05T00:30:00
3. 0    2020-01-05T01:00:00
4. 1    2020-01-05T01:30:00
5. 0    2020-01-05T02:00:00
6. 4    2020-01-05T02:30:00
7. 0    2020-01-05T03:00:00

Ya obtuve solo el tiempo de 24 horas de la marca de tiempo y traté de determinar si restar 2 marcas de tiempo es menos de 30 minutos según la siguiente fórmula:

=IF(((time)-(time-1))<=0.020833,"ok","not within 30min interval")

La fórmula de Excel que tenía en mente era obtener algo similar a una SUMIFSfunción que sumaría todos los valores consecutivos dentro de un intervalo de 30 minutos.

Intenté implementar algún tipo de COUNTIFverificación de datos consecutivos dentro del intervalo de 30 minutos y traté de resolver una función SUMIFSy una SUMPRODUCT, pero estoy luchando por unir las cosas y con la lógica.

Gracias

Respuesta1

Puedes utilizar esta fórmula:

=DATEVALUE(SUBSTITUTE(C2,"T"," "))+CEILING.MATH(ROUND(TIMEVALUE(SUBSTITUTE(C2,"T"," ")),5),"0:30")

ingrese la descripción de la imagen aquí

Desglosado en pasos:

  1. Para que Excel reconozca el texto como fecha/hora, elimine la T:

    =SUSTITUIR(C2,"T"," ")

  2. Para obtener la fecha de esa fecha/hora:

    =FECHAVALOR(D2)

  3. Para obtener la hora a partir de esa fecha/hora:

    =RONDA(VALOR DE TIEMPO(D2),5)

(Explicaré por qué se necesita la REDONDA aquí un poco más abajo)

  1. Para calcular el siguiente incremento de 30 minutos:

    =TECHO.MATEMÁTICAS(RONDA(VALOR DE TIEMPO(SUSTITUTO(C2,"T"," ")),5),"0:30")

Luego, al concatenar la fecha con el incremento de 30 minutos, obtendrá la fórmula en la parte superior de la respuesta. Luego puede girar la tabla para sumar el valor por incremento.

Nota sobre la llamada a ROUND en el paso 3:

Cuando escribí la fórmula por primera vez, no usé ROUND en el paso 3, pero descubrí que el incremento que salía para las 2:00:00 era 2:30:00, lo cual no era correcto. Durante la inspección, descubrí que la función TIMEVALUE no devolvía el nivel correcto de precisión. Redondear a 5 decimales solucionó ese problema.

ingrese la descripción de la imagen aquí

EDITAR:

Como tienes Excel 365, supongo que también tienes acceso a la función SECUENCIA.

Para agregar sin una tabla dinámica, puede hacer lo siguiente (suponiendo que no tenga una lista preexistente de incrementos):

  1. Calcule el número de incrementos de 30 minutos entre el grupo de marca de tiempo MIN y el grupo de marca de tiempo MAX
  2. Cree una secuencia que contenga esa cantidad de incrementos (para que también se representen los incrementos que no están en los datos de origen)
  3. Utilice SUMIF para consultar los datos de origen y sumar los datos por TimestampGroup
  4. Opcionalmente, cree un GroupIndex para la tabla agregada.

ingrese la descripción de la imagen aquí

Las fórmulas en la tabla agregada son:

Índice de grupo:

=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1)

Esto simplemente le indica cuántos incrementos de 30 minutos hay entre el TimestampGroup más pequeño y el TimestampGroup más grande y devuelve una secuencia entera para esa cantidad de incrementos.

Grupo de marca de tiempo:

=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1,1,MIN($D$3:$D$11),1/48)

Nuevamente, usando la función de secuencia, devuelva una lista de TimestampGroups, pero en lugar de comenzar la secuencia en 1 e incrementar en 1, que son los parámetros tercero y cuarto predeterminados, inicie la secuencia en el TimestampGroup mínimo e incremente en 30 minutos, que es 1/48 de día.

Valor:

=SUMIF($D$3:$D$11,$F16,$B$3:$B$11)

Sume la columna Datos para las filas indicadas por TimestampGroup en la fila actual de la tabla agregada.

información relacionada