☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
No Excel, como contar um grande número de carimbos de data/hora e organizar as contagens em períodos de 5 minutos, classificados por dia da semana?

Tenho pouco mais de 300.000 linhas em uma planilha e cada uma dessas linhas contém um carimbo de data/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 do número de carimbos de data e hora entre 12h e 12h05 no domingo, 12h05 e 12h10 de domingo, etc.). Sinto que preciso usar COUNTIFde alguma forma, mas não consigo descobrir exatamente como implementá-lo. Estou tentando organizar os resultados em outra planilha, parecida com esta.

insira a descrição da imagem aqui

A contagem para cada período de 5 minutos ficaria na coluna do meio, abaixo do dia da semana (é aí que eu inseriria a fórmula).

Agradecemos antecipadamente por qualquer ajuda, realmente agradeço!

EDIT: eu provavelmente deveria ter incluído que meus dados se parecessem com isto:

insira a descrição da imagem aqui

Responder1

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

DOWPrimeiro, adicione um campo Dia da Semana ( ). Para obter DOWuse a fórmula abaixo, onde a coluna Bconté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 5 minutos em que o carimbo de data/hora cai, Time Range. Use a fórmula abaixo para calcular isso, onde o carimbo de data/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 DOWrótulos de coluna e Time Rangerótulos de linha. Defina os valores como Count of Time Range.

insira a descrição da imagem aqui

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

Responder2

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

=SE(OU(ESTÁ EM BRANCO(A1),ESTÁ EM BRANCO(B1)),"",DIA DA SEMANA(A1))

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

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

A imagem mostra como a fórmula acima é inserida na célula B1 e tem condições para verificar se o horário está no intervalo de 12h00 a 12h10 e o dia está definido como Domingo ou 1.

insira a descrição da imagem aqui

Como você deve ter notado, a diferença de horário aqui é de 10 minutos, tornei-a personalizável, para que você possa configurá-la para 5 minutos ou 2 horas com base em suas necessidades.

Como estamos usando funções aqui, não importa se seus dados têm 4 linhas vazias ou 10 linhas vazias entre elas. As funções garantem que apenas dados que não estejam em branco sejam coletados. Eu não tinha dados suficientes, então o exemplo parece bem vazio. Por favor, encontre o exemplo xls em anexoaqui.

Responder3

Observação: vou solucionar esse código esta noite, ainda tendo alguns erros ao descer na lista de valores. Atualmente não é viável, só não quero reformatar minhas linhas de código. Sinta-se à vontade para brincar. Adicionarei comentários e instruções ao mesmo tempo.

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

Algumas suposições:

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

2 - Você deseja que todas as contagens de registros de data e hora sejam agrupadas em uma única listagem de dias (ou seja, mostre apenas um conjunto de domingo a sábado e não crie um novo conjunto de colunas para cada dia adicional - se começarmos em uma sexta-feira, nós não iniciaríamos o agrupamento na sexta-feira, e se terminarmos na 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 à última linha.

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

Você deve pressionar alt+f11, abrir a pasta de trabalho onde estão os dados e as planilhas de origem e inserir este código. Então aperte 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

Responder4

Você deve conseguir 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 (=Dia(A1)) e uma segunda coluna do carimbo de data/hora.

informação relacionada