Google Sheets多表筛选列运算：Components表金额列公式求解

问题背景 #

现有三个表格：

• Products表：记录产品及其数量
• CompsByProd表：记录各产品对应的组件、组件用量及类型
• Components表：需生成符合条件的唯一组件及其总用量，其中Component列已通过公式=UNIQUE(FILTER(CompsByProd[Component];ISNUMBER(MATCH(CompsByProd[Product];Products[Product];0));CompsByProd[Type]="T1"))得到结果（x、y），现需计算Amount列：将CompsByProd中满足「Type=T1且产品存在于Products表」的组件用量，与对应产品的数量相乘后按组件求和（期望结果：x=200，y=325）。

正确公式 #

单单元格公式（逐行计算） #

在Components表的Amount列第一个数据单元格（如B2）输入以下公式，下拉填充即可：

=SUMPRODUCT(FILTER(CompsByProd[Amount]*IFERROR(VLOOKUP(CompsByProd[Product],Products[Product:Amount],2,FALSE),0),CompsByProd[Component]=A2,CompsByProd[Type]="T1",ISNUMBER(MATCH(CompsByProd[Product],Products[Product],0))))

数组公式（一次性生成所有结果） #

在Components表的B2单元格输入以下公式，无需下拉，自动适配所有Component行：

=ARRAYFORMULA(IF(A2:A="","",SUMPRODUCT((CompsByProd[Component]=A2:A)*(CompsByProd[Type]="T1")*(ISNUMBER(MATCH(CompsByProd[Product],Products[Product],0)))*CompsByProd[Amount]*IFERROR(VLOOKUP(CompsByProd[Product],Products,2,FALSE),0))))

一次性生成整张Components表（QUERY版） #

无需分开生成Component和Amount列，直接用QUERY函数生成完整结果：

=QUERY(CompsByProd,"SELECT Component, SUM(Amount * P.Amount) WHERE Type='T1' AND Product IN (SELECT Product FROM Products) GROUP BY Component LABEL SUM(Amount * P.Amount) 'Amount'",1)

注：需将Products表的Product列命名为ProdList，或把公式中(SELECT Product FROM Products)替换为(INDIRECT("Products[Product]"))以适配结构化引用。

错误原因说明 #

你之前用=ARRAYFORMULA(IFERROR(VLOOKUP(CompsByProd[Product];Products;2;FALSE);0)*CompsByProd[Amount])得到乘积后，用SUMIF/SUMIFS报错是因为SUMIF系列函数要求求和范围为连续单元格区间，而ARRAYFORMULA生成的是动态数组，无法直接作为SUMIF的参数；SUMPRODUCT则支持数组条件判断，更适合这类动态计算场景。

优化建议 #

1. 坚持使用结构化引用：用表名[列名]替代单元格范围（如A1:B10），表格新增行时公式自动适配，可读性更强。
2. 优先用QUERY简化流程：如果不需要单独维护Component列，直接用QUERY一次性生成整张Components表，减少公式拆分，提升效率。
3. 添加数据验证：给CompsByProd的Product列添加数据验证，限定为Products表的产品列表，避免无效数据干扰计算。

内容的提问来源于stack exchange，提问作者Felipe Ramírez Darvich