这些Excel函数公式，很多人都在找！

很多人都知道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,'[s]')

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)<7)*D2:D13) 

MONTH函数返回A2:A13单元格中日期数据的月份值，结果为： 

{1;3;6;2;6;3;4;6;7;8;6;11} 

因为要计算1~6月份的业务总额，所以要判断月份值是否小于7。 

用“MONTH(A2:A13)<7”计算出的一组逻辑值与D2:D13单元格区域的数值相乘，最后用SUMPRODUCT函数返回乘积之和。 

08

汇总指定年份和月份的销量

下图为某单位销售表的部分内容，业务日期分布在不同年份，需要根据年份和月份，在G列和H列汇总销量。 

在G3单元格中输入以下公式，并复制到G3:H14单元格区域。 

=SUMPRODUCT((YEAR($A$2:$A$746)=G$2)*(MONTH($A$2:$A$746)=$F3)*$C$2: 

$C$746) “YEAR($A$2:$A$746)=G$2”部分表示使用YEAR函数分别计算“$A$2:$A$746”单元格的年份，并判断是否等于“G$2”单元格指定的年份值。 

“MONTH($A$2:$A$746)=$F3”部分表示使用MONTH函数分别计算“$A$2:$A$746”单元格的月份，并判断是否等于“$F3”单元格指定的月份值。 

将两组逻辑值相乘，如果对应位置均为逻辑值TRUE，相乘后结果为1，否则返回0。 

再与“$C$2:$C$746”单元格的销售额相乘，最后用SUMPRODUCT函数返回乘积之和。 

使用此公式时，需要注意使用不同单元格引用方式的变化。其中日期所在范围“$A$2: $A$746”和销售额所在范围“$C$2:$C$746”均为绝对引用，表示年份条件的“G$2” 使用列相对引用、行绝对引用，表示月份条件的“$F3”使用列绝对引用、行相对引用。

09

日期和时间数据的合并

下图为某企业新生产线的设备安装与调试计划表，需要根据开始日期和结束日期计算每个项目的天数。 

在D2单元格中输入以下公式，将单元格格式设置为常规后，向下复制到D6单元格。 

=DAYS(C2,B2)+1

公式也可以写为： 

=C2-B2+1 

在实际应用中，使用两个日期直接相减的方式计算相差天数更加方便。 

10

计算今天是本年度的第几天

如下图所示，设置A2单元格格式为自定义格式“第0天”，使用以下公式将返回系统当前日期是本年度的第几天。 

=TODAY()-'1-1'+1 

在Excel中输入“月-日”形式的日期，系统会默认按当前年份处理，“TODAY( )-'1-1'”就是用系统当前的日期减去本年度的1月1日，再加上一天得到今天是本年度的第几天。 

同理，使用以下公式可以计算本年度有多少天。 

='12-31'-'1-1'+1 

如果公式引用包含日期或时间的单元格时，Excel有可能会将公式所在单元格的格式自动更改为日期或时间，此时可根据需要重新调整单元格格式。

11

判断指定日期是所在季度的第几天

如下图所示，需要根据A列日期，计算出该日期是所在季度的第几天。 

在B2单元格中输入以下公式，并向下复制到B10单元格。 

=COUPDAYBS(A2,'9999-1',4,1)+1 

该函数为财务函数范畴，用于返回从付息期开始到结算日的天数。 

函数的基本语法如下。 

COUPDAYBS(settlement,maturity,frequency,[basis]) 

第一参数settlement是有价证券的结算日；第二参数maturity是有价证券的到期日，可以写成一个任意较大的日期序列值；第三参数frequency使用4，表示年付息次数按季支付。第四参数basis使用1，表示按实际天数计算日期。 

本例中年付息次数选择按季支付，所以A2单元格的日期所在季度的付息期即为该季度的第一天。公式以A2单元格的日期作为结算日，通过计算所在季度第一天到当前日期的间隔天数，结果加1，变通得到指定日期是所在季度的第几天。 

12

计算员工转正日期

下图为某单位新员工入职表的部分记录，需要根据入厂日期和实习期月数计算转正日期。 

在D2单元格中输入以下公式，并向下复制到D10单元格。 

=EDATE(B2,C2)

EDATE 函数使用B2单元格中的日期作为指定的开始日期，返回由C2单元格指定的月份后的日期。 

13

计算房屋租赁费

下图为某公司商铺租赁表的部分内容，需要根据租赁起止日期计算租赁月数。 

如果在F2单元格中直接使用以下公式计算间隔月数，并将公式向下复制到F9单元格，在部分单元格中会得到错误的结果，如图13-54中的F2、F3、F4和F8单元格。 

=DATEDIF(D2,E2,'m') 

使用DATEDIF函数计算间隔月数时，如果结束日期是当月的最后一天，并且开始日期的天数大于结束日期的天数，计算结果会少一个月。 

根据此规律，可以在原有公式基础上增加判断条件，在G2单元格中输入以下公式，并向下复制到G9单元格，公式将返回正确结果。 

=DATEDIF(D2,E2,'m')+AND(DAY(D2)>DAY(E2),E2=EOMONTH(E2,0))“DAY(D2)>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列对应的等级“合格”。