No Excel, como contar um número enorme de registros de data e hora e organizar contagens em períodos de 5 minutos classificados por dia da semana?

Question

Tenho mais de 300.000 linhas em uma planilha, e cada uma dessas linhas contém um registro de data e hora (HH: MM: SS AM) e uma data (DD / MM / AAAA).

Gostaria de obter contagens para esses carimbos de data e hora e agrupá-los em períodos de 5 minutos, por dia da semana (ou seja, gostaria de ter o número de carimbos de data entre 12:00 e 12:05 no domingo, 12 : 05 e 12:10 no domingo, etc). Eu sinto que tenho que usar COUNTIF de alguma forma, mas não consigo exatamente descobrir como implementá-lo. Estou tentando organizar os resultados em outra folha, que se parece com isso.

Acontagemparacadaperíodode5minutosirianessacolunadomeio,sobodiadasemana(entãoéaíqueeuestariainserindoafórmula).

Agradecemosantecipadamenteporqualquerajuda,realmenteaprecioisso!

EDIT:Euprovavelmentedeveriaterincluídoquemeusdadosseparecemcomisso:

    

Answer 1

Você pode adicionar alguns campos aos seus dados e gerar uma tabela dinâmica para obter as contagens.

Primeiro, adicione um campo Dia da semana ( DOW ). Para obter DOW , use a fórmula abaixo, em que a coluna B contém a data.

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

Em seguida, adicione um campo para representar o período de cinco minutos em que o registro de data e hora está, Time Range . Use a fórmula abaixo para calcular isso, em que o registro de data e hora está na coluna A .

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

Depois de adicionar essas colunas aos seus dados, crie uma tabela dinâmica que use todos os seus dados como fonte de dados.

Configure sua tabela dinâmica com DOW como marcadores de coluna e Time Range como rótulos de linha. Defina os valores para Count of Time Range .

Observação: esse exemplo parece um pouco estranho porque usei dados esparsos (20 registros durante uma semana) para gerar a tabela dinâmica. Deve parecer mais com o que você tem em mente com seu grande conjunto de dados.

    

Answer 2

Semelhante ao que o @Excellll recomendou, você pode adicionar uma coluna adicional especificando o dia da semana, ou seja, na planilha 1, onde os dados brutos estão presentes e as colunas A e B retêm a data e a hora respectivamente, você pode usar essa função na coluna C

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",WEEKDAY(A1))

A abordagem que estou usando é puramente baseada em funções. Como a imagem que você compartilhou, eu criei fórmulas personalizadas para a Folha 2 para gerar uma estrutura semelhante. A condição a seguir verifica se os carimbos de data e hora existem em um determinado intervalo, conforme declarado na primeira coluna da tabela e, adicionalmente, se é um determinado dia da semana. Então, conta todos esses valores.

=COUNTIFS(Sheet1!$B:$B, ">"&$A3,Sheet1!$B:$B,"<="&$A4,Sheet1!$C:$C,"="&B$2)

A imagem mostra como a fórmula acima é inserida na célula B1 e tem condições de verificar se o tempo está no intervalo de 12:00 AM a 12:10 AM e o dia está definido como domingo ou 1.

Comovocêdeveternotadoqueadiferençadefusohorárioaquiéde10minutos,euopersonalizei,assimvocêpodeconfigurá-lopara5minutosou2horascombaseemsuanecessidade.

Comoestamosusandofunçõesaqui,nãoimportaseseusdadostêm4linhasvaziasou10linhasvaziasnomeio.Asfunçõesgarantemqueapenasdadosnãovaziossejamcoletados.Eunãotenhodadossuficientesparaqueoexemplopareçabemvazio.Porfavor,encontreoexemploxlsemanexo aqui .

    

Answer 3

Observação - para solucionar esse código esta noite, ainda haverá alguns erros na lista de valores. Não funciona atualmente, só não quero reformatar minhas linhas de código. Sinta-se à vontade para brincar. Vou adicionar comentários e instruções ao mesmo tempo.

Alguém mencionou que isso poderia ser feito com o VBA. Eu gosto do VBA, então eu dei uma pancada nele.

Algumas suposições:

1 - O carimbo de data / hora e a data não estão contidos na mesma célula (ou seja, 12:00:00 AM | 7/21/2014 NÃO 7/21/2014 00:00:00)

2 - Você deseja agrupar todas as contagens de carimbos de data e hora em uma única listagem de dias (ou seja, mostrar apenas um conjunto de domingo a sábado e não criar um novo conjunto de colunas para cada dia adicional. Sexta-feira, não iniciaríamos o agrupamento na sexta-feira, e se terminássemos em uma terça-feira duas semanas depois, não teríamos 16 colunas de agrupamentos)

3 - Não há células nulas em seus dados da primeira linha até a última.

4 - Seus dados de data e hora e data têm cabeçalhos

Você deve pressionar alt + f11 e abrir a pasta de trabalho em que você tem os dados e as planilhas de origem e inserir esse código. Em seguida, pressione 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

    

Answer 4

Você deve poder arredondar cada registro para os cinco minutos mais próximos. Em seguida, use a função subtotal do Excel para obter as contagens.

Eu faria o arredondamento criando uma nova coluna da data (= Day (A1)) e uma segunda coluna do timestamp.