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

JaryYuan
JaryYuan
JaryYuan
39
文章
0
评论
2020-04-1809:05:00 评论 138 1446字
摘要

今天向大家介绍 Excel 中三个实用的隐藏函数,对于 HR 和财会小伙伴来说,一定会觉得相见恨晚哦。这三个函数分别是DATEDIF、NUMBERSTRING、DATESTRING。

一、什么是隐藏函数

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

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

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

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

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

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

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

二、三个函数

1、DATEDIF 函数

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

DATEDIF(start_date,end_date,unit)

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

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

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

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

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

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

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

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

使用以下公式:

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

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

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

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

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

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

2、NUMBERSTRING

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

NUMBERSTRING(Value,Type)

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

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

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

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

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

TEXT(Value,Format_text)

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

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

3、DATESTRING

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

DATESTRING(Date)

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

=DATESTRING(TODAY())

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

End.

作者:JaryYuan

来源:公众号JaryYuan

本文均已和作者授权,如转载请与作者联系。

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: