Como faço para 'SUM' por várias colunas no Excel

Question

Eu tenho um arquivo delimitado por vírgula que inclui duas colunas de data / hora (que importa como formato mm/dd/yyyy hh:mm personalizado do Excel) e status de 1 ou 0. O status representa um equipamento ligado ou desligado. Eu estou tentando gerar um gráfico que mostrará, horas acima vs. abaixo do dia.

CONSIDERAR:

1/1/2012 00:00, 1
1/1/2012 03:00, 0
1/1/2012 14:00, 1
1/3/2012 00:00, 0

Isso me diz que o equipamento estava funcionando por três horas, por onze horas, e depois por trinta e quatro horas (em dois dias de calendário). No entanto, eu gostaria de gerar um gráfico que mostra quantas horas por dia estávamos em alta ou baixa.

CONSIDERAR:

1/1 XXXXXXXXXXXXX-----------   (up 13, down 11)
1/2 XXXXXXXXXXXXXXXXXXXXXXXX   (up 24)

Para mim, parece que preciso gerar um conjunto de dados que some HORAS por STATUS por CALENDÁRIO DIA ... mas não consigo encontrar um sabor de tabela dinâmica ou combinação SUM(IF(SUMIF(...))) aninhada para que funcione. / p>

O mais preocupante é contabilizar as alterações de data ... no meu exemplo acima, já que o tempo de atividade a partir das 14:00 de 1/1/2012 cruza a meia-noite, preciso saber que 10 horas de atividade foram totalizadas com 1/1 / 2012 e 24 horas de disponibilidade são totalizadas com 1/2/2012.

Eu posso fazer algo com uma lista de agendas para gerar o resumo da data, mas preciso comparar um 01/01/2012 a 01/01/2012 03:00 como igual. Tem que haver um caminho ao longo das linhas de if(INTEGER-PORTIONS-OF-SERIAL-DATES-ARE-EQUAL,TOTAL-HOURS-IF-VALUE-IS_1,0) mas nada funcionou até agora.

Alguma sugestão? Eu tenho lutado isso a maior parte do dia e preciso de uma nova perspectiva.

Obrigado

    

Answer 1

OMGBBQ isso é um desafio! Eu gostaria de poder explicar isso melhor, mas por favor, tenha paciência comigo.

Minha solução requer alguns imóveis adicionais - 3 colunas para ser exata - e é baseada em algumas suposições:

1. Os dados do registro de data e hora teriam linhas alternadas de 1s e 0s, emulando uma chave liga / desliga como você descreveu (consulte a Imagem 1 abaixo).
2. Os carimbos de hora são organizados do mais antigo para o mais recente.

Parasimplificar,estouusandohorasinteiras.Emboravocêpossaadicionarcarimbosdedataehoranoníveldeumminuto,sepreferir.

AcolunaErastreiaonúmerodehorasrestantesdodiaemcadahorário.Euuseiissopara"SPLIT" os tempos decorridos que atravessam a meia-noite.

Colunas ON / OFF

Esta é a fórmula na coluna ON:

=SUM(IF(AND($B3,A4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=1,INT($A3)<>INT($A4)),$E3,IF(AND($B2=1,INT($A3)<>INT($A2)),24-$E3,0)),0))

Esta fórmula adiciona dois valores:

1. O número de horas que o switch está ON se a entrada não for o último horário do dia; caso contrário, 0.

2. O número de horas restantes até meia-noite se este for o último carimbo de hora do dia OU o número de horas decorridas desde meia-noite se este for o primeiro carimbo de hora do dia.

A fórmula na coluna OFF é semelhante, exceto que verifica se Status = 0.

=SUM(IF(AND(NOT($B3),B4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=0,INT($A3)<>INT($A4)),$E3,IF(AND($B2=0,INT($A3)<>INT($A2)),24-$E3,0)),$B3*($A3-INT($A3))*24))

Para extrair o DATE da coluna de registro de data e hora, usei a função INT() . O Excel usa números inteiros para representar datas e decimais para representar os horários (uma fração de um dia ou de 24 horas). Por exemplo, 06/01/2012 18:00 é igual a 41061.75 (18 horas depois da meia-noite é 18/24, o que é 0,75). Acredito que isso responda ao seu último parágrafo.

Consolidando as horas

Aqui está a fórmula na coluna Horas em (veja a imagem abaixo). É uma fórmula de array, então você precisa entrar usando Ctrl + Deslocar + Enter e depois copiá-lo:

=SUM((INT(stamps)=$G3)*hours_on)

Na coluna Horas OFF :

=SUM((INT(stamps)=$G3)*hours_off)

em que stamps é um intervalo nomeado referente ao intervalo Carimbo de data / hora (coluna A no meu exemplo)% hours_on é um nome Intervalo referente ao intervalo ATIVADO (Coluna C )
hours off é um Intervalo Nomeado referente ao intervalo DESATIVADO (Coluna D )

Gráfico

Observe como, para cada data, o número de horas ON e OFF totaliza 24.

Sevocêquiserestudarasfórmulaseapastadetrabalho,aquiestáumacópia: link

    

Answer 2

Supondo que você tenha carimbos de hora em A3: A24 e status em B3: B24 de acordo com a sugestão de Kaze, você pode obter as [decimal] horas ON para uma data em D3 com essa fórmula

=(LOOKUP(D3+1,A$3:B$24)-SUMPRODUCT((INT(A$3:A$24)=D3)+0,MOD(A$3:A$24,1),B$3:B$24*2-1))*24

não são necessárias colunas "auxiliares" ....

    

Answer 3

Não é bonito, mas aqui está uma solução:

Após as duas colunas, adicione uma terceira coluna definida como dia ([datecolumn])

       A       B    C  D
   ---------------------
1 | 1/1/2012 00:00, 1, 1
2 | 1/1/2012 03:00, 0, 1
3 | 1/1/2012 14:00, 1, 1
4 | 1/3/2012 00:00, 0, 3
 ...
99| 1/31/2012 11:23, 1,31

Depois, você pode criar colunas de soma definidas como

sumif(D1:D99, 1, C1:C99)

Que, no caso acima, mostrará a soma dos 1s somente quando a coluna D (definida anteriormente como a porção "Dia" do datetime, for 1. Repita essa fórmula para cada dia do mês (a parte que eu não sou tão louco por), e você deve ter seus valores.

Soma de horas no primeiro dia do mês

sumif(D1:D99, 1, C1:C99)

Soma das horas no segundo dia do mês

sumif(D1:D99, 2, C1:C99)

Soma das horas no dia 3 do mês

sumif(D1:D99, 3, C1:C99)

e assim por diante ...