Добавление предыдущих 7 дней, если не 0

Question

Я пытаюсь разработать трекер журналов часов для водителей.

Используя Excel 7 - мне нужно вычислить 7 предыдущих дней из текущего (скользящая шкала), если в дне ноль часов, то часы сбрасываются в ноль и начинают добавление снова на 7 дней.

Имеет ли это смысл?

Как мне написать формулу, чтобы сказать, добавьте предыдущие 7, если ноль начинается с 0 и считается 7 дней. Я хочу, чтобы это выглядело так:

      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

Как мне это сделать?

Answer 1

Я считаю, что вам понадобится вспомогательный столбец, в моем примере вспомогательный столбец находится в столбце D.

Поместите следующую формулу в ячейку D2 и перетащите вниз:

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

В ячейку C2 поместите эту формулу и перетащите вниз:

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

Для следующих образцов данных вы получите следующий вывод:

        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

По сути, вспомогательный столбец отслеживает, сколько дней подряд было часов, что соответствует формуле 70 часов /7 дней.

Есть решение без вспомогательного столбца, но оно, вероятно, будет очень длинным. Я думаю о функциях MOD с числом ROWS и формулой массива, чтобы получить последний увиденный 0 .

---

reEDIT: единственная формула, о которой я могу думать:

=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))

вводится как формула массива (с помощью Ctrl+Shift+Enter), начиная с ячейки C8 . Ячейка C2 будет иметь формулу =IF(B2=0,0,B1+B2) и перетаскиваться до максимум C7.

Answer 2

Если я не ошибаюсь, для каждой строки вам нужна сумма часов за предыдущие 7 дней, если не было 0 часов для любого из этих 7 дней.
Если есть 0 часов, то вам нужно количество часов до 0 часов. Правильно ли мое понимание?

Если да, то это кажется немного трудным для достижения, используя простые формулы (другие ответы могут прийти к умным формулам все же). Вы знакомы с макросами VBA. Если нет, то я другие члены СУ могу вам помочь с одним.

Вот псевдокод того, что вам нужно:

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)
)

Затем вы можете привязать макрос к событию изменения электронной таблицы, что означает, что столбец будет обновляться при каждом изменении содержимого листа.