前言

動態(tài)數(shù)組公式是 Excel 引入的一項(xiàng)重要功能，它將 Excel 分為兩種風(fēng)格：Excel 365 和傳統(tǒng) Excel（2019 或更早版本）。動態(tài)數(shù)組功能允許用戶從單個單元格中的公式返回多個結(jié)果值，并將這些值自動填充到與公式單元格相鄰的單元格中。由于結(jié)果會溢出到多個單元格中，這也被稱為溢出范圍功能。在傳統(tǒng) Excel 中，用戶必須使用 Ctrl + Shift + Enter 來將公式識別為數(shù)組公式，否則公式結(jié)果將僅返回單個值。因此，動態(tài)數(shù)組為用戶提供了更加便利的使用體驗(yàn)。現(xiàn)在我們將這樣的返回多個值的公式稱為動態(tài)數(shù)組公式。

今天小編就為大家介紹如何使用葡萄城公司的Java API 組件GrapeCity Documents for Excel（以下簡稱GcExcel）實(shí)現(xiàn)在Excel中添加動態(tài)數(shù)組。

動態(tài)數(shù)組公式

下表總結(jié)了GcExcel所有支持的公式及其語法、說明和代碼示例：

1.FILTER **：**FILTER 公式根據(jù)您指定的條件篩選范圍或數(shù)組。 FILTER(array,include,[if_empty])

代碼片段：sheet.getRange(“I4”).setFormula2(“=FILTER(D4:E12,E4:E12>G4,\”\“)”);

2.**RANDARRAY：**RANDARRAY 公式允許您在數(shù)組中生成從 0 到 1 的隨機(jī) numbers 列表。 RANDARRAY ([rows],[columns])

代碼片段：sheet.getRange(“$D$3”).setFormula2(“=RANDARRAY(4,5)”);

3.**SEQUENCE ：**SEQUENCE 公式允許您在數(shù)組中生成序列號列表。 SEQUENCE(rows,[columns],[start],[step])

代碼片段：sheet.getRange(“$D$4”).setFormula2(“=SEQUENCE(10,100,-10)”);

4.**SORTBY ：**SORTBY 公式允許您根據(jù)相應(yīng)范圍或數(shù)組中的值對范圍或數(shù)組進(jìn)行排序。 SORTBY(array, by_array1, [sort_order1], [[by_array2], [sort_order2]],…)

代碼片段：

5.**SORT：**SORT 公式用于按升序或降序?qū)Ψ秶驍?shù)組進(jìn)行排序。 SORT(array, [sort_index], [sort_order], [by_col])

6.**UNIQUE：**UNIQUE 公式允許您從項(xiàng)目范圍或數(shù)組中返回唯一列表。UNIQUE(array, [by_col], occurs_once])

代碼片段：

詳細(xì)代碼請點(diǎn)擊這里。

@ 運(yùn)算符

@ 運(yùn)算符（也稱為隱式**交集運(yùn)算符）**實(shí)現(xiàn)一種稱為隱式交集的公式行為，該行為將一組值減少為單個值。這適用于返回多個值的數(shù)組公式，在這種情況下，將根據(jù)單元格位置（即行和列）返回單個值。在老版本 Excel 中，這是默認(rèn)行為，因此不需要顯式運(yùn)算符。但是，在 Excel 365 中，所有公式都是數(shù)組公式，因此，如果您不希望數(shù)組公式溢出，則可以在公式前面加上 @ 運(yùn)算符，它只會返回一個值。

實(shí)現(xiàn)代碼：

public void ImplicitIntersection() {
    Workbook wb = new Workbook();
    //初始化工作表
    IWorksheet sheet = wb.getWorksheets().get(0);
    sheet.setName("IMPLICIT INTERSECTION");

    //添加樣例數(shù)據(jù)
    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");

    //添加含有隱式運(yùn)算符的動態(tài)數(shù)組公式
    sheet.getRange("$E$4").setFormula2("=@UNIQUE(D4:D12)");

    wb.save("output/ImplicitIntersection.xlsx");
}

實(shí)現(xiàn)效果：

溢出范圍參考 （#）

動態(tài)數(shù)組公式將其多值結(jié)果溢出到的單元格范圍稱為**溢出范圍。**每當(dāng)單擊溢出范圍中的任何單元格時(shí)，溢出范圍都會以藍(lán)色邊框突出顯示，表示該范圍中的所有值都是通過該范圍左上角單元格中的公式計(jì)算得出的。

溢出范圍參考運(yùn)算符用于引用此溢出范圍。要引用溢出范圍，請?jiān)谝绯龇秶凶笊辖菃卧竦牡刂泛蠓胖靡粋€主題標(biāo)簽或井號 （#）。

例如，若要查找單元格 E4 中應(yīng)用的 UNIQUE 公式提取了多少個唯一值，請?zhí)峁?COUNTA 公式的溢出范圍引用，如下面的代碼所示：

public void SpillReference() {
    Workbook wb = new Workbook();
    //初始化工作表
    IWorksheet sheet = wb.getWorksheets().get(0);
    sheet.setName("SPILL REFERENCE");

    //添加樣例數(shù)據(jù)
    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");

    //設(shè)置動態(tài)數(shù)組公式
    sheet.getRange("$E$4").setFormula2("=UNIQUE(D4:D12)");
    //設(shè)置帶有溢出范圍引用的公式
    sheet.getRange("$F$4").setFormula2("=COUNTA(E4#)");

    wb.save("output/SpillReference.xlsx");
}

實(shí)現(xiàn)效果：

現(xiàn)在，我們已經(jīng)了解了 GcExcel 如何在 Java 中為動態(tài)數(shù)組公式提供支持，下面小編將用一個簡單的例子展示動態(tài)數(shù)組公式的用途。

用例：使用動態(tài)數(shù)組在 Excel 中創(chuàng)建交互式圖表

考慮這樣一個場景：我們有一些表格數(shù)據(jù)，想用柱狀圖來展示。通常情況下，表格數(shù)據(jù)有很多行和列，但在制作圖表時(shí)，我們需要按照某些條件來選擇部分?jǐn)?shù)據(jù)。為了提取所需數(shù)據(jù)，我們需要根據(jù)一些條件來進(jìn)行篩選，這就是動態(tài)數(shù)組公式的應(yīng)用場景。我們使用FILTER函數(shù)根據(jù)定義的條件來篩選表格數(shù)據(jù)，一旦獲取到篩選后的數(shù)據(jù)，我們就可以用它來創(chuàng)建柱狀圖。

柱狀圖繪制來自預(yù)定義單元格范圍的數(shù)據(jù)，該范圍綁定到圖表系列。因此，如果過濾后的行數(shù)發(fā)生變化，例如用戶更改了“Show”列中的值，就會導(dǎo)致FILTER公式重新計(jì)算，從而篩選后的數(shù)據(jù)單元格范圍也會發(fā)生變化。但是，圖表系列始終引用相同的單元格范圍，因此任何新的篩選數(shù)據(jù)行如果不在系列單元格范圍內(nèi)，則不會在圖表上繪制。但是，我們希望所有經(jīng)過篩選的數(shù)據(jù)都能在圖表上顯示，這就需要將簡單靜態(tài)圖表轉(zhuǎn)換為交互式圖表，以便刷新其繪圖區(qū)域來繪制所有篩選數(shù)據(jù)的值。

因此，請繼續(xù)了解實(shí)現(xiàn)上述定義方案的詳細(xì)步驟。

步驟 1：加載數(shù)據(jù)文件

我們將首先在 GcExcel 工作簿中加載源數(shù)據(jù)，該數(shù)據(jù)將用于繪制圖表。

源數(shù)據(jù)文件下載（源文件文章系統(tǒng)的附件中）

將 Excel 文件加載到Workbook 中：

//創(chuàng)建一個工作簿，并打開數(shù)據(jù)文件
Workbook workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Open("xlsx\\SalesReport_sourcedata.xlsx");

加載效果：

步驟 2：使用動態(tài)數(shù)組公式提取圖表數(shù)據(jù)

現(xiàn)在將開始提取相關(guān)數(shù)據(jù)，以便在圖表中進(jìn)行繪制。我們將根據(jù)產(chǎn)品的銷售額繪制圖表。因此，圖表數(shù)據(jù)應(yīng)包含來自產(chǎn)品列和金額列的數(shù)值。從這兩列中提取特定數(shù)值的標(biāo)準(zhǔn)是基于展示列。我們將從展示列的值為1的每一行中提取產(chǎn)品和金額數(shù)值，并在圖表上繪制這些篩選后的數(shù)據(jù)。

使用FILTER函數(shù)從產(chǎn)品列中過濾數(shù)據(jù)，然后使用SORTBY函數(shù)按照金額列的值進(jìn)行降序排序。因此，我們將通過連接FILTER和SORTBY這兩個動態(tài)數(shù)組函數(shù)來創(chuàng)建一個動態(tài)數(shù)組公式。

將使用VLOOKUP函數(shù)提取金額列中的數(shù)值，并將FILTER函數(shù)的溢出范圍作為參數(shù)傳遞，以便獲取與每個篩選產(chǎn)品對應(yīng)的金額值。

以下是相應(yīng)的代碼片段：

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);
//使用動態(tài)數(shù)組公式來獲取產(chǎn)品列的值
worksheet.getRange("G4").setFormula2("=SORTBY(FILTER(B4:B13,E4:E13=1),FILTER(D4:D13,E4:E13=1),-1)");
//引用溢出區(qū)域來獲取金額列的值
worksheet.getRange("H4").setFormula2("=VLOOKUP(G4#,B4:D13,3,FALSE)");

以下是對工作表的快速瀏覽，其中包含 G 列和 H 列中經(jīng)過篩選和排序的數(shù)據(jù)，并突出顯示了溢出范圍：

步驟 3：使用篩選后的數(shù)據(jù)添加簡單圖表

上述步驟已生成要在圖表中繪制的數(shù)據(jù)。在這里，我們通過向工作表添加兩個命名范圍來添加一個簡單的圖表來展示銷售額，其中一個引用篩選出的數(shù)據(jù)中的“產(chǎn)品”列，另一個引用“銷售”列。然后，這些命名區(qū)域?qū)⒂糜趧?chuàng)建圖表系列。代碼如下所示：

//添加數(shù)據(jù)引用
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，會發(fā)現(xiàn)更改“Show”列中的值會重新計(jì)算動態(tài)數(shù)組公式并更新過濾后的數(shù)據(jù)單元格范圍。但是，僅當(dāng)新篩選的數(shù)據(jù)位于單元格區(qū)域 G3：H8（即系列單元格區(qū)域）時(shí)，此更改才會在圖表中可見。如果過濾后的數(shù)據(jù)不在單元格范圍 G3：H8 中，則它不會顯示在圖表中，如本示例中過濾后的數(shù)據(jù)范圍擴(kuò)展到 G3：H10，但圖表僅呈現(xiàn)來自 G3：H8 的數(shù)據(jù)：

此圖表應(yīng)繪制所有過濾掉的數(shù)據(jù)。為此，我們必須使用溢出范圍參考更新序列單元格范圍，這將確保序列單元格范圍始終包含包含過濾數(shù)據(jù)的完整單元格范圍。下面是示例代碼片段，該代碼片段更新命名區(qū)域以使用溢出范圍引用，從而根據(jù)動態(tài)數(shù)組公式的結(jié)果使引用的單元格區(qū)域動態(tài)化。然后，這些動態(tài)命名區(qū)域用于創(chuàng)建圖表系列，使圖表具有交互性。

代碼如下所示：

//添加命名引用，用于圖表數(shù)據(jù)
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)");

實(shí)現(xiàn)效果：

到此這篇關(guān)于使用Java實(shí)現(xiàn)在Excel中添加動態(tài)數(shù)組公式的文章就介紹到這了,更多相關(guān)Java Excel添加動態(tài)數(shù)組公式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論