Google Sheets中如何按D列值排除整行参与食材统计公式计算

一、生成动态唯一食材列表（O列） #

修改原公式，加入D列过滤条件，仅保留D列≠"Yes"的行中的食材：

简洁高效版（使用LET函数减少重复计算） #

=LET(
  filtered_data, FILTER({E4:E, G4:G, I4:I, K4:K, M4:M}, D4:D<>"Yes"),
  flattened_items, FLATTEN(filtered_data),
  SORT(UNIQUE(FILTER(flattened_items, flattened_items<>"")))
)

基础版（兼容旧版无LET函数环境） #

=SORT(UNIQUE(FILTER(FLATTEN(FILTER({E4:E, G4:G, I4:I, K4:K, M4:M}, D4:D<>"Yes")), FLATTEN(FILTER({E4:E, G4:G, I4:I, K4:K, M4:M}, D4:D<>"Yes"))<>"")))

逻辑说明：

1. 先用FILTER({E4:E, G4:G, I4:I, K4:K, M4:M}, D4:D<>"Yes")筛选出所有未标记为"Obtained?"的行的食材列
2. 通过FLATTEN将多列食材转换为一维列表
3. 过滤空值后用UNIQUE去重，最后SORT排序得到最终食材列表

二、统计对应食材总数量（P列） #

同样加入D列过滤条件，以下两种方案任选：

方案1：简化版（使用SUMPRODUCT） #

=SUMPRODUCT(
  ($D$4:$D<>"Yes")*
  ((E4:E=O4)*F4:F + 
   (G4:G=O4)*H4:H + 
   (I4:I=O4)*J4:J + 
   (K4:K=O4)*L4:L + 
   (M4:M=O4)*N4:N)
)

逻辑说明：

• $D$4:$D<>"Yes"：仅统计未标记为"Obtained?"的行
• 对每一行的5组食材-数量列进行匹配：如果某列食材等于当前O列的食材，则取对应数量，否则为0
• 用SUMPRODUCT汇总所有符合条件的数量总和

方案2：兼容原公式逻辑的修改版 #

=IFERROR(SUM(FILTER($F$4:$F, $E$4:$E=O4, $D$4:$D<>"Yes"))) + 
 IFERROR(SUM(FILTER($H$4:$H, $G$4:$G=O4, $D$4:$D<>"Yes"))) + 
 IFERROR(SUM(FILTER($J$4:$J, $I$4:$I=O4, $D$4:$D<>"Yes"))) + 
 IFERROR(SUM(FILTER($L$4:$L, $K$4:$K=O4, $D$4:$D<>"Yes"))) + 
 IFERROR(SUM(FILTER($N$4:$N, $M$4:$M=O4, $D$4:$D<>"Yes")))

逻辑说明：
在原公式的每个FILTER中新增$D$4:$D<>"Yes"条件，确保仅统计未标记为"Obtained?"的行中的对应数量，用IFERROR避免无匹配时出现错误值

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