求助：Google Sheets中结合REDUCE&FILTER跨表提取匹配数据

问题背景 #

我尝试使用QUERY、ARRAYFORMULA等公式多周仍未解决以下问题：

现有3个协同工作的Google Sheets工作簿：

• Product Catalogue（产品目录）：存储各产品类型及其关联组件清单
• Schedule（计划表）：每个产品对应独立标签页，存储该产品的所有特定迭代数据
• Components Required（组件需求表）：每个组件对应独立标签页，A1单元格为组件编号，标签页名称与组件编号完全一致

需求：在Components Required各标签页的A3单元格添加公式，实现以下逻辑：

1. 通过当前标签页A1的组件编号，在Product Catalogue中找到所有关联产品
2. 从Schedule的对应产品标签页中提取所有迭代数据，填充到当前标签页的A列（从A3开始）

已制作示例文件，Components Required的首个标签页为手动实现的预期效果。

解决方案公式 #

在Components Required标签页的A3单元格输入以下公式（需替换公式中的工作簿ID及对应列范围）：

=ARRAYFORMULA(
  FLATTEN(
    LAMBDA(linked_products,
      IFERROR(
        VLOOKUP(
          SEQUENCE(ROWS(linked_products)*1000),
          {SEQUENCE(ROWS(linked_products)*1000, 1, 0, 1/1000) + 1,
           FLATTEN(
             BYROW(linked_products, LAMBDA(product,
               IMPORTRANGE("【Schedule工作簿ID】", product&"!A2:A")
             ))
           )
          },
          2, FALSE
        )
      )
    )(
      FILTER(
        IMPORTRANGE("【Product Catalogue工作簿ID】", "A2:A"),
        IMPORTRANGE("【Product Catalogue工作簿ID】", "B2:B") = A1
      )
    )
  )
)

公式说明 #

1. 关联产品筛选：

   • IMPORTRANGE("【Product Catalogue工作簿ID】", "A2:A")：获取产品目录中的产品列表
   • IMPORTRANGE("【Product Catalogue工作簿ID】", "B2:B")：获取产品对应的组件编号
   • FILTER(...)：筛选出与当前标签页A1组件编号匹配的所有产品

2. 迭代数据提取与合并：

   • BYROW(linked_products, LAMBDA(product, IMPORTRANGE(...)))：对每个关联产品，从Schedule的对应标签页提取迭代数据（示例中为A2:A列，可按需修改范围）
   • FLATTEN(...)：将多个产品的迭代数据合并为一维数组
   • VLOOKUP + SEQUENCE：处理不同产品迭代数据的行数差异，确保所有数据连续输出

3. 自动扩展：ARRAYFORMULA确保公式自动填充至所有数据行，无需手动拖拽

注意事项 #

• 首次使用IMPORTRANGE时，需点击公式旁的「允许访问」授权跨工作簿数据读取
• 确保Schedule中的产品标签页名称与Product Catalogue中的产品名称完全一致（大小写、空格、特殊字符需严格匹配）
• 若单个产品的迭代数据行数超过1000，将公式中的1000替换为更大的数值
• 若迭代数据在Schedule的多列，可将product&"!A2:A"修改为对应范围（如product&"!A2:D"）

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