如何用Excel高效计算现金流

一口冰牛奶

数据1是个1n的数据集，数据2是个pq的数据集，结果集为p行。
以（x,y）表示数据集中第x行第y列的数据，以下表示均为结果集=数据集1数据集2：
（1,1）=（1,1）（1,1）
（1,2）=（1,2）（2,1）+（2,1）（1,2）
……
（5,3）=（1,5）（3,1）+（1,4）（3,2）+（1,3）（3,3）+（1,2）（3,4）+（1,1）*（3,5）
目前的处理是定义了一个函数，但由于模型中六七十万个单元格都需要进行该处理，每个单元格都调用宏，运行很慢。
是否有更好的办法得到结果集。

test.xlsx

Mengkelyu

我先纠正几个问题？你看看我的理解对不对

这里是（3，5）

这里应该是P*P? m不清楚是哪里来的。

如果p等于1k的话，最后的数据量就有1m了，这个数据量是相当大的。如果你的单元格是600k, 那么我用p=1k做测试，应该是能够说明问题的。

在优化方面，如果把计算完全写在VBA中运行就会更快。
很多时候计算慢是因为VBA和Excel GUI太多交互导致的。如果计算完全在VBA里，只把结果输出到Excel中，一般就会更快。

60万数据不太建议用Excel跑，但是如果必须用Excel的话，可以试试这个Code.

Mengkelyu

测试的时候 1000*1000 跑了88秒，还可以。例子可以看这个文件test - LargeDS - 2.zip

Sub run()

'On Error Resume Next
'运行代码前先问问用户要不要保存，以免跑代码死机
UserAnswer = MsgBox("Would you like to save before running?", vbYesNoCancel, "Save?")
      
      If UserAnswer = vbCancel Then
            Exit Sub
      ElseIf UserAnswer = vbNo Then
            Call main
      ElseIf UserAnswer = vbYes Then
           ThisWorkbook.Save
           Call main
        End If
End Sub
-----------------------------------------------------------------------------------------------------------------------
’主要代码
Sub main()
      
Application.Calculation = xlManual
Application.ScreenUpdating = False

Dim StartTime As Double
Dim SecondsElapsed As Double

Dim rg1 As Variant, rg2 As Variant, rg3 As Variant
Dim row As Long, column As Long

‘rg1是数据集1， rg2是数据集2

rg1 = Range("dataset1").CurrentRegion.Value
rg2 = Range("dataset2").CurrentRegion.Value
row = UBound(rg2, 1)
column = UBound(rg1, 2)

Dim result As Variant
ReDim result(row, row) As Double

Dim i As Long, j As Long, k As Long

Dim rangeA As Double, rangeB As Double

For i = 1 To row
If i Mod 100 = 0 Then Call ProgressTime("Progress: ", 1 / row)
    For j = 1 To row
       result(i, j) = 0
       For k = 1 To j
            If k > column Then
                rangeA = 0
            Else: rangeA = rg1(1, k)
            End If
       
            If j - k + 1 > UBound(rg2, 2) Then
                rangeB = 0
            Else
                rangeB = rg2(i, j - k + 1)
            End If
            result(i, j) = result(i, j) + rangeA * rangeB
        Next k
    Next j
Next i

’输出到一个名为Output的Named range
Range("output").Resize(UBound(result, 1), UBound(result, 2)) = result


  SecondsElapsed = Round(Timer - StartTime, 2)

  MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
  
  Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
------------------------------------------------------------------------------------------------------------------
'这个宏是用来看代码运行进度的
Private Sub ProgressTime(Message As String, percentage As Single)
Dim prog_Bar As String
'progress bar
    prog_Bar = Mid(String(20, ChrW(9632)) + String(20, ChrW(9633)), Round(20 + 1 - percentage * 20, 0), 20)
    
'Output
Application.StatusBar = Message & "  " & prog_Bar
End Sub

Mengkelyu

我也翻译成了R代码，也可以试试

rows<-1000
cols<-10

M1<-matrix(round(rnorm(cols)*100,0),nrow=1)

M2<-matrix(round(rnorm(rows*cols)*100,0),nrow=rows)


result <- matrix(, nrow = rows, ncol = rows)


for(i in 1:rows){
  for(j in 1:rows){
    result[i,j] = 0
     for(k in 1:j){
       if(k>ncol(M1)){
         rangeA = 0
       }
       else{
         rangeA = M1[1,k]
       }
       if(j-k+1>ncol(M2)){
         rangeB = 0
       }
       else{
         rangeB = M2[i,j-k+1]
       }
       result[i,j]=result[i,j] + rangeA * rangeB
     }
  }
}