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資料進行迭代的方法，如果您想了解更多有關於資料迭代的玩法和技巧，可以參考這篇幫助手冊，無論是初學者還是有經驗的專業人士，該幫助手冊都將為您提供有價值的指導和幫助。
擴充套件連結：