Excel公式慢

Question

我能做些什么来进一步提高这个公式的性能吗？

=IF($A3<>"",IF(Jan!$E6<>"",LET(d_patt,IF(Jan!$E6<>"",VLOOKUP(Jan!$E6,SETTINGS!$A$12:$B$27,2,FALSE)&IF(Jan!$B6<>"",Jan!$B6,0)&IF(Jan!$C6<>"",Jan!$C6,0)&IF(Jan!$D6<>"",Jan!$D6,0),""),"ROT"&IF(LEN(Teams!$BHR4)>0,MID(Teams!$BHR4,MOD(NETWORKDAYS.INTL(Teams!$C4,I$2,"0000000")-1,LEN(Teams!$BHR4)/3)*3+1,3),"000")&IF(LEFT(d_patt,3)="OVT",d_patt,"OVT000")&IF(LEFT(d_patt,3)="SSI",d_patt,"SSI000")&IF(LEFT(d_patt,3)="SSO",d_patt,"SSO000")&IF(LEFT(d_patt,3)="SDS",d_patt,"SDS000")&IF(LEFT(d_patt,3)="HOL",d_patt,"HOL000")&IF(LEFT(d_patt,3)="LID",d_patt,"LID000")&IF(LEFT(d_patt,3)="UNP",d_patt,"UNP000")&IF(LEFT(d_patt,3)="FLD",d_patt,"FLD000")&IF(LEFT(d_patt,3)="MAT",d_patt,"MAT000")&IF(LEFT(d_patt,3)="LIS",d_patt,"LIS000")&IF(LEFT(d_patt,3)="CBR",d_patt,"CBR000")&IF(LEFT(d_patt,3)="ABS",d_patt,"ABS000")),"ROT"&IF(LEN(Teams!$BHR4)>0,MID(Teams!$BHR4,MOD(NETWORKDAYS.INTL(Teams!$C4,I$2,"0000000")-1,LEN(Teams!$BHR4)/3)*3+1,3),"000")&"OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000"),"")

我在一年中的每一天 x 400 人的纸上都有这个，所以 146k + 次。因此，这占用了 80% 的工作表加载时间。

该表基本上从 Teams 获取轮类模式，从相关月份选项卡和当天相关单元格检查是否有任何假期、加类等，然后将生成如下代码。

ROT080OVT000SSI000SSO000SDS234HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000

到目前为止，我已经设法通过使用 LET 函数来加快速度，但没有显示是否有可能进行任何进一步的改进。

如果您需要示例文件，我可以将其发送或上传到某处，但不确定是否可以通过 stackoverflow 或首选网站上传到。

谢谢

Answer 1

以下是您的公式的简化版本，以降低计算的复杂性。

我们可以将整个字符串设置为默认的“000”，而不是一个接一个地构建“OVT000SSI000...”字符串，同时始终检查“d_patt”的前 3 个字符是否与当前字符串匹配与“d_patt”匹配的部分(请参阅下面屏幕截图中突出显示的元素)

这导致了完整的公式:

=IF(LEN($A3)>0,"ROT"&IF(LEN(Teams!$BHR4)>0,MID(Teams!$BHR4,MOD(NETWORKDAYS.INTL(Teams!$C4,I$2,"0000000")-1,LEN(Teams!$BHR4)/3)*3+1,3),"000")&LET(default,"OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000",d_patt,VLOOKUP(Jan!$E6,SETTINGS!$A$12:$B$27,2,FALSE)&IF(LEN(Jan!$B6)>0,Jan!$B6,0)&IF(LEN(Jan!$C6)>0,Jan!$C6,0)&IF(LEN(Jan!$D6)>0,Jan!$D6,0),IF(LEN(d_patt)>0,REPLACE(default,SEARCH(LEFT(d_patt,3),default),6,d_patt),default)),“”)

如果 $B6、$C​​6 和 $D6 只能为空或数字，换句话说，如果它们永远不是字母或特殊字符，则 'd_patt' 函数可以进一步缩短如下:

这导致了完整的公式:

=IF(LEN($A3)>0,"ROT"&IF(LEN(Teams!$BHR4)>0,MID(Teams!$BHR4,MOD(NETWORKDAYS.INTL(Teams!$C4,I$2,"0000000")-1,LEN(Teams!$BHR4)/3)*3+1,3),"000")&LET(default,"OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000",d_patt,VLOOKUP(Jan!$E6,SETTINGS!$A$12:$B$27,2,FALSE)&(Jan!$B6+0)&(Jan!$C6+0)&(Jan!$D6+0),IF(LEN(d_patt)>0,REPLACE(default,SEARCH(LEFT(d_patt,3),default),6,d_patt),default)),“”)