我正在用 Excel 为员工制作每周计划表。这是分发给员工的计划表，所以我需要让它易于阅读。目前，我为每位员工从左到右列出了他们周一、周二等的开始和结束时间。这是每个人都会看到的部分，所以如果可以的话，我不想弄乱那部分。
在它的右边，我需要知道他们那一周的总工作时间。目前我的计算方式如下：
=((D5-C5) * 24) + ((F5-E5) * 24) + ((H5-G5) * 24) + ((J5-I5) * 24) + ((L5-K5) * 24) + ((N5-M5) * 24) + ((P5-O5) * 24)
每次计算都是用结束时间减去开始时间，这样我就能得到一个接近的数字，但由于 Excel 不了解劳动法，所以它当然不会从每天总计 5 小时或以上的时间中减去半小时。到目前为止，我已经解决了这个问题，方法是手动输入一个名为“带午餐的日子”的列，然后我可以从第一列中减去该列的值 * .5 即可得到更正的总数。但是，我想使用一个函数来自动执行此操作。
我在谷歌上搜索了一下，似乎我应该能够使用CountIf、SumIf或SumProduct来做到这一点，但我不确定具体怎么做。我曾想过为每位员工每天设置一堆单元格，用于检查工作时间，如果工作时间达到或超过 5 小时，则将其计为带午餐的一天，但这似乎是一个糟糕的解决方案。有没有更好的方法来实现我想要的效果？
答案1
您的公式可以简化为：
=SUMPRODUCT(C5:P5,{-1,1,-1,1,-1,1,-1,1,-1,1,-1,1,-1,1})*24
这意味着 (C5*-1 + D5*1 + E5*-1 + F5*1 + ... + O5*-1 + P5*1) * 24。
要计算超过 5 小时的天数，似乎你不想改变结构，我想出了一个公式
=-SUM(D5-C5>5/24,F5-E5>5/24,H5-G5>5/24,J5-I5>5/24,L5-K5>5/24,N5-M5>5/24,P5-O5>5/24)*0.5
它看起来不太简洁，但可以作为一个公式使用。例如，D5-C5>5/24返回 TRUE（Excel 也将其解释为 1）或 FALSE（Excel 也将其解释为 0）。因此，它计算超过 5 小时的天数，乘以 -0.5。
用一个公式来制作所有东西，那就是
=SUMPRODUCT(C5:P5,{-1,1,-1,1,-1,1,-1,1,-1,1,-1,1,-1,1})*24-SUM(D5-C5>5/24,F5-E5>5/24,H5-G5>5/24,J5-I5>5/24,L5-K5>5/24,N5-M5>5/24,P5-O5>5/24)*0.5
把这个公式往下拖，就可以轻松应用到所有工人。
答案2
我想建议几种简单的方法来创建时间表，这些方法在各个行业都很流行并且在实践中也得到应用。
怎么运行的：
- 第一种方法的数据范围是
A2:E7。 - 将单元格格式应用于
hh:mm数据区域。 - 公式在
G2：=(E2-B2)-(D2-C2)。
笔记：
- 填写公式并应用格式
hh:mm。 - 您可以使用的另一个公式是
H2：
=SUM(C2-B2)+(E2-D2)
- 填充并将
h" Hrs. and "m" Mins."格式应用于单元格。 G9和中H9获取周总计的公式是：=SUM(G2:G7)&=SUM(H2:H7)，将格式应用于0.00 " Days"单元格。
或者，您也可以使用此公式将单元格 G12 中的总工作小时数转换为天数、小时数和分钟数。
=IF(INT(SUM(G2:G7))>0,INT(SUM(G2:G7))&" days, ","")& IF(HOUR(SUM(G2:G7))>0, HOUR(SUM(G2:G7)) & " hours, ","")& IF(MINUTE(SUM(G2:G7))>0, MINUTE(SUM(G2:G7)) & " minutes ","")
- 数据范围修复午餐时间方法是
A12:D17，单元格格式是hh:mm。 - 公式在
E12：=D12-B12-C12。 - 使用和获取上面显示的周总数以及单元格格式
0.00 " Days"。 - 或者您也可以使用公式，我建议在单元格中使用
G12。
根据需要调整公式中的单元格引用。