Excel公式性能优化请求：37K次调用致大文件运行卡顿

当前有一个Excel公式被调用37000次，导致文件体积庞大、运行性能缓慢，现请求对其进行优化：

=IFERROR(IF($A162<>"",IF(LEN(Teams!$BHR157)>0,LET(pattern,INDEX(Shifts!$I$3:$NC$402,MATCH($A162,Shifts!$A$3:$A$402,0),MATCH(BK$5,Shifts!$I$2:$NC$2,0)),IF(BK$5="","NA",IF(BK$5<Teams!$C157,"NE",IF(AND(Teams!$D157>0,BK$5>Teams!$D157),"NE",IF(IFERROR(NOT(VLOOKUP(BK$5,L_HOLS_SHIFT,2,FALSE)=0),FALSE),"CH",IF(MID(pattern,11,1)=0,MID(pattern,5,1),IF(AND(MID(pattern,5,1)=MID(pattern,11,1),MID(pattern,5,1)>0,SUMPRODUCT(--(MID(pattern,7,3)={"HOL","FLD","LID","UNP","ABS","CBR","LIS","MAT"}))),IF(MID(pattern,11,1)="0","",MID(pattern,7,3)),MID(pattern,5,1)+IF(SUMPRODUCT(--(MID(pattern,7,3)={"SSI","OVT"})),MID(pattern,11,1),"0")+IF(AND(MID(pattern,5,1)="0",MID(pattern,7,3)="SDS"),MID(pattern,11,1),"0")-IF(AND(MID(pattern,5,1)>"0",MID(pattern,7,3)="SDS"),MID(pattern,11,1),"0")-IF(SUMPRODUCT(--(MID(pattern,7,3)={"HOL","FLD","LID","UNP","ABS","CBR","LIS","MAT","SSO"})),MID(pattern,11,1),"0")))))))),"",""),"")

Pattern格式说明 #

Pattern格式为：ROT000XXX000（其中XXX为HOL、UNP等标识）

重点优化代码段 #

需重点优化的起始代码段：

IF(MID(pattern,11,1)=0,MID(pattern,5,1),IF(AND(MID(pattern,5,1)=MID(pattern,11,1),MID(pattern,5,1)>0,SUMPRODUCT(--(MID(pattern,7,3)={"HOL","FLD","LID","UNP","ABS","CBR","LIS","MAT"}))),

需实现的核心逻辑 #

每3个单元格对应一个班次，单元格A取ROT后第1位数字及XXX后第1位数字，下一单元格取第2位，最后单元格取第3位，具体逻辑如下：

• 检查XXX是否有值，若无则根据班次显示ROT对应数值（分为3个班次，ROT/XXX后每位数字对应一个班次）
• 若XXX=SDS，需检查ROT对应数值：若大于0则扣除该值，若为0则添加该值（例如ROT080SDS880需将第二班次的8移至第一班次）
• 若XXX=SSI或OVT，将对应小时数加到ROT数值后显示
• 若XXX为"HOL"、"FLD"、"LID"、"UNP"、"ABS"、"CBR"、"LIS"、"MAT"、"SSO"，扣除对应小时数
• 若ROT数值与XXX数值相同且为扣除操作，显示3位代码；若为0或空则不显示
• 若扣除时数小于全额，仅扣除对应时数

示例 #

针对ROT080SDS880，原排班表为：

调整后应为：

感谢协助！

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