[原创][科普帖]2007-2019各版本新增函数简介

查看: 25324|回复: 44

[原创]

[科普帖]2007-2019各版本新增函数简介

[复制链接]

流浪铁匠

流浪铁匠

当前离线

性别保密

UID3110404

阅读权限50

注册时间2015-6-3

在线时间 小时

最后登录1970-1-1

积分1136

财富

分享

技术

精华

TA的精华主题

TA的得分主题

电梯直达

1楼

发表于 2019-9-24 22:06

|

只看该作者

|倒序浏览

|阅读模式

[广告] ★Excel Home云课堂 - 专业的职场技能充电站★ ★Excel三大利器★ ★ 笨办法学VBA全系列★

本帖已被收录到知识树中，索引项：版本差异和兼容

本帖最后由 流浪铁匠 于 2019-9-25 08:34 编辑

作为一个过来人,函数的出现版本是影响公式使用的1个重要因素,也是新手无法正常使用公式的一个原因

所以简单整理了下最近几个版本的新增函数

截至最新版本,从官网的函数列表里以及之前在论坛与群里交流整理的资料分析,目前已知的工作表函数合计出现过491个(含特殊环境使用的自动化函数,已知并中文版可用的隐藏函数以及只存在于部分版本并已被删除的函数,另外由于中文版函数性质,len与lenb这种可以被视为2个函数)

2007是个分水岭,从这个版本开始函数涉及的很多参数完全不同于2003

QQ截图20190924220712.jpg (21.67 KB, 下载次数: 155)

下载附件

保存到相册

2019-9-24 22:07 上传

所以很多时候新手会由于在xls工作簿下使用整列整行引用或者套嵌层数超过7层出现错误提示

函数

491个函数列表.zip

2019-9-25 08:34 上传

点击文件名下载附件

77.15 KB, 下载次数: 1131

评分

25

查看全部评分

海龙周

7hao

橒♂蝣

1007890405

一亦

分享到:

QQ好友和群

QQ空间

收藏50

分享

有用5

无用0

回复

举报

流浪铁匠

流浪铁匠

当前离线

性别保密

UID3110404

阅读权限50

注册时间2015-6-3

在线时间 小时

最后登录1970-1-1

积分1136

财富

分享

技术

精华

TA的精华主题

TA的得分主题

2楼

楼主|

发表于 2019-9-24 22:08

|

只看该作者

[广告] ★会Excel,就能做支持PC、手机、Web、App、Windows、安卓、IOS、Linux信息一体化的各种管理系统 ★

本帖最后由 流浪铁匠 于 2019-9-28 16:03 编辑

言归正传,2007的新增函数有12个

1.png (89.86 KB, 下载次数: 116)

下载附件

保存到相册

2019-9-28 15:59 上传

其中iferror这个函数的出现将早期版本的if(iserror(原公式),公式报错时指定的结果,原公式) 写法来规避返回错误值的写法简化为iferror(原公式,公式报错时返回的结果)

使整个公式简化了一半且不用写2次原公式,大幅简化了公式与维护难度。

也正是因为这个函数出现于2007,早期版本无法使用,是一个经常被版本限制而被询问的函数(还是有部分人在用2003)

sumifs,countifs,averageif(s)等条件统计类函数的出现将一些之前版本需要辅助列或数组公式才能解决的条件统计问题出现了专门的函数,简化统计难度,提升了效率

可惜的是条件极值类的maxifs和minifs居然是3个版本后才出现而且在我看来还不如直接出largeifs/smallifs更加实用

SQL.REQUEST较偏门,是在之前的官网函数列表里发现的,官网介绍里2007是这个函数的最后支持版本,疑似pq的前身

而多维数据集函数如果使用过pp会发现其实和pp也是有关联的,

然而这个版本出现的多维数据集函数相对小众,数据源环境比getpivotdata更加苛刻,需要使用olap多维数据集或者power pivot建模关联创建的数据源才能使用

感觉和SQL.REQUEST类似,这批函数疑似是power pivot的度量值的前身,至少有诸多关联

另外请注意这个版本下edate/quotient等早期版本需要加载分析工具库才能使用的函数在这个版本可以直接使用了

此外,版本的差异会造成你在早期版本打开具有高版本函数的的公式时，公式内相应函数会添加_xlfn. 前缀

111222QQ图片20190928155209.png (7.69 KB, 下载次数: 116)

下载附件

保存到相册

2019-9-28 15:55 上传

表明公式内存在当前版本不支持的高版本函数,这个性质保证了使用低版本打开高版本函数写的公式的情况下,计算结果可以正常显示(但不能再进行任何计算,否则就会显示name错误)

回复

举报

流浪铁匠

流浪铁匠

当前离线

性别保密

UID3110404

阅读权限50

注册时间2015-6-3

在线时间 小时

最后登录1970-1-1

积分1136

财富

分享

技术

精华

TA的精华主题

TA的得分主题

3楼

楼主|

发表于 2019-9-24 22:09

|

只看该作者

[广告] ★用Excel做企业管理系统，Excel服务器学习和下载★

本帖最后由 流浪铁匠 于 2019-9-24 23:34 编辑

2010,这是XP时代的最后版本,这个版本的新增函数感觉主要是优化完善之前版本的一些函数,新增了62个函数但其实绝大多数只是对之前版本的统计类函数的改名/优化/完善

3.jpg (153.65 KB, 下载次数: 104)

下载附件

保存到相册

2019-9-24 23:31 上传

主要做了3个方面的改动

1,部分函数进行了算法更改,以便提高函数的准确性和性能。 例如，因为 BETADIST 函数不准确，所以实施了一种新算法，以便提高该函数的准确性。 MOD 函数现在使用新算法以同时获得准确性和速度，而 RAND 函数现在使用一种新随机数算法。

2,部分统计函数（参考）已重命名，以使它们与科学界的函数定义和 Excel 中的其他函数名称更加一致。 新的函数名称还更准确地说明了其功能。 例如，由于 CRITBINOM 返回二项式分布的反函数，所以 BINOM.INV 是更恰当的名称。

3,添加了一批由于早期版本相应函数性能缺失或不完善而新增的相关函数,这批函数的效果会更好

这个版本开始出现一个名词叫”兼容性函数”:在 Excel 2010 或更高版本中，这些函数已由新函数取代；新函数可以提供更好的精确度，其名称更好地反映其用法。 你仍可以出于与 Excel 早期版本兼容的目的使用这些函数。例如在后续有concat的版本,concatnate就被归在兼容性函数里,

这个版本微软可能有临时工,因为ceiling在这个版本接连出现了3个衍生函数,然而其中ecma.ceiling直接被雪藏,iso.ceiling在功能区函数库内被隐藏(但10版本帮助内能找到这个函数),而这个版本显性的ceiling.precise在下个版本2013就被隐藏了(还是可以使用但函数列表里不存在)

即ceiling在2010新增3个相关函数还是不够完善,这个问题在2013的相应新增函数ceiling.math才得到解决(这个函数才能指定负数时的舍入方向,floor同理)

这个版本优化完善的主要为统计函数,专业性很高,我对此没什么研究

但是这个版本里有几个函数是需要介绍的

workday.intl和networkdays.intl,这2个函数相对早期版本相应函数,主要解决了不能指定周末的问题,部分日期统计问题可以有效简化

5.jpg (33.1 KB, 下载次数: 123)

下载附件

保存到相册

2019-9-24 23:32 上传

aggregate是subtota的升级版,可惜不支持多维,但1/2参数组合也能解决很多subtotal不能解决的统计效果,这个函数我有专门开帖介绍过http://club.excelhome.net/thread-1482745-1-1.html,这里不再赘述

mode.mult 这个函数是mode的升级版,因为当有多个众数时mode只能返回第1个,而这个函数会根据结果个数自动扩展为相应个数的内存数组

新增统计函数专业性较高,以rank.avg为例,其结果本质为数值对应的不重复排名的平均值,在群里和论坛就没见过有人需求过这个效果

4.jpg (40.19 KB, 下载次数: 106)

下载附件

保存到相册

2019-9-24 23:33 上传

回复

举报

流浪铁匠

流浪铁匠

当前离线

性别保密

UID3110404

阅读权限50

注册时间2015-6-3

在线时间 小时

最后登录1970-1-1

积分1136

财富

分享

技术

精华

TA的精华主题

TA的得分主题

4楼

楼主|

发表于 2019-9-24 22:10

|

只看该作者

[广告] ★会Excel,就能做支持PC、手机、Web、App、Windows、安卓、IOS、Linux信息一体化的各种管理系统 ★

本帖最后由 流浪铁匠 于 2019-9-24 23:39 编辑

2013这个版本新增了50个函数,其中很多函数的功能都是早期版本函数不具备或者有局限的

6.jpg (159.15 KB, 下载次数: 88)

下载附件

保存到相册

2019-9-24 23:35 上传

7.jpg (101.85 KB, 下载次数: 82)

下载附件

保存到相册

2019-9-24 23:35 上传

这个版本对于函数来说是个幸运的版本,因为这个版本的很多函数的能力是之前版本的函数不具备或者能力大增的

首推目前已经被论坛海鲜老师玩坏的filterxml http://club.excelhome.net/thread-1485535-1-1.html

一个web函数活活被玩成split效果……

另外base+decimal的出现使函数提取与构造字母字符串成为可能

7.1.jpg (79.61 KB, 下载次数: 86)

下载附件

保存到相册

2019-9-24 23:37 上传

unichar和unicode的出现使函数总算可以完全创建unicode编码字符,而且解决了由于char和code的不完全可逆运算挖出的code结果为63的函数大坑

isformula和formulatext的出现填写了显示公式和判断公式的函数空白,而且不怕循环引用的能力在一些特殊环境会有用武之地

7.2.jpg (24.67 KB, 下载次数: 106)

下载附件

保存到相册

2019-9-24 23:37 上传

numbervalue是个清洗数值很实用的函数(因为能直接清除两侧多余空格和几个非打印字符,且能处理欧式数字格式) http://club.excelhome.net/thread-1482509-1-1.html

ceiling.math和floor.math总算填补了上个版本更改了3次也没完善的舍入性能问题

7.3.jpg (40.98 KB, 下载次数: 82)

下载附件

保存到相册

2019-9-24 23:38 上传

这个版本新增能力的函数太多,不同领域需求不一,上面仅以个人经验介绍了几个相对实用的(篇幅有限不一一介绍)

由于2010虽然新增了62个函数但其实有一部分的目的是改名,所以最近几个版本里2013是新增新功能函数最多的版本

回复

举报

流浪铁匠

流浪铁匠

当前离线

性别保密

UID3110404

阅读权限50

注册时间2015-6-3

在线时间 小时

最后登录1970-1-1

积分1136

财富

分享

技术

精华

TA的精华主题

TA的得分主题

5楼

楼主|

发表于 2019-9-24 22:11

|

只看该作者

[广告] ★Excel Home云课堂 - 专业的职场技能充电站★ ★Excel三大利器★ ★ 笨办法学VBA全系列★

本帖最后由 流浪铁匠 于 2019-10-25 17:20 编辑

2016，win7时代的最高版本，已知的新增函数有11个但只有6个使用范围相对大众,ifs,switch,maxifs，minifs，concat，textjoin

(注意这6个函数最早出现于2016版本,有段时间2016是可以使用的（如果你处在部分版本号且一直没进行过更新,至今仍可使用这批函数）,但目前被改成只在365帐号或者2019才能使用)

8.jpg (108.17 KB, 下载次数: 90)

下载附件

保存到相册

2019-9-24 22:14 上传

其中前4个函数功能相对鸡肋，ifs和switch对函数有一定了解的完全可以用if套嵌,lookup,vlookup,choose等替代实现

minifs和maxifs聊胜于无,虽然这2个函数的常规效果可以直接用aggregate替代而且这2个函数还是不能找对应第N大(/小)值

所以在我看来这个版本出现的实用函数只有concat和textjoin,这2个能合并文本型内存数组的函数填充了函数长期以来的空白领域

9.jpg (51.67 KB, 下载次数: 81)

下载附件

保存到相册

2019-9-24 22:14 上传

回复

举报

流浪铁匠

流浪铁匠

当前离线

性别保密

UID3110404

阅读权限50

注册时间2015-6-3

在线时间 小时

最后登录1970-1-1

积分1136

财富

分享

技术

精华

TA的精华主题

TA的得分主题

6楼

楼主|

发表于 2019-9-24 22:12

|

只看该作者

[广告] ★会Excel,就能做支持PC、手机、Web、App、Windows、安卓、IOS、Linux信息一体化的各种管理系统 ★

本帖最后由 流浪铁匠 于 2019-9-25 17:13 编辑

15.jpg (82.76 KB, 下载次数: 106)

下载附件

保存到相册

2019-9-24 23:40 上传

2019(365)这个版本出现了函数这么多年以来的一个重大变革,动态数组性质,这个性质造成了很多之前需要数组三键才能录入的函数现在可以直接enter完成

而且结果会根据结果的个数自动扩展结果区域

以filter为例,这个函数的出现将早期版本的经典套路万金油index+small+if+row成为历史

最近刚出的xlookup也对vlookup的性能和参数进行了优化，方便新手使用

由于我目前使用版本还是2016,未测试过这部分函数,而且作为新函数这几个函数在论坛已经有几个专门帖了

所以这里只附上相关链接

早期的6个

http://club.excelhome.net/thread-1495564-1-1.html

http://club.excelhome.net/thread-1441388-1-1.html

官网 动态数组 https://support.office.com/zh-cn ... 1-89a1-87a7eb36e531

官网,xlookup https://support.office.com/zh-cn ... 6-84F9-88EAE8BF5929

官网,xmatch https://support.office.com/zh-cn ... 3-A1C6-5A33ED6A0312

早期版本函数在官网介绍会有不少问题,新版本函数在官网基本都能找到第一手资料

然而目前还有部分极其实用的函数还未出现,例如按分隔符拆分的split,颠倒的reverse,从右往左找的xfind等等(最后1个是我瞎编的)

回复

举报

流浪铁匠

流浪铁匠

当前离线

性别保密

UID3110404

阅读权限50

注册时间2015-6-3

在线时间 小时

最后登录1970-1-1

积分1136

财富

分享

技术

精华

TA的精华主题

TA的得分主题

7楼

楼主|

发表于 2019-9-24 22:13

|

只看该作者

[广告] ★用Excel做企业管理系统，Excel服务器学习和下载★

以上即截至目前的最近5个版本的新增函数整理和简介

目前列表里整理出的函数共491个,这只能算是大致数量

因为例如widechar,截至目前,官网也未出现这个函数的相关介绍,这个功能的函数在官网的介绍里是jis和dbcs 2个函数,原因在于,测试下来这是个会根据语言环境改名的函数……

(中文简体widechar,英文dbcs,日文jis，朝鲜文junja’，中文繁体big5)

rmb和dollar这2个函数测试下来也具有类似性质

(中文rmb，英文dollar，日文yen，朝鲜文won)

(中文dollar，英文usdollar)

另外在中文版len和lenb这类函数性质是不一样的,被归纳为2个函数

ceiling相关衍生函数的性质证明了微软会隐藏早期版本不完善的函数被后续新函数替代

全部函数列表请在附件查看(有一批疑似泰历相关的中文版隐藏函数未列入里面因为中文版貌似无法正常返回结果)

将近500个函数,以官网函数列表为框架重新整理了一次，看着很多函数功能的不断完善优化，再回首，不胜感慨

想到了wow的一句话

“总有一天vlookup的使命将抵达终点,而xlookup,将加冕为王”

评分

4

查看全部评分

1007890405

664349687

swyrlbq

满坛皆为吾师

回复

举报

micch

micch

当前离线

性别保密

UID1405992

阅读权限95

注册时间2011-5-19

在线时间 小时

最后登录1970-1-1

积分18846

财富

分享

技术

精华

TA的精华主题

TA的得分主题

8楼

发表于 2019-9-24 22:22

|

只看该作者

[广告] ★用Excel做企业管理系统，Excel服务器学习和下载★

想想2003版的普及性，新函数取代旧函数就任重道远，毕竟文件是要交换使用的，总不能自己电脑上自娱自乐

回复

举报

solextrade

solextrade

当前离线

性别保密

UID134342

阅读权限95

注册时间2006-2-3

在线时间 小时

最后登录1970-1-1

积分15785

财富

分享

技术

精华

TA的精华主题

TA的得分主题

9楼

发表于 2019-9-24 22:48

|

只看该作者

[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★免费下载 ★ ★ 使用帮助★

流浪铁匠 发表于 2019-9-24 22:11

2016，win7时代的最高版本，已知的新增函数有11个但只有6个使用范围相对大众,ifs,switch,maxifs，minifs，c ...

铁大师的分享帖发出来，不会是我的小帖催化了铁大师的帖子吧，嘿嘿...

回复

举报

绿谷龙芽

绿谷龙芽

当前离线

性别保密

UID3570342

阅读权限30

注册时间2016-11-2

在线时间 小时

最后登录1970-1-1

积分519

财富

分享

技术

精华

TA的精华主题

TA的得分主题

10楼

发表于 2019-9-25 00:24

|

只看该作者

[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★免费下载 ★ ★ 使用帮助★

先收藏，再慢慢折腾

回复

举报