解锁你的应用程序潜力：用GcExcel轻松实现Excel的高级公式计算！
本帖最后由 Richard.Ma 于 2024-5-30 16:57 编辑您有没有想过让您的应用程序也能像 Excel 一样具备高级计算功能？
公式在数据分析中悄然发挥着重要作用。Microsoft Excel 已经提供了一系列的公式，可以简化数据分析过程中关键指标的提取，但是，如要要自己在应用程序中复制同样的功能则需要自定义大量的逻辑和投入时间。
想要在您的应用程序中轻松实现 Excel 公式和其他类似 Excel 的功能，您可以使用葡萄城推出的组件 GcExcel。GcExcel是一个基于 Excel 对象模型的综合 .NET 电子表格 API 库，不依赖于 Excel。它不仅允许您创建、编辑、加载和保存 Excel 文件，还允许您对数据执行各种计算，就像 Excel 一样。
在此博客中，就将介绍以下 Excel 公式并演示如何使用 GcExcel API来将这些公式应用到您的程序中：
[*]LOOKUP 和引用公式
[*]VLOOKUP/ HLOOKUP 公式
[*]LOOKUP 公式
[*]XLOOKUP 公式
[*]MATCH 公式
[*]FILTER 公式
[*]TRANSPOSE 公式
[*]
我们使用一个包含销售数据的JSON 来了解如何使用上述公式。
以下是数据在 Excel 电子表格中的呈现方式，经过一些额外的格式调整以提高美观性：
https://di87bzq19l2.feishu.cn/space/api/box/stream/download/asynccode/?code=ZGMxODhkODY1N2EzZTYwZDE2MmQ1NjVlMmM5OWY5MDZfUzhtWEtMd0tOQUk0M2NwUHhXYTJLTTMxcFIxellvNUtfVG9rZW46T1p2bmJtd1BKb1FzeFZ4NGZCVmNGSmdqbk5jXzE3MTcwNTI3MTM6MTcxNzA1NjMxM19WNA
LOOKUP 和引用公式
查找和引用公式可动态检索数据集中的值或索引。这些公式可处理数据数组并根据特定条件返回结果。
这些类别中的功能有助于快速分析数据并通过自动执行重复任务节省大量时间。
DsExcel 支持这些查找和引用功能，因此您也可以使您的应用程序功能强大。让我们通过示例了解其中的一些功能。
VLOOKUP/HLOOKUP 公式
VLOOKUP（垂直查找的缩写）在指定范围的最左边的列中搜索值，并从同一行的另一个指定列中检索该值。
例如，你可以使用 VLOOKUP 公式从给定的数据中查找销售特定产品 ID 的销售人员。
先来了解一下这个公式及其参数的语法。
=VLOOKUP(lookup_value, table_array,column_index_num,)
[*]lookup_value 第一个参数是在表格最左边的列中查找指定的值。在这个示例中，我们会寻找 K8 单元格中指定的特定产品 ID。
[*]table_array 此参数表示包含要搜索的源数据的目标表或单元格区域。在这里，我们将 B4作为目标区域，以产品 ID 列作为最左边的列。
[*]column_index_num 第三个参数表示目标列在表中的索引。因为我们在销售人员列 中搜索，所以这个参数的值应该是 4。
[*]range_lookup 这是一个可选参数，用于指定搜索类型。您可以将其设置为 TRUE 以进行近似匹配（默认），或将其设置为 FALSE 以进行精确匹配。在这里，我们将其设置为 FALSE。
使用这些参数值，最终的 VLOOKUP 公式如下所示：
=VLOOKUP(K8,B4:E13,4,FALSE)
在 GCExcel中，可以在 L8 单元格中设置此公式以检索销售人员的姓名：
JAVA:
//Apply formula to L8 cell of the worksheet
worksheet.getRange("L8").setFormula("=VLOOKUP(K8,B4:E13,4,FALSE)");
.NET
//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = "=VLOOKUP(K8,B4:E13,4,FALSE)";
https://di87bzq19l2.feishu.cn/space/api/box/stream/download/asynccode/?code=YTAxMWZiYzA0OWY2YzU2MjlhMDVkMDYyZDhlMTBhNTVfdlowRjlvRWhOY3FUVkdWdjBqNkNqbjRzcHlyV29PSGFfVG9rZW46SW12c2I4cU5BbzFXSjd4YkxXYWNReTlCbjNjXzE3MTcwNTI3MTM6MTcxNzA1NjMxM19WNA
如上面提到的，VLOOKUP 只能从最左边的列垂直搜索数据。对于水平数据，您可以使用 HLOOKUP（水平查找的缩写）公式在第一行中搜索数据。但是，这两个公式仅适用于按升序排序的查找数据。
LOOKUP 函数
LOOKUP函数可以在数据中执行垂直和水平搜索。
在垂直搜索中，此函数在特定列中查找值，并从返回同一行里另一个指定列的值。在水平搜索中，则是在特定行中查找值，并从返回同一列中另一行的值。
与需要搜索整个范围的 VLOOKUP 和 HLOOKUP 函数不同，LOOKUP 函数通过指定搜索并仅在列或行中显示结果来提供结果。这不仅可以提高性能，而且即使搜索列的位置发生变化，也可以提供正确的结果。
为了理解此功能，我们以搜索特定产品 ID 的销售人员姓名为例。
先了解一下 LOOKUP 函数的语法和参数：
=LOOKUP(lookup_value, lookup_array,result_array])
[*]lookup_value - 此参数是在表的特定列/行中查找指定的值。在这个的例子中，K8 单元格保存我们要寻找的产品 ID。
[*]lookup_array - 第二个参数表示包含要搜索的源数据的列/行。此处，B4:B13 是包含产品 ID 的列
[*]result_array - 第三个参数表示目标列/行，在本例中为销售人员列 (E4)。
最终我们生成的LOOKUP 函数如下所示：
=LOOKUP(K8,B4:B13,E4:E13)
下面的 GCExcel 代码显示如何使用 LOOKUP 函数找出销售人员的姓名：
JAVA
//Apply formula to L8 cell of the worksheet
worksheet.getRange("L8").setFormula("=LOOKUP(K8,B4:B13,E4:E13)");
.NET
//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = "=LOOKUP(K8,B4:B13,E4:E13)";
https://di87bzq19l2.feishu.cn/space/api/box/stream/download/asynccode/?code=YmJlZmM1ZGFmMzNkNDMzZjQyNGVmYTE4YjdlOGI4NTZfQjRGcmRMcHVuT3Npc1lnTHdxenhQT0dTSHNNbElGTGRfVG9rZW46SUZqdGJkbmxMbzRMRXF4ZHdUcGNxOE11bm9nXzE3MTcwNTI3MTM6MTcxNzA1NjMxM19WNA
与 VLOOKUP 和 HLOOKUP 函数一样，LOOKUP 仅当数据按升序排序时才提供正确的结果。但是，如果未找到匹配项，它不会处理这种情况。
XLOOKUP 函数
XLOOKUP 是 Excel 365 和 Excel 2019 中引入的最先进的查找函数。该函数消除了之前函数的所有限制。此外，它还具有一些高级匹配和搜索功能，例如搜索方向。
我们来扩展上一个示例，搜索销售人员以及他销售的特定产品 ID 的地区。此外，这次我们尝试反向搜索，从下到上。
先来看一个此函数的语法和参数：
=XLOOKUP(lookup_value,lookup_array,return_array,,,))
[*]lookup_value 与之前的查找函数类似，此参数也在表的特定列/行中查找指定的值，在本例中为 K8
[*]lookup_array 此参数表示要搜索 lookup_value 的列/行范围。产品 ID 位于 B4:B13 范围内
[*]return_array 第三个参数表示目标值的范围。D4:E13 是包含销售人员和销售地区列的范围
[*]not_found 如果未找到匹配项，此参数将返回指定值。我们将其设置为“未找到记录”
[*]match_mode 第五个参数指定匹配类型，其中 0 表示精确匹配，-1 表示精确匹配或下一个较小的项，1 表示精确匹配或下一个较大的项，2 表示通配符匹配。我们正在寻找精确值，因此将其设置为 0
[*]search_mode 最后一个参数定义搜索的方向，其中 1 表示从顶部执行搜索，-1 表示从底部执行搜索，2 表示当数据按升序排列时进行二分搜索，-2 表示当数据按降序排列时进行二分搜索。根据我们的用例，它设置为 -1
将所有这些参数放入XLOOKUP函数后，结果如下所示：
=XLOOKUP(K8,B4:B13, D4:E13,”Record Not Found”,0,-1)
以下 的GCExcel 代码演示如何使用 XLOOKUP 函数检索销售人员和地区名称：
JAVA
//Apply formula to L8 cell of the worksheet
worksheet.getRange("L8").setFormula2("=XLOOKUP(K8,B4:B13,D4:E13,\"Record Not Found\",0,-1)");
.NET
//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = "=XLOOKUP(K8,B4:B13,D4:E13,\"Record Not Found\",0,-1)";
https://di87bzq19l2.feishu.cn/space/api/box/stream/download/asynccode/?code=Mzg3ZTc2YzY3NGIyNGVjODFlODJlN2NlZTEyMWQwMGZfQldBZlUwRlNlT3lJQmo3U1lKSFRoVnlURU9TQ2o0bmZfVG9rZW46TkFYdmJ4U3Q5b3haMkJ4YlJDVWN3QkthblhlXzE3MTcwNTI3MTM6MTcxNzA1NjMxM19WNA
MATCH 函数
MATCH 函数在一定范围内搜索指定项并返回其索引。
使用此函数，您可以使其他函数动态化，即使数据结构发生变化，也能自动为您提供所需的结果。我们通过先前的 VLOOKUP 函数用例来理解这一点，其中 column_index_num 参数是一个硬编码值。如果您更改数据中销售人员列的位置，VLOOKUP 将不会返回正确的值。在这种情况下，MATCH 函数可以动态获取销售人员列的索引。
我们先看看 MATCH 函数的语法和参数。
=MATCH(lookup_value, lookup_array, )
[*]Lookup_value 第一个参数在范围内搜索指定值。我们将其设置为“销售人员”
[*]Lookup_array 此参数定义要搜索项目索引的列/行。在这里，我们在 B3:E3 范围内执行搜索
[*]Match_type 使用此参数，您可以指定搜索类型，其中 0 表示精确匹配，-1 表示查找值上方最接近的匹配，1 表示查找值下方最接近的匹配。我们将其设置为 0
输入所有参数后，将形成以下函数：
=MATCH("Sales Person",B3:E3,0)
在VLOOKUP函数中，只需将column_index_num参数替换为上面的MATCH函数即可使其动态化。
=VLOOKUP(K8,B4:E13,MATCH("Sales Person",B3:E3,0),FALSE)
现在，即使销售人员列的索引发生变化，VLOOKUP 公式也会给出相同的结果。
以下是最终的 DsExcel 代码，显示了 MATCH 函数与 VLOOKUP 函数的用法：
JAVA
//Apply formula to L8 cell of the worksheet
worksheet.getRange("L8").setFormula("=VLOOKUP(K8,B4:E13,MATCH(\"Sales Person\",B3:E3,0),FALSE)");
.NET
//Apply formula to L8 cell of the worksheet
worksheet.Range["L8"].Formula = "=VLOOKUP(K8,B4:E13,MATCH(\"Sales Person\",B3:E3,0),FALSE)";
https://di87bzq19l2.feishu.cn/space/api/box/stream/download/asynccode/?code=MjhjMGQ5ZjdjZTQ5YTRjN2IzOThlZjlmMjQ1NzhjZjBfWVB2dkdGY0pwUmhBRDh4OWZBdXA2dTJ6RXpzUVpaTWtfVG9rZW46RVV0S2JLNDhsb2pBSGx4anBqbGNHWU5NblZlXzE3MTcwNTI3MTM6MTcxNzA1NjMxM19WNA
FILTER 函数
FILTER 函数可让您根据特定条件提取数据。当您根据特定条件从大型数据集中获取部分数据时，此功能非常理想。
例如获取北部地区的销售记录。以下是 FILTER 函数的语法：
=FILTER(array,include,)
[*]Array -该参数定义要过滤的值的范围。将其设置为 A4:G13 以搜索所有记录
[*]Include -第二个参数是条件的布尔数组。在这个例子中，我们在 D4:D13 中寻找“北部”地区；也就是说，此参数的值应该是 D4:D13="North"
[*]If_empty- 当没有符合条件的条目时，此参数返回指定值。我们将其设置为“No Record Found”
以下是具有所有这些参数值的 FILTER 函数：
=FILTER(A4:G13,D4:D13=” North”,"No Record Found")
下面的 DsExcel 代码显示了如何使用 FILTER 函数获取北部地区销售商品的详细信息：
JAVA
//Apply formula to K8 cell of the worksheet
worksheet.getRange("K8").setFormula2("=FILTER(A4:G13,D4:D13=O4,\"No Record Found\")");
.NET
//Apply formula to K8 cell of the worksheet
worksheet.Range["K8"].Formula =worksheet.Range["K8"].Formula2 = "=FILTER(A4:G13,D4:D13=O4,\"No Record Found\")";
https://di87bzq19l2.feishu.cn/space/api/box/stream/download/asynccode/?code=MDBkOGQ2NDQwZjYzZWZkNGZiMGVkY2YzY2I2ZjY1ZDBfaEdCVW1pQjFWTDlaRmxZYzRvVkRGSWpZbTlaUHlpZVVfVG9rZW46VFZvTWJyUmNSb1dKbUx4eG9aMmN1bFlMbkxlXzE3MTcwNTI3MTM6MTcxNzA1NjMxM19WNA
TRANSPOSE 函数
TRANSPOSE 函数可改变给定数据的方向。进行行列转置。这有助于从不同的角度分析数据。此函数的语法非常简单，因为它只需要转置范围即可。
=TRANSPOSE(array)
假设我们要分析每月的销售额；转置数据并将月份作为列标题可以更轻松地分析每个月的数据。以下是 TRANSPOSE 函数，其唯一参数是目标范围：
=TRANSPOSE(A3:G13)
使用下面的 DsExcel 代码转置数据：
JAVA
//Apply formula to K3 cell of the worksheet
worksheet.getRange("K3").setFormula2("=TRANSPOSE(A3:G13)");
.NET
//Apply formula to K3 cell of the worksheet
worksheet.Range["K3"].Formula = "=TRANSPOSE(A3:G13)";
https://di87bzq19l2.feishu.cn/space/api/box/stream/download/asynccode/?code=ZmRmNGIyZjkyY2JmNmZjOTc3NDZmNjYzOTQwNTY2NjdfUW9vckx3N1FrcnY1T0xFVEZKY0VJOTZ5VWp3VkRJZ0ZfVG9rZW46SlY2NGJRSHZub00ybzV4TWtVRGNwdXJibnNkXzE3MTcwNTI3MTM6MTcxNzA1NjMxM19WNA
您可以下载 JAVA / .NET 示例项目来查看上述所有功能的实际运行代码。
总结
在这篇博客中，介绍了如何在程序代码中整合高级 Excel 函数（无需自定义逻辑）来增强JAVA/ .NET 应用程序中的数据分析功能。DsExcel 是一种复杂的电子表格 API，它提供了大量类似 Excel 的功能，除了这些完整的公式功能支持外，也包括了诸如过滤、排序、数据透视表、条件格式、图表、迷你图等功能。
页:
[1]