Excel公式性能优化咨询：146k+次调用的慢公式提速方案

我在工作表中对全年每天×400人（共146k+次计算）使用以下Excel公式，它占用了80%的加载时间。该公式从Teams获取排班模式，从对应月份工作表检查假期、加班等信息，生成类似ROT080OVT000SSI000SSO000SDS234HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000的编码。目前已通过LET函数优化，想咨询进一步提速方案。

公式如下：

=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"),"")

如需示例文件可提供。

针对146k+次的批量计算，以下是具体优化方向：

• 消除重复计算逻辑：当前公式中ROT段的计算在两个分支重复出现，可移入LET函数新增rot_code变量统一计算，避免重复执行NETWORKDAYS.INTL和MOD这类高开销运算。
• 替换低效查找函数：将VLOOKUP改为XLOOKUP（Excel 365/2021+）或INDEX+MATCH组合，前者无需指定列号且查找效率更高，后者避免VLOOKUP的整列遍历开销。
• 预计算固定值：把LEN(Teams!$BHR4)、LEN(Teams!$BHR4)/3这类固定值预存到辅助单元格，公式直接引用；对NETWORKDAYS.INTL的计算结果，可提前生成所有日期对应的工作日偏移量存入辅助区域，避免重复计算。
• 简化前缀判断逻辑：用SWITCH替代重复的LEFT+IF判断，一次性匹配d_patt的前缀并生成对应段，再结合TEXTJOIN拼接默认值，减少多次条件判断的开销。
• 改用动态数组批量计算：如果使用Excel 365/2021，用BYROW或BYCOL遍历人员和日期维度，一次性生成整区域结果，避免每个单元格单独触发计算。
• 优化数据结构：将各月份的假期/加班数据合并到单个工作表，新增月份列标识，减少跨工作表引用的开销；把SETTINGS表转为Excel表（Ctrl+T），让Excel自动优化查找性能。
• 调整计算设置：启用手动计算（文件>选项>公式>手动重算），仅在需要时按F9刷新，避免编辑时的实时重算；关闭“保存前自动重算”选项，减少保存时的计算负载。
• 切换到批量处理工具：若公式优化仍不达标，用Power Query读取所有数据源，通过M语言批量生成编码后加载到工作表；或用VBA编写循环逻辑，一次性遍历所有数据并写入结果，规避Excel公式引擎的性能瓶颈。

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