Google Sheets中动态偏移多行数据匹配表头及公式优化需求

一、动态生成水果表头（E1及右侧） #

用优化后的QUERY公式自动提取并排列水果名称，新增水果后自动更新：

=TRANSPOSE(QUERY(第一个表!B:B,"select distinct B where B is not null",1))

将公式放在E1单元格，会自动抓取第一个表中所有不重复的水果名称并横向展示。

二、替换VLOOKUP的高效自动填充方案 #

在E2单元格输入以下公式，无需手动拖动即可自动覆盖所有日期行和新增水果列：

=BYCOL(E1:1, LAMBDA(fruit,
  ARRAYFORMULA(IFERROR(XLOOKUP($D2:$D, 第一个表!A:A, INDEX(第一个表!$A:$Z,,MATCH(fruit, 第一个表!$1:$1,0))), 0))
))

公式逻辑说明： #

• BYCOL(E1:1, LAMBDA(fruit, ...))：遍历E1行的每个水果名称，逐列处理数据
• MATCH(fruit, 第一个表!$1:$1,0)：定位当前水果在第一个表中的对应数据列
• INDEX(第一个表!$A:$Z,,列号)：精准引用该水果的收获量数据列
• XLOOKUP($D2:$D, 第一个表!A:A, ...)：匹配连续日期与第一个表的不连续日期，返回对应收获量；XLOOKUP检索效率远高于VLOOKUP，支持灵活的匹配规则与默认值设置
• ARRAYFORMULA：一次性生成整列数据，避免逐个单元格重复公式，大幅降低计算负载
• IFERROR(..., 0)：将无匹配结果的单元格显示为0（可按需改为空值""）

三、额外性能优化建议 #

• 若第一个表数据量较大，可将第一个表!A:A改为实际数据范围（如第一个表!A2:A1000），缩小检索范围提速
• 用LET函数封装重复计算，进一步优化性能：

=LET(
  sourceHeaders, 第一个表!$1:$1,
  sourceDates, 第一个表!A:A,
  BYCOL(E1:1, LAMBDA(fruit,
    ARRAYFORMULA(IFERROR(XLOOKUP($D2:$D, sourceDates, INDEX(第一个表!$A:$Z,,MATCH(fruit, sourceHeaders,0))), 0))
  ))
)

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