Preencher os tempos perdidos de um conjunto de séries temporais interrompidas de dados

Question

Eu baixei por minuto os dados do Google Analytics. No entanto, ele fornece apenas um item de linha para cada minuto que existe um valor maior que zero.

Eu gostaria de fazer um gráfico desses dados, mas antes disso eu quero vê-los em uma série temporal contínua, injetando zeros explícitos para os minutos em que a fonte não tem dados.

Eu converti a 'Hora do dia' (coluna A ) e 'Minuto' (coluna B ) em 'Tempo' (coluna G ) usando

=TIME(RIGHT(A3,2),B3,0)

Eu tentei usar um índice / correspondência

=INDEX($C$3:$C$954,MATCH(H3,$G$3:$G$954,0))

na coluna I referenciando a série temporal contínua na coluna H , para retornar os dados na coluna C , mas retorna #N/A .

Veja uma captura de tela mostrando o resultado desejado, inserido manualmente, na coluna I :

Como H6 é 12:03:00 AM , I6 deve conter 2 , que é o valor de C3 , pois G3 é 12:03:00 AM .

Da mesma forma, H11 = G4 = 12:08:00 AM → I11 = C4 = 1 .

As linhas cujo valor H não está presente na coluna G devem ter um 0 na coluna I .

    

Answer 1

O problema acaba sendo devido a discrepâncias de ponto flutuante quando você usa o preenchimento em série Linear do Excel para preencher automaticamente os tempos na coluna H , em comparação com os tempos gerados com a função TIME() .

Isto pode ser confirmado inserindo =H6-G3 em qualquer célula, ou selecionando H6 , e então pressionando F2 seguido por Enter . Note que enquanto o método F2 pode ser usado para corrigir os valores de tempo um de cada vez, não é conveniente fazê-lo para qualquer número significativo de células.

A solução é gerar os valores Continuous Time com uma fórmula.

InsiraaseguintefórmulaemH3ectrl-enter/copy-paste/fill-down/preenchimentoautomáticoatéofinaldacolunaHdatabela,conformenecessário:

=IF(ISTEXT(H2),TIME(RIGHT(A3,2),0,0),TIME(HOUR(H2),MINUTE(H2)+1,0))

Observe que os valores de hora podem ser deixados como estão ou convertidos em valores literais copiando e colando como valores.

Como você pode ver, sua fórmula de índice / correspondência na coluna I agora selecionará os Usuários da coluna C quando houver um tempo correspondente na coluna G .

EDITAR:

Embora a fórmula acima seja "robusta" e permita que as linhas sejam inseridas / excluídas acima do cabeçalho sem quebrá-lo, como Scott apontou (especialmente se copiar / colar como valores), há uma versão mais simples e menos robusta:

=TIME(RIGHT($A$3,2),ROW()-3,0)

Além disso, simplificar isso leva à seguinte fórmula mais simples possível:

=TIME(0,ROW()-3,0)

A única coisa que resta a fazer é modificar a fórmula para gerar zeros quando o tempo está faltando:

=IFERROR(INDEX($C$3:$C$954,MATCH(H3,$G$3:$G$954,0)),0)