Office365新功能Xmatch、Randarray、Sequence应用技巧案例解读
我已经给大家讲解了Office365的一些新功能,比如Xlookup、Unique、Filter、Sort等,它们解决了查询、提取、排序的问题,在实际应用中有很大的价值。今天我们继续学习Office365的新功能:Xmatch、Randarray、Sequence。
1.Xmatch功能。
功能:返回项目在数组中的相对位置。默认情况下,需要完全匹配。
语法结构:Xmatch(固定位置,固定位置范围,[匹配模式],[搜索模式])。
解释:
1、Xmatch函数有4个参数,其中前两个参数是必须的。它的用法从字面意思或者Match的应用体验就可以看出。
2、第三个参数“MatchingMode”共有4个值,分别是0、1、-1、2。其中0为精确匹配,如果没有找到合适的值,#N/A被退回;-1是完全匹配或者返回下一个较小的值;1是完全匹配或者返回下一个较大的值;2是通配符匹配,主要用在文本数据匹配场合,“?”“*”分别表示匹配一个或多个字符。
3、第四个参数“SearchMode”共有4个值,分别是1、-1、2、-2。其中,1表示从上到下、从左到右搜索;-1表示从下到上、从右到左反向搜索;2表示按升序二分查找;-2表示按降序二分查找。
应用:
1.提取指定值的相对位置。
目的:提取指定数据表中员工从上到下的相对位置。
方法:
在目标单元格中??输入公式:XMATCH(J3,B3:B12,0,1)。
2.提取指定值的相对位置。
目的:提取指定数据表中员工从下到上的相对位置。
方法:
在目标单元格中??输入公式:XMATCH(J3,B3:B12,0,-1)。
解释:
1、Match函数中无法实现从下到上的搜索,这也是Xmatch函数更高级的体现。
2.当Xmatch函数的第四个参数为-1时,搜索“从下到上,从右到左”,但位置的返回值仍然根据数据范围从上到下,从左定位向右。请注意区分上图中“鲁肃”位置的变化。
3.提取指定值。
目的:根据不同的搜索方向提取“鲁肃”的“月薪”。
方法:
在目标单元格中??输入公式:INDEX(G3:G12,XMATCH(J3,B3:B12,0,1))或INDEX(G3:G12,XMATCH(J3,B3:B12,0,-1))。
解释:
1、示例中,首先按照“从上到下”的搜索方式定位到“鲁肃”的相对位置,提取出“月薪”;其次,按照“自下而上”的搜索方式定位“鲁肃”的相对位置,将位置从1变为10,然后提取对应位置的“月薪”。
2、该功能无法用IndexMatch实现,需要其他条件。
二、Randarray函数。
功能:返回一个随机数组。您可以指定要返回的行数和列数、最小值和最大值以及是返回整数还是小数值。
语法结构:Randarray([行数],[列数],[最小值],[最大值],[整数或小数值])。
解释:
1、从上面的语法结构可以看出,Randarray的所有参数都可以省略。当省略所有参数时,其功能与Rand函数没有什么不同。
2.如果不指定“行数”或“列数”,Randarray函数将返回0-1之间的单个值。
3、如果不指定“最小值”和“最大值”,则Randarray函数默认用0和1表示。
4、参数“最小值”必须小于“最大值”,否则错误码#VALUE!将被退回。
5、参数“整数或小数”功能有2个值,分别是TRUE和FALSE;“TRUE”表示“整数”,“FALSE”或省略表示十进制值。
应用:
1.返回10行12列的随机值。
方法:
在目标单元格中??输入公式:RANDARRAY(10,12)。
2、返回10行12列,最小值为1,最大值为100。
方法:
在目标单元格中??输入公式:RANDARRAY(10,12,1,100)。
3、返回10行12列,最小值为1,最大值为100的整数值。
方法:
在目标单元格中??输入公式:RANDARRAY(10,12,1,100,1)。
解释:
Randarray函数的第五个参数指定Randarray的返回值的类型。TRUE表示整数值,FALSE或省略表示小数值。
三、序列功能。
功能:返回算术序列中的数字列表。
语法结构:序列(行,[列],[起始值],[等差步])。
解释:
1、第一个参数“line”是算术序号列表中的行数,为必填项。
2、第二个参数column为算术序号列表的列数,可选,默认值为1。
3、第三个参数“起始值”为等差数列的第一个数,默认值为1。
4、第4个参数“AdjustmentStep”是数组中连续两个值的增量,默认值为1。
应用:
1.快速生成1-10的序列号。
方法:
在数据区域的目标单元格中??输入公式:SEQUENCE(10)。
2.快速返回10行12列的序列。
方法:
在目标单元格中??输入公式:SEQUENCE(10,12)。
解释:
在省略参数“起始值”和“步长”的情况下,默认值为1。
3.快速返回一个10行12列、起始值为100的序列。
方法:
在目标单元格中??输入公式:SEQUENCE(10,12,100)。
4.快速返回10行12列的序列,起始值为100,步长为5。
方法:
在目标单元格中??输入公式:SEQUENCE(10,12,100,5)。