☰

Como faço para `SUM` por múltiplas colunas no Excel

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Como faço para `SUM` por múltiplas colunas no Excel

Eu tenho um arquivo delimitado por vírgula que inclui duas colunas de data/hora (que importa como mm/dd/yyyy hh:mmformato personalizado do Excel) e status de 1 ou 0. O status representa um equipamento ligado ou desligado. Estou tentando gerar um gráfico que mostre horas de aumento versus diminuição por 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 ficou ligado por três horas, desligado por onze horas e depois ligado por trinta e quatro horas (em dois dias corridos). Porém, gostaria de gerar um gráfico que mostre quantas horas POR DIA ficamos em alta ou em baixa.

CONSIDERAR:

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

Para mim, parece que preciso gerar um conjunto de dados somando HORAS por STATUS por DIA DO CALENDÁRIO... mas não consigo encontrar um tipo de tabela dinâmica ou SUM(IF(SUMIF(...)))combinação aninhada para fazê-lo funcionar.

O mais problemático é contabilizar as mudanças de data... no meu exemplo acima, como meu tempo de atividade começando às 14h de 01/01/2012 ultrapassa a meia-noite, preciso saber que 10 horas de tempo de atividade são totalizadas com 01/01/2012 e 24 as horas de atividade são totalizadas em 02/01/2012.

Talvez eu consiga fazer algo com uma lista de calendário para gerar o somatório de datas, mas preciso de uma maneira de comparar 01/01/2012como 01/01/2012 03:00iguais. Deve haver um caminho nesse sentido, if(INTEGER-PORTIONS-OF-SERIAL-DATES-ARE-EQUAL,TOTAL-HOURS-IF-VALUE-IS_1,0)mas nada funcionou até agora.

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

Obrigado

Responder1

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

Minha solução requer algum espaço adicional – 3 colunas para ser exato – e é baseada em algumas suposições:

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

insira a descrição da imagem aqui

Para simplificar, estou usando horas inteiras. Embora você possa adicionar carimbos de data/hora em nível de minuto, se preferir.

Coluna Erastreia o número de horas restantes do dia em cada carimbo de data/hora. Usei isso para "dividir" os tempos decorridos que ultrapassam a meia-noite.

Colunas LIGADA/DESLIGADA

Aqui está 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 fica LIGADO se a entrada não for o último carimbo de data/hora do dia; caso contrário, 0.

2.O número de horas restantesatémeia-noite se este for odurarcarimbo de hora do diaOUo número de horas decorridasdesdemeia-noite se este for oprimeirocarimbo 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 a DATA da coluna de carimbo de data/hora, usei a INT()função. O Excel usa números inteiros para representar datas e decimais para representar tempos (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, que é 0,75).Acredito que isso responde ao seu último parágrafo.

Consolidando o Horário

Aqui está a fórmula noHoras LIGADAScoluna (veja a imagem abaixo). É uma fórmula de matriz, então você precisa inseri-la usando Ctrl+ Shift+ Entere depois copiá-la:

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

NoHoras OFFcoluna:

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

onde
stampsestá um intervalo nomeado referente aoCarimbo de data e horaintervalo (colunaAno meu exemplo)
hours_oné um intervalo nomeado referente aoSOBREintervalo (colunaC)
hours off é um intervalo nomeado referente aoDESLIGADOintervalo (colunaD)

Gráfico

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

insira a descrição da imagem aqui

Se você quiser estudar as fórmulas e a apostila, aqui está uma cópia:http://db.tt/KZgH7SFV

Responder2

Supondo que você tenha carimbos de data e hora em A3:A24 e status em B3:B24 conforme sugestão de Kaze, você pode obter as horas [decimais] ONpara uma data em D3 com esta 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" ....

Responder3

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

Após suas duas colunas, adicione uma terceira coluna definida como day([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

Então você pode fazer colunas de soma definidas como

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

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

Soma das horas do primeiro dia do mês

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

Soma das horas do dia 2 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...

informação relacionada