当前位置:首页 > 统计知识

一般人都不知道的三个 Excel 隐藏函数

作者:admin     时间:2020-07-29 16:00:55     来源:互联网    

一、什么是隐藏函数


普通函数在输入时都有智能填充功能,输入部分函数以后可以按 Tab 键进行补齐。

而隐藏函数输入时没有智能提示,也无法使用 Tab 键补齐,需要手工输入整个函数。


普通函数可以点击“编辑栏”旁边的“插入函数”按钮,进行查找使用,隐藏函数无法找到。

隐藏函数在函数列表里是找不到,甚至连帮助文件中也没有相关说明。


那么,又为什么隐藏这些函数呢 ?


微软没有官方声明将这几个函数隐藏的原因。对于 DATEDIF 函数,微软为了兼容当时流行的电子表格软件 Lotus 1-2-3 而提供了这个函数。1983 年由莲花公司出品 Lotus 1-2-3,出现时间早于微软的 Excel,在 1980 年代中期如日中天。但在后面的竞争中逐渐被淘汰。

二、三个函数


1、DATEDIF 函数


DATEDIF 函数用于计算两个日期之间相隔的天数、月数或年数。其函数语法为:

DATEDIF(start_date,end_date,unit)


第一和第二个参数是日期,第三参数是计算的间隔单位,可以为 D、M、Y、YD、YM、MD。


前面两个参数很容易理解,下面通过实际案列来理解最后一个参数。


结合 TODAY 函数,第三个参数选择 「Y」 可以计算员工年龄:


第三个参数选择 「D」 可以计算员工工龄(具体到天):


身份证号码可以提取出生日信息,所以可以直接用身份证号码计算年龄:


使用以下公式:

=DATEDIF(--TEXT(MID(B2,7,8),"0000-00-00"),TODAY(),"Y")

HR 小伙伴们经常需要在员工生日的时候送上慰问,所以需要在员工表上设置生日提醒。将第三个参数选择 「YD」 可以计算日期时间段中天数的差,而忽略日期中的年,进而实现生日提醒:


使用以下公式实现 10 天之内的生日提醒:

=TEXT(10-DATEDIF(C3,NOW()+10,"YD"),"0 天后生日;;今日生日")


涉及的计算员工的考勤工时,需要计算两个时间的小时、分钟或者秒数差,可惜的是 DATEDIF 的第三个参数没有提供这两个间隔上的计算参数。这时可以考虑使用 power pivot 的 DATEDIFF 函数。这两个函数真不能不说是相似啊。不过后者强大很多。


2、NUMBERSTRING


财务人员制作报销单、发票模板等需要将阿拉伯数字转化为中文大写数字。如果一个一个手工输入的话,工作量就不可估量了。隐藏函数 NUMBERSTRING 就是实现这种转换的函数。其基本语法为:

NUMBERSTRING(Value,Type)


第一个参数是数值,值得注意的是该参数不支持包含小数的数值。第二个参数指定返回结果的类型,有三种 1,2,3。第二个参数选择为 2 就是我们财务上需要的中文大写数字啦。

我们还可以使用另外两种方法实现中文大写数字转换。


使用快捷键 「Ctrl + 1」设置单元格格式为中文大写数字

使用 TEXT 函数也可以实现转换,TEXT 函数基本语法:

TEXT(Value,Format_text)


将 Format_text 设置为 [DBNum2] 就可以实现转换

3、DATESTRING


DATESTRING 用于将日期转换为“yy 年 mm 月 dd 日”格式的文本日期。基本语法为:

DATESTRING(Date)


在 Excel 中输入以下公式,将返回 20 年 2 月 23日(返回系统当天日期)

=DATESTRING(TODAY())


这个函数比较简单,使用场景比较少。小编就不多做介绍了,在这里抛砖引玉,供需要的朋友们使用哈。


End.