如何提升调用大数据集的Excel复杂数组公式运行速度？

核心优化措施 #

1. 替换全列引用为动态有效数据范围 #

全列引用（如G:G、B:B）会让公式遍历整列所有单元格（包括大量空行），这是性能消耗的核心原因。可以通过INDEX+COUNTA组合自动定位有效数据的最后一行，仅遍历有数据的区域：
优化后的公式示例：

=IFERROR(TAKE(TAKE(SORT(FILTER(
    'TELEDATA IN PROGRESS (9.22.23)'!E1:INDEX('TELEDATA IN PROGRESS (9.22.23)'!T:T,COUNTA('TELEDATA IN PROGRESS (9.22.23)'!E:E)),
    ('TELEDATA IN PROGRESS (9.22.23)'!G1:INDEX('TELEDATA IN PROGRESS (9.22.23)'!G:G,COUNTA('TELEDATA IN PROGRESS (9.22.23)'!E:E))=BI2)*
    ('TELEDATA IN PROGRESS (9.22.23)'!B1:INDEX('TELEDATA IN PROGRESS (9.22.23)'!B:B,COUNTA('TELEDATA IN PROGRESS (9.22.23)'!E:E))="MOL ")*
    ('TELEDATA IN PROGRESS (9.22.23)'!U1:INDEX('TELEDATA IN PROGRESS (9.22.23)'!U:U,COUNTA('TELEDATA IN PROGRESS (9.22.23)'!E:E))>0
),16,-1),1),,1),"")

这里以E列作为关键列（公式筛选范围起始列），用COUNTA统计有效行数，INDEX定位最后一行，确保公式只处理有数据的区域。

2. 合并嵌套TAKE函数 #

原公式中TAKE(TAKE(SORT(...),16,-1),1)可简化为TAKE(SORT(...),1,1)，直接指定取第1行第1列，减少一层函数嵌套，降低运算步骤：
简化后对应片段：

TAKE(SORT(...),1,1)

3. 启用Excel内置性能优化选项 #

• 打开Excel选项→「高级」→「计算选项」，勾选「启用多线程计算」，利用多核CPU并行处理运算。
• 将「自动重算」改为「手动重算」（按F9刷新），避免数据新增时频繁触发公式计算，减轻实时负荷。

4. 用LET函数减少重复引用 #

LET函数可将重复调用的数据区域定义为变量，避免公式多次读取同一区域，同时让逻辑更清晰：

=LET(
    DataRange, 'TELEDATA IN PROGRESS (9.22.23)'!E1:INDEX('TELEDATA IN PROGRESS (9.22.23)'!T:T,COUNTA('TELEDATA IN PROGRESS (9.22.23)'!E:E)),
    ColG, 'TELEDATA IN PROGRESS (9.22.23)'!G1:INDEX('TELEDATA IN PROGRESS (9.22.23)'!G:G,COUNTA('TELEDATA IN PROGRESS (9.22.23)'!E:E)),
    ColB, 'TELEDATA IN PROGRESS (9.22.23)'!B1:INDEX('TELEDATA IN PROGRESS (9.22.23)'!B:B,COUNTA('TELEDATA IN PROGRESS (9.22.23)'!E:E)),
    ColU, 'TELEDATA IN PROGRESS (9.22.23)'!U1:INDEX('TELEDATA IN PROGRESS (9.22.23)'!U:U,COUNTA('TELEDATA IN PROGRESS (9.22.23)'!E:E)),
    Filtered, FILTER(DataRange,(ColG=BI2)*(ColB="MOL ")*(ColU>0)),
    Sorted, SORT(Filtered,16,-1),
    Result, TAKE(Sorted,1,1),
    IFERROR(Result,"")
)

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