☰

En Excel, ¿cómo contar una enorme cantidad de marcas de tiempo y organizar los conteos en períodos de 5 minutos ordenados por día de la semana?

tag-icon • tag-icon microsoft-excel worksheet-function
En Excel, ¿cómo contar una enorme cantidad de marcas de tiempo y organizar los conteos en períodos de 5 minutos ordenados por día de la semana?

Tengo poco más de 300.000 filas en una hoja de cálculo y cada una de estas filas contiene una marca de tiempo (HH:MM:SS AM) y una fecha (DD/MM/AAAA).

Me gustaría obtener recuentos de estas marcas de tiempo y agruparlas en períodos de 5 minutos, por día de la semana (es decir, me gustaría saber el número de marcas de tiempo entre las 12:00 a. m. y las 12:05 a. m. del domingo, 12:05 y 12:10 a. m. el domingo, etc.). Siento que tengo que usarlo COUNTIFde alguna manera, pero no sé exactamente cómo implementarlo. Estoy tratando de organizar los resultados en otra hoja, que se parece a esta.

ingrese la descripción de la imagen aquí

El recuento de cada período de 5 minutos iría en la columna del medio, debajo del día de la semana (así que ahí es donde ingresaría la fórmula).

Gracias de antemano por cualquier ayuda, ¡realmente lo agradezco!

EDITAR: Probablemente debería haber incluido que mis datos se ven así:

ingrese la descripción de la imagen aquí

Respuesta1

Puede agregar un par de campos a sus datos y luego generar una tabla dinámica para obtener los recuentos.

Primero, agregue un DOWcampo Día de la semana ( ). Para obtenerlo, DOWutilice la siguiente fórmula, donde la columna Bcontiene la fecha.

=CHOOSE(WEEKDAY(B2,1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

A continuación, agregue un campo para representar el período de 5 minutos en el que se encuentra la marca de tiempo Time Range. Utilice la siguiente fórmula para calcular esto, donde la marca de tiempo está en la columna A.

=TIME(HOUR(A2),ROUNDDOWN(MINUTE(A2)/5,0)*5,0)

Una vez que haya agregado estas columnas a sus datos, cree una tabla dinámica que utilice todos sus datos como fuente de datos.

Configure su tabla dinámica con DOWetiquetas de columna y Time Rangeetiquetas de fila. Establezca los valores en Count of Time Range.

ingrese la descripción de la imagen aquí

Nota: Esta muestra parece un poco rara porque utilicé datos escasos (20 registros en una semana) para generar la tabla dinámica. Debería parecerse más a lo que tiene en mente con su gran conjunto de datos.

Respuesta2

De manera similar a lo que recomendó @Excellll, puede agregar una columna adicional que especifique el día de la semana, es decir, en la hoja 1, donde están presentes sus datos sin procesar y las columnas A y B contienen la fecha y hora respectivamente, puede usar esta función en la columna C.

=SI(O(ESTÁ EN BLANCO(A1),ESTÁ EN BLANCO(B1)),"",DÍA DE LA SEMANA(A1))

El enfoque que estoy usando se basa exclusivamente en funciones. Al igual que la imagen que compartiste, personalicé fórmulas para la Hoja 2 para generar una estructura similar. La siguiente condición verifica que existan marcas de tiempo en un rango particular como se declara en la primera columna de la tabla y, además, que sea un día determinado de la semana. Luego cuenta todos esos valores.

=CONTAR.SI(Hoja1!$B:$B, ">"&$A3,Hoja1!$B:$B,"<="&$A4,Hoja1!$C:$C,"="&B$2)

La imagen muestra cómo se ingresa la fórmula anterior en la celda B1 y tiene condiciones para verificar que la hora esté en el rango de 12:00 a. m. a 0:10 a. m. y el día esté configurado en domingo o 1.

ingrese la descripción de la imagen aquí

Como habrás notado, la diferencia horaria aquí es de 10 minutos, la he hecho personalizable, para que puedas configurarla en 5 minutos o 2 horas según tus necesidades.

Dado que aquí usamos funciones, no importa si sus datos tienen 4 filas vacías o 10 filas vacías en el medio. Las funciones garantizan que solo se recopilen datos que no estén en blanco. No tenía suficientes datos, por lo que el ejemplo parece bastante vacío. Encuentre los xls de muestra adjuntos.aquí.

Respuesta3

Nota: vamos a solucionar el problema de este código esta noche, todavía tengo algunos errores al bajar en la lista de valores. Actualmente no funciona, simplemente no quiero reformatear mis líneas de código. Siéntete libre de jugar. Agregaré comentarios e instrucciones al mismo tiempo.

Alguien mencionó que esto se podría hacer con VBA. Me gusta VBA, así que le di un golpe.

Un par de suposiciones:

1 - La marca de tiempo y la fecha no están contenidas en la misma celda (es decir, 12:00:00 a. m. | 21/07/2014 NO 21/07/2014 00:00:00)

2: desea que todos los recuentos de marcas de tiempo se agrupen en una única lista de días (es decir, mostrar solo un conjunto de domingo a sábado y no crear un nuevo conjunto de columnas para cada día adicional; si comenzamos en viernes, no comenzaríamos la agrupación el viernes, y si terminamos un martes dos semanas después, no tendríamos 16 columnas de agrupaciones)

3 - No hay celdas nulas en sus datos desde la primera fila hasta la última.

4 - Tus datos de marcas de tiempo y fechas tienen encabezados

Debe presionar alt+f11, abrir el libro de trabajo donde tiene los datos y las hojas de trabajo de origen e ingresar este código. Entonces presione F5.

Public Sub PrintDateGroups()
Dim icontrol As Integer
Dim iweeknum As Integer
Dim ipasscount As Integer
Dim lngwalktimevalues As Long
Dim ipasstimecount As Integer
icontrol = 1

Do Until icontrol = -1:
If ThisWorkbook.Sheets("Data").Cells(icontrol,2).Value = "" Then
    icontrol = -1

Else

    icontrol = icontrol + 1
    iweeknum = Weekday(ThisWorkbook.Sheets("Data").Cells(icontrol, 1).Value, vbSunday)

    For lngwalktimevalues = 0 To 99999999 Step 694444.4375
        If (TimeValue(Format(ThisWorkbook.Sheets("Data").Cells(icontrol, 2).Value, "hh:mm:ss")) * 100000000) <= lngwalktimevalues Then
            If iweeknum = 1 Then
                If ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value <> "" Then
                    ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value = ThisWorkbook.Sheets("Destination").Cells(lngwalktimevalues / 694444.4375, 2).Value + 1
                    Exit For
                Else
                    ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value = 1
                    Exit For
                End If
            Else
                If ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = "" Then
                    ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = 1
                    Exit For
                Else
                    ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = ThisWorkbook.Sheets("Destination").Cells(lngwalktimevalues / 694444.4375, ((3 * iweeknum) + (iweeknum - 2))).Value + 1
                    Exit For
                End If
            End If
        End If
    Next lngwalktimevalues
End If
Loop
End Sub

Respuesta4

Debería poder redondear cada registro a los cinco minutos más cercanos. Luego use la función subtotal de Excel para obtener los recuentos.

Haría el redondeo creando una nueva columna de la fecha (=Día(A1)) y una segunda columna de la marca de tiempo.

información relacionada