excel表格如何求乘积之和(Excel精品教程：996是福？离职前，他曝光一组Excel数据公开打脸老板！)

点击图片   1元抢购 Excel、Word、PPT全套课程每天一点小技能职场打怪不得怂

什么是996呢？是指早上9点上班，晚上9点下班，中午和晚上各有1小时吃饭时间，每周工作6天的工作模式而955，也就是朝九晚五，中午就餐在工作时间内，但不能休息，共计8小时工作时间，每周五天的工作模式那么问题来了，996一年比955多工作了多少小时?

不算不知道，一算吓一跳，竟然多了1042小时，这可是半年的工作量呢！！！咱们今天不讨论996是否有问题,而是讨论怎么用Excel计算出相差的工时数，从而挖掘一些关于时间计算方面的技巧，至少以后遇到类似问题时可以少走弯路。

1直接相减计算时间差如果不考虑休息时间的话，要计算工时可以直接用下班时间-上班时间，如果还要在工时后面增加“小时”两个字，就需要用到TEXT函数公式为=TEXT(C2-B2,"h小时")，操作过程看动画演示。

动画1在这个公式中，用到了表时间的格式代码“h”，表示取小时数在代码后可以自己添加需要显示的单位2数字和时间之间的换算如果要在工时中扣除休息时间，只需要在原工时中减去休息时间即可，但公式结果却出现了错误：。

温馨提示：加入下面QQ群：885401280，下载教程配套的课件练习操作。

这里有一个问题，上班时间和下班时间数字是时间格式，而休息时间数字却是数字格式在EXCEL中，一个自然数对应一个日期，而0到1之间的小数则对应一个时间在本例中，休息时间是自然数，可以直接参与日期计算但不能参与时间计算，它需要进行转换，才能与时间格式的数字进行运算，转换方法是将这个数除以24。

所以正确的公式应该是=TEXT(C2-B2-D2/24,"h小时")，结果如图所示：

注意：这里有一个坎，很多对EXCEL时间不了解的人会对C2-B2-D3/24心存疑惑他们认为D3/24是一个小数，C2-B2=21-9=12，后12减去一个小数怎么就等于10呢？实际上，EXCEL并非按21-9=12这么来计算时间的。

9:00或者21:00只是显示给我们看的时间格式数字，它们对应的真正的时间值是0.375（9/24）、0.875(21/24)C2-B2-D3/24=0.875-0.375-0.08333=0.41667。

最终时间值0.41667再显示为时间格式，就成了0.41667*24=10小时如果绕不过弯，那你也可以直接把休息时间的“2”改为“2:00”，则可以直接使用=TEXT(C2-B2-D2,"h小时")计算出工时。

3计算排除周末的工作日天数计算与工作日有关的问题时经常会用到NETWORKDAYS.INTL函数，如果要排除的休息日是固定的，在不考虑特殊节假日的情况下，只需要使用三个参数，函数格式为：NETWORKDAYS.INTL（开始日期，结束日期，周末）。

996模式的工作天数可以用公式“=NETWORKDAYS.INTL("2018-1-1","2018-12-31",11)”计算出来：

第三参数代码11表示仅星期日休息，在填写第三参数时，其他代码会有提示：

很明显，955模式只需要将第三参数改为“1”即可，表示星期六和星期日都休息这里需要说明的是，在公式中直接输入日期时，需要将日期放在引号中在本例中省略的函数的第四参数是除了周末的特殊节假日，可以单独输入，也可以直接引用单元格。

公式=NETWORKDAYS.INTL("2018-1-1","2018-12-31",11,{"2018-1-1","2018-5-1","2018-10-1"})会在去掉周末的基础上排除元旦，五一和十一这三天：

也可以通过单元格区域进行排除：公式为=NETWORKDAYS.INTL("2018-1-1","2018-12-31",11,I2:I4)

4防止小时发生进位  工作天数有了，每天的工时有了，相乘即可得到全年工作时数，然而公式的结果却出乎预料：

是公式错了吗？非也！在公式“=TEXT(F2*(C2-B2-D2/24),"h小时")”中，F2是年度工作天数313天，“C2-B2-D2/24”表示每天工时10小时，10小时在EXCEL中对应的数字是0.4166666667，二者的乘积130.4166666667是一个带小数：

时间代码h只能显示数字小数部分对应的小时数（即0.4166666667对应的10小时），整数部分（130）对应的是日期就无法显示这种问题在对时间求和的时候经常会遇到，解决方法也很简单，给时间代码h加上一对中括号即可，正确的公式为：=TEXT(F2*(C2-B2-D2/24),"[h]小时")。

5对含有单位的数据进行计算平时工作中经常会遇到在数字后面加单位的需求，看起来舒服，但要进行计算时就头疼了。正确的解决方法是使用自定义格式加单位，操作方法如动态演示：

动画2使用自定义格式添加单位，数据格式并没有发生变化，所以不管求和还是其他计算，都很方便但有些时候已经填好了单位，再把单位去掉用自定义格式添加单位其实也挺麻烦的以下通过计算带单位的996和955之间全年的时间差，介绍两个常用的公式。

公式1：通用法则通用法则需要使用SUMPRODUCT和SUBSTITUTE这两个函数，原理是利用SUBSTITUTE将数据中的单位替换为空，再使用SUMPRODUCT对数组求和或求差，公式为：=SUMPRODUCT(SUBSTITUTE(G2:G3,"小时","")*{1;-1})&"小时"

SUBSTITUTE(G2:G3,"小时","")将单元格G2、G3中的单位“小时”替换为空，结果为{"3130";"2088"}，是两个文本型的数字：

将两个文本型数字与常量数组{1;-1}相乘后变成一正一负的两个数{3130;-2088}，同时完成了文本数值转为数字的过程：

最后使用SUMPRODUCT完成求和。

公式2：个性法则个性法则是根据问题的具体特点设计的公式，在本例中，要计算的数据都是单元格的最左边4位，可以使用LEFT函数分别提取出来后进行相减，公式为：=(LEFT(G2,4)-LEFT(G3,4))&"小时"

这个公式相对很简单，也好理解，就本例而言是可行的，但不具有通用性今天分享了五个技巧，但其中涉及的细节知识点非常多，没有进行深入讨论，有不明白的点可以留言提问，共性问题会单独发表教程扫一扫添加老师微信

扫一扫，在线咨询Excel课程

Excel教程相关推荐早会这3个Excel小技巧，也不会瞎忙活半天了！还在用老方法对带单位的数字求和？这个方法帮你一步搞定！Lookup始终是哥！我用这个公式2分钟完成别人半天的查询工作量，同事看了都想学！

有1个Excel技巧叫：省时一整天，你却连我的名字都不知道…【Excel教程】想要全面系统学习Excel，不妨关注部落窝教育的《一周Excel直通车》视频课或者《Excel极速贯通班》《一周Excel直通车》。

视频课包含Excel技巧、函数公式、数据透视表、图表一次购买，永久学习最实用接地气的Excel视频课《一周Excel直通车》风趣易懂，快速高效，带您7天学会Excel38 节视频大课（已更新完毕，可永久学习）。

理论+实操一应俱全主讲老师： 滴答

Excel技术大神，资深培训师；课程粉丝100万+；开发有《Excel小白脱白系列课》        《Excel极速贯通班》原价299元限时特价99元少喝两杯咖啡，少吃两袋零食就能习得受用一生的Excel职场技能！。

  长按下面二维码立即购买学习

购课后，加客服微信：blwjymx2领取练习课件

让工作提速百倍的「Excel极速贯通班」↓ 点击阅读原文，免费试听。

亲爱的读者们，感谢您花时间阅读本文。如果您对本文有任何疑问或建议，请随时联系我。我非常乐意与您交流。