Excel如何按组件分组重置公式自动计算百分比类单位占比

以下方案均无需多列辅助，可直接得到最终计算结果：

适用Excel 2007及以上版本（单公式下拉） #

仅需在结果列第一行输入如下公式，下拉填充至所有数据行即可：

=IF(C2="%",IFERROR(B2/SUMIFS(B:B,A:A,A2,C:C,"%"),B2),B2)

列对应规则（可根据实际表结构调整）：

• A列：Component（组件分组字段）
• B列：原始数量字段
• C列：Unit of Measure（单位字段）

公式逻辑说明：

• 先判断当前行单位是否为%，非%单位直接返回原始数量
• %单位的行，用当前行数量除以「同组件分组下所有单位为%的行的数量总和」
• 加入IFERROR容错处理，避免同分组无%类行时出现除以0报错
• 2万行数据下SUMIFS运算效率远高于SUMPRODUCT，无明显卡顿

如果你的数据会频繁新增/删除行，可先选中源数据按Ctrl+T转换为Excel表格对象，使用结构化引用公式，新增行自动套用计算逻辑，无需手动调整公式范围：

=IF([@[Unit of Measure]]="%",IFERROR([@Quantity]/SUMIFS([Quantity],[Component],[@Component],[Unit of Measure],"%"),[@Quantity]),[@Quantity])

适用Excel 365/2021版本（无需下拉，整列自动计算） #

直接在结果列首个空白单元格输入如下动态数组公式，整列结果自动溢出，无需手动下拉填充：

=BYROW(A2:A20001,LAMBDA(x,LET(
u,INDEX(C:C,ROW(x)),
q,INDEX(B:B,ROW(x)),
sum_q,SUMIFS(B:B,A:A,x,C:C,"%"),
IF(u="%",IFERROR(q/sum_q,q),q)
)))

其中A2:A20001为你的组件列数据范围，按需调整即可，源数据修改后结果自动同步更新。

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