差和范围和差和
我在计算加班费。如果小时大于40，减去40。很简单。但是，使用单个复制/粘贴公式，我想对其进行细分，以显示一周中每一天有多少小时被计算为加班。下面是一个直观的例子：
Date | Hours | OT |
-----|-------|----|
Day1 | 10 | 00 |
Day2 | 08 | 00 |
Day3 | 18 | 00 |
Day4 | 16 | 12 | OT = if SUM(B2:B5>40) then SUM(B2:B5,-40,SUM((C2:C4)*-1))
Day5 | 04 | 04 |
Day6 | 08 | 08 |
Day7 | 09 | 09 |我在这里的主要问题是，我并不总是想从B2&C2开始，我也不想总是在B5&C4结束，因为这一周开始了，我需要知道我当前的单元格(B5)和下一篇专栏文章的一周内的单元格--每天1行(C4)。我需要B2到increment到B9到B16等，我需要C4用 B5来增加。例如，B7&C6，B6&C5等，我认为OFFSET可以工作，但我不知道如何使它工作，这样我就可以使用一个公式，而不是52个导数。
到目前为止，我能想到的最好的公式是：=SUM(OFFSET($B$2,(ROW()-2)*7,0,7,1),-40)，它只计算一周的总加班时间，以及静力学B2。
回答道：感谢@TomSharpe。这两个都是完美的。选择你最喜欢的：
偏移量
=IF(MOD(ROW()-ROW($2:$2),7)=0,0,IF(SUM(OFFSET($C$2,INT((ROW()-ROW($2:$2))/7)*7,0,MOD(ROW()-ROW($2:$2),7)))=0,--TEXT(SUM(OFFSET($B$2,INT((ROW()-ROW($2:$2))/7)*7,0,MOD(ROW()-ROW($2:$2),7)+1))-40,"0;\0"),B2))指数
=IF(MOD(ROW()-ROW($2:$2),7)=0,0,IF(SUM(INDEX(C:C,INT((ROW()-ROW($2:$2))/7)*7+2):C1)=0,--TEXT(SUM(INDEX(B:B,INT((ROW()-ROW($2:$2))/7)*7+2):B2)-40,"0;\0"),B2))回答 1
Stack Overflow用户
发布于 2017-12-15 10:47:39
这是一个抵消公式，它再次开始每周加班费计算。偏移量到当前周的开始，高度是当前周中的天数。我最终意识到，当前一天的加班时间大于零时，今天的加班仅仅是当前一天的工作时数(因为它们都是加班)。
=IF(MOD(ROW()-ROW($2:$2),7)=0,0,IF(C1=0,--TEXT(SUM(OFFSET($B$2,INT((ROW()-ROW($2:$2))/7)*7,0,MOD(ROW()-ROW($2:$2),7)+1))-40,"0;\0"),B2))使用文本函数是@Barry建议的一个技巧，这样可以避免在结果为负值时重复整个公式。
最好是用索引代替偏移量，如果时间允许的话，我会看看是否可以这样做。
以下是索引版本--稍微简单一些，因为范围的末尾只是B列中的当前单元格。
=IF(MOD(ROW()-ROW($2:$2),7)=0,0,IF(C1=0,--TEXT(SUM(INDEX(B:B,INT((ROW()-ROW($2:$2))/7)*7+2):B2)-40,"0;\0"),B2))编辑
正如OP指出的那样，我已经忘记了没有工作时间的日子。所以我的补偿公式应该是
=IF(MOD(ROW()-ROW($2:$2),7)=0,0,IF(SUM(OFFSET($C$2,INT((ROW()-ROW($2:$2))/7)*7,0,MOD(ROW()-ROW($2:$2),7)))=0,--TEXT(SUM(OFFSET($B$2,INT((ROW()-ROW($2:$2))/7)*7,0,MOD(ROW()-ROW($2:$2),7)+1))-40,"0;\0"),B2))我的指数公式应该是
=IF(MOD(ROW()-ROW($2:$2),7)=0,0,IF(SUM(INDEX(C:C,INT((ROW()-ROW($2:$2))/7)*7+2):C1)=0,--TEXT(SUM(INDEX(B:B,INT((ROW()-ROW($2:$2))/7)*7+2):B2)-40,"0;\0"),B2))一周的第一天加班时间总是设定为零(即使你进入了50小时)，因为在实践中，你不能每天工作超过24小时:如果你想改变门柱，让它为一整队人工作，我想这将是另一个问题。
https://stackoverflow.com/questions/47827921
复制相似问题
腾讯云开发者
