网站建设公司的方案模板,如何在word上做网站网址,2022年河北二建考试最新消息,在国外社交网站做产品推广本文由葡萄城技术团队发布。转载请注明出处#xff1a;葡萄城官网#xff0c;葡萄城为开发者提供专业的开发工具、解决方案和服务#xff0c;赋能开发者。 前言
动态数组公式是 Excel 引入的一项重要功能#xff0c;它将 Excel 分为两种风格#xff1a;Excel 365 和传统 … 本文由葡萄城技术团队发布。转载请注明出处葡萄城官网葡萄城为开发者提供专业的开发工具、解决方案和服务赋能开发者。 前言
动态数组公式是 Excel 引入的一项重要功能它将 Excel 分为两种风格Excel 365 和传统 Excel2019 或更早版本。动态数组功能允许用户从单个单元格中的公式返回多个结果值并将这些值自动填充到与公式单元格相邻的单元格中。由于结果会溢出到多个单元格中这也被称为溢出范围功能。在传统 Excel 中用户必须使用 Ctrl Shift Enter 来将公式识别为数组公式否则公式结果将仅返回单个值。因此动态数组为用户提供了更加便利的使用体验。现在我们将这样的返回多个值的公式称为动态数组公式。
今天小编就为大家介绍如何使用葡萄城公司的Java API 组件GrapeCity Documents for Excel以下简称GcExcel实现在Excel中添加动态数组。
动态数组公式
下表总结了GcExcel所有支持的公式及其语法、说明和代码示例
功能代码片段图像FILTER ****FILTER 公式根据您指定的条件筛选范围或数组。 FILTER(array,include,[if_empty])sheet.getRange(“I4”).setFormula2(“FILTER(D4:E12,E4:E12G4,\”\“)”);**RANDARRAY**RANDARRAY 公式允许您在数组中生成从 0 到 1 的随机 numbers 列表。 RANDARRAY ([rows],[columns])sheet.getRange(“$D$3”).setFormula2(“RANDARRAY(4,5)”);**SEQUENCE **SEQUENCE 公式允许您在数组中生成序列号列表。 SEQUENCE(rows,[columns],[start],[step])sheet.getRange(“$D$4”).setFormula2(“SEQUENCE(10,100,-10)”);**SORTBY **SORTBY 公式允许您根据相应范围或数组中的值对范围或数组进行排序。 SORTBY(array, by_array1, [sort_order1], [[by_array2], [sort_order2]],…)sheet.getRange(“$G$5”).setFormula2(“SORTBY($D$5:$E$12,$E$5:$E$12)”);**SORT**SORT 公式用于按升序或降序对范围或数组进行排序。 SORT(array, [sort_index], [sort_order], [by_col])sheet.getRange(“$I$5”).setFormula2(“SORT(D5: G13,4,1, FALSE)”);**UNIQUE**UNIQUE 公式允许您从项目范围或数组中返回唯一列表。UNIQUE(array, [by_col], occurs_once])sheet.getRange(“$G$4:$I$4”).setFormula2(“UNIQUE(B4: B12)”);
详细代码请点击这里。 运算符 运算符也称为隐式**交集运算符**实现一种称为隐式交集的公式行为该行为将一组值减少为单个值。这适用于返回多个值的数组公式在这种情况下将根据单元格位置即行和列返回单个值。在老版本 Excel 中这是默认行为因此不需要显式运算符。但是在 Excel 365 中所有公式都是数组公式因此如果您不希望数组公式溢出则可以在公式前面加上 运算符它只会返回一个值。
实现代码
public void ImplicitIntersection() {Workbook wb new Workbook();//初始化工作表IWorksheet sheet wb.getWorksheets().get(0);sheet.setName(IMPLICIT INTERSECTION);//添加样例数据sheet.getRange($D$3:$D12).setValue(new Object[]{Products, Apple, Grape, Pear, Banana,Apple, Grape, Pear, Banana, Banana});sheet.getRange($E$3).setValue(Unique Products);//添加含有隐式运算符的动态数组公式sheet.getRange($E$4).setFormula2(UNIQUE(D4:D12));wb.save(output/ImplicitIntersection.xlsx);
}实现效果
溢出范围参考 #
动态数组公式将其多值结果溢出到的单元格范围称为**溢出范围。**每当单击溢出范围中的任何单元格时溢出范围都会以蓝色边框突出显示表示该范围中的所有值都是通过该范围左上角单元格中的公式计算得出的。
溢出范围参考运算符用于引用此溢出范围。要引用溢出范围请在溢出范围中左上角单元格的地址后放置一个主题标签或井号 #。
例如若要查找单元格 E4 中应用的 UNIQUE 公式提取了多少个唯一值请提供对 COUNTA 公式的溢出范围引用如下面的代码所示
public void SpillReference() {Workbook wb new Workbook();//初始化工作表IWorksheet sheet wb.getWorksheets().get(0);sheet.setName(SPILL REFERENCE);//添加样例数据sheet.getRange($D$3:$D12).setValue(new Object[]{Products, Apple, Grape, Pear, Banana,Apple, Grape, Pear, Banana, Banana});sheet.getRange($E$3).setValue(Unique Products);sheet.getRange(F3).setValue(Unique Products Count);//设置动态数组公式sheet.getRange($E$4).setFormula2(UNIQUE(D4:D12));//设置带有溢出范围引用的公式sheet.getRange($F$4).setFormula2(COUNTA(E4#));wb.save(output/SpillReference.xlsx);
}实现效果
现在我们已经了解了 GcExcel 如何在 Java 中为动态数组公式提供支持下面小编将用一个简单的例子展示动态数组公式的用途。
用例使用动态数组在 Excel 中创建交互式图表
考虑这样一个场景我们有一些表格数据想用柱状图来展示。通常情况下表格数据有很多行和列但在制作图表时我们需要按照某些条件来选择部分数据。为了提取所需数据我们需要根据一些条件来进行筛选这就是动态数组公式的应用场景。我们使用FILTER函数根据定义的条件来筛选表格数据一旦获取到筛选后的数据我们就可以用它来创建柱状图。
柱状图绘制来自预定义单元格范围的数据该范围绑定到图表系列。因此如果过滤后的行数发生变化例如用户更改了“Show”列中的值就会导致FILTER公式重新计算从而筛选后的数据单元格范围也会发生变化。但是图表系列始终引用相同的单元格范围因此任何新的筛选数据行如果不在系列单元格范围内则不会在图表上绘制。但是我们希望所有经过筛选的数据都能在图表上显示这就需要将简单静态图表转换为交互式图表以便刷新其绘图区域来绘制所有筛选数据的值。
因此请继续了解实现上述定义方案的详细步骤。
步骤 1加载数据文件
我们将首先在 GcExcel 工作簿中加载源数据该数据将用于绘制图表。
源数据文件下载源文件文章系统的附件中
将 Excel 文件加载到Workbook 中
//创建一个工作簿并打开数据文件
Workbook workbook new GrapeCity.Documents.Excel.Workbook();
workbook.Open(xlsx\\SalesReport_sourcedata.xlsx);加载效果
步骤 2使用动态数组公式提取图表数据
现在将开始提取相关数据以便在图表中进行绘制。我们将根据产品的销售额绘制图表。因此图表数据应包含来自产品列和金额列的数值。从这两列中提取特定数值的标准是基于展示列。我们将从展示列的值为1的每一行中提取产品和金额数值并在图表上绘制这些筛选后的数据。
使用FILTER函数从产品列中过滤数据然后使用SORTBY函数按照金额列的值进行降序排序。因此我们将通过连接FILTER和SORTBY这两个动态数组函数来创建一个动态数组公式。
将使用VLOOKUP函数提取金额列中的数值并将FILTER函数的溢出范围作为参数传递以便获取与每个筛选产品对应的金额值。
以下是相应的代码片段
IWorksheet worksheet workbook.getWorksheets().get(0);
worksheet.getRange(G3).setValue(Product);
worksheet.getRange(H3).setValue(Sales);
worksheet.getRange(G3).getFont().setBold(true);
worksheet.getRange(H3).getFont().setBold(true);
//使用动态数组公式来获取产品列的值
worksheet.getRange(G4).setFormula2(SORTBY(FILTER(B4:B13,E4:E131),FILTER(D4:D13,E4:E131),-1));
//引用溢出区域来获取金额列的值
worksheet.getRange(H4).setFormula2(VLOOKUP(G4#,B4:D13,3,FALSE));以下是对工作表的快速浏览其中包含 G 列和 H 列中经过筛选和排序的数据并突出显示了溢出范围
步骤 3使用筛选后的数据添加简单图表
上述步骤已生成要在图表中绘制的数据。在这里我们通过向工作表添加两个命名范围来添加一个简单的图表来展示销售额其中一个引用筛选出的数据中的“产品”列另一个引用“销售”列。然后这些命名区域将用于创建图表系列。代码如下所示
//添加数据引用
wb.getNames().add(Product, Sheet1!$G$4:$G$8);
wb.getNames().add(Sales, Sheet1!$H$4:$H$8);//添加图表
IShape chartShape sheet.getShapes().addChart(ChartType.ColumnClustered, 600, 170, 500, 300);
IChart chart chartShape.getChart();
//添加系列
ISeries series chart.getSeriesCollection().newSeries();
series.setFormula(SERIES(\Sales\, Product, Sales, 1));生成的图表如下所示:
步骤 4使图表具有交互性
让我们首先了解使用此图表具有交互性的需求。观察下面的 GIF会发现更改“Show”列中的值会重新计算动态数组公式并更新过滤后的数据单元格范围。但是仅当新筛选的数据位于单元格区域 G3H8即系列单元格区域时此更改才会在图表中可见。如果过滤后的数据不在单元格范围 G3H8 中则它不会显示在图表中如本示例中过滤后的数据范围扩展到 G3H10但图表仅呈现来自 G3H8 的数据
此图表应绘制所有过滤掉的数据。为此我们必须使用溢出范围参考更新序列单元格范围这将确保序列单元格范围始终包含包含过滤数据的完整单元格范围。下面是示例代码片段该代码片段更新命名区域以使用溢出范围引用从而根据动态数组公式的结果使引用的单元格区域动态化。然后这些动态命名区域用于创建图表系列使图表具有交互性。
代码如下所示
//添加命名引用用于图表数据
wb.getNames().add(Product, Sheet1!$G$4#);
wb.getNames().add(Sales, Sheet1!$H$4#);//添加图表
IShape chartShape sheet.getShapes().addChart(ChartType.ColumnClustered, 600, 170, 500, 300);
IChart chart chartShape.getChart();//添加系列
ISeries series chart.getSeriesCollection().newSeries();
series.setFormula(SERIES(\Sales\, Product, Sales, 1));实现效果
总结
以上就是如何使用Java在Excel中添加动态数组公式的全过程如果您想要了解更多的资料可以点击下方链接查看
GcExcel Java 在线Demo | 动态数组的用例
GcExcel Java 在线Demo | 动态数组
动态数组 - GcExcel 中文文档Java版 | 服务端高性能表格组件 - 葡萄城
扩展链接
Redis从入门到实践 一节课带你搞懂数据库事务
Chrome开发者工具使用教程
从表单驱动到模型驱动解读低代码开发平台的发展趋势
低代码开发平台是什么
基于分支的版本管理帮助低代码从项目交付走向定制化产品开发
