Google Sheets大表格多行列提取数组公式性能优化问询

问题背景 #

现有一个两列表格，第一列为A-Z，第二列为1-9；需查找指定字母A、B、C、D对应的数值，结合手动输入得到类似1,2,3,5,9的结果。使用包含BYROW()、VSTACK、QUERY的公式处理4000+行表格时性能显著下降，修改后的ArrayFormula仍存在延迟，需进一步优化。

当前使用的公式 #

=ArrayFormula(let(
x,offset('Table'!A2:V2,,,'Total Timetable'!$U$1),
y_1,offset(N78,,,$L$76),
y_2,offset(O78,,,$L$76),
y_3,offset(P78,,,$L$76),
y_4,offset(L78,,,$L$76)&offset(M78,,,$L$76),
MAP(
y_1,y_2,y_3,y_4,LAMBDA(a_1,a_2,a_3,a_4,
JOIN("",SUBSTITUTE(SORT(VSTACK(if($F$7="D",{$G$5;$G$5+1},{$G$5}),IFNA(Filter(CHOOSECOLS(x,6),
CHOOSECOLS(x,19)=a_1,
CHOOSECOLS(x,2)=$A$2,
CHOOSECOLS(x,3)=$F$5,
REGEXMATCH(CHOOSECOLS(x,11),$G$2),
NOT(REGEXMATCH(CHOOSECOLS(x,5),$D$2)),
CHOOSECOLS(x,22)<>a_4,
CHOOSECOLS(x,13)=a_2,
CHOOSECOLS(x,18)=a_3),tocol(,1)))),"10","0")
)))))

优化建议 #

1. 避免重复调用CHOOSECOLS #

当前公式中多次重复调用CHOOSECOLS(x,列号)，每次调用都会重新提取列数据，额外增加计算量。建议在LET函数中提前提取所有需要用到的列，减少重复计算：

=ArrayFormula(let(
  x, offset('Table'!A2:V2,,,'Total Timetable'!$U$1),
  -- 提前提取所需列
  col2, CHOOSECOLS(x,2),
  col3, CHOOSECOLS(x,3),
  col5, CHOOSECOLS(x,5),
  col6, CHOOSECOLS(x,6),
  col11, CHOOSECOLS(x,11),
  col13, CHOOSECOLS(x,13),
  col18, CHOOSECOLS(x,18),
  col19, CHOOSECOLS(x,19),
  col22, CHOOSECOLS(x,22),
  -- 原y变量定义
  y_1, offset(N78,,,$L$76),
  y_2, offset(O78,,,$L$76),
  y_3, offset(P78,,,$L$76),
  y_4, offset(L78,,,$L$76)&offset(M78,,,$L$76),
  MAP(
    y_1,y_2,y_3,y_4,LAMBDA(a_1,a_2,a_3,a_4,
      JOIN("",SUBSTITUTE(SORT(VSTACK(
        if($F$7="D",{$G$5;$G$5+1},{$G$5}),
        IFNA(FILTER(col6,
          col19=a_1,
          col2=$A$2,
          col3=$F$5,
          REGEXMATCH(col11,$G$2),
          NOT(REGEXMATCH(col5,$D$2)),
          col22<>a_4,
          col13=a_2,
          col18=a_3
        ),tocol(,1))
      )),"10","0")
    )
  )
))

2. 替换OFFSET为非易失性引用 #

OFFSET是易失性函数，工作表任何变动都会触发它重新计算。如果引用范围是固定的，改用INDEX替代OFFSET，减少不必要的计算触发：

-- 替换示例
y_1, INDEX(N:N,78):INDEX(N:N,78+$L$76-1),
y_2, INDEX(O:O,78):INDEX(O:O,78+$L$76-1),
y_3, INDEX(P:P,78):INDEX(P:P,78+$L$76-1),
y_4, INDEX(L:L,78):INDEX(L:L,78+$L$76-1)&INDEX(M:M,78):INDEX(M:M,78+$L$76-1),

3. 预计算固定条件值 #

公式中$F$7="D"、$A$2、$F$5等都是固定值，可在LET中提前计算相关结果，避免在MAP循环内重复判断：

=ArrayFormula(let(
  x, offset('Table'!A2:V2,,,'Total Timetable'!$U$1),
  -- 预计算固定条件
  fixed_col2, $A$2,
  fixed_col3, $F$5,
  fixed_regex1, $G$2,
  fixed_regex2, $D$2,
  fixed_g5, $G$5,
  vstack_base, if($F$7="D",{fixed_g5;fixed_g5+1},{fixed_g5}),
  -- 提取所需列
  col2, CHOOSECOLS(x,2),
  col3, CHOOSECOLS(x,3),
  col5, CHOOSECOLS(x,5),
  col6, CHOOSECOLS(x,6),
  col11, CHOOSECOLS(x,11),
  col13, CHOOSECOLS(x,13),
  col18, CHOOSECOLS(x,18),
  col19, CHOOSECOLS(x,19),
  col22, CHOOSECOLS(x,22),
  -- y变量定义（替换OFFSET）
  y_1, INDEX(N:N,78):INDEX(N:N,78+$L$76-1),
  y_2, INDEX(O:O,78):INDEX(O:O,78+$L$76-1),
  y_3, INDEX(P:P,78):INDEX(P:P,78+$L$76-1),
  y_4, INDEX(L:L,78):INDEX(L:L,78+$L$76-1)&INDEX(M:M,78):INDEX(M:M,78+$L$76-1),
  MAP(
    y_1,y_2,y_3,y_4,LAMBDA(a_1,a_2,a_3,a_4,
      JOIN("",SUBSTITUTE(SORT(VSTACK(
        vstack_base,
        IFNA(FILTER(col6,
          col19=a_1,
          col2=fixed_col2,
          col3=fixed_col3,
          REGEXMATCH(col11,fixed_regex1),
          NOT(REGEXMATCH(col5,fixed_regex2)),
          col22<>a_4,
          col13=a_2,
          col18=a_3
        ),tocol(,1))
      )),"10","0")
    )
  )
))

4. 缩小计算数据集 #

用QUERY提前筛选出符合固定条件的行，减少后续FILTER和循环处理的数据量：

=ArrayFormula(let(
  -- 提前筛选固定条件的行
  filtered_x, QUERY('Table'!A2:V,"where B='"&$A$2&"' and C='"&$F$5&"' and not E matches '"&$D$2&"' and K matches '"&$G$2&"'",0),
  x, offset(filtered_x,0,0,'Total Timetable'!$U$1),
  -- 后续提取列和计算基于filtered_x
  ...
))

5. 替换逐行循环为数组运算 #

如果y_1到y_4的行数较多，MAP逐行执行FILTER的开销很大。可以构建所有a_1/a_2/a_3/a_4的组合，用数组方式一次性匹配x中的数据并聚合结果，避免逐行循环。

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