Adicionando os 7 dias anteriores a menos que 0

Question

Estou tentando desenvolver um rastreador de log de horas para os motoristas.

Usando o Excel 7 - Preciso calcular os 7 dias anteriores da atual (é uma escala móvel), se houver zero horas em um dia, as horas serão redefinidas para zero e começarão a ser adicionadas novamente por 7 dias.

Isso faz sentido?

Como faço para escrever uma fórmula para adicionar os 7 anteriores, a menos que zero comece em 0 e conte 7 dias. Eu quero que seja assim:

      hours worked  70hrs/7days 
Jan 1     10           10
Jan 2     12           22 
Jan 3      4           26
Jan 4      4           30
Jan 5      0            0
Jan 6      8            8
Jan 7     10           18
Jan 8     12           30

Como faço isso?

    

Answer 1

Acredito que você precisará de uma coluna auxiliar, no meu exemplo, a coluna auxiliar está na coluna D.

Coloque a seguinte fórmula na célula D2 e arraste para baixo:

=IF(B2=0,0,IF(D1=7,1,IFERROR(D1+1,1)))

Na célula C2, coloque esta fórmula e arraste para baixo:

=IF(OR(B2=0,D2=7),B2,C1+B2)

Para os exemplos de dados a seguir, você obtém a seguinte saída:

        hours worked  70hrs/7days  Helper
Jan 01       10           10          1
Jan 02       12           22          2
Jan 03        4           26          3
Jan 04        4           30          4
Jan 05        0            0          0
Jan 06        8            8          1
Jan 07       10           18          2
Jan 08       12           30          3
Jan 09       10           40          4
Jan 10        3           43          5
Jan 11       24           67          6
Jan 12        2            2          7
Jan 13        7            9          1
Jan 14        6           15          2
Jan 15        3           18          3
Jan 16       18           36          4
Jan 17       10           46          5
Jan 18       20           66          6
Jan 19       11           11          7

Basicamente, a coluna de ajuda registra quantos dias seguidos houve horas, o que alimenta a fórmula de 70 horas / 7 dias.

Há uma solução sem uma coluna auxiliar, mas é provável que seja muito longa. Estou pensando nas funções MOD com o número de ROWS e na fórmula de matriz para obter a última 0 .

REEDIT: A fórmula única que consigo pensar é:

=IF(B8=0,0,IF(COUNTIF(C1:C7,0)=0,IF(MOD(ROWS(INDIRECT("C"&MATCH(9^99,IF(C$1:C7=0,1))+1&":"&CELL("address",C7))),7)=0,B8,C7+B8),C7+B8))

inserido como fórmula de matriz (com Ctrl + Deslocamento + Enter ) a partir da célula C8 . A célula C2 terá a fórmula =IF(B2=0,0,B1+B2) e será arrastada até no máximo C7.

    

Answer 2

Se eu não estiver errado, para cada linha, você precisa de uma soma de horas dos últimos 7 dias, a menos que houvesse 0 horas para qualquer um desses 7 dias.
Se houver 0 horas, você precisará da soma das horas até 0 horas de entrada. O meu entendimento está certo?

Se sim, então isso parece um pouco difícil de alcançar usando fórmulas simples (outras respostas podem aparecer em fórmulas inteligentes). Você está familiarizado com macros VBA? Se não, eu sou outro membro da SU pode ajudá-lo com um.

Aqui está o pseudo-código do que você precisa:

For each cell in column C (
if zero is found in previous 7 cells then
(find index of zero cell
current cell value = sum up to zero cell index)
else 
(current cell value =  sum of previous 7 cells)
)

Em seguida, você pode vincular a macro a um evento de alteração da planilha, o que significa que a coluna será atualizada toda vez que você alterar o conteúdo da planilha.