我需要将矩阵提升到较大的幂(比如 200),但我不能使用 VBA,因为它适用于最终用户,而该用户的 IT 部门不允许运行宏。
而且由于功率很大,写出如下极大的公式也是不够的:
=MMULT(m,MMULT(m,MMULT(m..... )))
其中 m 是我的矩阵的范围。
有没有办法不借助 VBA 来做到这一点?
我环顾四周,到目前为止答案似乎是否定的。
答案1
你可以用迭代计算来实现
- 选中复选框:文件 - 选项 - 公式 - “启用迭代计算”(从 Excel 2007 版本开始)
- 将“最大迭代次数”值设置为足够大的数字(这是您可以提升矩阵的最大功率)
- 辅助细胞中的公式:
=IF(ISNUMBER(G2),IF(G2=E2,TEXT(G2,"0"),G2+1),IF(VALUE(G2)=E2,G2,1))
- 结果矩阵中的公式:(
=IF(ISNUMBER(G2),IF(G2=1,A2:C4,MMULT(A2:C4,H2:J4)),H2:J4)
这是一个数组公式,需要用CTRL+SHIFT+ENTER完成)
笔记
- 公式重新计算只有当你改变权力,在仅改变输入矩阵后不会重新计算,也不会重新计算所有公式(可以做到,但需要更复杂的公式和更多的辅助单元,如果您对此感兴趣请告诉我)。
- 此外,输入两个公式后,输出矩阵将仅显示 0 值,要进行计算,您需要更改功率,然后工作表将被“初始化”,它将正常工作
- 公式中尚未实现错误检查:
- 如果功率 > 最大迭代次数,则公式将仅运行设置的迭代次数
- 类似地,对于非整数或负数,公式不会停止,而是运行直到最大迭代次数
- 达到最大迭代次数后,公式将“溢出”,向前计数而不从 0 开始,为了解决这个问题,设置将在下一个迭代周期中达到的功率(例如,如果辅助 = 500,最大迭代次数 = 100,则设置 500 < 功率 < 600,因此公式将正确地停止在那里,并且它将在下一次功率设置中正常工作)。
- 如果有必要,上述大多数问题都可以在公式中修复。
更新
回答这个问题 更详细地描述该公式的工作原理。
答案2
我不认为可以无需宏即可完成如果您得出相同的结论,那么这里有一个宏可以快速完成大幂幂的工作:
Function PowerMatrix(Matrix As Range, Power As Long) As Variant
Dim Result As Variant
Dim Square As Variant
Dim i As Long
Square = Matrix
For i = 0 To 31
If (Power And 2 ^ i) Then
If IsEmpty(Result) Then
Result = Square
Else
Result = Application.WorksheetFunction.MMult(Square, Result)
End If
End If
If 2 ^ i >= Power Then Exit For
Square = Application.WorksheetFunction.MMult(Square, Square)
Next
PowerMatrix = Result
End Function
如果您无法绕过 VBA 宏的限制,那么 Excel 可能不是合适的入门工具。带有 javascript 的网页可能是更好的工具。