Excel 365无VBA实现行内合格付款金额求和方案问询

问题背景 #

使用Windows 10系统的微软365订阅版Excel，通过数据透视表汇总数千笔应付款交易，表格结构如下：

• 行：以供应商ID/名称为索引，每行对应一个供应商的全年付款数据
• 列：列标题为付款码，每个付款码对应是否需向IRS上报的“合格”属性，该属性存储在独立且需频繁更新的lookup表中

需求：编写无需VBA或宏的单个公式，对每行数据仅求和lookup表标记为“合格”的付款码列对应的金额，实际数据规模约900行×56列。

已尝试的公式 #

以下是尝试过的函数组合，但未实现筛选求和的核心需求：

• 整行无筛选求和：

  =BYROW(ALL_DATA,LAMBDA(row,SUM(row)))
  

• 单行累加无筛选求和：

  =LET(RESULT,SCAN(0,B3:BB3,LAMBDA(a,v,a+v)),INDEX(RESULT,1,COLUMNS(RESULT)))
  

• 单行REDUCE无筛选求和：

  =LET(RESULT,REDUCE(0,B3:BB3,LAMBDA(a,v,a+v)),RESULT)
  

• 修改自David Leal 2022年11月的方案（无法正常运行）：

  =LET(set, B3:BB25, m, ROWS(set), seq, SEQUENCE(1,m,3),CUMULATE, LAMBDA(x, SCAN(0, x, LAMBDA(acc,item, acc+item))),REDUCE(0,seq, LAMBDA(acc,idx, IF(idx = 1,CUMULATE(INDEX(set,idx)),VSTACK(acc, CUMULATE(INDEX(set,idx)))))))
  

解决方案 #

推荐两种简洁高效的无VBA公式方案，适配900行×56列的数据规模：

方案1：BYROW + XLOOKUP + FILTER（直观易读） #

假设：

• 付款数据区域为B3:BB902（对应900行供应商数据）
• lookup表结构：A列为付款码（与数据列标题一致），B列为“合格”标记（如TRUE/FALSE或“合格”/“不合格”），lookup表区域为Lookup!$A$2:$B$57

公式（适配文本标记“合格”）：

=BYROW(B3:BB902,LAMBDA(row,SUM(FILTER(row,XLOOKUP(TRANSPOSE(B2:BB2),Lookup!$A$2:$A$57,Lookup!$B$2:$B$57,"")="合格"))))

公式（适配布尔值TRUE标记合格）：

=BYROW(B3:BB902,LAMBDA(row,SUM(FILTER(row,XLOOKUP(TRANSPOSE(B2:BB2),Lookup!$A$2:$A$57,Lookup!$B$2:$B$57,FALSE)))))

方案2：MMULT（大数据量高效运算） #

利用矩阵乘法一次性完成所有行的合格列求和，避免逐行迭代，效率更高：

=MMULT(B3:BB902,--(XLOOKUP(B2:BB2,Lookup!$A$2:$A$57,Lookup!$B$2:$B$57,"")="合格"))

注：若数据无空值，可直接使用；若存在空值，可修改为MMULT(--(B3:BB902<>""),--(...))避免空值干扰。

公式说明 #

1. XLOOKUP：根据数据列标题（付款码）匹配lookup表的合格属性，生成与数据列一一对应的合格标记数组
2. FILTER：筛选当前行中对应合格付款码的金额，仅保留需求和的数值
3. BYROW：遍历每一行执行筛选求和操作，输出每行结果
4. MMULT：通过矩阵乘法将数据区域与合格标记数组相乘，直接得到所有行的求和结果，大数据场景下性能更优

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