如何用PIVOTBY/GROUPBY生成月份有序且无总计的类透视表

问题描述 #

现有包含Month（月份）和Status（状态）的数据集，需要用公式生成类透视表，要求：

• 月份作为列
• 状态分类作为行
• 对应状态的计数作为值

当前使用公式：

=PIVOTBY(B2:B27,TEXT(A2:A27,"mmm"),B2:B27,LAMBDA(x,ROWS(x)),,0)

存在两个问题：

1. 月份列按文本字母顺序排列，不符合自然月份顺序
2. 结果自动生成了总计列，不符合需求

解决方案 #

方案1：优化PIVOTBY公式 #

调整PIVOTBY参数，指定月份自然排序规则并关闭总计：

固定月份范围场景

=PIVOTBY(
    B2:B27, 
    TEXT(A2:A27,"mmm"), 
    B2:B27, 
    LAMBDA(x, ROWS(x)), 
    TRUE, 
    FALSE, 
    2, 
    XMATCH(TEXT(A2:A27,"mmm"), {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"})
)

参数说明：

• 第5个参数TRUE：保留行列表头
• 第6个参数FALSE：关闭总计行/列
• 第7个参数2：按列字段（月份）排序
• 第8个参数：通过XMATCH匹配预设月份列表，强制列按自然顺序排列

动态适配数据月份场景

如果数据中月份不固定，用MONTH函数提取数字自动排序：

=PIVOTBY(
    B2:B27, 
    TEXT(A2:A27,"mmm"), 
    B2:B27, 
    LAMBDA(x, ROWS(x)), 
    TRUE, 
    FALSE, 
    2, 
    SORTBY(UNIQUE(TEXT(A2:A27,"mmm")), MONTH(UNIQUE(TEXT(A2:A27,"mmm")&" 1")))
)

该公式会自动提取数据中的唯一月份，按实际月份数字排序，无需手动维护月份列表。

方案2：用GROUPBY构建类透视表 #

结合LET、XLOOKUP和HSTACK实现需求：

=LET(
    status_list, UNIQUE(B2:B27),
    month_list, SORTBY(UNIQUE(TEXT(A2:A27,"mmm")), MONTH(UNIQUE(TEXT(A2:A27,"mmm")&" 1"))),
    count_data, GROUPBY(B2:B27&"|"&TEXT(A2:A27,"mmm"), B2:B27, LAMBDA(x, ROWS(x)), 0),
    HSTACK(status_list, XLOOKUP(status_list&"|"&TOROW(month_list), INDEX(count_data,,1), INDEX(count_data,,2), 0))
)

逻辑说明：

1. 提取唯一状态列表和按自然顺序排序的月份列表
2. 用状态|月份拼接键作为分组依据，统计每个组合的计数
3. 用HSTACK合并状态行与对应月份的计数列，XLOOKUP匹配对应计数，无匹配时显示0

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