无需VBA：Excel按部门识别贡献80%销售额卖家并计算其平均销售额

问题背景 #

现有Excel表格结构：

• A列：Division（部门）
• B列：Seller Name（卖家姓名）
• C列：Sales Amount（销售额）

需求：

1. 识别每个部门中合计贡献80%销售额的卖家（核心难点：超100个部门，需实现部门切换时自动重置累加逻辑）
2. 计算这些达标卖家的平均销售额

当前进展：

• D列已用公式 =SUMIFS(C:C, A:A, A2) 计算对应部门总销售额
• E列已用公式 =C2/D2 计算单卖家销售额占部门总额的比例
• 未解决问题：无法实现部门内占比的动态累加，且部门切换时无法重置累加值

解决方案 #

步骤1：实现部门内销售额占比的动态累加 #

方案1（需先排序）

1. 先对表格按A列（部门）升序、C列（销售额）降序排序
2. 在F列（命名为Cumulative %）输入公式：

   =SUMIFS($C$2:$C2, $A$2:$A2, A2)/D2
   

   利用混合引用（$C$2:$C2）实现：下拉时仅扩展当前部门内的行范围，部门切换时累加自动重置。

方案2（无需手动排序，适合数据频繁更新）

在F列输入SUMPRODUCT公式，自动按销售额降序计算累加占比：

=SUMPRODUCT(($A$2:$A$1000=A2)*($C$2:$C$1000>=C2)*$C$2:$C$1000)/D2

（将1000替换为你实际的最大数据行数）
该公式会自动统计当前部门内销售额≥当前行的所有卖家的销售额总和，再除以部门总额得到累加占比，部门切换时自动重置范围。

步骤2：标记贡献合计80%的卖家 #

在G列（命名为Top 80%?）输入公式，判断当前卖家是否属于目标群体：

=IF(F2<=0.8, "是", "否")

步骤3：计算达标卖家的平均销售额 #

全局平均（所有部门的达标卖家）：

=AVERAGEIFS(C:C, G:G, "是")

分部门平均（当前行对应部门的达标卖家）：

=AVERAGEIFS(C:C, A:A, A2, G:G, "是")

Excel 365/2021 简化方案（动态数组） #

利用动态数组公式无需排序、更高效：

=LET(
    current_dept, A2,
    dept_sales, FILTER(C:C, A:A=current_dept),
    sorted_sales, SORT(dept_sales, -1),
    cumulative_pct, SCAN(0, sorted_sales, LAMBDA(a,b,a+b))/SUM(dept_sales),
    top_count, XMATCH(0.8, cumulative_pct, 1),
    top_sales, TAKE(sorted_sales,,top_count),
    AVERAGE(top_sales)
)

该公式直接返回当前部门内贡献前80%销售额的卖家的平均销售额，自动处理部门切换和累加重置。

内容的提问来源于stack exchange，提问作者Francisco Augusto Varela Aguir