什么是迭代計算

迭代計算其實是在 Excel 中，一種公式的循環(huán)引用，對于了解編程概念的同學，很容易會想到另一個詞“遞歸”。

簡單的說，就是一段程序調(diào)用自己，反復執(zhí)行的邏輯。遞歸在編程中，屬于中高級用法，因為遞歸需要一個中斷的條件，對于初級的邏輯，遞歸很容易造成程序無限遞歸，出現(xiàn) Stack Overflow（堆棧溢出）。

在 Excel 里，也是一樣的，迭代計算指的是，公式引用中出現(xiàn)了循環(huán)引用。

舉個例子：

A1 = A1 + 1;

A1 的結(jié)果是多少？如下圖所示，通常 Excel 會通過提示，禁止循環(huán)引用的出現(xiàn)。

但是當小編開啟迭代計算后，Excel 則允許循環(huán)引用。

此時，再看看剛才的公式，結(jié)果為 100。

所以，什么是迭代計算？

在 Excel 中，當出現(xiàn)循環(huán)引用時，反復循環(huán)遞歸的計算，即為迭代計算。迭代次數(shù)及迭代誤差，可以在 Excel 的設(shè)置中配置。默認為 100 次。

迭代計算的使用場景

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

同時，迭代計算也適用于水利工程，地質(zhì)工程的設(shè)計時的相關(guān)數(shù)據(jù)計算。

Java 中如何使用迭代計算

1. A1 = A1 + 1場景

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

以上述提到過的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");

運行程序，可以看到實現(xiàn)的效果：

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

2. IRR場景

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

IRR 的概念是，當 N 年的凈利潤為 0 時，內(nèi)部的收益率。

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

IRR 是復利計算，假定 E7 和 E8 兩個格子分別是 IRR。那么通過 IRR，在 F8 中可以根據(jù)復利公式進行計算。

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

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

經(jīng)過反復計算，其結(jié)果如下：

那么在 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());
}

計算結(jié)果：和 Excel 里計算的值基本一致。

到此這篇關(guān)于利用Java巧妙解決Excel公式迭代計算的文章就介紹到這了,更多相關(guān)Java Excel公式迭代計算內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論