Java编程之道:巧妙解决Excel公式迭代计算难题

原创
2023/10/18 13:54
阅读数 39

本文由葡萄城技术团队原创并首发。转载请注明出处:葡萄城官网,葡萄城为开发者提供专业的开发工具、解决方案和服务,赋能开发者。

什么是迭代计算

迭代计算其实是在 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数据进行迭代的方法,如果您想了解更多有关于数据迭代的玩法和技巧,可以参考这篇帮助手册,无论是初学者还是有经验的专业人士,该帮助手册都将为您提供有价值的指导和帮助。

扩展链接:

从表单驱动到模型驱动,解读低代码开发平台的发展趋势

低代码开发平台是什么?

基于分支的版本管理,帮助低代码从项目交付走向定制化产品开发

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部