如何在Excel中转换同期群分析（Cohort Analysis）表格格式？

针对你需要将按实际月份排列的同期群（Cohort）收入数据，转换为按「生命周期阶段（第1月/第2月…）」左对齐的对比格式需求，以下是两种简便实现方法：

方法1：INDEX+MATCH公式批量转换（推荐中小数据集） #

核心逻辑是通过匹配「同期群起始月份」和「生命周期对应的实际月份」，将原始数据映射到目标对齐格式区域。

假设数据结构 #

• 原始数据：A列为同期群起始月份（如2023-01），B_{Z列为实际业务月份表头，B2}Z10为对应收入值
• 目标区域：D列为同期群起始月份，E~Y列为生命周期阶段表头（第1月/第2月…）

公式写法 #

在目标区域的第一个数据单元格（如E2）输入以下公式，然后向右、向下批量填充：

=IFERROR(INDEX($B$2:$Z$10,MATCH($D2,$A$2:$A$10,0),MATCH($D2+(COLUMN()-COLUMN($E$1)),$B$1:$Z$1,0)),"")

公式解释 #

• MATCH($D2,$A$2:$A$10,0)：定位当前同期群在原始数据中的行号
• $D2+(COLUMN()-COLUMN($E$1))：计算当前生命周期阶段对应的实际月份（第1月=起始月+0，第2月=起始月+1，以此类推）
• MATCH(..., $B$1:$Z$1,0)：定位该实际月份在原始表头的列号
• IFERROR：自动填充空值处理未产生数据的阶段（如新同期群还未到第N月）

方法2：Power Query批量转换（适合大型数据集） #

如果数据量较大，Power Query可实现自动化批量转换，步骤如下：

1. 选中原始数据区域，点击「数据」选项卡 → 「从表格/区域」，导入Power Query编辑器
2. 点击「转换」选项卡 → 「逆透视列」，选中所有月份列，将宽表转为窄表（生成Cohort/月份/收入三列）
3. 添加自定义列：计算生命周期月份，跨年兼容公式为：

   Date.Year([月份])*12 + Date.Month([月份]) - (Date.Year([Cohort])*12 + Date.Month([Cohort])) + 1
   

4. 点击「转换」→ 「透视列」，以「生命周期月份」为透视列，「收入」为值列，聚合方式选择「不要聚合」（若单阶段仅一个数据值）
5. 将处理后的表格加载回Excel，即可得到左对齐的同期群对比格式

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