使用SUMPRODUCT对PowerQuery切片表格计数失效问题排查

原公式错误原因 #

你的公式出现#VALUE!错误且结果异常，核心问题在于OFFSET在数组运算中的兼容性问题：

• 在非Excel 365的版本中，OFFSET返回的多区域数组无法被SUBTOTAL正确处理，导致SUBTOTAL返回#VALUE!错误。
• 尽管中间步骤报错，SUMPRODUCT仍会尝试对可计算的部分进行求和，最终得到不符合预期的错误结果。

此外，原公式的逻辑是通过SUBTOTAL(2, OFFSET(...))判断单元格是否可见，但OFFSET的数组返回行为在旧版Excel中不稳定，无法可靠生成单个单元格的可见性判断值（1=可见，0=不可见）。

修正后的公式 #

使用INDEX替代OFFSET，因为INDEX在数组运算中更稳定，能可靠生成单个单元格的引用：

=SUMPRODUCT(--(SUBTOTAL(2, INDEX(C:C, ROW(C2:C13)))=1), --(MOD(B2:B13, 3)=0))

公式说明： #

1. INDEX(C:C, ROW(C2:C13))：生成C2到C13的单个单元格引用数组，每个元素对应表格中的一行单元格。
2. SUBTOTAL(2, 单个单元格)：对单个单元格执行计数，可见单元格返回1，不可见（被切片器筛选隐藏）返回0。
3. --(SUBTOTAL(...)=1)：将布尔值转换为0/1的数值数组，标记可见行。
4. --(MOD(B2:B13, 3)=0)：将"能被3整除"的条件转换为0/1的数值数组。
5. SUMPRODUCT：将两个数组对应元素相乘后求和，得到切片器筛选后符合条件的行数。

针对PowerQuery结构化表格的优化 #

如果你的表格是PowerQuery生成的结构化列表（ListObject），建议使用结构化引用，避免因行号变化导致的引用错误：

=SUMPRODUCT(--(SUBTOTAL(2, INDEX([C列], ROW([C列])-MIN(ROW([C列]))+1))=1), --(MOD([B列], 3)=0))

（将[B列]和[C列]替换为你表格中实际的列名）

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