Office 365 Excel按营收总和划分客户四分位的高效方法求助

核心需求回顾 #

按金额累计占比划分四分位（而非客户数量占比）：总营收的Top25%为Q4，次25%为Q3，再次25%为Q2，Bottom25%为Q1，需支持多列批量处理且自动化，对接OLAP模型。

Excel 365 动态数组解决方案（无辅助列） #

单年份列的公式实现 #

假设数据在结构化表格Table1中，目标年份列为[2020]，在对应四分位列的第一个单元格输入：

=LET(
    amt, Table1[2020],
    total, SUM(amt),
    // 按金额降序去重，避免重复金额导致的四分位拆分
    unique_amt, UNIQUE(SORT(amt, , -1)),
    group_totals, SUMIF(amt, unique_amt, amt),
    // 计算分组累计金额
    running_total, SCAN(0, group_totals, LAMBDA(a,b,a+b)),
    // 生成四分位阈值（总营收的0%、25%、50%、75%、100%）
    quartile_thresholds, total*{0,0.25,0.5,0.75,1},
    quartile_labels, {"Quartile 1","Quartile 2","Quartile 3","Quartile 4"},
    // 给每个金额分组分配四分位
    group_quartiles, XLOOKUP(running_total, quartile_thresholds, quartile_labels, , 1),
    // 将四分位匹配回原数据行
    XLOOKUP(amt, unique_amt, group_quartiles, "No Revenue", 0)
)

多年份列批量处理 #

如果需要同时处理[2020]和[2021]列，用BYCOL函数批量生成结果：

=BYCOL(Table1[2020]:[2021], LAMBDA(col,
    LET(
        amt, col,
        total, SUM(amt),
        unique_amt, UNIQUE(SORT(amt, , -1)),
        group_totals, SUMIF(amt, unique_amt, amt),
        running_total, SCAN(0, group_totals, LAMBDA(a,b,a+b)),
        quartile_thresholds, total*{0,0.25,0.5,0.75,1},
        quartile_labels, {"Quartile 1","Quartile 2","Quartile 3","Quartile 4"},
        group_quartiles, XLOOKUP(running_total, quartile_thresholds, quartile_labels, , 1),
        XLOOKUP(amt, unique_amt, group_quartiles, "No Revenue", 0)
    )
))

公式说明 #

• LET：封装变量，简化公式结构，减少重复计算
• UNIQUE+SORT：对金额降序去重，确保相同金额的客户被归为同一四分位
• SCAN：计算分组后的累计金额，替代手动添加累计列
• XLOOKUP(..., ,1)：用近似匹配判断累计金额落在哪个四分位区间
• BYCOL：遍历多列年份数据，批量生成四分位结果，无需重复写公式

OLAP模型（Power Pivot）DAX解决方案 #

如果后续要对接OLAP模型，用DAX创建计算列，适配多维数据集环境：

2020 Quartile = 
VAR TotalRevenue = CALCULATE(SUM('Table1'[2020]), ALL('Table1'))
VAR CurrentAmount = 'Table1'[2020]
// 计算当前金额及以上的所有客户总营收
VAR CumulativeAbove = CALCULATE(
    SUM('Table1'[2020]),
    FILTER(ALL('Table1'), 'Table1'[2020] >= CurrentAmount)
)
// 计算当前金额以下的所有客户总营收
VAR CumulativeBelow = TotalRevenue - CumulativeAbove
RETURN
SWITCH(
    TRUE(),
    CurrentAmount = 0, "No Revenue",
    CumulativeBelow < TotalRevenue * 0.25, "Quartile 4", // Top25%
    CumulativeBelow < TotalRevenue * 0.5, "Quartile 3",
    CumulativeBelow < TotalRevenue * 0.75, "Quartile 2",
    "Quartile 1" // Bottom25%
)

DAX公式说明 #

• ALL('Table1')：忽略当前筛选上下文，计算全局总营收
• CumulativeBelow：通过总营收减去当前金额及以上的累计值，得到当前金额以下的累计营收
• SWITCH：根据累计营收的区间判断客户所属四分位

为什么原生四分位函数不适用 #

PERCENTILE.EXC()、QUARTILE.EXC()等函数是基于数据点的数量分布划分四分位，比如将客户按金额排序后取第25%位置的客户金额作为阈值，而非按总营收的25%金额占比划分，完全不符合按金额总和分组的需求。

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