☰

添加前 7 天，除非 0

tag-icon • tag-icon microsoft-excel
添加前 7 天，除非 0

我正在尝试开发一个针对驾驶员的小时日志跟踪器。

使用 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

我该怎么做呢？

答案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）。单元格 C2 将包含公式并拖至最多 C7。EnterC8=IF(B2=0,0,B1+B2)

答案2

如果我没记错的话，对于每一行，您需要前 7 天的小时数总和，除非这 7 天中的任何一天的小时数为 0。
如果小时数为 0，那么您需要输入 0 小时以内的小时数总和。我的理解正确吗？

如果是，那么使用简单的公式似乎有点难以实现（尽管其他答案可能会提出巧妙的公式）。你熟悉 VBA 宏吗？如果不熟悉，我和其他 SU 成员可以帮你一个。

以下是您所需要的伪代码：

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

然后，您可以将宏与电子表格更改事件联系起来，这意味着每次更改工作表内容时，该列都会更新。

相关内容