office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(1)

前面已经给大家讲解了Office365的部分新函数,如Xlookup、Unique、Filter、Sort等,解决了查询、提取、以及排序的难题,在实际的应用中具有很多的价值。今天,我们继续学习Office365的新增函数:Xmatch、RANDarray、SEQUENCE。


一、Xmatch函数。

功能:返回项目在数组中的相对位置,默认情况下,需要精准匹配。

语法结构:=Xmatch(定位置,定位置所在的范围,[匹配模式],[搜索模式])。

解读:

1、Xmatch函数功能有4个参数,其中前两个参数时必备的。从字面意思或者Match的应用经验上就可以看出其用法。

2、第3个参数“匹配模式”共有4个值,分别为0、1、-1、2。其中0为精准匹配,如果未找到合适的值,则返回#N/A;-1为精准匹配或返回下一个较小的值;1为精准匹配或返回下一个较大的值;2为通配符匹配,主要应用在文本数据匹配场合,“?”和“*”分别表示匹配一个或多个字符。

3、第4个参数“搜索模式”共有4个值,分别为1、-1、2、-2。其中1表示从上到下、从左到右搜索;-1表示从下到上、从右到左逆向搜索;2表示二进制按升序搜索;-2表示二进制按降序搜索。

应用案例:

1、提取指定值的相对位置。

目的:提取员工在指定数据表中从上到下的相对位置。

office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(2)

方法:

在目标单元格中输入公式:=XMATCH(J3,B3:B12,0,1)。


2、提取指定值的相对位置。

目的:提取员工在指定数据表中从下到上的相对位置。

office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(3)

方法:

在目标单元格中输入公式:=XMATCH(J3,B3:B12,0,-1)。

解读:

1、从下到上的搜索在Match函数中是无法实现的,这也是Xmatch函数更为先进的体现。

2、当Xmatch函数的第4个参数为-1时,搜索“从下到上、从右到左”,但位置的返回值依然是按照数据范围从上到下、从左到右依次定位。请大家注意区分上图中“鲁肃”位置的变化。


3、提取指定的值。

目的:按照不同的搜索方向提取“鲁肃”的“月薪”。

office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(4)

方法:

在目标单元格中输入公式:=INDEX(G3:G12,XMATCH(J3,B3:B12,0,1))或=INDEX(G3:G12,XMATCH(J3,B3:B12,0,-1))。

解读:

1、示例中,首先按“从上到下”的搜索模式定位“鲁肃”的相对位置,并提取“月薪”;其次按“从下到上”的搜索模式定位“鲁肃”的相对位置,位置由原来的1变为了10,然后提取对应位置上的“月薪”。

2、此功能用Index Match是无法实现的,需要借助于其它条件。


二、Randarray函数。

功能:返回随机数组。可以指定要返回的行数和列数,最小值和最大值,以及是否返回整数或小数值。

语法结构:=Randarray([行数],[列数],[最小值],[最大值],[整数或十进制值])。

解读:

1、从上述的语法结构中可以看出,Randarray的参数可以全部省略,当省略全部参数时,其功能和Rand函数没有差别。

2、如果不指定“行数”或“列数”,Randarray函数将返回0-1之间的单个值。

3、如果不指定“最小值”和“最大值”,Randarray函数分别用0和1默认表示。

4、参数“最小值”必须小于“最大值”,否则返回错误代码#VALUE!。

5、参数“整数或十进制”功能有2个值,分别为TRUE和FALSE;“TRUE”表示“整数”,“FALSE”或省略表示十进制值。

应用案例:

1、返回10行,12列的随机值。

office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(5)

方法:

在目标单元格中输入公式:=RANDARRAY(10,12)。


2、返回10行、12列,最小值为1,最大值为100的值。

office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(6)

方法:

在目标单元格中输入公式:=RANDARRAY(10,12,1,100)。


3、返回10行、12列,最小值为1,最大值为100的整数值。

office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(7)

方法:

在目标单元格中输入公式:=RANDARRAY(10,12,1,100,1)。

解读:

Randarray函数的第5个参数制定了Randarray的返回值的类型。TRUE为整数值,FALSE或省略为十进制值。


三、Sequence函数。

功能:返回一个等差序列的数字列表。

语法结构:=Sequence(行,[列],[开始值],[等差步长])。

解读:

1、第1个参数“行”为等差序列数字列表的行数,必填。

2、第2个参数“列”为等差序列数字列表的列数,选填,默认值为1。

3、第3个参数“开始值”为等差序列的第一个数字,默认值为1。

4、第4个参数“等差步长”为数组中两个连续值递增的量,默认值为1。

应用案例:

1、快速生成1-10的序号。

office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(8)

方法:

在数据区域的目标单元格中输入公式:=SEQUENCE(10)。


2、快速返回10行、12列的序列。

office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(9)

方法:

在目标单元格中输入公式:=SEQUENCE(10,12)。

解读:

在省略参数“开始值”和“步长”的情况下,默认值都为1。


3、快速返回10行,12列,开始值为100的序列。

office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(10)

方法:

在目标单元格中输入公式:=SEQUENCE(10,12,100)。


4、快速返回10行,12列,开始值为100,步长为5的序列。

office365为什么没有dateif函数(Office365新增函数XmatchRandarraySequence应用技巧案例解读)(11)

方法:

在目标单元格中输入公式:=SEQUENCE(10,12,100,5)。


,