`
阅: 1901 | 回: 0
Excel中隐藏的函数 Excel基础 , 编号:0488 快速回复
发表于2015/10/26 14:03:09 楼主 
头像 等级:初学者
积分:0
财富值:0.0
身份:普通用户

打开Excel函数列表,密密麻麻的函数名称一定会让你看的眼花缭乱。在Excel 2013中,函数达到了454个之多,函数功能也越来越细化。好在这些函数都有帮助文件,一些不熟悉的函数在使用时可以看看帮助文件做一下简单的了解。
但是在Excel中,还有几个隐藏的函数,能够实现一些现有的函数无法实现的功能。为什么Excel会有隐藏函数,不知微软出于什么考虑而不公布出来。不过Excel隐藏函数不是很多,常用到的就三个: NUMBERSTRING、DATESTRING、DATEDIF。

首先看NUMBERSTRING函数,它的作用是将数字转换为大写。这个函数有两个参数,第一个参数是要转换的数值,第二个参数是指定返回的类型。类型共三种,其中:1为汉字小写,2为汉字大写,3为汉字读数。
如果数值有小数,该函数会四舍五入后再进行转换。

再来看DATESTRING函数,它的作用是将日期转换为yy年mm月dd天。如需计算两个日期的差值,隐藏函数DATEDIF可以很好解决。
这个函数是几个隐藏函数中,功能最强大,变化最多、应用最广的一个。
该函数有三个参数,第一个参数是开始日期,第二个参数是结束日期,通过设置最后一个参数,可以指定所需信息的返回类型。


第三参数在使用MD、YM、YD时,在会有不同的bug,使用频率也比较低。而使用D来计算两个日期的间隔天数与日期直接相减的效果相同,相比较而言,还是直接相减快捷方便一些。
除了有bug的3个和不方便的1个,最后就剩下Y和D了。咱们就来说说这两个。

如下图所示,需要根据员工的入厂日期计算工龄,计算的截止日期是14年10月1日。

C2单元格的公式是:
=DATEDIF(B2,"14-10-1","Y")
参数使用“Y”,表示计算时间段中的整年数。在计算员工工龄等方面,经常会被用到,大家注意看,由于是计算到14年10月1日的整年数,所以B5和B6的日期虽然只差了一天,但是DATEDIF函数的结果却差了一年。

账龄分析是指企业对应收账款,按账龄长短进行分类,并分析其可回收性。是财务工作中一个重要的组成。如下图所示,需要根据业务发生日期计算对应的账龄区间,计算的截止日期是14年10月1日。

C2单元格的公式:
=LOOKUP(DATEDIF(B2,"14-10-1","M"),{0,6,12,24},{"6个月以内","6个月至一年","一至二年","二年以上"})
DATEDIF函数第二参数使用“M”,计算B2单元格日期与截止日期间隔的整月数,不足一个月的部分被舍去。计算结果7,用作LOOKUP函数的第一参数查询值。
LOOKUP函数在{0,6,12,24}中查找小于或等于7的最大值进行匹配,然后返回第三参数{"6个月以内","6个月至一年","一至二年","二年以上"}中相同位置的值,最终计算结果为“6个月至一年”。



我的个性签名

快速回复

目前不允许游客发表,请 登录 注册 后再发贴。