前言

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

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

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

實(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)算符的動(dòng)態(tài)數(shù)組公式
    sheet.getRange("$E$4").setFormula2("=@UNIQUE(D4:D12)");

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

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

溢出范圍參考 （#）

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

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

例如，若要查找單元格 E4 中應(yīng)用的 UNIQUE 公式提取了多少個(gè)唯一值，請?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è)置動(dòng)態(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 中為動(dòng)態(tài)數(shù)組公式提供支持，下面小編將用一個(gè)簡單的例子展示動(dòng)態(tài)數(shù)組公式的用途。

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

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

柱狀圖繪制來自預(yù)定義單元格范圍的數(shù)據(jù)，該范圍綁定到圖表系列。因此，如果過濾后的行數(shù)發(fā)生變化，例如用戶更改了“Show”列中的值，就會(huì)導(dǎo)致FILTER公式重新計(jì)算，從而篩選后的數(shù)據(jù)單元格范圍也會(huì)發(fā)生變化。但是，圖表系列始終引用相同的單元格范圍，因此任何新的篩選數(shù)據(jù)行如果不在系列單元格范圍內(nèi)，則不會(huì)在圖表上繪制。但是，我們希望所有經(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)建一個(gè)工作簿，并打開數(shù)據(jù)文件
Workbook workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Open("xlsx\\SalesReport_sourcedata.xlsx");

加載效果：

步驟 2：使用動(dòng)態(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這兩個(gè)動(dòng)態(tài)數(shù)組函數(shù)來創(chuàng)建一個(gè)動(dòng)態(tài)數(shù)組公式。

將使用VLOOKUP函數(shù)提取金額列中的數(shù)值，并將FILTER函數(shù)的溢出范圍作為參數(shù)傳遞，以便獲取與每個(gè)篩選產(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);
//使用動(dòng)態(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ù)。在這里，我們通過向工作表添加兩個(gè)命名范圍來添加一個(gè)簡單的圖表來展示銷售額，其中一個(gè)引用篩選出的數(shù)據(jù)中的“產(chǎn)品”列，另一個(gè)引用“銷售”列。然后，這些命名區(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，會(huì)發(fā)現(xiàn)更改“Show”列中的值會(huì)重新計(jì)算動(dòng)態(tài)數(shù)組公式并更新過濾后的數(shù)據(jù)單元格范圍。但是，僅當(dāng)新篩選的數(shù)據(jù)位于單元格區(qū)域 G3：H8（即系列單元格區(qū)域）時(shí)，此更改才會(huì)在圖表中可見。如果過濾后的數(shù)據(jù)不在單元格范圍 G3：H8 中，則它不會(huì)顯示在圖表中，如本示例中過濾后的數(shù)據(jù)范圍擴(kuò)展到 G3：H10，但圖表僅呈現(xiàn)來自 G3：H8 的數(shù)據(jù)：

此圖表應(yīng)繪制所有過濾掉的數(shù)據(jù)。為此，我們必須使用溢出范圍參考更新序列單元格范圍，這將確保序列單元格范圍始終包含包含過濾數(shù)據(jù)的完整單元格范圍。下面是示例代碼片段，該代碼片段更新命名區(qū)域以使用溢出范圍引用，從而根據(jù)動(dòng)態(tài)數(shù)組公式的結(jié)果使引用的單元格區(qū)域動(dòng)態(tài)化。然后，這些動(dòng)態(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中添加動(dòng)態(tài)數(shù)組公式的文章就介紹到這了,更多相關(guān)Java Excel添加動(dòng)態(tài)數(shù)組公式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論