Como posso comparar datas seriais do Excel SEM converter para datas do tipo mm/dd/aa?

Question

Eu tenho uma tabela que contém vários valores que representam datas seriais do Excel. Após uma série de tentativas malsucedidas de comparar campos, minha abordagem atual é fazer comparações entre datas seriais em vez de datas de calendário. Estou tentando resumir os dados – por DIA – com fórmulas.

CONSIDERAR:

41021       some data
41021.625   some data
41021.63542 some data
41022       some data
41022.26042 some data
41022.91667 some data
41023       some data
41023.375   some data

RESULTADO DESEJADO:

41021       sum of 41021, 41021.625 and 41021.63542 data
41022       sum of 41022, 41022.26042 and 41022.91667 data
41023       sum of 41023 and 41023.375 data

Em essência, para todas as instâncias de SerialDate.SerialTime, valores de dados SUM associados a SerialDate.* independentemente do *.SerialTime para essa data.

Embora eu possa ver como fazer isso criando colunas de datas adicionais formatadas, =TEXT(<DateField>,"mm/dd/yyyy")estou procurando uma solução que me permita lidar com essa 'conversão' na fórmula, por exemploSUMIF((TEXT(<dateRange>,"yy/mm/dd"),=(TEXT(<dateField,"yy/mm/dd")),<dataRange>

Faz sentido? Alguém tem alguma ideia?

Obrigado

Answer 1

Supondo que seus tempos de série estejam na coluna A, os dados associados na coluna B, a seguinte fórmula

=IF(INT($A1)=$A1,SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

exibirá a soma atrás do primeiro valor inteiro de tempo na coluna C quando inserido em C1 e estendido para a coluna. Se isso é tudo que você precisa, está feito.

Se, no entanto, você precisar de todas as suas somas uma abaixo da outra, sem linhas em branco entre elas, você terá que usar umfórmula de matrize insira uma coluna extra para os rótulos de referência. Na coluna C, selecione as células nas linhas que contêm dados (ou seja, se seus dados preencherem as linhas 1 a 100, selecione C1 a C100) e insira a seguinte fórmula na barra de fórmulas (não diretamente na célula!):

=IFERROR(SMALL(IF(INT($A1:$A100)=$A1:$A100,$A1:$A100,""),ROW()),"")

Observe que para funcionar, você precisa salvá-lo como uma fórmula de matrizconfirmando a entrada da fórmula com Ctrl+Shift+Enter(não Enter- se você fez certo, a fórmula será exibida entre colchetes). Você também terá que ajustar o escopo conforme necessário, pois a fórmula retorna 0 para células vazias. Por fim, observe que as fórmulas de matriz ficam bastante lentas em grandes conjuntos de dados.

Uma vez feito isso, você pode adicionar

=IF($C1<>"",SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

para a coluna D e aí está você:

Answer 2

Se estiver tentando resumir os dados por dia (ou obter um subtotal diário), você pode tentar esta fórmula de matriz. Insira isso na barra de fórmulas e pressione Ctrl+ Shift+ Enter.:

=SUM((INT(datetimes)=D3)*somedata)

Onde

• datetimescontém suas datas de série, no formato Generaloumm/dd/yyyy h:mm
• somedatacontém alguns números para adicionar (B2:B25 no meu exemplo abaixo)

O Excel vê as datas como números inteiros (valores seriais) e os tempos como decimais (uma fração de 24 horas). Por exemplo, 06/01/2012 3:00:00 PMé igual a 41061.625(que é o que você verá se alterar o formato da célula para General). O número inteiro representa a data, enquanto 0.625representa 3:00 pmou 15:00ou 15/24. Para extrair o segmento DATE, você pode usar INT(A1).

Answer 3

A maneira mais simples de fazer isso é usar os recursos integrados de Tabela e Tabela Dinâmica do Excel (2007/2010).

1. Crie uma tabela Excel a partir de seus dados.
2. Crie uma coluna auxiliar para a Data, usando a função "INT" do Excel, que rolará todos os valores até o dia apropriado (a parte inteira do seu campo de data/hora). Você também pode criar facilmente uma coluna auxiliar para o valor Tempo do seu campo.
3. Crie uma tabela dinâmica usando sua tabela de dados como fonte.
4. Defina os rótulos das linhas como datas auxiliares e os valores dinâmicos como pontos de dados (e você pode adicionar o campo Hora como rótulos das colunas).

Então, sempre que atualizar sua tabela, você poderá atualizar sua tabela dinâmica para os subtotais atuais. Como bônus, você também pode escolher qualquer uma das outras funções agregadas (min, max, avg) e optar por formatar os rótulos da tabela dinâmica em um formato de data tradicional sem afetar os valores da tabela de dados.

Se estiver usando o Excel 2003, você pode fazer o mesmo usando a função Lista (em vez de Tabelas).

Exemplo Excel 2010

Exemplo Excel 2003