如何在Excel中实现按累计销售月份统计Distinct门店数的数据透视表？

需求回顾 #

• 数据源层级：item-门店-store_id-月份，包含# of items sold、total sum等字段
• 核心目标：统计每个商品（item）对应不同销售月份数的门店数量（例如SKU#2有18家门店仅销售1个月，44家门店销售2个月）

现有方案的弊端 #

• 依赖中间辅助透视表，需隐藏工作表
• 源数据更新时，需手动取消隐藏辅助表、两次刷新透视表，操作繁琐

方案1：Power Query 一站式生成结果 #

无需中间表，用Power Query完成分组统计+透视的全流程：

1. 选中数据源区域，点击「数据」选项卡 → 「从表格/区域」，进入Power Query编辑器
2. 在编辑器中执行以下操作：
   • 点击「转换」→ 「分组依据」：
     • 分组列：勾选item_name、store_id
     • 新列名：输入销售月份数，操作选择「计数（不同）」，关联列选择month
   • 完成分组后，点击「转换」→ 「透视列」：
     • 值列选择store_id，列值选择销售月份数
     • 聚合函数选择「计数（不同）」，勾选「填充空值」并输入0
3. 点击「关闭并上载」，将结果加载到新工作表。后续源数据更新时，只需右键结果表 → 「刷新」即可。

方案2：动态数组公式（Excel 365/2021及以上） #

无需透视表，直接用动态数组生成结果：

1. 提取唯一商品列表（假设item_name在数据源的A列）：

   =UNIQUE(数据源!A:A)
   

2. 提取1-12的月份数序列：

   =SEQUENCE(12)
   

3. 在交叉单元格输入以下公式，自动填充所有结果：

   =LET(
     当前商品, $G2,
     目标月份数, H$1,
     门店列表, UNIQUE(FILTER(数据源!C:C, 数据源!A:A=当前商品)),
     单店销售月数, BYROW(门店列表, LAMBDA(门店, COUNT(UNIQUE(FILTER(数据源!B:B, (数据源!A:A=当前商品)*(数据源!C:C=门店)))))),
     COUNTA(FILTER(单店销售月数, 单店销售月数=目标月份数))
   )
   

   （注：$G2为唯一商品列表单元格，H$1为月份数单元格；公式会自动计算每个商品对应不同月份数的门店数量）

方案3：数据透视表+DAX度量值（需启用数据模型） #

利用Excel数据模型的DAX能力，直接生成透视表：

1. 选中数据源，点击「插入」→ 「数据透视表」，勾选「将此数据添加到数据模型」
2. 在数据模型中添加计算列：

   销售月份数 = CALCULATE(DISTINCTCOUNT('数据源'[month]), ALLEXCEPT('数据源','数据源'[item_name],'数据源'[store_id]))
   

3. 配置透视表：
   • 行区域：item_name
   • 列区域：销售月份数
   • 值区域：store_id，设置值字段为「非重复计数」，空值填充为0
4. 源数据更新时，只需右键透视表 → 「刷新」即可，无需额外操作。

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