很多人都知道Excel函数公式威力强大，
但遇到问题时却写不出公式，
现找也找不到。
今天为大家带来15个
很多人都在找的Excel公式，
已备大家不时之需。
看完觉得好的，
记得去底部点个好看再分享给朋友噢。
01
日期和时间数据的合并
下图为某单位员工刷卡考勤的部分记录，需要根据B列的刷卡日期和C列的刷卡时间，得到日期和时间合并后的数据。
在D2单元格中输入以下公式，并向下复制到D10单元格，即可得到日期和时间合并后的数据。
=B2+C2
![]()
02
计算故障处理时长
下图为某运营商宽带故障报修记录表的一部分，需要根据C列的接单时间和E列的处理时间，计算故障处理时长。
![]()
在F2单元格中输入以下公式，并向下复制到F6单元格。
=INT((E2-C2)*1440)
一天有1 440分钟，要计算两个时间间隔的分钟数，只要用终止时间减去开始时间，再乘以1 440即可。最后用INT函数舍去计算结果中不足一分钟的部分，计算出时长的分钟数。
如果需要计算两个时间间隔的秒数，可使用以下公式。
=(E2-C2)*86400
一天有86 400秒，所以计算秒数时使用结束时间减去开始时间，再乘以86 400。
除此之外，使用TEXT函数能够以文本格式的数字返回两个时间的间隔。
以下公式返回取整的间隔小时数。
=TEXT(E2-C2,"[h]")
以下公式返回取整的间隔分钟数。
=TEXT(E2-C2,"[m]")
以下公式返回取整的间隔秒数。
=TEXT(E2-C2,"")
03
计算员工在岗时长
下图所示为某企业员工加班考勤的部分记录，需要根据C列的上班打卡时间和D列的下班打卡时间，计算员工的加班工作时长。
![]()
如果在E2单元格中使用公式“=D2-C2”计算时间差，由于部分员工的离岗时间为次日凌晨，仅从时间来判断，离岗时间小于到岗时间，两者相减得出负数，计算结果会出现错误。
通常情况下，员工在岗的时长不会超过24小时。如果下班打卡时间大于上班打卡时间，说明两个时间是在同一天，否则说明下班时间为次日。
在E2单元格中输入以下公式，并向下复制到E10单元格。
=IF(D2>C2,D2-C2,D2+1-C2)
IF函数判断D2单元格的下班打卡时间是否大于C2单元格的上班打卡时间，如果条件成立，则使用下班时间减去上班时间。否则用下班时间加1后得到次日的时间，再减去上班时间。
公式也可以简化为：
=IF(D2>C2,D2,D2+1)-C2
还可以借助MOD函数进行求余计算。
=MOD(D2- C2,1)
用D2单元格的下班时间减去C2单元格的上班时间后，再用MOD函数计算该结果除以1的余数，返回的结果就是忽略天数的时间差。
04
计算员工技能考核平均用时
下图所示为某企业员工技能考核表的部分数据，B列是以文本形式记录的员工操作用时，需要计算员工的平均操作时长。
![]()
将D2单元格格式设置为“时间”，然后输入以
下数组公式，按组合键，计算结果为“0:01:12”。
{=SUM(--TEXT({"0时","0时0分"}&B2:B10,
"h:m:s;;;!0"))/9}
由于B列的时间记录是文本内容，因此，Excel
无法直接识别和计算。
使用字符串“{"0时","0时0分"}”与B2:B10单
图13 -17 技能考核平均用时元格的内容连接，变成九行两列的内存数组“{"0时1分18秒","0时0分1分18秒";"0时59秒","0时0分59秒";…;"0时1分27秒","0时0分1分27秒"}”。
Excel 将“0时0分0秒”样式的文本字符串识别为时间，将“0时0秒”“0时0分”“0分0秒”等样式的字符串仍然识别为文本。
TEXT函数的第二参数使用“h:m:s;;;!0”，将时间样式的字符串转换为“h:m:s”样式，非时间样式的文本字符串强制显示为0。计算结果如下。
{"0:1:18","0";"0","0:0:59";…;"0:1:27","0"}
TEXT函数计算出的结果仍然为文本，加上两个负号，即负数的负数为正数，通过减负运算将文本结果转换为时间序列值。
最后将SUM函数的求和结果除以总人数9，得到考核平均用时。
05
从混合内容中提取时间和日期数据
从考勤机中导出的刷卡记录往往同时包含日期和时间，如下图所示，需要在C列和D列分别提取出B列刷卡记录中的日期和时间。
![]()
由于时间和日期数据的实质都是序列值，因此，既包含日期又包含时间的数据可以看作是带小数的数值。其中，整数部分为代表日期的序列值，小数部分为代表时间的序列值。
在C2单元格中使用以下公式提取日期数据。
=INT(B2)
=TRUNC(B2)
使用INT函数或TRUNC函数提取A列数值的整数部分，结果即为代表日期的序列值。
在D2单元格中可使用以下公式提取时间数据。
=B2-INT(B2)
=MOD(B2,1)
使用MOD函数计算A2单元格与1相除的余数，得到A2数值的小数部分，结果即为代表时间的序列值。如果结果显示为小数，可将单元格格式设置为“时间”。
除此之外，也可以使用TEXT函数完成日期时间的提取，以下公式可以提取出A列中的日期。
=--TEXT(A2,"e-m-d")
格式代码使用“e-m-d”，即“年-月-日”。
以下公式可以提取出A列中的时间。
=--TEXT(A2,"h:m:s")
格式代码使用“h:m:s”，即“时:分:秒”。
06
将英文月份转换为月份数值
如下图所示，A列为英文的月份名称，需要在B列转换为对应的月份数值。
![]()
在B2单元格中输入以下公式，并向下复制到B10单元格。
=MONTH(A2&1)
使用连接符“&”将A2单元格与数值“1”连接，得到新字符串“Apr1”，成为系统可识别的文本型日期样式，再使用MONTH函数提取出日期字符串中的月份。
YEAR、MONTH和DAY函数均支持数组计算，在按时间段的统计汇总中被广泛应用。
07
汇总指定时间段的销售额
下图为某单位2017年销售记录表的部分内容，A列是业务发生日期，D列是业务金额，需要计算上半年的业务总额。
![]()
可以使用以下公式完成汇总。
=SUMPRODUCT((MONTH(A2:A13)DAY(E2)”部分用于判断开始日期的天数是否大于结束日期的天数。
“E2=EOMONTH(E2,0)”部分用于判断结束日期是否为当月的最后一天。
当开始日期的天数大于结束日期的天数，并且结束日期等于当月的最后一天时，AND函数返回逻辑值TRUE，否则返回逻辑值FALSE。
最后将DATEDIF函数的结果与AND函数返回的逻辑值相加。在四则运算中，逻辑值TRUE的作用相当于1，逻辑值FALSE的作用相当于0。如果两个条件同时成立，则相当于原公式+1，否则为原公式+0。
14
计算员工应出勤天数
下图为某公司新入职员工的部分记录，需要根据入职日期，计算员工该月应出勤天数。
![]()
在C2单元格中输入以下公式，并向下复制到C8单元格。
=NETWORKDAYS(B2,EOMONTH(B2,0))
“EOMONTH(B2,0)”部分用于计算出员工入职所在月份的最后一天。
NETWORKDAYS函数以入职日期作为起始日期，以入职所在月份的最后一天作为结束日期，计算出两个日期间的工作日天数。
本例中省略第三参数，实际应用时如果该月份有其他法定节假日，可以使用第三参数予以排除。
15
判断考核等级
下图所示的是员工考核成绩表的部分内容，F3:G6单元格区域是考核等级对照表，首列已按成绩升序排序，要求在D列根据考核成绩查询出对应的等级。
![]()
在D2单元格中输入以下公式，并向下复制到D11单元格。
=VLOOKUP(C2,F$3:G$6,2)
VLOOKUP函数第四参数被省略，在近似匹配模式下返回查询值的精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于查询值的最大值。
C2单元格的成绩62在对照表中未列出，因此Excel在F列中查找小于62的最大值60进行匹配，并返回G列对应的等级“合格”。
希望这篇文章能帮到你！
怕记不住可以发到朋友圈自己标记喔。
![]()
以上图文源自
北京大学出版社出版
《Excel 2016函数与公式应用大全》
![]()
编辑推荐
1. 专家云集：多位身处各行各业，并身怀绝技的微软全球有价值专家与您无私分享。多年对 Excel的研究结果揭秘。
2. 知识点全覆盖：详尽而又系统地介绍了 Excel函数与公式的所有技术特点和应用方法，全面覆盖相关知识点，完备知识体系无人能及。
3. 解决实际问题：大量源自实际工作的典型案例，通过细致地讲解，生动地展示各种应用技巧，快速提高读者的办公效率，让读者提前完成手头工作，不用加班。
4. 专业级深度剖析：对常常困扰学习者的功能性特性进行深入剖析，可以让读者既能知其然，又能知其所以然。
购买地址：
点击左下文末“阅读原文”，可至京东图书频道在线选购（官方正版）
京东年中购物节，图书每满100减50，不容错过！
|
|
转播
