GcExcel V4.2 新特性解析 - 动态数组公式
本帖最后由 Clark.Pan 于 2021-10-5 18:37 编辑动态数组（Dynamic arrays）是微软在2018年的全球技术大会上提出的新技术。在Office 2019中，Excel支持新增了7个动态数组公式：FILTER，UNIQUE，SORT，SORTBY，SEQUENCE，SINGLE和RANDARRAY。而在最新的GcExcel V4.2功能中，也会上述动态数组公式进行了支持。
那么什么是动态数组公式。首先，它还是一个公式，但是跟普通公式的区别在于公式计算的结果可能作用于多个单元格，类似于数组公式的效果。接下来动态这个关键字表明，当您的数据发生变化时，该公式将自动调整大小并重新计算。所以公式本身覆盖的区域也是动态的，并非某个固定的单元格或区域。
举个例子：动态数组公式中常用的FILTER函数，用于在一片区域中筛选出符合条件的内容并依次排列在单元格中。但符合条件的内容可能是0个或多个，所以该公式返回的结果是一个动态的区域。
明白了什么是动态数组公式，接下来让我们看一下目前新增的几个动态数组公式各自的功能是什么。
FILTER：
FILTER 函数可以基于定义的条件筛选一系列数据。表达式为 FILTER(array,include,)。
其中，array为筛选数据的范围。
include 是筛选条件，比如C5:C20="苹果"意思就是返回筛选范围中C5:C20为“苹果”的所有行。另外，include可以为多个参数，例如：(C5:C20=H1)*(A5:A20=H2) 意思就是返回筛选范围中C5:C20为H1且A5:A20为H2的所有行。
if_empty （可选）表示为空时的返回值
UNIQUE：
UNIQUE 函数返回列表或范围中的一系列唯一值。表达式为 UNIQUE (array,,)。
其中，array表示指定范围。
by_col （可选）参数是一个逻辑值，指示如何进行比较。TRUE 将比较列彼此并返回唯一列，FALSE (或省略) 将行彼此比较并返回唯一行。
exactly_once （可选）参数是一个逻辑值，它将返回在区域或数组中恰好出现一次的行或列。 这是唯一的数据库概念。TRUE 将返回范围或数组中恰好发生一次的所有非重复行或列，即唯一出现的内容。FALSE (或省略) 将返回区域或数组中所有不同的行或列，即去重。
SORT：
SORT 函数可对某个区域或数组的内容进行排序。 表达式为 SORT(array,,,)
其中，array 表示要排序的区域或数组。
sort_index （可选）表示要按其排序的索引，默认为1。
sort_order（可选） 表示所需的排序顺序；1 表示升序（默认值），-1 表示降序。
by_col (可选) 指示所需的排序方向；False 表示按行排序（默认值），True 表示按列排序
SORTBY：
SORTBY 函数基于相应范围或数组中的值对范围或数组的内容进行排序。表达式为 SORTBY (array、by_array1、、,...)
其中，array 表示要排序的区域或数组。
by_array1，要对其进行排序的维度或区域
sort_order1，要用于排序的顺序。 1 表示升序，-1 表示降序。 默认值为升序。
by_array2，下一个要对其进行排序的维度或区域
sort_order2，第二个要用于排序的顺序。 1 表示升序，-1 表示降序。 默认值为升序。
这里by_array与sort_order可以添加多组，功能以此类推。
SORTBY与SORY的区别在于SORY可以基于多个维度对表格进行排序并显示排序结果。
SEQUENCE：
SEQUENCE 函数可在数组中生成一系列连续数字，例如，1、2、3、4。表达式为 SEQUENCE(rows,,,)
其中，rows 表示要返回的行数。
columns （可选）表示要返回的列数，默认值为1。
start（可选）表示生成序列中的第一个数字，默认从1开始。
step（可选） 表示数组中每个连续值递增的值，默认自增为1。
SINGLE：
准确的说SINGLE不是一个函数的名称而是一个运算符“@”，即隐式交集运算符，用于通过隐式交集逻辑将多个值返回单个值。那么什么是隐式交集：
如果值为单个项，则返回该项。
如果值为区域，则返回与公式位于同一行或同一列中的单元格中的值。
如果值为数组，则选取左上方的值。
简单的说就是，原本返回一个动态数组的公式如果加上“@”这个运算符之后只会返回一个数值，这样动态数组的结果会被固定在当前设置的单元格中。
RANDARRAY：
RANDARRAY 函数返回一组随机数字。 可指定要填充的行数和列数，最小值和最大值，以及是否返回整数或小数值。
表达式为RANDARRAY(,,,,)
其中，rows （可选）表示要返回的行数。默认值为1
columns （可选）表示要返回的列数，默认值为1。
min（可选）表示返回的最小数值，默认为0。
max（可选） 表示返回的最大数值，默认为1。
whole_number（可选）返回整数或十进制值TRUE 表示整数，FALSE 表示十进制数，默认为FALSE。
上面介绍了新增动态数组函数的功能与用法，那么如何在GcExcel中设置数组函数呢？我们接着往下看：
首先，我们可以通过workbook.setAllowDynamicArray 允许启用或禁用工作表中的动态数组公式。当我们启用之后就可以使用动态数组公式了：
workbook.setAllowDynamicArray(true);接下来我们就可以通过setFormula方法给某个单元格设置一个动态数组公式，例如：
sheet.getRange("A2").setFormula("=RANDARRAY(10,5,0,100,TRUE)");
为了支持动态数组公式，GcExcel中同时新增了两种特殊错误提示：
#Spill! - 表示公式返回多个结果，但不能将这些值返回到相邻单元格（例如需要返回10个结果，但是后面连续的10个单元格设置的有其他值，就会出现该错误提示）。
#Calc! - 当计算引擎遇到当前不支持的场景时发生。
页:
[1]