动态数组公式

SpreadJS为在表单中使用动态数组公式提供了广泛的支持。

使用场景

动态数组公式通过自动溢出并扩展到单元格范围，将多个结果作为输出返回。这使得在表单中使用数组公式变得更加轻松快捷。

动态数组公式特别有用：

用户希望在表单中实现数据缓存的有效利用。
用户希望构建对缓存友好的数据结构，以减少内存占用量（紧凑性）来促进随机访问。通常，这是可能的，因为动态数组只有很小的固定附加开销来存储有关大小和容量的信息。

UNIQUE函数

Unique函数返回一个单元格区域中所有唯一值的列表。

例如-单元格C4包含公式“ = UNIQUE（A4：A15）”，并且仅返回单元格范围A4到A15中的值的唯一客户名称。根据唯一值的数量，动态数组公式将自动溢出到单元格范围C5至C8，如下所示：

FILTER函数

FILTER功能允许用户根据定义的标准筛选单元格区域。可以基于单个条件或多个条件执行筛选操作。为了合并两个或多个筛选条件，用户可以使用“ *”运算符。

例如-单元格F5包含公式“ = FILTER（A5：D17，C5：C17 = F1）”。该公式根据一个筛选条件对单元格范围A5至D17进行筛选（当单元格范围C5至C17与单元格F1中的乘积值匹配，即Apple）。结果，将显示包含产品“ Apple”的A5到D17单元格区域中的所有值。

上图中的单元格F14包含公式“ = FILTER（A5：D17，（C5：C17 = F1）（A5：A17 = F2））”。该公式根据乘法（）运算符指定的两个筛选条件筛选单元格范围A5至D17。第一个条件是单元格范围C5至C17应匹配单元格F1中的乘积值，即Apple；第二个条件是单元格范围A5至A17应匹配区域“东部”。结果，将显示包含产品为“ Apple”和区域为“ East”的单元格区域A5至D17中的所有值。

RANDARRAY 函数

RANDARRAY函数返回一个随机数值数组。用户可以指定行和列的数量，最小值和最大值，并指定是返回整数还是十进制值。

例如，下图中的单元格A8包含公式“ = RANDARRAY（5,3）”，并返回介于0和1之间的一组随机值。

SORT 函数

SORT函数对单元格区域或数组中的数据进行排序。该函数的结果通过以升序（递增）或降序（递减）排列的动态值数组溢出到结果范围内。如果未指定排序顺序，则默认情况下，值将按升序按字母顺序排序。

例如-单元格D4包含公式“ = SORT（A4：A15）”，并返回按升序排序的客户名称。

如果要对A4到A15范围内的所有唯一值进行排序，则可以将排序功能应用于列C4中显示的唯一列表，也可以将函数SORT和UNIQUE合并为一个公式。

例如，单元格E4包含公式“ = SORT（C4＃）”，其中＃表示列表。此公式将对C列中的值列表进行排序（其中单元格C4已包含UNIQUE公式“ = UNIQUE（A4：A15）”），并将结果显示在E列中。

或者，您也可以将功能SORT和UNIQUE组合在一起。例如，下图中的单元格F4包含公式“ = SORT（UNIQUE（A4：A15））”，该公式返回范围A4：A15中按字母顺序排序的所有唯一值。

SORT BY 函数

SORTBY函数根据相应范围或数组中存在的值对单元格范围或数组的内容进行排序。

例如-单元格G4包含公式“ = SORTBY（A4：B15，B4：B15）”。此函数根据另一个单元格范围B4到B15对单元格范围A4到B15进行排序，并返回显示的客户名称以及按升序排序的年龄。

SEQUENCE 函数

SEQUENCE函数以升序返回数组中序列号的列表。

例如-单元格A2包含公式“ = SEQUENCE（4,5）”，并返回一个数组，其值溢出到包含四行五列的单元格范围内，显示的数字范围为1、2、3、4，最高为20。

SINGLE 函数

SINGLE函数使用隐式交叉逻辑返回单个值，单个单元格区域或错误。

例如-单元格A15包含公式“ = SINGLE（A15：E15）”，并通过评估单元格范围A15至E15中的行和列的交集，在单元格C16中返回结果“ C”。

溢出数组公式

在表单中使用通用公式是一项繁琐的任务，因为用户需要手动将公式复制到要计算结果的每个单元格中。当一个单元格包含一个动态数组公式时，该数组的元素会溢出到相邻的空单元格中，并返回多个值。与通用数组不同，动态数组会在将数据插入源范围或从源范围删除数据时自动调整大小。

Spilling - 当所有公式都返回多个值（在数组中）时，将使用结果（计算数据）填充相邻的单元格。这种行为称为溢出。任何具有可能返回多个结果的公式都可以称为动态数组公式。

Spilled Array Formulas - 在表单的单元格范围内返回多个结果并成功溢出的公式称为溢出数组公式。

Spill Range Operator - 溢出范围运算符（＃）可用于引用整个溢出范围。公式返回多个结果时，将返回#Spill错误。

启用动态数组函数

为了在表单中使用动态数组功能，用户首先需要启用动态数组跟随代码段。

// 启用动态数组
spread.options.allowDynamicArray = true;

代码

下面的示例代码演示了如何在表单中使用动态数组函数。

// 初始化 Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 4 });

// 获取表单
var activeSheet = spread.getActiveSheet();

// 启用动态数组
spread.options.allowDynamicArray = true;
activeSheet.FrozenRowCount = 1;
activeSheet.setText(0, 0, "Dynamic Array Functions");
activeSheet.getCell(0, 0).backColor("LightGray");
activeSheet.addSpan(0, 0, 1, 3, GC.Spread.Sheets.SheetArea.viewport);
// 在表单的单元格中设置Sheet[0]
activeSheet.setText(2, 0, "Customer's Name");
activeSheet.getCell(2, 0).backColor("LightGray");
activeSheet.setText(3, 0, "Larry");
activeSheet.setText(4, 0, "Safeway");
activeSheet.setText(5, 0, "Safeway");
activeSheet.setText(6, 0, "Raley");
activeSheet.setText(7, 0, "Vallarta");
activeSheet.setText(8, 0, "Safeway");
activeSheet.setText(9, 0, "Raley");
activeSheet.setText(10, 0, "Larry");
activeSheet.setText(11, 0, "Gilbert");
activeSheet.setText(12, 0, "Larry");
activeSheet.setText(13, 0, "Larry");
activeSheet.setText(14, 0, "Raley");
activeSheet.setColumnWidth(0, 120.0, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setText(2, 1, "Age");
activeSheet.getCell(2, 1).backColor("LightGray");
activeSheet.setText(3, 1, "32");
activeSheet.setText(4, 1, "23");
activeSheet.setText(5, 1, "23");
activeSheet.setText(6, 1, "39");
activeSheet.setText(7, 1, "18");
activeSheet.setText(8, 1, "23");
activeSheet.setText(9, 1, "39");
activeSheet.setText(10, 1, "32");
activeSheet.setText(11, 1, "19");
activeSheet.setText(12, 1, "32");
activeSheet.setText(13, 1, "32");
activeSheet.setText(14, 1, "39");
activeSheet.setColumnWidth(1, 50.0, GC.Spread.Sheets.SheetArea.viewport);
//设置 "Unique" 公式
activeSheet.setText(2, 2, "Unique List");
activeSheet.getCell(2, 2).backColor("LightBlue");
activeSheet.setFormula(3, 2, "UNIQUE(A4:A15)");
activeSheet.setColumnWidth(2, 90.0, GC.Spread.Sheets.SheetArea.viewport);
// 设置 "Sort" 公式
activeSheet.setText(2, 3, "Sort");
activeSheet.getCell(2, 3).backColor("LightBlue");
activeSheet.setFormula(3, 3, "SORT(A4:A15)");
activeSheet.setColumnWidth(3, 90.0, GC.Spread.Sheets.SheetArea.viewport);
// 设置 "Sort" 公式给列表
activeSheet.setText(2, 4, "Sort Unique");
activeSheet.getCell(2, 4).backColor("LightBlue");
activeSheet.setFormula(3, 4, "SORT(C4#)");
activeSheet.setColumnWidth(4, 90.0, GC.Spread.Sheets.SheetArea.viewport);
//设置 "Sort+Unique" 公式
activeSheet.setText(2, 5, "Sort Unique");
activeSheet.getCell(2, 5).backColor("LightBlue");
activeSheet.setFormula(3, 5, "SORT(UNIQUE(A4:A15)");
activeSheet.setColumnWidth(5, 90.0, GC.Spread.Sheets.SheetArea.viewport);
// 设置“ SortBy”公式，其中我们根据相应范围B4：B15中的值对范围A4：B15进行排序
activeSheet.setText(2, 6, "SortBy");
activeSheet.getCell(2, 6).backColor("LightBlue");
activeSheet.setFormula(3, 6, "SORTBY(A4:B15, B4:B15)");
activeSheet.setColumnWidth(6, 90.0, GC.Spread.Sheets.SheetArea.viewport);
//得到第二个表单
var activeSheet = spread.getSheet(1);
// 在表单的单元格中设置Sheet[1]
for (var i = 0; i < 9; i++)
 activeSheet.setColumnWidth(i, 70.0, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setText(3, 0, "Region");
activeSheet.getCell(3, 0).backColor("LightGray");
activeSheet.setText(4, 0, "East");
activeSheet.setText(5, 0, "North");
activeSheet.setText(6, 0, "Wast");
activeSheet.setText(7, 0, "Sast");
activeSheet.setText(8, 0, "East");
activeSheet.setText(9, 0, "East");
activeSheet.setText(10, 0, "West");
activeSheet.setText(11, 0, "South");
activeSheet.setText(12, 0, "North");
activeSheet.setText(13, 0, "North");
activeSheet.setText(14, 0, "East");
activeSheet.setText(15, 0, "South");
activeSheet.setText(16, 0, "West");
activeSheet.setText(3, 1, "Sales Rep");
activeSheet.getCell(3, 1).backColor("LightGray");
activeSheet.setText(4, 1, "Tom");
activeSheet.setText(5, 1, "Fred");
activeSheet.setText(6, 1, "Amy");
activeSheet.setText(7, 1, "Sal");
activeSheet.setText(8, 1, "Hector");
activeSheet.setText(9, 1, "Xi");
activeSheet.setText(10, 1, "Amy");
activeSheet.setText(11, 1, "Sal");
activeSheet.setText(12, 1, "Fred");
activeSheet.setText(13, 1, "Tom");
activeSheet.setText(14, 1, "Hector");
activeSheet.setText(15, 1, "Sravan");
activeSheet.setText(16, 1, "Xi");
activeSheet.setText(3, 2, "Product");
activeSheet.getCell(3, 2).backColor("LightGray");
activeSheet.setText(4, 2, "Apple");
activeSheet.setText(5, 2, "Grape");
activeSheet.setText(6, 2, "Pear");
activeSheet.setText(7, 2, "Banana");
activeSheet.setText(8, 2, "Apple");
activeSheet.setText(9, 2, "Banana");
activeSheet.setText(10, 2, "Banana");
activeSheet.setText(11, 2, "Pear");
activeSheet.setText(12, 2, "Apple");
activeSheet.setText(13, 2, "Grape");
activeSheet.setText(14, 2, "Grape");
activeSheet.setText(15, 2, "Apple");
activeSheet.setText(16, 2, "Grape");
activeSheet.setText(3, 3, "Units");
activeSheet.getCell(3, 3).backColor("LightGray");
activeSheet.setText(4, 3, "6380");
activeSheet.setText(5, 3, "2344");
activeSheet.setText(6, 3, "3434");
activeSheet.setText(7, 3, "5461");
activeSheet.setText(8, 3, "2341");
activeSheet.setText(9, 3, "3234");
activeSheet.setText(10, 3, "6532");
activeSheet.setText(11, 3, "7323");
activeSheet.setText(12, 3, "2334");
activeSheet.setText(13, 3, "8734");
activeSheet.setText(14, 3, "1932");
activeSheet.setText(15, 3, "7682");
activeSheet.setText(16, 3, "3293");
activeSheet.setText(0, 4, "Product:");
activeSheet.getCell(0, 4).backColor("LightGray");
activeSheet.setText(0, 5, "Apple");
activeSheet.setText(1, 4, "Region:");
activeSheet.getCell(1, 4).backColor("LightGray");
activeSheet.setText(1, 5, "East");
activeSheet.setText(2, 5, "Filtering performed on one Criteria");
activeSheet.getCell(2, 5).backColor("LightBlue");
activeSheet.addSpan(2, 5, 1, 4, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setText(3, 5, "Region");
activeSheet.getCell(3, 5).backColor("LightGray");
activeSheet.setText(3, 6, "Sales Rep");
activeSheet.getCell(3, 6).backColor("LightGray");
activeSheet.setText(3, 7, "Product");
activeSheet.getCell(3, 7).backColor("LightGray");
activeSheet.setText(3, 8, "Units");
activeSheet.getCell(3, 8).backColor("LightGray");
// 设置“Filter”公式（有一个条件），其中我们筛选范围A5：D21
// 基于条件，其中区域C5：C21等于单元格F1中的值
activeSheet.setFormula(4, 5, "FILTER(A5:D21, C5:C21=F1)", GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setText(12, 5, "Region");
activeSheet.getCell(12, 5).backColor("LightGray");
activeSheet.setText(12, 6, "Sales Rep");
activeSheet.getCell(12, 6).backColor("LightGray");
activeSheet.setText(12, 7, "Product");
activeSheet.getCell(12, 7).backColor("LightGray");
activeSheet.setText(12, 8, "Units");
activeSheet.getCell(12, 8).backColor("LightGray");
activeSheet.setText(11, 5, "Filtering performed on two Criteria");
activeSheet.getCell(11, 5).backColor("LightBlue");
activeSheet.addSpan(11, 5, 1, 4, GC.Spread.Sheets.SheetArea.viewport);
// Setting "Filter" Formula( with two conditions) wherein we filter range A5:D21
// 基于条件，其中区域C5：C21等于单元格F1中的值
// 并且区域A5：A21等于单元格F2中的值
activeSheet.setFormula(13, 5, "FILTER(A5:D21, (C5:C21=F1)*(A5:A21=F2))", GC.Spread.Sheets.SheetArea.viewport);
// 得到第二张表单
var activeSheet = spread.getSheet(2);
for (var i = 0; i < 7; i++)
activeSheet.setColumnWidth(i, 130.0, GC.Spread.Sheets.SheetArea.viewport);
// 设置“序列” FormulaactiveSheet.Columns [0，7] .Width = 130;
activeSheet.setText(0, 0, "SEQUENCE(4,5) Function");
activeSheet.addSpan(0, 0, 1, 2, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.getCell(0, 0).backColor("SkyBlue");
activeSheet.setFormula(1, 0, "SEQUENCE(4,5)", GC.Spread.Sheets.SheetArea.viewport);
// 设置“ RandArray”公式
activeSheet.setText(6, 0, "RANDARRAY(5,3) Function");
activeSheet.addSpan(6, 0, 1, 2, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.getCell(6, 0).backColor("SkyBlue");
activeSheet.setFormula(7, 0, "RANDARRAY(5,3)", GC.Spread.Sheets.SheetArea.viewport);
// 设置 "Single"公式
activeSheet.setText(13, 0, "SINGLE Function is represented by @ i.e. =@(A15:E15)");
activeSheet.addSpan(13, 0, 1, 4, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.getCell(13, 0).backColor("SkyBlue");
activeSheet.setValue(14, 0, "A");
activeSheet.setValue(14, 1, "B");
activeSheet.setValue(14, 2, "C");
activeSheet.setValue(14, 3, "D");
activeSheet.setValue(14, 4, "E");
activeSheet.setFormula(15, 2, "@(A15:E15)", GC.Spread.Sheets.SheetArea.viewport);