Office365新增函数Xmatch、Randarray、Sequence应用技巧案例解读

前面已经给大家讲解了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、提取指定值的相对位置。

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

方法：

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

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

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

方法：

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

解读：

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

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

3、提取指定的值。

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

方法：

在目标单元格中输入公式：=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列的随机值。

方法：

在目标单元格中输入公式：=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函数的第5个参数制定了Randarray的返回值的类型。TRUE为整数值，FALSE或省略为十进制值。

三、Sequence函数。

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

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

解读：

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

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

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

4、第4个参数“等差步长”为数组中两个连续值递增的量，默认值为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)。