动态数组

动态数组是将多个值（在一个数组中）返回到工作表上某个单元格区域的公式。因此，相邻单元格将根据单个输入的公式填充结果（计算数据）。这种行为称为 Spilling（溢出）,公式结果填充的范围被称之为 Spill Range（溢出范围）。溢出范围的操作符（#）可用于引用整个溢出范围。

GcExcel 对于需要在工作表中使用动态数组公式的用户，可以通过 IRange.setFormula2 方法在工作表中定义动态数组公式。它允许用户在不自动添加隐式交集运算符的情况下指定公式。要启用动态数组公式的使用，需要通过 setFormula2 方法设置指定 IRange 区域的公式。

注意： 在 v5.0 版本中，setAllowDynamicArray 方法已过时。该方法目前仍可与 IRange 一起使用。setFormula 方法兼容 v4.2 版本。但是，我们建议使用新的 setFormula2 方法，因为将来可能会删除 setAllowDynamicArray 方法。

您还可以使用 CalcError 枚举来指定计算错误的类型：

Calc：当计算引擎遇到当前不支持的场景时发生。
Spill：当公式返回多个结果，但无法将这些值返回到相邻单元格时发生。

GcExcel 中添加了以下动态数组函数：

函数	类别	描述
FILTER	查找和引用	根据定义的条件筛选一系列数据。
RANDARRAY	数学与三角函数	返回介于 0 和 1 之间的随机数数组。
SEQUENCE	数学与三角函数	生成数组中的序列号列表，例如1、2、3、4。
SINGLE	查找和引用	返回单个值，逻辑等同于隐式交集运算符。
SORT	查找和引用	对范围或数组的内容进行排序。
SORTBY	查找和引用	根据相应范围或数组中的值对范围或数组的内容进行排序
UNIQUE	查找和引用	返回列表或范围中唯一值

请参阅以下示例代码，以通过指定条件启用动态数组公式并使用筛选函数。

//create a new workbook
Workbook workbook = new Workbook();

IWorksheet sheet = workbook.getWorksheets().get(0);
sheet.setName("FILTER");
sheet.getRange("A1").setValue("The FILTER function filters a range or array based on criteria you specify. Syntax: FILTER(array,include,[if_empty])");

sheet.getRange("B3:E19").setValue(new Object[][] { 
    { "Region", "Sales Rep", "Product", "Units" },
    { "East", "Tom", "Apple", 6380 }, 
    { "West", "Fred", "Grape", 5619 }, 
    { "North ", "Amy", "Pear", 4565 },
    { "South", "Sal", "Banana", 5323 }, 
    { "East", "Fritz", "Apple", 4394 },
    { "West", "Sravan", "Grape", 7195 }, 
    { "North ", "Xi", "Pear", 5231 },
    { "South", "Hector", "Banana", 2427 }, 
    { "East", "Tom", "Banana", 4213 },
    { "West", "Fred", "Pear", 3239 }, 
    { "North ", "Amy", "Grape", 6420 }, 
    { "South", "Sal", "Apple", 1310 },
    { "East", "Fritz", "Banana", 6274 }, 
    { "West", "Sravan", "Pear", 4894 },
    { "North ", "Xi", "Grape", 7580 }, 
    { "South", "Hector", "Apple", 9814 } 
});

sheet.getRange("G3:L4").setValue(new Object[][] { { "Criterion", "", "Product", "Units", "", "Total:" }, { 5000, null, null, null, null, null } });

sheet.getRange("I4").setFormula2("=FILTER(D4:E19,E4:E19>G4,\"\")");
sheet.getRange("L4").setFormula2("=SUM(IF(E4:E19>G4,1,0))");

sheet.getRange("E4:E19,G4,J4:J12").setNumberFormat("#,##0");
        
//save to an excel file
workbook.save("FilterFunction.xlsx");

下图显示了上述代码的输出，其中 Filter 方法设置于单元格I4。

Dynamic Array formula Filter Function