excel - 如何在excel中动态使用基于过滤器的公式？

Question

我很难弄清楚如何在不使用 SUM 函数的情况下获得总值。

例子:

我可以像在图像中那样使用 SUM 函数获取 SumSQ 的总值。

预期值为:30323295

SumSQ 的公式:SumSQ = (SD^2 * count * (count - 1) + (SumX^2) )/count

在 Excel 中:SumSQ =(SUMSQ(D3) * B3 * (B3-1)+(SUMSQ(C3)))/B3

请注意，SumSQ 是一种辅助列。

我想要的是避免使用辅助列，在本例中为 SumSQ。但我仍然想获得总值 30323295。

所以基本上这是没有过滤器的样子:

总 SumSQ 的公式:SumSQ = (total_SD^2 * total_count * (total_count - 1) + (total_SumX^2) )/total_count

请注意，这必须是动态的，基于 A 列的过滤器。

在 PC_Number 中使用过滤器的示例:

这意味着它应该只计算基于过滤器可见的内容。

我已经尝试使用添加了 OFFSET()、MIN() 和 ROW() 的 SUBTOTAL() 函数，但它不会像我想要的那样工作。

这是我目前拥有的:

--start

=SUMPRODUCT(((SUBTOTAL(9,OFFSET(U5:U256,ROW(U5:U256)-MIN(ROW(U5:U256)),,1))^2)*SUBTOTAL(9,OFFSET(M5:M256,ROW(M5:M256)-MIN(ROW(M5:M256)),,1))*(SUBTOTAL(9,OFFSET(M5:M256,ROW(M5:M256)-MIN(ROW(M5:M256)),,1))-1)+((SUBTOTAL(9,OFFSET(Q5:Q256,ROW(Q5:Q256)-MIN(ROW(Q5:Q256)),,1))*SUBTOTAL(9,OFFSET(M5:M256,ROW(M5:M256)-MIN(ROW(M5:M256)),,1)))^2))/SUBTOTAL(9,OFFSET(M5:M256,ROW(M5:M256)-MIN(ROW(M5:M256)),,1)))

--end

这是使用我的公式的输出:

这对总值(value)有很大的影响。

不确定我是否解释得很好，但这就是它的要点。

Answer 1

普通公式，不是数组公式，只使用SumProduct:

=SUMPRODUCT((D3:D9*D3:D9*B3:B9*(B3:B9-1)+(C3:C9*C3:C9))/B3:B9)

或

=SUMPRODUCT((D3:D9^2*B3:B9*(B3:B9-1)+(C3:C9^2))/B3:B9)

编辑 1:过滤

要按 A 列 添加一些筛选，假设您必须按在 A1 中输入的值进行筛选:

=SUMPRODUCT((A3:A9 = $A$1)*((D3:D9^2*B3:B9*(B3:B9-1)+(C3:C9^2))/B3:B9))

编辑 2:要使过滤可选，下一个公式应用过滤除非 A1 为空，在这种情况下它会考虑所有行:

=SUMPRODUCT((A3:A9=IF(ISBLANK(A1),A3:A9,A1))*((D3:D9^2*B3:B9*(B3:B9-1)+(C3:C9^2))/B3:B9)) 

编辑 3:考虑仅可见(未过滤掉)的行:

=SUMPRODUCT(SUBTOTAL(2, OFFSET(B1, ROW(B3:B9)-1,0))*((D3:D9^2*B3:B9*(B3:B9-1)+C3:C9^2)/B3:B9))

想法是，如果单元格可见(隐藏)，则单个数字单元格的 subtotal(2) 返回 1 (0)。