关于加速SCAN数组公式及Solver非线性求解速度的技术问询

问题背景 #

搭建了一套基于数学模型的预测公式系统，用于计算预测值与实测数据的误差平方和，通过Solver工具以GCG Nonlinear方法最小化该误差，但整体求解过程耗时约20分钟，现寻求提速方案。

已定义命名函数（工作表：'100DN'） #

P_Th（性能瓶颈函数） #

=VSTACK(INDEX('100DN'!P_AU;1);SCAN(INDEX('100DN'!P_AU;1);INDEX('100DN'!T_AU;SEQUENCE(COUNT('100DN'!T_AU)-1;;2))-INDEX('100DN'!T_AU;SEQUENCE(COUNT('100DN'!T_AU)-1));LAMBDA(p;dt;EXP(-dt/'100DN'!$M$9)*(p-INDEX('100DN'!P_AU;1))+(1-EXP(-dt/'100DN'!$M$9))*'100DN'!$M$5*('100DN'!$M$2)+INDEX('100DN'!P_AU;1))))

工作表公式 #

• $M$10：=SUMSQ(P_TH-P_AU)

Solver参数 #

• 求解方法：GCG Nonlinear（默认设置）
• 目标：最小化$M$10
• 可变单元格：$M$5;$M$9
• 约束条件：
  • $M$5 >=.00001，初始值接近解
  • $M$9 >=.00001，初始值接近解
  • 勾选“使无约束变量为非负”

样本与数据说明 #

• P_AU和T_AU为预计算值；$M$2为常量-0.1

T_AU数据 #

0,004
0,054
0,104
0,154
0,204
0,254
0,304
0,354
0,404
0,454
0,504
0,554
0,604
0,654
0,704
0,753
0,803
0,853
0,904
0,955
1,004
1,054
1,104
1,154
1,203
1,254
1,305
1,355
1,404
1,454
1,504
1,553
1,603
1,654
1,704
1,755
1,805
1,854
1,904
1,954
2,004
2,055
2,104
2,154
2,204
2,253
2,303
2,354
2,404
2,455
2,505
2,555
2,604
2,654
2,704
2,754
2,805
2,854
2,904
2,954
3,004
3,053
3,104
3,154
3,204
3,254
3,304
3,354
3,404
3,454
3,504
3,554
3,604
3,654
3,704
3,754
3,804
3,854
3,904
3,954
4,004
4,054
4,104
4,154
4,203
4,254
4,304
4,355
4,404
4,454
4,504
4,553
4,604
4,654
4,705
4,754
4,804
4,854
4,904
4,953
5,004
5,054
5,104
5,154
5,204
5,254
5,303
5,354
5,404
5,454
5,504
5,554
5,604
5,653
5,703
5,754
5,805
5,854
5,904
5,953
6,003
6,053
6,104
6,154
6,204
6,255
6,304
6,354
6,404
6,454
6,504
6,555
6,605
6,655
6,704
6,754
6,804
6,854
6,904
6,954
7,004166667
7,0545
7,104166667
7,153833333
7,203833333
7,254
7,3045
7,3545
7,404333333
7,453666667
7,503833333
7,553666667
7,603666667
7,654166667
7,704333333
7,754166667
7,803833333
7,853833333
7,9035
7,953666667
8,004
8,054166667
8,1045
8,154166667
8,203833333
8,253666667
8,3035
8,3535
8,404666667

P_AU数据 #

81,453
77,080
71,801
67,594
63,615
60,098
56,518
53,552
50,886
47,887
45,411
43,620
41,766
40,003
38,200
36,120
35,205
33,085
32,093
30,318
29,280
28,276
26,927
26,233
25,230
24,279
23,537
22,557
21,605
21,165
20,196
19,283
18,607
17,972
17,821
17,002
16,595
15,751
15,387
14,966
13,990
13,236
13,376
12,879
12,838
11,859
11,685
11,199
10,919
10,648
10,542
9,876
9,284
9,550
8,334
8,292
7,902
7,400
7,483
7,527
7,434
6,846
6,703
6,106
5,941
5,453
5,389
4,942
5,171
4,869
4,232
4,691
3,914
4,086
3,673
3,489
3,729
2,549
2,823
2,878
2,747
1,969
1,932
2,168
2,077
1,680
1,654
1,483
1,249
0,704
1,122
0,550
0,395
0,588
0,467
0,452
0,068
0,613
0,085
-0,141
-0,492
-0,046
-0,671
-0,689
-0,867
-0,804
-0,685
-0,819
-0,724
-1,369
-1,349
-1,092
-1,433
-1,359
-1,380
-2,062
-1,655
-1,788
-1,941
-1,618
-1,867
-2,295
-2,130
-1,879
-2,138
-2,352
-2,207
-2,091
-2,306
-2,630
-2,195
-2,666
-2,763
-2,592
-2,454
-2,727
-2,793
-2,341
-2,520
-2,859
-2,72969
-2,777090167
-3,021991
-3,033841167
-2,681499833
-3,221071833
-3,349842167
-3,192631667
-3,355372333
-2,923240667
-3,365642167
-2,985651
-3,3490525
-2,8821605
-3,200531667
-3,155501667
-3,472292833
-3,622393333
-3,970784333
-3,574993
-3,311132
-3,488092667
-3,573413
-3,428842333
-3,755903833
-2,751020167
-1,678196333
-0,8755535
-0,515312167

备用预计算命名函数（供参考） #

DataSplit #

=LAMBDA(RowNum;ColName;FILTER(INDIRECT('100DN'!$L$16 & "[[" & ColName & "]]");(INDIRECT('100DN'!$L$16 & "[[Temps ]]")>INDEX(INDIRECT('100DN'!$L$17);RowNum;3-'100DN'!$L$3))*(INDIRECT('100DN'!$L$16 & "[[Temps ]]")<(INDEX(INDIRECT('100DN'!$L$17);RowNum;3-'100DN'!$L$3)+INDEX(INDIRECT('100DN'!$L$17);8;3-'100DN'!$L$3))))) 

P #

=LAMBDA(Col;'100DN'!DataSplit(Col;"Pressi"))

Tm #

=LAMBDA(Col;'100DN'!DataSplit(Col;"Temps ")-INDEX(INDIRECT('100DN'!$L$17);Col;3-'100DN'!$L$3))

P_AU #

=('100DN'!P(1)+'100DN'!P(2)+'100DN'!P(3)+'100DN'!P(4)+'100DN'!P(5)+'100DN'!P(6))/6

T_AU #

=('100DN'!Tm(1)+'100DN'!Tm(2)+'100DN'!Tm(3)+'100DN'!Tm(4)+'100DN'!Tm(5)+'100DN'!Tm(6))/6

• $L$16为完整数据集所在表名，$L$17为FILTER函数所用限制条件表名

备注 #

• P_TH通过VSTACK定义为溢出数组，以适配P_AU和T_AU的可变长度并用于动态图表；已知不使用SCAN内的LAMBDA（如拖拽递归函数）更快，但不支持可变长度。

内容的提问来源于stack exchange，提问作者Benoit Beaulieu