这些Excel函数公式,很多人都在找!(下)

论坛 期权论坛 期权     
excel教程   2019-7-27 22:20   5814   0
微信扫码观看全套Excel、Word、PPT视频





作者:ExcelHome
来源:Excel之家ExcelHome(ID:iexcelhome)

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单元格,在部分单元格中会得到错误的结果,如图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列对应的等级“合格”。



ExcelHome:每日Office干货,全年不休息(公众号ID:iexcelhome)

扫一扫添加老师微信

在线咨询Excel课程


Excel教程相关推荐


这些Excel函数公式,很多人都在找!(上)
Excel小白福利:鼠标点一点,统计和排名全搞定!
什么?学了这么久Excel,我居然连填充序号都不会?

想要跟随滴答老师全面系统学习Excel,不妨关注《一周Excel直通车》视频课或者《Excel极速贯通班》。

《一周Excel直通车》视频课
包含Excel技巧、函数公式、
数据透视表、图表。
一次购买,永久学习。
[iframe]https://v.qq.com/iframe/preview.html?width=500&height=375&auto=0&vid=b05674i0j3h[/iframe]
最实用接地气的Excel视频课
《一周Excel直通车》
风趣易懂,快速高效,带您7天学会Excel
38 节视频大课
(已更新完毕,可永久学习)
理论+实操一应俱全

主讲老师: 滴答



Excel技术大神,资深培训师;
课程粉丝100万+;
开发有《Excel小白脱白系列课》
        《Excel极速贯通班》。

原价299元
限时特价 99 元,随时涨价
少喝两杯咖啡,少吃两袋零食
就能习得受用一生的Excel职场技能!

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



购课后,加客服微信:blwjymx2领取练习课件


让工作提速百倍的「Excel极速贯通班」
↓ 点击阅读原文,可直接购买。
分享到 :
0 人收藏
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

积分:315
帖子:63
精华:0
期权论坛 期权论坛
发布
内容

下载期权论坛手机APP