Java程式設計之道：巧妙解決Excel公式迭代計算難題

葡萄城技術團隊發表於2023-10-17
Java程式設計Excel公式

本文由葡萄城技術團隊原創並首發。轉載請註明出處：葡萄城官網，葡萄城為開發者提供專業的開發工具、解決方案和服務，賦能開發者。

什麼是迭代計算

迭代計算其實是在 Excel 中，一種公式的迴圈引用，對於瞭解程式設計概念的同學，很容易會想到另一個詞“遞迴”。

簡單的說，就是一段程式呼叫自己，反覆執行的邏輯。遞迴在程式設計中，屬於中高階用法，因為遞迴需要一箇中斷的條件，對於初級的邏輯，遞迴很容易造成程式無限遞迴，出現 Stack Overflow（堆疊溢位）。

在 Excel 裡，也是一樣的，迭代計算指的是，公式引用中出現了迴圈引用。

舉個例子：

A1 = A1 + 1;

A1 的結果是多少？如下圖所示，通常 Excel 會透過提示，禁止迴圈引用的出現。

但是當小編開啟迭代計算後，Excel 則允許迴圈引用。

此時，再看看剛才的公式，結果為 100。

所以，什麼是迭代計算？

在 Excel 中，當出現迴圈引用時，反覆迴圈遞迴的計算，即為迭代計算。迭代次數及迭代誤差，可以在 Excel 的設定中配置。預設為 100 次。

迭代計算的使用場景

在很多數學模型的計算中，沒有特定的公式進行求解，而是需要不停的計算，反覆逼近一個期望的結果。

同時，迭代計算也適用於水利工程，地質工程的設計時的相關資料計算。

Java 中如何使用迭代計算

1. A1 = A1 + 1場景

接下來小編將以葡萄城公司的 Java API元件——GrapeCity Documents for Excel（以下簡稱為GcExcel）為例，為大家介紹如何在Java中實現迭代計算。

以上述提到過的A1 = A1 + 1 場景為例，下面是具體的程式碼：

// Create a new workbook
Workbook workbook = new Workbook();

// Enable iterative calculation
workbook.getOptions().getFormulas().setEnableIterativeCalculation(true);
workbook.getOptions().getFormulas().setMaximumIterations(10);
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("A1").setFormula("=B1 + 1");
worksheet.getRange("B1").setFormula("=A1 + 1");

System.out.println("A1:" + worksheet.getRange("A1").getValue().toString());
System.out.println("B1:" + worksheet.getRange("B1").getValue().toString());
    
// Save to an excel file
workbook.save("IterativeCalculation.xlsx");

執行程式，可以看到實現的效果：

結果值和使用Excel中使用公式的效果是一樣的，都是100。

2. IRR場景

接下來以 IRR 為例用 Excel 做一次計算。

IRR 的概念是，當 N 年的淨利潤為 0 時，內部的收益率。

假設投資本金為 1 萬，每年收益如下，在 Excel 中，分別用迭代計算和 IRR 公式進行計算。

IRR 是複利計算，假定 E7 和 E8 兩個格子分別是 IRR。那麼透過 IRR，在 F8 中可以根據複利公式進行計算。

其中 E7 為 IRR，IRR 需要使得上述的公式近似為 0。因此可以使用迭代計算來反推 IRR，E7 公式如下：

可以看到，小編一開始給 E7 定了一個初始值 0.3 （30%）。然後判斷 F7 （NPV）的值，淨值會隨著 IRR 變小，逐漸變大，而 - 0.000001，則是每一次計算，對於 IRR 的調整。假定當 NPV 大於 0 時，IRR 計算停止，只要每一次對 IRR 的調整足夠小，就可以近似的認為當 NPV 大於 0 的那一次結果趨近於 0。

經過反覆計算，其結果如下：

那麼在 Java 中，如何透過迭代計算來計算 IRR 呢？

透過藉助GcExcel， 可以很方便的把剛才的公式直接放在程式碼裡即可進行計算，程式碼如下：

public void IRR() {
    // Create a new workbook
    Workbook workbook = new Workbook();

    // Enable iterative calculation
    workbook.getOptions().getFormulas().setEnableIterativeCalculation(true);
    workbook.getOptions().getFormulas().setMaximumIterations(1000000);
    IWorksheet worksheet = workbook.getActiveSheet();
    worksheet.getRange("B1").setValue(-10000);
    worksheet.getRange("B2").setValue(1000);
    worksheet.getRange("B3").setValue(1500);
    worksheet.getRange("B4").setValue(2300);
    worksheet.getRange("B5").setValue(3200);
    worksheet.getRange("B6").setValue(4600);
    worksheet.getRange("B7").setValue(6800);
    worksheet.getRange("E7").setFormula("=IF(E7 = 0,E7+1,IF(F7 < 0,E7-0.000001,E7))");
    worksheet.getRange("F7").setFormula("=B1+B2/(1+E7)+B3/(1+E7)^2+B4/(1+E7)^3+B5/(1+E7)^4+B6/(1+E7)^5+B7/(1+E7)^6");

    System.out.println("E7 IRR:" + worksheet.getRange("E7").getValue().toString());
}

計算結果：和 Excel 裡計算的值基本一致。

總結

以上就是在Java中對Excel資料進行迭代的方法，如果您想了解更多有關於資料迭代的玩法和技巧，可以參考這篇幫助手冊，無論是初學者還是有經驗的專業人士，該幫助手冊都將為您提供有價值的指導和幫助。

擴充套件連結：

從表單驅動到模型驅動，解讀低程式碼開發平臺的發展趨勢

低程式碼開發平臺是什麼？

基於分支的版本管理，幫助低程式碼從專案交付走向定製化產品開發

相關文章