求可批量应用的ARRAYFORMULA：按条件统计唯一值数量

问题分析 #

原逐行公式无法批量计算，且统计的是符合条件的总行数而非唯一值数量，导致结果错误：

=IF(ISBLANK(A2);""; COUNTA(FILTER((Derivatives!B:B);INDEX((Derivatives!D:D);0;0)= left(A2;4);INDEX((Derivatives!F:F);0;0)= "EQUITY CALL")))

实际需求：对A列每个单元格，统计Derivatives!B:B中满足以下条件的唯一值数量：

• Derivatives!D:D的左4位与当前A列单元格的左4位匹配
• Derivatives!F:F等于"EQUITY CALL"

可用公式 #

公式1：BYROW+ARRAYFORMULA（直观易读） #

=ARRAYFORMULA(IF(A2:A=""; ""; 
  BYROW(A2:A; LAMBDA(cell; 
    COUNTA(UNIQUE(FILTER(Derivatives!B:B; LEFT(Derivatives!D:D; 4)=LEFT(cell; 4); Derivatives!F:F="EQUITY CALL")))
  ))
))

• 逻辑：遍历A列每个单元格，筛选符合条件的Derivatives!B:B数据，提取唯一值后计数，空单元格返回空。

公式2：QUERY+XLOOKUP（大数据量更高效） #

=ARRAYFORMULA(IF(A2:A=""; "";
  XLOOKUP(LEFT(A2:A; 4); 
    QUERY(Derivatives!D:F; "SELECT LEFT(D,4), COUNT(DISTINCT B) WHERE F='EQUITY CALL' GROUP BY LEFT(D,4)"; 1);
    QUERY(Derivatives!D:F; "SELECT COUNT(DISTINCT B) WHERE F='EQUITY CALL' GROUP BY LEFT(D,4)"; 1);
    0
  )
))

• 逻辑：先用QUERY预统计所有符合条件的左4位对应的唯一B值数量，再通过XLOOKUP匹配A列左4位快速获取结果，减少重复计算。

原公式问题说明 #

原公式使用COUNTA(FILTER(...))统计的是符合条件的总行数，而非唯一值数量；同时INDEX(Derivatives!D:D,0,0)的冗余写法可能导致匹配逻辑异常，直接引用整列即可。

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